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

366

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

Подготовительный этап: активируем надстройку “Поиск Решения”

Прежде, чем приступить к решению основной задачи, потребуется активировать надстройку “Поиск решения” в программе.

  1. Идем в меню “Файл”.Переход в меню Файл в Эксель
  2. В перечне слева выбираем пункт “Параметры”.Переход к параметрам Excel
  3. В правой части подраздела “Надстройки” выбираем для параметра “Управление” вариант “Надстройки Excel” и жмем “Перейти”.Переход к управлению надстройками в Эксель
  4. Появится окно для выбора нужных надстроек. Устанавливаем галочку напротив пункта “Поиск решения” и щелкаем OK.Включение надстройки Поиск решения в Excel

Этап 1: исходные данные

Давайте разберем применение метода наименьших квадратов, решив конкретный пример. Допустим, у нас есть два ряда числовых значений – X и Y.

Исходная таблица с рядами X и Y в Эксель

Данная зависимость может быть описана уравнением ниже:

Y=A+NX

Также, мы знаем, что если X=0, то и Y=0. А значит, данное уравнение можно записать так:

Y=NX

Приступим к выполнению нашей задачи, которая заключается в нахождении суммы квадратов разности.

Этап 2: решаем задачу с применением МНК

  1. Столбцу, находящемся слева от X, задаем имя N пишем число “1” (примерное значение первого коэф. N) напротив первого значения ряда X.Заполнение столбца в Эксель
  2. Столбцу с правой стороны от Y задаем название NX. Затем в самой верхней ячейке (напротив первых значений рядов X и Y) пишем формулу произведения коэф. N на соответствующее ему значение из столбца X. При этом адрес ячейки с коэффициентом нужно сделать абсолютным, чтобы он не менялся при копировании формулы. По готовности жмем Enter.Заполнение столбца в Excel
  3. Наводим указатель мыши на ячейку с полученным результатом. Как только появится черный плюсик (маркер заполнения), зажав левую кнопку мыши тянем его вниз до последней строки таблицы.Копирование формулы с помощью маркера заполнения в Эксель
  4. Получаем результаты расчетов в каждой ячейке столбца NX.Результат копирования формулы с помощью маркера заполнения в Excel
  5. Теперь нужно посчитать сумму разностей квадратов значений Y и NX. Встаем в самую верхнюю ячейку столбца справа от NX (не считая шапки таблицы) и щелкаем по значку “Вставить функцию” (fx).Вставка функции в ячейку таблицы Эксель
  6. В окне вставки функции выбираем категорию “Математические”, находим оператор “СУММКВРАЗН” и щелкаем OK.Выбор оператора СУММКВРАЗН для вставки в ячейку таблицы Excel
  7. Теперь нужно заполнить аргументы функции:
    • в поле “Массив_x”  указываем координаты диапазона ячеек столбца Y (без шапки). Адреса ячеек можно указать как вручную, напечатав их с клавиатуры, так и путем выделения с помощью зажатой левой кнопки мыши в самой таблице.
    • в поле “Массив_y” указываем диапазон ячеек столбца NX.
    • жмем Enter, когда все готово.Заполнение аргументов функции СУММКВРАЗН в Эксель
  8. Переключаемся во вкладку “Данные”. В группе “Анализ” щелкаем по функции “Поиск решения”.Активация функции Поиск решения в Эксель
  9. Нам предстоит заполнить параметры поиска решения:
    • в поле “Оптимизировать целевую функцию” следует указать ссылку на ячейку с функцией “СУММКВРАЗН”. Сделать это можно вручную или выбрав элемент в таблице.
    • для опции “До” выбираем вариант – “Минимум”.
    • в поле “Изменяя ячейки переменных” нужно указать координаты ячейки, в которой находится соответствующее значение коэф. N.
    • по готовности нажимаем “Найти решение”.Заполнение параметров Поиска решения в Excel
  10. После выполнения функции появится окно с результатами поиска решения и произойдет замена значения в столбце N. Найденная величина является наименьшим квадратом функции. Нажимаем OK, если полученный результат удовлетворителен.Результаты поиска решения в Эксель

Заключение

Итак, мы только что разобрали на практическом примере, каким образом можно применить метод наименьших квадратов в Эксель. На практике могут встречаться более сложные задачи, однако, в целом логика действий схожа с той, что мы описали.

ОСТАВЬТЕ ОТВЕТ

Введите свой комментарий
Пожалуйста, введите свое Имя