- •Тема 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-х покупок
Задание 3. Трехступенчатый прогрессивный налог
Всем известно, что в России используется плоская шкала для начисления подоходного налога – 13% независимо от доходов. Во многих странах используется прогрессивное налогообложение — система, при которой налоговые ставки увеличиваются по мере роста дохода налогоплательщика.
Рассмотрим действие 3-х ступенчатого простого подоходного налога, используемого в Германии. Исходными данными для расчетов будет таблица, определяющая % налога в зависимости от месячного заработка работника, переведенного в тыс. рублей. Расчеты будут производиться для 16-ти распространенных специальностей.
Основным инструментом для расчета 3-х ступенчатого прогрессивного налога в задании является логическая функция ЕСЛИ().
Задание выполняется на листе 3-х-ступенчатый прогр. налогфайла-заготовки.
Технология выполнения задания
Откройте файл-заготовку.
Откройте лист 3-х-ступенчатый прогр. налог. Цветным фоном выделены ячейки, которые следует заполнить результатами расчетов. На листе представлены исходные данные:
Курс евро
Ставки налога, соответствующие 3-м уровням, выраженные в рублях (данные взяты из Интернет-источников): при заработке до 23,7 тыс. руб. – налог 0%, при заработке от 23,7 до 161,49 тыс. руб. – налог 15%, при заработке более 161,49 тыс. руб. – налог 42%.
Таблица, в которой приведены названия некоторых профессий (столбец В) и месячный заработок в тыс. евро (столбец С).
В столбце D введите формулу перевода месячного заработка в тысячи рублей.
В столбце Eпри помощи функции ЕСЛИ определите, относится ли заработок к 1-му уровню налога (до 23,7 тыс. руб) по следующему правилу: если месячный заработок меньше максимального заработка для 1-ого уровня налогообложения, в ячейку заносится 1, в противном случае – 0.
Для ячейки E15 формула выглядит так: =ЕСЛИ(D15<$E$9;1;0)
В столбце Fопределите, относится ли заработок ко 2-му уровню. Для этого одновременно должны быть выполнены два условия: месячный заработок должен быть больше (либо равен) 23,7 тыс. руб. и меньше 161,49 тыс.руб. В этом случае в функции ЕСЛИ надо использовать составное логическое выражение.
В ячейке F15 формула выглядит так: =ЕСЛИ(И(D15>=$E$9; D15<$E$10);1;0)
Примечание. Для построения составного логического выражения используется логическая функция И(). Формула в ячейке состоит из функции ЕСЛИ и вложенной в нее функции И.
Технология построения формулы с вложенными функциями
Выделите ячейку, в которую надо ввести формулу.
Вставьте функцию ЕСЛИ. Откроется окно Аргументы функциидля функции ЕСЛИ.
Для выбора вложенной функции установите курсор в строке Логическое выражениеи раскройте список функций (Рис. 22).
Найдите и выберите функцию И. Откроется окно Аргументы функциидля функции И.
Введите логические выражения (Рис. 23). Не нажимайте кнопку ОК.
Для возвращения в окно аргументов функции ЕСЛИ щелкните на название функции в строке ввода (над таблицей). В открывшемся окне вы увидите составное логическое выражение с функцией И.
Введите недостающие аргументы функции ЕСЛИ.
Рис. 22
Рис. 23
В столбце Gопределите, относится ли заработок к 3-му уровню: если заработок больше 161,49 тыс. руб. то значение в ячейке равно 1, иначе – 0.
Для ячейки G15 формула выгляди так: =ЕСЛИ(D15>=$D$11;1;0)
В ячейках E32:G32 при помощи функции СУММ подсчитайте количество специальностей, соответствующих разным уровням налогообложения (суммирование нулей и единиц в столбцах даст нужный результат).
В столбце Hвведите формулу определения номера налогового уровня для каждой специальности по следующему правилу:
если значение ячейки в столбце Е равно 1, уровень равен 1;
если значение ячейки в столбце Fравно 1, уровень равен 2;
во всех остальных случаях уровень равен 3.
Логическая схема для определения уровней приведена на Рис. 24.
Чтобы проверить два условия и ввести 3 различных значения, надо использовать две функции ЕСЛИ – одна вложена в другую. При построении формулы можно проверять условие равенства 1 ячеек столбцов E,G,F.
Рис. 24
Для ячейки H15 формула выгляди так: =ЕСЛИ(Е15=1;1 ЕСЛИ(F15=1;2;3))
Примечание. Формулу вычисления номера уровня можно построить, используя другие логические выражения, но в любом случае придется использовать вложенные функции ЕСЛИ.
В столбце Iопределите по номеру уровня (ячейкаH15) процент подоходного налога и скопируйте формулу в нижестоящие ячейки. Т.к. шкала подоходного налога трехуровневая, понадобится использование 2-х вложенных функций ЕСЛИ. Логическая схема для построения формулы представлена на Рис. 25.
Рис. 25
Для ячейки I15: =ЕСЛИ(H15=1;$C$9;ЕСЛИ(H15=2;$C$10;$C$11))
В столбце Jопределите реальные налоговые отчисления в тыс. € по рассчитанному проценту отчислений: (месячный оклад)*(% отчислений).
Используя функцию СУММ, в ячейках C34 иJ34 подсчитайте суммарный месячный заработок работников и суммарные налоговые отчисления.
В ячейке C35 вычислите усредненный % налога по данной группе работников (отношение суммарного налога к суммарному заработку). Формат ячейки – процентный.
Сохраните выполненное задание.