Студопедия

Главная страница Случайная страница

КАТЕГОРИИ:

АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника






Расходы на персонал: использование базы данных




 

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

 

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

 

1. Создадим новый отдельный лист, пометим его цветом, установленным для переменных, и выберем ему уникальное описательное имя, пригодное для использования в формулах – например, pers_db.

2. Разместим в строке 1 заголовок "Персонал"

3. Разместим в строке 2 заголовки полей базы данных, также в виде, пригодном для использования в качестве переменных:

a. ID – порядковый номер записи

b. Position – название должности

c. Division – название подразделения

d. Type – название типа сотрудника

e. Salary – оклад за период

f. Start_period – период, в котором сотрудник поступает на работу

g. Bonus – сумма премиальных, или бонусов (предположим, что бонусы выплачиваются за факт найма и затем каждые n периодов)

h. 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.

 

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

 

 


mylektsii.ru - Мои Лекции - 2015-2019 год. (0.005 сек.)Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав Пожаловаться на материал