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

•если аргументы массив] и массив2 имеют различное количе­ ство точек данных, то функция КОРРЕЛ помещает в ячейку зна­ чение ошибки #Н/Д;

•если аргумент массив] либо массив! пуст или если стандарт­ ное отклонение их значений равно О, то функция КОРРЕЛ поме­ щает в ячейку значение ошибки #ДЕЛ/О!.

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

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

• В примере 13,1 {см. табл. 13.4) функция КОРРЕЛ использу­ ется для расчета коэффициентов корреляции между исследуемы­ ми признаками. Например, значение в ячейке С28 рассчитывает­ ся по формуле

=КОРРЕЛ(С5:С18;С5:С18),

а значение в ячейке С29 - по формуле

=КОРРЕЛ(С5:С18;В5:В18).

13.4.

Родственные статистические функции

в подразд. 13.3 были рассмотрены статистические функции КОВАР и КОРРЕЛ, используемые для расчетов соответственно в режимах «Ковариация» и «Корреляция». Здесь приводятся описа­ ния функций ФИШЕР и ФИШЕРОБР, родственных по своей сущности данным режимам.

Функция ФИШЕР

См. также ФИШЕРОБР, КОРРЕЛ, ПИРСОН, КОВАР

Синтаксис:

ФИШЕР (jc)

Результат:

Рассчитывает преобразование Фишера для аргумента х.

Аргументы:

х: числовое значение, которое необходимо преобразовать.

Замечания:

• если аргумент х не является числом, то функция ФИШЕР помещает в ячейку значение ошибки # ЗНАЧ!;

260

• если аргумент х < - 1 или аргумент х > 1, то функция ФИШЕР помещает в ячейку значение ошибки # ЧИСЛО!,

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

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

Выборочный коэффициент корреляции г^ представляет со­ бой случайную величину, поэтому его распределение можно счи­ тать нормальным или приближенно нормальным, если выполня­ ются следующие условия:

•переменные Хи Y, между которыми определяется корреля­ ционная связь, имеют совместное нормальное или приближенно нормальное распределение;

•коэффициент корреляции не равен ±1;

•объем выборки достаточно велик.

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

К 1 + г ^=:—In .

2 / - Г

Данное выражение получило название «z-преобразоеания Фи­ шера»*,

*(Fisher Ronald Aylmcr) Фишер Роналд Эйлмер (1890-1962) - англий­ ский статистик и генетик, член Лондонского королевского общества (1929). Основные труды по статистике и генетической теории эволюции, построил теорию точечные и интервальных статистических оценок, разработал мето­ дику планирования экспериментов и внес существенный вклад в создание теории статистической проверки гипотез.

2в1

Пример 13,2. Требуется на основе выборочных данных о де­ ловой активности однотипных коммерческих структур оценить тесноту связи между прибылью К (млн руб.) и затратами Jf (руб.) на производство единицы продукции (диапазон B2:D8 в табл. 13.5) [12].

Общая схема решения подобных задач состоит в следующем: 1. По одной из формул (13.1) - (13.3) рассчитывается линей­

ный коэффициент корреляции г^.

2. Проверяется значимость линейного коэффициента корре­ ляции на основе /-критерия Стьюдента (см, описание функций СТЬЮДРАСП и СТЬЮДРАСПОБР в подразд. 6.3.8). При этом

262

выдвигается и проверяется гипотеза Щ о равенстве коэффициен­ та корреляции нулю (Яо: г^ = 0). При проверке этой гипотезы ис­ пользуется /-статистика:

'-ili^-^'-i^-

Если гипотеза HQ подтверждается, /-статистика имеет распре­ деление Стьюдента с входными параметрами а и А: (а — уровень значимости; к-п-2-- число степеней свободы). Если расчетное значение t^ > /^, то гипотеза Н^:г^ — 0 отвергается, что свидетель­ ствует о значимости линейного коэффициента корреляции, а сле­ довательно, и о статистической существенности зависимости между А"и Y,

3. Для статистически значимого линейного коэффициента кор­ реляции определяется интервальная оценка для г по выражению

ze z'±Zy '/2-3

где z' — значение, полученное на основе z-преобразования Фишера; ^ — табулированные значения для стандартного нормального

распределения, зависимые от у =1 - а;

п— размер выборочной совокупности.

4.На основе обратного г-преобразования Фишера определя­ ется интервальная оценка для линейного коэффициента корреля­ ции г^.

5.Рассчитывается стандартная ошибка линейного коэффици­ ента корреляции по формуле

1-г'

а,= я-2

Результаты решения задачи приведены в табл. 13.5.

2вЗ

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

•массив B3:D8 содержит исходные данные задачи;

•ячейка D9 содержит формулу =KOPPEJT(C3:C8;D3:D8) - рассчитывается значение линейного коэффициента корреляции (п. 1 общей схемы решения задачи);

•ячейка D10 содержит формулу =ABS(D9)/KOPEHb (1-СТЕПЕНЬ(09;2))*КОРЕНЬ(6-2) - вычисляется расчетное значение /-критерия /р (п. 2 общей схемы решения задачи);

•ячейка D11 содержит формулу =СТЬЮДРАСПОБР(0,05;4) - рассчитывается табличное значение /-критерия t^ (а == 0,05; А:=/х — 2 = 6 — 2 = 4). Выполнение неравенства/р >/^свидетель­ ствует о значимости линейного коэффициента корреляции;

•ячейка D12 содержит формулу =НОРМСТОБР((0,95+1)/2) - вычисляется табулированное значение стандартного нормального распределения (см. описание функций НОРМОБР и НОРМСТОБР в подразд. 6.3.1);

•ячейка D13 содержит формулу =ФИШЕР(09) — определяет­ ся значение z\ полученное на основе преобразования Фишера;

•ячейки D14 и D15 содержат формулы =D13-D12*KO- РЕНЬ(1/(6 - 3)) и =D13+D12*KOPEHb( 1/(6-3)) - рассчитыва­ ются интервальные оценки z (п. 3 общей схемы решения задачи);

•ячейки D16 и D17 содержат формулы =ФИШЕР0БР(014) и =ФИШЕРОБР(015) — вычисляются интервальные оценки ли­ нейного коэффициента корреляции (п. 4 общей схемы решения задачи);

•ячейка D18 содержит формулу =KOPEHb((l-D9"2)/(6 - 2)) - рассчитывается значение стандартной ошибки линейного коэф­ фициента корреляции (п. 5 общей схемы решения задачи).

Таким образом, с вероятностью 0,95 линейный коэффициент корреляции заключен в интервале от -0,855 до -0,998 со стан­ дартной ошибкой 0,014. Следовательно, прибыль обследованных коммерческих структур находится в тесной связи с затратами на производство единицы продукции.

Функция ФИШЕРОВ?

См, также ФИШЕР, КОРРЕЛ, ПИРСОН, КОВАР.

Синтаксис:

ФИШЕРОБР (Z)

264

Результат:

Рассчитывает обратное преобразование Фишера.

Аргументы:

z: значение, для которого осуществляется обратное преобра­ зование Фишера.

Замечания:

если Z не является числом, то функция ФИШЕРОБР помеща­ ет в ячейку значение ошибки #ЗНАЧ!.

МатематикО'Статистическая интерпретация:

См, описание функции ФИШЕР Функция обратного преобразования Фишера используется в

ситуациях, когда известно значение, полученное на основе пря­ мого преобразования Фишера, и необходимо найти значение ар­ гумента этого преобразования.

Например, формула =ФИШЕРОБР(-2,407) вычисляет значе­ ние -0,984 (сравните с формулой =ФИШЕР(-0,984), рассчиты­ вающей значение -2,407 в ячейке D13 табл. 13.5).

Уравнение для обратного преобразования Фишера имеет сле­ дующий вид:

X- е^^+Г

ГЛАВА 14 Регрессия

14.1.

Краткие сведения из теории статистики

В главе 13 были рассмотрены основные аспекты корреляцион­ ного анализа, который имеет своей задачей определение тесноты и направления связи между изучаемыми величинами. Наряду с корреляционным анализом обычно проводится и регрессионный анализ, который заключается в определении аналитического вы­ ражения связи зависимой случайной величины Y (называемой также результативным признаком) с независимыми случайными величинами Х^, ^2^м ^т (называемыми также факторами),

2в5

Форма связи результативного признака Y с факторами A^i, ^2v-M -^m получила H?i3B^HHQ уравнения регрессии. В зависимости от типа выбранного уравнения различают линейную и нелинейную ре­ грессию (в последнем случае возможно дальнейшее уточнение: квадратичная, экспоненциальная, логарифмическая и т. д.).

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

множественной (многофакторной) регрессией. Например, Кейн-

сом было предложено уравнение парной линейной регрессии, вы­ ражающей зависимость частного потребления С от располагаемо­ го дохода }^^/: С = Q + 6У^, где CQ> О- величина автономного по­ требления; О < 6 < 1 - предельная склонность к потреблению.

При изучении регрессии следует придерживаться определен­ ной последовательности этапов:

1.Задание аналитической формы уравнения регрессии и опре­ деление параметров регрессии.

2.Определение в регрессии степени стохастической взаимо­ связи результативного признака и факторов, проверка общего ка­ чества уравнения регрессии.

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

Основное содержание выделенных этапов рассмотрим на при­ мере множественной линейной регрессии, реализованной в режи­ ме «Регрессия» надстройки Пакет анализа Microsoft Excel.

Этап 1. Уравнение линейной множественной регрессии име­ ет вид

266

Параметры уравнения рефессии могут быть определены с по­ мощью метода наименьших квадратов* (именно этот метод и ис­ пользуется в Microsoft Excel). Сущность данного метода заключа­ ется в нахождении параметров модели (л/), при которых миними­ зируется сумма квадратов отклонений эмпирических (фактичес­ ких) значений результативного признака от теоретических, полу­ ченных по выбранному уравнению рефессии, т е.

Рассматривая S в качестве функции параметров л,- и проводя математические преобразования (дифференцирование), получа­ ем систему нормальных уравнений с т неизвестными (по числу параметров а^\

«ol^l+011^1^+021^2^1 +--.+0;„SXm^l =Z>^i;

Решив систему уравнений, находим значения параметров а^у являющихся коэффициентами искомого теоретического уравне­ ния рефессии.

Этап 2. Для определения величины степени стохастической взаимосвязи результативного признака Yn факторов ^необходи­ мо знать следующие дисперсии:

• общую дисперсию результативного признака 7, отображаю­ щую влияние как основных, так и остаточных факторов:

•В справочных системах «англоязычных» профамм этот метод обозна­

чается как LS (Least Squares Method),

267

Кл-Т)'

^ 2 _ (=1

п

где J - среднее значение результативного признака У;

• факторную дисперсию результативного признака У, отобра­ жающую влияние только основных факторов:

Hiyi-yf

Стф - —

п

• остаточную дисперсию результативного признака У, отобра­ жающую влияние только остаточных факторов:

hyi-yif

Сто = -

п-{т + \)

При корреляционной связи результативного признака и фак­ торов выполняется соотношение

(Уф < (Уу. при этом а^= <т^ + сг^.

Для анализа общего качества уравнения линейной многофак­ торной регрессии используют обычно множественный коэффици­ ент детерминации R}, называемый также квадратом коэффициен­

та множественной корреляции R. Множественный коэффициент

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

И определяет долю вариации результативного признака, обуслов­ ленную изменением факторных признаков, входящих в много­ факторную регрессионную модель.

Так как в большинстве случаев уравнение регрессии прихо­ дится строить на основе выборочных данных, то возникает вопрос

268

об адекватности построенного уравнения генеральным данным. Для этого проводится проверка статистической значимости коэф­ фициента детерминации Л^ на основе F-критерия Фишера:

где« - число наблюдений; т - число факторов в уравнении регрессии.

л- т.

Вматематической статистике доказывается, что если гипотеза

Щ: R^ = 0 выполняется, то величина / имеет /-распределение с к = ти l-n-m-l числом степеней свободы, т.е.

\-R^ т

Гипотеза HQ\ R^ = О о незначимости коэффициента детерми­ нации R^ отвергается, если F^ > F^^^

При значениях R > 0,7 считается, что вариация результатив­ ного признака Уобусловлена в основном влиянием включенных в регрессионную модель факторов X,

Для оценки адекватности уравнения регрессии часто также

используют показатель средней ошибки аппроксимации:

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

269