Решение поставленной задачи средствами MS Excel

Вычисление коэффициентов регрессии осуществляется с помощью функции ЛИНЕЙН():

ЛИНЕЙН(Значения_y; Значения_x; Конст; статистика)

Значения_y - массив значений y.

Значения_x - необязательный массив значений x, если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значения_y.

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y = ax.

Статистика - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b.

Для вычисления множества точек на линии регрессии используется функция ТЕНДЕНЦИЯ.

ТЕНДЕНЦИЯ(Значения_y; Значения_x; Новые_значения_x; Конст)

Значения_y - массив значений y, которые уже известны для соотношения y = ax + b.

Значения_x - массив значений x.

Новые_значения_x - новый массив значений, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Если Новые_значения_x опущены, то предполагается, что они совпадают с массивом значений х.

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если Конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения а подбираются таким образом, чтобы выполнялось соотношение y = ax. Необходимо помнить, что результатом функций ЛИНЕЙН, ТЕНДЕНЦИЯ является множество значений - массив.

Для расчета коэффициента корреляции используется функция КОРРЕЛ, возвращающая значения коэффициента корреляции:

КОРРЕЛ(Массив1;Массив2)

Массив1 - массив значений y.

Массив2 - массив значений y.

Массив1 и Массив2 должны иметь одинаковое количество точек данных.

ПРИМЕР 8.1. Известна табличная зависимость G(L). Построить линию регрессии и вычислить ожидаемое значение в точках 0, 0.75, 1.75, 2.8, 4.5.

L		0,5		1,5		2,5		3,5
G		2,39	2,81	3,25	3,75	4,11	4,45	4,85	5,25

Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид изображенный на рис. 8.2.

Рис. 8.2

Для того, чтобы рассчитать значения коэффициентов регрессии а и b выделим ячейки К2:L2, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН. Заполним появившееся диалоговое окно так, как показано на рис. 8.3 и нажмем Ок.

Рис. 8.3

В результате вычисленное значение появится только в ячейке К2 (см. рис.8.4). Для того чтобы вычисленное значение появилось и в ячейке L2 необходимо войти в режим редактирования, нажав клавишу F2, а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER.

Для расчета значения коэффициента корреляции в ячейку M2 была введена следующая формула: М2 = КОРРЕЛ(B1:J1;B2:J2) (см. рис. 8.4).

Рис. 8.4

Для вычисления ожидаемого значения в точках 0, 0.75, 1.75, 2.8, 4.5 занесем их в ячейки L9:L13. Затем выделим диапазон ячеек M10:M13 и введем формулу:

= ТЕНДЕНЦИЯ(B2:J2;B1:J1;L9:L13).

Для того чтобы вычисленные значения появились и в ячейках M10:M13 необходимо нажать комбинацию клавиш CTRL+SHIFT+ENTER.

Рис. 8.5

Изобразим линию регрессии на диаграмме. Для этого выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные. В появившемся диалоговом окне (см. рис. 8.5), для добавления линии регрессии щелкнем по кнопке Добавить.

В качестве имени введем Линия регрессии, в качестве Значения Х: L9:L13, в качестве Значения Y: M9:M13. Далее выделяем линию регрессии, для изменения ее типа щелкаем правой кнопкой мыши и выбираем команду Тип диаграммы (см. рис. 8.6). Для форматирования линии регрессии (можно изменить толщину линии, цвет, тип маркера и т.п) дважды щелкаем по ней (см. рис. 8.7).

Рис. 8.6	Рис. 8.7

После форматирования графика рабочий лист примет вид, изображенный на рис. 8.8.

Рис. 8.8

Дата добавления: 2015-05-26