Применение метода наименьших квадратов в Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Применение метода наименьших квадратов в Excel

Реализация метода наименьших квадратов в 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) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции.

y0.51.52.53.5
x2.392.813.253.754.114.454.855.25

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

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

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

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

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

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

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

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

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

Читайте также:  Выравнивание ячеек под один размер в Microsoft Excel

Рис. 6

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

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

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

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

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

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

0.150.160.170.180.190.20
4.48174.49305.47396.04966.68597.3891

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.3. Использование инструмента анализа вариантов: Поиск решения.

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

Читайте также:  6 способов замены точки на запятую в программе Microsoft Excel

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

0,660,91,171,471,71,742,082,633,12
38,968,864,466,564,9559,3682,690,63113,5

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

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

(10).

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

Пусть значения А, В, С, D и К хранятся в ячейках A7:E7. Рассчитаем теоретические значения функции Z(t)=At 4 +Bt 3 +Ct 2 +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 4 +Bt 3 +Ct 2 +Dt+K. В ячейках B4:J4 получим ожидаемые значение функции в исходных точках. В ячейке F7 будет храниться суммарная квадратичная ошибка.

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

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

Метод наименьших квадратов в Excel

Программа Excel – мощный табличный редактор, позволяющий выполнять огромное количество различных операций и задач. В данной статье мы разберем, как можно применить метод наименьших квадратов (МНК), который используется для решения различных задач с минимизацией суммы квадратов отклонений некоторых функций от искомых переменных.

Подготовительный этап: активируем надстройку “Поиск Решения”

Прежде, чем приступить к решению основной задачи, потребуется активировать надстройку “Поиск решения” в программе.

  1. Идем в меню “Файл”.
  2. В перечне слева выбираем пункт “Параметры”.
  3. В правой части подраздела “Надстройки” выбираем для параметра “Управление” вариант “Надстройки Excel” и жмем “Перейти”.
  4. Появится окно для выбора нужных надстроек. Устанавливаем галочку напротив пункта “Поиск решения” и щелкаем OK.

Этап 1: исходные данные

Давайте разберем применение метода наименьших квадратов, решив конкретный пример. Допустим, у нас есть два ряда числовых значений – X и Y.

Данная зависимость может быть описана уравнением ниже:

Читайте также:  Замена символов в Microsoft Excel

Также, мы знаем, что если X=0, то и Y=0. А значит, данное уравнение можно записать так:

Приступим к выполнению нашей задачи, которая заключается в нахождении суммы квадратов разности.

Этап 2: решаем задачу с применением МНК

  1. Столбцу, находящемся слева от X, задаем имя N пишем число “1” (примерное значение первого коэф. N) напротив первого значения ряда X.
  2. Столбцу с правой стороны от Y задаем название NX. Затем в самой верхней ячейке (напротив первых значений рядов X и Y) пишем формулу произведения коэф. N на соответствующее ему значение из столбца X. При этом адрес ячейки с коэффициентом нужно сделать абсолютным, чтобы он не менялся при копировании формулы. По готовности жмем Enter.
  3. Наводим указатель мыши на ячейку с полученным результатом. Как только появится черный плюсик (маркер заполнения), зажав левую кнопку мыши тянем его вниз до последней строки таблицы.
  4. Получаем результаты расчетов в каждой ячейке столбца NX.
  5. Теперь нужно посчитать сумму разностей квадратов значений Y и NX. Встаем в самую верхнюю ячейку столбца справа от NX (не считая шапки таблицы) и щелкаем по значку “Вставить функцию” (fx).
  6. В окне вставки функции выбираем категорию “Математические”, находим оператор “СУММКВРАЗН” и щелкаем OK.
  7. Теперь нужно заполнить аргументы функции:
    • в поле “Массив_x” указываем координаты диапазона ячеек столбца Y (без шапки). Адреса ячеек можно указать как вручную, напечатав их с клавиатуры, так и путем выделения с помощью зажатой левой кнопки мыши в самой таблице.
    • в поле “Массив_y” указываем диапазон ячеек столбца NX.
    • жмем Enter, когда все готово.
  8. Переключаемся во вкладку “Данные”. В группе “Анализ” щелкаем по функции “Поиск решения”.
  9. Нам предстоит заполнить параметры поиска решения:
    • в поле “Оптимизировать целевую функцию” следует указать ссылку на ячейку с функцией “СУММКВРАЗН”. Сделать это можно вручную или выбрав элемент в таблице.
    • для опции “До” выбираем вариант – “Минимум”.
    • в поле “Изменяя ячейки переменных” нужно указать координаты ячейки, в которой находится соответствующее значение коэф. N.
    • по готовности нажимаем “Найти решение”.
  10. После выполнения функции появится окно с результатами поиска решения и произойдет замена значения в столбце N. Найденная величина является наименьшим квадратом функции. Нажимаем OK, если полученный результат удовлетворителен.

Заключение

Итак, мы только что разобрали на практическом примере, каким образом можно применить метод наименьших квадратов в Эксель. На практике могут встречаться более сложные задачи, однако, в целом логика действий схожа с той, что мы описали.

Ссылка на основную публикацию
Adblock
detector