Методът на най-малките квадрати в Excel. Регресионен анализ

Методът на най-малките квадрати (OLS) се отнася дорегресионен анализ. Той има много приложения, тъй като позволява приблизително представяне на дадена функция от други прости. OLS може да бъде изключително полезно при обработката на наблюденията и активно се използва за оценка на определени количества от резултатите от измерванията на други, съдържащи произволни грешки. От тази статия ще научите как да приложите изчисляването на най-малките квадрати в Excel.

Изложение на проблема на конкретен пример

Да предположим, че има два индикатора на X и Y. И Y зависи от X. Тъй като OLS е от интерес за нас от гледна точка на регресионния анализ (в Excel, методите му се изпълняват с помощта на вградени функции), тогава си заслужава веднага да се обърне внимание на конкретен проблем.

Така че, нека X - търговската площ на хранителния магазин, измерена в квадратни метра, и Y - годишния оборот, определени в милиони рубли.

Необходимо е да се направи прогноза, какъв оборот (Y)ще бъде в магазина, ако има една или друга зона за продажба. Очевидно е, че функцията Y = f (X) се увеличава, тъй като хипермаркетът продава повече стоки от щанда.

Няколко думи за точността на първоначалните данни, използвани за прогнозиране

Да приемем, че имаме таблица, изградена според данните за n магазините.

Според математическата статистика, резултатитеще бъде повече или по-малко правилно, ако се проучат данните за най-малко 5-6 обекта. Освен това не можете да използвате "аномални" резултати. По-специално, елитен малък бутик може да има оборот, понякога по-голям от оборота на големите търговски обекти от класа "masmarket".

Същността на метода

Данните в таблицата могат да бъдат представени на картезианската равнина под формата на точки M₁ (х₁, у₁), ... M_п (х_п, у_п). Сега решението на проблема се свежда до избора на приблизителната функция y = f (x), имаща графика, която минава възможно най-близо до точките M_1, М_{2, ..}М_п.

Разбира се, може да се използва висок полиномстепен, но тази възможност не е трудна само за внедряване, но е просто неправилна, тъй като тя няма да отрази главната тенденция, която трябва да бъде открита. Най-разумното решение е да се намери по права линия у = брадва + б, който най-добре се доближава до експерименталните данни, по-точно, на коефициентите - за А и Б.

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

За всяка сближаване оценката на нейната точност става особено важна. Ние отбелязваме чрез e_аз разликата (отклонението) между функционалните и експерименталните стойности за точката x_аз, т.е._аз = y_аз - f (х_аз).

Очевидно е, за да се оцени точността на сближаванетоможе да се използва сумата от отклоненията, т.е. когато се избира линия за приблизително представяне на зависимостта на X от Y, трябва да се даде предпочитание на тази с най-малка стойност на сумата e_аз във всички разглеждани точки. Не всичко е толкова просто, тъй като заедно с положителните отклонения ще има практически отрицателни.

Можете да разрешите проблема с помощта на модули за отклонениеили техните квадрати. Последният метод е най-широко използван. Той се използва в много области, включително регресионен анализ (в Excel, той се изпълнява с помощта на две вградени функции) и отдавна е доказано ефективен.

Метод на най-малките квадрати

В Excel, както знаете, има вграденФункция за автоматично сумиране, която позволява да се изчислят стойностите на всички стойности, намиращи се в избрания диапазон. По този начин нищо не ни пречи да изчислим стойността на израза (напр₁² + д₂² + д₃²+ ... e_п²).

В математическата нотация това има формата:

Тъй като първоначално беше взето решение да се приближи с права линия, имаме:

По този начин, проблемът с намирането на линия, която най-добре описва специфичната зависимост на количествата X и Y, намалява до изчисляване на минималната функция на две променливи:

Това изисква се равнява на нула частни производни на нов променливи а и б и примитивното за решаване на система, състояща се от две уравнения с 2 неизвестни видове:

След прости трансформации, включително разделяне по 2 и манипулиране със сумите, получаваме:

Решаването му, например, чрез метода Cramer, получаваме стационарна точка с някои коефициенти a^* и b^*, Това е минимумът, т.е. да се предскаже какъв оборот ще бъде в магазина за определена област, правата линия y = a^*x + b^*, който е регресионен модел завъпросния пример. Разбира се, това няма да ви позволи да намерите точния резултат, но това ще ви помогне да получите представа дали закупуването на определена област ще се изплати на заема.

Как да приложим метода на най-малките квадрати в Excel

В "Excel" има функция за изчисляване на стойносттаот МНК. Тя има следната форма: "ТЕНДЕНЦИИ" (известни стойности на Y, известни стойности на X, нови стойности на X, const.). Прилагаме формулата за изчисляване на OLS в Excel в нашата таблица.

За това клетката, в която се намирарезултатът от изчислението по метода на най-малките квадрати в Excel се показва, вписваме знака "=" и избираме функцията "TRENDS". В отворения прозорец попълнете съответните полета, като подчертаете:

• диапазон от известни стойности за Y (в този случай данни за оборота);
• обхват х₁, ... x_п, т.е. размера на търговското пространство;
• и известни и неизвестни стойности на х, за които да определят размера на оборота (информация за местоположението им върху лист см. по-долу).

Освен това формулата съдържа логическата променлива "Konst". Ако въведете 1 в съответното поле, това означава, че трябва да извършите изчисления, като приемете, че b = 0.

Ако искате да знаете прогнозата за повече от една стойност на х, а след това след въвеждане на формула, натиснете не на "Enter" ключ, а вие трябва да въведете в комбинация клавиатура на «Shift» + «Контрол» + «Влез» ( «Въвеждане в експлоатация»).

Някои функции

Регресионният анализ може да бъде достигнат доричайници. Excel формула за предсказване на стойностите на масив от неизвестни променливи - "TREND" - може да се използва дори и от тези, които никога не са чували за метода на най-малките квадрати. Достатъчно е просто да знаете някои от характеристиките на нейната работа. По-специално:

• Ако подредите диапазон от известни стойностипроменлива y в един ред или колона, тогава всеки ред (колона) с известни стойности на x ще се възприема от програмата като отделна променлива.
• Ако диапазонът не е посочен в прозореца "ТЕНДЕНЦИИ"известни х, в случай на използване на функцията на софтуер Excel ще го разглежда като масив от цели числа, броят на които съответства на гама с предварително определени стойности на променливата у.
• За да се получи масив от "прогнозираните" стойности на изхода, изразът за изчисляване на тенденцията трябва да бъде въведен като формула на масива.
• Ако не са зададени нови стойности за x, тогава функцията"TRENDS" счита, че те са равнозначни на известни. Ако те не са посочени, тогава масив 1 се приема като аргумент; 2; 3; 4; ..., което е пропорционално на обхвата с вече дадени параметри у.
• Диапазонът, съдържащ новите х стойности, трябва да бъдесе състоят от еднакви или повече редове или колони като диапазон с дадени стойности на y. С други думи, тя трябва да бъде пропорционална на независимите променливи.
• В масив с известни стойности на x,съдържат няколко променливи. Ако обаче е само една, се изисква диапазоните с дадени стойности на x и y да са съизмерими. В случай на няколко променливи е необходимо обхватът с дадени стойности на y да се съдържа в една колона или в един ред.

Функция "PREDICTION"

Регресионният анализ в Excel се изпълнява сизползвайки няколко функции. Една от тях се нарича "PREDICTION". Тя е подобна на "ТЕНДЕНЦИИ", т.е. тя дава резултат от изчисления, използвайки метода на най-малките квадрати. Само за един X, за който стойността на Y не е известна.

Сега знаете формулите в Excel за Dummies, които ви позволяват да предскажете стойността на бъдещата стойност на даден индикатор според линейна тенденция.