Lumpics lumpics.ru

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

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

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

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

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

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

  1. Переходим во вкладку «Файл».
  2. Переход во вкладку Файл в Microsoft Excel
  3. Кликаем по наименованию раздела «Параметры».
  4. Переход в раздел параметры в Microsoft Excel
  5. В открывшемся окне останавливаем выбор на подразделе «Надстройки».
  6. Переход в подраздел надстройки в Microsoft Excel
  7. В блоке «Управление», который расположен в нижней части окна, устанавливаем переключатель в позицию «Надстройки Excel» (если в нём выставлено другое значение) и жмем на кнопку «Перейти…».
  8. Переход к надстройкам Excel в Microsoft Excel
  9. Открывается небольшое окошко. Ставим в нём галочку около параметра «Поиск решения». Жмем на кнопку «OK».
Включение поиска решения в Microsoft Excel

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

Урок: Поиск решения в Экселе

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

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

Переменные числа в Microsoft Excel

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

y=a+nx

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

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

Решение

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

  1. Слева от первого значения x ставим цифру 1. Это будет приближенная величина первого значения коэффициента n.
  2. Значение  коэффициента n в Microsoft Excel
  3. Справа от столбца y добавляем ещё одну колонку – nx. В первую ячейку данного столбца записываем формулу умножения коэффициента n на ячейку первой переменной x. При этом, ссылку на поле с коэффициентом делаем абсолютной, так как это значение меняться не будет. Кликаем по кнопке Enter.
  4. Значение nx в Microsoft Excel
  5. Используя маркер заполнения, копируем данную формулу на весь диапазон таблицы в столбце ниже.
  6. Копирование формулы в Microsoft Excel
  7. В отдельной ячейке высчитываем сумму разностей квадратов значений y и nx. Для этого кликаем по кнопке «Вставить функцию».
  8. Переход в мастер функций в Microsoft Excel
  9. В открывшемся «Мастере функций» ищем запись «СУММКВРАЗН». Выбираем её и жмем на кнопку «OK».
  10. Мастер функций в Microsoft Excel
  11. Открывается окно аргументов. В поле «Массив_x» вводим диапазон ячеек столбца y. В поле «Массив_y» вводим диапазон ячеек столбца nx. Для того, чтобы ввести значения, просто устанавливаем курсор в поле и выделяем соответствующий диапазон на листе. После ввода жмем на кнопку «OK».
  12. Ввод аргументов функции в Microsoft Excel
  13. Переходим во вкладку «Данные». На ленте в блоке инструментов «Анализ» жмем на кнопку «Поиск решения».
  14. Переход в поиск решения в Microsoft Excel
  15. Открывается окно параметров данного инструмента. В поле «Оптимизировать целевую функцию» указываем адрес ячейки с формулой «СУММКВРАЗН». В параметре «До» обязательно выставляем переключатель в позицию «Минимум». В поле «Изменяя ячейки» указываем адрес со значением коэффициента n. Жмем на кнопку «Найти решение».
  16. Поиск решения методом наименьшего квадрата в Microsoft Excel
  17. Решение будет отображаться в ячейке коэффициента n. Именно это значение будет являться наименьшим квадратом функции. Если результат удовлетворяет пользователя, то следует нажать на кнопку «OK» в дополнительном окне.
Подтверждение результата в Microsoft Excel

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

1 Ответ
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Аноним
24 октября 2023 22:49

Спасибо! Первый способ выручил!

Задать вопрос