3.21. Команда "частичная сумма"

Файл $umesli

При решении практических задач часто возникает необ­ходимость суммировать данные при выполнении нескольких условий. Для этих случаев принято использовать математичес­кую функцию СУММЕСЛИ. Примеры использования этой функции даны в файле Master.

Однако в программе Excel есть другая, более удобная, возможность решать подобные задачи. Имеется в виду команда Частичная сумма.

Задание 1. Открыть рабочий лист "Пример". Изучить процесс создания формулы, по которой сделан расчёт в ячейке F4, и выполнить самосто­ятельно аналогичные расчёты для ячеек F13 и F15.

В ячейке F4 просуммированы данные из блока ячеек D4:D18, которые соответствуют фамилии "Балаганов" из блока ячеек В4:В18 и дню недели "понедельник" из блока ячеек С4:С18.

Ввод формулы в ячейку выполнен с помощью опции Части­чная сумма в такой последовательности:

1. Сервис V Мастер V Частичная сумма.

2. Должно появиться диалоговое окно, в котором выполняются четыре шага.

3. Задастся адрес всей таблицы с данными B3:D18.

4. Нажимается кнопка Далее.

5. Выбирается имя столбца, по которому суммируются данные — Выторг.

6. В окне Столбец выбирается имя столбца Фамилия.

7. В окне Оператор устанавливается оператор Знак равенства.

8. В окне Значение выбирается фамилия продавца - Балаганов.

9. Нажимается кнопка Добавить условие.

10. В окне Столбец выбирается имя стопбца День.

11. В окне Оператор усгяиашинзпася оператор Знак равенства.

12. В окне Значение выбирается день недели Понедельник.

13. Нажимается кнопка Добавить условие.

14. Нажимается кнопка Далее

15. Устанавливается опция Копировать формулу в отдельную ячейку.

16. Нажимается кнопка Далее.

17. Задаётся адрес ячейки, в которую вводится формула - F4.

18. Нажимается кнопка Готово.

Задание 2. Открыть рабочий лист "Задания". Только д своего варианта задания записать в Таблицу 2 (столбец " формулы для автоматического определения числа продан! автомобилей конкретным продавцом.

Задание 3. (Контрольное). В одну из формул внести такие j изменения, чтобы появилась возможность автоматически под- ' считать результаты работы для всех вариантов задания.

3.22. Логическая функция если Файл Nalog

Задание 1. Создать бланк платёжной ведомости с автома­тическим подсчетом суммы "К выдаче" сразу после ввода суммы в колонку "Начислено". Условно предполагается, что из начисленной суммы вычитается только величина подоходного налога, которая рассчитывается по следующим данным:

Зарплата	Подоходный налог рассчитывается по такому условию
до 17 грн	0
от 17,01 до 85 грн	10% от суммы, превышающей 17 грн
от 85,01 до 170 грн	6,80+15% от суммы, превышающей 85 грн
от 170,01 до 1020 грн	19,55+20% от суммы, превышающей 170 грн
от 1020,01 до 1700 грн	189,55+30% от суммы, превышающей 1020 грн
Свыше 1700 грн	393,55+40% от суммы, превышающей 1700 грн

Для решения задачи необходимо создать расчётную таблицу и ввести в неё формулы. Как обычно, формулы вводятся в ячейки первой строки таблицы с последующим их размножением на осталь­ные ячейки.

В ячейке ЕЗ должна подсчитываться величина подоходного налога с любой суммы, введённой в ячейку СЗ. Если бы процент на­лога был постоянным для любой суммы, то задача решалась бы прос­тым вычислением процента. Но в стране действует ступенчатая шка­ла для определения налога в соответствии с принципом "Чем больше заработал, тем больше налог". Учесть это условие можно, если при­менить логическую функцию ЕСЛИ, причём воспользоваться тем обстоятельством, что допускается ввод до семи вложенных друг в друга логических условий.

Создание сложных формул выполняется следующим образом:

1. Вызвать диалоговое окно функции Если.

2. Записать первое логическое условие.

3. Записать значение Истина для первого условия.

4. Перевести курсор в окон Ложь.

5. Выполнить щелчок по кнопке Если в левом верхнем углу окна.

6. Записать второе логическое условие.

7. Записать значение Истина для второго условия.

8. Перевести курсор в окон Ложь.

9. Выполнить щелчок мышью по кнопке Если во второй раз. И так далее...

Последовательность действий при выполнении задания:

1. Для удобства ввода адресов ячеек в сложную формулу, рекомендуем начать создание сложной формулы с заполнения Таблицы Исходные данные числами, которые даны в условии задачи.

2. Создать бланк платежной ведомости в следующем виде:

	A	B	C	D	E
1
2	№	Фамилия	Начислено	Удержано	К выдаче
3
4

3. В ячейку ЕЗ ввести формулу =C3-D3 для подсчёта разности между "Начислено" и "Удержано".

4. В ячейку DЗ ввести формулу

=ЕСЛИ(СЗ<=$Н$6;0;ЕСЛИ(СЗ<=$Н$7;$J$7*(СЗ-$Н$6);ЕСЛИ(СЗ<=$Н$8;$J8*(СЗ-$Н$7)+$I$8;ЕСЛИ(СЗ<=$Н$9;$J$9*(СЗ-

$Н$8)+$I$9;ЕСЛИ(СЗ<=$Н$ 10;$J$10*(C3-

$H$9)+$I$10;$J$11*(C3-$H$10)+$I$]1)))))

Дадим пояснение к выше записанной сложной формуле: Если в ячейку СЗ введена сумма менее той, которая b ячейку Н6, то налог равен нулю;

Если в ячейку СЗ введена сумма менее той, которая введена ячейку Н7, то налог подсчитываете* как произведение числа из ячейки $J$7 на разность содержимого ячеек СЗ и Н6.

Если в ячейку СЗ введена сумма менее той, которая введена в ячейку Н8, то налог подсчитывается как произведение числа нз ячейки $J$8 на разность содержимого ячеек СЗ и Н7 плюс число из ячеек $J$8.

Если в ячейку СЗ ведена сумма менее той, которая введена в ячейку Н9, то налог подсчитывается как произведение числа из ячейки $J$9 на разность содержимого ячеек СЗ и Н8 плюс число из ячеек $J$9.

Если в ячейку СЗ введена сумма менее той, которая введена в ячейку Н10, то налог подсчитывается как произведение числа из ячейки $J$10 на разность содержимого ячеек СЗ и 119 плюс число из ячеек $J$10

Для всех остальных сумм, введенных в ячейку СЗ, налог подсчитывается как произведение числа из ячейки $J$I1 на разность содержимого ячеек СЗ и H10 плюс число из ячейки $J$11.

5. Размножить формулы из строки три па несколько нижних

строк.

6. Проверить работоспособность расчетной таблицы путем ввода различных сумм в ячейку колонки Начислено.