Файл: Задание Прогнозирование данных.pdf

Задание
Прогнозирование данных
Цели:
1. Получить навыки прогнозирования с помощью скользящего среднего
2. Получить навыки прогнозирования с помощью функции регрессии
Задание: У вас есть отчет о ежедневном количестве звонков с жалобами на конкретный программный продукт за последние 10 дней.
Число
1 2
3 4
5 6
7 8
9 10
Количество звонков
10 11 10 12 13 13 13 10 16 17
Чтобы понять, существует ли какая-либо определенная тенденция поступления жалоб, вы создаете на основе средних данных о полученных звонках скользящее среднее.
Воспользуйтесь трехдневным скользящим средним, т.к. скользящее среднее за меньший период может не отразить тенденцию, а за больший период слишком сгладить ее.
Пояснение к выполнению:
I
способ: Прямое введение формулы
1.
Введите исходные данные в первые два столбца таблицы А и В (без заголовка)
2.
Чтобы получить скользящее среднее введите в ячейку С4 следующую формулу:
=СРЗНАЧ(B1:B3) (Формулы - Другие функции - Статистические - СРЗНАЧ).
3.
Затем с помощью маркера автозаполнения скопируйте эту формулу в ячейки
С5:С10.
4.
Постройте график изменения данных (количество звонков) и скользящего среднего.
5.
Добавьте форматирование графика: обозначьте названия осей (Макет - Название
Осей) и данных (Правый щѐлчок мышки по диаграмме - Выбрать данные -
Элементы легенды (ряды) - Изменить), назовите график (Макет - Название диаграммы). По желанию добавьте основные линии горизонтальной и (или)
вертикальной сетки по осям x и y (Макет - Сетка). Выберите наиболее подходящий размер графика для большей наглядности полученных результатов.
Примечание: в зависимости от версии Excel, название команд и их расположение на панели могут изменяться.
II
способ: Использование надстроек скользящего среднего
1.
Скопируйте входные данные на второй лист книги.
2.
В меню Данные выберите команду Анализ данных (если такой команды нет, включите ее через Параметры Excel - Надстройки — Пакет анализа - Перейти -
Выбрать необходимые надстройки - ОК).
3.
В появившемся окне выберите команду Скользящее среднее
4.
В поле Входной интервал введите данные о вашей базовой линии (выделите диапазон входных данных, столбец В).
5.
В поле Интервал введите количество дней – 3 дня, которые хотите включить в подсчет скользящего среднего.
6.
В поле Выходной интервал введите адрес ячейки, с которой хотите начать вывод
(С1).
7.
Поставьте значок Вывод графика.

8.
Нажмите ОК. (Значок #Н/Д означает - не хватает данных для подсчета среднего).
9.
Добавьте форматирование графика.
Этот способ имеет недостаток — прогноз создается на один временной период раньше.
III
способ: Составление прогнозов скользящего среднего с помощью диаграмм
1.
Скопируйте входные данные (столбец А и В) на третий лист книги.
2.
Выделите данные своей базовой линии (столбец В).
3.
Выберите Вставка - График, подберите необходимый тип графика.
4.
Вставьте график на текущий лист.
5.
Щелкните правой кнопкой на ряде данных диаграммы и из появившегося контекстного меню выберите команду Добавить линию тренда.
6.
В появившемся окне на вкладке Тип выберите тип Линейная фильтрация
7.
В окне Точки установите период вычисления скользящего среднего - количество дней – 5.
8.
Нажмите ОК.
9.
Добавьте форматирование графика.
Задание 2. Составление нелинейного прогноза с помощью функциирегрессии
I способ: Составление нелинейного прогноза с помощью функции Рост
1. Откройте новый лист Excel
2. Введите данные по продаже нового романа Дарьи Донцовой за 10 недель в первые два столбца таблицы, (таблица с заголовком вводиться в столбцы А и В).
Неделя
1 2
3 4
5 6
7 8
9 10
Объем продаж
1 1
4 5
10 19 34 55 64 120 2. Спрогнозируйте результаты продаж на 11-13 недели.
3. Для этого введите в столбец недель временные значения 11-13.
4. Выделите ячейки С2-С14. С помощью формулы массива введите в диапазон ячеек
С2-С14 формулу =РОСТ(В2:В11;А2:А11;А2:А14). После набора формулы нажмите клавиши Shift+Ctrl+Enter.
5. Постройте график на основе входных и прогнозируемых данных.
6. Добавьте форматирование графика.
II способ: Регрессивный анализ с помощью диаграмм.
1. Иногда возникает необходимость провести регрессивный анализ непосредственно на графике, без введения в рабочий лист значений для прогноза. Это можно сделать с помощью графической линии тренда. Скопируйте значения данных (столбец А и
В) на следующий лист.
2. Выделите диапазон данных (столбец В) и постройте график.
3. Щелкните правой кнопкой мыши по ряду данных и в контекстном меню выберите команду Добавить линию тренда, затем тип - Линейная
4. Щелкните на вкладке Параметры, в поле Вперед на введите число желаемых периодов - 5, на протяжении которых линия тренда будет проложена вперед.
5. Добавьте форматирование графика.