Метод наименьших квадратов и поиск решения в 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 подобран. Теперь можно убедиться, что рассчитанные значения теперь очень близки к измеренным.
P.S.
Вообще, конечно, для аппроксимации экспериментальных данных в Excel существуют специальные инструменты, которые позволяют осуществлять описание данных с помощью линейной, экспоненциальной, степенной и полиномиальной функцией, поэтому часто можно обойтись и без н адстройки «поиск решения». А вот когда дело касается какой-нибудь экзотической функции с одним неизвестным коэффициентом или задач оптимизации, то здесь надстройка «поиск решения» как нельзя кстати.
Надстройку «поиск решения» можно использовать и для других задач, главное — понять суть: есть ячейка, где мы подбираем значение, а есть целевая ячейка, в которой задано условие для подбора неизвестного параметра.