Автоматизация статистических отчетов предприятия в Excel: ведомость «Сумма заработной платы по профессиям»
Глава 1. Постановка задачи
1. Организационно-экономическая сущность задачи
2. Описание входной информации
3. Описание условно-постоянной информации
4. Описание результатов информации
5. Описание алгоритма решения задачи
Глава 2. Решение задачи средствами MS Excel
Список литературы
Глава 1. Постановка задачи
Допустим, для статистических отчетов необходимо ежемесячно получать ведомость под названием «Сумма заработной платы по профессиям», содержащую два показателя:
— профессия
— сумма заработной платы по профессиям
Первый показатель выводится путем анализа кода профессии, а второй показатель рассчитывается путем сложения всех сумм заработной платы соответствующие данному коду профессии.
Входной оперативной информацией служит расчетная ведомость заработной платы, содержащая следующие реквизиты: код цеха, код участка, табельный номер, Ф.И.О. сотрудников код профессии, профессия, разряд, сумма заработной платы по табельному номеру. Необходимо разместить данные реквизиты в первичном документе.
Справочной информацией служат следующие реквизиты: код цеха, наименование цеха, код участка, наименование участка, код профессии, профессия, табельный номер, разряд, Ф.И.О. сотрудников.
В результате следует получить ведомость со следующими реквизитами: кода профессии, сумм заработной платы соответствующие данному коду профессии.
1. Организационно-экономическая сущность задачи
1.1. Наименование задачи: сумма заработной платы по профессии.
1.2. Место решение задачи: бухгалтерия ООО «ЗЖБИ».
1.3. Цель решения задачи: Обеспечение необходимой информацией для составления статистической отчетности.
1.4. Периодичность решения задачи: ежемесячно до 5-го числа следующего месяца.
1.5. Для кого предназначено решение задачи: для главного бухгалтера.
1.6. Источник получения исходных документов: расчетная ведомость по заработной плате.
1.7. Информационная модель задачи.
1.8. Экономическая сущность задачи.
Учет начисленной заработной платы необходимой для составления статистической отчетности. На основании ведомости, получаемой в результате решения данной задачи, принимаются управленческие решения, касающиеся изменения окладов работников.
2. Описание входной информации
2.1. В данном разделе приводится перечень всех первичных документов, используемых для решения задачи.
2.2. В качестве входной информации используются документы «Расчетная ведомость». На основании этого документа создается следующий машинный документ.
Расчетная ведомость
Для упрощения описания входной информации ряд реквизитов, которые не используются для решения данной задачи (наименование цеха, наименование участка, Ф.И.О, профессия), в машинный документ не вошли.
Описание структуры первичного документа
«Расчет ведомость»
Будут различаться два типа данных: символьные С- те, что не поддаются арифметической обработке, и числовые- Ч, которые поддаются таковой.
2.3. Описание контроля ввода документа:
— Код цеха: контроль на диапазон значений (от 11 до 13);
— Код участка: контроль на диапазон значений (от 101 до 103);
— Код профессии: контроль на диапазон значений (от 1 до 7);
— Разряд: контроль на диапазон значений (от 3 до 6);
3. Описание условно- постоянной информации
3.1. В данном разделе приводится перечень справочников, используемых для решения задачи, а также описание их структуры.
Для решения задачи используется:
— справочник профессий, который служит для расшифровки кодов профессий;
— справочник цехов, который используется для расшифровки кодов цехов;
— справочник сотрудников, который используется для расшифровки табельных номеров.
Описание структуры документа
«Справочник сотрудников»
Количество документов за период: ежемесячно 1 шт.
Количество строк в документе (в среднем): 12.
Контроль правильности документа: логический контроль полученных сумм.
5. Описание алгоритма решения задачи
Для получения ведомости «Сумма заработной платы по профессиям» необходимо рассчитать показатель:
— сумма заработной платы по каждой профессии.
Расчеты выполняются по следующим формулам:
Где Sik — сумма заработной платы k-го кода профессии, начисленная i-му табельному номеру.
Приложение к постановке задачи
Форма первичного документа «Расчетная ведомость» (условная)
Глава 2. Решение задачи средствами MS Excel
1. Вызов Excel;
2. Переименовываем Лист 1 в Справочник цехов;
3. Вводим в ячейки названия заголовков (код цеха, наименование цеха, код участка, наименование участка);
4. Организуем контроль вводимых данных в колонку Код цеха:
• выделяем ячейки А3÷А5;
• выполняем команду Проверка… меню Данные;
• в поле Тип данных выбираем Целое число;
• задаем в поле Минимум: 11 (рис.1);
• задаем в поле Максимум: 13;
Рис. 1 Задание интервала допустимых значений целых чисел
• выбираем закладку Сообщение для ввода;
• вводим в поле Заголовок: ограничение для ввода номера
• вводим в поле Сообщение: код цеха может принимать значения 11 – 13;
• выбираем закладку Сообщение об ошибке;
• в поле Вид: выбираем Останов (рис.2).
В случае ввода ошибочных данных на экран монитора выводится сообщение об ошибке (рис.3).
Рис.3. Вид сообщения «Останов»
4. Вводим информацию, приведенную в табл.
Справочник цехов
Код цеха Наименование цеха Код участка Наименование участка
11 Арматурный 101 Сварка арматуры
12 Бетоносмесительный 102 Бетоносмесительный узел
13 Формовочный 103 Формовка ЖБИ
6. Присвоим имя группе ячеек:
• выделим ячейки А3:D5;
• выберем команду Имя в меню Вставка;
• выберем команду Присвоить;
• в окне Присвоение имени (рис.4) нажимаем кнопку Добавить.
Рис.4. Вид окна «Присвоение имени»
7. Аналогично создаются таблицы Справочник профессий и Справочник сотрудников.
8. Переименуем Лист4 в Расчетная ведомость.
9. Создаем таблицу Расчетная ведомость (рис.5).
10. Организуем проверку ввода данных в графу Код цеха.
11. Вводим исходные данные
Рис.5. Вид таблицы «Расчетная ведомость»
12.Заполним графу Код участка в соответствии с кодом цеха:
• сделаем ячейку В3 активной;
• воспользуемся командой Функция… меню Вставка;
• в поле Категория выберем Ссылки и массивы;
• в поле Функция выберем ВПР;
• введем информацию в поле Искомое значение;
• введем информацию в поле Таблица;
• введем информацию в поле Номер столбца; (рис.6)
Рис.6. Вид второго окна мастера функций
13. Скопируем формулу в ячейки В4÷D14.
14. Переименуем Лист 5 в Фактическая заработная плата по профессиям.
15.Создаем ведомость Фактическая заработная плата по профессиям, воспользовавшись командой Сводная таблица (рис.7).
Рис.7. Создание макета сводной таблицы
Расчетная ведомость
Справочник цехов
Справочник профессий
Справочник сотрудников
Фактическая заработная плата по профессиям
Список литературы
1. Колесников А. Excel 2000. К.: групп BHV, 1999.
2. Одинцов Б.Е. Обратные вычисления в формировании экономических решений. М.: Финансы и статистика, 2004.
3. Романов А.Н., Одинцов Б.Е. Информационные системы в экономике. М.: Вузовский учебник, 2006.
4. Романов А.Н., Одинцов Б.Е. Советующие информационные системы в экономике. М.: ЮНИТИ, 2000.