ТЕНДЕНЦИЯ(известные_значения_y;известные_значения_x;новые_значения_x;конст)

Лабораторная работа №5

«Аппроксимация и краткосрочный прогноз»

Цель работы: применение функций массивов; работа с данными различного формата.

Задание: разработать таблицу определения с помощью функции ЛИНЕЙН коэффициенты уравнения линейной аппроксимации, рассчитать теоретические значения Yрт и краткосрочный прогноз с помощью функции ТЕНДЕНЦИЯ.

Теоретическое введение.

 

Формула массива

Формула массива (матричная функция) может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива воздействует на несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца. Формула массива создается так же, как и простая формула. Выделяется ячейка или группа ячеек, в которых необходимо создать формулу, вводится формула, а затем нажимаются клавиши CTRL+SHIFT+ENTER.

Если необходимо вычислить одно значение, Microsoft Excel может понадобиться выполнить несколько действий для возврата такого значения. Для вычисления нескольких значений в формуле массива, необходимо ввести массив в диапазон ячеек, имеющих соответствующее число строк или столбцов, как аргументы массива. Кроме того, формулу массива можно использовать для вычисления одного или нескольких значений для последовательности, которая не указана на листе. В формулу массива можно включать константы так же, как это делается в простой формуле, но массив констант должен вводиться в определенном формате.

 

Функция ЛИНЕЙН

Матричная функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Уравнение для прямой линии имеет вид:

 

y = mx + b

или

y = m₁x₁ + m₂x₂ +... + b

(в случае нескольких интервалов значений x),

 

где зависимое значение y является функцией независимого значения x. Значения m - это коэффициенты, соответствующие каждой независимой переменной x, а b - это постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику. Синтаксис функции:

 

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)

 

Известные_значения_y - это множество значений y, которые уже известны для соотношения y = mx + b.

Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная. Известные_значения_x - это необязательное множество значений x, которые уже известны для соотношения y = mx + b. Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут быть массивами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

Если известные_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера как и известные_значения_y. Конст - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx.

Статистика - это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid}. Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

 

Функция ТЕНДЕНЦИЯ

Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x. Синтаксис функции:

 

ТЕНДЕНЦИЯ(известные_значения_y;известные_значения_x;новые_значения_x;конст)

 

Известные_значения_y - это множество значений y, которые уже известны для соотношения y = mx + b. Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. · Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x - это необязательное множество значений x, которые уже известны для соотношения y = mx + b. Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут быть массивами любой формы, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец). Если известные_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.

Новые_значения_x - это новые значения x, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Новые_значения_x должны содержать столбец (или стоку) для каждой независимой переменной, так же как известные_значения_x. Таким образом, если известные_значения_y имеет один столбец, то известные_значения_x и новые_значения_x должны иметь одинаковое количество столбцов. Если известные_значения_y имеет одну строку, то известные_значения_x и новые_значения_x должны иметь одинаковое количество строк. Если новые_значения_x опущены, то предполагается, что они совпадают с известные_значения_x. Если опущены оба массива известные_значения_x и новые_значения_x, то предполагается, что это массив {1;2;3;...} такого же размера, что и известные_значения_y.

Конст - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если конст имеет значение ИСТИНА или опущена, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.

Функцию ТЕНДЕНЦИЯ можно использовать для аппроксимации полиномиальной кривой, проводя регрессионный анализ для той же переменной, возведенной в различные степени. Например, пусть столбец A содержит значения y, а столбец B содержит значения x. Можно ввести x^2 в столбец C, x^3 в столбец D, и так далее, а затем провести регрессионный анализ столбцов от B до D со столбцом A.

Формулы, которые возвращают массивы, должны быть введены как формулы массивов. При вводе массива констант в качестве аргумента, такого как известные_значения_x, следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк.

 

	A	B	C	D	E	F	G	H
	Аппроксимация и краткосрочный прогноз
					Коэф-ты линейной модели(ЛИНЕЙН)
	Исходные данные			mтип	bтип	mинд	bинд
	Месяц,	Типовая	Индивид.		-0,077158251	3256,567675	0,02807396	1,154325632
	год	новостройка, $	проекты, $		Урт=mx+b	(Yрт-Y1)^2	Ури=mx+b	(Yри-Y2)^2
	авг.99				450,09	25,91425457	1022,288472	767,9287639
	сен.99				447,70	151,3220124	1023,158765	46,80249462
	окт.99				445,38	92,46839421	1024,000984	5775,850444
	ноя.99				442,99	49,11142726	1024,871277	3039,176134
	дек.99				440,68	373,3668167	1025,713495	204,1042113
	янв.00				438,29	176,5016434	1026,583788	5865,076622
	фев.00				435,89	252,6029074	1027,454081	5999,134664
	мар.00				433,66	74,9245535	1028,268226	6125,915177
	апр.00				431,26	126,8774989	1029,138519	3497,364382
	май.00				428,95	194,5814002	1029,980737	1598,459363
	июн.00				426,56	211,9160875	1030,85103	0,724252336
	июл.00				424,24	85,42547715	1031,693249	1467,407175
	авг.00				421,85	172,9044954	1032,563542	2250,217574
	сен.00				419,46	932,7661913	1033,433834	4431,054392
	x	Y1	Y2		SТлин=	2920,68316	SИлин=	41069,21565
		Прогноз
		Функция(ТЕНДЕНЦИЯ)		Расчетный прогноз Y=m*x+b
	окт.00	417,1440313	1034,276053		417,14		1034,276053
	ноя.00	414,7521255	1035,146346		414,75		1035,146346
	дек.00	412,437378	1035,988565		412,44		1035,988565
	янв.01	410,0454722	1036,858858		410,05		1036,858858
	фев.01	407,6535664	1037,72915		407,65		1037,72915
	мар.01	405,4931354	1038,515221		405,49		1038,515221

 

Рис. 4 Таблица расчета коэффициентов линейной аппроксимации и краткосрочного прогноза.

.

Последовательность выполнения работы.

1. В таблицу заносятся два столбца исходных данных – Xi и Yi по вариантам (см. пртложение 2).

I - Расчет коэффициентов «m» и «b» линейной модели Y=m*x+b стоимости квадратного метра жилья в здании типового проекта (или индивидуального) (приведенные данные по некому региону)..

1. Выделить две смежные ячейки – например, E4:F4.

2. Выбрать функцию рабочего листа ЛИНЕЙН.

3. Задать параметры данной функции:

- диапазон известных значений Y (В6:В19);

- диапазон известных значений Х (А6:А19);

4. Одновременно нажать клавиши Shift + Ctrl + Enter. В результате указанных действий в выделенных ячейках E4 и F4 будут размещены значения коэффициентов модели.

II - Вычисление значений стоимости жилья Yрт, по найденным в (I) коэффициентам линейной модели.

1. В ячейку E6 записать формулу = E4*А6+F4. Результатом расчета может оказаться некоторая дата.

2. С помощью маркера автозаполнения записать аналогичные формулы в ячейки по E19 включительно. Предварительно в первоначальной формуле записать абсолютные адреса ячеек, содержащих значения коэффициентов «m» и «b».

3. Выделить диапазон расчетных значений и изменить их формат на «Общий», в результате чего появятся значения, сопоставимые с исходными данными Y1 в диапазоне B6:B19.

III - Вычисление квадрата разности исходных и расчетных значений стоимости жилья.

1. В ячейку F6записать формулу = (E6-B6)^2.

2. С помощью маркера автозаполнения записать аналогичные формулы в ячейки по F19 включительно.

IV Вычисление интегрированной оценки качества приближения исходных и расчетных значений.

1. В ячейке F20 записать формулу вычисления суммы квадратов отклонений исходных Y1 и расчетных значений, рассчитанных в (III).

V Вычисление краткосрочного прогноза стоимости жилья на период с октября 2000 года по март 2001.

1. В ячейки А23:А28 записать новые значения независимой переменной Х – даты с октября 2000 года по март 2001 в указанном в таблице формате.

2. В ячейку E23 записать формулу = E4*A23+F4 и далее повторить действия, указанные в (II).

Для вычисления краткосрочного прогноза изменения зависимой переменной У1 на короткий период времени можно применить функцию ТЕНДЕНЦИЯ, которая минуя значения коэффициентов модели сразу возвращает прогноз по линейной модели. Расположим рассчитанный с ее помощью прогноз в столбце исходных данных «В» в ячейках В23:В28.

1. Выделить смежные ячейки В23:В28 для последующего размещения в них расчетных значений.(Смотри предлагаемую таблицу).

2. Выбрать функцию рабочего листа ТЕНДЕНЦИЯ.

3. Задать параметры данной функции:

- диапазон известных значений Y1 (В6:В19);

- диапазон известных значений Х (А6:А19);

- диапазон новых значений Х (А23:А28);

4. Одновременно нажать клавиши Shift + Ctrl + Enter.

При правильном выполнении указанных действий в выделенных ячейках В23:В28 будут размещены значения прогноза стоимости жилья на указанный период. Результаты идентичны полученным в пункте V с помощью функции ЛИНЕЙН, т.к. также рассчитаны по линейной модели.

 

Контрольные вопросы:

1. Постановка задачи аппроксимации
2. Математическая запись критерия приближения
3. Формулы вычисления значений коэффициентов линейной модели
4. Основная идея метода наименьших квадратов
5. Способы построения на диаграмме нескольких зависимостей
6. Функция «ЛИНЕЙН» - ее назначение и аргументы
7. Особенности работы с функцией «ЛИНЕЙН».
8. Функция «ТЕНДЕНЦИЯ» - ее назначение и аргументы
9. Особенности работы с функцией «ТЕНДЕНЦИЯ».
10. Особенности работы с данными типа «Дата».
11. Определение параметров линейной аппроксимации с помощью инструмента «Линия тренда»

 

 

Отчет о работе должен содержать табличные формулы, значения коэффициентов модели и график.