Метод наименьших квадратов представляет собой математическую процедуру построения линейного уравнения, которое бы наиболее точно соответствовало набору двух рядов чисел. Целью применения данного способа является минимизация общей квадратичной ошибки. В программе Excel имеются инструменты, с помощью которых можно применять данный метод при вычислениях. Давайте разберемся, как это делается.
Использование метода в Экселе
Метод наименьших квадратов (МНК) является математическим описанием зависимости одной переменной от второй. Его можно использовать при прогнозировании.
Включение надстройки «Поиск решения»
Для того, чтобы использовать МНК в Экселе, нужно включить надстройку , которая по умолчанию отключена.
- Переходим во вкладку .
- Кликаем по наименованию раздела .
- В открывшемся окне останавливаем выбор на подразделе .
- В блоке , который расположен в нижней части окна, устанавливаем переключатель в позицию (если в нём выставлено другое значение) и жмем на кнопку .
- Открывается небольшое окошко. Ставим в нём галочку около параметра . Жмем на кнопку .
Теперь функция в Excel активирована, а её инструменты появились на ленте.
Условия задачи
Опишем применение МНК на конкретном примере. Имеем два ряда чисел и , последовательность которых представлена на изображении ниже.
Наиболее точно данную зависимость может описать функция:
y=a+nx
При этом, известно что при x=0 y тоже равно . Поэтому данное уравнение можно описать зависимостью .
Нам предстоит найти минимальную сумму квадратов разности.
Решение
Перейдем к описанию непосредственного применения метода.
- Слева от первого значения ставим цифру 1. Это будет приближенная величина первого значения коэффициента .
- Справа от столбца добавляем ещё одну колонку – . В первую ячейку данного столбца записываем формулу умножения коэффициента на ячейку первой переменной . При этом, ссылку на поле с коэффициентом делаем абсолютной, так как это значение меняться не будет. Кликаем по кнопке .
- Используя маркер заполнения, копируем данную формулу на весь диапазон таблицы в столбце ниже.
- В отдельной ячейке высчитываем сумму разностей квадратов значений и . Для этого кликаем по кнопке .
- В открывшемся ищем запись . Выбираем её и жмем на кнопку.
- Открывается окно аргументов. В поле вводим диапазон ячеек столбца . В поле вводим диапазон ячеек столбца . Для того, чтобы ввести значения, просто устанавливаем курсор в поле и выделяем соответствующий диапазон на листе. После ввода жмем на кнопку .
- Переходим во вкладку . На ленте в блоке инструментов жмем на кнопку .
- Открывается окно параметров данного инструмента. В поле указываем адрес ячейки с формулой . В параметре обязательно выставляем переключатель в позицию . В поле указываем адрес со значением коэффициента . Жмем на кнопку .
- Решение будет отображаться в ячейке коэффициента . Именно это значение будет являться наименьшим квадратом функции. Если результат удовлетворяет пользователя, то следует нажать на кнопку в дополнительном окне.
Как видим, применение метода наименьших квадратов довольно сложная математическая процедура. Мы показали её в действии на простейшем примере, а существуют гораздо более сложные случаи. Впрочем, инструментарий Microsoft Excel призван максимально упростить производимые вычисления.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.