Применение метода наименьших квадратов в Excel
С помощью метода нахождения наименьших квадратов в таблице Эксель можно составить линейное уравнение, которое бы принадлежало двум наборам чисел. Применяется этот метод, чтобы сократить к минимуму возможность появления квадратичной ошибки. И в этой статье мы рассмотрим, какие инструменты Microsoft Excel позволяют провести данную процедуру.
Использование метода в Экселе
Способ нахождения наименьших квадратов исходит из описания первого числа от второго. Так же он используется для некоторого прогнозирования.
Включение надстройки «Поиск решения»
Для начала нам необходимо включить данную функцию в нашем Excel, которая по стандарту не активирована.
Действие 1:
Поэтому переключаемся в раздел "Файл".
Действие 2:
В левом списке находим "Параметры" и нажимаем на них.
Действие 3:
В появившемся окне кликаем по "Настройки", которые находятся внизу левого списка.
Действие 4:
В этом же окне только уже в самом низу в разделе "Управление" параметр устанавливаем на "Надстройки Excel", после чего кликаем по "Перейти...".
Действие 5:
Теперь в окне "Надстройки" ставим крести в пункте "Поиск решений". После кликаем на "ОК".
Готово. Мы включили необходимую функцию в Excel, которая теперь позволит нам воспользоваться методом наименьших квадратов.
Статья: Поиск решения в Экселе
Условия задачи
Воспользуемся нашим методом на практике. Допустим, что есть 2 ряда чисел x и y таких, как это показано на скриншоте.
Эту взаимозависимость можно описать следующей функцией.
y=a+nx
По условию мы знаем, что начальные значения x и y равны 0.
Из этого выходит обычная зависимость y=nx.
Теперь следует найти минимальную сумму квадратов разности.
Решение
Давайте посмотрим, как здесь можно применить данный нам метод.
Действие 1:
В столбце перед столбиком х поставьте 1. Назовем её n и значить она будет приближенное значение первого параметра коэффициента n.
Действие 2:
Создаем новый столбик справа от y и называем его nx. В первую строчку этого столбика записываем форму умножения n на первую ячейку в столбике x так, как это показано на скриншоте. После нажимаем на "Enter".
Читайте:
Абсолютные и относительные ссылки в Excel
Автозаполнение ячеек в Excel
Действие 3:
Переносим эту формулу на все остальные ячейки в столбике nx с помощью маркера заполнения.
Действие 4:
Теперь выбираем произвольную ячейку и записываем туда сумму разностей квадратов параметров x и nx. Сделаем это через мастера функций. Поэтому нажимаем на соответствующую кнопку, находящуюся слева от поля значений.
Действие 5:
В появившемся окне находим строку "СУММКВРАЗН" и нажимаем на неё. После кликаем по кнопке "ОК".
Действие 6:
Теперь у нас должно появится окно аргументов. В верхнем разделе "Массив_x" записываем диапазон ячеек y, а в "Массив_y" диапазон ячеек nx. Теперь вновь кликаем по "ОК".
Действие 7:
Переключаемся в раздел "Данные". Там справа находим кнопку "Поиск решений". Она должна находиться в меню "Анализ". Теперь кликаем по ней.
Действие 8:
Откроется окошко, где можно настроить параметры поиска решений. В строчке "Оптимизировать целевую функцию" заносим ссылку на ячейку с формулой "СУММКВРАЗН".
В "До" указываем значение "Минимум". И в следующей строчке "Изменяя ячейки" надо записать ячейку значения столбика n. После этого кликаем по "Найти решение".
Действие 9:
Общее решение запишется в ячейку значения n. Это и будет готовым ответом, то есть наименьшим квадратом функции. В случае, если вы согласны с результатом, то кликните по "ОК".
Теперь вы научились применять метод МНК и даже опробовали его на конкретном примере. Процедура не выглядит сложной. Если действовать по нашей инструкции, то можно с легкостью добиться желаемого результата в Microsoft Excel. А мы лишь надеемся, что наша статья была вам полезной.
Рейтинг:
(голосов:1)
Предыдущая статья: Как удалить приложение Ютуб с телефона Андроид навсегда
Следующая статья: Программы для русификации программ скачать бесплатно
Следующая статья: Программы для русификации программ скачать бесплатно
Не пропустите похожие инструкции:
Комментариев пока еще нет. Вы можете стать первым!
Популярное
Авторизация
Добавить комментарий!