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