- •Тема 4.3. Встроенные функции табличного процессораexcel Цели и задачи изучения темы:
- •Оглавление
- •Тема 4.3. Встроенные функции табличного процессора excel 1
- •4.3.1. Общие сведения о функциях
- •Правила построения формул с помощью Мастера функций:
- •4.3.2. Категории функций
- •Математические функции
- •Функция слчис()
- •Функция суммесли()
- •Типы логических выражений
- •Функции категории Дата и время
- •Функции категории Ссылки и массивы
- •4.3.3. Статистические функции
- •Статистические функции с условиями
- •4.3.4. Логические функции
- •Сложные логические выражения
- •Функция и()
- •Функция или()
- •Использование вложенных функций если()
- •4.3.5. Элементы интерфейса Excel 2007 для работы с функциями
- •Практикум 4.3. Использование встроенных функций для решения экономических задач
- •Задание 1. Вкладка Формулы и справочная система по функциям
- •Задание 2. Штрафы за отходы
- •Задание 3. Трехступенчатый прогрессивный налог
- •Технология построения формулы с вложенными функциями
- •Задание 4. Абонемент
- •Задание 5. Статистика
- •Задание 6. Расчет отпускных
- •Задание 7. Простой 6-ступенчатый налог
- •Задание 8. Сложный прогрессивный налог
- •Задание 9. Расчет «больничного»
- •Задание 10. Сравнение вкладов
- •Задание 11. Покупка
- •11_1. Расчет скидок по одной покупке
- •11_2. Определение правильности работы формул
- •11_3. Исследование изменения уровня скидок на примере 3-х покупок
Задание 6. Расчет отпускных
В задании предстоит рассчитать отпускные для сотрудника. Отпускные зависят от среднего заработка. На первый взгляд, алгоритм расчета среднего заработка для отпуска прост. Однако на практике нужно учитывать много нюансов. Для расчета отпускных необходимо сначала определить расчетный период. Согласно трудовому законодательству это 12 календарных месяцев, предшествующих уходу сотрудника в отпуск.
Далее следует подсчитать сумму выплат, начисленных сотруднику за это время. В нее включаются все выплаты, предусмотренные системой оплаты труда, независимо от источника их финансирования.
Делением суммы выплат на 12, а потом на 29,4 (среднемесячное число календарных дней) определяется средний дневной заработок. Сумму отпускных можно рассчитать, умножив среднедневной заработок на число календарных дней отпуска. Но из суммы выплат должно быть исключено все, что оплачивалось «по-среднему» - командировочные, оплата по больничным листам, отпускные и пр., а из расчетного периода - дни, за которые производились подобные начисления.
Т.к. все нюансы трудно учесть в учебной задаче, упростим ситуацию, представив молодого сотрудника, который за расчетный период не присутствовал на рабочем месте только во время предыдущего планового отпуска.
Задание выполняется на листе «Расчет отпускных» файла-заготовки. В процессе выполнения задания будут использованы ранее освоенные функции, а также функции категории Дата и Время.
Технология выполнения задания
Откройте файл-заготовку.
Откройте лист Расчет отпускных. Цветом выделены ячейки, в которые должны быть введены формулы для расчетов.
В ячейки B10 иF10 введите формулы, определяющие номер месяца по дате начала и окончания отпуска соответственно:
Для B10: =МЕСЯЦ(B12)
В ячейку D12 введите формулу, определяющую количество дней в месяце начала отпуска. Для определения числа дней можно использовать 2 функции:
функция КОНМЕСЯЦА(B12;0) определит дату последнего дня в заданном ячейкой B12 месяце (0 в качестве 2-ого аргумента обеспечивает это);
функция ДЕНЬ() вычленит из полученной даты число, обозначающее последний день месяца, а это число является одновременно количеством дней в этом месяце.
Т.о. формула для D12: =ДЕНЬ(КОНМЕСЯЦА(B12;0)).
Введите в ячейку H12 аналогичную формулу, определяющую количество дней в месяце окончания отпуска.
В ячейку E12 введите формулу, определяющую количество отработанных дней в месяце, в котором начался отпуск: из количества календарных дней в месяце вычитаются календарные дни отпуска в данном месяце («последняя дата месяца» - «дата начала отпуска» +1).
Пример для E12: =D12-(C12-B12+1)
По аналогичной формуле в ячейке I12 подсчитайте количество отработанных дней в месяце окончания отпуска.
В ячейках C18:N18, используя функцию ЕСЛИ(), определите признаки полного/неполного рабочего месяца: если номер месяца совпадает с номером месяца начала ИЛИ конца отпуска, то признак - прочерк, в противном случае – 1.
Пример для C18: =ЕСЛИ(ИЛИ(C16=$B$10;C16=$F$10);"-";1).
В ячейке B22 по определенным в предыдущем пункте признакам при помощи функции СЧЕТЕСЛИ() посчитайте количество полных месяцев.
В ячейке C22 просуммируйте все виды заработков за полностью отработанные месяцы, т.е. с признаком «1» (функция СУММЕСЛИ()):
Пример для C22: =СУММЕСЛИ(C18:N18;1;C17:N17)
В ячейке D22 просуммируйте заработки за неполные месяцы:
Пример для D22: =$N$7/D12*E12+=$N$7/H12*I12
В ячейке E22 определите количество календарных рабочих дней в расчетном периоде, просуммировав усредненное число календарных дней в полностью отработанных месяцах и число отработанных дней в неполных месяцах:
Пример для E22: =$E$7*B22+E12+I12
В ячейке F22 определите среднедневной заработок как частное от деления суммарного заработка за весь расчетный период на количество отработанных дней.
В ячейке G22 рассчитайте отпускные, как произведение среднедневного заработка на количество календарных дней в отпуске.
Сохраните файл с выполненным заданием