Решете систем на равенки користејќи го методот на најмали квадрати во Excel. Примена на методот на најмали квадрати во Excel. Неколку зборови за исправноста на првичните податоци што се користат за предвидување

Метод најмали квадратие математичка постапка за конструирање на линеарна равенка која најмногу одговара на множество од две серии на броеви. Целта на користењето на овој метод е да се минимизира вкупната квадратна грешка. Excel има алатки кои можат да ви помогнат да го примените овој метод во вашите пресметки. Ајде да дознаеме како се прави ова.

· Користење на методот во Excel

o Овозможување на додатокот „Пребарување решенија“.

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

o Решение

Користење на методот во Excel

Методот на најмали квадрати (LSM) е математички опис на зависноста на една променлива од друга. Може да се користи за прогнозирање.

Овозможување на додатокот Find Solution

За да користите MNC во Excel, треба да го овозможите додатокот „Наоѓање решение“, што е стандардно оневозможено.

1. Одете на јазичето "Датотека".

2. Кликнете на името на делот "Опции".

3. Во прозорецот што се отвора, изберете ја потсекцијата „Додатоци“.

4. Во блокот "Контрола", кој се наоѓа на дното на прозорецот, поставете го прекинувачот на позиција „Додатоци на Excel“(ако има различна вредност) и кликнете на копчето „Оди...“.

5. Се отвора мал прозорец. До параметарот ставаме крлеж „Наоѓање решение“. Кликнете на копчето "ДОБРО".

Сега функцијата Наоѓање решениево Excel се активира, а неговите алатки се појавуваат на лентата.

Лекција:Наоѓање решение во Excel

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

Дозволете ни да ја опишеме примената на LSM на конкретен пример. Имаме два реда броеви xИ y, чија низа е прикажана на сликата подолу.

Оваа зависност најпрецизно може да се опише со функцијата:

Истовремено, познато е дека кога x=0 yисто така еднакви 0 . Затоа, оваа равенка може да се опише со зависноста y=nx.

Мора да го најдеме минималниот збир на квадрати на разликата.

Решение

Ајде да продолжиме со опис на директната примена на методот.

1. Лево од првата вредност xстави број 1 . Ова ќе биде приближна вредност на првата вредност на коефициентот n.

2. Десно од колоната yдодадете друга колона - nx. Во првата ќелија од оваа колона ја пишуваме формулата за множење на коефициентот nпо ќелија од првата променлива x. Во исто време, ја правиме врската до полето со коефициентот апсолутен, бидејќи оваа вредност нема да се промени. Кликнете на копчето Внесете.

3. Користејќи го маркерот за полнење, копирајте ја оваа формула во целиот опсег на табелата во колоната подолу.

4. Во посебна ќелија, пресметајте го збирот на разликите помеѓу квадратите на вредностите yИ nx. За да го направите ова, кликнете на копчето „Вметни функција“.

5. Во отворената „Волшебник за функции“бара влез „СУМКВАРНА“. Изберете го и притиснете го копчето "ДОБРО".

6. Се отвора прозорецот со аргументи. На терен „Array_x“ y. На терен "Array_y"внесете го опсегот на ќелиите на колоната nx. За да внесете вредности, едноставно ставете го курсорот во полето и изберете го соодветниот опсег на листот. Откако ќе влезете, кликнете на копчето "ДОБРО".

7. Одете на јазичето „Податоци“. На лентата во алатникот „Анализа“кликнете на копчето „Наоѓање решение“.

8. Се отвора прозорецот со параметри за оваа алатка. На терен „Оптимизирајте ја целната функција“означете ја адресата на ќелијата со формулата „СУМКВАРНА“. Во параметарот "Пред"не заборавајте да го поставите прекинувачот на позиција "минимум". На терен „Промена на клетките“означете ја адресата со вредноста на коефициентот n. Кликнете на копчето „Најди решение“.

9. Решението ќе се прикаже во ќелијата на коефициентот n. Оваа вредност ќе биде најмалиот квадрат на функцијата. Ако резултатот го задоволи корисникот, тогаш кликнете на копчето "ДОБРО"во дополнителен прозорец.

Како што можете да видите, примената на методот на најмали квадрати е прилично сложена математичка процедура. Тоа го покажавме на дело користејќи едноставен пример, но има многу посложени случаи. Сепак, алатките на Microsoft Excel се дизајнирани да ги поедностават пресметките што е можно повеќе.

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

Општи одредби

Како помал бројво апсолутна вредност, толку подобро се избира правата линија (2). Како карактеристика на точноста на избирање права линија (2), можеме да го земеме збирот на квадрати

Минималните услови за С ќе бидат

	(6)
	(7)

Равенките (6) и (7) може да се напишат на следниов начин:

	(8)
	(9)

Од равенките (8) и (9) лесно е да се најдат a и b од експерименталните вредности на xi и y i. Правата (2), дефинирана со равенките (8) и (9), се нарекува права добиена со методот на најмали квадрати (ова име нагласува дека збирот на квадратите S има минимум). Равенките (8) и (9), од кои се одредува права линија (2), се нарекуваат нормални равенки.

Можете да наведете едноставен и општ начин за составување нормални равенки. Користејќи ги експерименталните точки (1) и равенката (2), можеме да напишеме систем на равенки за a и b

Ајде да ги помножиме левата и десната страна на секоја од овие равенки со коефициентот на првата непозната a (т.е. со x 1, x 2, ..., x n) и да ги собереме добиените равенки, што резултира со првата нормална равенка (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.

Која наоѓа најширока примена во различни области на науката и практичната дејност. Ова може да биде физика, хемија, биологија, економија, социологија, психологија и така натаму, итн. По волја на судбината, честопати морам да се занимавам со економијата, и затоа денес ќе ви организирам патување во неверојатна земја наречена Економетрија=) ...Како не сакаш?! Таму е многу добро - само треба да се одлучите! ...Но, она што сигурно дефинитивно го сакате е да научите како да ги решавате проблемите метод на најмали квадрати. А особено вредните читатели ќе научат да ги решаваат не само точно, туку и МНОГУ БРЗО ;-) Но прво општа изјава за проблемот+ придружен пример:

Дозволете ни да проучуваме индикатори во одредена предметна област кои имаат квантитативен израз. Во исто време, постојат сите причини да се верува дека индикаторот зависи од индикаторот. Оваа претпоставка може да биде како научна хипотеза, и да се заснова на основниот здрав разум. Сепак, да ја оставиме науката настрана и да истражиме повеќе апетитни области - имено, продавници за храна. Да означиме со:

– малопродажна површина на самопослуга, кв.м.,
- годишен промет на самопослуга, милиони рубли.

Апсолутно е јасно дека колку е поголема површината на продавницата, толку во повеќето случаи нејзиниот промет ќе биде поголем.

Да претпоставиме дека по извршувањето на набљудувања/експерименти/пресметки/танци со тамбура имаме на располагање нумерички податоци:

Со намирниците, мислам дека сè е јасно: - ова е областа на првата продавница, - нејзиниот годишен промет, - областа на 2-та продавница, - нејзиниот годишен промет итн. Патем, воопшто не е неопходно да се има пристап до класифицирани материјали - прилично точна проценка на трговскиот промет може да се добие со помош на математичка статистика. Сепак, да не се расејуваме, курсот за комерцијална шпионажа е веќе платен =)

Табеларните податоци можат да се напишат и во форма на точки и да се прикажат во познатата форма Декартов систем .

Ајде да одговориме на едно важно прашање: Колку поени се потребни за квалитативна студија?

Колку е поголем, толку подобро. Минималниот прифатлив сет се состои од 5-6 поени. Дополнително, кога количината на податоци е мала, „аномалните“ резултати не можат да бидат вклучени во примерокот. Така, на пример, мала елитна продавница може да заработи поредоци со големина повеќе од „своите колеги“, а со тоа изобличува општа шема, што е она што треба да го најдете!

Многу едноставно кажано, треба да избереме функција, распоредкој поминува што поблиску до точките . Оваа функција се нарекува приближување (приближување - приближување)или теоретска функција . Општо земено, овде веднаш се појавува очигледен „претендент“ - полиномот висок степен, чиј график минува низ СИТЕ точки. Но, оваа опција е комплицирана и често едноставно неточна. (бидејќи графиконот ќе „јамка“ цело време и слабо ќе го одразува главниот тренд).

Така, бараната функција мора да биде прилично едноставна и во исто време соодветно да ја одразува зависноста. Како што може да претпоставите, еден од методите за наоѓање такви функции се нарекува метод на најмали квадрати. Прво, да ја разгледаме нејзината суштина во општи рамки. Дозволете некоја функција да ги приближи експерименталните податоци:


Како да се оцени точноста на ова приближување? Да ги пресметаме и разликите (отстапувањата) помеѓу експерименталните и функционалните вредности (го проучуваме цртежот). Првата мисла што ми паѓа на ум е да се процени колку е голема сумата, но проблемот е што разликите можат да бидат негативни (На пример, ) а отстапувањата како резултат на таквото сумирање ќе се поништат едни со други. Затоа, како проценка на точноста на приближувањето, се моли да се земе збирот модулиотстапувања:

или пропадна: (во случај некој да не знае: - ова е иконата за збир и - помошна променлива „контра“, која зема вредности од 1 до ).

Со приближување на експериментални точки со различни функции, ќе добиеме различни значења, и очигледно, каде што оваа сума е помала, таа функција е попрецизна.

Таков метод постои и се нарекува метод со најмал модул. Меѓутоа, во пракса стана многу пораспространета метод на најмал квадрат, во кој можните негативни вредности не се елиминираат од модулот, туку со квадратирање на отстапувањата:

, по што напорите се насочени кон избор на функција таква што збирот на квадратните отстапувања беше што е можно помал. Всушност, оттука доаѓа името на методот.

И сега се враќаме на нешто друго важна точка: како што е наведено погоре, избраната функција треба да биде прилично едноставна - но има и многу такви функции: линеарна , хиперболичен, експоненцијален, логаритамски, квадратни итн. И, се разбира, тука веднаш би сакал да го „намалам полето на активност“. Која класа на функции треба да ја изберам за истражување? Примитивна, но ефикасна техника:

– Најлесен начин е да се прикажат точките на цртежот и анализирајте ја нивната локација. Ако тие имаат тенденција да трчаат во права линија, тогаш треба да барате равенка на права со оптимални вредности и . Со други зборови, задачата е да се најдат ТАКВИ коефициенти така што збирот на квадратните отстапувања да биде најмал.

Ако точките се наоѓаат, на пример, заедно хипербола, тогаш очигледно е јасно дека линеарната функција ќе даде слаба апроксимација. Во овој случај, ние ги бараме „најповолните“ коефициенти за равенката на хиперболата – оние кои даваат минимален збир на квадрати .

Сега забележете дека и во двата случаи зборуваме функции на две променливи, чии аргументи се пребарувани параметри на зависност:

И во суштина треба да решиме стандарден проблем - најдете минимална функција од две променливи.

Ајде да се потсетиме на нашиот пример: да претпоставиме дека точките за складирање имаат тенденција да се наоѓаат во права линија и постојат сите причини да се верува дека линеарна зависностпромет од малопродажен простор. Да најдеме ТАКВИ коефициенти „а“ и „биди“ такви што збирот на квадратните отстапувања беше најмал. Сè е како и обично - прво Парцијални деривати од прв ред. Според правило за линеарностМожете да разликувате директно под иконата за сума:

Ако сакате да користите оваа информацијаза есеј или предмет - ќе бидам многу благодарен за врската во листата на извори; такви детални пресметки ќе најдете на неколку места:

Ајде да создадеме стандарден систем:

Секоја равенка ја намалуваме за „два“ и, дополнително, ги „разбиваме“ збировите:

Забелешка : независно анализирајте зошто „a“ и „be“ може да се извадат надвор од иконата за збир. Патем, формално ова може да се направи со сумата

Ајде да го преработиме системот во „применета“ форма:

по што почнува да се појавува алгоритмот за решавање на нашиот проблем:

Дали ги знаеме координатите на точките? Знаеме. Износите можеме да го најдеме? Лесно. Ајде да направиме наједноставно систем од две линеарни равенки во две непознати(„а“ и „биди“). Ние го решаваме системот, на пример, Крамеровиот метод, како резултат на што добиваме стационарна точка. Проверка доволен услов за екстрем, можеме да потврдиме дека во овој момент функцијата достигнува точно минимум. Проверката вклучува дополнителни пресметки и затоа ќе ја оставиме зад сцената (доколку е потребно, рамката што недостасува може да се види). Го извлекуваме конечниот заклучок:

Функција најдобриот начин (барем во споредба со која било друга линеарна функција)ги доближува експерименталните точки . Грубо кажано, неговиот график поминува што е можно поблиску до овие точки. Во традицијата економетријатасе нарекува и добиената приближна функција спарена линеарна регресивна равенка .

Проблемот што се разгледува има голем практично значење. Во нашиот пример ситуација, равенка. ви овозможува да предвидите каков трговски промет („Игрек“)продавницата ќе има на една или друга вредност на продажниот простор (едно или друго значење на „x“). Да, добиената прогноза ќе биде само прогноза, но во многу случаи ќе испадне доста точна.

Ќе анализирам само еден проблем со „вистински“ бројки, бидејќи нема тешкотии во него - сите пресметки се на ниво училишна наставна програма 7-8 одделение. Во 95 проценти од случаите, ќе биде побарано да најдете само линеарна функција, но на самиот крај на статијата ќе покажам дека не е потешко да се најдат равенките на оптималната хипербола, експоненцијалната и некои други функции.

Всушност, останува само да ги дистрибуирате ветените добра - за да научите да решавате такви примери не само точно, туку и брзо. Ние внимателно го проучуваме стандардот:

Задача

Како резултат на проучување на врската помеѓу два индикатори, добиени се следните парови на броеви:

Користејќи го методот на најмали квадрати, пронајдете ја линеарната функција која најдобро ја приближува емпириската (искусен)податоци. Направете цртеж на кој ќе се конструираат експериментални точки и график на приближната функција во Декартов правоаголен координатен систем . Најдете го збирот на квадратните отстапувања помеѓу емпириските и теоретските вредности. Дознајте дали функцијата би била подобра (од гледна точка на методот на најмали квадрати)доближи ги експерименталните точки.

Ве молиме имајте предвид дека значењата „x“ се природни и ова има карактеристично значајно значење, за кое ќе зборувам малку подоцна; но тие, се разбира, можат да бидат и фракционо. Покрај тоа, во зависност од содржината на одредена задача, и вредностите „Х“ и „игра“ можат да бидат целосно или делумно негативни. Па, ни беше дадена задача „без лице“ и ја започнуваме решение:

Како решение на системот ги наоѓаме коефициентите на оптималната функција:

За покомпактно снимање, променливата „контра“ може да се испушти, бидејќи веќе е јасно дека сумирањето се врши од 1 до .

Попогодно е да се пресметаат потребните износи во табеларна форма:


Пресметките може да се вршат на микрокалкулатор, но многу е подобро да се користи Excel - и побрзо и без грешки; погледнете кратко видео:

Така, го добиваме следново систем:

Овде можете да ја помножите втората равенка со 3 и одземете го 2-риот од првата равенка член по член. Но, ова е среќа - во пракса, системите често не се подарок, а во такви случаи тоа заштедува Крамеровиот метод:
, што значи дека системот има уникатно решение.

Ајде да провериме. Разбирам дека не сакате, но зошто да ги прескокнете грешките каде што тие апсолутно не можат да се пропуштат? Да го замениме пронајденото решение во левата страна на секоја равенка на системот:

Се добиваат десните страни на соодветните равенки, што значи дека системот е правилно решен.

Така саканата апроксимативна функција: – од сите линеарни функцииТаа е таа што најдобро ги приближува експерименталните податоци.

За разлика од директно зависност на прометот на продавницата од нејзината површина, пронајдената зависност е обратно (принцип „колку повеќе, толку помалку“), а овој факт веднаш се открива со негативното наклон. Функција ни кажува дека со зголемување на одреден индикатор за 1 единица, вредноста на зависниот индикатор се намалува просекза 0,65 единици. Како што велат, колку поскапува леќата, толку помалку се продава.

За да го нацртаме графикот на приближната функција, ги наоѓаме нејзините две вредности:

и извршете го цртежот:


Конструираната права линија се нарекува линија на тренд (имено, линеарна линија на тренд, т.е. во општ случај, трендот не е нужно права линија). На сите им е познат изразот „да се биде во тренд“ и мислам дека овој термин не бара дополнителни коментари.

Да го пресметаме збирот на квадратни отстапувања помеѓу емпириските и теоретските вредности. Геометриски, ова е збирот на квадратите на должините на сегментите „малина“ (од кои две се толку мали што не се ни видливи).

Ајде да ги сумираме пресметките во табела:


Повторно, тие можат да се направат рачно; за секој случај, ќе дадам пример за првата точка:

но многу поефективно е тоа да се направи на веќе познат начин:

Повторуваме уште еднаш: Кое е значењето на добиениот резултат?Од сите линеарни функции y функција индикаторот е најмал, односно во неговото семејство е најдобрата апроксимација. И тука, патем, конечното прашање на проблемот не е случајно: што ако предложената експоненцијална функција дали би било подобро да се доближат експерименталните точки?

Ајде да го најдеме соодветниот збир на квадратни отстапувања - за да ги разликувам, ќе ги означам со буквата „епсилон“. Техниката е сосема иста:


И повторно, за секој случај, пресметките за првата точка:

Во Excel ја користиме стандардната функција EXP (синтаксата може да се најде во помошта за Excel).

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

Но, тука треба да се забележи дека е „полошо“. уште не значи, што е проблемот. Сега изградив график на оваа експоненцијална функција - и исто така поминува блиску до точките - толку многу што без аналитичко истражување е тешко да се каже која функција е поточна.

Ова го завршува решението и се враќам на прашањето за природните вредности на аргументот. Во различни студии, обично економски или социолошки, природните „Х“ се користат за нумерирање месеци, години или други еднакви временски интервали. Размислете, на пример, на следниот проблем.

Методот на најмали квадрати (OLS) припаѓа на полето на регресивна анализа. Има многу намени бидејќи овозможува приближно претставување дадена функцијадруги се поедноставни. LSM може да биде исклучително корисен во обработката на набљудувањата и активно се користи за проценка на некои количини врз основа на резултатите од мерењата на други кои содржат случајни грешки. Во оваа статија, ќе научите како да ги имплементирате пресметките на најмалите квадрати во 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, треба да му дадете предност на онаа со најмала вредност на сума e i во сите точки што се разгледуваат. Сепак, не е сè толку едноставно, бидејќи заедно со позитивните отстапувања ќе има и негативни.

Проблемот може да се реши со помош на модули за отстапување или нивните квадрати. Последниот метод е најшироко користен. Се користи во многу области, вклучително и регресивна анализа (имплементирана во Excel со користење на две вградени функции) и одамна ја докажа својата ефикасност.

Метод на најмал квадрат

Excel, како што знаете, има вградена функција AutoSum која ви овозможува да ги пресметате вредностите на сите вредности лоцирани во избраниот опсег. Така, ништо нема да не спречи да ја пресметаме вредноста на изразот (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 има функција за пресметување на вредности со користење на најмали квадрати. Ја има следната форма: „TREND“ (познати Y вредности; познати X вредности; нови X вредности; константа). Ајде да ја примениме формулата за пресметување на OLS во Excel на нашата табела.

За да го направите ова, внесете го знакот „=“ во ќелијата во која треба да се прикаже резултатот од пресметката со методот на најмали квадрати во Excel и изберете ја функцијата „TREND“. Во прозорецот што се отвора, пополнете ги соодветните полиња, означувајќи:

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

Дополнително, формулата ја содржи логичката променлива „Const“. Ако внесете 1 во соодветното поле, тоа ќе значи дека треба да ги извршите пресметките, под претпоставка дека b = 0.

Ако треба да ја дознаете прогнозата за повеќе од една x вредност, тогаш откако ќе ја внесете формулата не треба да притиснете „Enter“, туку треба да ја напишете комбинацијата „Shift“ + „Control“ + „Enter“ на тастатурата.

Некои карактеристики

Анализата на регресија може да биде достапна дури и за кукли. Формулата Excel за предвидување на вредноста на низа непознати променливи - TREND - може да се користи дури и од оние кои никогаш не слушнале за најмали квадрати. Доволно е само да се знаат некои од карактеристиките на неговата работа. Особено:

• Ако го распоредите опсегот на познати вредности на променливата y во еден ред или колона, тогаш секој ред (колона) со познати вредности на x ќе биде перципиран од програмата како посебна променлива.
• Ако опсегот со познато x не е наведен во прозорецот TREND, тогаш кога се користи функцијата во Excel, програмата ќе го третира како низа составена од цели броеви, чиј број одговара на опсегот со дадените вредности на променлива y.
• За да излезе низа од „предвидени“ вредности, изразот за пресметување на трендот мора да се внесе како формула за низа.
• Ако новите вредности на x не се наведени, тогаш функцијата TREND ги смета за еднакви на познатите. Ако тие не се наведени, тогаш низата 1 се зема како аргумент; 2; 3; 4;…, што е пропорционално на опсегот со веќе дадени параметри y.
• Опсегот што ги содржи новите x вредности мора да има исти или повеќе редови или колони како опсегот што ги содржи дадените вредности y. Со други зборови, тој мора да биде пропорционален на независните променливи.
• Низа со познати x вредности може да содржи повеќе променливи. Меѓутоа, ако зборуваме само за еден, тогаш се бара опсезите со дадените вредности на x и y да бидат пропорционални. Во случај на неколку променливи, потребно е опсегот со дадените y вредности да се вклопи во една колона или еден ред.

Функција ПРЕДВИДУВАЊЕ

Анализата на регресија во Excel се спроведува со користење на неколку функции. Еден од нив се нарекува „ПРЕДИКЦИЈА“. Слично е на „TREND“, т.е. го дава резултатот од пресметките користејќи го методот на најмали квадрати. Сепак, само за еден X, за кој вредноста на Y е непозната.

Сега знаете формули во Excel за кукли кои ви дозволуваат да ја предвидите идната вредност на одреден индикатор според линеарен тренд.

Методот на најмали квадрати (LS) се заснова на минимизирање на збирот на квадратни отстапувања на избраната функција од податоците што се испитуваат. Во оваа статија ќе ги приближиме достапните податоци користејќи линеарна функцијаy = а x + б .

Метод на најмал квадрат(Англиски) Обичен Најмалку Квадрати , О.Л.С.) е еден од основните методи на регресивна анализа во смисла на проценка на непознати параметри регресивни моделиспоред податоците од примерокот.

Да го разгледаме приближувањето по функции кои зависат само од една променлива:

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

Забелешка: Во овој член се разгледуваат случаи на приближување со полином од 3 до 6 степен. Овде се разгледува приближувањето со тригонометриски полином.

Линеарна зависност

Ние сме заинтересирани за врската помеѓу 2 променливи XИ y. Постои претпоставка дека yзависи од Xспоред линеарниот закон y = секира + б. За да ги одреди параметрите на оваа врска, истражувачот направи набљудувања: за секоја вредност на x i, беше направено мерење на y i (види пример датотека). Според тоа, нека има 20 пара вредности (x i; y i).

Забелешка:Ако чекорот за промена е X е константна, потоа да се изгради расфрлаат парцелиможе да се користи, ако не, тогаш треба да го користите типот на графиконот Место .

Од дијаграмот е очигледно дека врската помеѓу променливите е блиску до линеарна. За да се разбере која од многуте прави линии „најправилно“ ја опишува врската помеѓу променливите, неопходно е да се одреди критериумот според кој линиите ќе се споредуваат.

Како таков критериум го користиме изразот:

Каде ŷ јас = а * x i + б ; n – број на парови вредности (во нашиот случај n=20)

Горенаведениот израз е збир на квадратните растојанија помеѓу набљудуваните вредности на y i и ŷ i и често се означува како SSE ( Збир на На квадрат Грешки (Остатоци), збир на квадратни грешки (остатоци)) .

Метод на најмал квадрате да се избере таква линија ŷ = секира + б, за што горенаведениот израз ја зема минималната вредност.

Забелешка:Секоја линија во дводимензионален простор е уникатно одредена од вредностите на 2 параметри: а (наклон) и б (смена).

Се верува дека колку е помал збирот на квадратни растојанија, толку подобро соодветната линија ги приближува достапните податоци и може дополнително да се користи за предвидување на вредностите на y од променливата x. Јасно е дека дури и ако во реалноста нема врска помеѓу променливите или врската е нелинеарна, тогаш OLS сепак ќе ја избере „најдобрата“ линија. Така, методот на најмали квадрати не кажува ништо за присуството на вистинска врска помеѓу променливите; методот едноставно ви овозможува да изберете такви параметри на функцијата а И б , за што горенаведениот израз е минимален.

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

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

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

= КОВАРИЈАНЦА.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Исто така да се пресмета параметарот А можете да ја користите формулата = TILT(C26:C45;B26:B45). За параметар б користете ја формулата = LEG(C26:C45;B26:B45) .

Конечно, функцијата LINEST() ви овозможува да ги пресметате двата параметри одеднаш. За да внесете формула LINEST(C26:C45;B26:B45)Треба да изберете 2 ќелии по ред и да кликнете CTRL + SHIFT + ENTER(види статија за). Вредноста ќе се врати во левата ќелија А , на десно - б .

Забелешка: За да избегнете мешање со влезот формули на низаќе треба дополнително да ја користите функцијата INDEX(). Формула = ИНДЕКС(LINEST(C26:C45,B26:B45),1)или само = LINEST(C26:C45;B26:B45)ќе го врати параметарот одговорен за наклонот на линијата, т.е. А . Формула = ИНДЕКС(LINEST(C26:C45,B26:B45),2)ќе го врати параметарот одговорен за пресекот на линијата со оската Y, т.е. б .

Откако ги пресметавме параметрите, дијаграм на расејувањеможете да ја нацртате соодветната линија.

Друг начин за исцртување права линија со користење на методот на најмали квадрати е алатката график Тренд линија. За да го направите ова, изберете го дијаграмот, изберете од менито Јазиче за распоред, В групна анализакликнете Тренд линија, тогаш Линеарно приближување .

Со штиклирање на полето „покажи равенка во дијаграм“ во полето за дијалог, можете да бидете сигурни дека параметрите пронајдени погоре се совпаѓаат со вредностите во дијаграмот.

Забелешка: За да се поклопат параметрите, типот на дијаграмот мора да биде . Поентата е дека кога се конструира дијаграм РаспоредВредностите на X-оската не можат да бидат специфицирани од корисникот (корисникот може да наведе само етикети кои не влијаат на локацијата на точките). Наместо X вредности, се користи низата 1; 2; 3; ... (за категориите за нумерирање). Затоа, ако градите линија на трендна типски дијаграм Распоред, тогаш наместо вистинските вредности на X ќе се користат вредностите на оваа низа, што ќе доведе до неточен резултат (освен, се разбира, реалните вредности X не се совпаѓаат со низата 1; 2; 3; ...).

Има многу апликации, бидејќи овозможува приближно претставување на дадена функција со други поедноставни. LSM може да биде исклучително корисен во обработката на набљудувањата и активно се користи за проценка на некои количини врз основа на резултатите од мерењата на други кои содржат случајни грешки. Во оваа статија, ќе научите како да ги имплементирате пресметките на најмалите квадрати во 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, треба да му дадете предност на онаа со најмала вредност на сума e i во сите точки што се разгледуваат. Сепак, не е сè толку едноставно, бидејќи заедно со позитивните отстапувања ќе има и негативни.

Проблемот може да се реши со помош на модули за отстапување или нивните квадрати. Последниот метод е најшироко користен. Се користи во многу области, вклучително и регресивна анализа (имплементирана во Excel со користење на две вградени функции) и одамна ја докажа својата ефикасност.

Метод на најмал квадрат

Excel, како што знаете, има вградена функција AutoSum која ви овозможува да ги пресметате вредностите на сите вредности лоцирани во избраниот опсег. Така, ништо нема да не спречи да ја пресметаме вредноста на изразот (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 има функција за пресметување на вредности со користење на најмали квадрати. Ја има следната форма: „TREND“ (познати Y вредности; познати X вредности; нови X вредности; константа). Ајде да ја примениме формулата за пресметување на OLS во Excel на нашата табела.

За да го направите ова, внесете го знакот „=“ во ќелијата во која треба да се прикаже резултатот од пресметката со методот на најмали квадрати во Excel и изберете ја функцијата „TREND“. Во прозорецот што се отвора, пополнете ги соодветните полиња, означувајќи:

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

Дополнително, формулата ја содржи логичката променлива „Const“. Ако внесете 1 во соодветното поле, тоа ќе значи дека треба да ги извршите пресметките, под претпоставка дека b = 0.

Ако треба да ја дознаете прогнозата за повеќе од една x вредност, тогаш откако ќе ја внесете формулата не треба да притиснете „Enter“, туку треба да ја напишете комбинацијата „Shift“ + „Control“ + „Enter“ на тастатурата.

Некои карактеристики

Анализата на регресија може да биде достапна дури и за кукли. Формулата Excel за предвидување на вредноста на низа непознати променливи - TREND - може да се користи дури и од оние кои никогаш не слушнале за најмали квадрати. Доволно е само да се знаат некои од карактеристиките на неговата работа. Особено:

• Ако го распоредите опсегот на познати вредности на променливата y во еден ред или колона, тогаш секој ред (колона) со познати вредности на x ќе биде перципиран од програмата како посебна променлива.
• Ако опсегот со познато x не е наведен во прозорецот TREND, тогаш кога се користи функцијата во Excel, програмата ќе го третира како низа составена од цели броеви, чиј број одговара на опсегот со дадените вредности на променлива y.
• За да излезе низа од „предвидени“ вредности, изразот за пресметување на трендот мора да се внесе како формула за низа.
• Ако новите вредности на x не се наведени, тогаш функцијата TREND ги смета за еднакви на познатите. Ако тие не се наведени, тогаш низата 1 се зема како аргумент; 2; 3; 4;…, што е пропорционално на опсегот со веќе наведените параметри y.
• Опсегот што ги содржи новите x вредности мора да има исти или повеќе редови или колони како опсегот што ги содржи дадените вредности y. Со други зборови, тој мора да биде пропорционален на независните променливи.
• Низа со познати x вредности може да содржи повеќе променливи. Меѓутоа, ако зборуваме само за еден, тогаш се бара опсезите со дадените вредности на x и y да бидат пропорционални. Во случај на неколку променливи, потребно е опсегот со дадените y вредности да се вклопи во една колона или еден ред.

Функција ПРЕДВИДУВАЊЕ

Имплементиран со користење на неколку функции. Еден од нив се нарекува „ПРЕДИКЦИЈА“. Слично е на „TREND“, т.е. го дава резултатот од пресметките користејќи го методот на најмали квадрати. Сепак, само за еден X, за кој вредноста на Y е непозната.

Сега знаете формули во Excel за кукли кои ви дозволуваат да ја предвидите идната вредност на одреден индикатор според линеарен тренд.