Тема: Динамика продаж.
Дата выдачи: 13 апреля – 15 апреля 2006 г.
Цель: Автоматизация подведения итогов и графический анализ данных.
Задание на лабораторную работу. Провести анализ динамики продаж за отчетный период, построить график продаж по дням, сделать прогноз на следующие пять дней.
Порядок выполнения лабораторной работы.
Скопировать все с листа 4проверка на лист 11ИтогиДни
Произвести сортировку продаж по дате по возрастанию (можно воспользоваться кнопкой на стандартной панели инструментов).
Командой итоги ( Данные / Промежуточные итоги) высчитать: суммарную стоимость проданного в рублях и суммарное количество проданного в килограммах, а также количество продаж за день (операция – количество).
В результате: количество продаж, суммарное количество килограммов проданных овощей и сумма выручки по всему магазину должны совпадать с общими итогами по товарам и покупателям.
Отобразить дневные итоговые суммы по количеству и выручке без детальной информации по каждой покупке (используя кнопки слева от таблицы). Скрыть пустые столбцы с наименованиями товара и ценами (команда: Выделить столбец – М* по выделенному столбцу – Скрыть).
Произвести сортировку по выручке по убыванию.
Вставить новый лист («12ГрафикТренд»).
Скопировать лист 11 на лист 12.
Построение диаграммы
Методические указания к построению графика. Промежуточные итоги добавили в список продаж строки с текстом «xx Декабрь Итог». Временной график строится по числовым значениям Даты,
а не по текстовым категориям.
Поэтому выделим из итоговых строк текст даты, преобразуем в числовое значение и подадим для построения графика. Для этого используем функцию:
ПСТР(текст; начало; количество) – возвращает заданное количество символов из текстовой строки, начиная с указанной позиции, и функцию;
ЗНАЧЕН(текст) – преобразует текст, представляющий число в одном из форматов Excel, в числовое значение.
На рисунке ниже показаны в столбце H выделенный из столбца C текст даты, в столбце I – преобразованный в числовую дату текст, ячейкам столбца I назначен числовой формат даты ДД МММ (рис. ).
Цветной обводкой на рисунке выделен диапазон данных для графика, который нужно подать в окне мастера диаграмм Исходные данные – Ряд.
Столбцу Н формат;
Выделим
ячейки G4:G72; Команда: Вставка – График
-
получим график:
М* по оси Х
Выбрать данные
Изменить
Выделим диапазон (I9;I72 – ОК! – ОК!)
М* по оси Х – Формат оси:
Минимальное значение 01. дек
Максимальное значение 04.01.13 (т.к. формат декабря обозначен 2012г.)
Закрыть
Диаграмма принимает вид:
М* по линии графика
Добавить линию тренда
Тип: Полиномиальная
Степень – 2
Вперёд на: 5 периодов
Формат ряда Выручка – Линия тип: мелкий штрих, цвет: синий, толщина: тонкая; Маркер тип: окружность, цвет: синий, фон: отсутствует, размер: 10 пт, с тенью.
Линия тренда показывает направление изменения в ряде данных, добавляется из свойств ряда: Right Click на ряде Выручка – Добавить линию тренда, Тип: Линейный, Построен на ряде: Выручка; Параметры тренда – Название аппроксимирующей кривой: Линейный тренд по выручке, Затем Right Click на построенной линии тренда – Формат линии тренда – Вид – Линия цвет: светло-коричневый, толщина: самая толстая Линейный тренд создается с использованием линейного уравнения y=kx+b. Аналогично добавляется полиномиальный тренд степени 2 с использованием уравнения y=c2x2+c1x+c0, прогноз вперед на 5 периодов, цвет линии – красный, тонкая.
Дизайн диаграммы дополнен голубой рамкой с тенью и светлой градиентной заливкой для всей области диаграммы. На правом поле размещен привлекающий внимание объект в виде стрелки, оформленный 3D-эффектами.
Лабораторная работа в Excel 7.