Реализация метода наименьших квадратов в MS EXCEL

4.1. Использование встроенных функций

Вычисление коэффициентов регрессии осуществляется с помощью функции

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

где

Значения_y — массив значений y,

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

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

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

Необходимо помнить, что результатом функций ЛИНЕЙН()является множество значений – массив.

Для расчета коэффициента корреляции используется функция

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

возвращающая значения коэффициента корреляции, где Массив1 — массив значений y, Массив2 — массив значений x. Массив1 и Массив2 должны быть одной размерности.

ПРИМЕР 1. Зависимость y(x) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

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

Рис. 2

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

Рис. 3

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

Для расчета значения коэффициента корреляции в ячейку С6 была введена следующая формула:

С7=КОРРЕЛ(B3:J3;B2:J2).

Рис. 4

Зная коэффициенты регрессии аи b вычислим значения функции y=ax+b для заданных x. Для этого введем формулу

B5=$A$7*B2+$B$7

и скопируем ее в диапазон С5:J5(рис. 5).

Рис. 5

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

Рис. 6

Чтобы изменить вид линии регрессии, выполним следующие действия. Щелкнем правой кнопкой мыши по точкам, изображающим график линии, выберем команду Тип диаграммыи установим вид точечной диаграммы, так как показано на рис. 7.

Рис. 7

Тип линии, ее цвет и толщину можно изменить следующим образом. Выделить линию на диаграмме, нажать правую кнопку мыши и в контекстном меню выбрать команду Формат рядов данных… Далее сделать установки, например, так как показано на рис. 8.

Рис. 8

В результате всех преобразований получим график экспериментальных данных и линию регрессии в одной графической области (рис. 9).

Рис. 9

4.2. Использование линии тренда.

Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы – линия тренда.

ПРИМЕР 2. В результате эксперимента была определена некоторая табличная зависимость.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости.

Решение задачи можно разбить на следующие этапы: ввод исходных данных, построение точечного графика и добавление к этому графику линии тренда.

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

Рис. 10

Появившееся диалоговое окно позволяет построить аппроксимирующую зависимость.

На первой вкладке (рис. 11) этого окна указывается вид аппроксимирующей зависимости.

На второй (рис. 12) определяются параметры построения:

· название аппроксимирующей зависимости;

· прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда);

· показывать ли точку пересечения кривой с прямой y=const;

· показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме);

· помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).

Рис. 11

Рис. 12

Выберем в качестве аппроксимирующей зависимости полином второй степени (рис. 11) и выведем уравнение, описывающее этот полином на график (рис. 12). Полученная диаграмма представлена на рис. 13.

Рис. 13

Аналогично с помощью линии тренда можно подобрать параметры таких зависимостей как

· линейная y=a∙x+b,

· логарифмическая y=a∙ln(x)+b,

· экспоненциальная y=a∙e^b,

· степенная y=a∙x^b,

· полиномиальная y=a∙x²+b∙x+c, y=a∙x³+b∙x²+c∙x+d и так далее, до полинома 6-й степени включительно,

· линейная фильтрация.

4.3. Использование решающего блока

Значительный интерес представляет реализация в MS Excel подбора параметров методом наименьших квадратов с использованием решающего блока. Эта методика позволяет подобрать параметры функции любого вида. Рассмотрим эту возможность на примере следующей задачи.

ПРИМЕР 3. В результате эксперимента получена зависимость z(t) представленная в таблице

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Подобрать коэффициенты зависимости Z(t)=At⁴+Bt³+Ct²+Dt+K методом наименьших квадратов.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных

(10).

Рассмотрим процесс решения задачи оптимизации (рис. 14).

Рис. 14

Пусть значения А, В, С, D и К хранятся в ячейках A7:E7. Рассчитаем теоретические значения функции Z(t)=At⁴+Bt³+Ct²+Dt+K для заданных t(B2:J2). Для этого в ячейку B4 введем значение функции в первой точке (ячейка B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Скопируем эту формулу в диапазон С4:J4 и получим ожидаемое значение функции в точках, абсциссы которых хранится в ячейках B2:J2.

В ячейку B5 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

B5=(B4-B3)^2,

и скопируем ее в диапазон С5:J5. В ячейке F7 будем хранить суммарную квадратичную ошибку (10). Для этого введем формулу:

F7 = СУММ(B5:J5).

Воспользуемся командой Сервис®Поиск решения и решим задачу оптимизации без ограничений. Заполним соответствующим образом поля ввода в диалоговом окне, показанном на рис. 14 и нажмем кнопку Выполнить. Если решение будет найдено, то появится окно, изображенное на рис. 15.

Результатом работы решающего блока будет вывод в ячейки A7:E7значений параметров функции Z(t)=At⁴+Bt³+Ct²+Dt+K. В ячейках B4:J4 получим ожидаемые значение функции в исходных точках. В ячейке F7 будет храниться суммарная квадратичная ошибка.

Изобразить экспериментальные точки и подобранную линию в одной графической области можно, если выделить диапазон B2:J4, вызвать Мастер диаграмм, а затем отформатировать внешний вид полученных графиков.

Рис. 17 отображает рабочий лист MS Excel после проведенных вычислений.

Рис. 15

Рис. 16

Рис. 17

5. СПИСОК ЛИТЕРАТУРЫ

1. Алексеев Е.Р., Чеснокова О.В., Решение задач вычислительной математики в пакетах Mathcad12, MATLAB7, Maple9. – НТ Пресс, 2006.–596с. :ил. –(Самоучитель)

2. Алексеев Е.Р., Чеснокова О.В., Е.А. Рудченко, Scilab, решение инженерных и математических задач. –М., БИНОМ, 2008.–260с.

3. Березин И.С., Жидков Н.П., Методы вычислений.–М.:Наука, 1966.–632с.

4. Гарнаев А.Ю., Использование MS EXCEL и VBA в экономике и финансах. – СПб.: БХВ - Петербург, 1999.–332с.

5. Демидович Б.П., Марон И А., Шувалова В.З., Численные методы анализа.–М.:Наука, 1967.–368с.

6. Корн Г., Корн Т., Справочник по математике для научных работников и инженеров.–М., 1970, 720с.

7. Алексеев Е.Р., Чеснокова О.В. Методические указания к выполнению лабораторных работ в MS EXCEL. Для студентов всех специальностей. Донецк, ДонНТУ, 2004. 112 с.