Ежедневный мониторинг выполнения плана в 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

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

Случайное распределение с заданной суммой. I часть

Случайное распределение с заданной суммой ексельСлучайное распределение с заданной суммой. Часть I.

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

Не буду уточнять, для чего это необходимо, вариантов может быть масса.

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

Итак — условия задачи:

  1. Есть некая сумма;
  2. Есть календарь, где каждый день разбит на часы (см.рис.)
  3. Эту сумму необходимо распределить по дням и часам с определенными ограничениями:
    1. В ночное время значения должны составлять 70% от дневных;
    2. Вариации должны быть в интервале 5-10% от средних значений;
    3. В праздничные дни значения также должны составлять 70% от дневных значений в обычные дни.

Для решения я использовал две ключевые функции: СЛЧИС и НОРМОБР. Первая функция генерирует случайное число в диапазоне от 0 до 1. Вторая — формирует значения в соответствии с нормальным распределением (если кто не знаком с этим термином, прошу в википедию, а в двух словах поясню, что множество случайных замеров какого-либо явления подчиняются как раз закону нормального распределения).

Создание вводной страницы

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

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

Закладываем все это на странице «Ввод» (см.рисунок выше).

Настройка формы основной таблицы

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

Формула первого числа месяца будет преобразовывать введенные на странице «Ввод» текстовые значения в дату: =ДАТАЗНАЧ(1&Ввод!B2&Ввод!B1)

Следующие числа будут образованы простым прибавлением единицы к предыдущей дате. А формула, соответствующая 29-му числу будет следующей: =ЕСЛИ(МЕСЯЦ(N34)=МЕСЯЦ(N34+1);N34+1;»»).

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

Настройка условий распределения

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

Распределение долей

Запрограммировать условие, при котором в ночные часы должно быть одно значение, а в дневные — другое, не проблема. Используем функцию ЕСЛИ. В случае, когда необходимо заложить два условия, используем функцию ИЛИ: =ЕСЛИ(ИЛИ($A4<Ночь1;$A4>=Ночь2);ДоляНочь; ДоляДень)

Перевод функции на русский примерно такой:

  • Если одно из двух условий верно:
    • значение меньше значения в ячейке «Ночь1» или…
    • значение больше или равно значения в ячейке «Ночь2«
  • то используем значение в ячейке «ДоляНочь«, в противном случае…
  • используем значение в ячейке «ДоляДень«.

Однако нам необходимо использовать еще одно условие — доля в праздничные дни. Здесь решение не такое тривиальное. Используем функцию ПОИСКПОЗ, которая проверяет, соответствует ли выбранное значение какому-либо значению из списка.

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

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

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B$3;Праздник;0));ДоляДень;ДоляПраздник)

Перевод на русский:

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

Теперь, напрягая остатки интеллекта (у меня шел дым из ушей), объединяем эти две части формулы в одну:

=ЕСЛИ(ИЛИ($A4<Ночь1;$A4>=Ночь2);ДоляНочь;ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B$3;Праздник;0));ДоляДень;ДоляПраздник))

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

На этом я прерываюсь. Продолжим в следующей статье.

Случайное распределение с заданной суммой. II часть

Случайное распределение с заданной суммой ексельСлучайное распределение с заданной суммой. Часть II.

В прошлой статье мы определили условия распределения, и распределили доли.

Здесь я опишу завершение работы и в конце текста выложу файл со всеми расчетами.

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

Распределение средних по долям

Теперь нам необходимо распределить значения в соответствии с этими долями. Здесь все чрезвычайно просто:

  • просуммируем все доли и назовем ячейку с этой суммой «СумДоли«;
  • создадим еще одну таблицу для промежуточных значений (я ее назвал «Распределение средних по долям);
  • в ячейке новой таблицы, соответствующей первому числу месяца запишем формулу: =B4/СумДоли*Сумм
    В4 — это значение, соответствующее первому числу месяца из первой таблицы, «Сумм» — значение в ячейке с суммой, которую необходимо «раскидать»;
  • скопируем формулу в прочие ячейки таблицы.

Случайное распределение

Мы определили своеобразный каркас нашей модели. Осталась самая малость — обеспечить случайное распределение в рамках тех ограничений, которые были заданы. Мы обеспечили соблюдение почти всех правил, кроме одного — значения должны варьироваться в пределах установленного диапазона вариации (мы определили 10%). Для этого создадим еще одну таблицу («Распределение корректив«), которая будет устанавливать корректирующие значения в пределах диапазона, установленного нами ранее (процент вариации, если помните).

Для случайного распределения по нормальному принципу используем две функции:

  • НОРМОБР преобразует число в единичное наблюдение кривой нормального распределения;
  • СЛЧИС обеспечит случайное число.

Для функции НОРМОБР необходимо три переменных:

  1. случайное число в интервале от 0 до 1, которое мы обеспечиваем функцией СЛЧИС;
  2. среднее значение. Поскольку среднее значение у нас зависит от указанных ранее чисел, нам необходимо оперировать долями, которые мы устанавливали в ячейке «Процент вариации» и числами, которые мы рассчитали в таблице распределения средних значений. Формула здесь будет такая — =B63-B63*(1-Вариация/2). Суть ее в том, что мы определяем среднее значение отклонения в большую или меньшую сторону, которое должно равняться половине значения вариации, которую мы установили в ячейке «Процент вариации» (уж не знаю, понятно ли я объяснил суть формулы);
  3. стандартное отклонение. Не буду вдаваться в подробности статистических функций, скажу лишь, что это разброс большинства значений в нашей выборке от среднего значения. Чем меньшее число мы установим, тем больше значений в выборке будет стремиться к среднему. Чтобы приблизить кривую распределения к нормальной, я предлагаю установить стандартное отклонение как пятую часть вариации. Формула такова: =B63-B63*(1-Вариация/5)

Есть одна проблемка — мы не сможем настроить сложение или вычитание по случайному принципу. Поэтому нам необходимо обеспечить вариацию от нуля в сторону положительных или отрицательных чисел. А затем уже можно прибавить к среднему значению положительное или отрицательное число. Поэтому из преобразованного с помощью функции НОРМОБР числа мы вычтем то среднее значение, которое было в этой формуле использовали: =B63-B63*(1-Вариация/2). Таким образом вся формула, соответствующая первому числу месяца, будет выглядеть так:
=НОРМОБР(СЛЧИС();B63-B63*(1-Вариация/2); B63-B63*(1-Вариация/5))-(B63-B63*(1-Вариация/2))

Скопируем ее на все ячейки.

Настройка основной таблицы

Пора переходить к нашей основной таблице. Мы вычислили все промежуточные значения, и осталось из средней, вычисленной в таблице «Распределение средних по долям» вычесть отклонение, вычисленное в таблице «Распределение корректив«. Формула, соответствующая первому числу месяца, будет такой: =Коррективы!B63+Коррективы!B120

Казалось бы — и все. Однако остается проблема. Если мы просуммируем все, вычисленные таким образом, значения, они не дадут идеального совпадения с той суммой, которая нами установлена изначально. Можно было бы установить какую-нибудь одну корректирующую ячейку, значение в которой сформировалось бы путем вычитания из общей суммы, суммы всех ячеек, кроме последней. Но, в этому случае, отклонение может стать слишком большим. Поэтому я сделал 12 корректирующих ячеек, в диапазоне N43:N54.

Коррекция для обеспечения конечной суммы

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

  1. определяем шкалу, которая будет обеспечивать доли разброса. Эта шкала должна в сумме давать 12, чтобы сумма соответствовала двенадцати ячейкам. Ее я настроил рядом с таблицей распределения корректив, в диапазоне R156:R167;
  2. производим расчет значений путем перемножения среднего значения на долю (диапазон S156:S167);
  3. добавим шкалу, которая будет определять порядок вывода значений в таблице (диапазон Т156:Т167);
  4. в основной таблице, в ячейках диапазона N43:N54 используем функцию ВЫБОР, которая вытягивает значение из таблицы на основании номера, указанного нами в шкале, созданной на предыдущем шаге. Формула первого значения в этом диапазоне будет такой: =ВЫБОР(Коррективы!T156;Коррективы!$S$156;Коррективы!$S$157;Коррективы!$S$158;Коррективы!$S$159;Коррективы!$S$160;Коррективы!$S$161;Коррективы!$S$162;Коррективы!$S$163;Коррективы!$S$164;Коррективы!$S$165;Коррективы!$S$166;Коррективы!$S$167)

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

А у меня — все. Во вложении файл со всеми расчетами.

Оценка 360 градусов в Excel

Оценка 360 градусов в Excel
Оценка 360 градусов в Excel

Многим сотрудникам HR-служб не в диковинку система оценки 360 градусов.

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

Нередко такую оценку используют при аттестации сотрудников.

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

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

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

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

Создание бланка

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

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

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

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

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

Возвращаемся на лист «Компетенции и жмем кнопку «Обновить бланк». Оценка 360 градусов в Excel

На листе «Бланк» будет сформировано необходимое количество блоков по количеству компетенций.

Настройка бланка

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

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

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

Остается исключить лишние листы. Сделать это можно двумя способами – либо скрыть листы, либо переместить лист «Бланк» с копированием в новый файл. Одно условие – наименование листа с бланком должно быть именно «Бланк», и никак иначе.

Импорт бланков и обработка результатов

Оценка в Excel 360 градусов

Для сбора и обработки данных необходимо открыть файл «Обработка данных 360», и нажать на кнопку «Импортировать данные из бланка», расположенную на листе «Таблица». Откроется диалоговое окно для выбора файла с бланком, а, после выбора файла, новое окно запросит ФИО эксперта и информацию о том, кем эксперт является по отношению к оцениваемому – руководителем, коллегой или подчиненным. Если эксперт оценивает сам себя, то выбираем вариант «Самооценка».

В самую последнюю очередь появится окно с вопросом: «Будем обновлять список менеджеров?«. Если вы заранее составили полный список оцениваемых сотрудников, то нажмите «Да» только при загрузке самого первого бланка. В дальнейшем, можете нажимать «Да» только в случаях, когда список оцениваемых будет дополнен.

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

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

  • Перекрестная – в строках расположены ФИО оцениваемых сотрудников, а в колонках – ФИО экспертов. Это вспомогательная таблица. На пересечении фамилий можете ставить любые отметки.
  • KPI – помимо экспертной оценки, неплохо бы использовать и показатели результатов деятельности. Внесите процент выполнения показателей каждым оцениваемым сотрудником, и эти проценты преобразуются в баллы в соответствии с расположенной ниже таблицей. Интервалы в таблице можно менять. Главное – не менять расположение от меньшего к большему.
  • Подсчет результатов – здесь размещаются блоки с расчетными баллами по каждому оцениваемому. Осмысленные значения будут появляться только при наличии оценок, как минимум, Руководителя и самооценки.
  • Рейтинг – итоговые расчеты с рейтингом.
  • Веса — страница, на которой можно настроить вес той или иной оценки при расчете итогового балла. Как видим, если у сотрудника есть только оценка руководителя и самооценка, то при расчете итоговой оценки вес баллов будет распределяться по варианту 3. При наличии оценок руководителя, подчиненного и самооценки — по варианту 2. При наличии оценок всех экспертов — по варианту 1. Если вы хотите исключить участие оценок KPI в рейтинге, то установите этому виду оценок вес 0, а компетенциям вес 100%.

Конструктор бланков скачивайте , а обработчик .

P. S: надеюсь, вы помните, что для работы макросов, их необходимо подключить. Тем, кто не знает, как это делать, ознакомьтесь со .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Учет затрат на персонал в программе Excel

Учет затрат на персонал в программе ExcelУчет затрат на персонал в Excel.

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

Сегодня мы поработаем над учетом затрат.

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

Значит — создаем единый файл на все расходы, связанные с персоналом.

Создание таблицы

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

  • — порядковый номер записи;
  • Дата — дата внесения записи;
  • Тип контрагента — это могут быть СМИ, кадровые агентства, рекламные агентства и т.п. Это поле необходимо для облегчения поиска и группировки контрагентов;
  • Наименование контрагента — собственно, наименовании организации, с которой осуществлялся расчет;
  • Статья затрат — здесь, думаю, объяснять не надо;
  • Предмет оплаты — что именно приобретаем или заказываем;
  • Количество;
  • Стоимость единицы;
  • Сумма.

Вносим эти наименования колонок, выделяем диапазон и вставляем таблицу (см.рис.). Дадим этому листу наименование «Затраты». Теперь займемся настройкой ввода данных.

Настройка ввода данных

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

Аналогичным образом обеспечиваем ввод данных в колонке «Дата«. Только вместо «Целое число» выбираем «Дата«. В качестве начальной даты предлагаю установить первое января текущего года.

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

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

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

В колонке «Количество«, как и в колонке «» установим ограничение на внесение только целых чисел больше 0.

В колонке «Стоимость единицы» у нас будут действительные значения больше 0. А формат настроим в рублях.

В колонке «Сумма» у нас будет произведение значений колонок «Количество» и «Стоимость единицы«. Здесь тоже установим денежный формат.

Создание связанных списков

Для начала создадим лист, на котором будем вести все списки. Так и назовем этот лист — «Списки«.

Теперь вставим туда таблицу следующего вида:

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

Выделяем всю строку с наименованиями типов контрагентов (включая колонку ««) и дадим ему имя «Тип«.

Во вкладке меню «Работа с таблицами» (она появляется в верхней строке меню, если табличный курсор установить в пределах таблицы) ставим галочку напротив пункта «Строка итогов«. В итогах колонки «» выбираем «Количество«. Ячейке с подсчитанным количеством чисел в колонке «» даем имя «СчетКонтрагенты«. Это значение будет задавать высоту списка в основной таблице.

Затем нам необходимо ячейке заголовка первой колонки (в моем случае, это колонка «Агентство«) дать имя «Старт«. От нее будет вестись отсчет смещения в формуле.

Теперь переходим на страницу «Затраты«. Выделяем колонку «Тип контрагента» и жмем на пункт «Проверка данных» во вкладке меню «Данные«. В поле «Тип данных» выбираем пункт «Список«, а в поле «Источник» пишем формулу =Тип. Этот выпадающий список настроен.

Выделяем колонку «Наименование контрагента«, также выбираем тип данных — список, а в поле «Источник» заносим следующую формулу: =СМЕЩ(Старт;1;ПОИСКПОЗ(C2;Тип;0)-2;СчетКонтрагенты;1). Суть формулы в том, что она формирует список на основании координат в таблице «Тип«, соответствующих значению в колонке «Тип контрагента«.

Таблица готова. Можно начинать вводить данные.

Настройка отчета

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

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

Последний штрих — вставка графика. Убедимся, что табличный курсор в пределах сводной таблицы, заходим во вкладку меню «Вставка» и выбираем понравившийся вид графика (я выбрал гистограмму).

База учета затрат готова, можно пользоваться.