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

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

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

.Табл. 3. 2

Таблица вывода дополнительной регрессионной статистики

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

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

ln y = х1* ln (m1) + ... + xт *ln(mn) + ln(b) (3.4)

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

 Внимание. Формулы, которые возвращают массивы, должны быть введены как формулы массивов.¹²

Задание 5. Прогнозирование с помощью функций линейн() и лгрфприбл(),

В таблице (рис. 3.1.) приведены данные по продаже электробытовых товаров (холодильники, пылесосы, обогреватели и др.)1997 - 2004гг., полученные на основе данных фирмы "Бета"

Рис. 3. 1.. Данные для начала расчета по фирме "Бета"

Необходимо найти уравнение регрессии наилучшим образом описывающее результаты хозяйственной деятельности за 1997 – 2004 годы, полагая, что процессы, определяющие спрос населения на электробытовые товары остается неизменным в течение всего последующего времени.

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

• В ячейку А5 введите текст: Линейная статистика.

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

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

• Для этого выделите команду ВСТАВКА / ФУНКЦИЯ / выберите Категорию Статистические и в ней выберите функцию ЛИНЕЙН().

Рис. 3. 2. Выбор функции ЛИНЕЙН().для расчета линейной статистики

(Для вызова функции можно также щелкнуть на кнопке Вставка функций строки формул) В открывшемся диалоговом окне (рис. 3.3):

Рис. 3. 3. Диалоговое окно Аргументы функции ЛИНЕЙН()

• В поле "Известные значения Y" введите адреса ячеек, содержащих эти значения (B4:I4).

• В поле "Известные значения Х" введите адреса ячеек, содержащих эти значения - (B2:I2).

• В поле "Статистика" введите "1".

• Нажмите клавиши Ctrl + Shift + Enter.

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

В ячейке А6 будет помещено значение коэффициента А, а в ячейке В6 – коэффициента В линейной регрессии. Таким образом, теоретические значения величин товарооборота можно подсчитать в ячейках B5:I5 по формуле:

Y = 0.9982*X + 1.0418, (3.5)

где X это Порядковый номер года.

• Постройте график, в который включите значения фактического и теоретического товарооборота фирмы в зависимости от номера года. Значение фактического товарооборота представьте в точечном виде, теоретического товарооборота – в пунктирном. Для фактического товарооборота постройте линию тренда, как это описано в задании 1 и результаты расчетов запишите в ячейках В5:I5 (рис. 3.4).

Рис. 3. 4. Фрагмент рабочего листа Excel с примером расчета функции ЛИНЕЙН()

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

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

Рис. 3. 5. Фрагмент рабочего листа Excel с примером расчета функции ЛГРФПРИБЛ ()

• Найдите линию тренда и подсчитайте теоретические значения, оформите график, в соответствии с рис. 3.5.

Как видите, результаты расчетов достаточно близки.