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

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

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

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

2. На листе Таблица создайте таблицу для расчетов автозаправочной станции с клиентами (см. рисунок).

3. Заполните столбец Цена за литрформулами, позволяющими отображать цену в соответствии с видом заправляемого топлива на основе диапазонаH1:I4. При необходимости используйте абсолютные адреса.

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

5. В зависимости от суммы за топливо и имеющихся на клиентской карте баллов, за каждую заправку АЗС начисляет клиентам бонусные баллы по правилам, представленным в диапазоне B1:C4. Заполнить столбецНачислено за заправку, балловна основе правил начисления баллов из диапазонаB1:C4, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейкиC2:С4 – проценты будут меняться при проверке работы). Баллы начисляются по следующему принципу:

• если сумма за топливо < 1500 руб. и на карте < 200 баллов, то начисляется 5 баллов;

• если сумма за топливо < 1500 руб. и на карте >= 200 баллов, то начисляется 10 баллов;

• если сумма за топливо >= 1500 руб., то начисляется 15 баллов.

6. Столбец Количество баллов после заправки заполнить расчетными формулами.

7. Скидка рассчитывается в зависимости от количества заправленного топлива на основе правил расчета (диапазон E1:F5). Заполнить столбецСкидкаединой формулой «ЕСЛИ» для всех строк таблицы. В формуле обязательно использовать ячейкиF2:F5. Правила расчета скидки заключаются в следующем:

• если заправлено <= 30 литров, то скидка составляет 0% от суммы за топливо;

• если заправлено от 30 до 50 литров (30<…<=50), то скидка составляет 5% от суммы за топливо;

• если заправлено от 50 до 100 литров (50<…<100), то скидка составляет 7% от суммы за топливо;

• если заправлено >= 100 литров, то скидка составляет 10% от суммы за топливо.

8. Столбец Сумма к оплатерассчитывается как сумма за топливо минус скидка.

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

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

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

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

• количество клиентов, заправивших ДТ (результат запишите в строке 24).

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

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

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

1. Выбрать из таблицы записи с количеством баллов после заправки от 500 до 1000.

2. Выбрать из таблицы записи по заправке дизельного топлива.

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

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

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

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

Ниже сделайте выборку с помощью Расширенного фильтра по следующему условию (это одно сложное условие): выбрать из таблицы все записи по топливу, начинающемуся на «А», в которых заправлено >= 50 литров, либо все записи по заправкам свыше 100 литров, отобразить в результате выборки столбцы в следующем порядке: Номер карты, Покупатель, Заправлено литров, Вид топлива, Начислено за заправку, Сумма к оплате(подобрать условия так, чтобы фильтр «нашел» хотя бы одну запись).

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

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

ВАРИАНТ 5