Материал: 5703
36
Лист 12ГрафикТренд. Постройте график суммарных дневных продаж за каждый день, образец смотри на Рис. 31. В этой диаграмме типа график по горизонтальной оси должны идти даты с 1 по 31 декабря и 4 дня следующего месяца. Для анализа тенденции и прогноза на следующие пять дней добавьте на график линии тренда, линейного и полиномиального степени 2 вперед на 5 дней.
Методические указания к построению графика. Промежуточные итоги добавили в список продаж строки с текстом «хх Декабрь Всего». Временной график строится по числовым значениям даты, а не по текстовым категориям. Поэтому выделим из итоговых строк текст даты, преобразуем в числовое значение и используем для построения графика, см. Рис. 30. Для этого используем функцию ПСТР(текст; начало; количество), которая возвращает заданное количество символов из текстовой строки, начиная с указанной позиции, и функцию ЗНАЧЕН(текст) – преобразует текст, представляющий число в одном из форматов Excel, в числовое значение. На Рис. 30 показаны в столбце J выделенный из столбца C текст даты, в столбце K – преобразованный в числовую дату текст, ячейкам столбца K назначен числовой формат даты «Д ММММ». Обводкой на рисунке выделен диапазон данных для графика, который необходимо подать в окне мастера диаграмм Исходные данные – Ряд – Значения: G14:G77, Подписи оси Х: К14:К77, Имя ряда: G9.
Рис. 30. Исходные данные для построения графика продаж
37
37
Рис. 31. График продаж и тренды с прогнозом на следующие четыре дня
38
Формат ряда Выручка: Линия – тип: мелкий штрих, цвет: синий, толщина: тонкая; Маркер – тип: окружность, цвет: синий, фон: отсутствует, размер: 10 пт, с тенью.
Линия тренда, показывающая направление изменения в ряде данных, добавляется из свойств ряда: Right Click на ряде Выручка – Добавить линию тренда, Тип: Линейный, Построен на ряде: Выручка; Параметры тренда – Название аппроксимирующей кривой: Линейный тренд по выручке, Прогноз вперед на: 5 периодов. Затем оформляем построенную линию тренда: Right Click на линии тренда – Формат линии тренда – вкладка Вид
– Линия цвет: светло-коричневый, толщина: самая мощная. Линейный тренд создается с использованием линейного уравнения y=kx+b. Аналогично добавляется полиномиальный тренд степени 2 с использованием уравнения y=c2x2+c1x+c0, прогноз вперед на 5 периодов, цвет линии – красный, тонкая.
Дизайн диаграммы дополнен голубой рамкой с тенью и светлой градиентной заливкой для всей области диаграммы. На правом поле размещен привлекающий внимание объект в виде стрелки, оформленный 3Dэффектами.
Форма отчетности по лабораторной работе 6. Лист 11ИтогиДни,
лист 12ГрафикТренд.
Лабораторная работа 7
Фильтрация списков
Цель: Изучить средства фильтрации списков Excel автофильтрами. Задание на лабораторную работу. Последовательно выполните на од-
ном листе фильтрацию списка, отвечая на поставленные вопросы. Ответы приведены.
Порядок выполнения лабораторной работы Лист 13АФильтр. Сделайте копию листа продаж на следующий лист
книги. Включите автофильтры (далее АФ) командой Данные – Фильтр – Автофильтр – появятся раскрывающие кнопки у заголовков полей, смотри Рис. 32. Отменяет текущий фильтр пункт Все в списке фильтров данного поля.
Выполните поиск вхождений по одному полю, то есть фильтрацию списка автофильтром на точное совпадение с одним из значений списка.
39
АФ 1. Отобразите записи по продажам одного конкретного товара, капусты. Для этого необходимо раскрыть список фильтров поля Товар и выбрать его название.
Рис. 32. Таблица данных с включенными автофильтрами
АФ 2. Отобразите покупки одного конкретного клиента, Оптова.
АФ 3. Отобразите тринадцатую продажу.
АФ 4. Отобразите продажи за 29 декабря.
АФ 5. Выберите продажи весом 10 кг.
Выполните поиск экстремальных значений в списке, максимальных или минимальных по одному полю инструментом Первые 10.
40
АФ 6. Отобразить 10 наиболее крупных по стоимости разовых покупок.
АФ 7. Выберите первые 10 продаж за отчетный период.
АФ 8. Отобразите 10 % наибольших по весу покупок.
АФ 9. Найдите три продажи по самой высокой отпускной цене.
АФ 10. Отобразите 20 % продаж самого дешевого товара.