Макарова Н.В. Статистика в Excel-1

Так как /р^ = 3,53 и ф = 5,75 попадают в критический ин­ тервал (-оо; -3,18) U (3,1»; + оо), то коэффициенты регрессии aj и ^2 являются значимыми.

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

Для пересчета уравнения рефессии в диалоговом окне Регрес­ сия необходимо задать те же самые параметры (см, рис. 14.2), за исключением лишь того, что следует активизировать флажок Кон­ станта-ноль. В случае если незначимым является коэффициент при факторном признаке, следует пересмотреть набор признаков в уравнении регрессии.

После пересчета уравнения на рабочем листе генерируются таблицы, аналогичные табл. 14.2-14.6. Для сравнения приведем только первые три из них (табл. 14.7—14.9).

12

ВРегрессионная статистика

Таким образом, получаем новое уравнение регрессии: 3^-0,66x1 +0,21x2.

Проверка значимости коэффициента детерминации Л^ и ко­ эффициентов ^1 и ^2 при факторных признаках подтверждает адекватность полученного уравнения.

Экономическая сущность коэффициентов Л] и t22 в получен­ ном уравнении регрессии состоит в том, что они показывают сте­ пень влияния каждого фактора на прибыль предприятий. Так,

та

Таблица 14.8

увеличение оборотных средств на 1 млн руб. ведет к росту прибы­ ли на 0,66 млн руб., а увеличение основных фондов на 1 млн руб. ведет к росту прибыли на 0,21 млн руб.

281

Кроме того, дополнительно можно рассчитать и коэффициен­ ты эластичности Эх^ ~ 0,45 и Эх2 == 0,55, которые показывают, что по абсолютному приросту наибольшее влияние на прибыль пред­ приятий оказывает второй фактор: увеличение стоимости основ­ ных фондов ^2 на 1 % вызывает рост прибыли на 0,55 %, тогда как рост величины оборотных средств Jfi на 1 % способствует росту прибыли на 0,45 %.

14.3.

Статистические функции, связанные с режимом «Регрессия»

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

См. также ЛГРФПРИБЛ, ПРЕДСКАЗ, ТЕНДЕНЦИЯ.

Синтаксис:

Рассчитывает массив данных, описывающих уравнение ли­ нейной множественной (или парной) рефессии на основе метода

признаков А"/ (необязательный аргумент);

•конст: логическое значение, которое указывает, требуется ли, чтобы свободный член ^о был равен О (необязательный аргумент);

•статистика: логическое значение, которое указывает, тре­ буется ли вернуть дополнительную статистику по регрессии (нео­ бязательный аргумент).

Замечания:

• массив известные значения х может содержать одно или несколько множеств переменных. Если используется только одна переменная, то аргументы известные значения у и известные значения х могут быть массивами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент известные значения у дол-

282

жен быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).

• если аргумент известные значения х опущен, то предпо­ лагается, что это массив {1; 2; 3;...} такого же размера, как и аргу­ мент известные значения у;

•если аргумент конст = 1 или опущен, то а^ вычисляется обычным образом;

•если аргумент конст == О, то а^ полагается равным О и зна­ чения ai подбираются так, чтобы выполнялось соотношение

У=а^Х^'\'а^2'^-'^^т^т^

•если аргумент статистика - О или опущен, то функция ЛИНЕЙН вычисляет только коэффициенты а^ и свободный член а^;

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

{^т^ ^т-'Ь - » ^Ь ^О; S^m^ ^^m^U --'^ ^^Ь ^^0^ ^^5 ^^у^ ^5 4^5 ^ге^ ^^resid}^

Дополнительная регрессионная статистика:

sei, se2, ..,,se^ — стандартные значения ошибок для коэффици­ ентов ^ j , ^2,,.., а^;

5^0 — стандартное значение ошибки для свободного члена а^ {seQ = #Н/Д, если аргумент конст == 0);

R^ - коэффициент детерминации;

sey — стандартная ошибка для оценки у; F— F-статистика;

df— степени свободы;

sSj^g — регрессионная сумма квадратов; ^^resid ~ остаточная сумма квадратов.

Порядок расчета дополнительной регрессионной статистики представлен в табл, 14.10.

Математико-статистическая интерпретация:

См. подразд. 14.1.

Технологию работы с функцией ЛИНЕЙН рассмотрим на примере 14.1. Для данного примера формула (=ЛИНЕЙН(СЗ: :C8;D3:E8;0;1)} рассчитает следующий массив значений (табл. 14.11):

Как видим, данная формула рассчитывает значения, анало­ гичные значениям из табл. 14.7-14.9:

283

Таблица 14.10

<^т ^m-I 02 flfl ^0

•El 1 = С29 в табл. 14.9 ~ коэффициент а2\

•F11 = С28 в табл. 14.9 - коэффициент а^',

•G11 = С27 в табл. 14.9 - коэффициент ^QJ

•Е12 = D29 в табл. 14.9 - стандартную ошибку для коэффици­ ента ^2;

•F12 = D28 в табл. 14.9 - стандартную ошибку для коэффици­ ента ^i;

•Е13 = С15 в табл. 14.7 ~ коэффициент детерминации F^\

•F13 = С17 в табл. 14.7 - стандартную ошибку для оценки у\

•Е14 = F22 в табл. 14.8 — расчетное значение F-критерия Фи­ шера F ;

•Fl4 = С23 в табл. 14.8 - число степеней свободы kQ\

•Е15 = D22 в табл. 14.8 - регрессионную (факторную) сумму квадратов;

•F15 = D23 в табл. 14.8 - остаточную сумму квадратов. Функцию ЛИНЕЙН удобно применять, когда не требуется

проводить полный анализ уравнения рефессии.

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

См. также ЛИНЕЙН, ПРЕДСКАЗ.

Синтаксис:

Признаков J^' (необязательный аргумент);

• новые значения х: множество новых значений х, для ко­ торых функция ТЕНДЕНЦИЯ рассчитывает соответствующие значения у (необязательный аргумент);

• конст: логическое значение, которое указывает, требуется ли, чтобы свободный член а^ был равен О (необязательный аргумент).

Замечания:

• массив известные значения х может содержать одно или несколько множеств переменных. Если используется только одна переменная, то аргументы известные значения у и известные значения х могуг быть массивами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент известные значения з^ должен быть вектором (т.е. интервалом высотой в одну строку или шири­

ной в один столбец);

• если аргумент известные значения х опущен, то предпо­ лагается, что это массив {1;2;3;...} такого же размера, как и аргу­ мент известные значения у;

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

285

Кроме того, если известны величина оборотных средств и стоимость основных фондов для новых предприятий, то с помо­ щью функции ТЕНДЕНЦИЯ может быть спрогнозирована их прибыль. Например, известно, что для предприятия 7 л^! = 95, Х2 = = 380, тогда формула =ТЕНДЕНЦИЯ(СЗ:С8;рЗ:Е8;{95;380}; 0) рас­ считает прогнозируемое значение прибыли у = 140,90 млн руб.

Примечание, Для парной регрессии и размерности аргумента новые значения ::с в одну ячейку функция ТЕНДЕНЦИЯ адекватна функции ПРЕДСКАЗ {см, описание функции ПРЕДСКАЗ).

Кроме того, функцию ТЕНДЕНЦИЯ удобно использовать

при экстраполяции и интерполяции рядов динамики.

Под экстраполяцией понимается распространение выявлен­ ных в анализе рядов динамики закономерностей развития изучае­ мого явления на будущее. Экстраполяция широко применяется при прогнозировании социально-экономических явлений и бази­ руется на следующих предпосылках:

•развитие исследуемого явления в целом следует описывать плавной кривой;

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

Кпрогнозированию уровней динамического ряда близок во­ прос об интерполяции — определении некоторых неизвестных уровней внутри данного динамического ряда. Интерполяция тес­ но связана с аналитическим выравниванием ряда {см, главу 16). При интерполяции считается, что ни выявленная тенденция, ни

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

Функцию ТЕНДЕНЦИЯ можно также использовать и для ап­ проксимации полиномиальной кривой у = До "•" ^i-^ "•" ^2^ + •.. + + ЛдаХ^, проводя рефессионный анализ для одного факторного признака, но возведенного в различные степени. Например, пусть столбец В содержит значения у, а столбец С ~ значения х. Можно ввести х^ в столбец D, х - в столбец Е и так далее, а за­ тем на основании введенных данных построить уравнение рег­ рессии. Например, большое применение при выравнивании ря-

287

дов динамики имеет парабола (полином 2-го порядка), уравне­ ние которой имеет вид

у - OQ-^ a^t-^ ^ 2 ^ .

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

Пример 14.2. Известны данные об урожайности пшеницы в области за 1993-1999 гп Какую урожайность пшеницы можно ожидать в области в 2000-2002 гг.?

Анализ представленных данных позволяет заметить, что в рас­ сматриваемом периоде рост урожайности пшеницы происходил с некоторым ускорением (можно показать, что абсолютный при­ рост урожайности в среднем увеличивался на 0,12 ц в год). Учиты­ вая данное обстоятельство, проведем выравнивание ряда по пара­ боле (табл. 14.13).

Содержимое ячеек в табл. 14.13:

•массив ВЗ:С9 содержит исходные данные задачи;

•массив D3:D9 содержит года прошедшего периода, а массив ЕЗ:Е9 - их квадраты (например, ячейка ЕЗ содержит формулу =СТЕПЕНЬ(03;2);

288

• массив F3:F9 содержит формулу {=ТЕНДЕНЦИЯ(СЗ:С9; D3:E9;D3:E9;1)} - рассчитывается массив значений теоретичес­ кого ряда урожайности у;

Примечание. Для ввода формулы необходимо предварительно выделить диапазон ячеек F3:F9, после чего ввести формулу и нажать комбинацию кла­ виш Ctrl + Shift + Enter. Microsoft Excel автоматически заключит формулу в фигурные скобки {}.

•массив D10:D12 содержит порядковые номера годов в про­ гнозируемом периоде, а массив Е10:Е12 - их квадраты (например, ячейка ЕЮ содержит формулу =СТЕПЕНЬ(О10;2);

•массив F10:FI2 содержит формулу {=ТЕНДЕНЦИЯ(СЗ:С9; D3:E9; D10:E12;1)} — вычисляется массив прогнозируемых значе­ ний урожайности на 2000-2002 гп

Таким образом, при сохранении тенденции, которая наблюда­ лась в течение последних семи лет, можно ожидать, что урожай­ ность пшеницы в области в последующие три года составит при­ близительно 29,8; 31,1; 32,5 ц/га.

289