48518 (Разработка автоматизированной информационной системы по начислению заработной платы по 18-разрядной тарифной сетке), страница 5

В таблице 1 «Годовой табель учета рабочего времени» следует отразить только данные по каждому из 4-х работников, выбранных по одному из вариантов в разрезе 4-х месяцев: январь, февраль, март, апрель. В качестве исходных данных для построения сводной таблицы - промежуточной формы 1 «Месячный табель учета рабочего времени» - следует выбрать (выделить) все ячейки таблицы 1 «Годовой табель учета рабочего времени» и вызвать мастера сводных таблиц: Данные, Сводная таблица.

Устанавливается параметр «в списке или базе данных… » и нажимается Далее>>..

Указывается (выделяется) диапазон, содержащий исходные данные ($A; F66), нажимается Далее>>.

Построение макета сводной таблицы происходит путем перетаскивания кнопок полей в область, где они должны быть расположены.

Макет сводной таблицы:

Поле страница: месяц расчета зарплаты;

Поле столбец: табельный номер работника;

Поле данные: количество отработанных дней, количество дней по болезни, процент выданного аванса.

Нажимается Далее>>.

Вызывается контекстное меню к сводной таблице и выбирается команда Параметры сводной таблицы.

Используются следующие параметры:

- снять параметр «автоформат »);

- установить параметр «обновить при открытии»;

- снять параметры «общая сумма по столбцам» и «общая сумма по строкам».

При определении параметров сводной таблицы необходимо чтобы в поле Месяц расчета зарплаты был выбран лишь тот месяц, который определен заданием. Для этого нужно отключить параметр Показать все и установить необходимое значение. По строкам 6, 7 и 8 в промежуточной форме 1 «Месячный табель учета рабочего времени» необходимо найти сумму выбранных значений и результаты - поместить в итоговый столбец (F).

На основании данных справочников 1-4 и промежуточной формы 1 «Месячный табель учета рабочего времени» формируется промежуточная форма 2 «Расчетно-платежная ведомость »).

Месяц расчета зарплаты [ссылка на ячейку с названием месяца в промежуточной форме 1 «Месячный табель учета рабочего времени » ].

Дата расчета зарплаты [выбирается согласно месяцу расчета зарплаты (в этой таблице) из справочника 1 «Количество рабочих дней в месяце»]. В MS Excel для решения приведенной задачи используется функция из категории «Ссылки и массивы» - ВПР.

Количество рабочих дней в месяце [выбирается согласно месяцу расчета зарплаты (промежуточная форма 2 «Расчетно-платежная ведомость») из справочника 1 «Количество рабочих дней в месяце»] (аналогично предыдущему показателю).

1. Табельный номер работника [вводится («вручную») согласно выбранному варианту].

2. Ф.И.О. работника [выбирается из справочника 4 «Учетные сведения о сотрудниках отделения» согласно табельному номеру работника с использованием функции ВПР].

3. Тарифный разряд [выбирается из справочника 4 «Учетные сведения о сотрудниках» согласно табельному номеру работника с использованием функции ВПР] (аналогично предыдущему показателю).

4. Тарифный коэффициент [выбирается из справочника 2 «Тарифный справочник» согласно тарифному разряду работника с использованием функции ВПР].

Трудовой стаж определяется на дату расчета зарплаты от даты начала трудовой деятельности. [В MS Excel для решения приведенной задачи была использована функция из категории «дата и время» ДНЕЙ360. Начальная дата – дата начала трудовой деятельности текущего работника - выбирается с помощью функций ВПР из справочника 4 "Учетные сведения о сотрудниках отделения"; конечная дата – дата расчета зарплаты. Полученное выражение делится на 360 (дней в году)].

1. Процент оплаты больничного листа определяется соответственно стажу. Для этого используется функция ЕСЛИ из категории «Логические».

2. Оклад [минимальная зарплата (абсолютная ссылка на соответствующую ячейку справочника 3 «Базовые показатели для расчета заработной платы») * тарифный коэффициент].

Начислено, руб.:

1. Зарплата [оклад / количество рабочих дней в месяце (абсолютная ссылка на соответствующую ячейку в этой таблице) * количество отработанных дней (выбирается с помощью функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени»)].

2. По больничному листу [оклад / количество рабочих дней в месяце (абсолютная ссылка на соответствующую ячейку этой таблицы)* количество дней по больничным листам (выбирается с помощью функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени» {по строке 3})* процент оплаты по больничным листам (ссылка на соответствующую ячейку этой таблицы)].

3. Итого начислено - сумма всех начислений в этой таблице - [зарплата + по больничному листу].

Удержано, руб.

1. Аванс [оклад * процент выданного аванса (выбирается с помощью функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени» {по строке 4})].

2. Подоходный налог [зарплата * на процент походного налога (абсолютная ссылка на соответствующую ячейку справочника 3 «Базовые показатели для расчета заработной платы»)].

3. Профсоюзный взнос [начислено всего (в этой таблице) * процент профсоюзного сбора (абсолютная ссылка на соответствующую ячейку справочника 3 «Базовые показатели для расчета заработной платы»)]. Рассчитывается только по работникам, состоящим в профсоюзе, поэтому следует воспользоваться функциями ЕСЛИ и ВПР.

4. Итого удержано - сумма всех удержаний [аванс + подоходный налог + профсоюзный взнос].

5. К выдаче, руб. [итого начислено – итого удержано].

На третьем этапе разработки АИС создаются выходные формы (таблицы и диаграммы).

Выходная форма 1 «Расчетный лист заработной платы работника» заполняется на основании справочников 2-4 и промежуточных форм 1-2.

Табельный номер работника – вводится («вручную») номер одного работника, по которому выполнялись расчеты.

Месяц расчета заработной платы – [ссылка на промежуточную форму 1 «Месячный табель учета рабочего времени»].

Ф.И.О. работника [выбирается согласно табельному номеру работника (в этой таблице) с использованием функции ВПР из справочника 4 «Учетные сведения о сотрудниках»].

Начало трудовой деятельности [аналогично предыдущему показателю].

Стаж, лет [выбирается согласно табельному номеру работника (в этой таблице) с использованием функции ВПР из промежуточной формы 2 «Расчетно-платежная ведомость»].

Тарифный разряд [выбирается согласно табельному номеру работника (в этой таблице) с использованием функции ВПР из справочника 4 «Учетные сведения о сотрудниках»].

Тарифный коэффициент [выбирается согласно тарифному разряду работника (в этой таблице) с использованием функции ВПР из справочника 2 «Тарифный справочник»].

ОКЛАД [минимальная зарплата (абсолютная ссылка на справочник 3 «Базовые показатели для расчета заработной платы») * тарифный коэффициент (в этой таблице)].

Отработано дней [выбирается согласно табельному номеру работника с использованием функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени»].

Дни по болезни (аналогично предыдущему показателю).

НАЧИСЛЕНО - ВСЕГО, РУБ. [зарплата + по больничному листу (в этой таблице)].

Зарплата [выбирается согласно табельному номеру работника (в этой таблице) с помощью функции ВПР из промежуточной формы 2 «Расчетно-платежная ведомость»].

По больничному листу [аналогично предыдущему].

УДЕРЖАНО - ВСЕГО, РУБ. [выданный аванс + подоходный налог +профсоюзный взнос (в этой таблице)].

Выданный аванс [выбирается согласно табельному номеру работника (в этой таблице) с использованием функции ВПР из промежуточной формы 2 «Расчетно-платежная ведомость»].

Подоходный налог [аналогично предыдущему].

Профсоюзный взнос [аналогично предыдущему].

К ВЫДАЧЕ, РУБ. [всего начислено – всего удержано)].

Выходная форма 2 «Платежная ведомость»

1. Месяц [ссылка на промежуточную форму 1 «Месячный табель учета рабочего времени»].

2. Табельный номер работника [вводится («вручную») согласно варианту Ошибка! Источник ссылки не найден.].

3. Ф.И.О. работника [выбирается согласно табельному номеру работника (в этой таблице) с использованием функции ВПР из справочника 4 «Учетные сведения о сотрудниках»].

4. К выдаче [выбирается согласно табельному номеру работника (в этой таблице) с использованием функции ВПР из промежуточной формы 2 «Расчетно-платежная ведомость»].

На основе данных Выходной формы 2 «Платежная ведомость» строиться обычная гистограмма.

Для построения обычной гистограммы необходимо сделать следующие: 1) выделить область с требуемыми значениями (столбцы с Ф.И.О. и К выдаче) в Выходной форме 2 «Платежная ведомость»; 2) вызвать мастер диаграмм: Вставка, Диаграмма; выбрать тип диаграммы – Гистограмма; вид – Обычная и следовать дальнейшим рекомендациям мастера диаграмм.

Выводы и предложения

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

Поставленная цель была достигнута и все необходимые задачи были решены.

Была разработана и реализована в табличном процессоре MS Excel автоматизированная информационная система по начислению заработной платы по 18-ти разрядной сетке. АИС отвечает требованиям, предъявляемым к автоматизированным информационным системам: алгоритм ее функционирования, спроектированные формы таблиц соответствуют фактическим, форматы данных логически обоснованы.

Список использованной литературы.

1. Улезько А.В. Автоматизированные системы обработки экономической информации: Учебное пособие /А.В.Улезько, А.В.Агибалов, Е.Ю.Горюхина; Под ред. А.П.Курносова. –Воронеж: ВГАУ, 2000. –101 с.

2. Информатика: Учебник /А.П.Курносов, С.А.Кулев, А.В.Улезько и др. –Воронеж: ВГАУ, 1997. -234 с.

3. Информатика: Учебник для вузов /Макарова Н.В., Матвеев Л.А., Бройдо В.Л. и др.; Под ред. Макаровой Н.В. – М: Финансы и статистика, 2001. – 768 с.

4. Информационные системы и технологии в экономике: Учебник. - 2-е изд., доп. и перераб. /Т.П. Барановская, В.И. Лойко, М.И. Семенов, А.И. Трубилин; Под ред. В.И. Лойко –М.: Финансы и статистика, 2003. -416 c

5. Методические указания для разработки курсового проекта по дисциплине «Информационные системы в экономике». Под ред. А.П.Курносова. –Воронеж: ВГАУ, 2005. –54 с.

6. Уткин В. Б. Информационные системы и технологии в экономике: Учебник для вузов. – М.: ЮНИТИ – ДАНА, 2003. – 335 с..

7. Экономическая информатика /Под ред. П.В. Конюховского и Д.Н.Колесова. –СПб: Питер, 2000. – 560 с.

8. Экономическая информатика и вычислительная техника / Под ред В.П. Косарева и А.Ю. Москва «Финансы и статистика» 1998 г.

ПРИЛОЖЕНИЯ

Справочник 1. Количество рабочих дней в месяце

Месяц	Количество рабочих дней в месяце	Дата расчета зарплаты
январь	15	31.01.2006
февраль	19	28.02.2006
март	22	31.03.2006
апрель	21	30.04.2006
май	20	31.05.2006
июнь	21	30.06.2006
июль	21	31.07.2006
август	23	31.08.2006
сентябрь	22	30.09.2006
октябрь	21	31.10.2006
ноябрь	21	30.11.2006
декабрь	22	31.12.2006

Справочник 2. Тарифный справочник

Тарифный разряд	Тарифный коэффициент
1	1,00
2	1,11
3	1,23
4	1,36
5	1,51
6	1,67
7	1,84
8	2,02
9	2,22
10	2,44
11	2,68
12	2,89
13	3,12
14	3,36
15	3,62
16	3,90
17	4,20
18	4,50

Справочник 3. Базовые показатели для расчета заработной платы

Показатели	Значение
Размер мин. заработной платы, руб.	1100
Процент подоходного налога	13%
Процент профсоюзного взнос	1%
Процент оплаты б/л: стаж до 5 года	60%
стаж от 5 до 8 лет	80%
стаж свыше 8 лет	100%

Справочник 4. Учетные сведения о сотрудниках

Табельный номер работника	Ф. И. О. работника	Начало трудовой деятельности	Тарифный разряд	Членство в профсоюзе
0	Алтухов П.И.	03.03.2003	11	да
1	Алябьева Л.И.	20.11.1999	12	да
2	Карамов В.З.	19.01.2006	14	да
3	Киньшин С.Н.	01.11.1998	14	нет
4	Колесник Т.В.	14.03.1995	14	да
5	Ломов Н.И.	14.08.1992	9	нет
6	Малютин И.С.	01.12.2000	14	нет
7	Мешкова Н.Т.	18.09.2004	9	нет
8	Милова Е.Ю.	10.10.1994	12	да
9	Несекин П.П.	14.03.1998	8	нет
10	Осколкова Н.Э.	20.11.2004	10	нет
11	Панов Ю.В.	05.01.1997	13	да
12	Перова И.А.	05.02.1999	9	да
13	Попов В.Н.	05.06.2004	12	нет
14	Птицын В.А.	15.08.1989	15	нет
15	Санин В.Б.	06.07.1991	14	нет

Таблица 1. Годовой табель учета рабочего времени

Месяц расчета зарплаты	Табельный номер работника	Ф.И.О.	Количество отработанных дней	Количество дней по болезни	Процент выданного аванса
январь	0	Алтухов П.И.	15
январь	1	Алябьева Л.И.	15		40%
январь	2	Карамов В.З.	12	3	20%
январь	3	Киньшин С.Н.	15
январь	4	Колесник Т.В.	15
январь	5	Ломов Н.И.	7
январь	6	Малютин И.С.	14	1
январь	7	Мешкова Н.Т.	14		30%
январь	8	Милова Е.Ю.	9
январь	9	Несекин П.П.	11
январь	10	Осколкова Н.Э.	15
январь	11	Панов Ю.В.	15
январь	12	Перова И.А.	14	1	17%
январь	13	Попов В.Н.	10
январь	14	Птицын В.А.	10	2
январь	15	Санин В.Б.	15
февраль	0	Алтухов П.И.	14	5
февраль	1	Алябьева Л.И.	19
февраль	2	Карамов В.З.	9	10
февраль	3	Киньшин С.Н.	19		15%
февраль	4	Колесник Т.В.	19
февраль	5	Ломов Н.И.	6	13	40%
февраль	6	Малютин И.С.	16	3
февраль	7	Мешкова Н.Т.	19		30%
февраль	8	Милова Е.Ю.	19
февраль	9	Несекин П.П.	18	1
февраль	10	Осколкова Н.Э.	17	2
февраль	11	Панов Ю.В.	17
февраль	12	Перова И.А.	16		15%
февраль	13	Попов В.Н.	16
февраль	14	Птицын В.А.	10	9
февраль	15	Санин В.Б.	19
март	0	Алтухов П.И.	22
март	1	Алябьева Л.И.	22
март	2	Карамов В.З.	12	10
март	3	Киньшин С.Н.	15	7
март	4	Колесник Т.В.	22
март	5	Ломов Н.И.	7	15
март	6	Малютин И.С.	16	6
март	7	Мешкова Н.Т.	22		30%
март	8	Милова Е.Ю.	19	3
март	9	Несекин П.П.	21
март	10	Осколкова Н.Э.	22
март	11	Панов Ю.В.	22		80%
1	2	3	4	5	6
март	12	Перова И.А.	17
март	13	Попов В.Н.	17
март	14	Птицын В.А.	10	10
март	15	Санин В.Б.	22
апрель	0	Алтухов П.И.	21		13%
апрель	1	Алябьева Л.И.	20	1
апрель	2	Карамов В.З.	11	10
апрель	3	Киньшин С.Н.	9	11
апрель	4	Колесник Т.В.	21
апрель	5	Ломов Н.И.	20		40%
апрель	6	Малютин И.С.	10	11	45%
апрель	7	Мешкова Н.Т.	18
апрель	8	Милова Е.Ю.	21
апрель	9	Несекин П.П.	21
апрель	10	Осколкова Н.Э.	5	15	20%
апрель	11	Панов Ю.В.	12
апрель	12	Перова И.А.	15		25%
апрель	13	Попов В.Н.	18
апрель	14	Птицын В.А.	21
апрель	15	Санин В.Б.	21