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

Метод наименьших квадратов представляет собой математическую процедуру построения линейного уравнения, которое бы наиболее точно соответствовало набору двух рядов чисел. Целью применения данного способа является минимизация общей квадратичной ошибки. В программе Excel имеются инструменты, с помощью которых можно применять данный метод при вычислениях. Давайте разберемся, как это делается.

Использование метода в Экселе

Метод наименьших квадратов (МНК) является математическим описанием зависимости одной переменной от второй. Его можно использовать при прогнозировании.

Включение надстройки «Поиск решения»

Для того, чтобы использовать МНК в Экселе, нужно включить надстройку , которая по умолчанию отключена.

  1. Переходим во вкладку .
  2. Кликаем по наименованию раздела .
  3. В открывшемся окне останавливаем выбор на подразделе .
  4. В блоке , который расположен в нижней части окна, устанавливаем переключатель в позицию (если в нём выставлено другое значение) и жмем на кнопку .
  5. Открывается небольшое окошко. Ставим в нём галочку около параметра . Жмем на кнопку .

Теперь функция в Excel активирована, а её инструменты появились на ленте.

Условия задачи

Опишем применение МНК на конкретном примере. Имеем два ряда чисел и , последовательность которых представлена на изображении ниже.

Наиболее точно данную зависимость может описать функция:

y=a+nx

При этом, известно что при x=0 y тоже равно . Поэтому данное уравнение можно описать зависимостью .

Нам предстоит найти минимальную сумму квадратов разности.

Решение

Перейдем к описанию непосредственного применения метода.

  1. Слева от первого значения ставим цифру 1. Это будет приближенная величина первого значения коэффициента .
  2. Справа от столбца добавляем ещё одну колонку – . В первую ячейку данного столбца записываем формулу умножения коэффициента на ячейку первой переменной . При этом, ссылку на поле с коэффициентом делаем абсолютной, так как это значение меняться не будет. Кликаем по кнопке .
  3. Используя маркер заполнения, копируем данную формулу на весь диапазон таблицы в столбце ниже.
  4. В отдельной ячейке высчитываем сумму разностей квадратов значений и . Для этого кликаем по кнопке .
  5. В открывшемся ищем запись . Выбираем её и жмем на кнопку.
  6. Открывается окно аргументов. В поле вводим диапазон ячеек столбца . В поле вводим диапазон ячеек столбца . Для того, чтобы ввести значения, просто устанавливаем курсор в поле и выделяем соответствующий диапазон на листе. После ввода жмем на кнопку .
  7. Переходим во вкладку . На ленте в блоке инструментов жмем на кнопку .
  8. Открывается окно параметров данного инструмента. В поле указываем адрес ячейки с формулой . В параметре обязательно выставляем переключатель в позицию . В поле указываем адрес со значением коэффициента . Жмем на кнопку .
  9. Решение будет отображаться в ячейке коэффициента . Именно это значение будет являться наименьшим квадратом функции. Если результат удовлетворяет пользователя, то следует нажать на кнопку в дополнительном окне.
Читайте также  Выводим деньги с Вебмани

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

Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Источник: starhit

Домашние хитрости