Главная | Обратная связь | Поможем написать вашу работу!

Прогнозирование результата

Задание 1

Тема: Прогнозирование с помощью скользящего среднего

Задание: У вас есть отчет о ежедневном количестве звонков с жалобами на конкретный программный продукт за последние 10 дней.

Число
Количество звонков

Чтобы понять, существует ли какая-либо определенная тенденция поступления жалоб, вы создаете на основе средних данных о полученных звонках скользящее среднее.

Воспользуйтесь трехдневным скользящим средним, т.к. скользящее среднее за меньший период может не отразить тенденцию, а за больший период слишком сгладить ее.

Пояснение к выполнению:

I способ: Прямое введение формулы

1. Введите исходные данные в первые два столбца таблицы (без заголовка)

2. Чтобы получить скользящее среднее введите в ячейку С4 следующую формулу: =СРЗНАЧ(B1:B3) (Формулы - Другие функции - Статистические - СРЗНАЧ).

3. Затем с помощью средств автозаполнения скопируйте эту формулу в ячейки С5:С10.

4. Постройте графики изменения данных (количество звонков) и скользящего среднего.

5. Добавьте форматирование графика: обозначьте названия осей (Макет - Название Осей) и данных (Правый щёлчок мышки по диаграмме - Выбрать данные - Элементы легенды (ряды) - Изменить), назовите график (Макет - Название диаграммы). По желанию добавьте основные линии горизонтальной и(или) вертикальной сетки по осям x и y (Макет - Сетка). Выберите наиболее подходящий размер графика для большей наглядности полученных результатов.

II способ: Использование надстроек скользящего среднего

1. Скопируйте входные данные на второй лист книги.

2. В меню Данные выберите команду Анализ данных (если такой команды нет, включите ее через Параметры Excel - Надстройки — Пакет анализа - Перейти - Выбрать необходимые надстройки - ОК).

3. В появившемся окне выберите команду Скользящее среднее

4. В поле Входной интервал введите данные о вашей базовой линии (укажите диапазон входных данных).

5. В поле Интервал введите количество дней, которые хотите включить в подсчет скользящего среднего.

6. В поле Выходной интервал введите адрес ячейки, с которой хотите начать вывод.

7. Поставьте значок Вывод графика.

8. Нажмите ОК. (Значок #Н/Д означает - не хватает данных для подсчета среднего).

9. Добавьте форматирование графика: обозначьте названия осей (Макет - Название Осей) и данных (Правый щёлчок мышки при выборе диаграммы - Выбрать данные - Элементы легенды (ряды) - Изменить), назовите график (Макет - Название диаграммы). По желанию добавьте основные линии горизонтальной и(или) вертикальной сетки по осям x и y (Макет - Сетка). Выберите наиболее подходящий размер графика для большей наглядности полученных результатов.

Этот способ имеет недостаток — прогноз создается на один временной период раньше.

III способ: Составление прогнозов скользящего среднего с помощью диаграмм

1. Скопируйте входные данные на третий лист книги.

2. Выделите данные своей базовой линии.

3. Выберите Вставка - График, подберите необходимый тип диаграммы.

4. Вставьте диаграмму на текущий лист.

5. Щелкните правой кнопкой на ряде данных диаграммы и из появившегося контекстного меню выберите команду Добавить линию тренда.

6. В появившемся окне на вкладке Тип выберите тип Линейная фильтрация

7. В окне Точки установите период вычисления скользящего среднего - количество дней.

8. Нажмите ОК.

Задание 2

Тема: Прогнозирование с помощью функции регрессии

Задание: Внесите результаты ваших наблюдений в два первых столбца таблицы на четвертый лист книги. Составьте прогноз, используя функцию регрессии.

Пояснение к выполнению:

I способ: Составление линейных прогнозов с помощью функции Тенденция

1. Выделите ячейки С1:С10 и введите следующую формулу: =ТЕНДЕНЦИЯ(В1:В10;А1:А10) в ручную или с помощью команды Формулы - Другие функции - Статистические - ТЕНДЕНЦИЯ. Диапазон В1:В10 — диапазон входных данных, Excel выражает как Известные значения У. Диапазон ячеек А1:А1O - диапазон временных интервалов, Excel выражает как Известные значения X.

Ввод формулы заканчивается нажатием комбинации клавиш <Ctrl+Shift+Enter>, только в этом случае формула воспринимается как формула массива. Не снимая выделения диапазона щелкните в строке формул и нажмите комбинацию клавиш. Если вы этого еще не сделали.

В данном примере функция Тенденция вычисляет прогноз, основанный на связи между фактическими результатами наблюдений и числами 1-10, которые могут отражать либо дни, либо месяцы.

2. Введите в ячейку A11 число 11, а в ячейку С11 формулу: =ТЕНДЕНЦИЯ(В 1:В10;А1:А10;А11).

Значение 11 в ячейке A11 является Новым значением X и определяет время, которое связывается с перспективной оценкой. Введя в ячейку A11 большее значение, вы можете спрогнозировать данные более позднего временного момента.

3. Существует возможность одновременного прогнозирования данных для нескольких новых временных моментов. Введите в ячейки А11:А24 числа 11-24. Выделите диапазон ячеек С11:С24 и введите формулу массива: =ТЕНДЕНЦИЯ(В1:В10;А1:А10;А11:А24), где А11-А24 - новые значения X.

4. Постройте диаграмму на основе входных и прогнозируемых данных.

5. Добавьте форматирование графика: обозначьте названия осей (Макет - Название Осей) и данных (Правый щёлчок мышки при выборе диаграммы - Выбрать данные - Элементы легенды (ряды) - Изменить), назовите график (Макет - Название диаграммы). По желанию добавьте основные линии горизонтальной и(или) вертикальной сетки по осям x и y (Макет - Сетка). Выберите наиболее подходящий размер графика для большей наглядности полученных результатов.

II способ: Составление нелинейного прогноза с помощью функции Рост

1. Введите данные по продаже нового романа Дарьи Донцовой за 10 недель в первые два столбца таблицы на новый лист книги, (таблица с заголовком вводиться в столбцы А и В).

Неделя
Объем продаж

2. Спрогнозируйте результаты продаж на 11-13 недели.

3. Для этого введите в столбец недель временные значения 11-13.

4. С помощью формулы массива введите в диапазон ячеек С2-С14 формулу =РОСТ(В2:В11;А2:А11;А2:А14). Обратите внимание, что в отличие от функции Тенденция в функции Рост в качестве новых значений X указываться весь временной интервал, а не только новые значения.

5. Постройте диаграмму на основе входных и прогнозируемых данных.

6. В случаях, когда вы имеете дело с резким ростом данных, удобнее использовать экспоненциальную шкалу для просмотра зависимости. Постройте на основе тех же данных график, выбрав на вкладке Нестандартные логарифмический график.

7. С помощью функции Тенденци я можно получить такие же результаты, как и с помощью функции Рост, если использовать ее логарифмические значения. Выделите диапазон ячеек D2-D14 и введите следующую формулу массива:

=ЕХР(ТЕНДЕНЦИЯ(LN(В2:В11);А2:А11;А2:А14)), где LN - математическая функция Логарифм.

8. Постройте на основе этих данных график и сравните его с прогнозом, полученным с помощью функции РОСТ.

III способ: Регрессивный анализ с помощью диаграмм.

1. Иногда возникает необходимость провести регрессивный анализ непосредственно на графике, без введения в рабочий лист значений для прогноза. Это можно сделать с помощью графической линии тренда. Скопируйте значения данных на следующий лист.

2. Выделите диапазон данных и постройте график.

3. Щелкните правой кнопкой мыши по ряду данных и в контекстном меню выберите команду Добавить линию тренда, затем тип - Линейная

4. Щелкните на вкладке Прогноз, в поле Вперед на введите число желаемых периодов, на протяжении которых линия тренда будет проложена вперед.

5. Добавьте форматирование графика: обозначьте названия осей (Макет - Название Осей) и данных (Правый щёлчок мыши при выборе диаграммы - Выбрать данные - Элементы легенды (ряды) - Изменить), назовите график (Макет - Название диаграммы). По желанию добавьте основные линии горизонтальной и(или) вертикальной сетки по осям x и y (Макет - Сетка). Выберите наиболее подходящий размер графика для большей наглядности полученных результатов.

Задание 3

Прогнозирование результата

Подбор параметра

Предположим, что известен размер вклада, который будет помещен в банк на определенный срок под заданный процент. Необходимо установить во сколько раз увеличиться вклад за указанный срок, а также определить сумму выплат в конце периода.

1. Откройте новую книгу.

2. Введите в столбец А названия параметров, в столбец В значения параметров: Размер вклада (определите по-своему усмотрению), Срок вклада (20 лет), Процентная ставка (5%).

3. В следующей строке рассчитайте Коэффициент наращивания, который будет определяться по формуле: =(1+процентная ставка)^срок вклада). Внимание!!! Переменные в формулы вводите с помощью ссылки на ячейки, где лежат соответствующие значения переменных.

4. Рассчитайте Сумму выплат по формуле: =(размер вклада * коэффициент наращивания).

5. Предположим, что через 20 лет мы хотим получить ровно полмиллиона. Процентная ставка не меняется, значит нужно определить, каким должен быть первоначальный вклад. Используйте для его определения функцию Подбор параметра.

6. Выделите ячейку с суммой выплат. Возьмите команду Данные - Анализ «что-если» – Подбор параметра.

7. В открывшемся окне адрес выделенной ячейки будет автоматически введен в поле Установить в ячейке, в поле Значение введите необходимое значение параметра (500000), в поле Изменяя значение ячейки введите ссылку на ячейку со значением суммы первоначального вклада, ОК.

8. Появиться окно Результаты подбора параметра, а вычисленные значения будут вставлены в таблицу. ОК.

Таблица подстановки.

Она создается для исследования зависимости некоторой величины от одного или нескольких параметров.

Определите сумму выплат для разных процентных ставок:

1. Создайте таблицу подстановки рядом с исходной таблицей (отступив один столбец). Для этого в первую строку ведите название столбцов таблицы: Процент, Коэффициент, Сумма выплат.

2. Создайте список исходных данных (столбец Процент): значения процентной ставки от 3% до 7% с шагом 0,25.

3. Введите формулы, отражающие исследуемые последовательности в верхнюю строчку таблицы подстановки (вместо слов заголовка): коэффициент заменяется на формулу: =((1+процентная ставка)^срок вклада) а Сумма выплат - на формулу: =(размер вклада * коэффициент наращивания) с ссылками на создаваемую таблицу.

4. Выделите диапазон ячеек с данными будущей таблицы подстановок и возьмите команду Данные - Анализ «что-если» – Таблица данных.

5. В поле Подставлять значения по строкам в введите ссылку на ячейку со значением процентной ставки 3% в данной таблице. ОК. В результате таблица подстановки будет заполнена.

12	Поделиться:

Воспользуйтесь поиском по сайту: