Использование электронных таблиц EXCEL для решения задач управления чело-веческими ресурсами

Любое поле БД может размещаться однократно в одной из областей страницы, строки или столбца; в области данных можно многократно помещать одни и те же поля для вычисления различных видов итогов, изменяя названия этих полей. Наименования полей исходной таблицы не могут использоваться в расчётной (сводной) части таблицы.

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

 

1. Создать именованный блок БазаДанных на листе Табель учёта работы для таблицы, включая шапку таблицы, - команды Формулы, Присвоить имя.
2. Создать сводную таблицу:

1 этап.

• Выполнить команду меню Вставка, Сводная таблица;
• Указать таблицу или диапазон ячеек - блок БазаДанных (F3, выбор из списка имён); на новом листе; ОК.
• Появится лист, справа расположены окна. В самом верхнем окне находятся названия полей Табеля (БазаДанных). Перетащить поля в нижние окна в части макета:

Окно Название строк – Код работника; Дата работы; ∑Значений( из окна ∑Значений)

Окно Название столбцов – Код подразделения

Окно ∑Значений (область данных сводной таблицы)  - Количество часов работы, Количество выполненной работы, Стоимость работника, Стоимость работы

• В области сводной таблицы щёлкнуть правой клавишей мыши на дату в области название строк и выбрать строку Группировать, по месяцам;
• переименовать поле Сумма Количество часов работы в Сумма часов работы;
• переименовать поле Сумма Количество выполненной работы в Сумма выполненной работы;
• переименовать поле Сумма Стоимость работника в Затраты на работника;
• переименовать поле Сумма Стоимость работы в Выручка от работы;
• Для переименования в области сводной таблицы щёлкните правой клавишей мыши на строке названия, выберете из построчного меню строку Параметры полей значений и в открывшемся окне измените Имя поля;
• Добавьте в окно ∑Значений - Стоимость работника, Стоимость работы;
• переименовать поле Сумма Стоимость работника в Структура затрат на работника по отделу и укажите дополнительное вычисление – доля от суммы по столбцу;
• переименовать поле Сумма Стоимость работы в Структура выручки от работы по отделу и укажите дополнительное вычисление – доля от суммы по столбцу;
• Переименовать новый лист в Своды.

2 этап.

Изменить параметры сводной таблицы с помощью команды контекстного меню Параметры сводной таблицы:

Общая сумма по столбцам

Общая сумма по строкам

Автоформат

Сохранять форматирование

Сохранять данные с таблицей

Обновить при открытии

 

Встроенные функции работы с базой данных EXCEL

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

• Проверить состав ячеек блока БазаДанных, созданный для таблицы Табель рабочего времени;
• Подготовить блок (лист) критериев.

Основные встроенные функции по работе с базой данных:

ДСРЗНАЧ	Среднее значение по указанному полю базы данных.
БСЧЁТ	Количество числовых ячеек в указанном поле для выбранных записей базы данных .
БСЧЁТА	Количество непустых ячеек в указанном поле для выбранных записей базы данных
БИЗВЛЕЧЬ	Отбор первой записи базы данных, удовлетворяющей заданному условию.
ДМАКС	Максимальное значение в указанном поле для выбранных записей базы данных .
ДМИН	Минимальное значение в указанном поле для выбранных записей базы данных .
БДПРОИЗВЕД	Произведение значений в указанном поле для выбранных записей базы данных.
ДСТАНДОТКЛ	Стандартное отклонение по выборке из выделенной части базы данных.
ДСТАНДОТКЛП	Стандартное отклонение по генеральной совокупности из выделенной части базы данных.
БДСУММ	Сумма значений в указанном поле для выбранных записей базы данных..
БДДИСП	Дисперсия по выборке из выделенной части базы данных.
БДДИСПП	Дисперсия по генеральной совокупности из выделенной части базы данных.

 

Итоговые показатели по подразделениям

На листе Критерии подготовить диапазон критериев для поля Код подразделения.

Для этого следует:

1. Скопировать название столбца Код подразделения в таблице Сотрудники (Персонал) и вставить его в ячейки, начиная с A1 до ячейки E1
2. Выделить блок значений столбца Код подразделения в таблице Справочник подразделений на листе Справочники;
3. Выполнить команду Копировать;
4. Установить курсор на лист Критерии в ячейку A2 и выполнить команду Специальная вставка, Транспонировать;

 

 

А  В  С   D   E

1	Код подразделения	Код подразделения	Код подразделения	Код подразделения	Код подразделения
2	У	К	Б	М	П

Рис. 5

 

На листе Справочники:

1. Ввести вспомогательный массив чисел для таблицы Справочник подразделений - в столбце (справа от таблицы по высоте блока Код_подразделения) вида:

0

1

2

3 (т.д.)

Этот массив обеспечивает простоту размножения формул, использующих встроенные функции работы с базой данных. Вводится значение 0 в первую ячейку столбца, закрасить требуемый блок, выполнить команду меню Главная, Редактировать, Заполнить, Прогрессия, Арифметическая, шаг 1.

2. Создать блок с именем База_данных для таблицы Персонал, включая и шапку таблицы.
3. Ввести формулу расчета суммы окладов по Коду подразделения в соответствующую ячейку первой строки таблицы Справочник подразделений (под названием Фонд зарплаты):

=БДСУММ(База_данных;13;СМЕЩ(Критерии!$А$1;0;$G3;2;1))

где  База_данных - имя блока базы данных (таблица Персонал);

13 - номер поля, содержащего оклад;

СМЕЩ(Критерии!$А$1;0;$G3;2;1 - встроенная функция для задания области критерия, включает:

Критерии!$А$1 - начальная ячейка области критерия;

0 - смещение  по строкам области критерия;

$G3 -адрес ячейки, содержащей смещение по столбцам области критерия для очередного Кода подразделения;

2 - высота области критерия;

1 - ширина области критерия.

4. Скопировать формулу БДСУММ в другие ячейки столбца Фонд зарплаты;
5. Скопировать формулу БДСУММ для расчета других показателей по Коду подразделения: и внести необходимые исправления (БазаДанных - имя блока базы данных (таблица Табель рабочего времени)):

Сумма зарплаты:

=БДСУММ(БазаДанных;10;СМЕЩ(Критерии!$А$1;0;$G3;2;1))

Сумма заработана:

= БДСУММ (БазаДанных;11;СМЕЩ(Критерии!$А$1;0;$G3;2;1))

6. Ввести формулу расчета количества работников по Коду подразделения в соответствующую ячейку первой строки таблицы Справочник подразделений (под названием Количество работников):

Количество работников:

= =БСЧЁТ(Персонал;14;СМЕЩ(Критерии!$A$1;0;$G3;2;1))

 

Итоги по датам работы

Итоги выводятся по календарным датам работы.

Для вычисления итогов следует:

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

Дата по значениям  первого столбца - Дата работы

Стоимость_работника по значениям  столбца – Стоимость работника

Стоимость_работы по значениям  первого столбца – Стоимость работы

 

2. На листе Справочники ввести формулы расчета:

• Затраты на работников за период учета (дату):

=СУММЕСЛИ(Дата;$A20;Стоимость_работника);

где;$A20 - адрес ячейки, содержащей учетную дату Даты работы в таблице Итоги по датам работы на листе Справочники.

• Стоимость работы за период учета (дату):

=СУММЕСЛИ(Дата;$A20;Стоимость_работы);

3. Скопировать формулы для расчета показателей по всем периодам.

Листы Диаграммы

Создать диаграмму следующего вида:

Структура Затраты - Результаты.

Выделить таблицу Справочник  подразделений.

Выполнить команду меню Вставка, Диаграмма, указать:

Тип - гистограмма, формат 1;

- -