Задание Составить таблицы следующего вида: Справочник изделий Код изделия
Наименование изделия
Цена изделия, руб.
Справочник заказов Номер заказа
Код изделия
Дата заказа
Количество изделий
Нормативный срок исполнения заказа
Фактическая дата исполнения заказа
Заполнить первые две таблицы. При заполнении первой таблицы следует учесть, что код модели может повториться. С помощью справочных таблиц должна автоматически заполняться результирующая таблица. Номер заказа
Стоимость заказа
Штраф за задержку
Штраф за задержку исполнения заказа рассчитывается следующим образом: Если от 5 до 10 дней, то 3% от стоимости изделия Если от 10 до 30 дней, то 5% от стоимости изделия Если более 30 дней, то 10% от стоимости изделия Построить объемную столбиковую диаграмму стоимости заказа по кодам изделия, автоматически корректируемую при изменении данных в исходных
таблицах.
В
итоговой
таблице
наименование
продукции
упорядочить по алфавиту. Вывести номер заказа с минимальным штрафом за задержку.
2
Решение 1. Создание таблиц Переименуем текущий лист рабочей книги в лист с именем “Справочник изделий”. Для этого щелкнем правой клавишей мыши по ярлыку листа и в контекстном меню выберем пункт “Переименовать”. Введем в поле ярлычка новое название. Выделим диапазон ячеек A1:С11 и нажмем кнопку контекстного меню “Все границы”. Выделим диапазон ячеек A1:EС1 и выполним команду Формат/Ячейки. В диалоговом окне на вкладке “Выравнивание” в области “Отображение” установим флажок переносить по словам. Заполним шапку таблицы и внесем данные в таблицу, как показано на рисунке 1.
Рисунок 1. Таблица “Справочник изделий”.
3
Пользуясь технологией создания таблицы “Справочник изделий” на листах 2 и 3 создадим таблицы “Справочник заказов” и “Исполнение заказа” (рисунок 2 и 3). Таблицу “Справочник заказов” представим в заполненном виде.
Рисунок 2. Таблица “Справочник цен товара”.
4
Рисунок 3. Таблица “Исполнение заказа”. 2. Выполнение расчетов в таблице “Исполнение заказов” В ячейку А2 введем ссылку на лист “Справочник заказов” на номер заказа. А в ячейку В2 введем формулу для расчета стоимости заказа: ='Справочник заказов'!D2*ВПР('Справочник заказов'!C2;'Справочник изделий'!$A$2:$C$11;3;ЛОЖЬ) Штраф за задержку произведем по алгоритму, указанному в условии. Для этого введем вспомогательный столбец для расчета количества дней задержки исполнения заказа с помощью следующей формулы: =ДНЕЙ360 ('Справочник заказов'!F2 - 'Справочник заказов'!E2; ИСТИНА) Зная теперь, какова была задержка исполнения заказа в днях, определим штраф за задержку: 5
=B2*ЕСЛИ(D2>30;0,1;ЕСЛИ(D2>10;0,05;ЕСЛИ(D2>5;0,03;0))) Все эти формулы представлены для второй строки, для остальных строк достаточно растянуть формулы ниже, расчет будет произведен автоматически (рисунок 4).
Рисунок 4. Таблица “Расчет штрафа за задержку исполнения заказа”. 3. Построение объемной столбиковой диаграммы Для построения объемной столбиковой диаграммы стоимости товара создадим дополнительный лист Excel, который назовем “Диаграмма”. В ячейку А2 внесем ссылку на код изделия: ='Справочник изделий'!A2 Наименование изделия также вынесем в эту вспомогательную таблицу во второй столбец: =ВПР(A2;'Справочник изделий'!A2:B11;2;ЛОЖЬ) А общую стоимость по каждому изделию определим посредством формулы: 6
=СУММЕСЛИ('Справочник
заказов'!$C$2:$C$11;
Диаграмма!A2;
'Исполнение заказа'!$B$2:$B$11) Получим следующую таблицу (рисунок 5):
Рисунок 5. Таблица “Общая стоимость заказов по изделиям”. На основе этих данных строим круговую диаграмму, которая наглядно показывает какого товара по стоимости на складе больше всего (рисунок 6).
7
160000,00 140000,00 120000,00 100000,00 80000,00 60000,00 40000,00 20000,00
Платье детское
Халат женский
Шорты мужские
Брюки женские
Юбка женская
Р1
Пальто мужское
Костюм мужской, тройка
Костюм мужской, двойка
Брюки мужские
Пальто женское
0,00
Рисунок 6. Объемная столбиковая диаграмма стоимости заказов по изделиям. 4. Вывод номера заказа с минимальным штрафом за задержку Создадим область критериев: в ячейку G2 введем заголовок “Штраф меньше минимального”, а в ячейку G3 введем критерий отбора: =C2=МИН($C$2:$C$11) В диапазон ячеек I1:L1 скопируем имена столбцов из ячеек A1:D1. Выполним
команду
Данные/Фильтр/Расширенный
фильтр.
В
диалоговом окне установим флажок “Скопировать результат в другое место”, зададим исходный диапазон, диапазон условий и диапазон результатов (рисунок 7).
8
Рисунок 7. Диалоговое окно “Расширенный фильтр”. После нажатия кнопки ОК получим следующую выборку заказов с минимальной величиной штрафов.
Рисунок 8. Результат применения расширенного фильтра.
9