Построение линейного и нелинейного графика временного ряда

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

Проведем прогнозирование роста выпуска товаров с помощью функции Excel – РОСТ. Существует большое количество типов данных, которые изменяются во времени нелинейным способом. Примерами таких данных являются – объем продаж новой продукции. Функция РОСТ строит прогноз на основе модели: у = а·х+ b, где а и b - параметры; у - прогнозируемая переменная (в данном случае объем выпуска); х - независимая переменная (в данном случае время).

Известные значения х - это 7 предплановых года. Известные значения у - объемы выпуска в эти годы. Исходные данные внести в ячейки А2:В8. В ячейку С2 ввести формулу: =РОСТ (В2:В8;А2:А8;;ИСТИНА) и получаем результат = 7098. Далее для ввода формулы массива выделить ячейки С2:С8, нажать клавишу F2 и далее комбинацию клавиш <Ctrl +Shift + Enter>. Полученные данные (С2:С8) прогнозируют базовую линию результатов фактических наблюдений. Для составления прогноза на новые временные моменты (А9:А13) в ячейку С9 ввести с помощью формулы массива следующее:

{= РОСТ (В2:В8; А2:А8;А9:А13; ИСТИНА)}

Excel вернет в ячейки С9-С13 прогноз на временные моменты с2009 по 2050г (рис. 35).

Рис. 35. Прогноз на временные моменты

Из рисунка 13 наблюдается тенденция увеличения роста объема выпуска. Так, в 2010 году объем выпуска составит 26384 шт., в 2014 году- 50867шт., а в 2050 году- 18722766 шт.

(тренда)

Рассмотрим пример из анализа рынка образовательных услуг (табл. 17).

Таблица 17

Данные приема в Вуз на специальность «Менеджмент организации»

Текущий номера года (Т)	1 (2001)	2 (2002)	3 (2003)	4 (2004)	5 (2005)	6 (2006)	7 (2007)	8 (2008)	9 (2009^*)
Цифры приема (yt)

^* предполагаемые цифры приема.

Простейшей моделью, выражающей тенденцию развития, является линейная функция тренда: у_t = а₀ + а₁t.

Для построения линейной функции тренда необходимо в Excel перенести данные второй строки таблицы 17.

1. Вставка – Диаграмма. Появляется окно «Мастер диаграмм (шаг 1 из 4): тип диаграммы».

2. Во вкладке Стандартны е выбираем тип диаграммы: График - График с маркерами (рис. 36).Нажимаем кнопку Далее.

Рис. 36. Окно «Мастер диаграмм (шаг 1 из 4): тип диаграммы.

3. Появляется окно «Мастер диаграмм (шаг 2 из 4): источник данных диаграммы», в котором в поле Диапазон вносим А1:А9. В поле «Ряд в» выбираем в столбцах (рис. 37). Далее.

.

Рис. 37. Выкладка Диапазон данных.

4. Появляется окно «Мастер диаграмм (шаг 3 из 4): параметры диаграммы», в котором указываются параметры диаграммы (рис. 38). Далее.

Рис. 38. Окно Мастер диаграмм на третьем шаге

5. Появляется окно «Мастер диаграмм (шаг 4 из 4): размещение диаграммы», где выбираем Поместить диаграмму на листе – имеющемся (рис.17). Готово (рис. 39).

Рис. 39. Окно размещения диаграммы

Рис. 40. Результаты работы Мастера диаграмм.

Для вставки линии тренда нелинейной зависимости необходимо:

1. Щелкнуть правой кнопкой мышки на одном из рядов диаграммы (рис. 41).

Рис. 41. Меню изменения диаграммы

2. Выбрать команду Добавить линию тренда. На экране появится диалоговое окно «Линия тренда».

3. В выкладке Тип выбираем Полиноминальная, степень – 2. В выкладке Параметры: Название аппроксимирующей кривой – установить в положении автоматическое; Поле Прогноз – не активизировать; Пересечение кривой с осью У в точке – не активизировать: Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации – активизировать (рис. 42). ОК.

Рис. 42. Окно «Линия тренда»

На графике появляется Полиноминальная линия регрессии (рис. 43). Те же самые действия выполняются для построение других видов тренда (экспоненциальная, степенная, логарифмическая) (рис. 44).

Рис. 43. Линейное и полиноминально уравнения регрессии

Рис. 44. Различные виды линей тренда

Математическое нахождение трендовых моделей:

Линейная функция тренда: у_t = а₀ + а₁t., параметры данного уравнения находятся с помощью метода наименьшего квадрата:

.

Для получения более точных значений рассчитаем параболическую трендовую модель: у_t = а₀ + а₁t + а₂t². Оценки параметров данной трендовой модели имеют вид:

;

; .

3.5. Построение и графическое отображение интервального вариационного ряда распределения (гистограмма)

Рассмотрим в качестве примера результаты опроса по данным табл. 18.

Таблица 18

Результаты опроса оптимальной стоимости образовательных услуг.

№ опрошенного	Стоимость образовательных услуг (тыс.руб.)	№ опрошенного	Стоимость образовательных услуг (тыс.руб.)	№ опрошенного	Стоимость образовательных услуг (тыс.руб.)	№ опрошенного	Стоимость образовательных услуг (тыс.руб.)
					23,1
	19,4				17,4
	15,5		23,1
					18,6
							18,5
	16,5
			20,5
			22,5		21,7		16,8
	17,5						25,2
	23,5		19,5
	23,7		21,4		25,5		24,7
	17,5		25,1
			21,5		18,6
			18,5				24,3
							19,6
	18,4						22,7
	22,5		17,7		19,3		22,8

Для выполнения задания необходимо:

1. Перенести данные таблицы № 9 в Excel.

2. Сервис – Анализ данных – Гистограмма – ОК (рис. 45).

Рис. 45. Окно «Анализ данных»

3. В появившемся диалоговом окне «Гистограмма» задаются следующие параметры: Входной интервал – диапазон ячеек со значениями стоимости образовательных услуг (А2:А81). Интервал карманов – оставить незаполненным. Выходной интервал – любую ячейку (С2). ОК (рис. 46).

Рис. 46. Окно «Гистограмма» с заполненными параметрами

4. На листе появляется таблица (рис. 47).

Карман	Частота
16,625
19,25
21,875
24,5
27,125
29,75
32,375
Еще

Рис. 47. Расчет нижних границ интервалов

5. В данной таблице необходимо выделить левую верхнюю ячейку (значение 14) и удалить его. Далее в ячейку с именем «Еще» ввести максимальное значение из таблицы 18, т.е. число 35 и получим таблицу 19.

Таблица 19

Карман	Частота
16,625
19,25
21,875
24,5
27,125
29,75
32,375

6. Сервис – Анализ данных – Гистограмма – ОК.

7. В появившемся диалоговом окне «Гистограмма» задаются следующие параметры: Входной интервал – диапазон ячеек со значениями стоимости образовательных услуг (А2:А81). Интервал карманов – диапазон карманов итоговой промежуточной таблицы с верхними границами. Выходной интервал – любую ячейку (С13). Интегральный процент – активизировать. Вывод графика – активизировать. ОК (рис. 48).

Рис. 48. Окно «Гистограмма» с необходимыми параметрами

8. В результате данных действия на рабочем листе появляется выходная таблица 20 и диаграмма (рис. 49).

Таблица 20

Выходная таблица

Карман	Частота	Интегральный %
16,625		8,75%
19,25		33,75%
21,875		52,50%
24,5		77,50%
27,125		91,25%
29,75		93,75%
32,375		97,50%
		100,00%
Еще		100,00%

Рис. 49. Гистограмма и кумулята интервального ряда распределения

Далее необходимо преобразовать выходную таблицу в результативную. Для этого необходимо заменить название столбцов выходной таблицы следующим образом: карман – группа респондентов по стоимости образовательных услуг; частота – число респондентов в группе; интегральный % - накопительная часть группы.

Затем строки первого столбца привести к виду «нижняя граница интервала – верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала. Строку с именем «Еще» удалить, добавить и заполнить строку «Итого» (табл. 21).

Таблица 21

Результативная таблица

Группа респондентов по стоимости образовательных услуг	Число респондентов в группе	Накопительная часть группы
15-16,625		8,75%
16,625-19,25		33,75%
19,25-21,875		52,50%
21,875-24,5		77,50%
24,5-27,125		91,25%
27,125-29,75		93,75%
29,75-32,375		97,50%
32,375-35		100,00%
Итого		100,00%

В результате данных преобразований автоматически изменится гистограмма распределения (рис. 50).

Рис. 50. Преобразованный вид гистограммы и кумуляты интервального ряда распределения стоимости образовательных услуг

Дата добавления: 2014-12-26