0909424_EA62B_balashova_t_a_red_ekonometrika

11

рументы позволяют представить результаты анализа в графичес# ком виде.

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

 Форматы чисел в MS Excel

Применяя различные числовые форматы, можно изменить фор# мат числа, не изменяя само число. Числовой формат не влияет на фактическое значение ячейки, используемое в MS Excel для осуще# ствления расчетов.

С помощью различных числовых форматов можно выводить числовые данные как текст, число, проценты, даты, валюты и т.д. Например, экспоненциальный формат используется для отображе# ния чисел в экспоненциальном представлении и замены части числа на E + n, где E (экспонента) равно предыдущему числу, умноженно# му на 10 в степени n. Например, в экспоненциальном формате, где количество знаков после запятой равно двум, число 12345678901 отобразится как 1,23E + 10, то есть как 1,23, умноженное на 10 в 10#й степени. Можно указать используемое количество десятичных зна# ков. Величина 8,069Е – 10 в экспоненциальной записи – это то же, что 0,0000000008 в обычной записи.

Для смены формата числа необходимо выполнить следующие действия:

Excel 2003		Excel 2010
1. В меню Формат выберите ко-		1. На вкладке Главная в группе Число
манду Ячейки… (или просто на-		нажмите кнопку вызова диалогового окна
жмите клавиши Ctrl + 1).		рядом с надписью Число (или просто на-
2. В списке Числовые	форматы	жмите клавиши Ctrl + 1).
выберите нужный формат и, если
это необходимо, настройте его
параметры.
3. Чтобы закончить и вернуться
на лист, нажмите кнопку ОК.
		2. В списке Числовые форматы выберите
		нужный формат и, если	это необходимо,
		настройте его параметры.
		3. Чтобы закончить и вернуться на лист,
		нажмите кнопку ОК.

12

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

Чаще всего числовые данные отображаются правильно незави# симо от того, вводятся ли они в таблицу вручную или импортиру# ются из базы данных или другого внешнего источника. Однако иногда MS Excel применяет к данным неправильный числовой формат, из#за чего приходится изменять некоторые настройки. На# пример, при копировании данных из MS Word к числовым данным может быть применен текстовый формат, что впоследствии приво# дит к проблемам при вычислениях или нарушению порядка сорти# ровки. Индикатором данной ошибки служит маленький зеленый треугольник в левом верхнем углу ячейки. Для ее исправления:

Excel 2003		Excel 2010
1. В меню Сервис выберите коман-		1. Выделите любую ячейку или диапа-
ду Параметры и откройте вкладку		зон смежных ячеек с индикатором
Контроль ошибок.		ошибки в верхнем левом углу.
2. Убедитесь, что установлены		2. Нажмите появившуюся рядом с выде-
флажки Включить фоновую про-		ленной ячейкой или диапазоном ячеек
верку ошибок и Число сохранено		кнопку ошибки.
как текст.
3. Выделите ячейки с зеленым инди-
катором ошибки в верхнем левом
углу	.
4. Нажмите на появившуюся рядом с
ячейкой кнопку	и выберите ко-
манду Преобразовать в число.
		3. Выберите в меню пункт Преобразо-
		вать в число.

13

При выполнении вычислений ошибка также может возникнуть, если при вводе чисел использовать разделитель целой и дробной части, отличный от принятого в системе. Как правило, для разделе# ния целой и дробной частей используются «.» или «,». Определить, какой разделитель используется, можно щелкнув по ярлыку Язык и региональные стандарты в окне Панель управления Windows.

 Копирование листов Microsoft Excel

Лист с исходными данными не следует изменять ни при каких обстоятельствах. Для выполнения различных расчетов или тестов нужно сначала скопировать данные, а затем уже на листе#копии произвести необходимые изменения.

Наиболее удобным способом является копирование целого лис# та. Для этого:

Excel 2003	Excel 2010
1. В меню Правка выберите команду	1. На вкладке Главная в группе Ячей-
Переместить/скопировать лист….	ки нажмите кнопку Формат и в разде-
2. В диалоговом окне Переместить	ле Упорядочить листы выберите
или скопировать в списке Перед	пункт Переместить или скопировать
листом укажите, куда необходимо	лист.
скопировать лист, установите флажок
Создать копию.
3. Чтобы закончить и вернуться на
лист, нажмите кнопку ОК.
	2. В диалоговом окне Переместить
	или скопировать в списке Перед
	листом укажите, куда необходимо
	скопировать лист, установите флажок
	Создать копию.
	3. Чтобы закончить и вернуться на
	лист, нажмите кнопку ОК.

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

14

 Создание парных диаграмм рассеяния

Важнейшим элементом эконометрического исследования явля# ется графический анализ исходных данных. В случае множествен# ной регрессии, то есть когда у нас несколько показателей, необходи# мо построить парные диаграммы зависимости объясняемой пере# менной Y от каждой из объясняющих переменных Х – диаграммы рассеяния.

В эконометрическом анализе диаграммы, используемые при построении регрессионной модели, имеют тип «Точечная».

Каждая диаграмма должна иметь содержательный заголовок, пусть и такой простой, как «Зависимость объема продаж транспор# тного средства от расходов на бензин и индекса потребительских расходов». Оси диаграммы тоже должны быть названы сокращен# ными именами соответствующих переменных (обязательно вклю# чая единицы измерения). А вот легенды на парной диаграмме рас# сеяния может и не быть, если на ней не добавлена линия тренда.

Для создания диаграммы рассеяния нужно выделить два столб# ца данных со значениями показателей, включая их названия (мет# ки) в первой строке матрицы данных, и выполнить следующие дей# ствия:

	Excel 2003	Excel 2010
1. В меню Вставка выберите команду		1. На вкладке Вставка в группе Диа-
Диаграмма….		граммы выберите тип и вид диаграм-
2. Следуйте инструкциям в раскрыв-		мы, диаграмма добавится на лист.
шемся	диалоговом окне Мастер диа-	2. Щелкните в любом месте внедрен-
грамм.		ной диаграммы, чтобы активизировать
		ее. Откроется панель Работа с диа-
		граммами с дополнительными вклад-
		ками Конструктор, Макет и Формат.
		3. Выберите необходимые	команды
		для оформления диаграммы.

Нужно помнить, что для того чтобы MS Excel правильно опре# делил переменные, объясняемая переменная Y должна быть распо# ложена в правом из двух выделенных столбцов, а объясняющая пе# ременная X – в левом.

15

Для добавления данных в уже построенную диаграмму необхо# димо предварительно внести их в исходную таблицу, а затем выпол# нить следующие действия:

Excel 2003		Excel 2010
1. Щелкните правой кнопкой мыши на		1. Щелкните правой кнопкой мыши на
области диаграммы.		области диаграммы.
2. В раскрывшемся	контекстном меню	2. В раскрывшемся	контекстном меню
выберите команду Исходные дан-		выберите команду Выбрать данные.
ные.		3. В окне Выбор источника данных		в
3. В окне Исходные данные перейди-		поле Элементы легенды (ряды)
те на вкладку Ряд и укажите новые		выделите нужный ряд и нажмите кноп-
диапазоны данных для значений Y и		ку Изменить.
X.		4. В окне Изменение ряда укажите
4. Чтобы закончить и вернуться на		новые диапазоны данных для значе-
лист, нажмите кнопку ОК.		ний Y и X.
		5. Чтобы закончить и вернуться на
		лист, последовательно нажимайте
		кнопку ОК.

16

3. Справочные материалы для выполнения расчетов

Формулы, используемые при корреляционном анализе

Функция или

Результат вычис-

Формула для вычислений

инструмент Анализа

лений/ Примечания

данных в Excel

Среднее значение

СРЗНАЧ(число1;

Возвращает

среднее

x

1

n

число2;…)

значение

(среднее

xi

арифметическое)

n i 1

аргументов

Дисперсия

ДИСП(число1;

Оценивает

диспер-

Sx2

1

(xi x)2

число2;…)

сию по выборке

n 1

Стандартное отклонение

СТАНДОТКЛОН

Оценивает

стандарт-

Sx

Sx2

(число1;число2;…)

ное отклонение

по

выборке.

Стандарт-

ное отклонение – это

мера того,

насколько

широко

разбросаны

точки данных отно-

сительно их среднего

Сумма квадратов отклонений

КВАДРОТКЛ (чис-

Возвращает

сумму

ло1;число2;…)

квадратов

отклоне-

n

x)2

ний точек данных от

( xi

их среднего

i 1

Коэффициент корреляции

КОРРЕЛ (мас-

Возвращает

коэффи-

n

сив1;массив2)

циент

корреляции

(xi x)( yi y)

между

интервалами

rx,y

i 1

ячеек

массив1

и

n

n

массив2

( xi x)2 (yi y)2

i 1

i 1

t-критерий Стьюдента для провер-

СТЬЮДРАСПОБР

Вычисленное по этой

ки значимости коэффициента корре-

(вероятность; степе-

формуле значение

ляции

ни_свободы)

tнабл сравнивается с

r2

критическим значе-

нием t-критерия,

tнабл

y,x

(n 2)

1 r2

которое берется из

y,x

таблицы значений t-

распределения

Стьюдента с учетом

заданного уровня

значимости и числа

степеней свободы

(n – 2) или определя-

ется с помощью

функции СТЬЮД-

РАСПОБР( )

17

Матрица

коэффициентов

парной

Обращение к средст-

корреляции

вам анализа данных.

1

r

r

...

r

Для

вычисления мат-

рицы

коэффициентов

yx

yx

2

yx

парной

корреляции R

1

1

m

ryx

rx x ...

rx x

следует

воспользо-

R

r

1

r

1

2

...

r

1 m

ваться

инструментом

1

yx2

x1x2

... ...

x2 xm

Корреляция из пакета

...

...

...

Анализ данных

rx1xm

rx2 xm ...

1

ryxm

Инструмент Корре-

ляция применяется, если имеется более двух переменных измерений для каждого объекта. В результате выдается таблица – корреляционная матрица, показывающая значение функции КОРРЕЛ( ) для каждой возможной пары переменных измерений. Любое значение коэффициента корреляции должно находиться в диапазоне от –1 до +1 включительно

Формулы, используемые при регрессионном анализе

	Формула	Функцияили	Результат вычислений /
		инструмент Анализа дан-
	для вычислений	ных в Excel	Примечания
	Оценка параметров мо-	Для вычисления параметров	Возвращает		подробную
	дели парной и множест-	уравнения регрессии следу-	информацию		о	парамет-
	венной линейной регрес-	ет воспользоваться инстру-	рах модели,			качестве
	сии	ментом Регрессия из пакета	модели, расчетных зна-
	A ( X X ) 1 X Y	Анализ данных	чениях и остатках в виде
			четырех таблиц: Регрес-
			сионная	статистика,
			Дисперсионный			анализ,
			Коэффициенты,			Вывод
			остатка.
			Также могут быть полу-
			чены график		подбора и
			график	остатков

18

Оценка качества модели регрессии

F-критерий Фишера

=FРАСПОБР(вероятность;

Возвращает обратное

для проверки значимо-

степени_ свобо-

значение для F-распреде-

сти модели регрессии

ды1;степени_свободы2)

ления вероятностей.

вероятность – это вероят-

FРАСПОБР( ) можно ис-

R2 / k

пользовать, чтобы опреде-

ность, связанная с F-

лить критическиезна-

F (1 R2 ) /(n k 1)

распределением

чения F-распределения.

степени_свободы 1 – это

Чтобы определить крити-

ческоезначение F, нужно

числитель степеней свободы

использовать уровень

( 1 = k)

значимости αкак аргумент

степени_свободы 2 – это

вероятность для

FРАСПОБР( ).

знаменатель степеней свобо-

ды ( 2 = (n – k – 1),

где k – количество факторов,

включенных в модель)

Коэффициент детерминации

Коэффициент детерминации показывает

n

y)2

n

долю вариации результативного признака,

( yˆi

εi2

находящегося под воздействием изучаемых

2

i 1

i 1

факторов, то есть определяет, какая доля

R

1

вариации признакаY учтена в модели и

n

y)2

n

( yi

( yi y)2

обусловлена влиянием на него факторов.

i 1

i

1

Чем ближе R2

к 1, тем вышекачество моде-

ли

Коэффициент множественной корре-

Данный коэффициент является универсаль-

ляции (индекс корреляции) R

ным, так как он отражает тесноту связи и

точность модели, атакже может использо-

n

n

y)2

ваться при любой форме связи переменных.

Чем ближе R к 1, тем выше качество моде-

εi2

( yˆi

R 1

i 1

=

i 1

ли

n

n

( yi y)2

( yi y)2

i 1

i 1

t-критерий

Стьюдента

для

оценки

Вычисленное значение taj сравнивается с

значимости

параметров

модели

линей-

критическим значением t-критерия, которое

ной регрессии:

берется из таблицы значений t-распреде-

taj aˆ j / aj

ления Стьюдента с учетом заданного

уровня значимости и числа степеней свобо-

ды (n – k – 1). В Excel критическое значение

t-критерия можно получить с помощью

функции

СТЬЮДРАСПОБР(вероятность; степе-

ни_свободы)

вероятность – вероятность, соответствую-

щая двустороннему распределению Стью-

дента

степени_свободы – число степеней свобо-

ды, характеризующее распределение

19

Средняя относительная ошибка аппроксимации

Eотн = 1 n ei 100% n i 1 y i

Средняя относительная ошибка аппроксимации – оценка точности модели

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

Коэффициенты

Коэффициент эластичности показывает, на сколько

эластичности

x j

процентов изменится значение исследуемой величины

Эj a j

при изменении соответствующего фактора на 1%

y

Бета-коэффициенты

Бета-коэффициент показывает, на какую часть своего

Sx

j

СКО изменится значение исследуемой переменной при

βj aˆ j

изменении соответствующего фактора на 1 СКО

Sy

Дельта-коэффициенты

Дельта-коэффициент

показывает

среднюю

долю

r

β

/ R2

влияния соответствующего

фактора в

совокупном

j

y,x j

j

влиянии всех факторов, включенных в модель

Построениеинтервальныхпрогнозовпомоделирегрессии

U ( Xпрогн) e

t

1 XпрогнT

( X T X ) 1 Xпрогн

–

ошибка

прогнозирования,

которая позволяет определить доверительный интервал прогноза,

где е

еt2

– стандартная ошибка модели

n k

1

Регрессионная статистика в отчете Excel

Наименование

Принятое наименование

Формула

в отчете Excel

Множественный

Коэффициент множествен-

R

R2

R

ной корреляции, индекс

корреляции

R-квадрат

Коэффициент детермина-

n

( yˆi y)2

n

ции, R2

R2

ei2

i

1

1

i 1

n

( yi y)2

n

( yi y)2

i

1

i 1

Нормированный

Скорректированный R2

n 1

R-квадрат

R

2 1 1 R2

n k

1

Стандартная

Среднеквадратическое

e

ei2 /(n k 1)

ошибка

отклонение от модели

ESS /(n k 1)

						20
	Дисперсионный анализ в отчете Excel
Наиме-	Df –		MS – дисперсия
	число	SS – сумма
нование				F-критерий Фишера
	степе-		на одну степень
в отчете	ней	квадратов	свободы
Excel
	свободы
Регрессия	k	RSS yˆi y 2	yˆi y 2 / k	F		R2 / k
			RSS / k		1 R2	/ n k 1
Остаток	n – k –1	ESS ei2	ei2 /(n k 1)
			ESS /(n k 1)
Итого	n – 1	TSS yi y 2

Названия некоторых функций в Excel 2010 были изменены по сравнению с более ранними версиями.

Чтобы повысить точность работы функций MS Excel, обеспе# чить их согласованность и привести имена функций в соответствие с их назначением, корпорация Microsoft изменила, переименовала и добавила несколько функций в библиотеку MS Excel 2010.

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

	Название функции	Название функции
	в Excel более ранних		Примечания
		в Excel 2010
	версий
	ДИСП(число1,[число2],...	ДИСП.В(число1,[число2]	Оценивает дисперсию
	])	,...])	по выборке
	СТЬЮДРАС-	СТЬДЕНТ.ОБР.2Х(веро-	Возвращает двусторон-
	ПОБР(вероятность; сте-	ятность, степени_сво-	нее обратное
	пени_свободы)	боды)	t-распределение Стью-
			дента
	FРАСПОБР(вероятность	F.ОБР.ПХ(вероятность,	Возвращает значение,
	;степени_свободы1;	степени_свободы1, сте-	обратное (правосторон-
	степени_свободы2)	пени_свободы2)	нему) F-распределению
			вероятностей
	ХИ2ОБР(вероятность,	ХИ2.ОБР.ПХ(вероят-	Возвращает обратное
	степени_ свободы)	ность,степени_свободы)	значение односторонней
			вероятности распреде-
			ления хи-квадрат