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

 

 

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

 

Рассматривается предметная область – отдел управления персоналом. Решаются задачи:

- учёта рабочего времени работников,

- создания штатного расписания,

- расчёт заработной платы и создание расчётной ведомости по зарплате,

- оценка результатов работы  персонала.

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

 

В среде электронных таблиц EXCEL обеспечить:

1. Создание и ведение нормативно-справочной информации для учета персонала предприятия;
2. Регулярный учет сведений о работе персонала и результатах деятельности;
3. Составление отчетов о штатном расписании, заработной плате и результатах работы;
4. Ведение статистики по затратам на человеческие ресурсы и результатам работы;
5. Планирование затрат на человеческие ресурсы.

Информационно-логическая модель предметной области

 

 

 

 

 

Рис. 1. Связи информационных объектов (ИО) - один ко многим, реквизитный состав ИО приведен в таблице 1.

Таблица 1

Наименование ИО	Реквизиты	Формат
Подразделения	Код подразделения Наименование подразделения Количество работников (человек) Фонд зарплаты (в стоимостном выражении) Сумма зарплаты  (затраты на персонал) Сумма заработана (результат работы)	Текст 1 Текст 25 Числовой Денежный Денежный Денежный
Должности	Код должности Наименование должности	Текст 1 Текст 25
Образование	Образование	Текст 20
Персонал	Код работника ФИО работника Подразделение (список) Должность (список) Дата приёма Дата увольнения Дата рождения Пол (м, ж) Образование (список) Тип психики (список)	Числовой Текст 25 Текст 25 Текст 25 Дата Дата Дата Текст 1 Текст 20 Текст 4
Учетные периоды	Код периода (месяц, год) Суммарный объем зарплаты (в стоимостном выражении) Суммарный объем выработки (в стоимостном выражении)	Дата Денежный Денежный
Штатное расписание	Код подразделения (список) Код должности (список) Количество должностей Оклад Суммарный объем зарплаты	Текст 1 Текст 1 Числовой Денежный Денежный
Справочник работ	Код работы Наименование работы Тариф за единицу работы	Текст 3 Текст 20 Денежный
Табель учёта работы	Дата работы (день, месяц, год) Код подразделения (список) Код работника (список) Количество часов работы Количество выполненных работ (операций) Код работы Тариф за единицу работы (выбор по коду работы) Норматив по выполненным работам Оценка качества работ (значение от 0 до 1)	Дата Текст 1 Числовой Числовой Числовой Текст 3 Денежный Числовой Числовой
Тип психики	Тип психики	Текст 4
Итоги по датам работы	Дата работы Стоимость работника Стоимость работы	Дата Денежный Денежный

 

 

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

Последовательность выполнения лабораторной работы

• Определение состава и назначения листов рабочей книги;
• Проектирование структуры данных (макета размещения информации) отдельных листов рабочей книги;
• Выбор информационных технологий EXCEL для решения задачи;

 

Рабочая книга EXCEL может включать 1-255 листов, каждый из которых содержит табличные структуры данных. Согласно приведенной схеме ИЛМ ПО (рис. 1) создается набор листов рабочей книги:

• Справочники, содержит справочники (коды и наименования) подразделений, должностей, видов работ, а также итоговые данные деятельности работников предприятия по датам;
• Штатное расписание – содержит данные о составе должностей подразделений и их окладах. Формируется фонд заработной платы предприятия.
• Персонал - содержит данные о составе работников предприятия и их свойствах.
• Табель учёта работы, содержит базу данных оперативного учета работы персонала и её результатов;
• Итог штатного расписания, Итог табеля, содержит промежуточные итоги по группам значений ключевого полям базы данных;
• Своды, содержит сводные данные оперативного учета работы персонала;
• Анализ, содержит результаты дополнительного анализа работы;
• Критерии, имеет вспомогательный характер для автоматизации обработки данных по оперативному учету работы персонала;
• Статистика, содержит сведения статистического характера оперативного учета работы персонала;
• Диаграммы, иллюстрирует состояние затрат на человеческие ресурсы и результатов деятельности.

Машинная реализация в среде EXCEL

Рабочая книга Человеческие ресурсы.xls

• Создать новую рабочую книгу - команда меню ФАЙЛ, Создать, шаблон - Книга,
• Сохранить созданную рабочую книгу под именем Человеческие ресурсы - команда Кнопка Office, Сохранить как, выбрать диск, папку, задать параметры - пароль на открытие книги.

Лист Справочники

Переименовать лист Лист1 в лист Справочники.

Для нормативно-справочной информации ИО Подразделения, Должности, Периоды учета на листе Справочники создаются таблицы.

Справочник Подразделения

 

Код подразделения	Наименование подразделения	Количество работников	Фонд зарплаты	Сумма зарплаты	Сумма заработана

Рис. 2

1. В столбцы Код подразделения и Наименование подразделения ввести коды и наименования подразделений (для учебного примера - ограничиться: У - Управление; Б - Бухгалтерия; М – отдел маркетинга; К – отдел кадров; П - производство). Остальные столбцы таблицы НЕ ЗАПОЛНЯТЬ.
2. Создать именованный блок Код_подразделения для ячеек, содержащих коды подразделений.

 

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

· Первый символ обязательно буква или символ подчеркивания.

· Имена не имеют сходства со ссылками на ячейки.

· Пробел запрещен (можно символ подчеркивания, точки).

· Максимальная длина имени - 255 символов.

· Строчные и прописные буквы в именах блоков не различаются.

Блок ячеек одного листа может включать не обязательно смежные ячейки. Для выделения нескольких областей ячеек листа одного блока используется клавиша <Ctrl>. Microsoft Excel позволяет также создавать "объемные" блоки, содержащие ячейки различных листов одной рабочей книги, но имеющих одинаковую конфигурацию.

1 способ создания именованного блока ячеек одного листа:

• Выполнить команду меню Формулы, Определённые имена, Имя присвоить;
• Ввести имя создаваемого блока ячеек в строке Имя,
• Установить курсор в строку Формула, нажать кнопку и закрасить диапазон ячеек, содержащий только значения кодов ЦБ;
• Нажать кнопку ОК.

2 способ создания именованного блока ячеек одного листа:

• Закрасить диапазон ячеек, содержащий название столбца и значения кодов ЦБ;
• Выполнить команду меню Формулы, Определённые имена, Создать из выделенного фрагмента
• Указать, что имя блока находится в строке выше;
• Нажать кнопку ОК.

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

EXCEL обеспечивает режим - гиперссылки на любые фрагменты рабочей книги, файлы допустимых форматов, ресурсы Internet - команда Вставка, Гиперссылка. При этом происходит автоматический переход к указанному фрагменту или осуществляется вызов файла-источника.

 

Пересчет показателей по Коду подразделения и коду работника должен осуществляться динамически в процессе оперативного ввода учетной информации работы персонала. Это возможно за счет применения встроенных функций работы с базами данных EXCEL (см. далее).

• Суммарный объем затрат на зарплату за весь период наблюдения.
• Суммарный объем результатов работ за весь период наблюдения.

Справочник должностей

 

Код должности	Наименование должности

Рис. 3

1. Ввести код и наименование должности:  Дир - директор, ГлБ – главный бухгалтер, Бух - бухгалтер, Мар – маркетолог, Нот – начальник отдела, Инс – инспектор, Раб - рабочий. Остальные столбцы не заполнять.
2. Создать именованный блок Код_должности для ячеек, содержащих коды должности и Должность для ячеек, содержащих Наименование должности.

Справочник работы

Код работы	Наименование работы	Тариф за единицу работы

Рис. 4

3. Ввести код и наименование работы:  Док – работа с документом (тариф - 100), Усл – оказание услуги (тариф - 200), Изг - изготовление изделия (тариф - 300).
4. Создать именованный блок Код_работы для ячеек, содержащих коды работы.

Справочник Типа психики

 

Тип психики	Код свойства	Наименование свойства
ISTY	I	интраверсия
ISTP	E	экстраверсия
ESTP	S	сенсорика
ESTY	N	интуиция
ISFY	F	этика
ISFP	T	логика
ESFP	Y	рациональность
ESPY	P	иррациональность
INFY
INFP
ENFP
ENFY
INTY
INTP
ENTP
ENTY

Рис. 4

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

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

 

Дата работы	Стоимость работников	Стоимость работы

Рис. 5

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

 

1. Заполнить блок ячеек столбца Дата работы в течение года. Для этого:

• В ячейку первой строки данной таблицы ввести начальную дату учета работ, например, 01.01.12
• Выполнить команду меню Главная, Редактирование, Заполнить, Прогрессия, выбрать тип - Даты, Единицы - Рабочий день, Предельное значение - 01.01.2013, нажать кнопку ОК.

2. Создать для заполненных ячеек именованный блок ячеек - Дата_работы.

Штатное расписание

 

Код подразделения	Код должности	Количество должностей	Оклад	Суммарный объем зарплаты

Рис. 6

 

1. Для ячеек столбца Код подразделения предусмотреть ввод данных из списка значений - блока Код_подразделения, для этого использовать возможности предоставляемые командой Данные – Работа с данными - Проверка данных

На вкладке Параметры указать:

Тип данных - Список

Источник - блок ячеек Код_подразделения (имя блока вставить из списка имен блоков, вызываемого при нажатии клавиши <F3>).

На вкладке Сообщение для ввода указать:

Заголовок - Код_подразделения

Сообщение – ввод из списка

На вкладке Сообщение об ошибке указать:

Вид - Останов

Заголовок - ОШИБКА

Сообщение – ввод не из списка

При нарушении условий проверки выводится сообщение, ввод блокируется.

2. Суммарный объем зарплаты = Количество должностей * Оклад

Лист Персонал

Код сотрудника	Фамилия	Имя	Отчество	Дата рождения	Пол	Дата приёма	Дата уволен	Код подразделения	Должн.	Образо-вание	Тип психики
1	Иванова	Ирина	Ивановна	12.08.1980	ж	07.08.2001
2	Петров	Андрей	Ильич	07.04.1970	м	05.09.2000
3	Соболева	Мария	Петровна	11.08.1978	ж	05.09.2000