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

m1; m2;…mn;b	Коэффициенты уравнения регрессии
se1;se2,.....sen	Стандартные значения ошибок для коэффициентов m1;m2;...mn
seb	Стандартное отклонение ошибки для постоянной b (если « константа» имеет значение «Ложь», то выводится признак ошибки «#Н.Д»
R2	Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y.
sey	Стандартная ошибка для оценки Y
F	F - статистика (Критерий Фишера). Используется для оценки того, являются ли связь между зависимой и независимой переменными случайной или нет. Оценка производится найденной величины F с табличным значением при заданном количестве степеней свободы
df	Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН
ssreg	Регрессионная сумма квадратов
ssresid	Остаточная сумма квадратов

Дополнительная регрессионная статистика выводится в виде таблицы в предварительно выделенном интервале ячеек.

mn	mn	....	m2	m1	b
sen	sen-1	....	se2	se1	seb
R2	sey
F	df
ssrtg	ssresid

Важно! Методы, которые используются для проверки уравнений, полученных с помощью функции ЛГРФПРИБЛ (), такие же, как и для функции ЛИНЕЙН (). Однако, дополнительная статистика, которую возвращает функция ЛГРФПРИБЛ (), основана на следующей линейной модели:

ln y = х₁* ln (m1) +... + x_т *ln(m_n) + ln(b)

Это следует помнить при оценке дополнительной статистики, особенно значений se_i и se_b, которые следует сравнивать с ln(m_i) и ln (b), а не с m_i и b. Для получения более подробной информации, см. любой справочник по математической статистике.

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

Внимание: В том случае, если в качестве независимой переменной используется дата, товведитедополнительную строку с номером этой даты (в примере ячейки A2:I2) и используйте ее в дальнейшем в качестве независимой переменной. Excel может выдать ошибку при использовании даты в качестве независимой переменной.

· На рабочем листе Excel выделите область ячеек A6: B10,

· Вызовите мастер функций ЛИНЕЙН()

· В открывшемся диалоговом окне функции введите:

· в окно «Известные значения Y» адреса ячеек, содержащих эти значения (B4:I4)

· В окно «Известные значения Х» введите адреса ячеек, содержащих эти значения (B2:I2);

· В окно «конст» введите 1;

· В окно «статистика» введите 1;

· Нажмите клавиши CTRL+SHIFT+ENTER

·

FВ результате выполненных действий, в выделенный интервал ячеек будут возвращены значения статистики уравнения линейной регрессии.

0,998214286	1,0417857
0,111690715	0,5640105
0,930131386	0,7238386
79,87546906
41,85013393	3,1436536

Уравнение линейной регрессии для примера будет иметь вид

Y = 0.998*X + 1.042

R² = 0,93

Подобным же образом может быть найдено уравнение логарифмического приближения (функция ЛГРФПРИБЛ()).

Результат:

1,2483849	1,7956126
0,0368701	0,1861847
0,8578381	0,2389453
36,205413
2,0671435	0,3425692

Уравнение экспоненциальной регрессии для примера будет иметь вид

Y = 1,248^X * 1,796

R² = 0,86

Уравнение для прямой линии имеет следующий вид:

y = mx + b или

y = m1x1 + m2x2 +... + b (в случае нескольких диапазонов значений x),

Уравнение для экспоненциальной регрессии имеет следующий вид:

Y =b*m^x

Y = (b*(m1^x1)*(m2^x2)*.....)(в случае нескольких диапазонов значений x).

где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная.

Функции прогноза ТЕНДЕНЦИЯ(), РОСТ()

Когда нас интересует прогноз значений Y на какой-то интервал времени (или другой отрезок независимых переменных), Excel предлагает использовть функции прогноза, основанные на линейном или экспоненциальном приближении[4].

Функция ТЕНДЕНЦИЯ()

Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы: известные _значения_ Y и известные _значения_ X. Возвращает значения Y, в соответствии с этой прямой для заданного массива новые _значения_ X.

F Для прогнозирования нового ряда значений Y для ряда значений Х:

· установите курсор мыши в диапазон ячеек, куда должны быть возвращены прогнозируемые значения Y;

· вызовите функцию ТЕНДЕНЦИЯ() (категория функция «Статистические»)

o в окно «Известные значения_Y» введите адреса ячеек, содержащих известные значения Y;

o в окно «Известные значения_Х», введите адреса ячеек известных значений Х;

o в окно «Новые значения_Х» введите адреса ячеек, содержащие новые значения Х.

o нажмите клавиши CTRL+SHIFT+ENTER.