Содержание
Постановка задачи 2
1. Организационно-экономическая сущность задачи 3
2. Описание входной информации 4
3. Описание условно-постоянной информации 5
4. Описание результирующей информации 6
5. Описание алгоритма решения задачи 7
Решение задачи средствами MS Excel 8
Решение задачи средствами MS Access 10
Список литературы 16
Постановка задачи
Руководству важно ежемесячно получать сводный отчет по начислению заработной платы в разрезе специальностей (должностей), а также по предприятию в целом. Для этого используется ведомость «Свод зарплаты по профессиям»», содержащая два показателя:
- сумма начисленной зарплаты по каждой профессии;
- общая сумма зарплаты по предприятию.
Первый показатель рассчитывается путем сложения сумм начисленной зарплаты каждому работнику определенной профессии, а второй – путем сложения сумм начисленной зарплаты каждому работнику на предприятии.
Входной информацией служит ведомость «Расчет заработной платы», содержащая следующие реквизиты: табельный номер, код профессии, цех, профессия, разряд, начисленная зарплата.
Справочной информацией служат следующие реквизиты: код цеха, цех, код профессии, профессия.
В результате следует получить ведомость со следующими реквизитами: Профессия, начисленная зарплата по профессии, начисленная зарплата по предприятию в целом.
1. Организационно-экономическая сущность задачи
-
Наименование задачи: Учет начисленной зарплаты.
-
Место решения задачи: плановый отдел ООО «Максимум»
-
Цель решения задачи: Контроль начисления зарплаты в соответствии со штатным расписанием, сокращение затрат на составление ведомости распределения зарплаты по профессиям.
-
Периодичность решения задачи: ежемесячно до 15 числа следующего месяца.
-
Для кого предназначено решение задачи: для руководства предприятия.
-
Источники получения исходных документов: бухгалтерия ООО «Максимум».
-
Информационная модель задачи.
1.8. Экономическая сущность задачи.
Учет начисления зарплаты необходим для контроля за расходом средств, направляемых на оплату труда, согласно штатного расписания и положений о премировании различных цехов. На основании полученной ведомости составляется план расходов средств на оплату труда на следующий месяц.
2. Описание входной информации
В качестве входной информации используются ведомости по расчету зарплаты. На основании этих документов создается следующий машинный документ.
Расчет заработной платы
Таб.№
|
Код проф.
|
Цех
|
Профессия
|
Разряд
|
Начисленная з/пл
|
n
|
i
|
|
|
|
Si
|
Описание структуры первичного документа «Расчет заработной платы»
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
Способ ввода реквизита
|
целые
|
дробные
|
Таб №
|
Tn
|
С
|
4
|
|
|
Вручную
|
Код профессии
|
KP
|
С
|
4
|
|
|
Вручную
|
Цех
|
D
|
С
|
20
|
|
2
|
Автом.из справочника
|
Профессия
|
NP
|
С
|
50
|
|
1
|
Автом.из справочника
|
Разряд
|
R
|
С
|
1
|
|
|
Вручную
|
Начисленная з/пл
|
S
|
ч
|
6
|
2
|
|
Вручную
|
Различаются два типа данных: символьные С – те, что не поддаются арифметической обработке, и числовые – Ч, которые ей поддаются.
Количество документов за период: 4 шт. за месяц.
Контроль ввода документов:
— код цеха: контроль на диапазон значений (от 1 до 4)
— код профессии: контроль на диапазон значений (от 1 до 13)
— табельный номер: уникальный номер.
3. Описание условно-постоянной информации
Для решения задачи используются два справочника:
— справочник цехов (Код_цеха), который служит для расшифровки кодов цехов;
— справочник профессии (Код_профессии), который служит для расшифровки кодов материалов.
Описание структуры документа «Справочник цехов» (Код_цеха)
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
Способ ввода реквизита
|
целые
|
дробные
|
Код цеха
|
KD
|
С
|
4
|
|
1
|
Вручную
|
Наименование цеха
|
ND
|
С
|
20
|
|
|
Вручную
|
Описание структуры документа «Справочник профессий» (Код_профессии)
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
Способ ввода реквизита
|
целые
|
дробные
|
Код профессии
|
KP
|
С
|
4
|
|
1
|
Вручную
|
Наименование профессии
|
NP
|
С
|
50
|
|
|
Вручную
|
Код цеха*
|
KD
|
C
|
4
|
|
|
Вручную
|
Наименование цеха*
|
ND
|
С
|
20
|
|
|
Автом.из справочника
|
* при решении задачи с использованием СУБД MS Access данные строки в справочник не входят.
4. Описание результирующей информации
Проектируется форма результирующего документа.
Описание структуры результирующего документа
Распределение начисления заработной платы по профессиям за ________ месяц
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
целые
|
дробные
|
Наименование профессии
|
NP
|
С
|
50
|
|
1
|
Сумма зарплаты по профессии
|
QS
|
Ч
|
6
|
2
|
|
Общая сумма начисленной зарплаты
|
OS
|
Ч
|
7
|
2
|
|
Количество документов за период: ежемесячно 1 шт.
Количество строк в документе (в среднем): 13
Контроль правильности документа: логический контроль полученных сумм.
5. Описание алгоритма решения задачи
Для получения ведомости «Распределение начисления заработной платы по профессиям за месяц» необходимо рассчитать два показателя:
— сумма начисленной зарплаты по профессии;
— общая сумма начисленной зарплаты по предприятию.
Расчеты выполняются по следующим формулам:
;
где Si — сумма начисленной заработной платы каждому работнику i-ой профессии;
Ci — сумма начисленной зарплаты по профессии;
С — общая сумма начисленной зарплаты по предприятию.
Приложение к постановке задачи
Форма первичного документа «Ведомость по расчету заработной платы» (условная)
Решение задачи средствами MS Excel
Для решения задачи средствами MS Excel создадим новый документ и сохраним его под именем «Зарплата по профессиям».
В этом документе создадим два листа для справочников: «Код_цеха» и «Код_профессии». Также создадим лист «Расчет зарплаты», содержащий ведомость по расчету зарплаты.
Так как профессии в различных цехах не дублируются, то целесообразно в справочник «Код_профессии» добавить сведения о принадлежности профессии к цеху, что упростит ввод данных в ведомости (информацию можно будет брать из одного справочника).
Вид таблиц Excel справочника «Код_профессии» и ведомости «Расчет зарплаты» (в сокращенном варианте) представлены на таблицах 1 и 2.
Код_профессии
|
|
|
|
Справочник профессии
|
|
|
Код профессии
|
Наименование
|
Код цеха
|
Цех
|
1
|
Электрик
|
1
|
ВПР(C4;Код_цеха;2;0)
|
2
|
Мастер по ремонту
|
2
|
ВПР(C5;Код_цеха;2;0)
|
3
|
Мастер ОГЭ
|
1
|
ВПР(C6;Код_цеха;2;0)
|
4
|
Водитель
|
3
|
ВПР(C7;Код_цеха;2;0)
|
5
|
Машинист
|
2
|
ВПР(C8;Код_цеха;2;0)
|
6
|
Слесарь
|
2
|
ВПР(C9;Код_цеха;2;0)
|
7
|
Гл.энергетик
|
1
|
ВПР(C10;Код_цеха;2;0)
|
8
|
Гл.бухгалтер
|
4
|
ВПР(C11;Код_цеха;2;0)
|
9
|
Бухгалтер
|
4
|
ВПР(C12;Код_цеха;2;0)
|
10
|
Нач.трансп.цеха
|
3
|
ВПР(C13;Код_цеха;2;0)
|
11
|
Нач.компрессорного цеха
|
2
|
ВПР(C14;Код_цеха;2;0)
|
12
|
Зам.директора
|
4
|
ВПР(C15;Код_цеха;2;0)
|
13
|
Директор
|
4
|
ВПР(C16;Код_цеха;2;0)
|
|
|
|
|
Таб.1. «Справочник Код_профессии»
|
|
|
|
|
|
|
Расчет заработной платы
|
|
|
|
|
|
|
|
|
Таб.№
|
Код проф.
|
Цех
|
Профессия
|
Разряд
|
Начисленная з/пл
|
1
|
1
|
ВПР(B5;Код_профессии;4;0)
|
ВПР(B5;Код_профессии;2;0)
|
|
|
2
|
1
|
ВПР(B6;Код_профессии;4;0)
|
Электрик
|
|
|
3
|
1
|
ВПР(B7;Код_профессии;4;0)
|
ВПР(B7;Код_профессии;2;0)
|
|
|
12
|
2
|
ВПР(B8;Код_профессии;4;0)
|
ВПР(B8;Код_профессии;2;0)
|
|
|
354
|
13
|
ВПР(B9;Код_профессии;4;0)
|
ВПР(B9;Код_профессии;2;0)
|
|
|
ИТОГО
|
|
|
|
|
СУММ(F5:F9)
|
Таб..2. Ведомость «Расчет зарплаты»
Для автоматического составления ведомости «Распределение начисленной зарплаты по профессиям» воспользуемся мастером создания сводных таблиц MS Excel. Для его работы необходимо выделить шапку таблицы «Расчет зарплаты» и все ее данные (кроме строки «Итого») и выполнить команду меню Данные-Сводная таблица, и следовать шагам мастера. В конце элемент «Профессия» необходимо поместить в область строк, а элемент «Начисленная з/пл» – в область данных.
Контрольный пример
Расчет заработной платы
|
Расчет заработной платы
|
|
|
|
|
|
|
|
|
Таб.№
|
Код проф.
|
Цех
|
Профессия
|
Разряд
|
Начисленная з/пл
|
1
|
1
|
ОГЭ
|
Электрик
|
3
|
7 500,00
|
2
|
1
|
ОГЭ
|
Электрик
|
3
|
7 500,00
|
3
|
1
|
ОГЭ
|
Электрик
|
4
|
8 500,00
|
12
|
2
|
Компрессорный
|
Мастер по ремонту
|
5
|
8 000,00
|
4
|
2
|
Компрессорный
|
Мастер по ремонту
|
6
|
8 750,00
|
5
|
3
|
ОГЭ
|
Мастер ОГЭ
|
6
|
9 000,00
|
13
|
4
|
Транспортный
|
Водитель
|
2
|
8 000,00
|
153
|
4
|
Транспортный
|
Водитель
|
3
|
8 450,00
|
512
|
5
|
Компрессорный
|
Машинист
|
4
|
8 000,00
|
54
|
5
|
Компрессорный
|
Машинист
|
4
|
8 000,00
|
65
|
5
|
Компрессорный
|
Машинист
|
5
|
8 800,00
|
78
|
5
|
Компрессорный
|
Машинист
|
5
|
8 800,00
|
587
|
6
|
Компрессорный
|
Слесарь
|
6
|
10 000,00
|
588
|
7
|
ОГЭ
|
Гл.энергетик
|
|
15 000,00
|
698
|
8
|
Управление
|
Гл.бухгалтер
|
|
20 000,00
|
412
|
9
|
Управление
|
Бухгалтер
|
|
10 000,00
|
687
|
9
|
Управление
|
Бухгалтер
|
|
10 000,00
|
657
|
10
|
Транспортный
|
Нач.трансп.цеха
|
|
12 000,00
|
987
|
11
|
Компрессорный
|
Нач.компрессорного цеха
|
|
15 000,00
|
513
|
12
|
Управление
|
Зам.директора
|
|
20 000,00
|
369
|
12
|
Управление
|
Зам.директора
|
|
22 000,00
|
354
|
13
|
Управление
|
Директор
|
|
35 000,00
|
ИТОГО
|
|
|
|
|
268 300,00
|
Справочник цехов (Код_цеха)
Код цеха
|
Наименование
|
1
|
ОГЭ
|
2
|
Компрессорный
|
3
|
Транспортный
|
4
|
Управление
|
Справочник профессий (Код_профессии)
Код_профессии
|
|
|
|
|
Справочник профессии
|
|
|
|
Код профессии
|
Наименование
|
Код цеха
|
Цех
|
|
1
|
Электрик
|
1
|
ОГЭ
|
|
2
|
Мастер по ремонту
|
2
|
Компрессорный
|
|
3
|
Мастер ОГЭ
|
1
|
ОГЭ
|
|
4
|
Водитель
|
3
|
Транспортный
|
|
5
|
Машинист
|
2
|
Компрессорный
|
|
6
|
Слесарь
|
2
|
Компрессорный
|
|
7
|
Гл.энергетик
|
1
|
ОГЭ
|
|
8
|
Гл.бухгалтер
|
4
|
Управление
|
|
9
|
Бухгалтер
|
4
|
Управление
|
|
10
|
Нач.трансп.цеха
|
3
|
Транспортный
|
|
11
|
Нач.компрессорного цеха
|
2
|
Компрессорный
|
|
12
|
Зам.директора
|
4
|
Управление
|
|
13
|
Директор
|
4
|
Управление
|
|
|
|
|
|
|
|
|
|
|
|
Свод зарплаты по профессиям (Свод по профессиям)
Профессия
|
Начисленная з/пл
|
Бухгалтер
|
20 000,00
|
Водитель
|
16 450,00
|
Гл.бухгалтер
|
20 000,00
|
Гл.энергетик
|
15 000,00
|
Директор
|
35 000,00
|
Зам.директора
|
42 000,00
|
Мастер ОГЭ
|
9 000,00
|
Мастер по ремонту
|
16 750,00
|
Машинист
|
33 600,00
|
Нач.компрессорного цеха
|
15 000,00
|
Нач.трансп.цеха
|
12 000,00
|
Слесарь
|
10 000,00
|
Электрик
|
23 500,00
|
Общий итог
|
268 300,00
|
Решение задачи средствами MS Access
Создадим новую базу данных «Зарплата». В ней создадим в режиме конструктора три таблицы:
1. Справочник цехов
Рис.3. Создание таблицы «Справочник цехов» в режиме конструктора
2. Код профессии.
Рис.4. Создание таблицы «Код профессии» в режиме конструктора
3. Расчет
Рис.5 Создание таблицы «Расчет» в режиме конструктора
После этого заполняем данные таблицы по порядку:
1. Справочник цехов
Рис.6. Заполненная таблица «Справочник цехов»
2. Код профессии
Рис.7. Заполненная таблица «Код профессии»
3. Расчет
Рис.8. Заполненная таблица «Расчет»
Далее необходимо создать связи между таблицами. Выполняем команду Сервис-Схема данных и устанавливаем связи, как на рис.9.
Рис.9. Схема связи данных в таблицах БД «Зарплата»
Чтобы получить итоговую таблицу с распределением начисления зарплаты по профессиям необходимо создать запрос в режиме конструктора. Вид запроса показан на рис.10
Рис.10. Запрос на выборку данных для итоговой таблицы.
Создадим итоговый отчет в режиме мастера. На первом шаге выберем «Запрос: Расчет зарплаты», в выбранные поля переместим только «Наименование профессии» и «Зарплата». Второй шаг пропускаем как есть. На третьем шаге нажимаем кнопку «Итоги…» и в открывшемся окне отмечаем флаг «Sum», а в поле «Показать» выбираем «только итоги». Четвертый шаг пропускаем как есть. На пятом шаге выберем деловой стиль. На шестом шаге дадим название нашему отчету «Зарплата по профессиям».
После небольшой доработки в конструкторе у нас получился следующий отчет:
Зарплата по профессиям
Наименование профессии Зарплата
Бухгалтер
(2 записей)
Sum 20 000
Водитель
(2 записей)
Sum 16 450
Главный бухгалтер
(1 запись)
Sum 20 000
Главный энергетик
(1 запись)
Sum 15 000
Директор
(1 запись)
Sum 35 000
Зам.директора
(2 записей)
Sum 42 000
Мастер АХУ
(2 записей)
Sum 16 750
Мастер ОГЭ
(1 запись)
Sum 9 000
Машинист АХУ
(4 записей)
Sum 33 600
Начальник компрессорного
(1 запись)
Sum 15 000
Начальник транспортного
(1 запись)
Sum 12 000
Слесарь КиП
(1 запись)
Sum 10 000
Электрик
(3 записей)
Sum 23 500
ИТОГО 268 300
Список литературы
-
Информационные системы в экономике: Учебник для студентов вузов / Под ред.Г.А.Титоренко. – М.: ЮНИТИ-ДАНА, 2008. -463 с.
-
Методические указания по выполнению контрольных работ для самостоятельной работы студентов III курса. – М.: Вузовский учебник, 2007. – 80 с.
|
