Методът на най-малките квадрати (MNC) принадлежи към областта на регресионния анализ. Той има много приложения, тъй като ви позволява да извършите приблизително представяне на дадена функция от други, по-прости. MNC може да се окаже изключително полезна при лечението на наблюдения и се използва активно за оценка на някои от стойностите, измерени от други, които съдържат случайни грешки. От тази статия ще научите как да приложите изчислението на най-малките квадрати в Excel.
Изявление на проблем в конкретен пример
Да предположим, че има два показателя за X и Y. Освен това Y зависи от X. Тъй като MNCs ни интересуват от гледна точка на регресионен анализ в Excel, неговите методи се изпълняват с помощта на вградени функции). Същото се отнася и за разглеждането на конкретна задача.
Така, нека X да е търговското пространство на хранителния магазин, което се измерва в квадратни метри, а Y е годишният оборот, който се определя в милиони рубли. Необходимо е да се направи прогноза за това, кой стоков оборот (Y) ще бъде близо до магазина, ако има една или друга търговска зона. Очевидно е, че функцията Y = f (X) се увеличава, тъй като хипермаркетът продава повече стоки, отколкото срив.
Няколко думи за коректността на изходните данни, използвани за прогнозиране
Да предположим, че имаме таблица, построена според данните за n магазина.
X
x 1
x 2
xn
Y
y 1
y 2
ун
математическа статистика, резултатите ще бъдат повече или по-малко коректни, ако се изследват данни за най-малко 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, трябва да се даде предимство на тази с най-малката стойност на сумата e i във всички разглеждани точки. Но не всичко е толкова просто, тъй като заедно с положителните отклонения ще има и практически отрицателни. Можете да разрешите този въпрос с помощта намодули за отклонение или техните квадрати. Последният метод е получил най-разпространеното. Той се използва в много области, включително регресионен анализ в Excel, неговата реализация се извършва с две вградени функции) и отдавна доказа своята ефективност.
Метод на най-малките квадрати
Excel, както е известно, има вградена функция за автоматично сумиране, която ви позволява да изчислите стойностите на всички стойности, разположени в избрания диапазон. Така че нищо не ни пречи да изчислим стойността на израза (e 1 2 + e 2 2 + e 3 2 + e n 2). В математическия запис той има формата:
Тъй като първоначално е решено да се сближи с помощта на права линия, имаме:
По този начин, задачата за намиране на права линия, която най-добре описва специфичната зависимост на величините X и Y, се свежда до изчисляване на минимума на функцията на две променливи:
За да направим това, трябва да приравним частичните частични производни на новите променливи а и b на нула и да решим примитивна система, състояща се от две уравнения на два неизвестни вида:
След прости преобразувания, включително разделяне на 2 и манипулиране със суми, получаваме:
Като го решим, например чрез метода на Крамер, получаваме стационарна точка с определени коефициенти a * и b *. Това е минимумът, т.е. да се предвиди какво ще бъде стоковият оборот в магазина в дадена област, подравнява се права линия y = a * x + b *, което е регресионен модел за въпросния пример. Разбира се, това няма да ви позволи да намерите точния резултат, но ще ви помогне да получите представа дали да купувате или неспецифична зона за съхранение на кредити.
Как да приложим метода на най-малките квадрати в Excel
В Excel има функция за изчисляване на стойността за MNC. Той има следната форма: "TREND" (тъй като стойността е Y; стойността е X; новата стойност е X; const.). Приложете формулата за изчисляване на MNC в таблицата на Excel. За да направите това, в клетка, в която трябва да се покаже резултатът от изчисляването по метода на най-малките квадрати Excel, въвеждаме знака "=" и избираме функцията "TREND". В отворения прозорец, попълнете съответните полета, маркирайки:
обхвата на известните стойности за Y (в този случай данните за оборота);
обхват x 1, x n, т.е. размерът на търговското пространство;
известни неизвестни стойности на x, за които е необходимо да се установи размерът на оборота (информация за тяхното местоположение на лист, виж по-долу).
Освен това във формулата има логическа променлива "Константа". Ако въведете поле 1 в съответното поле, това ще означава, че трябва да се направи изчислението, като се има предвид, че b = 0. Ако искате да знаете прогнозата за повече от една стойност на x, след въвеждане на формулата, трябва да кликнете не на "Enter" но трябва да въведете клавишната комбинация Shift + Ctrl + Enter.
Някои характеристики
Регресионният анализ може дори да бъде достъпен за чайниците. Формулата на Excel за предвиждане на стойността на масив от неизвестни променливи - "TREND" - може да се използва дори от тези, които никога не са чували за метода на най-малките квадрати. Достатъчно е да знаете някои от характеристиките на нейната работа. По-специално:
Ако зададете диапазона на известните стойности на променливата y в един ред или колона, тогаваВсеки ред (колона) с известни стойности на x ще се възприема от програмата като една променлива.
Ако прозорецът ТЕНДЕНЦИЯ не посочва обхват с позната x, тогава, ако използвате функцията в Excel, програмата ще я разглежда като масив, състоящ се от цели числа, чийто номер съответства на диапазона с посочените стойности на променливата y.
За да се получи масив от прогнозирани стойности на изхода, изразът за изчисляване на тренда трябва да се въведе като формула на масива.
Ако не са определени нови стойности на x, функцията "TREND" ги счита за равни на познатите. Ако те не са дадени, масивът се приема като аргумент 1; 2; 3; 4, което е измерим обхват с вече зададени параметри y.
Диапазон, съдържащ нови стойности на x, трябва да се състои от същите или повече редове или колони, както и диапазона с посочените y стойности. С други думи, трябва да са съответните независими променливи.
Поредица от известни стойности на x може да съдържа няколко променливи. Въпреки това, ако е около една, тогава диапазоните с дадените стойности на x и y трябва да бъдат пропорционални. В случай на множество променливи, се изисква обхватът с посочените стойности на y да бъде в един ред или в един ред.
ПРОГНОЗНА функция
Регресионният анализ в Excel се изпълнява с няколко функции. Една от тях се нарича "ПРОГНОЗА". Тя е подобна на "ТЕНДЕНЦИИ", тоест произвежда резултата от изчисленията, използвайки метода на най-малките квадрати. Въпреки това, само за един X, за който неизвестната стойност е Y. Сега знаете формулите в Excel за манекени, които позволяватда се предвиди стойността на бъдещата стойност на един или друг показател в съответствие с линейната тенденция.
