Метод наименьших квадратов в Excel. Регрессионный анализ

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

Постановка проблемы на определенном образце

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

Итак, пускай X — торговая зона продовольственного маркета, меримая в квадратных метрах, а вот Y — годовой товарооборот, ориентируемый в миллионах руб..

Требуется выполнить мониторинг, каковой товарооборот (Y) станет у маркета, в случае если у него какая-нибудь торговая зона. Бесспорно, что функция Y = f (X) возрастающая, ведь гипермаркет реализовывает чаще товаров, нежели ларек.

Несколько текстов о корректности начальных сведений, оборотных для исчезновения

Допустим, у нас имеется список, возведенная по достоверным сведениям для n маркетов.

X

x1

x2

xn

Y

y1

y2

yn

Согласно математической статистике, итоги будут иметься более или менее корректными, в случае если изучаются сведения по впрочем б 5-6 объектам. А также, невозможно ввести «неестественные» итоги. Например, лучший маленький бутик имеет возможность обладать товарооборот в разы больший, нежели товарооборот наибольших торговых пикселей класса «масмаркет».

Суть метода

Данные таблицы возможно изобразить на декартовой поверхности в качестве пикселей M1 (x1, y1), … Mn (xn, yn). В настоящее время заключение проблемы сведется к подбору аппроксимирующей функции y = f (x), располагающей график, идущий как возможно поближе к точкам M1, M2, ..Mn.

Конечно, применяют многочлен важной степени, однако подобный вариант включая труднореализуем, а также запросто некорректен, ведь не станет отбивать ведущую веянию, какую и должно выявить. Наиболее благоразумным заключением представляет собой исследование буквальной у = ax + b, какая наилучшим образом приближает опытные сведения, a вернее, коэффициентов — a и b.

Оценка точности

При абсолютно любой аппроксимации необычную эпохальность обретает анализа ее точности. Обозначим сквозь ei разницу (отклонение) меж опытными и функциональными значениями для точки xi, т. е. ei = yi — f (xi).

Очевидно, что для анализы точности аппроксимации применяют необходимую сумму отклонений, т. е. в момент выбора буквальной для приближенного впечатления зависимости X от Y должно уделять основное внимание той, у какой кратчайшее величина необходимой суммы ei во всех без исключения рассматриваемых точках. Ведь, не все так запросто, ведь в одном ряду с лестными отклонениями буквально будут находиться и негативные.

Решить вопрос возможно, применяя модули отклонений или же их квадраты. Завершающий способ обрел более широченное распространение. Он в ход идет во множества областях, в том числе регрессионный анализ (в Excel его продажа исполняется за счет 2-х вмонтированных функций), и давным-давно доказал собственную отдача.

Метод кратчайших квадратов

В Excel, как нам известно, есть вмонтированная функция автосуммы, дающая возможность определить уровни всех без исключения уровней, находящихся в удаленном промежутке. Следовательно, ничто не навредит для нас рассчитать величина выражения (e12 + e22 + e32+ … en2).

В математической записи это обладает образец:

Ведь с самого начала было решено об аппроксимировании за счет буквальной, то насчитываем:

Следовательно, дилемма пребывания буквальной, какая наилучшим образом описывает определенную зависимость величин X и Y, сводится к исчислению минимального количества функции 2-х непостоянных:

Чтобы достичь желаемого результата надо приравнять к нулю приватные производные по новым непостоянным a и b, и решить примитивную конструкцию, состоящую из 2-х уравнений с двумя анонимными облика:

После нехитрых преобразований, в том числе деление на два и манипуляции с суммами, получим:

Решая ее, в частности, методом Крамера, покупаем стационарную точку с какими-то коэффициентами a* и b*. Именно это имеется минимальное количество, т. е. для исчезновения, каковой товарооборот станет у маркета при конкретной площади, подойдет прямая y = a*x + b*, являющая собой регрессионную модель для образца, о каком проходит речь. Еще бы, она не даст возможность обнаружить верный исход, однако может помочь заполучить спектакль про то, окупится ли покупка в долг маркета именной площади.

Как реализоавать способ кратчайших квадратов в Excel

В «Эксель» есть функция для вычисления уровни по МНК. Она обладает ближайший образец: «ТЕНДЕНЦИЯ» (известн. уровни Y; известн. уровни X; ранее не известные уровни X; конст.). Используем формулу вычисления МНК в Excel к нашей таблице.

Чтобы достичь желаемого результата в ячейку, в какой обязан быть отражен исход вычисления по методу кратчайших квадратов в Excel, используем символ «=» и выкарабкаем функцию «ТЕНДЕНЦИЯ». В открывшемся окошке наполним надлежащие поля, выделяя:

  • диапазон ведомых уровней для Y ( в этом примере сведения для товарооборота);
  • диапазон x1, …xn, т. е. величины торговых площадей;
  • и ведомые, и анонимные уровни x, для какого должно проверить величину товарооборота (сообщение про их месторасположении на действующем листе см. затем).

А также, в формуле имеется закономерная непостоянная «Конст». В случае если использовать в надлежащее для нее поле один, то это будет значить, что вытекает воплотить исчисления, думая, что b = 0.

Если должно выведать мониторинг для наиболее нежели 1-го уровни x, то опосля ввода формулы вытекает нажать не на «Ввод», а вот должно набрать на клавиатуре (клаве) комбинацию «Shift» + «Control»+ «Enter» («Ввод»).

Некоторые качества

Регрессионный анализ быть может общедоступен в том числе чайникам. Формула Excel для исчезновения уровни массива анонимных непостоянных — «ТЕНДЕНЦИЯ» — имеет возможность использоваться в том числе теми, кто вовек не слышал о методе кратчайших квадратов. Необходимо запросто быть в курсе какие-либо качества ее деятельности. Например:

  • Если предрасположить диапазон ведомых уровней непостоянной y в некой строчке или же столбце, то всякая строчка (столбец) с ведомыми значениями x станет приниматься программкой в виде единичной непостоянной.
  • Если в окошке «ТЕНДЕНЦИЯ» не указан диапазон с ведомыми x, то в любом случае применения функции в Excel программка станет оценивать его как массив, состоящий из круглых количеств, численность каких отвечает промежутку с указанными значениями непостоянной y.
  • Чтобы заполучить на выходе массив «предсказанных» уровней, выражение для исчисления направленности должно вводить как формулу массива.
  • Если не велены ранее не известные уровни x, то функция «ТЕНДЕНЦИЯ» полагает их равным ведомым. В случае если и они не миссы, то в виде аргумента приступает массив один; два; трем; четвертая;…, какой соразмерен промежутку с уже указанными параметрами y.
  • Диапазон, вмещающий ранее не известные уровни x вынужден состоять из настолько же или же большего числа строчек или же столбцов, как диапазон с указанными значениями y. Иначе говоря он обязан быть соразмерным автономным непостоянным.
  • В массиве с ведомыми значениями x имеет возможность находиться некоторого количества непостоянных. Ведь в случае если речь проходит лишь только об одной, то надо, дабы промежутки с указанными значениями x и y имелись соразмерны. В любом случае немногих непостоянных должно, дабы диапазон с указанными значениями y вмещался в некоем столбце или же в некой строчке.

Функция «ПРЕДСКАЗ»

Регрессионный анализ в Excel реализуется за счет немногих функций. 1 из них именуется «ПРЕДСКАЗ». Она подобна «ТЕНДЕНЦИИ», т. е. предоставляет исход исчислений по методу кратчайших квадратов. Ведь всего для 1-го X, для какого анонимно величина Y.

Теперь вы понимаете формулы в Excel для чайников, дающие возможность спрогнозировать значение грядущего уровни какого-нибудь показателя в соответствии линейному тренду.

Рейтинг
( Пока оценок нет )
Загрузка ...