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

функция ПРЕДСКАЗ

См. также ЛИНЕЙН, ТЕНДЕНЦИЯ.

Синтаксис:

ПРЕДСКАЗ (х; известные значения у; известные зна­ чения х)

Результат:

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

Аргументы:

• х: точка данных, для которой предсказывается значение;

Замечания:

• если аргумент X не является числом, то функция ПРЕДСКАЗ помещает в ячейку значение ошибки #ЗНАЧ!;

данных, то функция ПРЕДСКАЗ помещает в ячейку значение ошибки #Н/Д;

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

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

См, описание функции ТЕНДЕНЦИЯ.

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

Пример 14.3. Покупатель планирует приобрести квартиру в де­ кабре текущего года. В июне он собирает информацию о ценах на подобную квартиру за последние 6 мес. Какую цену может ожи­ дать покупатель в декабре?

Рассмотрим решение задачи в среде Microsoft Excel (табл.

14.14).

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

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

•массив D3:D8 содержит порядковые номера месяцев в рас­ сматриваемом периоде;

290

•ячейка С9 содержит формулу =ПРЕДСКАЗ(12;СЗ:С8;03:08)

—рассчитывается прогнозируемая стоимость квартиры в декабре (12 — порядковый номер декабря).

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

Примечание. Аналогичное решение может быть получено и с помощью функции ТЕНДЕНЦИЯ. Для этого в ячейку С9 необходимо ввести формулу =ТЕНДЕНЦИЯ(СЗ:С8;03:08; 12; 1).

Функция НАКЛОН

См. также ЛИНЕЙН.

Синтаксис:

НАКЛОН (известные значения у\ известные значе­ ния х)

Результат:

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

Аргументы:

291

Замечания:

•аргументы должны быть числами или именами, массивами или ссылками, содержащими числа;

•если аргумент, который является массивом или ссылкой, со­ держит текстовые, логические значения или пустые ячейки, то та­ кие значения игнорируются; однако ячейки с нулевыми значени­ ями учитываются;

• если аргументы известные значения у и известные значения х пусты или содержат различное число точек данных, то функция СТОШУХ помещает в ячейку значение ошибки #Н/Д.

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

Наклон линии линейной регрессии является мерой скорости изменения результативного признака Уотносительно изменения факторного признака X и определяется отношением расстояния по вертикали на расстояние по горизонтали между двумя любыми точками теоретической прямой:

лл

У)-- Уг . .

Xj-Xi

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

Значение наклона линии регрессии (коэффициента ^i) удобно находить с помощью функции НАКЛОН, исключающей предва­ рительные расчеты. Заметим, что это же значение рассчитывает и функция ЛИНЕЙН в таблице дополнительной рефессионной статистики (см. значение а^ в табл. 14.10).

Пример 14.4. Требуется рассчитать наклон линии линейной регрессии для примера 14.3.

Формула =HAKfIOH(C3:C8;D3:D8) рассчитает искомое значение Ui = 56,86. Это же значение вычисляет и формула =ЛИНЕЙН(СЗ:С8;03:В8;1;1) в таблице дополнительной рег­ рессионной статистики (см. значение а^ в табл. 14.10). Кроме того, аналогичное решение можно получить с помощью фор­ мулы

292

I.(yi-y)(Xi-x) ai=^ .

Вместе с тем заметим, что данная формула требует довольно громоздких расчетов, поэтому более предпочтительным является использование функции НАКЛОН.

Функция ОТРЕЗОК

См. также ЛИНЕЙН.

Синтаксис:

ОТРЕЗОК (известные значения дс; известные значе­ ния у)

Результат:

Рассчитывает значение, соответствующее точке пересечения линии парной линейной рефессии с осью К

Замечания:

•аргументы должны быть числами или массивами, содержа­ щими числа;

•если аргумент, который является массивом, содержит текс­ товые, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитыва­ ются;

• если аргументы известные значения >> и известные зна­ чения X пусты или содержат различное число точек данных, то функция ОТРЕЗОК помещает в ячейку значение ошибки #Н/Д,

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

Функция ОТРЕЗОК используется, когда нужно определить значение результативного признака при значении факторного признака, равном 0. В этом случае уравнение у ~ а^ + а^х прини­ мает вид р = ао-

Например, функцию ОТРЕЗОК можно использовать, чтобы предсказать электрическое сопротивление металла при темпера-

293

туре 0*С, если имеются данные измерений при комнатной темпе­ ратуре и выше.

Пример 14.5. Для задачи, приведенной в примере 14.3, рассчи­ тать, какую предположительно стоимость имела квартира в дека­ бре прошедшего года.

В этой задаче линия рефессии строится на основании данных за первые шесть месяцев текущего года, где 1 — январь. Поэтому для построенной линии рефессии декабрь предыдущего года будет иметь значение О, ноябрь - значение —1, октябрь значение - 2 и т.д. Тогда формула =OTPE30K(C3:C8;D3:D8) рассчитает иско­ мое значение стоимости квартиры за декабрь предыдущего года, равное 22489,33 у. е.

Заметим, что это же значение вычисляет и функция ЛИНЕЙН в таблице дополнительной рефессионной статистики (см. значе­ ние CQ В табл. 14.10). Кроме того, аналогичное решение можно по­ лучить и с помощью формулы =ТЕНДЕНЦИЯ(СЗ:С8;ВЗ:О8;0;1)

(здесь О — значение факторного признака X, для которого функ­ ция ТЕНДЕНЦИЯ рассчитывает соответствующее значение ре­ зультативного признака У).

Функция СТОПГСХ

См. также ЛИНЕЙН.

Синтаксис:

CTOIIIYX (известные значения у; известные значе­ ния х)

•аргументы должны быть числами или массивами, содержа­ щими числа;

•если аргумент, который является массивом или ссылкой, со­ держит текстовые, логические значения или пустые ячейки, то та­ кие значения игнорируются; однако ячейки с нулевыми значени­ ями учитываются;

294

• если аргументы известные значения у и известные значения х пусты или содержат различное число точек данных, то функция CTOIUYX помешает в ячейку значение ошибки #Н/Д.

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

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

где У1 - /-е фактическое значение результативного признака; у^ - /-е теоретическое значение результативного признака; п - объем выборочной совокупности.

Для парной регрессии значение стандартной ошибки оценки удобно находить с помощью функции СТОИГУХ, исключающей предварительные расчеты. Заметим, что это же значение рассчи­ тывает и функция ЛИНЕЙН в таблице дополнительной регресси­ онной статистики {см, значение sey в табл. 14.10).

Пример 14.6. Требуется рассчитать стандартную ошибку Сощ для примера 14.3.

Формула =CTOIIIYX(C3:C8;D3:D8) рассчитает искомое значение аош=53,64. Это же значение вычисляет и формула = J I H H E H H ( C 3 : C 8 ; D 3 : D 8 ; 1 ; 1 ) в таблице дополнительной регрес­ сионной статистики {см, значение scy в табл. 14.10).

Необходимо отметить, что функцию CTOUIYX нельзя исполь­ зовать применительно к множественной регрессии. Для этого не­ обходимо использоваться функцию ЛИНЕЙН с аргументом ста­ тистика =1 или следующую формулу:

29S

Примечание, В множественной регрессии для получения теоретических значений результативного признака У удобно использовать функцию ТЕН­ ДЕНЦИЯ.

Функция ПИРСОН

См, также КОРРЕЛ, ЛИНЕЙН.

Синтаксис:

ПИРСОН (массив!; массив2)

Результат:

Рассчитывает значение коэффициента корреляции Пирсона для парной линейной рефессии (аналогично функции КОРРЕЛ),

Аргументы:

•массив!: множество значений факторного признака А";

•массив2: множество значений результативного признака К

Замечания:

•аргументы должны быть числами или массивами, содержа­ щими числа;

•если аргумент, который является массивом, содержит текс­ товые, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитыва­ ются;

•если аргументы массив! или массив! пусты или содержат различное число точек данных, то функция ПИРСОН помещает в ячейку значение ошибки #Н/Д.

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

Функция ПИРСОН, так же как и функция КОРРЕЛ, рассчи­ тывает значение линейного коэффициента корреляции* между двумя множествами данных.

Между линейным коэффициентом корреляции и коэффици­ ентом рефессии существует определенная зависимость, выражае­ мая формулой

а.

* Линейный коэффициент корреляции получил также название коэф­ фициента корреляции Пирсона.

296

где Oi - коэффициент при факторном признаке в уравнении рег­ рессии (flj определяет наклон линии регрессии — см. описа­ ние функции НАКЛОН);

Сф- стандартное отклонение факторного признака; Су - стандартное отклонение результативного признака.

Так, для примера 14.3 формула =nMPCOH(D3:D8;C3:C8) рассчитывает значение 0,91, такое же, как и формула =НАКЛОН (СЗ:С8;03:08)*СТАНДОТКЛОНП(03:08)/СТАНДОТКЛОНП (СЗ:С8).

Функция КВПИРСОН

•аргументы должны быть числами или массивами, содержа­ щими числа;

•если аргумент, который является массивом, содержит тексто­ вые, логические значения или пустые ячейки, то такие значения иг­ норируются; однако ячейки с нулевыми значениями учитываются;

• если аргументы известные значения у и известные зна­ чения X пусты или содержат различное число точек данных, то функция КВПИРСОН помещает в ячейку значение ошибки #Н/Д.

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

См, описание функции ПИРСОН.

Функция КВПИРСОН рассчитывает квадрат коэффициента корреляции Пирсона для парной линейной регрессии.

Так, дл^ примера 14.3 формула =КВПИРСОН(СЗ:С8;03: :D8) рассчитывает значение 0,83, такое же, как и формула =СТЕПЕНЬ(ПИРСОН(03:В8;СЗ:С8);2).

297

14.4.

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

Функция ЛГРФПРИБЛ

См, также ЛИНЕЙН, РОСТ

Синтаксис:

ЛГРФПРИБЛ (известные значения у\ известные зна­ чения х\ конст; статистика).

Результат:

Рассчитывает массив данных, описывающих уравнение экспо­

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

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

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

Замечания:

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

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

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

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

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

298

•если аргумент статистика = О или опущен, то функция ЛГРФПРИБЛ вычисляет только коэффициенты а-^ (в том числе и^о);

•если аргумент статистика = 1, то функция ЛГРФПРИБЛ рассчитывает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {a^;a^_i;...;ai;ao;'S^m»*^^m^i'

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

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

SCQ : стандартное значение ошибки для коэффициента aQ {SCQ = = #Н/Д, если аргумент канет == 0);

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

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

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

•ssreg • регрессионная сумма квадратов; 55residостаточная сумма квадратов.

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

Внимание! Дополнительная регрессионная статистика, которую рассчитывает функция ЛГРФПРИБЛ, основана на следующей ли­ нейной модели:

1пу == In^o "^ ^:ilnai + Xjlnaj + ... + xjna^.

Это следует помнить при оценке дополнительной регрессионной статистики. Например, для расчета значимости коэффициентов рег­ рессии используется формула /р = ln(|aj)/a^. (сравните с формулой для линейной рефессии /р - laj/a^..

299