Задание 4. Абонемент
Согласно статистике во время финансового кризиса посещаемость фитнес- центров вне зависимости от статуса и стоимости абонементов сократилась на 20%. Поэтому все оздоровительные заведения сегодня вынуждены прибегать к весьма существенным скидкам. В предлагаемой задаче фитнес- центр предлагает следующую систему скидок:
скидки не суммируются, но начисляются по гибкой системе;
клиенту может быть представлено до 3-х скидок, расположенных в таблице исходных данных в порядке приоритетности: 1-ая скидка действует на всю сумму абонемента, последующие - на сумму за вычетом уже предоставленных скидок;
если фактическая сумма приобретенных услуг (за вычетом скидок) превышает некоторый порог (15000р.) клиенту предоставляется дополнительный бесплатный пакет услуг (по усмотрению администрации – массаж, занятия с тренером, SPA-процедуры и пр.)
если фактическая сумма приобретенных услуг (за вычетом скидок) превышает некоторый порог (25000р.) покупателю предоставляется клубная карта, дающая клиенту льготы и скидки на услуги компаний - участников программы из других областей (медицинские центры, рестораны, магазины).
Исходные данные представлены в виде списка клиентов (Рис. 26) с указанием («1» в соответствующей ячейке) положенных ему льгот:
Рис. 26. Исходные данные к заданию 4
В задании используются более сложные варианты аргументов рассмотренных ранее функций. Задание выполняется на листе «Фитнес» файла-заготовки.
Технология выполнения задания
Откройте файл-заготовку.
Откройте лист Фитнес. Цветным фоном выделены ячейки, которые следует заполнить результатами расчетов.
В ячейке H14 просуммируйте содержимое диапазона B14:G14 (функция СУММ() игнорирует незаполненные ячейки).
Распространите формулу суммирования на других клиентов, скопировав ее в нижестоящие ячейки.
В ячейке I14 посчитайте максимальное количество скидок, которые будут предоставлены клиенту. Т.к. центр предоставляет не более 3-х скидок, то если этот порог не превышен (ячейкаH14), сумма скидок не меняется, в противном случае - сумма заменяется на 3 (максимальное количество скидок).
Для реализации такой схемы следует использовать функцию ЕСЛИ():
В ячейку I14: =ЕСЛИ(H14>3;3;H14) (см. Рис. 27)
Рис. 27. Аргументы функции ЕСЛИ() для задания 4
Скопируйте формулу в нижестоящие ячейки.
Рассчитайте сумму клиента с учетом самой приоритетной скидки «Постоянный клиент» (столбец Сумм_1): если у клиента признак этой скидки есть, то сумма приобретенных услуг уменьшается на 10% (ячейка B13), иначе остается без изменения:
В ячейку K14: =ЕСЛИ(B14=1;J14*(1-$B$13);J14)
Скопируйте формулу в нижестоящие ячейки.
По аналогичной формуле посчитайте столбец Сумм_3. Рассчитывая суммы по первым трем скидкам, мы не заботились о проверке ограничения по количеству ссылок. Начиная со столбца Сумм_4 условие в функции ЕСЛИ() должно усложниться.
Рассчитайте сумму клиента с учетом скидки «Годовой абонемент» (столбец Сумм_4): если у клиента есть признак этой скидки И не превышено количество возможных скидок, то предыдущая сумма (M14) уменьшается на 25% (ячейкаE13), иначе остается без изменения:
В ячейку N14: =ЕСЛИ(И(E14=1;СУММ($B14:D14)<3);M14*(1-$E$13);M14)
Рис. 28. Сложное условие с вложенными И() и СУММ() в функции ЕСЛИ()
Как видно из рисунка (Рис. 28), аргумент Лог_выражениесодержит вложенные функции: логическую функцию И() и функцию суммирования со смешанной ссылкой ($B14), означающую, что начало диапазона суммирования при копировании формулы всегда будет в столбце B. Смешанные ссылки получаются неоднократным нажатием клавиши F4.
Скопируйте формулу в нижестоящие ячейки.
Распространите формулу в ячейку N14 вправо наO14 иP14 и подправьте ее по смыслу.
Заполните столбцы Сумм_5 и Сумм_6-окончат. для всех клиентов.
Рассчитайте общий процент скидки как отношение сэкономленной суммы («общая сумма» – «окончательная сумма») к общей сумме услуг.
Отметьте признаком «1» клиентов, которым полагаются дополнительные услуги:
В ячейку R14: =ЕСЛИ(P14>=$B$9;1;"-") и заполнить вниз.
Составьте список клиентов, которым следует оформить клубную карту:
В ячейку S14: =ЕСЛИ(P14>=$B$10;A14;"-") и заполнить вниз.
Сохраните документ с выполненным заданием.