Он имеет множество применений, так как позволяет осуществлять приближенное представление заданной функции другими более простыми. МНК может оказаться чрезвычайно полезным при обработке наблюдений, и его активно используют для оценки одних величин по результатам измерений других, содержащих случайные ошибки. Из этой статьи вы узнаете, как реализовать вычисления по методу наименьших квадратов в Excel.
Постановка задачи на конкретном примере
Предположим, имеются два показателя X и Y. Причем Y зависит от X. Так как МНК интересует нас с точки зрения регрессионного анализа (в Excel его методы реализуются с помощью встроенных функций), то стоит сразу же перейти к рассмотрению конкретной задачи.
Итак, пусть X — торговая площадь продовольственного магазина, измеряемая в квадратных метрах, а Y — годовой товарооборот, определяемый в миллионах рублей.
Требуется сделать прогноз, какой товарооборот (Y) будет у магазина, если у него та или иная торговая площадь. Очевидно, что функция Y = f (X) возрастающая, так как гипермаркет продает больше товаров, чем ларек.
Несколько слов о корректности исходных данных, используемых для предсказания
Допустим, у нас есть таблица, построенная по данным для n магазинов.
Согласно математической статистике, результаты будут более-менее корректными, если исследуются данные по хотя бы 5-6 объектам. Кроме того, нельзя использовать «аномальные» результаты. В частности, элитный небольшой бутик может иметь товарооборот в разы больший, чем товарооборот больших торговых точек класса «масмаркет».
Суть метода
Данные таблицы можно изобразить на декартовой плоскости в виде точек M 1 (x 1 , y 1), … M n (x n , y n). Теперь решение задачи сведется к подбору аппроксимирующей функции y = f (x), имеющей график, проходящий как можно ближе к точкам M 1, M 2, .. M n .
Конечно, можно использовать многочлен высокой степени, но такой вариант не только труднореализуем, но и просто некорректен, так как не будет отражать основную тенденцию, которую и нужно обнаружить. Самым разумным решением является поиск прямой у = ax + b, которая лучше всего приближает экспериментальные данные, a точнее, коэффициентов - a и b.
Оценка точности
При любой аппроксимации особую важность приобретает оценка ее точности. Обозначим через e i разность (отклонение) между функциональными и экспериментальными значениями для точки x i , т. е. e i = y i - f (x i).
Очевидно, что для оценки точности аппроксимации можно использовать сумму отклонений, т. е. при выборе прямой для приближенного представления зависимости X от Y нужно отдавать предпочтение той, у которой наименьшее значение суммы e i во всех рассматриваемых точках. Однако, не все так просто, так как наряду с положительными отклонениями практически будут присутствовать и отрицательные.
Решить вопрос можно, используя модули отклонений или их квадраты. Последний метод получил наиболее широкое распространение. Он используется во многих областях, включая регрессионный анализ (в Excel его реализация осуществляется с помощью двух встроенных функций), и давно доказал свою эффективность.
Метод наименьших квадратов
В Excel, как известно, существует встроенная функция автосуммы, позволяющая вычислить значения всех значений, расположенных в выделенном диапазоне. Таким образом, ничто не помешает нам рассчитать значение выражения (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
В математической записи это имеет вид:
Так как изначально было принято решение об аппроксимировании с помощью прямой, то имеем:
Таким образом, задача нахождения прямой, которая лучше всего описывает конкретную зависимость величин X и Y, сводится к вычислению минимума функции двух переменных:
Для этого требуется приравнять к нулю частные производные по новым переменным a и b, и решить примитивную систему, состоящую из двух уравнений с 2-мя неизвестными вида:
После нехитрых преобразований, включая деление на 2 и манипуляции с суммами, получим:
Решая ее, например, методом Крамера, получаем стационарную точку с некими коэффициентами a * и b * . Это и есть минимум, т. е. для предсказания, какой товарооборот будет у магазина при определенной площади, подойдет прямая y = a * x + b * , представляющая собой регрессионную модель для примера, о котором идет речь. Конечно, она не позволит найти точный результат, но поможет получить представление о том, окупится ли покупка в кредит магазина конкретной площади.
Как реализоавать метод наименьших квадратов в Excel
В "Эксель" имеется функция для расчета значения по МНК. Она имеет следующий вид: «ТЕНДЕНЦИЯ» (известн. значения Y; известн. значения X; новые значения X; конст.). Применим формулу расчета МНК в Excel к нашей таблице.
Для этого в ячейку, в которой должен быть отображен результат расчета по методу наименьших квадратов в Excel, введем знак «=» и выберем функцию «ТЕНДЕНЦИЯ». В раскрывшемся окне заполним соответствующие поля, выделяя:
- диапазон известных значений для Y (в данном случае данные для товарооборота);
- диапазон x 1 , …x n , т. е. величины торговых площадей;
- и известные, и неизвестные значения x, для которого нужно выяснить размер товарооборота (информацию об их расположении на рабочем листе см. далее).
Кроме того, в формуле присутствует логическая переменная «Конст». Если ввести в соответствующее ей поле 1, то это будет означать, что следует осуществить вычисления, считая, что b = 0.
Если нужно узнать прогноз для более чем одного значения x, то после ввода формулы следует нажать не на «Ввод», а нужно набрать на клавиатуре комбинацию «Shift» + «Control»+ «Enter» («Ввод»).
Некоторые особенности
Регрессионный анализ может быть доступен даже чайникам. Формула Excel для предсказания значения массива неизвестных переменных — «ТЕНДЕНЦИЯ» — может использоваться даже теми, кто никогда не слышал о методе наименьших квадратов. Достаточно просто знать некоторые особенности ее работы. В частности:
- Если расположить диапазон известных значений переменной y в одной строке или столбце, то каждая строка (столбец) с известными значениями x будет восприниматься программой в качестве отдельной переменной.
- Если в окне «ТЕНДЕНЦИЯ» не указан диапазон с известными x, то в случае использования функции в Excel программа будет рассматривать его как массив, состоящий из целых чисел, количество которых соответствует диапазону с заданными значениями переменной y.
- Чтобы получить на выходе массив «предсказанных» значений, выражение для вычисления тенденции нужно вводить как формулу массива.
- Если не указаны новые значения x, то функция «ТЕНДЕНЦИЯ» считает их равным известным. Если и они не заданы, то в качестве аргумента берется массив 1; 2; 3; 4;…, который соразмерен диапазону с уже заданными параметрами y.
- Диапазон, содержащий новые значения x должен состоять из такого же или большего количества строк или столбцов, как диапазон с заданными значениями y. Иными словами он должен быть соразмерным независимым переменным.
- В массиве с известными значениями x может содержаться несколько переменных. Однако если речь идет лишь об одной, то требуется, чтобы диапазоны с заданными значениями x и y были соразмерны. В случае нескольких переменных нужно, чтобы диапазон с заданными значениями y вмещался в одном столбце или в одной строке.
Функция «ПРЕДСКАЗ»
Реализуется с помощью нескольких функций. Одна из них называется «ПРЕДСКАЗ». Она аналогична «ТЕНДЕНЦИИ», т. е. выдает результат вычислений по методу наименьших квадратов. Однако только для одного X, для которого неизвестно значение Y.
Теперь вы знаете формулы в Excel для чайников, позволяющие спрогнозировать величину будущего значения того или иного показателя согласно линейному тренду.
Метод наименьших квадратов
На заключительном уроке темы мы познакомимся с наиболее известным приложением ФНП , которое находит самое широкое применение в различных областях науки и практической деятельности. Это может быть физика, химия, биология, экономика, социология, психология и так далее, так далее. Волею судьбы мне часто приходится иметь дело с экономикой, и поэтому сегодня я оформлю вам путёвку в удивительную страну под названием Эконометрика =) …Как это не хотите?! Там очень хорошо – нужно только решиться! …Но вот то, что вы, наверное, определённо хотите – так это научиться решать задачи методом наименьших квадратов . И особо прилежные читатели научатся решать их не только безошибочно, но ещё и ОЧЕНЬ БЫСТРО;-) Но сначала общая постановка задачи + сопутствующий пример:
Пусть в некоторой предметной области исследуются показатели , которые имеют количественное выражение. При этом есть все основания полагать, что показатель зависит от показателя . Это полагание может быть как научной гипотезой, так и основываться на элементарном здравом смысле. Оставим, однако, науку в сторонке и исследуем более аппетитные области – а именно, продовольственные магазины. Обозначим через:
– торговую площадь продовольственного магазина, кв.м.,
– годовой товарооборот продовольственного магазина, млн. руб.
Совершенно понятно, что чем больше площадь магазина, тем в большинстве случаев будет больше его товарооборот.
Предположим, что после проведения наблюдений/опытов/подсчётов/танцев с бубном в нашем распоряжении оказываются числовые данные:
С гастрономами, думаю, всё понятно: – это площадь 1-го магазина, – его годовой товарооборот, – площадь 2-го магазина, – его годовой товарооборот и т.д. Кстати, совсем не обязательно иметь доступ к секретным материалам – довольно точную оценку товарооборота можно получить средствами математической статистики
. Впрочем, не отвлекаемся, курс коммерческого шпионажа – он уже платный =)
Табличные данные также можно записать в виде точек и изобразить в привычной для нас декартовой системе .
Ответим на важный вопрос: сколько точек нужно для качественного исследования?
Чем больше, тем лучше. Минимально допустимый набор состоит из 5-6 точек. Кроме того, при небольшом количестве данных в выборку нельзя включать «аномальные» результаты. Так, например, небольшой элитный магазин может выручать на порядки больше «своих коллег», искажая тем самым общую закономерность, которую и требуется найти!
Если совсем просто – нам нужно подобрать функцию , график
которой проходит как можно ближе к точкам 
. Такую функцию называют аппроксимирующей
(аппроксимация – приближение)
или теоретической функцией
. Вообще говоря, тут сразу появляется очевидный «претендент» – многочлен высокой степени, график которого проходит через ВСЕ точки. Но этот вариант сложен, а зачастую и просто некорректен (т.к. график будет всё время «петлять» и плохо отражать главную тенденцию)
.
Таким образом, разыскиваемая функция должна быть достаточно простА и в то же время отражать зависимость адекватно. Как вы догадываетесь, один из методов нахождения таких функций и называется методом наименьших квадратов
. Сначала разберём его суть в общем виде. Пусть некоторая функция приближает экспериментальные данные :
Как оценить точность данного приближения? Вычислим и разности (отклонения) между экспериментальными и функциональными значениями (изучаем чертёж)
. Первая мысль, которая приходит в голову – это оценить, насколько великА сумма , но проблема состоит в том, что разности могут быть и отрицательны (например,
)
и отклонения в результате такого суммирования будут взаимоуничтожаться. Поэтому в качестве оценки точности приближения напрашивается принять сумму модулей
отклонений:
или в свёрнутом виде: (вдруг кто не знает:
– это значок суммы, а
– вспомогательная переменная-«счётчик», которая принимает значения от 1 до
)
.
Приближая экспериментальные точки различными функциями, мы будет получать разные значения , и очевидно, где эта сумма меньше – та функция и точнее.
Такой метод существует и называется он методом наименьших модулей . Однако на практике получил гораздо бОльшее распространение метод наименьших квадратов , в котором возможные отрицательные значения ликвидируются не модулем, а возведением отклонений в квадрат:
, после чего усилия направлены на подбор такой функции , чтобы сумма квадратов отклонений 
была как можно меньше. Собственно, отсюда и название метода.
И сейчас мы возвращаемся к другому важному моменту: как отмечалось выше, подбираемая функция должна быть достаточно простА – но ведь и таких функций тоже немало: линейная , гиперболическая , экспоненциальная , логарифмическая , квадратичная и т.д. И, конечно же, тут сразу бы хотелось «сократить поле деятельности». Какой класс функций выбрать для исследования? Примитивный, но эффективный приём:
– Проще всего изобразить точки на чертеже и проанализировать их расположение. Если они имеют тенденцию располагаться по прямой, то следует искать уравнение прямой
с оптимальными значениями и . Иными словами, задача состоит в нахождении ТАКИХ коэффициентов – чтобы сумма квадратов отклонений была наименьшей.
Если же точки расположены, например, по гиперболе
, то заведомо понятно, что линейная функция будет давать плохое приближение. В этом случае ищем наиболее «выгодные» коэффициенты для уравнения гиперболы 
– те, которые дают минимальную сумму квадратов 
.
А теперь обратите внимание, что в обоих случаях речь идёт о функции двух переменных
, аргументами которой являются параметры разыскиваемых зависимостей
:
И по существу нам требуется решить стандартную задачу – найти минимум функции двух переменных .
Вспомним про наш пример: предположим, что «магазинные» точки имеют тенденцию располагаться по прямой линии и есть все основания полагать наличие линейной зависимости
товарооборота от торговой площади. Найдём ТАКИЕ коэффициенты «а» и «бэ», чтобы сумма квадратов отклонений 
была наименьшей. Всё как обычно – сначала частные производные 1-го порядка
. Согласно правилу линейности
дифференцировать можно прямо под значком суммы:
Если хотите использовать данную информацию для реферата или курсовика – буду очень благодарен за поставленную ссылку в списке источников, такие подробные выкладки найдёте мало где:
Составим стандартную систему:
Сокращаем каждое уравнение на «двойку» и, кроме того, «разваливаем» суммы:
Примечание
: самостоятельно проанализируйте, почему «а» и «бэ» можно вынести за значок суммы. Кстати, формально это можно проделать и с суммой
Перепишем систему в «прикладном» виде:
после чего начинает прорисовываться алгоритм решения нашей задачи:
Координаты точек мы знаем? Знаем. Суммы 
найти можем? Легко. Составляем простейшуюсистему двух линейных уравнений с двумя неизвестными
(«а» и «бэ»). Систему решаем, например, методом Крамера
, в результате чего получаем стационарную точку . Проверяя достаточное условие экстремума
, можно убедиться, что в данной точке функция 
достигает именно минимума
. Проверка сопряжена с дополнительными выкладками и поэтому оставим её за кадром (при необходимости недостающий кадр можно посмотреть
здесь
)
. Делаем окончательный вывод:
Функция 
наилучшим образом (по крайне мере, по сравнению с любой другой линейной функцией)
приближает экспериментальные точки . Грубо говоря, её график проходит максимально близко к этим точкам. В традициях эконометрики
полученную аппроксимирующую функцию также называют уравнением пАрной линейной регрессии
.
Рассматриваемая задача имеет большое практическое значение. В ситуации с нашим примером, уравнение позволяет прогнозировать, какой товарооборот («игрек»)
будет у магазина при том или ином значении торговой площади (том или ином значении «икс»)
. Да, полученный прогноз будет лишь прогнозом, но во многих случаях он окажется достаточно точным.
Я разберу всего лишь одну задачу с «реальными» числами, поскольку никаких трудностей в ней нет – все вычисления на уровне школьной программы 7-8 класса. В 95 процентов случаев вам будет предложено отыскать как раз линейную функцию, но в самом конце статьи я покажу, что ничуть не сложнее отыскать уравнения оптимальной гиперболы, экспоненты и некоторых других функций.
По сути, осталось раздать обещанные плюшки – чтобы вы научились решать такие примеры не только безошибочно, но ещё и быстро. Внимательно изучаем стандарт:
Задача
В результате исследования взаимосвязи двух показателей, получены следующие пары чисел:
Методом наименьших квадратов найти линейную функцию, которая наилучшим образом приближает эмпирические (опытные)
данные. Сделать чертеж, на котором в декартовой прямоугольной системе координат построить экспериментальные точки и график аппроксимирующей функции 
. Найти сумму квадратов отклонений между эмпирическими и теоретическими значениями. Выяснить, будет ли функция лучше (с точки зрения метода наименьших квадратов)
приближать экспериментальные точки.
Заметьте, что «иксовые» значения – натуральные, и это имеет характерный содержательный смысл, о котором я расскажу чуть позже; но они, разумеется, могут быть и дробными. Кроме того, в зависимости от содержания той или иной задачи как «иксовые», так и «игрековые» значения полностью или частично могут быть отрицательными. Ну а у нас дана «безликая» задача, и мы начинаем её решение :
Коэффициенты оптимальной функции найдём как решение системы:
В целях более компактной записи переменную-«счётчик» можно опустить, поскольку и так понятно, что суммирование осуществляется от 1 до .
Расчёт нужных сумм удобнее оформить в табличном виде:
Вычисления можно провести на микрокалькуляторе, но гораздо лучше использовать Эксель – и быстрее, и без ошибок; смотрим короткий видеоролик:
Таким образом, получаем следующую систему
:
Тут можно умножить второе уравнение на 3 и из 1-го уравнения почленно вычесть 2-е
. Но это везение – на практике системы чаще не подарочны, и в таких случаях спасает метод Крамера
:
, значит, система имеет единственное решение.
Выполним проверку. Понимаю, что не хочется, но зачем же пропускать ошибки там, где их можно стопроцентно не пропустить? Подставим найденное решение в левую часть каждого уравнения системы:
Получены правые части соответствующих уравнений, значит, система решена правильно.
Таким образом, искомая аппроксимирующая функция: – из всех линейных функций экспериментальные данные наилучшим образом приближает именно она.
В отличие от прямой
зависимости товарооборота магазина от его площади, найденная зависимость является обратной
(принцип «чем больше – тем меньше»)
, и этот факт сразу выявляется по отрицательному угловому коэффициенту
. Функция 
сообщает нам о том, что с увеличение некоего показателя на 1 единицу значение зависимого показателя уменьшается в среднем
на 0,65 единиц. Как говорится, чем выше цена на гречку, тем меньше её продано.
Для построения графика аппроксимирующей функции найдём два её значения:
и выполним чертёж:
Построенная прямая называется линией тренда
(а именно – линией линейного тренда, т.е. в общем случае тренд – это не обязательно прямая линия)
. Всем знакомо выражение «быть в тренде», и, думаю, что этот термин не нуждается в дополнительных комментариях.
Вычислим сумму квадратов отклонений 
между эмпирическими и теоретическими значениями. Геометрически – это сумма квадратов длин «малиновых» отрезков (два из которых настолько малы, что их даже не видно)
.
Вычисления сведём в таблицу:
Их можно опять же провести вручную, на всякий случай приведу пример для 1-й точки:
но намного эффективнее поступить уже известным образом:
Еще раз повторим: в чём смысл полученного результата?
Из всех линейных функций
у функции показатель является наименьшим, то есть в своём семействе это наилучшее приближение. И здесь, кстати, не случаен заключительный вопрос задачи: а вдруг предложенная экспоненциальная функция 
будет лучше приближать экспериментальные точки?
Найдем соответствующую сумму квадратов отклонений – чтобы различать, я обозначу их буквой «эпсилон». Техника точно такая же:
И снова на всякий пожарный вычисления для 1-й точки:
В Экселе пользуемся стандартной функцией EXP
(синтаксис можно посмотреть в экселевской Справке)
.
Вывод
: , значит, экспоненциальная функция приближает экспериментальные точки хуже, чем прямая .
Но тут следует отметить, что «хуже» – это ещё не значит
, что плохо. Сейчас построил график этой экспоненциальной функции – и он тоже проходит близко к точкам 
– да так, что без аналитического исследования и сказать трудно, какая функция точнее.
На этом решение закончено, и я возвращаюсь к вопросу о натуральных значениях аргумента. В различных исследованиях, как правило, экономических или социологических, натуральными «иксами» нумеруют месяцы, годы или иные равные временнЫе промежутки. Рассмотрим, например, такую задачу:
Имеются следующие данные о розничном товарообороте магазина за первое полугодие:
Используя аналитическое выравнивание по прямой, определите объем товарооборота за июль
.
Да без проблем: нумеруем месяцы 1, 2, 3, 4, 5, 6 и используем обычный алгоритм, в результате чего получаем уравнение – единственное, когда речь идёт о времени, то обычно используют букву «тэ» (хотя это не критично) . Полученное уравнение показывает, что в первом полугодии товарооборот увеличивался в среднем на 27,74 д.е. за месяц. Получим прогноз на июль (месяц №7) : д.е.
И подобных задач – тьма тьмущая. Желающие могут воспользоваться дополнительным сервисом, а именно моим экселевским калькулятором (демо-версия) , который решает разобранную задачу практически мгновенно! Рабочая версия программы доступна по обмену или за символическую плaтy .
В заключение урока краткая информация о нахождение зависимостей некоторых других видов. Собственно, и рассказывать-то особо нечего, поскольку принципиальный подход и алгоритм решения остаются прежними.
Предположим, что расположение экспериментальных точек напоминает гиперболу. Тогда чтобы отыскать коэффициенты наилучшей гиперболы , нужно найти минимум функции – желающие могут провести подробные вычисления и прийти к похожей системе:
С формально-технической точки зрения она получается из «линейной» системы 
(обозначим её «звёздочкой»)
заменой «икса» на . Ну а уж суммы-то 
рассчитаете, после чего до оптимальных коэффициентов «а» и «бэ» рукой подать
.
Если есть все основания полагать, что точки располагаются по логарифмической кривой , то для розыска оптимальных значений и находим минимум функции 
. Формально в системе (*) нужно заменить на :
При вычислениях в Экселе используйте функцию LN . ПризнАюсь, мне не составит особого труда создать калькуляторы для каждого из рассматриваемых случаев, но всё-таки будет лучше, если вы сами «запрограммируете» вычисления. Видеоматериалы урока в помощь.
С экспоненциальной зависимостью ситуация чуть сложнее. Чтобы свести дело к линейному случаю, прологарифмируем функцию и воспользуемся свойствам логарифма
:
Теперь, сопоставляя полученную функцию с линейной функцией , приходим к выводу, что в системе (*) нужно заменить на , а – на . Для удобства обозначим :
Обратите внимание, что система разрешается относительно и , и поэтому после нахождения корней нужно не забыть найти сам коэффициент .
Чтобы приблизить экспериментальные точки оптимальной параболой 
, следует найти минимум функции трёх переменных
. После осуществления стандартных действий получаем следующую «рабочую» систему
:
Да, конечно, сумм здесь побольше, но при использовании любимого приложения трудностей вообще никаких. И напоследок расскажу, как с помощью Экселя быстро выполнить проверку и построить нужную линию тренда: создаём точечную диаграмму, выделяем мышью любую из точек и через правый щелчок выбираем опцию «Добавить линию тренда»
. Далее выбираем тип диаграммы и на вкладке «Параметры»
активируем опцию «Показывать уравнение на диаграмме»
. ОК
Как всегда статью хочется завершить какой-нибудь красивой фразой, и я уже чуть было не напечатал «Будьте в тренде!». Но вовремя передумал. И не из-за того, что она шаблонна. Не знаю, кому как, а мне что-то совсем не хочется следовать пропагандируемому американскому и в особенности европейскому тренду =) Поэтому я пожелаю каждому из вас придерживаться своей собственной линии!
http://www.grandars.ru/student/vysshaya-matematika/metod-naimenshih-kvadratov.html
Метод наименьших квадратов является одним из наиболее распространенных и наиболее разработанных вследствие своей простоты и эффективности методов оценки параметров линейныхэконометрических моделей . Вместе с тем, при его применении следует соблюдать определенную осторожность, поскольку построенные с его использованием модели могут не удовлетворять целому ряду требований к качеству их параметров и, вследствие этого, недостаточно “хорошо” отображать закономерности развития процесса .
Рассмотрим процедуру оценки параметров линейной эконометрической модели с помощью метода наименьших квадратов более подробно. Такая модель в общем виде может быть представлена уравнением (1.2):
y t = a 0 + a 1 х 1t +...+ a n х nt + ε t .
Исходными данными при оценке параметров a 0 , a 1 ,..., a n является вектор значений зависимой переменной y = (y 1 , y 2 , ... , y T)" и матрица значений независимых переменных
в которой первый столбец, состоящий из единиц, соответствует коэффициенту модели .
Название свое метод наименьших квадратов получил, исходя из основного принципа, которому должны удовлетворять полученные на его основе оценки параметров: сумма квадратов ошибки модели должна быть минимальной.
Примеры решения задач методом наименьших квадратов
Пример 2.1. Торговое предприятие имеет сеть, состоящую из 12 магазинов, информация о деятельности которых представлена в табл. 2.1.
Руководство предприятия хотело бы знать, как зависит размер годового товарооборота от торговой площади магазина.
Таблица 2.1
| Номер магазина | Годовой товарооборот, млн руб. | Торговая площадь, тыс. м 2 |
| 19,76 | 0,24 | |
| 38,09 | 0,31 | |
| 40,95 | 0,55 | |
| 41,08 | 0,48 | |
| 56,29 | 0,78 | |
| 68,51 | 0,98 | |
| 75,01 | 0,94 | |
| 89,05 | 1,21 | |
| 91,13 | 1,29 | |
| 91,26 | 1,12 | |
| 99,84 | 1,29 | |
| 108,55 | 1,49 |
Решение методом наименьших квадратов. Обозначим - годовой товарооборот -го магазина, млн руб.; - торговая площадь -го магазина, тыс. м 2 .
Рис.2.1. Диаграмма рассеяния для примера 2.1
Для определения формы функциональной зависимости между переменными и построим диаграмму рассеяния (рис. 2.1).
На основании диаграммы рассеяния можно сделать вывод о позитивной зависимости годового товарооборота от торговой площади (т.е. у будет расти с ростом ). Наиболее подходящая форма функциональной связи - линейная .
Информация для проведения дальнейших расчетов представлена в табл. 2.2. С помощью метода наименьших квадратов оценим параметры линейной однофакторной эконометрической модели
Таблица 2.2
| t | y t | x 1t | y t 2 | x 1t 2 | x 1t y t |
| 19,76 | 0,24 | 390,4576 | 0,0576 | 4,7424 | |
| 38,09 | 0,31 | 1450,8481 | 0,0961 | 11,8079 | |
| 40,95 | 0,55 | 1676,9025 | 0,3025 | 22,5225 | |
| 41,08 | 0,48 | 1687,5664 | 0,2304 | 19,7184 | |
| 56,29 | 0,78 | 3168,5641 | 0,6084 | 43,9062 | |
| 68,51 | 0,98 | 4693,6201 | 0,9604 | 67,1398 | |
| 75,01 | 0,94 | 5626,5001 | 0,8836 | 70,5094 | |
| 89,05 | 1,21 | 7929,9025 | 1,4641 | 107,7505 | |
| 91,13 | 1,29 | 8304,6769 | 1,6641 | 117,5577 | |
| 91,26 | 1,12 | 8328,3876 | 1,2544 | 102,2112 | |
| 99,84 | 1,29 | 9968,0256 | 1,6641 | 128,7936 | |
| 108,55 | 1,49 | 11783,1025 | 2,2201 | 161,7395 | |
| S | 819,52 | 10,68 | 65008,554 | 11,4058 | 858,3991 |
| Среднее | 68,29 | 0,89 |
Таким образом,
Cледовательно, при увеличении торговой площади на 1 тыс. м 2 при прочих равных условиях среднегодовой товарооборот увеличивается на 67,8871 млн руб.
Пример 2.2. Руководство предприятия заметило, что годовой товарооборот зависит не только от торговой площади магазина (см. пример 2.1), но и от среднего числа посетителей. Соответствующая информация представлена в табл. 2.3.
Таблица 2.3
Решение. Обозначим - среднее число посетителей -го магазина в день, тыс. чел.
Для определения формы функциональной зависимости между переменными и построим диаграмму рассеяния (рис. 2.2).
На основании диаграммы рассеяния можно сделать вывод о позитивной зависимости годового товарооборота от среднего числа посетителей в день (т.е. у будет расти с ростом ). Форма функциональной зависимости - линейная.
Рис. 2.2. Диаграмма рассеяния для примера 2.2
Таблица 2.4
| t | x 2t | x 2t 2 | y t x 2t | x 1t x 2t |
| 8,25 | 68,0625 | 163,02 | 1,98 | |
| 10,24 | 104,8575 | 390,0416 | 3,1744 | |
| 9,31 | 86,6761 | 381,2445 | 5,1205 | |
| 11,01 | 121,2201 | 452,2908 | 5,2848 | |
| 8,54 | 72,9316 | 480,7166 | 6,6612 | |
| 7,51 | 56,4001 | 514,5101 | 7,3598 | |
| 12,36 | 152,7696 | 927,1236 | 11,6184 | |
| 10,81 | 116,8561 | 962,6305 | 13,0801 | |
| 9,89 | 97,8121 | 901,2757 | 12,7581 | |
| 13,72 | 188,2384 | 1252,0872 | 15,3664 | |
| 12,27 | 150,5529 | 1225,0368 | 15,8283 | |
| 13,92 | 193,7664 | 1511,016 | 20,7408 | |
| S | 127,83 | 1410,44 | 9160,9934 | 118,9728 |
| Cреднее | 10,65 |
В целом необходимо определить параметры двухфакторной эконометрической модели
у t = a 0 + a 1 х 1t + a 2 х 2t + ε t
Информация, требующаяся для дальнейших расчетов, представлена в табл. 2.4.
Оценим параметры линейной двухфакторной эконометрической модели с помощью метода наименьших квадратов.
Таким образом,
Оценка коэффициента =61,6583 показывает, что при прочих равных условиях с увеличением торговой площади на 1 тыс. м 2 годовой товарооборот увеличится в среднем на 61,6583 млн руб.
Оценка коэффициента = 2,2748 показывает, что при прочих равных условиях с увеличением среднего числа посетителей на 1 тыс. чел. в день годовой товарооборот увеличится в среднем на 2,2748 млн руб.
Пример 2.3. Используя информацию, представленную в табл. 2.2 и 2.4, оценить параметр однофакторной эконометрической модели
где - центрированное значение годового товарооборота -го магазина, млн руб.; - центрированное значение среднедневного числа посетителей t-го магазина, тыс. чел. (см. примеры 2.1-2.2).
Решение. Дополнительная информация, необходимая для расчетов, представлена в табл. 2.5.
Таблица 2.5
| -48,53 | -2,40 | 5,7720 | 116,6013 | |
| -30,20 | -0,41 | 0,1702 | 12,4589 | |
| -27,34 | -1,34 | 1,8023 | 36,7084 | |
| -27,21 | 0,36 | 0,1278 | -9,7288 | |
| -12,00 | -2,11 | 4,4627 | 25,3570 | |
| 0,22 | -3,14 | 9,8753 | -0,6809 | |
| 6,72 | 1,71 | 2,9156 | 11,4687 | |
| 20,76 | 0,16 | 0,0348 | 3,2992 | |
| 22,84 | -0,76 | 0,5814 | -17,413 | |
| 22,97 | 3,07 | 9,4096 | 70,4503 | |
| 31,55 | 1,62 | 2,6163 | 51,0267 | |
| 40,26 | 3,27 | 10,6766 | 131,5387 | |
| Cумма | 48,4344 | 431,0566 |
Используя формулу (2.35), получим
Таким образом,
http://www.cleverstudents.ru/articles/mnk.html
Пример.
Экспериментальные данные о значениях переменных х
и у
приведены в таблице.
В результате их выравнивания получена функция 
Используя метод наименьших квадратов , аппроксимировать эти данные линейной зависимостью y=ax+b (найти параметры а и b ). Выяснить, какая из двух линий лучше (в смысле метода наименьших квадратов) выравнивает экспериментальные данные. Сделать чертеж.
Решение.
В нашем примере n=5
. Заполняем таблицу для удобства вычисления сумм, которые входят в формулы искомых коэффициентов.
Значения в четвертой строке таблицы получены умножением значений 2-ой строки на значения 3-ей строки для каждого номера i .
Значения в пятой строке таблицы получены возведением в квадрат значений 2-ой строки для каждого номера i .
Значения последнего столбца таблицы – это суммы значений по строкам.
Используем формулы метода наименьших квадратов для нахождения коэффициентов а
и b
. Подставляем в них соответствующие значения из последнего столбца таблицы:
Следовательно, y = 0.165x+2.184 - искомая аппроксимирующая прямая.
Осталось выяснить какая из линий y = 0.165x+2.184
или лучше аппроксимирует исходные данные, то есть произвести оценку методом наименьших квадратов.
Доказательство.
Чтобы при найденных а
и b
функция принимала наименьшее значение, необходимо чтобы в этой точке матрица квадратичной формы дифференциала второго порядка для функции 
была положительно определенной. Покажем это.
Дифференциал второго порядка имеет вид:
То есть
Следовательно, матрица квадратичной формы имеет вид
причем значения элементов не зависят от а
и b
.
Покажем, что матрица положительно определенная. Для этого нужно, чтобы угловые миноры были положительными.
Угловой минор первого порядка 
. Неравенство строгое, так как точки
Если некоторая физическая величина зависит от другой величины, то эту зависимость можно исследовать, измеряя y при различных значениях x . В результате измерений получается ряд значений:
x 1 , x 2 , ..., x i , ... , x n ;
y 1 , y 2 , ..., y i , ... , y n .
По данным такого эксперимента можно построить график зависимости y = ƒ(x). Полученная кривая дает возможность судить о виде функции ƒ(x). Однако постоянные коэффициенты, которые входят в эту функцию, остаются неизвестными. Определить их позволяет метод наименьших квадратов. Экспериментальные точки, как правило, не ложатся точно на кривую. Метод наименьших квадратов требует, чтобы сумма квадратов отклонений экспериментальных точек от кривой, т.е. 2 была наименьшей.
На практике этот метод наиболее часто (и наиболее просто) используется в случае линейной зависимости, т.е. когда
y = kx или y = a + bx.
Линейная зависимость очень широко распространена в физике. И даже когда зависимость нелинейная, обычно стараются строить график так, чтобы получить прямую линию. Например, если предполагают, что показатель преломления стекла n связан с длиной λ световой волны соотношением n = a + b/λ 2 , то на графике строят зависимость n от λ -2 .
Рассмотрим зависимость y = kx (прямая, проходящая через начало координат). Составим величину φ сумму квадратов отклонений наших точек от прямой
Величина φ всегда положительна и оказывается тем меньше, чем ближе к прямой лежат наши точки. Метод наименьших квадратов утверждает, что для k следует выбирать такое значение, при котором φ имеет минимум
или
(19)
Вычисление показывает, что среднеквадратичная ошибка определения величины k равна при этом
, (20)
где n число измерений.
Рассмотрим теперь несколько более трудный случай, когда точки должны удовлетворить формуле y = a + bx (прямая, не проходящая через начало координат).
Задача состоит в том, чтобы по имеющемуся набору значений x i , y i найти наилучшие значения a и b.
Снова составим квадратичную форму φ , равную сумме квадратов отклонений точек x i , y i от прямой
и найдем значения a и b , при которых φ имеет минимум
;
.
Совместное решение этих уравнений дает
(21)
Среднеквадратичные ошибки определения a и b равны
(23)
. (24)
При обработке результатов измерения этим методом удобно все данные сводить в таблицу, в которой предварительно подсчитываются все суммы, входящие в формулы (19)(24). Формы этих таблиц приведены в рассматриваемых ниже примерах.
Пример 1. Исследовалось основное уравнение динамики вращательного движения ε = M/J (прямая, проходящая через начало координат). При различных значениях момента M измерялось угловое ускорение ε некоторого тела. Требуется определить момент инерции этого тела. Результаты измерений момента силы и углового ускорения занесены во второй и третий столбцы таблицы 5 .
Таблица 5
| n | M, Н · м | ε, c -1 | M 2 | M · ε | ε - kM | (ε - kM) 2 |
| 1 | 1.44 | 0.52 | 2.0736 | 0.7488 | 0.039432 | 0.001555 |
| 2 | 3.12 | 1.06 | 9.7344 | 3.3072 | 0.018768 | 0.000352 |
| 3 | 4.59 | 1.45 | 21.0681 | 6.6555 | -0.08181 | 0.006693 |
| 4 | 5.90 | 1.92 | 34.81 | 11.328 | -0.049 | 0.002401 |
| 5 | 7.45 | 2.56 | 55.5025 | 19.072 | 0.073725 | 0.005435 |
| ∑ | | | 123.1886 | 41.1115 | | 0.016436 |
По формуле (19) определяем:
.
Для определения среднеквадратичной ошибки воспользуемся формулой (20)
0.005775 кг -1 · м -2 .
По формуле (18) имеем
S J = (2.996 · 0.005775)/0.3337 = 0.05185 кг · м 2 .
Задавшись надежностью P = 0.95 , по таблице коэффициентов Стьюдента для n = 5, находим t = 2.78 и определяем абсолютную ошибку ΔJ = 2.78 · 0.05185 = 0.1441 ≈ 0.2 кг · м 2 .
Результаты запишем в виде:
J = (3.0 ± 0.2) кг · м 2 ;
Пример 2. Вычислим температурный коэффициент сопротивления металла по методу наименьших квадратов. Сопротивление зависит от температуры по линейному закону
R t = R 0 (1 + α t°) = R 0 + R 0 α t°.
Свободный член определяет сопротивление R 0 при температуре 0° C , а угловой коэффициент произведение температурного коэффициента α на сопротивление R 0 .
Результаты измерений и расчетов приведены в таблице (см. таблицу 6 ).
Таблица 6
| n | t°, c | r, Ом | t-¯ t | (t-¯ t) 2 | (t-¯ t)r | r - bt - a | (r - bt - a) 2 ,10 -6 |
| 1 | 23 | 1.242 | -62.8333 | 3948.028 | -78.039 | 0.007673 | 58.8722 |
| 2 | 59 | 1.326 | -26.8333 | 720.0278 | -35.581 | -0.00353 | 12.4959 |
| 3 | 84 | 1.386 | -1.83333 | 3.361111 | -2.541 | -0.00965 | 93.1506 |
| 4 | 96 | 1.417 | 10.16667 | 103.3611 | 14.40617 | -0.01039 | 107.898 |
| 5 | 120 | 1.512 | 34.16667 | 1167.361 | 51.66 | 0.021141 | 446.932 |
| 6 | 133 | 1.520 | 47.16667 | 2224.694 | 71.69333 | -0.00524 | 27.4556 |
| ∑ | 515 | 8.403 | | 8166.833 | 21.5985 | | 746.804 |
| ∑/n | 85.83333 | 1.4005 | | | | | |
По формулам (21), (22) определяем
R 0 = ¯ R- α R 0 ¯ t = 1.4005 - 0.002645 · 85.83333 = 1.1735 Ом .
Найдем ошибку в определении α. Так как , то по формуле (18) имеем:
.
Пользуясь формулами (23), (24) имеем
;
0.014126 Ом .
Задавшись надежностью P = 0.95, по таблице коэффициентов Стьюдента для n = 6, находим t = 2.57 и определяем абсолютную ошибку Δα = 2.57 · 0.000132 = 0.000338 град -1 .
α = (23 ± 4) · 10 -4 град
-1 при P = 0.95.
Пример 3. Требуется определить радиус кривизны линзы по кольцам Ньютона. Измерялись радиусы колец Ньютона r m и определялись номера этих колец m. Радиусы колец Ньютона связаны с радиусом кривизны линзы R и номером кольца уравнением
r 2 m = mλR - 2d 0 R,
где d 0 толщина зазора между линзой и плоскопараллельной пластинкой (или деформация линзы),
λ длина волны падающего света.
λ = (600 ± 6) нм;
r 2 m = y;
m = x;
λR = b;
-2d 0 R = a,
тогда уравнение примет вид y = a + bx .
.Результаты измерений и вычислений занесены в таблицу 7 .
Таблица 7
| n | x = m | y = r 2 , 10 -2 мм 2 | m -¯ m | (m -¯ m) 2 | (m -¯ m)y | y - bx - a, 10 -4 | (y - bx - a) 2 , 10 -6 |
| 1 | 1 | 6.101 | -2.5 | 6.25 | -0.152525 | 12.01 | 1.44229 |
| 2 | 2 | 11.834 | -1.5 | 2.25 | -0.17751 | -9.6 | 0.930766 |
| 3 | 3 | 17.808 | -0.5 | 0.25 | -0.08904 | -7.2 | 0.519086 |
| 4 | 4 | 23.814 | 0.5 | 0.25 | 0.11907 | -1.6 | 0.0243955 |
| 5 | 5 | 29.812 | 1.5 | 2.25 | 0.44718 | 3.28 | 0.107646 |
| 6 | 6 | 35.760 | 2.5 | 6.25 | 0.894 | 3.12 | 0.0975819 |
| ∑ | 21 | 125.129 | | 17.5 | 1.041175 | | 3.12176 |
| ∑/n | 3.5 | 20.8548333 | | | | | |
Экстраполяция - это метод научного исследования, который основан на распространении прошлых и настоящих тенденций, закономерностей, связей на будущее развитие объекта прогнозирования. К методам экстраполяции относятся метод скользящей средней, метод экспоненциального сглаживания, метод наименьших квадратов.
Сущность метода наименьших квадратов состоит в минимизации суммы квадратических отклонений между наблюдаемыми и расчетными величинами. Расчетные величины находятся по подобранному уравнению – уравнению регрессии. Чем меньше расстояние между фактическими значениями и расчетными, тем более точен прогноз, построенный на основе уравнения регрессии.
Теоретический анализ сущности изучаемого явления, изменение которого отображается временным рядом, служит основой для выбора кривой. Иногда принимаются во внимание соображения о характере роста уровней ряда. Так, если рост выпуска продукции ожидается в арифметической прогрессии, то сглаживание производится по прямой. Если же оказывается, что рост идет в геометрической прогрессии, то сглаживание надо производить по показательной функции.
Рабочая формула метода наименьших квадратов : У t+1 = а*Х + b , где t + 1 – прогнозный период; Уt+1 – прогнозируемый показатель; a и b - коэффициенты; Х - условное обозначение времени.
Расчет коэффициентов a и b осуществляется по следующим формулам:
где, Уф – фактические значения ряда динамики; n – число уровней временного ряда;
Сглаживание временных рядов методом наименьших квадратов служит для отражения закономерности развития изучаемого явления. В аналитическом выражении тренда время рассматривается как независимая переменная, а уровни ряда выступают как функция этой независимой переменной.
Развитие явления зависит не от того, сколько лет прошло с отправного момента, а от того, какие факторы влияли на его развитие, в каком направлении и с какой интенсивностью. Отсюда ясно, что развитие явления во времени выступает как результат действия этих факторов.
Правильно установить тип кривой, тип аналитической зависимости от времени – одна из самых сложных задач предпрогнозного анализа .
Подбор вида функции, описывающей тренд, параметры которой определяются методом наименьших квадратов, производится в большинстве случаев эмпирически, путем построения ряда функций и сравнения их между собой по величине среднеквадратической ошибки, вычисляемой по формуле:
где Уф – фактические значения ряда динамики; Ур – расчетные (сглаженные) значения ряда динамики; n – число уровней временного ряда; р – число параметров, определяемых в формулах, описывающих тренд (тенденцию развития).
Недостатки метода наименьших квадратов :
- при попытке описать изучаемое экономическое явление с помощью математического уравнения, прогноз будет точен для небольшого периода времени и уравнение регрессии следует пересчитывать по мере поступления новой информации;
- сложность подбора уравнения регрессии, которая разрешима при использовании типовых компьютерных программ.
Пример применения метода наименьших квадратов для разработки прогноза
Задача . Имеются данные, характеризующие уровень безработицы в регионе, %
- Постройте прогноз уровня безработицы в регионе на ноябрь, декабрь, январь месяцы, используя методы: скользящей средней, экспоненциального сглаживания, наименьших квадратов.
- Рассчитайте ошибки полученных прогнозов при использовании каждого метода.
- Сравните полученные результаты, сделайте выводы.
Решение методом наименьших квадратов
Для решения составим таблицу, в которой будем производить необходимые расчеты:
Определим условное обозначение времени как последовательную нумерацию периодов базы прогноза (графа 3). Рассчитаем графы 4 и 5. Расчетные значения ряда Ур определим по формуле У t+1 = а*Х + b, где t + 1 – прогнозный период; Уt+1 – прогнозируемый показатель; a и b - коэффициенты; Х - условное обозначение времени.
Коэффициенты a и b определим по следующим формулам:
где, Уф – фактические значения ряда динамики; n – число уровней временного ряда.
а = / = - 0,17
b = 22,13/10 – (-0,17)*55/10 = 3,15
Рассчитываем среднюю относительную ошибку по формуле:
ε = 28,63/10 = 2,86% точность прогноза высокая.
Вывод : Сравнивая результаты, полученные при расчетах методом скользящей средней , методом экспоненциального сглаживания и методом наименьших квадратов, можно сказать, что средняя относительная ошибка при расчетах методом экспоненциального сглаживания попадает в пределы 20-50%. Это значит, что точность прогноза в данном случае является лишь удовлетворительной.
В первом и третьем случае точность прогноза является высокой, поскольку средняя относительная ошибка менее 10%. Но метод скользящих средних позволил получить более достоверные результаты (прогноз на ноябрь – 1,52%, прогноз на декабрь – 1,53%, прогноз на январь – 1,49%), так как средняя относительная ошибка при использовании этого метода наименьшая – 1,13%.
Пример.
Экспериментальные данные о значениях переменных х
и у
приведены в таблице.
В результате их выравнивания получена функция 
Используя метод наименьших квадратов , аппроксимировать эти данные линейной зависимостью y=ax+b (найти параметры а и b ). Выяснить, какая из двух линий лучше (в смысле метода наименьших квадратов) выравнивает экспериментальные данные. Сделать чертеж.
Суть метода наименьших квадратов (МНК).
Задача заключается в нахождении коэффициентов линейной зависимости, при которых функция двух переменных а
и b
принимает наименьшее значение. То есть, при данных а
и b
сумма квадратов отклонений экспериментальных данных от найденной прямой будет наименьшей. В этом вся суть метода наименьших квадратов.
Таким образом, решение примера сводится к нахождению экстремума функции двух переменных.
Вывод формул для нахождения коэффициентов.
Составляется и решается система из двух уравнений с двумя неизвестными. Находим частные производные функции по переменным а
и b
, приравниваем эти производные к нулю.
Решаем полученную систему уравнений любым методом (например методом подстановки
или ) и получаем формулы для нахождения коэффициентов по методу наименьших квадратов (МНК).
При данных а
и b
функция принимает наименьшее значение. Доказательство этого факта приведено .
Вот и весь метод наименьших квадратов. Формула для нахождения параметра a содержит суммы , , , и параметр n - количество экспериментальных данных. Значения этих сумм рекомендуем вычислять отдельно. Коэффициент b находится после вычисления a .
Пришло время вспомнить про исходый пример.
Решение.
В нашем примере n=5
. Заполняем таблицу для удобства вычисления сумм, которые входят в формулы искомых коэффициентов.
Значения в четвертой строке таблицы получены умножением значений 2-ой строки на значения 3-ей строки для каждого номера i .
Значения в пятой строке таблицы получены возведением в квадрат значений 2-ой строки для каждого номера i .
Значения последнего столбца таблицы – это суммы значений по строкам.
Используем формулы метода наименьших квадратов для нахождения коэффициентов а
и b
. Подставляем в них соответствующие значения из последнего столбца таблицы:
Следовательно, y = 0.165x+2.184 - искомая аппроксимирующая прямая.
Осталось выяснить какая из линий y = 0.165x+2.184
или лучше аппроксимирует исходные данные, то есть произвести оценку методом наименьших квадратов.
Оценка погрешности метода наименьших квадратов.
Для этого требуется вычислить суммы квадратов отклонений исходных данных от этих линий 
и 
, меньшее значение соответствует линии, которая лучше в смысле метода наименьших квадратов аппроксимирует исходные данные.
Так как , то прямая y = 0.165x+2.184 лучше приближает исходные данные.
Графическая иллюстрация метода наименьших квадратов (мнк).
На графиках все прекрасно видно. Красная линия – это найденная прямая y = 0.165x+2.184
, синяя линия – это , розовые точки – это исходные данные.
Для чего это нужно, к чему все эти аппроксимации?
Я лично использую для решения задач сглаживания данных, задач интерполяции и экстраполяции (в исходном примере могли бы попросить найти занчение наблюдаемой величины y при x=3 или при x=6 по методу МНК). Но подробнее поговорим об этом позже в другом разделе сайта.
Доказательство.
Чтобы при найденных а
и b
функция принимала наименьшее значение, необходимо чтобы в этой точке матрица квадратичной формы дифференциала второго порядка для функции была положительно определенной. Покажем это.
Дифференциал второго порядка имеет вид:
То есть
Следовательно, матрица квадратичной формы имеет вид
причем значения элементов не зависят от а
и b
.
Покажем, что матрица положительно определенная. Для этого нужно, чтобы угловые миноры были положительными.
Угловой минор первого порядка 
. Неравенство строгое, так как точки несовпадающие. В дальнейшем это будем подразумевать.
Угловой минор второго порядка
Докажем, что 
методом математической индукции .
Вывод
: найденные значения а
и b
соответствуют наименьшему значению функции , следовательно, являются искомыми параметрами для метода наименьших квадратов.