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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Учет заявок на персонал в Excel. III часть

Учет заявок на персонал в ExcelУчет заявок на персонал в Excel. Часть III.

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

Сегодня займемся, собственно, этой обработкой.

Давайте подумаем, какие отчеты по заявкам на персонал нас могут заинтересовать?

Это могут быть:

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

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

Доработка исходной таблицы

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

Пишем «Срок подбора персонала:», а справа вносим значение, например, 10. Неплохо было бы уточнить – чего именно 10. Входим через контекстное меню в формат ячеек, и в закладке «Число» выбираем пункт «Все форматы». Затем в поле «Тип» вносим следующие символы: #* «раб. дней». Для удобства дадим имя ячейке – «СрокПодбора»

Следующий шаг – подсчет количества дней между датами написания заявки и предполагаемой датой выхода сотрудника. Считаем только рабочие дни, поэтому используем функцию «ЧИСТРАБДНИ». Для учета праздничных дней предлагаю создать отдельную страницу «Праздники», и туда занести все праздничные дни в году. Этот список назовем аналогично – «Праздники». Формула будет следующей:

=ЧИСТРАБДНИ([@[Дата заявки]];[@[Дата открытия вакансии]]; Праздники)

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

Теперь создадим еще одну колонку (назовем ее «Отклонение от нормы«), в которой будет появляться сигнал о том, что срок слишком мал. Воспользуемся функцией «ЕСЛИ»:

=ЕСЛИ([@[Срок на подбор]]<СрокПодбора;1; «»)

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

Формирование сводных таблиц

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

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

В область сводной таблицы «Названия строк» вносим поле «Название должности». В область «Названия столбцов» вносим «Дата заявки». Чтобы сгруппировать даты по месяцам, устанавливаем курсор в наименование колонки (там, где указана дата) и вызываем контекстное меню. Здесь выбираем пункт «Группировать» и метод группировки – «месяц» и «квартал».

В область значений заводим поле «Необходимое количество сотрудников» и настраиваем его как сумма значений.

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

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

В область значений заводим поле «Отклонение от нормы» и настраиваем его как сумма значений.

График нам здесь не нужен, впрочем, можете его сформировать по своему усмотрению.

И последняя сводная таблица – средние значения по зарплате. Этот лист я назвал «СредняяЗП». В строках у нас будут наименования должности, в колонках я ничего не указал. Впрочем, можно установить даты, сгруппированные поквартально.

В области значений здесь будет поле «Размер ЗП», а настроим его не как сумма, а как средние значения.

Структура хранения файлов

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

Для всей системы файлов службы создайте отдельную папку. Пусть она будет называться HR. Ее разместите на своем компьютере либо в сетевой папке, но с доступом только сотрудников службы персонала.

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

Для файлов, в которых будут храниться данные, предусмотрим папку «Базы».

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

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

Учет заявок на персонал в Excel. II часть

Учет заявок на персонал в ExcelУчет заявок на персонал в Excel. Часть II.

Продолжаем работу.

В прошлой статье описан порядок создания шаблона заявки на подбор персонала.

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

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

В этом случае нам никак не обойтись без создания макроса (подробнее о работе с макросами здесь).

Поэтому создаем новый файл, и сохраняем его в формате с поддержкой макросов.Назовем его «База вакансий«. Лист с таблицей назовем «База»

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

Открываем закладку меню «Разработчик» и жмем пункт Visual Basic. В открывшемся приложении для написания кода VBA.

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

Для начала, необходимо продумать порядок работы макроса. Я предлагаю такой:

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

Копирование данных из файла по выбору

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

  • для получения пути к файлу (тип String (строка));
  • для определения диапазона копирования (тип String (строка));
  • для указания данных (тип Variant, т.е. — любой. Этот тип можно не указывать).

Чтобы долго не расписывать, выкладываю текст макроса со своими комментариями:

Внесение порядкового номера новой строки

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

Это сделать очень просто, если воспользоваться комбинацией клавиш для выделения заполненных ячеек таблицы (Ctrl-Shift-стрелка, указывающая направление выделения от активной ячейки).

Используем макрорекордер — запускаем запись макроса, жмем Ctrl-Shift-стрелка вниз, останавливаем запись макроса. Записанную команду используем для макроса «Заполнение_базы_заявок«.

Нам потребуется переменная для счетчика цикла. Объявляем ее строкой Dim i As Integer. Затем используем команды в следующей последовательности:

  1. активируем лист «База«;
  2. устанавливаем курсор на ячейку «А2» (для определения верхней границы выделяемого диапазона);
  3. выделяем диапазон с заполненными ячейками;
  4. если первая строка таблицы не заполнена, то в ячейку «А3» ставим цифру 1, а переменной «i» присваиваем значение 2. Это необходимо, так как цикл в этом случае не запускается, и счетчик будет равен нулю, а на следующих шагах он будет использован;
  5. если первая строка таблицы заполнена, то запускаем цикл, проставляющий порядковые номера в выделенном диапазоне;
  6. Устанавливаем значение переменной «i».

Текст макроса следующий:

Вставка новых данных

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

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

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

Теперь создадим кнопку для запуска макроса. Во вкладке меню «Разработчик» имеется кнопка «Вставить«. Жмем ее и выбираем значок «Кнопка управления формы«. В строке над таблицей «рисуем» кнопку и привязываем ее к нашему макросу. Также изменяем наименование кнопки.

На этом все. Файл с текстом макроса во вложении.

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

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

Учет заявок на персонал в Excel. I часть

Учет заявок на персонал в ExcelУчет заявок на персонал в Excel. Часть I.

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

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

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

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

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

Упрощенно, поиск и подбор нового сотрудника проходит следующие стадии:

  1. обработка заявки на вакансию;
  2. выбор каналов поиска (продвинутые планируют бюджет);
  3. публикация объявлений или взаимодействие с кадровыми агентствами;
  4. организация и проведение собеседований с кандидатами;
  5. принятие решения по кандидатам и переход на стадию оформления сотрудника.

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

  • выбираем в главном меню «Сохранить как» и в выпавшем окне кликаем на пункте «Другие форматы«;
  • в открывшемся окне находим пункт «Тип файла» и выбираем из списка «Шаблон Excel 97-2003» (с новым форматом шаблонов у меня возникали неполадки, поэтому я выбрал старый формат. Для наших целей это не принципиально);
  • выбираем папку для размещения сохраняемого шаблона.

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

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

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

Создание формы заявки

Возвращаемся на страницу «Заявка» и заносим необходимые наименования ячеек. В моей практике использовалась следующая информация:

  1. Заявитель — ФИО того, кто заполняет заявку;
  2. Дата заявки;
  3. Название должности — в этой ячейке будем выбирать из списка
  4. Подразделение — тоже выбираем из списка;
  5. Непосредственно будет подчиняться (должность и ФИО);
  6. Чем вызвана потребность новом сотруднике — это может быть увольнение прежнего сотрудника или открытие новой штатной единицы, выбираем из списка;
  7. Функции сотрудника — здесь нежелательно предоставлять возможность ввода функционала, поскольку перечень обязанностей должен соответствовать должности. При выборе должности все функции должны отображаться автоматически. Поэтому используем функцию «ВПР«, которая на основании выбранного наименования должности вытянет его функционал из таблицы «Должность«. Формула здесь будет такой: =ЕСЛИ(ЕОШИБКА(ВПР(B3;Должность;2;ЛОЖЬ));»»;ВПР(B3;Должность;2;ЛОЖЬ));
  8. Схема начисления з/п;
  9. Общий размер начисленной з/п — установим защиту на ввод только числовых значений;
  10. Оплата на период испытательного срока — тоже устанавливаем ввод только числовых значений;
  11. Дата открытия вакансии (предполагаемый выход сотрудника) — устанавливаем возможность ввода таты, начиная от текущей;
  12. Необходимое количество сотрудников — ввод только чисел;
  13. Где предполагается размещение сотрудника? — сюда вводится информация о кабинете, в котором будет работать сотрудник .

Форма подготовки рабочего места

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

Сюда входят следующие пункты:

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

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

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

Файл с шаблоном можете скачать отсюда.