Microsoft Excel не заменит вам основную систему управления персоналом или продвинутую систему анализа персонала, но в качестве инструмента ежедневного отслеживания и специальной отчетности для HR-отдела его трудно превзойти. Использование формул и функций в Excel для ответов на вопросы и принятия решений поможет вам стать профессионалом в области управления персоналом, опирающимся на данные. Это также поможет вам понять, что говорят вам встроенные инструменты аналитики.
Использовать Excel проще, быстрее и дешевле, чем полагаться на ИТ-специалистов при подготовке отчетов, и, скорее всего, в вашей HR-команде есть один или несколько человек, которые хотя бы немного разбираются в этом.
Однако навыки большинства пользователей не выходят за рамки простого отслеживания и использования отчетов, созданных другими. Роль “мастера Excel” в отделе кадров может положительно повлиять на вашу трудоспособность и доход.
Обширная документация Microsoft написана для обычных пользователей. Прекрасные учебные ресурсы и десятки отличных книг облегчают обучение, а для нас обучение никогда не прекращается.
Исходя из этого, вот несколько из наиболее распространенных формул, инструментов и функций Excel для работы с персоналом.
Содержание
Работа с датами
Начнем с некоторых часто используемых функций для работы с датами.
Excel хранит даты как последовательные порядковые номера, начинающиеся с 1, начиная с 1 января 1900 года в 00:00 утра. 16 марта 2021 года – это 44271, потому что это 44 271-й день с начала времени Excel.
Значения меньше дня хранятся в виде десятичных дробей.
Excel предлагает нам множество способов хранения, сравнения и вычисления дат и времени. Существует шестнадцать стандартных форматов для дат, а пользовательские правила форматирования дают еще больше возможностей для управления отображением. Чтобы выбрать формат, щелкните правой кнопкой мыши на ячейке или списке дат и выберите один из форматов дат или выберите пользовательские форматы, чтобы увидеть еще больше.
Прежде чем мы начнем вычислять даты, давайте посмотрим, как мы можем ввести сегодняшнюю дату – даже если мы не знаем, что это такое.
Использование функции СЕГОДНЯ()
Функция СЕГОДНЯ – это экономия времени. Она получает сегодняшнюю дату и время с вашего компьютера, поэтому обновляется автоматически. Ваши рабочие листы автоматически обновляются, когда вы их открываете или изменяете.
Эта функция удобна для вычисления таких параметров, как возраст, время работы или в любом другом случае, когда используемый период не имеет определенного конца. Например, если вы хотите рассчитать возраст сотрудника, вы можете вычесть дату его рождения из сегодняшнего дня. В Excel это можно реализовать через функцию РАЗНДАТ().
В первый аргумент подставляем дату рождения, во второй аргумент – сегодняшнюю дату (то есть функцию СЕГОДНЯ), в третьем аргументе могут быть разные значения, в зависимости от того какое значение нужно вернуть. Ниже таблица с перечнем возвращаемых значений.
| “d” | разница в днях |
| “m” | разница в полных месяцах |
| “y” | разница в полных годах |
| “ym” | разница в полных месяцах без учета лет |
| “md” | разница в днях без учета месяцев и лет ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение, если день начальной даты больше дня конечной даты (например, в EXCEL 2007 при сравнении дат 28.02.2009 и 01.03.2009 результат будет 4 дня, а не 1 день). Избегайте использования функции с этим аргументом. Альтернативная формула приведена ниже. |
| “yd” | разница в днях без учета лет ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение. Избегайте использования функции с этим аргументом. |
Функция ЧИСТРАБДНИ (NETWORKDAYS)
ЧИСТРАБДНИ – это удобная функция для управления проектами, расписаниями или в любое время, когда вам нужно рассчитать промежуток рабочих дней, включающий праздники.
=ЧИСТРАБДНИ (начальная дата; конечная дата; праздники)
Для простого внесения праздников в формулу введем все даты праздников по производственному календарю. В 2022 году список будет следующий.
Затем создадим Имя в Диспетчере имен, выбрав диапазон дат.
Теперь напишем формулу для расчета количества рабочих дней между двумя датами.
Форматирование таблиц
Прежде чем мы начнем работать с формулами поиска, давайте обсудим, как форматировать таблицы сотрудников, чтобы ваши функции и процедуры работали последовательно.
Каждая таблица должна иметь первичный ключ. В языке структурированных запросов (SQL) столбец или набор столбцов – это уникальный идентификатор. В Excel мы хотим, чтобы первичным ключом был один столбец, который идентифицирует каждую строку данных.
Если вы работаете с информацией о сотрудниках, ключом почти всегда является идентификатор сотрудника. Если поместить его в первый (крайний левый) столбец каждого рабочего листа, в котором перечислены отдельные сотрудники, то функции ВПР, ИНДЕКС, ПОИСКПОЗ и ИНДЕКС-ПОИСКПОЗ станут намного проще в использовании.
Создание “умной” таблицы
Нам нравится давать имена нашим таблицам и массивам, чтобы ими было легче пользоваться. Быстрый способ сделать это – использовать функцию “Таблица” на вкладке “Вставка.
Убедитесь, что ваша таблица является непрерывной, то есть в ней нет пробелов между столбцами и строками.
Нажмите на любую ячейку с данными в таблице и воспользуйтесь сочетанием клавиш Ctrl+T.
Переименуем нашу таблицу, чтобы в будущем к ней было удобнее обращаться.
Добавим столбец с уникальным ключом.
Фильтрация данных
Если вы следите за работой в Excel, вы, вероятно, заметили, что в ваших таблицах автоматически устанавливаются фильтры. Фильтры позволяют экономить время, когда нужно нарезать данные на кусочки. Они также могут быть полезны, когда вы хотите проверить данные в таблице.
К примеру, нам нужно посмотреть только тех сотрудников, которые были трудоустроены после 1 января 2020 года. Для этого воспользуемся фильтром.
Строка итогов
Строка итогов – это дополнительная строка в таблице данных, которая позволяет при помощи статистической функции обрабатывать числовые данные таблицы. Располагается внизу таблицы.
Добавим строку итогов в таблицу, чтобы показать средний возраст сотрудников и средний срок работы в месяцах
Работа со сводными таблицами
Необходимо предоставить данные о среднем сроке работы в компании по отделам в месяцах и средний возраст сотрудников в разрезе отделов. Для решения этой задачи использование сводных таблиц будет очень кстати.
Подготовка данных
Для начала добавим расчетные столбцы в таблицу, которые бы рассчитывали срок работы в компании и возраст по сотрудникам через функцию РАЗНДАТ.
Создание сводной таблицы
Для создания сводной таблицы на вкладке “Вставка” нажмем по полю “Сводная таблица”. В качестве таблицы выберем нашу таблицу “СписокСотрудников”.
В первой таблице покажем средний возраст сотрудников. Для этого переместим в поле “Строки” отдел, а в “Значения” – возраст.
Так как нам нужна не сумма всех возрастов, а среднее значение нажмем по стрелочке рядом в поле “Сумма по полю Возраст” и выберем “Параметры полей значений” и выберем в списке “Среднее”.
И в качестве последнего штриха – отредактируем страницу.
По аналогии создаем вторую сводную таблицу и в итоге у нас получается следующее.
В качестве заключения
Надеюсь, что этот краткий обзор помог вам немного разобраться с такими элементами Excel, как сводные и “умные” таблицы, а также с парочкой формул. Учитесь на практике и применяйте эти формулы и функции в своей работе.
Оставляйте свои комментарии с вопросами и предложениями.
