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

Как ранее уже рассматривалось, линейная аппроксимация основывается на представлении аппроксимирующей функции в форме прямой линиии, которая описывается выражением в общем виде как y = ax+ b. Исходя из метода наименьших квадратов, вычисляются коэффициенты регрессии a и b. Программа 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 должны иметь одинаковое количество точек данных.

Порядок выполнения описанных операций рассматривается в следующем примере.

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

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

В диапазоне ячеек A1:J2 листа MS Excel размещается заданная таблица. По значениям этой таблицы строится точечный график. Рабочий лист имеет вид, изображенный на рис. 22.2.

Рис. 22.2

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

Рис. 22.3

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

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

Рис. 22.4

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

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

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

Рис. 22.5

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

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

Рис. 22.6 Рис. 22.7

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

Рис. 22.8