Табличный процессор microsoft excel
 Скачать 1.83 Mb.
|
Практическое задание № 8«Создание автоматизированных расчетных систем в Excel» Цель: получить навыки создания автоматизированных расчетных систем в Excel на примере системы по начислению заработной платы. Исходя из исходных данных (учетные сведениях о сотрудниках, ко- личество отработанных дней, налоговые ставки и др.) и алгоритма расчета необходимо создать в табличном процессоре систему взаимосвязанных таблиц для автоматизированного расчета заработной платы. Для упрощения системы все расчеты производятся на условном примере, т.е. формы таблиц и алгоритм расчета не в полной мере соответ- ствуют действительности. Технология выполнения В табличном процессоре MS Excel создать и сохранить в рабочую папку книгу с именем Зарплата. На первом этапе в созданной книге необходимо оформить и заполнить таблицы с исходными данными. Все таблицы должны быть расположены на отдельных листах, наглядно оформ- лены (обрамление, заливка цветом, размер шрифт). Все денежные значения должны быть отображены с двумя знаками после запятой, стаж – с одним знаком. Все листы должны иметь краткое название отражающее содержание таблиц (например, раб_дни, ТС, Спр_данные, Уч_сведения, табель_год, табель_мес, РПВ, Расч_лист и т.д.). На всех листах необходимо зафиксировать области: всю таблицу или шапку таблицы и подлежащее. Исходные данные Таблица 1. Количество рабочих дней в месяце
Таблица 2. Тарифный справочник
Таблица 3. Базовые показатели для расчета заработной платы
Таблица 4. Учетные сведения о сотрудниках
Таблица 5. Годовой табель учета рабочего времени
На следующем этапе создаются расчетные таблицы. Таблица 6 «Табель учета рабочего времени за месяц» На основании данных таблицы 5 «Годовой табель учета рабочего времени» и, используя мастер сводных таблиц, сформировать сводную таблицу (Рис. 55). Вкачестве исходных данныхдля построения сводной таблицы - следует выбрать (выделить) все ячейки таблицы 5 «Годовой табель учета рабочего времени» (с шапкой). Макет сводной таблицы: Фильтр отчета: Месяц расчета зарплаты Названия столбцов: Табельный номер работника Сумма Значения: Сумма по полю Количество отработанных дней, Сумма по полю Количество дней по болезни. Название строк: Σ Значения (перенести из области Названия столбцов)  Рис. 55. Примерный вид сводной таблицы. Таблица 7 «Расчетно-платежная ведомость» На основании данных табл. 1-4 и 6 рассчитать таблицу 7 «Расчетно-платежная ве- домость» (Рис. 56). Месяц расчета зарплаты [ссылка на ячейку с названием месяца в табл. 6]. Дата расчета зарплаты [выбирается из табл. 1 согласно месяцу расчета зарплаты (тек. табл.)]. В MS Excel для решения данной задачи необходимо использо- вать функцию из категории «Ссылки и массивы» - ВПР. Синтаксис функции ВПР: ВПР ("Искомое_значение" – ссылка на месяц расчета зарплаты (в этой табли- це); "Таблица" - все ячейки справочника 1 "Количество рабочих дней в месяце"; "Но- мер_столбца" - номер столбца, в котором находится дата расчета зарплаты (вводится вручную или определяется с помощь функции СТОЛБЕЦ ссылкой на одну ячейку в шапке таблицы по определяемому столбцу), чтобы функция ВПР искала точное соот- ветствие искомому значению, необходимо в поле "Интервал_просмотра" ввести 0 (ЛОЖЬ). Количество рабочих дней в месяце [выбирается согласно месяцу рас- чета зарплаты (табл.7) из табл. 1] (аналогично предыдущему показателю). Таблица 7. Расчетно-платежная ведомость
Рис. 56. Примерный вид таблицы 7 «Расчетно-платежная ведомость» Табельный номер работника [вводится пользователем («вручную»)]. Ф.И.О. работника [выбирается из справочника 4 «Учетные сведения о со- трудниках отделения» согласно табельному номеру работника с использованием функ- ции ВПР]. Синтаксис функции ВПР: ВПР ("Искомое_значение" - ссылка на табельный номер работника (в текущей таблице); "Таблица" - все ячейки справочника 4 "Учетные сведения о сотрудниках от- деления"; "Номер_столбца" - номер столбца, в котором находятся фамилии работни- ков; "Интервал_просмотра" - 0. Тарифный разряд [выбирается из табл. 4 согласно табельному номеру ра- ботника с использованием функции ВПР] (аналогично предыдущему показателю). Тарифный коэффициент [выбирается из табл. 2 согласно тарифному разря- ду работника с использованием функции ВПР]. Синтаксис функции ВПР: ВПР ("Искомое_значение" - ссылка на тарифный разряд работника (в тек. таблице); "Таблица" - все ячейки табл. 2; "Номер_столбца" - номер столбца, в котором находят- ся тарифный коэффициент; "Интервал_просмотра" - (0) ЛОЖЬ. Трудовой стаж определяется на дату расчета зарплаты от даты начала тру- довой деятельности. [В MS Excel для решения приведенной задачи можно использовать функцию из категории «дата и время» ДНЕЙ360. Начальная дата – дата начала тру- довой деятельности текущего работника - выбирается с помощью функций ВПР из табл. 4; конечная дата – дата расчета зарплаты. Полученное выражение разделить на 360 (дней в году)]. Функция ДНЕЙ360 определяет количество дней между двумя да- тами на основе 360-дневного года (двенадцать 30-дневныхмесяцев). Синтаксис функции (ДНЕЙ 360 (ВПР))/360: начальная дата – ВПР ("Искомое_значение" - ссылка на соответствующую ячейку текущей таблицы, "Таблица" - все ячейки табл. 4, "Номер_столбца" - номер столбца, в котором находится дата начала трудовой деятельности, Интервал_просмотра - 0 (Ложь)); конечная дата – дата расчета (абсолютная ссылка на соответствующую ячейку этой таблицы); метод: не указывать. При необходимости формат ячейки полученного значения (дата) переведите в число] Процент оплаты больничного листа определяется исходя из стажа ра- ботника по данным табл. 3. Для этого используется функция ЕСЛИ из категории «Ло- гические». Функция ЕСЛИ определяет одно значение из двух возможных, если условие выполняется - значение ИСТИНА и если условие не выполняется – значение ЛОЖЬ. В данном случае, так как условия три (стаж < 5; стаж < 8; стаж > 8 лет), функцию ЕСЛИ следует использовать дважды, вложив одну в другую. Синтаксис функций ЕСЛИ (ЕСЛИ): ЕСЛИ 1-го уровня (Логическое выражение: стаж текущего работника (ссылка на со- ответствующую ячейку текущей таблицы) < 5; Истина: абсолютная ссылка на про- цент до 5 лет (табл. 3); Ложь: вложенная функция ЕСЛИ 2-го уровня); ЕСЛИ 2-го уровня (Логическое выражение: стаж текущего работника (ссылка на соответствующую ячейку этой таблицы) < 8; Истина: абсолютная ссылка на про- цент от 5 до 8 лет); Ложь: абсолютная ссылка на процент свыше 8 лет ( табл. 3). Оклад [минимальная зарплата (абсолютная ссылка на соответствующую ячей- ку справочника 3 «Базовые показатели для расчета заработной платы») * тарифный ко- эффициент]. Начислено, руб.: Зарплата [оклад / количество рабочих дней в месяце (абсолютная ссылка на соответствующую ячейку в этой таблице) * количество отработанных дней (выбирается с помощью функции ГПР из табл. 6)]. Синтаксис функции ГПР: ГПР (Искомое_значение - ссылка на табельный номер работника (в тек. таблице); Таблица - абсолютная ссылка на табл. 6 (выделяются 3 строки таблицы, начиная со строки с табельными номерами); Номер_строки – 2 (номер строки, в которой находит- ся количество отработанных дней; Интервал_просмотра - (0) ЛОЖЬ). По больничному листу [оклад / количество рабочих дней в месяце (абсо- лютная ссылка на соответствующую ячейку этой таблицы)* количество дней по боль- ничным листам (выбирается с помощью функции ГПР из табл. 6 (строка 3))* процент оплаты по больничным листам (ссылка на соответствующую ячейку текущей табли- цы)]. Итого начислено - сумма всех начислений в этой таблице - [зарплата + по больничному листу]. Налог на доходы [зарплата * на процент налога на доходы (абсолютная ссылка на соответствующую ячейку табл. 3)]. Профсоюзный взнос [зарплата * процент профсоюзного взноса (абсолютная ссылка на соответствующую ячейку табл. 3)]. Рассчитывается только по работникам, состоящим в профсоюзе, поэтому следует воспользоваться функциями ЕСЛИ и ВПР. Синтаксис функции ЕСЛИ (ВПР): ЕСЛИ ( Логическое значение: членство в профсоюзе (ВПР ("Искомое_значение" - ссылка на табельный номер работника в текущей таблице, "Таблица" - все ячейки табл. 4, "Но- мер_столбца" - номер столбца, в котором отражается принадлежность к профсоюзу, Интервал_просмотра – 0) = «да»); Истина: зарплата * процент профсоюзного взноса (абсолютная ссылка на табл. 3); Ложь: 0). Итого удержано - сумма всех удержаний [подоходный налог + профсоюз- ный взнос]. К выдаче, руб. [итого начислено – итого удержано]. Таблица 8 «Расчетный лист заработной платы работника» Таблица 8 «Расчетный лист заработной платы работника» (Рис. 57) заполняется на основании данных таблиц 1-6 (алгоритм расчетов аналогичен использованному в таблице 7 «Расчетно-платежная ведомость»). Месяц расчета заработной платы – [ссылка на ячейку табл. 6]. Табельный номер работника – вводится пользователем («вручную») из списка табельных номеров (например: 3). Ф.И.О. работника [выбирается согласно табельному номеру работника (тек. табл.) с использованием функции ВПР из табл. 4].
Рис. 57. Примерный вид таблицы 8 «Расчетный лист заработной платы работника» Начало трудовой деятельности [выбирается согласно табельному номе- ру работника (тек. табл.) с использованием функции ВПР из табл. 4]. Стаж, лет [определяется с помощью функции ДНЕЙ360, используя даты начала трудовой деятельности и расчета заработной платы; разделить на 360]. Тарифный разряд [выбирается согласно табельному номеру работника (тек. табл.) с использованием функции ВПР из табл. 4]. Тарифный коэффициент [выбирается согласно тарифному разряду работни- ка (тек. табл.) с использованием функции ВПР из табл. 2]. ОКЛАД [минимальная зарплата (абсолютная ссылка на справочник 3 «Базовые показатели для расчета заработной платы») * тарифный коэффициент (в этой таблице)]. Отработано дней [выбирается согласно табельному номеру работника с ис- пользованием функции ГПР из табл. 6]. Дни по болезни [выбирается согласно табельному номеру работника с ис- пользованием функции ГПР из табл. 6].. НАЧИСЛЕНО - ВСЕГО, РУБ. [зарплата + по больничному листу (в этой таб- лице)]. Зарплата [оклад / количество рабочих дней в месяце * количество отрабо- танных дней]. По больничному листу [аналогично предыдущему]. УДЕРЖАНО - ВСЕГО, РУБ. [налог на доходы + профсоюзный взнос]. Налог на доходы [зарплата * на процент налога на доходы (табл. 3)]. Профсоюзный взнос [зарплата * процент профсоюзного взноса (табл. 3)]. Рассчитывается только по работникам, состоящим в профсоюзе, поэтому следует ис- пользовать функцию ЕСЛИ. К ВЫДАЧЕ, РУБ. [всего начислено – всего удержано)]. Диаграмма заработной платыНа основе данных табл. 7 построить круговую диаграмму следующе- го вида. Диаграмма заработной платы работников предприятия   Абрамова Е. В. Абросимов А. Ю. Бабич Е. Н. Баутин А. С.Рис. 58. Примерный вид диаграмма заработной платы работников Гистограмма сумм начислений и удержаний» На основе данных Итого начислено и Итого удержано таблицы 7 «Расчетно- платежная ведомость» построить обычную гистограмму следующего вида.  Рис. 59. Примерный вид выходной формы 4 «Гистограмма сумм начислений и удержаний» | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||