Расчет скидок по одной покупке

Технология выполнения задания

1. Откройте файл-заготовку.

2. Откройте лист Покупка_1. Цветом выделены ячейки, в которые следует ввести формулы для расчетов.

3. Установите масштаб 75%, чтобы таблица товаров полностью находилась в зоне видимости листа.

4. В столбце F17:F44 вычислите стоимость приобретенных товаров (Цена*Кол-во).

5. Дайте имя Без_скидки диапазону F17:F44.

6. В ячейке J3 вычислите накопленную сумму покупки для скидки по дисконтной карте (ДК): накопленная сумма по дисконтной карте из ячейки на листе Накопленные суммы +сумма по всем товарам из текущей закупки за исключением пиломатериалов (не по группе 1).

='Накопленные суммы'!J3+СУММЕСЛИ(B17:B44;"<>1";F17:F44))

Обратите внимание! Под термином «предыдущий лист» подразумевается предыдущая покупка. Для того, чтобы смоделировать историю покупок, введен лист Накопленные суммы, расположенный перед листом Покупка_1) с нулевым значениями накопленных сумм.

7. В ячейках J6 и J7 по аналогичным формулам вычислите накопленные суммы по сантехнике (группа 3) и метизам (группа 4), используя для одноименные ячейки на предыдущем листе.

Для ячейки J6: =' Накопленные суммы'!J6+СУММЕСЛИ(B17:B44;3;F17:F44)

8. В ячейке N3 вычислите % скидки по дисконтной карте - выбор из 3-х вариантов: 0%, 2% или 5% в зависимости от накопленной суммы.

Для ячейки N3: =ЕСЛИ(J3<D4;0%;ЕСЛИ(J3>=D5;E5;E4))

Здесь используются относительные ссылки на исходные данные, т.к. введенные формулы не предполагается куда-либо копировать.

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

=ЕСЛИ(N3>0;"да";"нет")

10. В диапазон G17:G44 перенесите дисконтную скидку из ячейки N3 на все позиции покупки кроме пиломатериалов.

Для ячейки G17:

=ЕСЛИ(B17<>1;$N$3;0)

11. В диапазоне H17:H44 вычислите стоимость покупок с учетом скидки по дисконтной карте, т.к. по условиям она действует до всех других скидок.

Для ячейки H17:

=F17-F17*G17

12. В ячейке J4 вычислите накопленную сумму покупки для скидки на пиломатериалы. Т.к. по условию задания после начисления скидки на пиломатериалы накопленная сумма сбрасывается, то для суммирования берется сброшенная сумма: сброшенная сумма из ячейки J5 на предыдущем листе Накопленные суммы +сумма по всем позициям пиломатериалов (п/м) из текущей закупки (только по группе 1).

Для ячейки J4:

='Накопленные суммы'!J5 +СУММЕСЛИ(B17:B44;1;F17:F44)

(см. пояснения в п.5)

13. В ячейке N4 вычислите % скидки на пиломатериалы:

=ЕСЛИ(И(H14="да";N4>=D6);E6;0)

Обратите внимание! Скидки суммируются только для владельцев дисконтных карт, что и учитывается в условии (H14="да")

14. Т.к. скидка по пиломатериалам действует только на текущую покупку и сбрасывает накопленную сумму, то в ячейку J5 введите формулу обнуления (сброса) суммы из ячейки F65, если скидка состоялась.

Для ячейки J5: =ЕСЛИ(N4<>0;0; J4)

15. В ячейках N5 и N6 по формулам, аналогичным формуле, описанной в п.13, определите проценты скидок по сантехническим товарам и метизам соответственно.

16. В ячейке N7 по дате покупки определите % скидки на товары для ремонта, которые действуют только в летние месяцы (5<номер месяца<9):

=ЕСЛИ(И(МЕСЯЦ(B14)>5;МЕСЯЦ(B14)<9);E9;0%)

17. В диапазон I17:I44 перенесите скидку из ячейки N4 на все позиции, относящиеся к пиломатериалам (группа 1).

Для ячейки I17: =ЕСЛИ(B17=1;$N$4;0)

18. Аналогичным образом перенесите скидки по сантехническим товарам (группа 3), метизам (группа 4) и на товары ля ремонта (группа 2) из ячеек N5, N6 и N7 в диапазоны J17:J44, K17:K44 и L17:L44 соответственно.

19. В диапазон M17:M44 введите формулу суммирования % всех скидок (пиломатериалы, сантехника, метизы, товары для ремонта.

20. В диапазоне N17:N44 рассчитайте окончательную стоимость товаров по всем позициям списка.

Для ячейки N17: =H17-H17*M17

21. Дайте имя Со_скидкой диапазону N17:N44.

22. В ячейках J8 и J9 вычислите исходную суммарную стоимость по диапазону Без_скидки и стоимость со скидками по диапазону Со_скидкой соответственно.

23. В ячейке N66 вычислите общий процент скидки по всей покупке:

=(J8-J9)/J8