Ежедневник в Excel. I часть

Ежедневник в ExcelЕжедневник в Excel часть I.

Многие из нас ведут ежедневники.

Вещь очень полезная, но не всегда удобная.

У меня, например, ежедневник неизбежно превращался в записную книжку.

А планы, даже если я их там вел, регулярно перечеркивались. Более удобным для планирования я считаю различные электронные варианты. Один из них — Excel.

Конечно, если уж говорить об электронном планировщике, то почему бы не использовать предназначенный исключительно для этого MS Outlook?

Однако:

  • во-первых, он удобен далеко не всем;
  • во вторых, его требуется специально осваивать;
  • в-третьих, его затруднительно перемещать с одного рабочего места на другой (если, к примеру, постоянное рабочее место отсутствует).

А таблица Excel настолько проста и понятна с точки зрения интерфейса, что можно сесть за компьютер, и тут же накидать план хоть на день, хоть на год.

Идея использовать Excel для ведения ежедневника принадлежит не мне. Впервые я с этим столкнулся на сайте известного тайм-менеджера Глеба Архангельского www.improvement.ru.

Подчеркну — это только идея. Прелесть Excel в том, что он гибок, и каждый может настроить ежедневник под себя.

Я же расскажу о своем варианте, и объясню, почему он именно такой. Первоначально планировщик выглядел точно так, каким его предложил Глеб архангельский. Однако, со временем, что-то было изменено под меня лично, а от чего-то я попросту отказался.

Логика ежедневника-планировщика

Логика планировщика в ExcelЛогика планировщика в Excel включает следующие принципы:

  1. Основной принцип предложенного подхода — ежедневник должен входить в экран и иметь возможность быть распечатанным на одной странице (А4 или А5 — это на ваш выбор).
  2. Должны быть верно учтены зоны распределения внимания — первоначально мы смотрим в левую верхнюю зону, проходим слева на право, переключаемся в центр. Держим в поле зрения левую сторону и в самую последнюю очередь обращаем внимание на правую нижнюю зону.
  3. Человек одномоментно может держать в зоне своего внимания не более трех единиц информации.
  4. Планирование должно учитывать стадии — долгосрочное планирование, среднесрочное планирование, краткосрочное планирование.
  5. За максимальный период планирования принимаем год. На мой взгляд, это наиболее оправдано для регулярного тайм-менеджмента. Впрочем, если пожелаете, можете сверху посередине написать задачи из разряда жизненных целей. В этом случае планировщик будет иметь более целостный вид.
  6. Любой план может меняться. Это не должно нарушать структуру планировщика.

Структура ежедневника в Excel

Таким образом получаем следующий вариант структуры ежедневника (нетерпеливые в конце второй части этой статьи могут скачать его образец):

Структура ежедневника Excel

Что мы здесь имеем:

  1. Левая верхняя часть для долгосрочных планов. Это задачи наиболее высокого уровня обобщения. О них мы должны постоянно помнить. Из них формулируются среднесрочные цели. Их ревизию мы проводим 1 раз в месяц, в зависимости от выполнения среднесрочных целей. При желании, можно указать срок.
  2. Верхняя средняя часть для среднесрочных целей. Они логически вытекают из долгосрочных. Нередко среднесрочные цели не связаны с долгосрочными, если в организации, в которой вы работаете, задачи вам ставятся без учета стратегии. Но это уже другая тема. В идеале, конечно, среднесрочные цели призваны способствовать достижению долгосрочных. В этом блоке срок указывать очень желательно. Ревизия блока проводится еженедельно.
  3. Правая верхняя часть для планирования встреч. Здесь все просто — пишем, с кем и где встречаемся, и указываем время встречи. Зона должна привлекать внимание в день, когда встреча запланирована. О том, как это сделать, поговорим ниже.
  4. Средняя зона для оперативного ежедневного планирования. Как можно уже догадаться, ежедневные задачи вытекают из среднесрочных целей. В России, конечно, очень часто срочные связаны с авралами в результате отсутствия долгосрочного планирования и выставления приоритетов. Но это тоже отдельная тема. Для выделения этой зоны, я окрасил ее в желтый цвет.
  5. Левая нижняя сторона отведена для внесения возникающих идей для их последующего более глубокого обдумывания. Маленькая записная книжка. Очень важный творческий инструмент.
  6. Правая нижняя сторона — своеобразная мусорная корзина. Когда задача выполнена, она помещается сюда. В колонке «Примечания» можно написать дату выполнения (полезно для отчетов руководству), а также обзор ошибок и идей для дальнейшего планирования. Рекомендую проводить ревизию корзины еженедельно, удаляя пункты, по которым отчет или анализ уже сформированы.

В следующей статье я расскажу как дополнительно настроить ежедневник в Excel и как им пользоваться.

Пожелания пишите в комментариях. Если статья понравилась — прошу вас нажать на одну из кнопок ниже.

Ежедневник в Excel. II часть

Ежедневник в ExcelЕжедневник в Excel часть II.

Продолжим разбираться с ежедневником.

Первая часть статьи здесь.

Грех было бы не использовать возможности форматирования в Excel  для большей наглядности.

Вот что здесь можно сделать:

  • Автоматическое отображение текущей даты. В Excel есть функция СЕГОДНЯ(), которая сама выводит текущую дату. Для наглядности, эту функцию можно улучшить, чтобы отображалась не просто числа, а целый текст и указывался день недели. Для этого в ячейку просто нужно внести формулу
    =СЦЕПИТЬ(«Сегодня «;ТЕКСТ(СЕГОДНЯ();»дддд, «);ТЕКСТ(СЕГОДНЯ();»[$-FC19]Д ММММ ГГГГ \го\да\.»)).
  • На основании сегодняшней даты, необходимо добиться, чтобы таблица автоматически сигнализировала о целях и задачах, срок которых уже приближается к критическим. Для долгосрочных целей, если указан срок, сигнал должен появиться за месяц. Для среднесрочных — за неделю. Также, если какая-либо запланированная встреча должна произойти сегодня, то она должна быть выделена аналогичным образом.
    • Я долго не думал, а просто определил правило окрашивания выделяемых ячеек в красный цвет. Для этого в Excel версии 2007 года и выше имеется инструмент условного форматирования ячеек.
    • Для начала нам необходимо в какой-нибудь свободной ячейке внести функцию СЕГОДНЯ. Это необходимо для того, чтобы сравнивать даты. Формула, которую мы внесли для отображения текущей даты в удобном для нас виде не годится. Внесем функцию в ячейку Е11, а затем установим белый цвет шрифта, чтобы запись нас не отвлекала.
    • Теперь определяем правило форматирования для блока долгосрочных задач:
      1. Выделяем все ячейки блока
      2. Нажимаем на кнопку «Условное форматирование» и выбираем из выпадающего списка «Создать правило».
      3. В открывшемся окне выбираем «Использовать формулу для определения форматируемых ячеек».
      4. В поле для ввода формулы вносим: =И(($B2-$E$11)<=30;($B2-$E$11)>0). Как видим, мы установили два условия для окрашивания — срок должен быть меньше или равен 30 дням (месяц) и быть больше нуля (чтобы исключить окрашивание незаполненных ячеек).
      5. Нажимаем на кнопку «Формат», открываем закладку «Заливка» и выбираем красный цвет.
    • Для блока среднесрочных задач все проделываем аналогично, но формула здесь будет чуть другая: =И(($E2-$E$11)<=7;($E2-$E$11)>0).
    • Для блока встреч формула должна сравнивать текущую дату с числом, указанным для встречи. но, предварительно, мы должны округлить значение даты встречи, чтобы исключить из сравнения время. Ведь время, если перевести его в числовой формат, эквивалентно дробным значениям. В противном случае даже если дата сегодняшняя, сравнение укажет разные значения. Формула будет следующей: =ОКРУГЛВНИЗ($H2;0)=$E$11

Ежедневник готов!

Как им пользоваться

Каждое утро открывайте его, и определяйте план на сегодня. Вечером корректируйте текущие задачи в зависимости от их выполнения, планируйте встречи. Выполненные задачи складывайте в корзину и отмечайте дату ее выполнения и ценные идеи, возникшие в ходе ее выполнения.

Каждую пятницу проводите ревизию корзины и блока среднесрочных задач. Если приходите к мысли, что сроки не реальны или мероприятие потеряло актуальность, не бойтесь их редактировать. Планировщик для того, чтобы не забывать и управлять своей жизнью. Мы его хозяева, а не наоборот.

В последний день месяца анализируйте, насколько вы приблизились к выполнению долгосрочных целей. Принимайте решение о коррекции, при необходимости. Редактируйте среднесрочные цели.

Когда есть свободное время, обращайте внимание на блок идей. Возможно, они послужат поводом для новых задач или изменению стратегии.

Если специфика вашей деятельности предполагает перемещения, распечатайте эту страницу и носите ее вместе с записной книжкой. Все необходимые заметки вносите сюда, и переносите на компьютер вечером.

Мой вариант ежедневника можно скачать здесь.

Желаю вам успехов!

Пожелания пишите в комментариях. Если статья понравилась — прошу вас нажать на одну из кнопок ниже.

Создание плана-графика в excel

Создание плана-графика ExcelСоздание плана-графика в Excel.

При планировании мероприятий, особенно, если работа происходит в группе, важно иметь наглядный план-график.

Конечно, существуют программы, типа MS Project или, бесплатный,  OpenProj. Но гибкость, которая присуща Excel при планировании проекта, не сравнится ни с какой специализированной программой.

Да и требуется ли приобретать и учиться работе в новой программе, ради обычного составления графика Ганта? На мой взгляд, если ты не являешься профессиональным менеджером проекта, то и погружаться в дебри специализированных программ тебе совершенно ни к чему. Лучше потратить время на развитие в более близких твоему профилю областях.

Итак, допустим, нам необходимо спланировать и распределить по ответственным задачи по какому-либо проекту. В качестве образца возьмем один из проектов, план-график к которому мне довелось составлять.

Создание структуры таблицы

Мы имеем перечень мероприятий, ответственных по каждому мероприятию и сроки.

Для начала продумаем структуру таблицы. Помимо временной шкалы, она будет состоять из следующих колонок:

  1. Порядковый номер мероприятия;
  2. Наименование мероприятия;
  3. Ответственный исполнитель;
  4. Дата начала мероприятия;
  5. Здесь можно сделать дату окончания, но мне кажется компактнее и нагляднее использовать значение продолжительности мероприятия в днях. (берем календарные дни. Для более продвинутых, можно запрограммировать условие расчета по рабочим дням, но это отдельная тема.)
  6. Примечания — как бы полно мы ни называли задачу, все равно будут возникать нюансы, которые целесообразно писать в примечаниях.

Над таблицей предусмотрим две строки:

  • Наименование проекта;
  • Строка для отображения текущей даты.

Построение шкалы времени

Далее выстраиваем шкалу времени. У меня нет строгих требований к ее длине. Я, скорее, ориентируюсь на удобство восприятия. Обычно выделяю 30 колонок, и сужаю их размер, чтобы ячейки стали квадратными.

Над таблицей выделяем место для определения шага временной шкалы. Это понадобиться нам для того, чтобы гибко подходить к размерам страницы. Допустим, если проект будет общей продолжительностью три месяца, то сделав шаг в один день, мы слишком растянем временную шкалу, и нам потребуется не 30, а более 90 ячеек по горизонтали. Это сделает таблицу трудночитаемой. В подобных случаях можно сделать шаг, допустим, в три дня, или в одну неделю, и шкала окажется вполне компактной.

В строке заголовков будут даты. Увеличиваем высоту строки с датами, чтобы их значения, сориентированные вертикально, полностью помещались в ячейке. Теперь нужно задать ориентацию текста в этих ячейках. Выделяем все, которые мы хотим настроить, нажимаем правую кнопку мыши и в открывшемся окне выбираем пункт «Формат ячеек«. Открываем закладку «Выравнивание» и находим блок «Ориентация«. На мой взгляд, удобно будет, если мы установим ориентацию в 45 градусов. Так и сделаем.

Изменяемой будет только самое левое значение. Сюда мы вносим дату начала проекта. А в ячейку справа от нее вносим формулу =G3+$O$2, где второе слагаемое, это адрес ячейки с шагом временной шкалы. Далее просто протягиваем эту формулу — то есть выделяем ячейку с формулой, наводим курсор мыши на ее нижний правый угол (появиться черный крестик), зажимаем левую кнопку мыши, и ведем мышкой вправо до конца строки. Таким образом, формула будет скопирована автоматически.

Проверяем — внесем в ячейку с шагом временной шкалы число 2. Смотрим на шкалу — все даты должны отображаться через день — если в одной ячейке 1 января, то в следующей ячейке должно быть 3 января.

Настройка условного форматирования

Теперь самое интересное и полезное для плана-графика проекта — настройка условного форматирования. Нам нужно, чтобы на временной шкале по каждому мероприятию цветом отображался интервал с даты начала этого мероприятия до его окончания. Кроме того, на мой взгляд, полезным будет настроить что-то вроде вертикальной полоски, которая будет отображаться в колонке с текущей (сегодняшней) датой.

Самое сложное в этом — продумать формулу, которая будет являться условием окрашивания ячеек. Логически, все — просто. Должно быть окрашено то, что позднее или равно дате начала, но раньше или равно дате окончания. В Excel для этого используется функция «И», которая проверяет соответствие нескольким условиям.

условное форматированиеВыделяем все ячейки временной шкалы. Находим в меню Excel «Условное форматирование» и выбираем пункт «Создать правило«. Выбираем в открывшемся окне пункт «Использовать формулу для определения форматируемых ячеек«. В строку для формул вносим: =И(G$3>=$D4;G$3<=($D4+$E4)). Цвет заливки выберем, например, зеленый.формула шкалы

Создадим второе условие, для полоски, отмечающей сегодняшнюю дату. Если бы во временной шкале у нас был шаг в один день, то мы бы просто внесли равенство сегодняшней даты с датой по временной шкале. Но текущий день во временной шкале может отсутствовать, если шаг установлен в несколько дней. Поэтому нам снова нужно внести двойное условие — текущая дата должна быть больше или равно соответствующей даты в шкале времени, и быть меньше (без равно) следующей по шкале времени даты.

Формула выглядит так: =И($C$2>=G$3;$C$2<H$3). На этот раз цвет выбираем красный.

Итак, проверяем работоспособность таблицы. Заносим все данные, и смотрим, что у нас получилось. Как видим — все работает, а полученный шаблон можно использовать для любых других проектов.

Делитесь материалом со своими друзьями, нажав на кнопки ниже. Наверняка кому-то он будет полезен.

Усовершенствование плана-графика

Связанные задачи

Вопрос от читателя по имени Олег (см. комментарии ниже) натолкнул меня на усовершенствование файла. Действительно, почему бы не предусмотреть возможность связывания задач, когда дата начала последующей задачи будет начинаться на следующий день после предыдущей? Кроме того, было бы неплохо предусмотреть сигнализирование в случаях, когда происходит задержка выполнения той или иной задачи.

Что было сделано:

В прежней версии плана-графика дата старта проекта указывалась вначале колонки с датами. Удобнее было бы, если бы дата старта проекта указывалась в заголовке. Так и сделаем — добавим строку в заголовке, создадим там поле «Дата старта проекта«, а от той ячейки, куда мы раньше заносили дату, сделаем ссылку на это поле.

Как видно из рисунка, после колонки «Ответственный» добавлена колонка «№ предшествующей задачи«. Суть ее понятна — в нее будет вносится номер той задачи, к которой должна быть привязана текущая задача (после которой должно начинаться ее выполнение). А в ячейку первой строки колонки с датой начала занесем следующую формулу:

=ЕСЛИ(ЕПУСТО(D5);$C$3;ВПР(D5;$A$5:$F$21;5;ЛОЖЬ)+ВПР(D5;$A$5:$F$21;6;ЛОЖЬ)+1)

Протянем ее до нижней ячейки колонки. Суть формулы в том, что при отсутствии ссылки на предшествующую задачу будет отображена дата старта проекта. В случае, если номер предшествующей задачи будет указан, то будет указана дата этой задачи плюс значение ее продолжительности плюс 1 (на следующий день). Все, привязка выполнена. Теперь, если начнется аналогичный проект, достаточно будет сменить дату старта проекта, а все прочие даты выполнения задач сдвинутся автоматически.

Следующая задача — настроить индикацию задержек. Видов задержек может быть два:

  • задача выполнена, но с запозданием;
  • задача до сих пор не выполнена.

В первом случае в колонке «Дата фактического выполнения» (ее мы добавили к новой версии файла) будет внесена дата, позднее даты начала выполнения задачи плюс значение колонки «Продолжительность«. Во втором случае текущая дата будет позднее даты начала задачи плюс значение колонки «Продолжительность«, но в колонке «Дата фактического выполнения» ячейка будет пуста.

Предусмотрим для разных случаев различные сообщения:

  • Задержек нет — цвет заливки зеленый;
  • Задержка на … дней — надпись жирным красным шрифтом;
  • Срок выполнения задачи вышел — цвет заливки красный.

Для отображения соответствующих записей, внесем в верхнюю ячейку колонки «Сообщение о ходе выполнения» следующую формулу:

=ЕСЛИ(ЕПУСТО(G5);ЕСЛИ($C$2>(E5+F5);»срок выполнения задачи вышел»;»»);ЕСЛИ(F5<РАЗНДАТ(E5;G5;»d»);»задержка на «&РАЗНДАТ(E5;G5;»d»)-F5&» дней»;»задержек нет»))

Теперь осталось настроить условное форматирование для разных вариантов текста, и файл готов. Чтобы проще было ориентироваться, все ячейки, в которые данные заносятся с клавиатуры, я закрасил желтым цветом. Вот что у меня получилось:

таблица ексель

Скачать файл можно по ссылке.

По просьбе читателя по имени Данила, добавлена функция отображения сообщения о завершении задачи с опережением срока. Скачать файл с этой функцией можно по ссылке.

Уточнение срока

И еще одно дополнение. В графике отсчет всех сроков начинается со следующего дня. Например, если задача стартовала 11-го января, а закончилась 12-го, то таблица укажет, что продолжительность задачи 1 день, и все расчеты будет осуществлять исходя из этого принципа. Однако, по логике, 11-е число тоже должно считаться, и, в этом случае, следовало бы считать два дня. Возможно, это не принципиально, но для полноты картины выкладываю файл, который первый день срока выполнения задачи тоже учитывает при расчете ее продолжительности.

График с почасовым учетом

Благодаря моим уважаемым читателям, план-график обрастает все новыми возможностями. Так, по просьбе Александра, решил доработать файл, чтобы он вел учет не по дням, а по часам. Стало чуть менее удобно вносить данные о дате и времени, но, зато, теперь все считается в почасовом формате. Принимайте новый файл.

Учет выходных дней

И еще одно улучшение — читательница Вера предложила считать продолжительность задач в рабочих днях. Что и было сделано в этом файле.

Ежедневный мониторинг выполнения плана в Excel. I часть

Ежедневный мониторинг выполнения плана ExcelЕжедневный мониторинг выполнения плана в Excel. Часть I.

Сегодня я хочу поделиться с вами своим опытом участия в планировании производства.

Тогда производство работало в убыток, и отслеживать его работу приходилось в ежедневном режиме.

Для этого существуют, конечно, целые информационные системы, но компания небольшая, и приобретать дорогостоящую программу, требующую, плюс ко всему, особого сопровождения, просто нецелесообразно. Поэтому мы решили использовать обычный Excel.

Итак, у нас есть годовой план выхода готовой продукции в рублях, разбитый помесячно с приростом. Чтобы этот план выполнить, необходимо ежедневно контролировать, насколько мы к нему приближаемся. На основании статистики прошлого периода, мы знаем, сколько в среднем вырабатывает один рабочий за смену. С этой суммой будем сверяться в дальнейшем, чтобы понимать, когда мы начинаем отставать и что необходимо предпринять.

Постановка задачи

Мы знаем, сколько рабочий в среднем вырабатывает ежедневно. Производственных ресурсов достаточно. Не хватает только людей. Таким образом программа должна уметь:

  • Разбивать месячный план на каждый день, но с учетом того, сколько уже было сделано. Другими словами, если сегодня мы не справились с дневной нормой, то она перераспределяет нормативы следующих дней.
  • Рассчитывать, сколько человек необходимо привлечь к производству, чтобы справиться с планом.
  • Указывать фактическую выработку на человека , чтобы сигнализировать об отклонениях.
  • Указывать, сколько уже сделано на текущий день.
  • Использовать данные, которые в настоящее время уже ведутся, чтобы не перегружать сотрудников дополнительной работой.

Продумывание логики реализации

На момент постановки задачи мы имели в каждом цехе регулярно заполняемую мастерами таблицу учета отработанного времени. От мастеров также требовалось регулярно отслеживать дневную выработку своей бригады. Поэтому мы бы их несильно напрягли, если б обязали вносить сумму дневной выработки бригады в таблицу.

В имеющейся таблице учета рабочего времени мы имеем все, что нас интересует в плане отслеживания текущего состояния дел на производстве:

  • сколько сотрудников отработало в смене;
  • сколько было выработано за каждую смену.

Этого достаточно, чтобы рассчитать:

  • сумму выработки на текущий день;
  • сумму выработки на человекосмену.

Все данные, которые мастера вносят в таблицы (эту страницу назовем «Бригады«), автоматически обрабатываются на отдельной странице (назовем ее «Планирование«), в которой мы и сможем наблюдать всю интересующую нас информацию.

Количество рабочих, необходимое для выполнения плана, будет рассчитываться на основании нормативного значения выработки на человекосмену. Поскольку это значение у нас определяется отдельным порядком, то в таблицу оно вносится руководителем вручную, также, как и месячный план.

Таблица ввода отработанных часов

Таблица ввода отработанного каждым рабочим времени уже ведется мастерами. Нам необходимо ее немного улучшить:

  • настроить выбор месяца из списка;
  • настроить выбор из списка наименования профессий, разрядов и бригад;
  • на основании выбранного месяца логично настроить автоматическое выстраивание дат и дней недели в заголовке каждой колонки;
  • количество дней в разных месяцах разное, поэтому желательно, чтобы это было учтено в заголовках колонок. Например, даты, относящиеся к следующим месяцам, попросту бы не отображались;
  • защитить все прочие ячейки таблицы от ошибочного ввода.

О выборе из списка я уже писал в этой статье. Единственное, что рекомендую, выделить для всех списков отдельный лист (назовем его «Списки«). Это позволит не переживать о том, что изменение структуры какой-либо таблицы может нарушить список, расположенный здесь же.

Теперь подробнее об автоматическом выстраивании дат. Фактически, нам необходимо установить дату только первого числа месяца, поскольку все прочие можно сформировать простым прибавлением единицы к каждому предыдущему значению.

Для даты у нас в разных ячейках есть наименование месяца и год (в ячейке С1 мы это предусмотрели). Используем функцию ДАТАЗНАЧ (она преобразует текстовое обозначение даты в числовой, который и понимает Excel)  и ссылаемся на эти ячейки. А в том месте, где должно быть число месяца, просто ставим 1. Все эти данные в формуле объединяем с помощью оператора &: =ДАТАЗНАЧ(1&E1&C1)

Теперь подумаем, как сделать так, чтобы дата, переходящая на следующий месяц не отображалась. Предлагаю такой вариант:

  • используем функцию МЕСЯЦ, которая преобразует дату в число, обозначающее порядковый номер месяца;
  • с помощью функции ЕСЛИ настроим сравнение по алгоритму
    • если номер месяца даты в ячейке слева равен номеру месяца той же даты плюс 1 то …
    • оставляем ту же дату плюс 1, в противном случае …
    • оставляем пустое значение (оно указывается просто двумя кавычками).
  • Эту формулу запишем во всех ячейках, соответствующих числам месяца 29-31.

=ЕСЛИ(МЕСЯЦ(AG3)=МЕСЯЦ(AG3+1);AG3+1;»»)

День недели определяется с помощью функции ДЕНЬНЕД. Правда возвращает он число от 1 до 7, а нам нужно текстовое обозначение. Делается это с помощью следующей формулы: =ТЕКСТ(ДЕНЬНЕД(G3;2);»ДДД») Как вы понимаете, три буквы Д в кавычках определяет формат отображения дня недели.

Чтобы не было надписей, соответствующих пустым ячейкам с датой, добавим условие ЕСЛИ ячейке, соответствующей 29-му числу месяца: =ЕСЛИ(AH3=»»;»»;ТЕКСТ(ДЕНЬНЕД(AH3;2);»ДДД»)) Затем протянем эту формулу на ячейки, соответствующие 30-му и 31-му числам месяца.

Предлагаю также закрепить области по границам ячейки F5. Это существенно облегчит работу в случае, если размер таблицы будет очень большим.

Еще одна рекомендация — установите табличный курсор в наименовании колонки «Бригады» и установите фильтр. Поскольку таблицу будут заполнять мастера разных бригад, то нужно позволить им не отвлекаться на чужих работников выбрав соответствующее обозначение в фильтре.

О защите ячеек от ошибочного ввода я писал здесь. Настроим условие на ввод целых чисел от 1 до 12 (ну, или до 24). Пароль в своем файле я не устанавливаю, а вам предлагаю это сделать.

На этом мы прервемся. В следующей, заключительной, статье поработаем со страницей отчета, а в конце текста я, как обычно, выложу файл со всеми формами и формулами для скачивания.

Ежедневный мониторинг выполнения плана в Excel. II часть

Ежедневный мониторинг выполнения плана ExcelЕжедневный мониторинг выполнения плана в Excel. Часть II.

Продолжим формировать таблицу.

Ранее мы настроили страницу ввода данных об отработанном времени рабочих и о дневной выработке.

Пора переходить к обработке этих данных.

Страница отчета

Открываем другой лист, который назовем «Планирование«. В верхней левой части разместим следующие поля:

  • План на месяц в рублях — сумма будет вноситься руководителем вручную, поскольку это значение рассчитывается заранее;
  • Текущая дата — это значение будет устанавливаться автоматически с помощью функции СЕГОДНЯ;
  • Фактическое выполнение плана на текущую дату — значение рассчитывается автоматически на основании данных по ежедневной выработке, которую вносят мастера на странице «Бригады«;
  • Процент выполнения плана — просто делим факт на план;
  • Выработка на человекосмену в рублях — очень важный показатель, который мы рассчитали ранее на основании прежних статистик по выработке. На основании этого значения будет рассчитываться потребность в рабочих для выполнения ежедневного плана по выработке.

Формулы, которые прописаны в ячейках, я, для вашего удобства, разместил справа от них. Вот что у меня получилось (см. рис.)

Теперь приступим к созданию строк нашей таблицы:

  • Дата — дата, соответствующая дню месяца. Мы можем сделать простые ссылки на ячейки с датой страницы «Бригады«;
  • План — здесь должен быть хитрый расчет, вначале месяца мы просто делим сумму плана на количество рабочих дней в месяце, и получаем искомое значение. Количество дней в месяце это функция СЧЕТ, которая подсчитывает количество чисел в диапазоне. Эту функцию я поместил справа от нашей таблицы, чтобы не загромождать формулу. А теперь давайте подумаем — после того, как первый день будет отработан, значение выработки в этот день должно скорректировать план. Если план на день перевыполнен, то сумма перевыполнения распределяется по оставшимся дням, снижая, тем самым, плановую выработку на эти дни. Если план не выполнен, то выработка на следующие дни должна быть больше. Это непростая задача, и о том, как она решена, будет написано ниже.
  • Факт — простые ссылки на значения со страницы «Бригады«.
  • План по количеству рабочих на день. Здесь достаточно разделить дневной план по выработке на значение выработки на человекосмену. Правда будут получаться дробные значения, а полтора землекопа нам ну никак не нужны. Поэтому используем функцию ОКРВВЕРХ. Вот формула для ячейки, соответствующей первому числу месяца: =ОКРВВЕРХ(B8/$B$5;1)
  • Количество рабочих. Снова используем функцию СЧЕТ. На этот раз на странице «Бригады» посчитаем количество чисел в колонках с соответствующими днями месяца. Ведь каждое число, это человек, который отработал в этот день определенное количество часов.
  • Не хватает для плана — это разница между требуемым количеством рабочих, и фактическим.
  • Фактическая выработка на человекосмену — фактическую выработку делим на количество рабочих.

Как же нам настроить автоматический пересчет плана на каждый день? Используем следующие предпосылки:

  • Каждый день план уменьшается на сумму фактической выработки прошлых дней. Значит расчет будем вести от разницы между месячным планом и суммой выработки прошлых дней.
  • Вот только когда мы будем протягивать формулу на следующие ячейки, она будет увеличивать план, поскольку в этой логике все нулевые значения будут означать, что план не выполнен. Нам нужно создать отдельную строку, в которой будет условие — если рабочие в день не работали, будут браться плановые значения. А если рабочие работали — фактические. Это условие я поместил в строку №15, которую назвал «Проверка». Вот какая там формула: =ЕСЛИ(B9=0;B8;B9). Можно привязать расчет и к формуле СЕГОДНЯ:  =ЕСЛИ(В7>=СЕГОДНЯ();В8;В9). Правда в этом случае необходимо обеспечить своевременное заполнение таблицы и с данными в ней задним числом уже не поиграешься.
  • Каждый раз, считая ежедневный план, нам необходимо делить оставшуюся плановую сумму на оставшееся количество дней. Откуда брать оставшееся количество дней? Тут я долго не думал, а просто добавил над таблицей ряд от 1 до 30, начинающийся с колонки, соответствующей второму числу месяца. Теперь если из количества дней в месяце (эту формулу мы добавили чуть ранее, как вы помните) вычесть цифру, соответствующую текущей дате, то получится как раз количество оставшихся дней до конца месяца.

Таким образом, формула плана по выработке на первое число месяца будет такой: =$B$1/$AG$7

А со второго числа — такой: =($B$1-СУММ($B$15:B15))/($AG$7-C6)

Протянем ее до конца месяца и, начиная с 29 числа, добавим условие на пустое значение (если ячейка даты пустая, то и эта ячейка должна быть пустой):

=ЕСЛИ(AE7=»»;»»;($B$1-СУММ($B$15:AD15))/($AG$7-AE6))

Все! Вот формулы в ячейках, соответствующих вторым числам месяца (см. рис.)

И вот такая получилась таблица:

Остается составить диаграмму для наглядности. И тут нас ждет очередная засада — график выстраивает линии даже там, где их быть не должно — на пустых значениях линии плана, и на нулевых значениях линии факта. Я долго искал решение, и нашел один вариант — использовать функцию НД. Ее диаграмма воспринимает как пустое значение, и линии не строит.

Для этого нам нужно выстроить отдельные ряды с формулой:

  • Для плановых значений делаем простые ссылки, а в колонке соответствующей 29-му числу месяца пишем: =ЕСЛИ(AD8=»»;НД();AD8). Затем протягиваем формулу до колонки, соответствующей 31-му числу месяца.
  • Для фактических значений начиная с колонки, соответствующей первому числу, пишем: =ЕСЛИ(B9=0;НД();B9). Заем протягиваем формулу до 28-го числа. Далее пишем: =ЕСЛИ(AE9=0;НД();ЕСЛИ(AE9=»»;НД();AE9)) и протягиваем формулу до 31-го числа.

Эти два ряда и используем для построения диаграммы.

Осталось навести небольшой марафет, и вуаля — таблица ежедневного мониторинга выполнения плана производства готова! Можно ее и дальше дорабатывать, например, сделать так, чтобы она еще и зарплату считала, но это тема уже отдельной большой статьи.

Расчет заработной платы в Excel. I часть

Расчет заработной платы ExcelРасчет заработной платы в Excel. Часть I.

Схем расчета заработной платы в разных организациях разработано великое множество.

И, казалось бы, какой смысл приводить пример всего лишь одной расчетной таблицы зарплаты?

Тем не менее, я полагаю, на примере, который здесь рассматривается, достаточно понять логику, чтобы впоследствии легко настроить расчет по любому алгоритму.

Тем более, что рассматриваем мы отнюдь не простой способ расчета сдельной оплаты труда с фондом, зависящим от суммы выработки предприятия. Этот фонд часто называют «котел«.

В чем суть этого котла?

  1. Ежемесячно подсчитывается сумма, на которую было произведено (или продано, если это торговля) продукции;
  2. Эта сумма соотносится с планом, меняясь в зависимости от процента выполнения плана;
  3. По каждому рабочему (сотруднику) рассчитывается его доля, на которую он может рассчитывать в этом «котле». Эта доля зависит:
    1. От времени, которое сотрудник отработал за месяц;
    2. От его эффективности, которая может определяться по различным параметрам. Например: количество брака, которое допустил работник, количества нареканий к нему со стороны руководства или коллег (покупателей), количества произведенной (проданной) продукции и т.п.
    3. От количества работников, поскольку, чем больше людей в бригаде, тем меньшая сумма приходится на каждого.
  4. Сумма котла умножается на эту долю, определяя, таким образом, сумму, на которую может рассчитывать работник.

Настроить расчет прочих схем расчета заработной платы по показателям просто — вставляешь формулу, и готово. А в случае с котлом, помимо учета показателей, необходимо еще и определять долю каждого по нескольким параметрам.

Структура файла

В качестве источника данных об отработанном времени можно взять таблицу учета рабочего времени, которую мы создавали здесь . Но, поскольку мы берем пример из производства, то, предлагаю, использовать созданную ранее таблицу ежедневного мониторинга выполнения плана производства. Здесь есть и информация об отработанном времени, и данные о ежедневной выработке, которые лягут в основу расчета котла.

выработка

На производстве оценка доли, которую имеет рабочий, рассчитывается на основании оценки, называемой «Коэффициент Трудового Участия» (КТУ). Ее устанавливает мастер или бригадир ежедневно, на основании того, как сработал этот день рабочий — сколько сделал брака, исполнял ли распоряжения и т.п. Обычно правила выставления КТУ строго регламентируются, чтобы свести к минимуму произвол, возможный со стороны мастеров или бригадиров. Эти правила тоже можно заложить в Excel, но мы не будем усложнять материал. Главное — понять принцип.

Итак, помимо отработанного времени, в ячейках необходимо проставлять еще и КТУ. Можно для этого создать еще одну таблицу, но мастерам это было бы неудобно. Приемлемый вариант, когда руководитель проставляет количество отработанных часов сотрудником, а рядом — его КТУ. Но для этого нужно будет добавить колонок на странице «Бригады», а этого я делать не рекомендую, так как тем самым мы нарушим структуру таблицы, на которую уже понаделали разных ссылок. Рискуем эти ссылки «сломать», что приведет к переделке всего файла.

К тому же в дальнейшем, когда мы будем использовать протягивание формул, оно не будет корректно работать, если данные в таблице будут чередоваться. Поэтому, предлагаю, добавить две страницы: одна — для заполнения мастерами, другая — для значений КТУ, скопированных из первой.

Лист «Бригады» предлагаю переименовать на «Часы«. Лист с КТУ так и назовем — «КТУ«. А лист для ввода назовем «Ввод«.

Страницы для учета часов и КТУ

Поскольку первоначально мы настраивали лист «Бригады» (теперь «Часы«) для ввода, то можно попросту скопировать его содержание на лист «Ввод» со всеми формулами. Следующий шаг — на все заполненные ячейки страницы «Ввод» настраиваем ссылки со всех ячеек страницы «Часы«. Для этого устанавливаем табличный курсор на ячейку А5 листа «Часы«, вносим знак «=«, переходим на лист «Ввод» и кликаем мышкой на аналогичной ячейке. Жмем «Enter«. Затем протягиваем ячейку А1 листа «Часы» на все ячейки в диапазоне А5:AJ28.

Аналогичным образом сделаем ссылки со всех прочих заполненных ячеек листа «Часы«.

Поскольку на странице «Часы» данные вносится не будут, нужно сделать все ячейки здесь защищаемыми. Ну, и отмените заливку, чтобы не сбивать пользователей с толку.

Страницу «КТУ» я предлагаю оформить также, как и страницу «Часы«. То есть копируем все ячейки страницы «Часы» и вставляем их в страницу «КТУ«. Ссылки на количество часов удаляем.

Теперь добавим колонки для ввода значений КТУ на странице ввод и строку для наименования колонок. После всех манипуляций с оформлением, получится вот такая таблица (см. рис.).

таблица ввода

Если вы помните, мы делали ограничение на ввод значений кроме от 1 до 12. На странице «Часы» это ограничение можно оставить, а со страницы КТУ ограничение нужно снять.

Выделите все ячейки для значений КТУ, нажмите на пункт «Проверка данных» вкладки меню «Данные» и выберите пункт «Любое значение«.ексель

Аналогичным образом выделите все ячейки для ввода КТУ на странице «Ввод» (выделять придется с помощью клавиши Ctrl) и смените ограничение.

В организации, где я работал, максимальный КТУ мог равняться двум. Поэтому давайте настроим диапазон возможных значений КТУ от 0 до 2.

Сделаем ссылки на ячейки с КТУ со страницы «КТУ«. На этот раз автозаполнением нам удастся протянуть ссылки только по вертикали. Протягивание по горизонтали не учтет то, что нам нужно пропускать ссылки через колонку. Поэтому придется настраивать ссылки вручную.

В следующей статье мы приступим к настройке страницы расчета заработной платы.

Расчет заработной платы в Excel. II часть

Расчет заработной платы ExcelРасчет заработной платы в Excel. Часть II.

Продолжаем формировать таблицу расчета заработной платы для рабочих, получающих оклад и сделку.

В прошлой статье мы подготовили страницы учета рабочего времени, КТУ и выработки.

Теперь, на основании данных на этих страницах, нужно рассчитать необходимую сумму.

Страница расчета ЗП

После того, как все страницы настроены, остается добавить лист для расчета заработной платы. Так его и назовем — «РасчетЗП» Первые 5 колонок будут ссылками на страницу «Ввод» (№; ФИО; Профессия; Разряд; Бригада). Поэтому просто скопируем их с листа «Часы» или с листа «КТУ«.

Расчет оклада

Теперь нам необходимо рассчитать окладную часть заработной платы. Но, прежде чем рассчитать оклад, который привязан к отработанному времени, необходимо знать его размер в нормальных условиях. Для этого будет предназначена следующая колонка, которую мы назовем «Оклад«.

Оклад для каждого разряда будет свой. На листе «Списки» добавим список окладов, соответствующих тому или иному разряду (список которых у нас уже имеется) (см. рис.).

Теперь в колонке «Оклад» в ячейку F5 занесем формулу: =ВПР(D5;Разряд;2;ЛОЖЬ)

Функция ВПР возвращает значение, соответствующее выбранному из другого диапазона. То есть мы ссылаемся на значение в ячейке D5 (Разряд 1), сравниваем его с первой колонкой таблицы «Разряд» (если вы помните, то списку с разрядами мы дали, в свое время, наименование «Разряд«), указываем, что необходимо отобразить значение из второй колонки этой таблицы, которое соответствует нашей записи, и указываем на то, что совпадение должно быть точным.

Протягиваем формулу до нижней строки таблицы.

Добавив колонку в таблицу «Разряды«, мы немного нарушили условие на выбор из списка разрядов на странице «Ввод«. Выделяем все ячейки с разрядами, открываем пункт «Проверка данных» вкладки меню «Данные«. Теперь нам просто необходимо уточнить ссылку — ссылаемся не на всю таблицу, под названием «Разряд«, а еще и на ее колонку, тоже под названием «Разряд«. Наименования колонок в таблице, на которую ссылаемся, пишем в квадратных скобках (см. рис.)

Помимо количества отработанных за месяц часов, необходимо знать норматив — количество рабочих часов, установленных трудовым законодательством и указанных в производственном календаре. Чтобы пользователям не приходилось ежемесячно обращаться к производственному календарю, можно заносить количество нормативных рабочих часов на весь год. Сделаем это на листе «Списки» добавив колонку справа от названий месяцев.

Для расчета окладной части зарплаты, нужно положенный оклад разделить на положенное количество рабочих часов в текущем месяце (так мы определяем тарифную ставку часа в этом месяце), и умножить на отработанное количество часов.

Нормативное количество рабочих часов в текущем месяце предлагаю указать с помощью функции ВПР в ячейке справа от названия месяца (F1). Формула в ней будет такой: =ВПР(E1;Списки!A2:B13;2;ЛОЖЬ).

Количество отработанных часов, это сумма строк на странице «Часы«.

Назовем колонку «Оклад начисл.» Вот формула в ячейке G5:

=F5/$F$1*СУММ(Часы!F5:AJ5).

Протянем ее до нижней строки таблицы.

Расчет сделки

Приступаем к разработке формулы расчета сдельной части оплаты труда. Логика расчета такова:

  1. В зависимости от выполнения плана производства, определяется сумма общего котла.
  2. Доля каждого рабочего в этом котле определяется по сумме всех КТУ сотрудника за месяц.
    1. Сумма КТУ всех работников, это 100%.
    2. Доля КТУ работника, это сумма его КТУ за месяц, деленное на общую сумму всех КТУ и умноженное на 100%.
  3. Подобным образом определяем долю отработанного работником времени.
  4. Умножаем долю работника в котле на его же долю в общем количестве отработанных рабочими часов.
  5. Умножаем сумму котла на долю работника, определив, тем самым, сумму его сделки.

Для определения суммы котла руководство определило такое условие:

  • При выполнении плана менее чем на 50% сумма не выделяется;
  • При выполнении плана в диапазоне от 50% до 70% выделяется 1% от месячной суммы выработки;
  • При выполнении плана в диапазоне от 70% до 90% выделяется 2% от месячной суммы выработки;
  • При выполнении плана в диапазоне от 90% до 110% выделяется 3% от месячной суммы выработки;
  • При перевыполнении плана более чем на 110% выделяется 3,5% от месячной суммы выработки.

Заведем следующий список на странице «Списки» (см. рис.)

Назовем этот список «ДоляКотла«. Для определения процента, составляющего долю котла будем использовать функцию ВПР с указанием ИСТИНА (при указании аргумента ИСТИНА будет возвращаться значение, соответствующее ближайшему меньшему значению сравниваемого числа).

Формула расчета будет такой:

ВПР(СУММ(Планирование!$B$9:$AF$9)/Планирование!$B$1;ДоляКотла;2;ИСТИНА).

  • СУММ(Планирование!$B$9:$AF$9) — это сумма выработки за весь месяц;
  • Планирование!$B$1 — это план по выработке;
  • ДоляКотла — это таблица, на которую ссылается функция ВПР.

Теперь приступим к определению доли каждого в этой сумме. Как мы помним, расчет производится дважды — доля от общей суммы КТУ и доля от общего количества отработанного всеми времени. Предлагаю добавить отдельную колонку, которую мы назовем «Персональная доля«. Заносим формулу:

=СУММ(Часы!F5:AJ5)/СУММ(Часы!$F$5:$AJ$32)*СУММ(КТУ!F5:AJ5)/СУММ(КТУ!$F$5:$AJ$32)

Следующий шаг — расчет сделки. Перемножаем сумму выработки, долю котла и персональную долю сотрудника. Формула для сотрудника в верхней строке будет такой:

=Планирование!$B$3*ВПР(СУММ(Планирование!$B$9:$AF$9)/Планирование!$B$1;ДоляКотла;2;ИСТИНА)*H5

Теперь осталось просуммировать все составляющие в колонке «Итого ЗП«, оформить таблицу и все готово.

График смен. II часть

График смен. Часть II. В статье был выложен файл для управления рабочим временем при сменном характере работ.

Он позволяет планировать смены, вести учет отработанного времени сотрудников и рассчитывать итоговые данные за месяц.

Однако кому-то он может показаться неприемлемым, поскольку для своей работы использует код VBA, или, как многие называют, макрос.

График смен в Excel

Специально для них я создал другой файл, который макросы не использует. Однако, если прошлый инструмент работает в любой версии, то этот файл в версиях MS Excel ранее 2007 года не будет обладать полной функциональностью. Кроме того он потребует чуть больших манипуляций, чем тот, что использует макросы.

Как и в прежнем случае, начинаем с заполнения страницы «График». Здесь практически ничего не изменилось, кроме отображения праздничных, выходных и рабочих дней. Выходные и праздничные дни здесь также окрашены бледно-розовым и красным цветом, но не в самом графике, а в заголовке.

Рабочие дни, в соответствии с выбранным графиком, окрашиваются на графике оранжевым цветом. Продолжительность рабочих дней здесь автоматически не рассчитывается, ее необходимо вносить вручную. Также вручную вносятся отметки о прогулах, больничных и т.п.

Наименование месяца выбираем из списка. Год и подразделение вносим вручную. Наименования должностей редактируем на странице «Списке» в соответствующей таблице «Должность».

Здесь тоже изменилось мало, кроме того, что продолжительность работы не рассчитывается автоматически при клике на ячейке. Для этого необходимо внести в соответствующую ячейку какой-нибудь символ. Например букву «Я», как это принято в табельном учете. А продолжительность работы в смене считается в колонке «Прод-сть смены (ч)».

Наименование смены, время начала рабочего дня в смене, время окончания и продолжительность обеда вносятся в соответствующие ячейки вручную.

После настройки смен и выбора соответствующей смены для сотрудника на странице «График», оранжевым цветом закрасятся его рабочие дни. Теперь можно ежедневно вносить продолжительность его фактической работы, или проставлять коды, означающие причину отсутствия сотрудника на работе.

На странице «Отчет» таблица отобразит данные без необходимости нажимать особую кнопку. Однако это имеет и свои неудобства – в случае коррекции списка сотрудников, в частности, удаления строк в таблице на странице «График», формулы в этой таблице будут сбиваться, и их необходимо будет восстанавливать.

Список праздников здесь также можно корректировать, настраивая, тем самым, выделение  в графике цветом иных праздничных дней. Как и прежде, несколько выходных дней одного и того же праздника должны вноситься в отдельных строках.

Итак, файл, хоть и чуть менее удобный, чем прежний, все-таки, вполне пригоден для планирования и учета работы сотрудников по графику.  и пользуйтесь. При обнаружении ошибки или сбоя, прошу, — дайте знать в комментариях.

График смен. I часть

График смен в ExcelГрафик смен. Часть I.

Как показывает анализ посещений страниц этого сайта, читателей интересуют готовые файлы для офисной работы.

А описание порядка их разработки, и, уж тем более, разбор макросов спросом не пользуются.

Поэтому я решил ограничиться выкладыванием готовых решений Excel и описанием порядка их использования.

Думаю, так будет практичнее. А о том, как создавать такие файлы, можно пообщаться отдельно в комментариях.

А сейчас вернемся к инструментарию кадровиков. В я писал об учете рабочего времени. Точнее – о создании автозаполняемого табеля рабочего времени Т-12. Хотя там и предлагалась форма ввода данных об отработанном времени сотрудников, но она не позволяла вести, например, посменный учет. Для планирования графика смен и расчета отработанного времени в соответствии с выбранным графиком, лучше использовать файл, созданный специально для этих целей. О нем и речь. Как всегда, в конце статьи имеется ссылка на скачивание.

Итак, что умеет делать созданный мной файл? Сразу оговорюсь, что для его нормального функционирования потребуется включить макросы (похожий файл без макросов можно скачать ). В параметрах Excel есть пункт «Центр управления безопасностью».

Выбрав эту вкладку, нажимаем на кнопку «Параметры центра управления безопасностью», находим и выбираем пункт «Параметры макросов». Далее можете выбрать пункт «Отключить все макросы с уведомлением», если не желаете допускать работу макросов «без разрешения».

Теперь, в случае наличия макросов в файле, программа будет выводить уведомление и кнопку включения макросов в открываемом файле.

Начинать работу целесообразно с заполнения списка сотрудников. Заходим на лист «График» и выбираем месяц из списка. Год вносим вручную. Наименование подразделения также вносим вручную. Обратили внимание, что в заголовках таблицы графика появились числа того месяца, который был выбран? А если это текущий месяц, то число с сегодняшней датой окрашено в светло-зеленый цвет. Это первая, далеко не самая интересная возможность.

Затем вносим фамилии, имена и отчества работников в соответствующую колонку. При внесении ФИО сотрудника, автоматически добавляется строка и порядковый номер этой строки. В календарной части строки ячейки, соответствующие выходным дням, окрашиваются в бледно-розовый цвет. Если в месяце имеются праздничные дни, то ячейки, соответствующие им, окрасятся в красный цвет.

К вопросу о праздниках – государств и республик много. У каждого свои порядки. Поэтому в файле предусмотрена возможность коррекции списка праздников. Достаточно просто в колонку «А» внести наименование праздника, а в колонку «В» внести дату. Если к одному празднику относятся несколько дней (как в новогодние каникулы, например), то придется продублировать все эти дни отдельными строками. Интервалы дат таблица не поймет.

После ввода фамилии можно ввести наименование должности и выбрать смену на месяц для этого сотрудника. Но для выбора необходимо настроить списки. Список должностей вносится на листе«Списки». Зайдите на него и просто отредактируйте имеющийся список должностей в соответствующей таблице. А вот для настройки необходимых смен потребуется отдельная тема.

На листе «Смены» можно настроить любое количество смен. Для этого введите наименование смены в соответствующей колонке. К таблице автоматически добавится строка с соответствующим порядковым номером.

В колонки «Время начала» и «Время окончания» вносим, соответственно, время начала ежедневной работы в данной смене, и время ее окончания. Можно вводить значения от 0:00 до 23:59 только в формате времени (часы и минуты, разделенные двоеточием). При вводе любых других значений и в другом формате программа выдаст сообщение об ошибке и не даст внести данные.

Аналогичным образом внесите продолжительность обеденного перерыва. Здесь возможен ввод временного интервала от 0:00 до 2:00 часов (время перерыва, допускаемого законодательством).

Также, как и в предыдущей таблице, при создании новой строки, в календарном графике выходные и праздничные дни автоматически окрашиваются в бледно-розовый или красный цвета.

Переходим к заполнению дней, являющихся рабочими для настраиваемой смены. Для этого просто кликните на необходимой ячейке, и в ней автоматически отобразится количество часов, предусмотренных в этой смене (в зависимости от времени начала и окончания, и продолжительности обеденного перерыва, указанных ранее). Так, кликая по необходимым ячейкам, настраиваем режим выхода на работу в текущей смене. Если кликнули на ячейку ошибочно, то просто удалите появившееся значение с помощью клавиши Delete.

В колонке ИТОГО автоматически будет считаться общее количество часов, накопленных за месяц в настраиваемой смене. Это удобно при настройке, учитывая, что мы ориентируемся на нормативное количество рабочих часов в месяце, установленных законодательством.

После настройки всех смен, возвращаемся на страницу «График».

В колонке «Должность» предусмотрен выбор должности из списка. Нажимаем на кнопку с правой стороны ячейки, и выбираем из списка должностей, внесенных нами ранее на странице «Списки».

Аналогичным образом выбираем из списка наименований смен. Этот список формируется автоматически, по мере настройки смен. При выборе той или иной смены, график автоматически заполняется часами, настроенными нами ранее для отобранной смены.

Но если бы мы ограничились только лишь простановкой часов, соответствующих той или иной смене, то вряд ли бы инструмент был пригоден для управления. В течение месяца происходит многое, и график отработанного времени приходится редактировать. Для этого кликнете мышкой на ячейке, соответствующей сотруднику и корректируемому дню. Появится окно, позволяющее либо ввести количество часов, либо установить код, соответствующий причине отсутствия сотрудника на рабочем месте.

Поскольку табельный учет, в соответствии с дополнениями к трудовому законодательству, уже необязателен, то я не стал вносить весь список кодов из форм табельного учета. Нам они потребуются лишь для внутренней отчетности.

Формирование отчета

Отчет об отработанном времени формируется на странице «Отчет» при нажатии на кнопку «Сформировать отчет». Отобразится список сотрудников, количество отработанных ими часов в течение месяца, количество дней отсутствия по той или иной причине. Этот отчет можно использовать для последующей кадровой отчетности.

Так работает созданный файл. Разумеется, его можно продолжать дорабатывать под специфику организации. Например, в некоторых копаниях, время начала и окончания работы в разные дни в одной смене могут отличаться. В данной таблице это не предусмотрено, но, при желании — можно настроить.

Я постарался предусмотреть возможные ошибки, и предотвратить их появление. Но, возможно, в процессе эксплуатации вы, все-таки, их обнаружите. Очень вас прошу – напишите об этом в комментариях к статье. Тем самым, вы поможете мне их исправить и опубликовать более совершенную версию.

И, как обещал, выкладываю  на скачивание созданного файла.