2. Индивидуальная часть.
На листе «Заказы» ячейки В3:В14 заполняем в виде списка. Для этого выделяем эти ячейки, открываем вкладку «Данные», «Проверка данных». В открывшейся вкладке выбираем тип данных- список. В поле источник, возвращаемся на лист «Справочники» и выделяем там ячейки D2:D4. Нажимаем OK. Затем нажимаем курсором на ячейки В3:В14 и выбираем необходимые нам текстовые значения для каждой из этих ячеек.
Заполняем на листе «Заказы» ячейки G3:G14 с помощью логической функции ЕСЛИ И. Нам необходимо вывести «выполнено», если заказ доставлен в течение 3 дней и сумма выполнения не менее суммы заказа. В противном случае вывести «не выполнено». В итоге получаем формулу =ЕСЛИ(И(D3-C3<=3;F3>=E3);"выпонено";"невыполнено"). Вводим её в ячейку G3 и копируем в остальные ячейки по вертикали.
Далее необходимо заполнить на листе «заказы» ячейки H3:H14 с помощью функции ВПР. $. Пересчитать Сумма заказа, руб. по курсу в день заказа .Дата изменения курса должна быть ближайшей к дате заказа. Для этого открываем вкладку «Формулы», «Ссылки и массивы», где выбираем функцию ВПР. В открывшемся окне в строке «Искомое значение» выбираем на листе «Заказы» ячейку D3, так как искомым значением в данном случае является дата заказа. В строке таблица выбираем на листе «Справочники» А$2:В$5. Номер столбца-2, интервальный просмотр-1. в итоге получаем формулу: =E3/ВПР(заказы!C3;справочники!A$2:B$5;2;1). Где Е3- сумма заказа в рублях.
Далее на листе «Итоги» необходимо получить для каждого заказчика итоговые суммы заказов и выполнения заказов в рублях. Для этого копируем таблицу с листа «Заказы» на лист «Итоги». Выделяем таблицу. Открываем вкладку «Данные», «промежуточный итог». В открывшейся вкладке в строке «при каждом изменении в»- выбираем заказчики. В стоке «операция» выбираем сумма. «добавить итоги по» ставим галочки на «сумма заказа, руб.» и «сумма выполнения,руб.» нажимаем OK. Получаем такую таблицу:
На листе «Гистограмма» необходимо создать гистограмму на основании итогов полученных в предыдущем пункте. Для сворачиваем таблицу так , что бы остались видны только полученные итоги по каждому заказчику ( выбрав уровень 2 слева от таблицы). Получается следующая таблица:
Выделяем все ячейки, оставшиеся в таблице, удерживая клавишу Ctrl. Открываем вкладку «Вставка», «Гистограмма» и выбираем гистограмму с группировкой. Полученную гистограмму форматируем, выбрав 12 размер шрифта Time New Roman полужирный. Также присваиваем названия осям гистограммы, переименовываем легенду на « сумма заказа» и «сумма выполнения». Присваиваем имя самой гистограмме- «Итоги».
На листе «Фильтр» выводим сведения о заказах, у которых сумма заказа больше суммы выполнения. Для этого используем расширенный фильтр на вкладке «Данные».
Далее на этом же листе необходимо построить сводную таблицу, в которой вывести суммы заказов на каждую дату заказа, обеспечив выбор по заказчикам в области страниц. Для чего открываем вкладку «Вставка» , «Сводная таблица». В открывшемся окне в строке «Таблица диапазон»- выделяем таблицу на листе «Фильтр». Нажимаем OK. при этом открывается список полей сводной таблицы, где перемещаем в область «фильтр отчета»- поле «заказчики», «названия строк»- «дата заказа», а в область «значения»- поле «сумма заказа $». Полученную сводную таблицу форматируем.
Следующий шаг: средствами Word по результатам фильтрации подготовить и оформить рассылку писем заказчикам. Для этого открываем новый документ Microsoft Word, копируем таблицус листа «Фильтр» в документ Word, как электронную таблицу. Оформляем рассылку писем с помощью «пошагового мастера слияния» на вкладке рассылки.
Сохраняем книгу под именем заказы.