Метод на най-малките квадрати в Excel. Регресионен анализ
Най-малките квадрати е математическа процедура за конструиране на линейно уравнение, което най-точно съответства на набор от две серии от числа. Целта на този метод е да се сведе до минимум общата квадратна грешка. Excel има инструменти, които можете да използвате, за да приложите този метод при изчисления. Нека видим как се прави това.
Методът на най-малките квадрати (OLS) е математическо описание на зависимостта на една променлива от другата. Може да се използва при прогнозиране.
Активиране на добавката Solver
За да използвате OLS в Excel, трябва да активирате добавката "Търсете решение"който е деактивиран по подразбиране.
Сега функцията Намиране на решениев Excel се активира и инструментите му са се появили на лентата.
Условия на проблема
Нека опишем приложението на OLS с конкретен пример. Имаме два реда числа х и г , чиято последователност е показана на изображението по-долу.
Функцията може най-точно да опише тази зависимост:
Освен това е известно, че за х = 0 гсъщо е равно 0 ... Следователно това уравнение може да се опише чрез зависимостта y = nx .
Трябва да намерим минималния сбор от квадратите на разликата.
Решение
Нека да преминем към описанието на директното приложение на метода.
Както можете да видите, прилагането на метода на най-малките квадрати е доста сложна математическа процедура. Показахме го в действие с помощта на най-простия пример, но има много по-сложни случаи. Въпреки това, инструментариумът на Microsoft Excel е предназначен да опрости възможно най-много изчисленията.
Метод на най-малките квадрати (OLS)
Системата от m линейни уравнения с n неизвестни има вида:
Възможни са три случая: m Ако m> n и системата е последователна, тогава матрицата A има най-малко m - n линейно зависими редове. Тук решението може да се получи чрез избиране на n всякакви линейно независими уравнения (ако съществуват) и прилагане на формулата X = A -1 CHV, тоест чрез свеждане на задачата до предварително решената. В този случай полученото решение винаги ще удовлетворява останалите m - n уравнения. Въпреки това, когато използвате компютър, е по-удобно да използвате по-общ подход - методът на най-малките квадрати. Алгебричният метод на най-малките квадрати се разбира като метод за решаване на системи от линейни уравнения чрез минимизиране на евклидовата норма брадва? б? > инф. (1.2) Помислете за някакъв експеримент, по време на който в моменти от време температурата Q (t) се измерва например. Нека резултатите от измерването са дадени от масива Да приемем, че условията на експеримента са такива, че измерванията се извършват с умишлена грешка. В тези случаи законът за изменение на температурата Q (t) се търси с помощта на някакъв полином P (t) = + + + ... +, определяне на неизвестните коефициенти, ..., от съображенията, че стойността E (, ...,), дефинирана от равенството гаус алгебрично exel приближение взе минималната стойност. Тъй като сумата от квадратите е сведена до минимум, този метод се нарича напасване на данните на най-малките квадрати. Ако заменим P (t) с неговия израз, тогава получаваме Нека поставим проблема за дефиниране на масив така, че стойността да е минимална, т.е. Нека дефинираме масив, използвайки метода на най-малките квадрати. За да направим това, приравняваме частичните производни към нула: Ако въведете m × n матрица A = (), i = 1, 2 ..., m; j = 1, 2, ..., n, където I = 1, 2 ..., m; j = 1, 2, ..., n, тогава писменото равенство приема формата Нека пренапишем написаното равенство по отношение на операциите с матрици. По дефиниция имаме умножение матрица-колона За транспонирана матрица подобна връзка изглежда така Нека въведем обозначението: i-тият компонент на вектора Ax ще бъде обозначен. В съответствие с написаните матрични равенства ще имаме В матрична форма това равенство може да се пренапише като A T x = A T B (1.3) Тук A е правоъгълна m × n матрица. Освен това, в проблемите на апроксимацията на данните, като правило, m> n. Уравнение (1.3) се нарича нормално уравнение. Беше възможно от самото начало, използвайки евклидовата норма на векторите, да запишем проблема в еквивалентна матрична форма: Нашата цел е да минимизираме тази функция по отношение на x. За да бъде достигнат минимумът в точката на решение, първите производни по отношение на x в тази точка трябва да са равни на нула. Производните на тази функция са 2A T B + 2A T Ax и следователно решението трябва да удовлетворява системата от линейни уравнения (A T A) x = (A T B). Тези уравнения се наричат нормални уравнения. Ако A е m × n матрица, тогава A> A - n × n е матрица, т.е. матрицата на нормалното уравнение винаги е квадратна симетрична матрица. Освен това, той притежава свойството на положителна определеност в смисъл, че (A> Ax, x) = (Ax, Ax)? 0 Коментирайте. Понякога решението на уравнение от вида (1.3) се нарича решение на системата Ax = B, където A е правоъгълна m × n (m> n) матрица по метода на най-малките квадрати. Проблемът с най-малките квадрати може да се интерпретира графично като минимизиране на вертикалните разстояния от точките на данните до кривата на модела (вижте фигура 1.1). Тази идея се основава на предположението, че всички грешки в апроксимацията съответстват на грешките в наблюденията. Ако има и грешки в обяснителните променливи, тогава може да е по-подходящо да се сведе до минимум евклидовото разстояние от данните до модела. Алгоритъмът за внедряване на OLS в Excel по-долу предполага, че всички първоначални данни вече са известни. Двете страни на матричното уравнение AЧX = B на системата се умножават отляво по транспонираната матрица на системата AT: AT AX = AT B След това умножаваме двете страни на уравнението вляво по матрицата (AT A) -1. Ако тази матрица съществува, тогава системата е дефинирана. Имайки предвид това (AT A) -1 * (AT A) = E, получаваме X = (AT A) -1 AT B. Полученото матрично уравнение е решение на система от m линейни уравнения с n неизвестни за m> n. Нека разгледаме приложението на горния алгоритъм за конкретен пример. Пример. Нека е необходимо да се реши системата В Excel списъкът с решението в режима на показване на формули за тази задача изглежда така: Резултати от изчисленията: Необходимият вектор X се намира в диапазона E11: E12. При решаване на дадена система от линейни уравнения са използвани следните функции: 1. MOBRE - Връща обратното на матрица, съхранена в масив. Синтаксис: MOBR (масив). Масив - Цифров масив с равен брой редове и колони. 2. MULTIPLE - връща произведението на матриците (матриците се съхраняват в масиви). Резултатът е масив със същия брой редове като масив1 и същия брой колони като масив2. Синтаксис: MULTIPLE (масив1, масив2). Масив1, масив2 - умножени масиви. След като въведете функцията в горната лява клетка на диапазона на масива, изберете масива, започващ с клетката, съдържаща формулата, натиснете F2 и след това натиснете CTRL + SHIFT + ENTER. 3. TRANSPOSE - преобразува вертикален набор от клетки в хоризонтален или обратно. Резултатът от използването на тази функция е масив с броя на редовете, равен на броя на колоните в оригиналния масив и броя на колоните, равен на броя на редовете в първоначалния масив. Е, на работа са докладвали на инспекцията, статията е написана вкъщи за конференцията - сега можете да пишете в блога. Докато обработвах данните си, разбрах, че няма как да не пиша за една много готина и необходима добавка в Excel, която се нарича. Така че статията ще бъде посветена на тази конкретна добавка и аз ще ви разкажа за нея, като използвам пример за употреба метод на най-малките квадрати(OLS) за търсене на неизвестни коефициенти на уравнение при описание на експериментални данни. Как да активирате добавката Find Solution Първо, нека да разберем как да активираме тази добавка. 1. Отидете в менюто "Файл" и изберете "Опции на Excel" 2. В прозореца, който се показва, изберете "Търсене на решение" и щракнете върху "Отиди". 3. В следващия прозорец поставете отметка пред елемента "търсене на решение" и щракнете върху "OK". 4. Добавката е активирана - вече може да бъде намерена в елемента от менюто "Данни". Сега накратко за метод на най-малките квадрати (OLS)
и къде може да се приложи. Да кажем, че имаме набор от данни, след като направихме някакъв експеримент, в който изследвахме ефекта на стойността X върху стойността на Y. Искаме да опишем това влияние математически, така че по-късно да можем да използваме тази формула и да знаем, че ако променим стойността на X с толкова много, ще получим стойността на Y така и така ... Ще взема един супер прост пример (виж фиг.). Ясно е, че точките са разположени една след друга, сякаш по права линия, и затова спокойно приемаме, че нашата зависимост е описана от линейна функция y = kx + b. В същото време определено сме сигурни, че когато X е равно на нула, стойността на Y също е равна на нула. Това означава, че функцията, описваща зависимостта, ще бъде още по-проста: y = kx (спомнете си училищната програма). Като цяло трябва да намерим коефициента k. С това ще направим OLS
с помощта на добавката "търсене на решение". Методът се състои във факта, че (тук - внимание: трябва да помислите за това) сумата от квадратите на разликите между експериментално получените и съответните изчислени стойности е била минимална. Тоест, когато X1 = 1 действително измерената стойност Y1 = 4,6 и изчислената y1 = f (x1) е 4, квадратът на разликата ще бъде (y1-Y1) ^ 2 = (4-4,6) ^ 2 = 0,36 ... Със следното същото: когато X2 = 2, действително измерената стойност Y2 = 8,1 и изчислената y2 е 8, квадратът на разликата ще бъде (y2-Y2) ^ 2 = (8-8,1) ^ 2 = 0,01 . И сумата от всички тези квадрати трябва да бъде възможно най-малка. И така, нека започнем да тренираме за използване на OLS и Намерете Solution Excel Add-ins
. 1. Ако не сте включили добавката "търсене на решение", тогава се върнете към точката Как да активирате търсенето на добавка за решение и да активирате 🙂 2. В клетка A1 въведете стойността "1". Тази единица ще бъде първото приближение към реалната стойност на коефициента (k) на нашата функционална зависимост y = kx. 3. В колона B имаме стойностите на параметъра X, в колона C - стойностите на параметъра Y. В клетките на колоната D въвеждаме формулата: "коефициент k, умножен по стойността от X". Например, в клетка D1 въвеждаме "= A1 * B1", в клетка D2 - "= A1 * B2" и т.н. 4. Смятаме, че коефициентът k е равен на единица и функцията f (x) = y = 1 * x е първото приближение към нашето решение. Можем да изчислим сумата от квадратите на разликите между измерените стойности на Y и тези, изчислени по формулата y = 1 * x. Можем да направим всичко това ръчно, като въведем съответните препратки към клетки във формулата: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... и т.н. Накрая грешим и да разберем, че сме загубили много време. В Excel, за изчисляване на сумата от квадратите на разликите, има специална формула, "SUMKVRAZN", която ще направи всичко вместо нас. Въведете я в клетка A2 и задайте първоначалните данни : диапазонът на измерените стойности Y (колона C) и диапазонът на изчислените стойности Y (колона D). 4. Изчислена е сумата от разликите на квадратите - сега отиваме в раздела „Данни“ и избираме „Търсене на решение“. 5. В менюто, което се появява, изберете клетка A1 (тази с коефициент k) като клетка, която трябва да се промени. 6. Изберете клетка A2 като цел и задайте условието „задаване на минималната стойност“. Не забравяйте, че това е клетката, в която изчисляваме сумата от квадратите на разликите между изчислените и измерените стойности и тази сума трябва да бъде минимална. Щракнете върху "изпълни". 7. Избира се коефициент k. Сега можете да се уверите, че изчислените стойности вече са много близки до измерените. Като цяло, разбира се, за приближаване на експериментални данни в Excel, има специални инструменти, които ви позволяват да опишете данни с помощта на линейна, експоненциална, степенна и полиномна функция, така че често можете да правите без n добавки за търсене на решения... Говорих за всички тези методи за сближаване в моята мина, така че ако се интересувате, погледнете. Но когато става дума за някаква екзотична функция с един неизвестен коефициентили проблеми с оптимизацията, тук надстройкамного уместно. Намерете добавка за решениеможе да се използва за други задачи, основното е да се разбере същността: има клетка, в която избираме стойност, и има целева клетка, в която е зададено условие за избор на неизвестен параметър. Методът на най-малките квадрати (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 има функция за изчисляване на стойността на OLS. Той има следната форма: "TREND" (известни Y стойности; известни X стойности; нови X стойности; const.). Нека приложим формулата за изчисляване на OLS в Excel към нашата таблица. За да направите това, в клетката, в която трябва да се покаже резултатът от изчислението по метода на най-малките квадрати в Excel, въведете знака "=" и изберете функцията "TREND". В прозореца, който се отваря, попълнете съответните полета, като подчертаете: В допълнение, формулата съдържа булева променлива "Const". Ако въведете 1 в съответното поле, това ще означава, че трябва да се извършат изчисления, като се приеме, че b = 0. Ако трябва да знаете прогнозата за повече от една стойност на x, тогава след като въведете формулата, не трябва да натискате "Enter", но трябва да въведете на клавиатурата комбинацията "Shift" + "Control" + "Enter" („Въведете“). Регресионният анализ може дори да е достъпен за манекени. Формулата на Excel за прогнозиране на стойността на масив от неизвестни променливи - "TREND" - може да се използва дори от тези, които никога не са чували за метода на най-малките квадрати. Достатъчно е само да знаете някои от особеностите на нейната работа. По-специално: Регресионният анализ в Excel се реализира с помощта на няколко функции. Една от тях се казва "ПРОГНОЗА". Той е подобен на "TREND", тоест дава резултат от изчисления по метода на най-малките квадрати. Но само за един X, за който стойността на Y е неизвестна. Сега знаете формулите в 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 има функция за изчисляване на стойността на OLS. Той има следната форма: "TREND" (известни Y стойности; известни X стойности; нови X стойности; const.). Нека приложим формулата за изчисляване на OLS в Excel към нашата таблица. За да направите това, в клетката, в която трябва да се покаже резултатът от изчислението по метода на най-малките квадрати в Excel, въведете знака "=" и изберете функцията "TREND". В прозореца, който се отваря, попълнете съответните полета, като подчертаете: В допълнение, формулата съдържа булева променлива "Const". Ако въведете 1 в съответното поле, това ще означава, че трябва да се извършат изчисления, като се приеме, че b = 0. Ако трябва да знаете прогнозата за повече от една стойност на x, тогава след като въведете формулата, не трябва да натискате "Enter", но трябва да въведете на клавиатурата комбинацията "Shift" + "Control" + "Enter" („Въведете“). Регресионният анализ може дори да е достъпен за манекени. Формулата на Excel за прогнозиране на стойността на масив от неизвестни променливи - "TREND" - може да се използва дори от тези, които никога не са чували за метода на най-малките квадрати. Достатъчно е само да знаете някои от особеностите на нейната работа. По-специално: Реализира се с няколко функции. Една от тях се казва "ПРОГНОЗА". Той е подобен на "TREND", тоест дава резултат от изчисления по метода на най-малките квадрати. Но само за един X, за който стойността на Y е неизвестна. Сега знаете формулите в Excel за манекени, които ви позволяват да прогнозирате бъдещата стойност на даден индикатор според линеен тренд.Алгебрични най-малки квадрати
Анализ на експерименталните данни
OLS към Excel
Метод на най-малкия квадрат
Прилагане на добавката за търсене на решение
P.S.
Това е всичко! В следващата статия ще ви разкажа една приказка за ваканция, така че, за да не пропуснете статията,Постановка на проблема с помощта на конкретен пример
Няколко думи за коректността на изходните данни, използвани за прогнозиране
Същност на метода
Оценка на точността
Метод на най-малкия квадрат
Как да приложим метода на най-малките квадрати в Excel
Някои функции
Функция ПРОГНОЗА
Постановка на проблема с помощта на конкретен пример
Няколко думи за коректността на изходните данни, използвани за прогнозиране
Същност на метода
Оценка на точността
Метод на най-малкия квадрат
Как да приложим метода на най-малките квадрати в Excel
Някои функции
Функция ПРОГНОЗА