Задание 11. Покупка

Строительный бизнес в нашей стране набирает обороты. Вместе с ним развивается и инфраструктура, сопутствующая этому бизнесу – в частности магазины и оптово-розничные базы строительных материалов. Чтобы выжить в условиях конкуренции, надо создать особые условия для привлечения покупателей.

В настоящем задании предстоит произвести расчеты по оплате товаров с учетом всех видов скидок в супермаркете «Дачный рай».

Магазин представляет покупателям следующие виды скидок:

1. Дисконтные накопительные карты:

Накопленная сумма покупок, кроме пиломатериалов (руб)	Скидка по дисконтной карте
>=3000	2%
>=100000	5%

2. Разовые накопительные скидки на пиломатериалы:

Если накопленная сумма >= 100000 - скидка на текущую покупку, накопленная сумма обнуляется, действует для постоянных покупателей, т.е. при наличии дисконтной карты.

3. Накопительная скидка на сантехническую группу товаров:

Если накопленная сумма >=150000 руб. – постоянная скидка на группу 7% при наличии дисконтной карты.

4. Накопительная скидка на метизы:

Если накопленная сумма >=20000 руб. – скидка на метизы 10% при наличии дисконтной карты.

5. Летняя скидка на товары для ремонта:

3% независимо от суммы приобретенных товаров в летние месяцы (июнь, июль, август), действует для всех покупателей, независимо от наличия дисконтной карты.

Приоритет у дисконтной карты, последующие скидки действуют после нее.

Т.к. задача носит учебный характер, ассортимент товаров намеренно сведен до минимума.

Группы товаров индексированы:

1 – пиломатериалы (п/м);

2 – товары для ремонта (тов/рем);

3 – сантехнические товары (сан/т);

4 – металлоизделия (метизы).

Целью данного контрольного задания является комплексное применение ранее изученных функций и освоение работы с листами книги Excel, т.к. для накопления сумм по категориям товаров используются суммы по текущим покупкам с разных листов.

Задание состоит из 3-х частей:

• расчет скидок по одной покупке (11_1);

• определение правильности работы введенных формул (11_2);

• исследование изменения уровня скидок на примере 3-х покупок (11_3).

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

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

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

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