Задача 4.2. Оплата телефонных переговоров
Предметная область: расчет стоимости телефонных переговоров. Элементы предметной области: фамилии сотрудников, время начала переговоров, время их окончания.
Отношения:
размер оплаты за каждую полную или неполную минуту городских разговоров определяется по шкале:
Временной интервал
Тариф (грн)
С 8:00 до 18:00
0,36
С 18:00 до 23:00
0,22
С 23:00 до 8:00
0,18
стоимость отдельного разговора определяется как произведение соответствующего тарифа на продолжительность разговора в минутах.
Требования:
рассчитать стоимость 20 телефонных переговоров, проведенных 4 сотрудниками фирмы в разное время суток;
при вводе фамилий сотрудников использовать элемент управления Список средства Проверка данных;
на отдельном листе той же рабочей книги для каждого сотрудника подсчитать общую продолжительность переговоров за рассматриваемые сутки;
сделать графическую интерпретацию этих данных (круговая диаграмма).
Ограничение: если начало переговоров относится к одному временному интервалу, а окончание – к другому, то в расчете стоимости переговоров используется тариф, соответствующий временному интервалу начала переговоров.
Методические указания к решению
Основной единицей измерения времени в MS Excel являются сутки. Каждые сутки представляются последовательными десятичными значениями. Базовая дата, представляемая десятичным значением 1, – это 1.01.1900. Максимальное значение даты 2958465 представляет 31.12.9999. Когда в ячейку рабочего листа вводится дата, MS Excel сохраняет ее в виде десятичного значения, которое равно количеству дней между базовой и заданной датой.
Время суток – это десятичная дробь, представляющая часть суток между их началом (0:00) и заданным временем. Например, время 12:00 представляется значением 0,5. Назначая десятичные значения дням, часам, минутам и секундам, MS Excel позволяет выполнять сложные вычисления с датами и временем так же, как и с другими данными: значения даты и времени можно складывать и вычитать, а также использовать в других вычислениях. Например, чтобы определить число дней между двумя датами, можно вычесть одну дату из другой.
Чтобы определить количество часов, минут или секунд между двумя показаниями времени, записанных, например, в ячейках B2 (начальное время) и C2 (конечное время), необходимо воспользоваться следующими формулами:
Количество часов между двумя показаниями времени |
=(С2-В2) * 24 |
Количество минут между двумя показаниями времени |
= (С2-В2) * 24 * 60 |
Количество секунд между двумя показаниями времени |
= (С2-В2) * 24 * 60 * 60 |
Если переговоры начались до полуночи, а закончились после (С2<B2), приведенные формулы требуют уточнения. Продолжительность временного интервала, например, в минутах, при переходе временного интервала через 24:00 рассчитывается по формуле:
= (1 + С2 – В2) * 24 * 60.
Ввести данные согласно рис. 25.
|
A |
B |
C |
D |
E |
F |
1 |
РАСЧЕТ ОПЛАТЫ ЗА ТЕЛЕФОННЫЕ ПЕРЕГОВОРЫ |
|||||
2 |
|
Временной интервал |
|
|
|
|
3 |
|
Начало |
Окончание |
Тариф (грн) |
|
|
4 |
|
8:00 |
18:00 |
0,36 |
|
|
5 |
|
18:00 |
23:00 |
0,22 |
|
|
6 |
|
23:00 |
8:00 |
0,18 |
|
|
7 |
|
|
|
|
|
|
8 |
Фамилия |
Время начала |
Время окончания |
Продолжительность (мин) |
Тариф |
Стоимость |
9 |
Андреев |
|
|
? |
? |
? |
Рис. 25. Макет таблицы для задачи «Оплата телефонных переговоров»
Ввести формулу для расчета продолжительности переговоров в минутах (формат ячейки D9 – числовой):
=ЕСЛИ (C9<B9,1-B9+C9,C9-B9)*24*60.
Для автоматической подстановки тарифа согласно приведенной шкале используется следующая логическая конструкция:
ЕСЛИ время начала переговоров в интервале от 8:00 до 18:00,
ТО ТАРИФ 1,
ИНАЧЕ
ЕСЛИ время начала переговоров в интервале от 18:00 до 23:00,
ТО ТАРИФ 2,
ИНАЧЕ ТАРИФ 3.
Данная логическая конструкция кодируется с помощью формулы вида:
=ЕСЛИ (И (B9>=$B$4,B9<$C$4),$D$4,
ЕСЛИ (И (B9>=$B$5,B9<$C$5),$D$5,$D$6)).
Инструкция по вводу вложенной функции
Чтобы вставить функцию Б в качестве аргумента функции А, необходимо установить в курсор в соответствующее поле диалогового окна Аргументы функции и, нажав стрелку списка в строке формул, указать функцию Б (рис. 26).
Рис. 26. Технология ввода вложенной функции
Для продолжения ввода аргументов в функцию А требуется щелкнуть по ее имени в строке формул, чтобы вернуть соответствующее ей диалоговое окно Аргументы функции (рис. 27).
Рис. 27. Ввод остальных аргументов исходной функции
Для ввода второй функции ЕСЛИ установить курсор в поле значение_если_ложь, щелкнуть по стрелке списка и выбрать в открывшемся списке ЕСЛИ.