В таблице 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 (дней в году)].
5. Процент оплаты больничного листа определяется соответственно стажу. Для этого используется функция ЕСЛИ из категории «Логические».
6. Оклад [минимальная зарплата (абсолютная ссылка на соответствующую ячейку справочника 3 «Базовые показатели для расчета заработной платы») * тарифный коэффициент].
Начислено, руб.:
7. Зарплата [оклад / количество рабочих дней в месяце (абсолютная ссылка на соответствующую ячейку в этой таблице) * количество отработанных дней (выбирается с помощью функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени»)].
8. По больничному листу [оклад / количество рабочих дней в месяце (абсолютная ссылка на соответствующую ячейку этой таблицы)* количество дней по больничным листам (выбирается с помощью функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени» {по строке 3})* процент оплаты по больничным листам (ссылка на соответствующую ячейку этой таблицы)].
9. Итого начислено - сумма всех начислений в этой таблице - [зарплата + по больничному листу].
Удержано, руб.
10. Аванс [оклад * процент выданного аванса (выбирается с помощью функции ГПР из промежуточной формы 1 «Месячный табель учета рабочего времени» {по строке 4})].
11. Подоходный налог [зарплата * на процент походного налога (абсолютная ссылка на соответствующую ячейку справочника 3 «Базовые показатели для расчета заработной платы»)].
12. Профсоюзный взнос [начислено всего (в этой таблице) * процент профсоюзного сбора (абсолютная ссылка на соответствующую ячейку справочника 3 «Базовые показатели для расчета заработной платы»)]. Рассчитывается только по работникам, состоящим в профсоюзе, поэтому следует воспользоваться функциями ЕСЛИ и ВПР.
13. Итого удержано - сумма всех удержаний [аванс + подоходный налог + профсоюзный взнос].
14. К выдаче, руб. [итого начислено – итого удержано].
На третьем этапе разработки АИС создаются выходные формы (таблицы и диаграммы).
Выходная форма 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-ти разрядной сетке. АИС отвечает требованиям, предъявляемым к автоматизированным информационным системам: алгоритм ее функционирования, спроектированные формы таблиц соответствуют фактическим, форматы данных логически обоснованы.
Список использованной литературы.