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

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

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

Да предположим, че има два параметъра X и Y. И Y зависи от X. От МНК интересите на САЩ по отношение на регресионен анализ (Excel в своите методи се прилагат с помощта на вградени функции), трябва незабавно да отидете на разглеждане на конкретна задача.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

За да направим това, трябва да преценим нулевите частични деривати по отношение на новите променливи a и b и да решим една примитивна система, състояща се от две уравнения с две неизвестни:

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

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

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

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

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

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

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

Ако трябва да знаете прогнозата за повече от една стойност от x, след като въведете формулата, не трябва да натиснете "Enter", но трябва да въведете "Shift" + "Control" + "Enter" на клавиатурата.

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

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

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

Функция "PREDICTION"

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

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