Метод наименьших квадратов и поиск решения в Excel. Применение метода наименьших квадратов в Excel Расчет методом наименьших квадратов xls

Метод наименьших квадратов и поиск решения в Excel. Применение метода наименьших квадратов в Excel Расчет методом наименьших квадратов xls

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

Как включить надстройку «поиск решения»

Для начала разберемся, как эту надстройку включить.

1. Идем в меню «Файл» и выбираем пункт «Параметры Excel»

2. В появившемся окне выбираем «Поиск решения» и нажимаем «перейти».

3. В следующем окне ставим галочку напротив пункта «поиск решения» и нажимаем «ОК».

4. Надстройка активирована — теперь ее можно найти в пункте меню «Данные».

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

Теперь вкратце о методе наименьших квадратов (МНК) и о том, где его можно применять.

Допустим, у нас есть набор данных после совершения нами какого-то эксперимента, где мы изучали влияния величины Х на величину Y.

Мы хотим это влияние описать математически, чтобы потом этой формулой пользоваться и знать, что, если мы поменяем величину Х на столько-то, получим величину Y такую-то.

Возьму супер-простой пример (см. рис.).

Ежу понятно, что точки расположились друг за другом как будто по прямой, а потому мы смело предполагаем, что наша зависимость описывается линейной функцией y=kx+b. При этом мы точно уверены, что при X равном нулю значение Y тоже равно нулю. Значит, функция, описывающая зависимость, будет еще проще: y=kx (вспоминаем школьную программу).

В общем, нам предстоит найти коэффициент k. Вот это мы и сделаем с помощью МНК с применением надстройки «поиск решения».

Метод заключается в том, чтобы (здесь — внимание: нужно вдуматься) сумма квадратов разностей экспериментально полученных и соответствующих расчетных значений была минимальной. То есть когда X1=1 реально измеренное значение Y1=4,6, а расчетное y1=f (x1) равно 4, квадрат разности будет (y1-Y1)^2=(4-4,6)^2=0,36. Со следующими так же: когда X2=2, реально измеренное значение Y2=8,1, а расчетное у2 равно 8, квадрат разности будет (y2-Y2)^2=(8-8,1)^2=0,01. И сумма всех этих квадратов должна быть минимально возможной.

Итак, приступим к тренировке по использованию МНК и надстройки Excel «поиск решения» .

Применение надстройки поиск решения

1. Если не включили надстройку «поиск решения», то возвращаемся к пункту Как включить надстройку «поиск решения» и включаем

2. В ячейку А1 введем значение «1». Эта единица будет первым приближением к реальному значению коэффициента (k) нашей функциональной зависимости y=kx.

3. В столбце B у нас расположились значения параметра X, в столбце C — значения параметра Y. В ячейках столбца D вводим формулу: «коэффициент k умножить на значение Х». Например, в ячейке D1 вводим «=A1*B1», в ячейке D2 вводим «=A1*B2» и т.д.

4. Мы считаем, что коэффициент к равен единице и функция f (x)=у=1*х – это первое приближение к нашему решению. Можем рассчитать сумму квадратов разностей между измеренными значениями величины Y и рассчитанными по формуле y=1*х. Можем все это сделать вручную, вбивая в формулу соответствующие ссылки на ячейки: «=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2. и т.д. В конце концов ошибаемся и понимаем, что потеряли кучу времени. В Excel для расчета суммы квадратов разностей есть специальная формула, «СУММКВРАЗН», которая все за нас и сделает. Введем ее в ячейку А2 и зададим исходные данные: диапазон измеренных значений Y (столбец C) и диапазон рассчитанных значений Y (столбец D).

4. Сумму разностей квадратов рассчитали – теперь идем во вкладку «Данные» и выбираем «Поиск решения».

5. В появившемся меню в качестве изменяемой ячейки выбираем ячейку A1 (та, что с коэффициентом k).

6. В качестве целевой выбираем ячейку A2 и задаем условие «установить равной минимальному значению». Помним, что это ячейка, где у нас производится расчёт суммы квадратов разностей расчетного и измеренного значений, и сумма эта должна быть минимальной. Нажимаем «выполнить».

7. Коэффициент k подобран. Теперь можно убедиться, что рассчитанные значения теперь очень близки к измеренным.

Вообще, конечно, для аппроксимации экспериментальных данных в Excel существуют специальные инструменты, которые позволяют осуществлять описание данных с помощью линейной, экспоненциальной, степенной и полиномиальной функцией, поэтому часто можно обойтись и без надстройки «поиск решения» . Обо всех этих способах апппроксимации я рассказывал в своем , так что если интересно, — посмотрите. А вот когда дело касается какой-нибудь экзотической функции с одним неизвестным коэффициентом или задач оптимизации, то здесь надстройка как нельзя кстати.

Надстройку «поиск решения» можно использовать и для других задач, главное — понять суть: есть ячейка, где мы подбираем значение, а есть целевая ячейка, в которой задано условие для подбора неизвестного параметра.
Вот и все! В следующей статье расскажу сказку про отпуск, так что, чтобы не проворонить выход статьи,

Метод наименьших квадратов используется для оценки параметров уравнение регрессии.

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

1. выбор формы связи (вида аналитического уравнения регрессии);
2. оценку параметров уравнения;
3. оценку качества аналитического уравнения регрессии.

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

Задача оценивания параметров линейного парного уравнения методом наименьших квадратов состоит в следующем: получить такие оценки параметров , , при которых сумма квадратов отклонений фактических значений результативного признака — y i от расчетных значений – минимальна.
Формально критерий МНК можно записать так: .

Видео: Урок 1.Поиск решения, оптимизация, оптимальный план производства

• в поле «Установить целевую ячейку» указать адрес D6;
• установить флажок «Равной максимальному значению»;
• в поле «Изменяя ячейки» определить изменяемые ячейки (B3:B5);
• в поле «Ограничения» по одному добавить каждое из следующих четырех ограничений задачи (B6=300- B3>=50- B4>=40- B5<=40). Для этого щелкнуть по кнопке «Добавить» и в появившемся окне «Добавление ограничения» ввести ссылку на ячейку (B6), оператор ограничения (=) и значение (300), для добавления следующего ограничения щелкнуть кнопку «Добавить» и повторить процедуру добавления ограничения- после ввода последнего ограничения щелкнуть кнопку «ОК».
• в диалоговом окне «Поиск решения» щелкнуть кнопку “Выполнить”;
• в диалоге “Результаты поиска решения” установить переключатель «Сохранить найденное решение», в окне «Тип отчета» выбрать «Результаты» и нажать кнопку “Ok”;

В результате с помощью средства Поиск решения будут найдены оптимальные объемы выпуска продукции для максимизации прибыли.