Метод на претеглени най-малки квадрати в excel. Прилагане на метода на най-малките квадрати в Excel

Методът на най-малките квадрати (OLS) се основава на минимизиране на сумата от квадратите на отклоненията на избраната функция от изследваните данни. В тази статия ние приближаваме наличните данни с помощта на линейна функцияг = а х + б .

Метод на най-малкия квадрат(англ. обикновени Най-малкото Квадратчета , OLS) е един от основните методи за регресионен анализ по отношение на оценката на неизвестни параметри регресионни моделиспоред примерни данни.

Помислете за апроксимацията по функции в зависимост само от една променлива:

• Линеен: y = ax + b (тази статия)
• : y = a * Ln (x) + b
• : y = a * x m
• : y = a * EXP (b * x) + c
• : y = ax 2 + bx + c

Забележка: В тази статия са разгледани случаите на апроксимация с полином от 3-та до 6-та степен. Тук се обсъжда тригонометричното полиномно приближение.

Линейна зависимост

Интересуваме се от връзката на 2 променливи хи г... Има предположение, че гзависи от хлинейно г = брадва + б... За да определи параметрите на тази връзка, изследователят направи наблюдения: за всяка стойност на x i беше направено измерване на y i (вижте примерния файл). Съответно нека има 20 двойки стойности (x i; y i).

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

От диаграмата е очевидно, че връзката между променливите е близка до линейна. За да разберем коя от многото прави линии най-„правилно“ описва връзката между променливите, е необходимо да се определи критерият, по който ще се сравняват линиите.

Като такъв критерий използваме израза:

където ŷ и = а * x i + б ; n - броят на двойките стойности (в нашия случай n = 20)

Горният израз е сумата от квадратите на разстоянията между наблюдаваните стойности на y i и ŷ i и често се обозначава като SSE ( Сума на На квадрат Грешки (Остатъци), сумата от квадратите на грешките (остатъци)) .

Метод на най-малкия квадрате изборът на такава линия ŷ = брадва + бза които горният израз приема минимална стойност.

Забележка:Всяка линия в двуизмерно пространство се определя еднозначно от стойностите на 2 параметъра: а (наклон) и б (смяна).

Смята се, че колкото по-малка е сумата от квадратите на разстоянията, толкова по-добре съответната линия апроксимира наличните данни и може да се използва допълнително за прогнозиране на стойностите на y от променливата x. Ясно е, че дори ако в действителност няма връзка между променливите или връзката е нелинейна, OLS все пак ще избере „най-добрия“ ред. По този начин OLS не казва нищо за наличието на реална връзка на променливи, методът просто ви позволява да изберете такива параметри на функцията а и б за които горният израз е минимален.

След като извършите не много сложни математически операции (вижте подробностите), можете да изчислите параметрите а и б :

Както можете да видите от формулата, параметърът а представлява ковариационното съотношение и следователно в MS EXCEL за изчисляване на параметъра а можете да използвате следните формули (вж. примерен файл лист Линеен):

= KOVAR (B26: B45; C26: C45) / DISP.G (B26: B45)или

= COVARIATION.B (B26: B45; C26: C45) / DISP.B (B26: B45)

Също така за изчисляване на параметъра а можете да използвате формулата = НАКЛОН (C26: C45; B26: B45)... За параметър б използвайте формулата = ПРИХРАНЯВАНЕ (C26: C45; B26: B45) .

И накрая, функцията LINEST () ви позволява да оцените и двата параметъра наведнъж. За да въведете формула LINEST (C26: C45; B26: B45)необходимо е да изберете клетки в ред 2 и да натиснете CTRL + SHIFT + ENTER(виж статията за). Ще бъде върната стойността в лявата клетка а , вдясно - б .

Забележка: За да не се забърквате с входа формули за масивище трябва да използвате допълнително функцията INDEX (). Формула = ИНДЕКС (ЛИНИЯ (C26: C45; B26: B45); 1)или просто = LINEST (C26: C45; B26: B45)ще върне параметъра, отговорен за наклона на линията, т.е. а ... Формула = ИНДЕКС (ЛИНИЯ (C26: C45; B26: B45); 2)ще върне параметъра, отговорен за пресичането на линията с оста Y, т.е. б .

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

Друг начин за начертаване на права линия с помощта на метода на най-малките квадрати е инструментът за диаграма Линия на тренда... За да направите това, изберете диаграмата, изберете от менюто Раздел оформление, v групов анализщракнете Линия на тренда, тогава Линейна апроксимация .

Като поставите отметка в квадратчето „Показване на уравнението в диаграмата“ в диалоговия прозорец, можете да се уверите, че параметрите, намерени по-горе, съвпадат със стойностите на диаграмата.

Забележка: За да съвпадат параметрите, диаграмата трябва да има тип. Факт е, че при изграждането на диаграма ГрафикСтойностите на оста X не могат да бъдат посочени от потребителя (потребителят може да посочи само етикети, които не засягат местоположението на точките). Вместо X стойности се използва последователност 1; 2; 3; … (За номера на категории). Следователно, ако изградите тренд линияна диаграма като График, тогава вместо действителните стойности на X ще бъдат използвани стойностите на тази последователност, което ще доведе до неправилен резултат (освен ако, разбира се, действителните стойности на X не съвпадат с последователността 1 ; 2; 3; ...).

4.1. Използване на вградени функции

Изчисление регресионни коефициентиизвършва с помощта на функцията

LINEST(Стойности_y; X-стойности; Konst; статистика),

Стойности_y- масив от y стойности,

X-стойности- незадължителен масив от стойности хако масив хе пропуснато, тогава се приема, че това е масив (1; 2; 3; ...) със същия размер като Стойности_y,

Konst- булева стойност, която показва дали константата е задължителна ббеше равно на 0. Ако Konstима смисъл ВЯРНОили пропуснато тогава бсе изчислява по обичайния начин. Ако аргументът Konstзначи е НЕПРАВНО бе зададено равно на 0 и стойностите аса избрани така, че връзката y = ax.

Статистика- булева стойност, която показва дали да се върнат допълнителни статистики за регресия. Ако аргументът Статистикаима смисъл ВЯРНО, след това функцията LINESTвръща допълнителна регресионна статистика. Ако аргументът Статистикаима смисъл ЛЪЖАЩАили пропуснат, тогава функцията LINESTвръща само коефициента аи постоянно б.

Трябва да се помни, че резултатът от функциите ЛИНИЯ ()е набор от стойности - масив.

За изчисление коефициент на корелациясе използва функция

CORREL(Масив1;Масив2),

връщане на стойностите на коефициента на корелация, където Масив1- масив от стойности г, Масив2- масив от стойности х. Масив1и Масив2трябва да са със същото измерение.

ПРИМЕР 1... Пристрастяване г(х) е представена в таблицата. Изграждане регресионна линияи изчисли коефициент на корелация.

Нека да въведем таблица със стойности в лист на MS Excel и да изградим диаграма на разсейване. Работният лист ще приеме формата, показана на фиг. 2.

За изчисляване на стойностите на регресионните коефициенти аи бразпределете клетки A7: B7,обърнете се към съветника за функции и в категорията Статистическиизберете функцията LINEST... Попълнете появилия се диалогов прозорец, както е показано на фиг. 3 и натиснете Добре.

В резултат на това изчислената стойност ще се появи само в клетката A6(фиг. 4). За да се появи стойността в клетката B6е необходимо да влезете в режим на редактиране (клавиш F2)и след това натиснете клавишната комбинация CTRL + SHIFT + ENTER.

За изчисляване на стойността на коефициента на корелация на клетка C6беше въведена следната формула:

C7 = CORREL (B3: J3; B2: J2).

Познаване на коефициентите на регресия аи бизчислете стойностите на функцията г=брадва+бза даденост х... За да направите това, въвеждаме формулата

B5 = $ A $ 7 * B2 + $ B $ 7

и го копирайте в диапазона C5: J5(фиг. 5).

Нека начертаем линията на регресия на диаграмата. Изберете експерименталните точки на графиката, щракнете с десния бутон и изберете командата Първоначални данни... В диалоговия прозорец, който се показва (фиг. 5), изберете раздела Реди щракнете върху бутона Добавете... Попълнете полетата за въвеждане, както е показано на фиг. 6 и натиснете бутона Добре... Към графиката на експерименталните данни се добавя регресионна линия. По подразбиране неговата графика ще се показва като точки, които не са свързани с изглаждащи линии.

За да промените външния вид на регресионната линия, следвайте тези стъпки. Щракнете с десния бутон върху точките, представляващи линейната графика, изберете командата Тип диаграмаи задайте изгледа на диаграмата на разсейване, както е показано на фиг. 7.

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

В резултат на всички трансформации ще получим графика с експериментални данни и регресионна линия в една графична област (фиг. 9).

4.2. Използване на тренд линия.

Конструирането на различни апроксимиращи зависимости в MS Excel е реализирано като свойство на диаграма - тренд линия.

ПРИМЕР 2... В резултат на експеримента беше определена някаква таблична зависимост.

Изберете и изградете приблизителна зависимост. Изграждане на графики на таблична и избрана аналитична зависимост.

Решението на проблема може да бъде разделено на следните стъпки: въвеждане на първоначални данни, изграждане на точков график и добавяне на линия на тенденция към този график.

Нека разгледаме този процес подробно. Нека да въведем необработените данни в работния лист и да начертаем експерименталните данни. След това изберете експерименталните точки на графиката, щракнете с десния бутон и използвайте командата Добаветел тенденция инициатива(фиг. 10).

Появяващият се диалогов прозорец ви позволява да изградите приблизителна зависимост.

Първият раздел (фиг. 11) на този прозорец показва вида на апроксимиращата зависимост.

На втория (фиг. 12) се определят конструктивните параметри:

· Името на апроксимиращата зависимост;

Прогноза напред (назад) от нединици (този параметър определя колко единици напред (назад) е необходимо да се разшири линията на тренда);

Дали да се покаже пресечната точка на крива с права линия y = const;

· Показване на апроксимиращата функция на диаграмата или не (опция за показване на уравнението на диаграмата);

· Дали да се постави стойността на стандартното отклонение на диаграмата или не (поставете параметъра на диаграмата стойността на надеждността на апроксимацията).

Нека изберем полином от втора степен като апроксимираща зависимост (фиг. 11) и да изведем уравнението, описващо този полином на графиката (фиг. 12). Получената диаграма е показана на фиг. тринадесет.

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

Линеен г=a ∙ x+б,

Логаритмичен г=a ∙ ln(х)+б,

Експоненциална г=а ∙ д б,

Силово право г=a ∙ x b,

Полином г=a ∙ x 2 +b ∙ x+° С, г=a ∙ x 3 +b ∙ x 2 +c ∙ x + dи така нататък до полином от 6-та степен включително,

· Линейно филтриране.

4.3. Използване на инструмента за анализ на опции: Намиране на решение.

Значителен интерес представлява реализацията в MS Excel на избора на параметрите на функционалната зависимост по метода на най-малките квадрати с помощта на инструмента за анализ на опции: Търсене на решение. Тази техника ви позволява да избирате параметрите на функция от всякакъв вид. Нека разгледаме тази възможност, като използваме примера на следния проблем.

ПРИМЕР 3... В резултат на експеримента е получена зависимостта z (t), представена в таблицата

Изберете коефициенти на зависимост Z (t) = At ​​4 + Bt 3 + Ct 2 + Dt + Kметод на най-малките квадрати.

Този проблем е еквивалентен на проблема за намиране на минимума на функция от пет променливи

Нека разгледаме процеса на решаване на задачата за оптимизация (фиг. 14).

Нека стойностите А, V, С, ди ДА СЕсъхранявани в клетките A7: E7... Нека изчислим теоретичните стойности на функцията З(т)=При 4 + Bt 3 + Ct 2 + Dt + Kза даденост т(B2: J2). За да направите това, в клетката B4въведете стойността на функцията в първата точка (клетка B2):

B4 = $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

Нека копираме тази формула в диапазона C4: J4и получаваме очакваната стойност на функцията в точките, чиито абсциси се съхраняват в клетките B2: J2.

В клетката B5въвеждаме формула, която изчислява квадрата на разликата между експерименталните и изчислените точки:

B5 = (B4-B3) ^ 2,

и го копирайте в диапазона C5: J5... В клетка F7ще съхраняваме общата квадратна грешка (10). За да направите това, въвеждаме формулата:

F7 = СУМА (B5: J5).

Нека използваме командата Service®Търсете решениеи решаване на проблема за оптимизация без ограничения. Попълнете полетата за въвеждане в диалоговия прозорец, показан на фиг. 14 и натиснете бутона Изпълни... Ако се намери решение, тогава прозорецът, показан на фиг. 15.

Резултатът от работата на блока за решение ще бъде изведен в клетките A7: E7стойности на параметритефункции З(т)=При 4 + Bt 3 + Ct 2 + Dt + K... В клетките B4: J4получи очаквана стойност на функциятав изходните точки. В клетка F7ще се пази обща квадратна грешка.

Можете да начертаете експериментални точки и монтирана линия в същата графична област, като изберете диапазон B2: J4, повикване Съветник за диаграмии след това форматирайте външния вид на получените графики.

Ориз. 17 показва работния лист на MS Excel след изчисленията.

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

Постановка на проблема с помощта на конкретен пример

Да предположим, че има два индикатора X и Y. И Y зависи от X. Тъй като OLS представлява интерес за нас от гледна точка на регресионния анализ (в 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.

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

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

За всяко приближение оценката на неговата точност е от особено значение. Нека означим с e i разликата (отклонението) между функционалните и експерименталните стойности за точка x i, тоест e i = y i - f (x i).

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

Проблемът може да се реши с помощта на модулите на отклоненията или техните квадрати. Последният метод е най-разпространеният. Използва се в много области, включително регресионен анализ (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

Excel има функция за изчисляване на стойността на OLS. Той има следната форма: "TREND" (известни Y стойности; известни X стойности; нови X стойности; const.). Нека приложим формулата за изчисляване на OLS в Excel към нашата таблица.

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

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

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

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

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

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

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

Функция ПРОГНОЗА

Реализира се с няколко функции. Една от тях се казва "ПРОГНОЗА". Той е подобен на "TREND", тоест дава резултат от изчисления по метода на най-малките квадрати. Но само за един X, за който стойността на Y е неизвестна.

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

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

Постановка на проблема с помощта на конкретен пример

Да предположим, че има два индикатора X и Y. И Y зависи от X. Тъй като OLS представлява интерес за нас от гледна точка на регресионния анализ (в 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.

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

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

За всяко приближение оценката на неговата точност е от особено значение. Нека означим с e i разликата (отклонението) между функционалните и експерименталните стойности за точка x i, тоест e i = y i - f (x i).

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

Проблемът може да се реши с помощта на модулите на отклоненията или техните квадрати. Последният метод е най-разпространеният. Използва се в много области, включително регресионен анализ (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

Excel има функция за изчисляване на стойността на OLS. Той има следната форма: "TREND" (известни Y стойности; известни X стойности; нови X стойности; const.). Нека приложим формулата за изчисляване на OLS в Excel към нашата таблица.

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

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

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

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

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

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

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

Функция ПРОГНОЗА

Регресионният анализ в Excel се реализира с помощта на няколко функции. Една от тях се казва "ПРОГНОЗА". Той е подобен на "TREND", тоест дава резултат от изчисления по метода на най-малките квадрати. Но само за един X, за който стойността на Y е неизвестна.

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

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

Използване на метода в Excel

o Активиране на добавката Solver

o Проблемни условия

o Решение

Използване на метода в Excel

Методът на най-малките квадрати (OLS) е математическо описание на зависимостта на една променлива от другата. Може да се използва при прогнозиране.

Активиране на добавката Solver

За да използвате OLS в Excel, трябва да активирате добавката "Търсете решение"който е деактивиран по подразбиране.

1. Отидете на раздела "Файл".

2. Щракнете върху името на раздела "Параметри".

3. В прозореца, който се отваря, спрете избора на подраздел "Добавки".

4. В блока "контрол"разположен в долната част на прозореца, поставете превключвателя в положение Добавки на Excel(ако има различна стойност) и щракнете върху бутона "Отивам ...".

5. Отваря се малък прозорец. Поставяме отметка до параметъра в него "Търсете решение"... Щракнете върху бутона "ДОБРЕ".

Сега функцията Намиране на решениев Excel се активира и инструментите му са се появили на лентата.

Урок:Намиране на решение в Excel

Условия на проблема

Нека опишем приложението на OLS с конкретен пример. Имаме два реда числа хи г, чиято последователност е показана на изображението по-долу.

Функцията може най-точно да опише тази зависимост:

Освен това е известно, че за x = 0 yсъщо е равно 0 ... Следователно това уравнение може да се опише чрез зависимостта y = nx.

Трябва да намерим минималния сбор от квадратите на разликата.

Решение

Нека да преминем към описанието на директното приложение на метода.

1. Вляво от първата стойност хсложи номер 1 ... Това ще бъде приблизителната стойност на първата стойност на коефициента н.

2. Вдясно от колоната гдобавете още една колона - nx... В първата клетка на тази колона напишете формулата за умножаване на коефициента нна клетка от първата променлива х... В същото време правим връзката към полето с коефициента абсолютна, тъй като тази стойност няма да се промени. Щракнете върху бутона Въведете.

3. Използвайки манипулатор за запълване, копирайте тази формула в целия диапазон на таблицата в колоната по-долу.

4. В отделна клетка изчислете сумата от разликите на квадратите на стойностите ги nx... За да направите това, кликнете върху бутона "Вмъкване на функция".

5. В отворените "Съветник за функции"търси рекорд "SUMKVRAZN"... Изберете го и кликнете върху бутона "ДОБРЕ".

6. Отваря се прозорецът с аргументи. В полето "Масив_x" г... В полето "Масив_y"въвеждаме диапазона от клетки на колоната nx... За да въведете стойности, просто поставете курсора в полето и изберете съответния диапазон на листа. След като въведете, кликнете върху бутона "ДОБРЕ".

7. Отидете на раздела "Данни"... На лентата в кутията с инструменти "анализ"натиснете бутона "Търсете решение".

8. Отваря се прозорецът с параметри за този инструмент. В полето „Оптимизиране на целевата функция“посочваме адреса на клетката с формулата "SUMKVRAZN"... В параметъра "Преди"не забравяйте да поставите превключвателя в положение "минимум"... В полето "Промяна на клетки"посочете адреса със стойността на коефициента н... Щракнете върху бутона "Намери решение".

9. Решението ще се покаже в клетката на коефициента н... Именно тази стойност ще бъде най-малкият квадрат на функцията. Ако резултатът удовлетворява потребителя, натиснете бутона "ДОБРЕ"в допълнителен прозорец.

Както можете да видите, прилагането на метода на най-малките квадрати е доста сложна математическа процедура. Показахме го в действие с помощта на най-простия пример, но има много по-сложни случаи. Въпреки това, инструментариумът на Microsoft Excel е предназначен да опрости възможно най-много изчисленията.

http://multitest.semico.ru/mnk.htm

Общи положения

Колкото по-малко е числото в абсолютна стойност, толкова по-добре е избрана правата линия (2). Като характеристика на точността на избора на права линия (2) можем да вземем сбора от квадрати

Минималните условия за S ще бъдат

	(6)
	(7)

Уравнения (6) и (7) могат да бъдат записани, както следва:

	(8)
	(9)

От уравнения (8) и (9) е лесно да се намери a и b от експерименталните стойности x i и y i. Линия (2), дефинирана от уравнения (8) и (9), се нарича линията, получена по метода на най-малките квадрати (това име подчертава факта, че сумата от квадрати S има минимум). Уравнения (8) и (9), от които се определя правата линия (2), се наричат ​​нормални уравнения.

Можете да посочите прост и общ начин за писане на нормални уравнения. Използвайки експерименталните точки (1) и уравнение (2), можем да напишем системата от уравнения за a и b

Умножаваме лявата и дясната страна на всяко от тези уравнения по коефициента на първото неизвестно a (т.е. по x 1, x 2, ..., xn) и добавяме получените уравнения, резултатът е първото нормално уравнение ( 8).

Умножаваме лявата и дясната част на всяко от тези уравнения по коефициента на втората неизвестна b, т.е. с 1 и добавете получените уравнения, резултатът е второто нормално уравнение (9).

Този метод за получаване на нормални уравнения е общ: подходящ е например за функцията

има постоянна стойност и тя трябва да се определи от експериментални данни (1).

Системата от уравнения за k може да се запише:

Намерете линия (2), като използвате метода на най-малките квадрати.

Решение.Намираме:

X i = 21, y i = 46,3, x i 2 = 91, x i y i = 179,1.

Записваме уравнения (8) и (9) 91a + 21b = 179.1,

21a + 6b = 46,3, следователно намираме
a = 0,98 b = 4,3.