Задание 4. Абонемент

Согласно статистике во время финансового кризиса посещаемость фитнес- центров вне зависимости от статуса и стоимости абонементов сократилась на 20%. Поэтому все оздоровительные заведения сегодня вынуждены прибегать к весьма существенным скидкам. В предлагаемой задаче фитнес- центр предлагает следующую систему скидок:

· скидки не суммируются, но начисляются по гибкой системе;

· клиенту может быть представлено до 3-х скидок, расположенных в таблице исходных данных в порядке приоритетности: 1-ая скидка действует на всю сумму абонемента, последующие - на сумму за вычетом уже предоставленных скидок;

· если фактическая сумма приобретенных услуг (за вычетом скидок) превышает некоторый порог (15000р.) клиенту предоставляется дополнительный бесплатный пакет услуг (по усмотрению администрации – массаж, занятия с тренером, SPA-процедуры и пр.)

· если фактическая сумма приобретенных услуг (за вычетом скидок) превышает некоторый порог (25000р.) покупателю предоставляется клубная карта, дающая клиенту льготы и скидки на услуги компаний - участников программы из других областей (медицинские центры, рестораны, магазины).

Исходные данные представлены в виде списка клиентов (Рис. 43) с указанием («1» в соответствующей ячейке) положенных ему льгот:

Рис. 43. Исходные данные к заданию 4

В задании используются более сложные варианты аргументов рассмотренных ранее функций. Задание выполняется на листе «Фитнес» файла-заготовки.

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

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

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

3. В ячейке H14 просуммируйте содержимое диапазона B14:G14 (функция СУММ() игнорирует незаполненные ячейки).

4. Распространите формулу суммирования на других клиентов, скопировав ее в нижестоящие ячейки.

5. В ячейке I14 посчитайте максимальное количество скидок, которые будут предоставлены клиенту. Т.к. центр предоставляет не более 3-х скидок, то если этот порог не превышен (ячейка H14), сумма скидок не меняется, в противном случае - сумма заменяется на 3 (максимальное количество скидок).

Для реализации такой схемы следует использовать функцию ЕСЛИ():

В ячейку I14: =ЕСЛИ(H14>3;3;H14) (см. Рис. 44)

Рис. 44. Аргументы функции ЕСЛИ() для задания 4

6. Скопируйте формулу в нижестоящие ячейки.

7. Рассчитайте сумму клиента с учетом самой приоритетной скидки «Постоянный клиент» (столбец Сумм_1): если у клиента признак этой скидки есть, то сумма приобретенных услуг уменьшается на 10% (ячейка B13), иначе остается без изменения:

В ячейку K14: =ЕСЛИ(B14=1;J14*(1-$B$13);J14)

8. Скопируйте формулу в нижестоящие ячейки.

9. По аналогичной формуле посчитайте столбец Сумм_3. Рассчитывая суммы по первым трем скидкам, мы не заботились о проверке ограничения по количеству ссылок. Начиная со столбца Сумм_4 условие в функции ЕСЛИ() должно усложниться.

10. Рассчитайте сумму клиента с учетом скидки «Годовой абонемент» (столбец Сумм_4): если у клиента есть признак этой скидки И не превышено количество возможных скидок, то предыдущая сумма (M14) уменьшается на 25% (ячейка E13), иначе остается без изменения:

В ячейку N14: =ЕСЛИ(И(E14=1;СУММ($B14:D14)<3);M14*(1-$E$13);M14)

Рис. 45. Сложное условие с вложенными И() и СУММ() в функции ЕСЛИ()

Как видно из рисунка (Рис. 45), аргумент Лог_выражение содержит вложенные функции: логическую функцию И() и функцию суммирования со смешанной ссылкой ($B14), означающую, что начало диапазона суммирования при копировании формулы всегда будет в столбце B. Смешанные ссылки получаются неоднократным нажатием клавиши F4.

11. Скопируйте формулу в нижестоящие ячейки.

12. Распространите формулу в ячейку N14 вправо на O14 и P14 и подправьте ее по смыслу.

13. Заполните столбцы Сумм_5 и Сумм_6-окончат. для всех клиентов.

14. Рассчитайте общий процент скидки как отношение сэкономленной суммы («общая сумма» – «окончательная сумма») к общей сумме услуг.

15. Отметьте признаком «1» клиентов, которым полагаются дополнительные услуги:

В ячейку R14: =ЕСЛИ(P14>=$B$9;1;"-") и заполнить вниз.

16. Составьте список клиентов, которым следует оформить клубную карту:

В ячейку S14: =ЕСЛИ(P14>=$B$10;A14;"-") и заполнить вниз.

17. Сохраните документ с выполненным заданием.