Учет междугородных переговоров и расчет их стоимости согласно тарифам в MS Excel

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

Электронный документ должен точно (шрифты, форматы, размеры ячеек, типы данных, выравнивание, исходные данные) соответствовать представленному на рисунках. Он состоит из четырех страниц, на первой из которых (с именем Документ) располагается основной документ — Стоимость переговоров, на второй (лист Счет)—счет за междугородные переговоры, на третьей и четвертой (листы Тарифы, Абоненты)—справочные данные.

Шаг 1

В основной документ вручную заносятся следующие сведения:

- номер абонента и код города;

- время начала разговора в формате часы:минуты;

- длительность разговора в минутах.

Автоматически определяются и рассчитываются в основном документе:

- название города;

- стоимость разговора по следующему правилу:

1. если начало разговора не попадает в промежуток льготного времени (справочник Льготы), то разговор оплачивается по полному тарифу (справочник Тарифы);

2. если начало разговора попадает в промежуток льготного времени, то стоимость разговора снижается в соответствии с размером льготы, указанной на странице Тарифы.

Необходимо предусмотреть контроль правильности вносимых в документ номера телефона и кода города (колонки А, В). Если номер телефона или код города отсутствует в справочниках Тарифы и Абоненты, вместо названия города должно появляться сообщение Ошибка! Для этой цели можно использовать функции ЕОШИБКА и ИЛИ.

Шаг 2

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

В случае, если в счет внесен ошибочный номер телефона, вместо фамилии владельца должно появляться сообщение Номер не существует.

Шаг 3

Справочники формируются полностью вручную.

Шаг 4

После создания электронного документа в области основного документа последовательно выделите, а затем скопируйте в другую область электронной таблицы строки, соответствующие:

- только звонкам в Москву;

- звонкам во все города, кроме Вологды и Архангельска;

- разговорам длительностью от 5 до 15 мин.;

- звонкам, сделанным в течение льготного времени;

- разговорам абонента с заданным номером с Москвой длительностью не менее 10 мин. и с Архангельском длительностью не менее 5 мин.

Примечание:

При копировании в другую область электронного документа графа Код города должна быть автоматически исключена из копии.

Шаг 5

Скопируйте документ Стоимость переговоров на другую страницу электронной таблицы и выполните сортировку строк документа различным образом:

- по возрастанию номера абонента;

- в первую очередь по убыванию номера абонента, во вторую - по на-званию города (в алфавитном порядке);

- в первую очередь по коду города, во вторую - по стоимости разговора, в третью - по номеру абонента.

Шаг 6

В копии документа Стоимость переговоров организуйте для каждого абонента, не занося в электронный документ дополнительных формул, подсчет суммарной длительности переговоров и суммарной их стоимости (используйте возможности меню Данные, Итоги).

Шаг 7

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

Шаг 8

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

- соотношение суммарных времен переговоров со всеми городами, с которыми велись переговоры (несколько диаграмм разного типа);

- соотношение времен переговоров в льготное и остальное время для каждого города и городов друг с другом (несколько диаграмм разного типа).

Стоимость переговоров:

Номер абонента | Код города | Название города | Время разговора | Длительность (мин.) | Стоимость (у. е.)

553313 818 Архангельск 20:00 10 800

775638 95 Москва 11:25 7 840

515475 812 С.-Петербург 7:50 12 504

553313 817 Вологда 14:00 3 300

775638 812 С.-Петербург 17:45 9 540

553313 95 Москва 21:50 6 504

517480 816 Новгород 6:10 1 52,5

776019 818 Архангельск 9:35 8 640

553313 812 С.-Петербург 22:40 3 126

775638 818 Архангельск 11:05 10 800

517480 812 С.-Петербург 8:00 4 168

776019 95 Москва 12:20 15 1800

776019 95 Москва 12:55 22 2640