Основная часть (max 7 баллов)

1. Создайте электронную таблицу (рабочую книгу MSExcel) из 3-х рабочих листов и назовите рабочие листы:

Таблица, Выборки, Доп.

Сохраните рабочую книгу в папке H:\Lab2\

2. На листе Таблица создайте таблицу для расчета сотового оператора со своими абонентами (см. рисунок). ФИО абонентов могут быть вымышленными.

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

4. Столбец Скидказаполнить на основе правил из диапазонаB1:D7, составив единую формулу «ЕСЛИ» для всех строк таблицы. В формуле обязательно использовать ячейкиC2:C7 иD2:D7 (их значения будут меняться при проверке работы), при необходимости использовать абсолютные адреса. Скидка рассчитывается по следующему принципу:

• если длительность исходящих звонков менее 60 минут, и при этом подключен пакет смс 50, то скидка 0%;

• если длительность исходящих звонков менее 60 минут, и при этом подключен пакет смс 100, то скидка 3%;

• если длительность исходящих звонков 60 и более минут, и при этом подключен пакет смс 50, то скидка 5%;

• если длительность исходящих звонков 60 и более минут, и при этом подключен пакет смс 100, то скидка 7%;

• если подключен пакет смс 300, то скидка 4% независимо от длительности звонков;

• во всех остальных случаях скидка 0%.

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

6. Столбец Рекомендуемая услугазаполнить на основе правил из диапазонаI1:J4, составив единую формулу «ЕСЛИ» для всех строк таблицы. В формуле обязательно использовать ячейкиJ1:J4 (их значения будут меняться при проверке работы), при необходимости использовать абсолютные адреса. Рекомендуемая услуга выбирается по следующему принципу:

• если счет за месяц менее 200 рублей, то рекомендовать услугу «Безлимитный Интернет»;

• если счет за месяц от 200 до 1000 рублей (200<…<=1000), то рекомендовать услугу «Домашний регион»;

• если счет за месяц более 1000 рублей, то рекомендовать услугу «Любимый номер».

7. Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.

8. С помощью соответствующих формул подсчитайте:

- итоговые суммы по всем столбцам (строка 22);

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

- количество клиентов с отрицательным итоговым балансом (результат запишите в строке 27).

Подпишите полученные результаты.

9. Сделайте копию основной таблицы (диапазон A10:L20) ниже на этом же листе (вставьте только значения), отформатируйте копию таблицы. Отсортируйте копию таблицы сотового оператора по длительности звонков по убыванию и по фамилиям в порядке возрастания (это одна «двойная» сортировка). Перед отсортированной копией поместите соответствующий заголовок.

10. Скопируйте исходную таблицу (диапазон A10:L20) на листВыборки(вставьте только значения). Отформатируйте копию таблицы. Сделайте выборки с помощью Фильтра по следующим условиям (результаты выборки скопируйте ниже верхней таблицы на листеВыборкии перед каждой выборкой поместите соответствующий заголовок):

1. Выбрать из таблицы записи со скидкой от 100 до 200 рублей.

2. Выбрать из таблицы записи по абонентам, у которых подключен пакет смс 50.

11. Сохраните Рабочую книгу.

Предъявите результат преподавателю.

Дополнительная часть ВАРИАНТ 2 (max 3 балла, принимается преподавателем только при сданной основной части)

Скопируйте исходную таблицу (диапазон A10:L20) на листДоп(вставьте только значения). Отформатируйте копию таблицы.

Ниже сделайте выборку с помощью Расширенного фильтра по следующему условию (это одно сложное условие): выбрать из таблицы все записи по абонентам с фамилией, начинающейся на «П», у которых подключен пакет смс 100, либо все записи по абонентам с фамилией, начинающейся на «Д», у которых положительный баланс на конец месяца, отобразить в результате выборки столбцы в следующем порядке: Номер телефона, ФИО, Подключен пакет смс, Баланс на начало текущего месяца, Оплачено в текущем месяце, Баланс счета на конец текущего месяца (подобрать условия так, чтобы фильтр «нашел» хотя бы одну запись).

Сохраните Рабочую книгу.

Предъявите результат преподавателю.

ВАРИАНТ 3