29
Лабораторная работа 5
Анализ реализации товаров
Цель: Автоматизация подведения итогов и графический анализ данных. Задание на лабораторную работу. Провести анализ реализации това-
ров за отчетный период, продемонстрировать результаты на гистограммах.
Порядок выполнения лабораторной работы Лист 9ИтогиТовары. Сделайте копию листа продаж на следующий
лист книги. Отсортируйте записи по товарам в порядке, задаваемом списком товаров. Инструментом Excel Данные – Итоги вычислите по каждому товару суммарную выручку и суммарное количество проданного в килограммах, образец см. на Рис. 20.
Рис. 20. Автоитоги по количеству и выручке товара
Добавьте в итоги количество продаж по каждому наименованию, наибольшую и наименьшую отпускную цену по каждому товару, самую большую и самую маленькую покупку по весу и в рублях, среднюю цену
30
реализации, средний вес одной покупки и среднюю стоимость одной продажи по образцу на Рис. 21.
Рис. 21. Пять итоговых функций по каждому товару
Отобразите итоги реализации товаров за отчетный период без детальной информации по каждой продаже, см. Рис. 22.
Рис. 22. Итоги по товарам
31
Отобразите общий итог по всему магазину: сколько было продаж, сколько килограммов овощей продано всего, на какую сумму и т.д. по всему магазину, образец см. на Рис. 23.
Рис. 23. Итоги по магазину
Верните итоговые суммы по количеству и выручке по каждому наименованию (Рис. 24).
Рис. 24. Итоговые суммы по количеству и выручке
Скройте все столбцы между столбцами Товар и Количество. Отсортируйте полученные итоги по выручке по убыванию. Самый выгодный товар, выручка от продаж которого составила наибольшую сумму, займет первую строку, см. Рис. 25.
Рис. 25. Отсортированные итоги со скрытыми столбцами
32
Лист 10Гистограмма. Скопируйте лист 9ИтогиТовары на следующий
ипостройте смешанную диаграмму: по основной оси (слева, см. Рис. 26) – гистограмма по количеству реализованного товара за отчетный период, на второй, вспомогательной оси (справа, см. Рис. 26) – график выручки по каждому товару.
Построение диаграммы с помощью мастера диаграмм. На первом шаге мастера выбираем тип диаграммы с вкладки нестандартные График|гистограмма2 – классическая смешанная гистограмма и график на двух осях. На втором шаге заказываем диапазон данных для отрисовки из трех столбцов: наименования товаров, количество и выручка, и четырех итоговых строк для каждого товара. Всего 12 ячеек, не считая скрытые. Образец диаграммы см. на Рис. 26, диапазон данных для диаграммы смотри на предыдущем рисунке (Рис. 25) ='10Гистограмма'!$D$9:$G$59. На третьем шаге мастера на вкладке Заголовки заполняем Название диаграммы: Всего реализовано товара; Ось Х (категорий): Наименования товаров; Ось Y (значений): Продано, кг; Вторая ось Y (значений): Выручка, руб. На странице Оси должны уже быть включены основная ось Х, основная ось Y
ивспомогательная ось Y. На вкладке Линии сетки заказываем основные по оси Y. Легенду отключаем. На странице Подписи данных заказываем в подписи значения. На четвертом шаге размещаем диаграмму на имеющемся листе 10Гистограмма.
Рис. 26. Образец оформления смешанной диаграммы
33
В режиме ручного редактирования доводим оформление диаграммы по образцу Рис. 26. Заголовок перемещаем в правый верхний угол, шрифт Times, 18 пт, ЖКЧ. Убираем серый фон и рамку области построения. Линии сетки изображаем бледным сиреневым штрихом (переходный цвет между синим и красным). Формат левой вертикальной оси (оси гистограммы): на вкладке Вид окрашиваем все элементы этой оси в темно-красный цвет вишни и заказываем штрихи основных делений на линии оси Внутрь; на вкладке Шкала, чтобы гистограмма занимала нижнюю половину области построения, продлеваем шкалу оси вверх – максимальное значение: 160, цена основных делений: 20; на вкладке Шрифт оформляем подписи меток делений шкалы оси – шрифт Arial, 11 пт, вишня; на вкладке Число убираем граммы из числового формата значений подписей меток делений шкалы оси. Название оси окрашиваем цветом вишня, шрифт Arial, 12 пт, ЖК и размещаем от нуля. Формат правой вертикальной оси (ось графика): вкладка Вид – как для левой оси, только цвета темно-синего индиго; на вкладке Шкала отступаем снизу для гистограммы, задав минимальное значение: -2000, прореживаем шкалу, увеличивая цену основных делений до 500; Шрифт – Arial, 11 пт, индиго; на вкладке Число убираем копейки в числовом формате и убираем отображение отрицательных значений маской «? ??0" р.";;? ??0" р.";». Название оси закрашиваем индиго, шрифт Arial, 12 пт, ЖК и размещаем от нуля. Название горизонтальной оси шрифт Arial, 12 пт, ЖК, убираем штрихи всех делений шкалы на линии оси. Формат ряда гистограммы – вкладка Параметры – Ширина зазора между прямоугольниками: 5. Через форматы точек данных – вкладка Вид закрашиваем прямоугольники овощными цветами; подписи значений размещаем на цветных прямоугольниках маркеров. Формат ряда графика – вкладка Вид – выбираем цвет линии синий, маркер полный минус, синий, размера 40 пт. Подписи значений ряда графика – 11 пт, в рублях без копеек. Поясняющий текст «Максимальная выручка» и «Максимальная продажа» на выносках с полупрозрачной заливкой фона цвета хелп. Маркеры рядов, выноски и область диаграммы оформляем тенью.
Форма отчетности по лабораторной работе 5. Лист 9ИтогиТовары,
лист 10Гистограмма.