Опции инструмента «Регрессия», связанные с оценкой дисперсии относительно рассматриваемой математической модели

3.1.2.1. Для установления и исследования несоответствий между экспериментальными и теоретическими значениями Y, определяемыми уравнением регрессии, поставить «Галочки» в опции «Остатки», «График остатков», «График подбора» или «Стандартизированные остатки». В результате появятся таблицы:

- «Вывод остатка»,

- «Вывод вероятности».

А также графики:

- «График остатков»,

- «График подбора»,

- «График нормального распределения».

- «График остатков» - отклонений фактических значений y = f(х_i) от значений y = f(х_i), рассчитанных в соответствии с теоретической моделью (полученным уравнением регрессии).

Примечание 3. «Стандартизированный остаток» представляет собой отношение «остатка» к «стандартной ошибке единичного наблюдения регрессионной статистики» (см. выше п. 3.1.1.3, таблица «Регрессионная статистика»).

3.1.2.2. Установить величины максимальных положительных и отрицательных остатков по полученному «Графику остатков».

Статистические функции MS Excel, основанные на линейной

Регрессии

3.2.1. Выделить массив из расположенных рядом не менее пяти строк и не менее двух столбцов (пустых ячеек) и открыть статистическую функцию ЛИНЕЙН (рис. 5), рассчитывающую коэффициенты m и b линейного уравнения y = mx + b.

Рис. 5. Аргументы функции ЛИНЕЙН

3.2.2. Ввести в качестве аргументов независимо от расположения в строках или в столбцах (без наименований строк или столбцов):

- «Известные значения x»,

- «Известные значения у», точно соответствующие этим значениям x.

Примечания 4:

- Аргумент «Конст» (см. рис. 5) - логическое значение, которое указывает, требуется ли, чтобы константа b в уравнении (4) была равна 0. Если «Конст» имеет значение ИСТИНА или отсутствует, то b вычисляется обычным образом. Если «Конст» имеет значение ЛОЖЬ (ставится значение 0), то m подбирается так, чтобы выполнялось b = 0.

- Аргумент «Статистика» - логическое значение, которое указывает, требуются ли дополнительные статистические данные по регрессии. Если аргумент «Статистика» имеет значение 0 («ЛОЖЬ») или отсутствует, то функция ЛИНЕЙН подсчитает только коэффициенты регрессии. (В этом случае достаточно предварительно выделить, см. п. 2.2.1, две ячейки (пустые), расположенные рядом в одной строке.) Если в качестве аргумента «Статистика» представлено любое другое число («ИСТИНА»), то кроме коэффициентов m и b производится расчёт дополнительной статистики в форме таблицы ячеек (табл. 2).

Таблица 2.

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

m	b
Стандартная ошибка коэфф. m	Стандартная ошибка коэфф. b
Коэффициент детерминации	Стандартная ошибка для оценки y
F-статистика (F-расчётное)	Число степеней свободы для нахождения F-критических значений
Сумма квадратов отклонений, обусловленных регрессией	Сумма квадратов остатков

В. К недостаткам дополнительной статистики функции ЛИНЕЙН относится то, что выводимые в указанном порядке результаты никак не обозначены. Поэтому для получения всеобъемлющих результатов РА в виде, доступном для недостаточно искушённых пользователей, рекомендуется пользоваться инструментом «Регрессия» [5].

3.2.3. Набрать комбинацию клавиш CTRL+SHIFT+ENTER, в результате чего в порядке, представленном в табл. 1, будут выведены коэффициенты регрессии и регрессионной статистики. (При нажатии «ОК» будет подсчитан только коэффициент m).

3.2.4. Сравнить и убедиться в равенстве соответствующих коэффициентов регрессии, полученных по п.п. 3.2.3., 3.1.1.3. и 2.5.2.

3.2.5. Открыть функцию «ТЕНДЕНЦИЯ» (рис. 6), которая для заданного массива новых значений x определяет значения y в соответствии с линейным трендом, рассчитанным для массивов известных значений y и известных значений x в виде уравнения y = mx + b.

Рис. 6. Аргументы функции ТЕНДЕНЦИЯ

3.2.6. Рассчитать значения функции ТЕНДЕНЦИЯ, введя следующие аргументы:

- «Известные значения x»;

- соответствующие им «Известные значения у»;

- в качестве аргумента «Новые значения x» - величины x, равные удвоенному и утроенному значению максимального известного значения x, ставя задачу узнать, какими должны быть при имеющейся линейной регрессии значения отклика, соответствующие этим новым значениям x;

- в качестве аргумента «Конст» - любое ненулевое число и нажать «ОК».

3.2.7. Открыть функцию ПРЕДСКАЗ (рис. 7), сходную с функцией ТЕНДЕНЦИЯ, но рассчитывающую в соответствии с линейным уравнением регрессии лишь одно прогнозируемое значение отклика по одному значению аргумента «x».

Рис. 7. Аргументы функции ПРЕДСКАЗ

3.2.8. Рассчитать с помощью функции ПРЕДСКАЗ значение отклика, соответствующее значению x, равному удвоенному максимальному известному значению x. (Результат расчёта этой и других функций, определяющих одно значение, появляется в окне аргументов сразу после внесения данных до нажатия «ОК».)

3.2.9. Открыть статистическую функцию НАКЛОН (рис. 8), устанавливающую наклон линии линейной регрессии для точек данных, образованных известными значениями y_i и известными значениями x_i, иначе говоря, коэффициент m в уравнении y = mx + b.

Рис. 8. Аргументы функции НАКЛОН

3.2.10. Рассчитать коэффициент m в уравнении y = mx + b с помощью функции НАКЛОН и сравнить с коэффициентом m, рассчитанным с помощью функции ЛИНЕЙН, см. п. 3.2.3, и с коэффициентом b₀, рассчитанным с помощью инструмента Регрессия, см. п. 3.1.1.3. для тех же исходных данных.

3.2.11. Открыть статистическую функцию ОТРЕЗОК, имеющую те же аргументы, что и функция НАКЛОН и вычисляющую точку пересечения линии регрессии с осью y, т.е. при x = 0, иначе говоря, коэффициент bв уравнении регрессии y = mx + b.

3.2.12. Рассчитать коэффициент b в уравнении с помощью функции ОТРЕЗОК и сравнить с коэффициентом b, рассчитанным с помощью функции ЛИНЕЙН, см. п. 3.2.3, и с коэффициентом b₁, рассчитанным с помощью инструмента Регрессия, см. п. 3.1.1, для тех же исходных данных.