Основная часть (max 7 баллов)
Создайте электронную таблицу (рабочую книгу MSExcel) из 3-х рабочих листов и назовите рабочие листы:
Таблица, Выборки, Доп.
Сохраните рабочую книгу в папке H:\Lab2\
На листе Таблица создайте таблицу расчета стоимости ремонта бытовой техники (названия изделий могут быть вымышленными):
Скидка за ремонт изделия рассчитывается в зависимости от стоимости ремонта (диапазон B1:C4). Заполнить столбецСкидка за ремонт, руб.на основе правил вычисления скидки из диапазонаB1:C4, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейкиC2:С4 – проценты будут меняться при проверке работы). Правила вычисления скидки:
если стоимость ремонта < 500 руб., то скидка составит 5% от стоимости ремонта;
если стоимость ремонта от 500 до 1500 руб. (500<=…<1500), то скидка составит 7% от стоимости ремонта;
если стоимость ремонта >= 1500 руб., то скидка составит 10% от стоимости ремонта.
Составить формулу расчета значений в столбце Дата готовности.
Заполнить столбец Срок хранения готового неоплаченного изделия, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса. При этом если изделие оплачено до срока готовности, то срок хранения равен 0.
Сумма за хранение готового неоплаченного изделия рассчитывается в зависимости от стоимости ремонта и количества дней хранения готового изделия (диапазон F1:G4). Заполнить столбецСумма за хранениена основе правил начисления суммы из диапазонаF1:G4, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейкиG2:G4 – проценты будут меняться при проверке работы). Правила начисления пени:
если стоимость ремонта < 1000 руб. или срок хранения <= 5 дней, то пени за хранение составит 2% в день от стоимости ремонта;
если стоимость ремонта >= 1000 руб. и срок хранения от 5 до 10 дней, то пени за хранение составит 3% в день от стоимости ремонта;
если стоимость ремонта >= 1000 руб. и срок хранения >= 10 дней, то пени за хранение составит 5% в день от стоимости ремонта.
Вычислить общую сумму за ремонт и хранение изделий, составив соответствующие формулы в столбце Общая сумма.
Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.
С помощью соответствующих формул подсчитайте:
итоговые суммы по пятому и двум последним столбцам (строка 19);
максимальный и минимальный сроки хранения готовых изделий, средний размер суммы за хранение (результаты запишите в строках 21-23);
количество клиентов, заказавших ремонт стиральной машины (результат запишите в строке 24).
Подпишите полученные результаты.
Сделайте копию основной таблицы (диапазон A7:L17) ниже на этом же листе (вставьте только значения), отформатируйте копию таблицы. Отсортируйте копию таблицы по наименованию изделий по убыванию и стоимости ремонта в порядке возрастания (это одна «двойная» сортировка). Перед отсортированной копией поместите соответствующий заголовок.
Скопируйте исходную таблицу (диапазон A7:L17) на листВыборки(вставьте только значения). Отформатируйте копию таблицы. Сделайте выборки с помощью Фильтра по следующим условиям (результаты выборки скопируйте ниже верхней таблицы на листеВыборкии перед каждой выборкой поместите соответствующий заголовок):
Выбрать из таблицы записи с общей суммой от 1000 до 2000 руб.
Выбрать из таблицы записи по ремонту утюгов.
Сохраните Рабочую книгу.
Предъявите результат преподавателю.
Дополнительная часть ВАРИАНТ 5 (max 3 балла, принимается преподавателем только при сданной основной части)
Скопируйте исходную таблицу (диапазон A7:L17) на листДоп(вставьте только значения). Отформатируйте копию таблицы.
Ниже сделайте выборку с помощью Расширенного фильтра по следующему условию (это одно сложное условие): выбрать из таблицы все записи по изделиям, название которых начинается на «М» и срок хранения которых превышает 50 дней, либо все записи по изделиям, название которых начинается на «С» и общая сумма < 2000 руб., отобразить в результате выборки столбцы в следующем порядке: Номер заказа, Наименование изделия, Общая сумма, Дата готовности, Дата оплаты, Срок хранения(подобрать условия так, чтобы фильтр «нашел» хотя бы одну запись).
Сохраните Рабочую книгу.
Предъявите результат преподавателю.
Домашнее задание
Подготовиться к выполнению лабораторной работы по MSExcel. Знать и уметь использовать:
построение графиков функций;
сводные таблицы;
подбор параметра;
решение уравнений;
работа с макросами.
1 семестр 2013-2014 г.г. Румянцева Е.Л., каф. ИПОВС