На основе матричного оператора 1МНК, пакет Excel

Матричный оператор 1МНК имеет вид , где

,

- транспонированная матрица Х.

Для транспонирования матрицы Х выполните следующие действия:

1) выделите область пустых ячеек, состоящую из (р+1) = 5 строк и n = 20 столбцов для вывода результата, здесь р – количество независимых переменных, n – количество наблюдений;

2) активизируйте Мастер функций любым из способов:

a) в главном меню выберите Вставка/Функция;

b) на панели инструментов Стандартная щелкните по кнопке Вставка функции;

3) в раскрывшемся окне выберите Категорию Ссылки и массивы, Функцию – ТРАНСП (рис.1). Щелкните по кнопке ОК;

4) в строке Массив появившегося окна укажите диапазон ячеек, в которых содержится матрица Х. Щелкните по кнопке ОК

Рис. 6.1

5) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем – на комбинацию клавиш <Ctrl>+<Shift>+<Enter>.

Результат:

.

Произведение матриц (X'X) находим с помощью Мастера функций, используя Категорию Статистические, функцию МУМНОЖ:

1) выделите область пустых ячеек, состоящую из (р+1) = 5 строк и (р+1) = 5 столбцов для вывода результата;

2) в окне МУМНОЖ в строке Массив 1 укажите диапазон ячеек, в которых содержится матрица X ' (первый сомножитель), а в строке Массив 2 – матрица Х (второй сомножитель). Щелкните по кнопке ОК;

3) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем – на комбинацию клавиш <Ctrl>+<Shift>+<Enter>.

Результат умножения матриц:

				139,9	77,4
					5995,4
	(X'X) =	139,9		1186,95	498,74	1307,7
		77,4	5995,4	498,74	310,36	909,8
				1307,7	909,8	2756,5
Аналогично найдем с помощью функции МОБР обратную матрицу:
		15,5851	-0,0545	-0,5706	-1,6119	-0,104
		-0,0545	0,00282	-0,0042	-0,0244	-0,0033
	(X'X)-1 =	-0,5706	-0,0042	0,04402	0,07434	0,02697
		-1,6119	-0,0244	0,07434	0,98119	-0,0763
		-0,104	-0,0033	0,02697	-0,0763	0,04194
						56,9124
						0,3375
	(X'Y) =	8003,2		B =(X'X)-1	(X'Y) =	-1,8406
		4716,5				-2,2722
						-0,0976

Таким образом, получили эконометрическую модель:

= 56,912 + 0,338Х₁ – 1,841Х₂ – 2,272Х₃ – 0,098Х₄.

Подставив в модель исходные значения Х_ij (i = 1,2,…,20; j = 1,2,3,4), получим расчетные значения . Разность между фактическими и расчетными значениями результирующего показателя представляет собой остатки (е_i ), являющиеся оценками значений возмущения.

		50,6616			1,33836			1,79122
		51,9809			1,01908			1,03852
		51,8206			-1,8206			3,31455
		53,4467			-2,4467			5,98621
		53,9931			0,00686			4,7E-05
		54,6805			0,31949			0,10207
		55,1784			1,82157			3,31813
		53,5887			-1,5887			2,52395
		57,0558			2,94423			8,66847
	=	61,6085		e =	-1,6085		е2 =	2,58722
		63,6903			-1,6903			2,85696
		63,5094			0,49061			0,2407
		63,3813			1,61865			2,62003
		65,5109			1,4891			2,21743
		64,9954			2,00462			4,01849
		65,2103			-3,2103			10,3061
		64,5605			-1,5605			2,4353
		66,1197			-0,1197			0,01434
		66,9538			1,04619			1,09451
		70,0535			-0,0535			0,00286
							Сумма	55,1371

Найдем стандартную ошибку остатков (модели) по формуле:

Определим стандартные ошибки оценок параметров модели:

где – диагональные элементы матрицы (Х’X)^-1.

, ,

, ,

.

Для проверки статистической надежности (значимости) оценок параметров модели найдем величину t-статистики, используя формулу:

.

, , ,

, .

Табличное значение t-Стьюдента можно найти в Excel, обратившись к мастеру функций, категория Статистические, функция СТЬЮДРАСПОБР (рис. 2.5).

Рис. 2.5

В диалоговое окно этой функции следует ввести вероятность на уровне значимости, т.е. ввести 0,05 (соответствующая доверительная вероятность при этом составит 0,95) или 0,01 (доверительная вероятность 0,99), и число степеней свободы, равное 15 (20 - 5 = 15) (рис. 2.5), в итоге получим t_табл = 2,131.

Сравнив вычисленные t-статистики с табличным значением, делаем вывод о статистической незначимости b₃ и b₄, поскольку

t₃ = 1,197 < t_табл = 2,131; t₄ = 0,249 < t_табл = 2,131.

2). с использованием стандартной программы «ЛИНЕЙН»:

Встроенная статистическая функция ЛИНЕЙН определяет коэффициенты линейной регрессии:

= b₀ + b₁X₁ + b₂X₂ + b₃X₃ + … + b_рX_р.

Порядок вычислений следующий:

1) введите исходные данные;

2) выделите область пустых ячеек, состоящую из 5 строк и (р + 1) столбцов (где р – количество независимых переменных) для вывода результатов регрессионной статистики;

3) активизируйте Мастер функций любым из способов:

a) в главном меню выберите Вставка/Функция;

b) на панели инструментов Стандартная щелкните по кнопке Вставка функции f _x

4) в раскрывшемся окне выберите Категорию Статистические, Функцию – ЛИНЕЙН. Щелкните по кнопке ОК;

5) заполните аргументы функции (рис. 2):

· Известные значения У – диапазон, содержащий данные, характеризующие результативный признак;

· Известные значения Х – диапазон, содержащий данные, описывающие все независимые переменные;

· Константа – логическое значение, указывающее на наличие или отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член = 0;

· Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения. Щелкните по кнопке ОК;

6) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем – на комбинацию клавиш <Ctrl>+<Shift>+<Enter>.

Рис. 6.2. Мастер функций. Работа с функцией ЛИНЕЙН

Дополнительная регрессионная статистика будет выводиться в следующем виде (табл. 6.2):

Таблица 6.2

Значение bр	Значение bр-1	...	Значение b2	Значение b1	Значение b0
Стандартная ошибка оценки bр ()	Стандартная ошибка оценки bр-1 ()	...	Стандартная ошибка оценки b2 ()	Стандартная ошибка оцен-ки b1 ()	Стандартная ошибка оцен-ки b0 ()
Коэффициент детерминации R2	Стандартная ошибка модели (остатков)	#Н/Д	#Н/Д	#Н/Д	#Н/Д
F-статистика	Число степеней свободы n-(p+1)	#Н/Д	#Н/Д	#Н/Д	#Н/Д
Регрессионная сумма квадратов	Остаточная сумма квадратов	#Н/Д	#Н/Д	#Н/Д	#Н/Д

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

ln = lnb₀ + b₁ lnX₁ + b₂ ln X₂ + b₃ lnX₃ + b₄ lnX₄,

или Z = A + b₁z₁ + b₂z₂ + b₃z₃ + b₄z₄,

где Z = ln , z_j = lnX_j (j = 1,2,3,4); А = lnb₀, т.е. b₀ = exp(A) = e^A, где е – основание натурального логарифма.

Из способа преобразования видно, что для вычисления коэффициентов степенной функции с помощью ЛИНЕЙН следует в строки Известные значения У и Известные значения Х окна рассматриваемой функции вводить логарифмы исходных значений У и Х.

Для рассматриваемого примера (модель производительности труда) результат применения функции ЛИНЕЙН выглядит следующим образом:

Линейная модель

b₀ = exp(3,625281) = e^3,625281 = 37,5353.

Таким образом, получили следующие уравнения:

= 56,912 + 0,338Х₁ – 1,841Х₂ – 2,271Х₃ – 0,098Х₄;

= 37,5353 X₁^0,198 X₂^-0,192 X₃^-0,124 X₄^0,0541.

Однако следует иметь в виду, что статистические характеристики степенной модели определены через логарифмы, и поэтому прямо использовать их для сравнения с соответствующими характеристиками линейной модели (например, с целью выбора лучшей модели) нельзя. Необходимо привести их в сопоставимый вид. Это касается всех функций, при преобразовании которых к линейному виду изменялась (преобразовывалась) зависимая переменная Y.

С учетом пересчета, который выполняется с применением соответствующих формул (3), (5), для степенной функции получили:

R²= 0,9242; S_e = 1,9906.

Не нашли, что искали? Воспользуйтесь поиском: