Как гибко и эффективно решать любые задачи, связанные с расходами на персонал

Вначале создадим базу данных на сотрудников. Предположим, что сотрудники получают каждый период зарплату и каждые n периодов – бонус. Сотрудники также различаются по подразделению, типу  

1. Создадим новый отдельный лист, пометим его цветом, установленным для переменных, и выберем ему уникальное описательное имя, пригодное для использования в формулах – например, pers_db.
2. Разместим в строке 1 заголовок «Персонал»
3. Разместим в строке 2 заголовки полей базы данных, также в виде, пригодном для использования в качестве переменных: 
• ID – порядковый номер записи
• Position – название должности
• Division – название подразделения
• Type – название типа сотрудника
• Salary – оклад за период
• Start_period – период, в котором сотрудник поступает на работу
• Bonus – сумма премиальных, или бонусов (предположим, что бонусы выплачиваются за факт найма и затем каждые n периодов) 
• Bonus_period – число периодов, после которых сотруднику положен очередной бонус
4. Внесем в таблицу данные по принципу один сотрудник – одна строка-запись.
5. Посредством команды Insert->Create Name (в Excel 2007 и 2010 кнопка Create from Selection) создадим именованные массивы из каждой колонки базы. При создании команда включит заголовок в будущий именованный массив, но во избежание глюков при работе с данными массив надо вручную сократить на ряд, содержащий заголовки (2). Первым рядом массивов должен всюду быть ряд 3. Проверить адреса массивов можно в менеджере имен. 
6. Присвоим всей базе данных созданный ранее визуальный стиль «переменная». 

Обратите внимание, что в наших примерах мы используем искусственные значение зарплат и бонусов, разнесенные по «уровням» сотрудников, вида 1^(номер «уровня»-1). Это сделано в целях оперативного аудита модели – чтобы по положению единиц и нулей контролировать корректность сумм на глазок по мере того, как мы пишем формулы, агрегирующие данные таблицы. Например, сумма 1010 будет означать, что в сумму входят: 1 запись со значением 1000 (уровень 4); 0 записей со значением 100 (уровень 3); 1 запись со значением 10 (уровень 2); 0 записей со значением 1 (уровень 1). Если бы у нас было больше одного сотрудника каждого «уровня», то мы бы еще могли определить число одинаковых обнаруженных записей – например, 2010 означало бы, что число записей уровня 4 равно 2. Когда мы будем уверены, что формулы  составлены безошибочно, в базу можно внести актуальные значения зарплат и бонусов. 

Результатом этой работы должна стать таблица следующего вида:

 

Простейшим способом обработать базу данных в Excel является создание Pivot table (сводной таблицы). Но извлечь из нее данные прямым запросом нельзя, так как при каждом изменении число ее рядов и колонок изменяется, и модель будет развалена. Для этого необходимо использовать функцию GETPIVOTDATA, которая может работать лишь с уже сформированной таблицей. Чтобы обработать несколько разных запросов, одной таблицы может оказаться недостаточно, а более сложные по критериям запросы таблица вообще не сможет обработать – ее встроенные фильтры недостаточно гибки. Поэтому разберем, как составлять запросы вручную, используя только стандартные формулы Excel. 

Для запросов к базе создадим новый лист модели, на котором оформим следующую сетку. Колонки, помеченные стилем «переменная», будут использованы как переменные особого типа, – в них мы будем указывать массивы, из которых мы выбираем данные, и критерии, по которым мы эти данные фильтруем. Все примеры формул будут находиться в колонке G, соответствующей периоду 1. 

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

 

Запрос 1. Каковы ежемесячные выплаты зарплаты? 

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

– Основной массив – Salary (C3)

– Критерий 1 – Start_period (D3)

Мы используем функцию условной суммы SUMIF. Это простейшая формула, доступная в любой версии Excel. 

=SUMIF($D3,"<=G$2",$C3)

Однако, если мы введем эту формулу, окажется, что она возвращает нулевые значения. Дело в том, что Excel не воспринимает в формулах переданные извне текстовые строки ни как имена массивов, ни как адреса ячеек. Поэтому нам понадобится транслировать переменные в форму, доступную Excel. Только в этом случае мы сможем, заменяя названия переменных в колонках С-F, управлять всем рядом формул сразу. 

Для преобразования имени массива в адрес массива используется функция INDIRECT

INDIRECT(C3) -> 'pers_db'!E3:E6

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

"<="&F$2

Корректно оформленная формула SUMIF будет выглядеть так: 

=SUMIF(INDIRECT($D4),"<="&G$2,INDIRECT($C4))

=SUMIF(	Условная сумма.
INDIRECT($D4),	Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D4 (Start_period).  Колонка закреплена жестко (знак $) для последующего копирования.
"<="&G$2,	Если сотрудник уже работает в компании, поле start_period его записи меньше или равно значению текущего периода (ячейка G2).  Ряд закреплен жестко (знак $) для последующего копирования.
INDIRECT($C4)	Сложить отвечающие критерию значения из массива, имя которого вписано в клетку С4 (Salary).  Колонка закреплена жестко (знак $) для последующего копирования.
)	Формула завершена

Запрос 2. Какой бонус за переход выплачивается ежемесячно?

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

– Основной массив – Bonus (C5)

– Критерий 1 – Start_period (D5)

Первое побуждение аналитика ограничиться заменой критерия Salary на Bonus и скопировать уже составленную формулу на ряд ниже с новым критерием – 

=SUMIF(INDIRECT($D5),"<="&G$2,INDIRECT($C5))

– не даст правильного результата, поскольку в предыдущей формуле действует оператор «больше или равно» вместо необходимого для этого запроса оператора «равно». Благодаря этому уже созданную формулу можно упростить, и максимально корректная запись формулы будет такова: 

=SUMIF(INDIRECT($D6),G$2,INDIRECT($C6))

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

В результате этих упражнений должна получиться матрица с такими значениями:

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

 Юрий Аммосов

11/04/2012