Løs et ligningssystem ved å bruke minste kvadraters metode i Excel. Bruke minste kvadraters metode i Excel. Noen få ord om riktigheten av de første dataene som brukes til prediksjon

Metode minste kvadrater er en matematisk prosedyre for å konstruere en lineær ligning som passer best til et sett med to tallserier. Hensikten med å bruke denne metoden er å minimere den totale kvadratfeilen. Excel har verktøy som kan hjelpe deg med å bruke denne metoden til dine beregninger. La oss finne ut hvordan dette gjøres.

· Bruke metoden i Excel

o Aktivering av "Solution Search"-tillegget

o Problemforhold

o Løsning

Bruker metoden i Excel

Minste kvadraters metode (LSM) er en matematisk beskrivelse av avhengigheten til en variabel av en annen. Den kan brukes til prognoser.

Aktiverer Finn løsning-tillegget

For å bruke MNC i Excel, må du aktivere tillegget "Finne en løsning", som er deaktivert som standard.

1. Gå til fanen "Fil".

2. Klikk på seksjonsnavnet "Alternativer".

3. Velg underseksjonen i vinduet som åpnes "Tillegg".

4. I blokken "Kontroll", som er plassert nederst i vinduet, sett bryteren i posisjon "Excel-tillegg"(hvis den har en annen verdi) og klikk på knappen "Gå...".

5. Et lite vindu åpnes. Vi setter et hake ved siden av parameteren "Finne en løsning". Klikk på knappen "OK".

Nå funksjonen Å finne en løsning i Excel er aktivert, og verktøyene vises på båndet.

Lekse: Finne en løsning i Excel

Betingelsene for problemet

La oss beskrive bruken av LSM på spesifikt eksempel. Vi har to rader med tall x Og y, hvis sekvens er vist på bildet nedenfor.

Denne avhengigheten kan beskrives mest nøyaktig av funksjonen:

Samtidig vet man at når x=0 y også like 0 . Derfor kan denne ligningen beskrives av avhengigheten y=nx.

Vi må finne minimumsummen av kvadrater av differansen.

Løsning

La oss gå videre til en beskrivelse av den direkte anvendelsen av metoden.

1. Til venstre for den første verdien x sette et tall 1 . Dette vil være en omtrentlig verdi av den første koeffisientverdien n.

2. Til høyre for kolonnen y legg til en kolonne til - nx. I den første cellen i denne kolonnen skriver vi formelen for å multiplisere koeffisienten n per celle i den første variabelen x. Samtidig gjør vi koblingen til feltet med koeffisienten absolutt, siden denne verdien ikke endres. Klikk på knappen Tast inn.

3. Bruk fyllmarkøren til å kopiere denne formelen til hele tabellen i kolonnen nedenfor.

4. I en egen celle regner du ut summen av forskjellene mellom kvadratene til verdiene y Og nx. For å gjøre dette, klikk på knappen "Sett inn funksjon".

5. I det åpnede "Funksjonsveiviser" ser etter en oppføring "SUMMKVARNA". Velg den og trykk på knappen "OK".

6. Argumentvinduet åpnes. I felt "Array_x" y. I felt "Array_y" angi området for kolonneceller nx. For å legge inn verdier, plasser ganske enkelt markøren i feltet og velg det tilsvarende området på arket. Etter inntasting klikker du på knappen "OK".

7. Gå til fanen "Data". På båndet i verktøykassen "Analyse" klikk på knappen "Finne en løsning".

8. Parametervinduet for dette verktøyet åpnes. I felt "Optimaliser objektivfunksjonen" angi adressen til cellen med formelen "SUMMKVARNA". I parameteren "Før" pass på å sette bryteren til posisjon "Minimum". I felt "Endre celler" angi adressen med koeffisientverdien n. Klikk på knappen "Finn en løsning".

9. Løsningen vil vises i koeffisientcellen n. Denne verdien vil være det minste kvadratet av funksjonen. Hvis resultatet tilfredsstiller brukeren, klikker du på knappen "OK" i et ekstra vindu.

Som du kan se, er bruken av minste kvadraters metode en ganske kompleks matematisk prosedyre. Vi viste det i aksjon ved å bruke et enkelt eksempel, men det er mye mer komplekse saker. Microsoft Excel-verktøy er imidlertid laget for å forenkle beregningene så mye som mulig.

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

Generelle bestemmelser

Hvordan mindre antall i absolutt verdi, jo bedre er den rette linjen (2) valgt. Som en karakteristikk for nøyaktigheten av å velge en rett linje (2), kan vi ta summen av kvadrater

Minimumsbetingelsene for S vil være

	(6)
	(7)

Ligningene (6) og (7) kan skrives som følger:

	(8)
	(9)

Fra ligningene (8) og (9) er det lett å finne a og b fra de eksperimentelle verdiene til xi og y i. Linje (2), definert av ligningene (8) og (9), kalles en linje oppnådd ved minste kvadraters metode (dette navnet understreker at summen av kvadratene S har et minimum). Ligningene (8) og (9), som rett linje (2) bestemmes fra, kalles normalligninger.

Du kan angi en enkel og generell måte å komponere normale ligninger på. Ved å bruke eksperimentelle punkter (1) og ligning (2) kan vi skrive et likningssystem for a og b

La oss multiplisere venstre og høyre side av hver av disse ligningene med koeffisienten til den første ukjente a (dvs. med x 1, x 2, ..., x n) og legge til de resulterende ligningene, noe som resulterer i den første normalligningen (8) .

La oss multiplisere venstre og høyre side av hver av disse ligningene med koeffisienten til den andre ukjente b, dvs. med 1, og legg til de resulterende ligningene, er resultatet den andre normalligningen (9).

Denne metoden for å oppnå normale ligninger er generell: den egner seg for eksempel for funksjonen

det er en konstant verdi og den må bestemmes fra eksperimentelle data (1).

Ligningssystemet for k kan skrives:

Finn rett linje (2) ved å bruke minste kvadraters metode.

Løsning. Vi finner:

Xi=21, yi=46,3, xi2=91, xiyi=179,1.

Vi skriver ligningene (8) og (9)91a+21b=179.1,

21a+6b=46.3, herfra finner vi
a=0,98 b=4,3.

Som finner den bredeste anvendelsen innen ulike felt av vitenskap og praktisk aktivitet. Dette kan være fysikk, kjemi, biologi, økonomi, sosiologi, psykologi og så videre og så videre. Etter skjebnens vilje må jeg ofte forholde meg til økonomien, og derfor vil jeg i dag arrangere for deg en tur til et fantastisk land kalt Økonometri=) ...Hvordan kan du ikke ha det?! Det er veldig bra der – du må bare bestemme deg! ...Men det du sannsynligvis vil er å lære å løse problemer minste kvadraters metode. Og spesielt flittige lesere vil lære å løse dem ikke bare nøyaktig, men også VELDIG RASK ;-) Men først generell problemstilling+ medfølgende eksempel:

La oss studere indikatorer i et bestemt fagområde som har et kvantitativt uttrykk. Samtidig er det all grunn til å tro at indikatoren er avhengig av indikatoren. Denne antakelsen kan være slik vitenskapelig hypotese, og være basert på grunnleggende sunn fornuft. La oss imidlertid legge vitenskapen til side og utforske mer appetittvekkende områder – nemlig dagligvarebutikker. La oss betegne med:

– butikkareal til en dagligvarebutikk, kvm,
– årlig omsetning for en dagligvarebutikk, millioner rubler.

Det er helt klart at jo større butikkarealet er, desto større blir i de fleste tilfeller omsetningen.

Anta at vi etter å ha utført observasjoner/eksperimenter/beregninger/danser med en tamburin har numeriske data til rådighet:

Med dagligvarebutikker tror jeg alt er klart: - dette er arealet til den første butikken, - dens årlige omsetning, - arealet til den andre butikken, - dens årlige omsetning, etc. Det er for øvrig slett ikke nødvendig å ha tilgang til klassifisert materiale – en ganske nøyaktig vurdering av handelsomsetningen kan fås v.h.a. matematisk statistikk. La oss imidlertid ikke bli distrahert, det kommersielle spionasjekurset er allerede betalt =)

Tabelldata kan også skrives i form av punkter og avbildes i kjent form Kartesisk system .

La oss svare på et viktig spørsmål: Hvor mange poeng trengs for en kvalitativ studie?

Jo større jo bedre. Minimum akseptabelt sett består av 5-6 poeng. I tillegg, når datamengden er liten, kan ikke "anomale" resultater inkluderes i utvalget. Så for eksempel kan en liten elitebutikk tjene størrelsesordener mer enn «kollegene», og dermed forvrenge generelt mønster, som er det du trenger å finne!

For å si det veldig enkelt, må vi velge en funksjon, rute som passerer så nærme punktene som mulig . Denne funksjonen kalles tilnærmet (tilnærming - tilnærming) eller teoretisk funksjon . Generelt sett dukker det umiddelbart opp en åpenbar "utfordrer" her - polynomet høy grad, hvis graf går gjennom ALLE punkter. Men dette alternativet er komplisert og ofte rett og slett feil. (siden grafen vil "løkke" hele tiden og reflekterer hovedtrenden dårlig).

Dermed må den søkte funksjonen være ganske enkel og samtidig reflektere avhengigheten tilstrekkelig. Som du kanskje gjetter, kalles en av metodene for å finne slike funksjoner minste kvadraters metode. Først, la oss se på essensen i generelle termer. La noen funksjoner tilnærme eksperimentelle data:


Hvordan evaluere nøyaktigheten av denne tilnærmingen? La oss også beregne forskjellene (avvikene) mellom de eksperimentelle og funksjonelle verdiene (vi studerer tegningen). Den første tanken som dukker opp er å anslå hvor stor summen er, men problemet er at forskjellene kan være negative (For eksempel, ) og avvik som følge av slik summering vil oppheve hverandre. Derfor, som et estimat for nøyaktigheten av tilnærmingen, ber det om å ta summen moduler avvik:

eller kollapset: (i tilfelle noen ikke vet: – dette er sumikonet, og – en hjelpevariabel for "teller", som tar verdier fra 1 til ).

Ved å tilnærme eksperimentelle punkter med ulike funksjoner vil vi oppnå forskjellige betydninger, og åpenbart, der dette beløpet er mindre, er denne funksjonen mer nøyaktig.

En slik metode finnes og den kalles minste modul metoden. Men i praksis har det blitt mye mer utbredt minste kvadrat-metoden, der mulige negative verdier elimineres ikke av modulen, men ved å kvadrere avvikene:

, hvoretter innsatsen er rettet mot å velge en funksjon slik at summen av kvadrerte avvik var så liten som mulig. Egentlig er det her navnet på metoden kommer fra.

Og nå skal vi tilbake til noe annet viktig poeng: som nevnt ovenfor, bør den valgte funksjonen være ganske enkel - men det er også mange slike funksjoner: lineær , hyperbolsk, eksponentiell, logaritmisk, kvadratisk etc. Og her vil jeg selvfølgelig umiddelbart "redusere aktivitetsfeltet." Hvilken klasse funksjoner bør jeg velge for forskning? En primitiv, men effektiv teknikk:

– Den enkleste måten er å skildre punkter på tegningen og analyser deres plassering. Hvis de har en tendens til å løpe i en rett linje, bør du se etter ligning av en linje med optimale verdier og . Oppgaven er med andre ord å finne SLIKE koeffisienter slik at summen av kvadrerte avvik blir minst.

Hvis punktene er plassert for eksempel langs overdrivelse, så er det åpenbart klart at den lineære funksjonen vil gi en dårlig tilnærming. I dette tilfellet ser vi etter de mest "gunstige" koeffisientene for hyperbelligningen – de som gir minimumsummen av kvadrater .

Legg nå merke til at i begge tilfeller er det snakk om funksjoner til to variabler, hvis argumenter er søkte avhengighetsparametere:

Og egentlig må vi løse et standardproblem - finn minimumsfunksjon av to variabler.

La oss huske eksempelet vårt: anta at "butikk"-punkter har en tendens til å være plassert i en rett linje, og det er all grunn til å tro at lineær avhengighet omsetning fra butikklokaler. La oss finne SLIKE koeffisientene "a" og "være" slik at summen av kvadrerte avvik var den minste. Alt er som vanlig - først 1. ordens partielle derivater. I følge linearitetsregel Du kan skille rett under sum-ikonet:

Hvis du vil bruke denne informasjonen for et essay eller kurs - jeg vil være veldig takknemlig for lenken i kildelisten; du vil finne slike detaljerte beregninger noen få steder:

La oss lage et standard system:

Vi reduserer hver ligning med "to", og i tillegg "deler vi opp" summene:

Merk : analyser uavhengig hvorfor "a" og "be" kan tas ut utenfor sumikonet. Forresten, formelt sett kan dette gjøres med summen

La oss omskrive systemet i "anvendt" form:

hvoretter algoritmen for å løse problemet vårt begynner å dukke opp:

Kjenner vi koordinatene til punktene? Vi vet. Beløp kan vi finne den? Enkelt. La oss gjøre det enkleste system av to lineære ligninger i to ukjente("a" og "be"). Vi løser systemet f.eks. Cramers metode, som et resultat av dette får vi et stasjonært punkt. Sjekker tilstrekkelig tilstand for et ekstremum, kan vi bekrefte at funksjonen på dette tidspunktet når nøyaktig minimum. Sjekken innebærer tilleggsberegninger og derfor vil vi legge den bak kulissene (om nødvendig kan den manglende rammen sees). Vi trekker den endelige konklusjonen:

Funksjon den beste måten (i det minste sammenlignet med en hvilken som helst annen lineær funksjon) bringer eksperimentelle poeng nærmere . Grovt sett passerer grafen så nært disse punktene som mulig. I tradisjon økonometri den resulterende tilnærmelsesfunksjonen kalles også paret lineær regresjonsligning .

Problemet som vurderes har et stort praktisk betydning. I vår eksempelsituasjon, Eq. lar deg forutsi hvilken handelsomsetning ("Igrek") butikken vil ha til en eller annen verdi av salgsarealet (en eller annen betydning av "x"). Ja, den resulterende prognosen vil bare være en prognose, men i mange tilfeller vil den vise seg å være ganske nøyaktig.

Jeg vil analysere bare ett problem med "ekte" tall, siden det ikke er noen problemer med det - alle beregninger er på nivået skolepensum 7-8 klassetrinn. I 95 prosent av tilfellene vil du bli bedt om å finne bare en lineær funksjon, men helt på slutten av artikkelen vil jeg vise at det ikke er vanskeligere å finne likningene til den optimale hyperbelen, eksponentialfunksjonen og noen andre funksjoner.

Det gjenstår faktisk bare å distribuere de lovede godsakene – slik at du kan lære å løse slike eksempler ikke bare nøyaktig, men også raskt. Vi studerer standarden nøye:

Oppgave

Som et resultat av å studere forholdet mellom to indikatorer, ble følgende tallpar oppnådd:

Bruk minste kvadraters metode, finn den lineære funksjonen som best tilnærmer empirien (opplevde) data. Lag en tegning som du kan konstruere eksperimentelle punkter på og en graf over den tilnærmede funksjonen i et kartesisk rektangulært koordinatsystem . Finn summen av kvadrerte avvik mellom empiriske og teoretiske verdier. Finn ut om funksjonen ville vært bedre (fra minste kvadraters synspunkt) bringe eksperimentelle poeng nærmere.

Vær oppmerksom på at "x"-betydningene er naturlige, og dette har en karakteristisk meningsfull betydning, som jeg vil snakke om litt senere; men de kan selvfølgelig også være brøkdeler. I tillegg, avhengig av innholdet i en bestemt oppgave, kan både "X" og "game" verdier være helt eller delvis negative. Vel, vi har fått en "ansiktsløs" oppgave, og vi begynner på den løsning:

Vi finner koeffisientene til den optimale funksjonen som en løsning på systemet:

For et mer kompakt opptak kan "teller"-variabelen utelates, siden det allerede er klart at summeringen utføres fra 1 til .

Det er mer praktisk å beregne de nødvendige beløpene i tabellform:


Beregninger kan utføres på en mikrokalkulator, men det er mye bedre å bruke Excel - både raskere og uten feil; se en kort video:

Dermed får vi følgende system:

Her kan du gange den andre ligningen med 3 og trekk 2. fra 1. ligning ledd for ledd. Men dette er flaks - i praksis er systemer ofte ikke en gave, og i slike tilfeller sparer det Cramers metode:
, som betyr at systemet har en unik løsning.

La oss sjekke. Jeg forstår at du ikke vil, men hvorfor hoppe over feil der de absolutt ikke kan gå glipp av? La oss erstatte den funnet løsningen på venstre side av hver likning av systemet:

Høyresidene av de tilsvarende ligningene oppnås, noe som betyr at systemet er løst riktig.

Dermed vil den ønskede tilnærmelsesfunksjonen: – fra alle lineære funksjoner Det er hun som best tilnærmer de eksperimentelle dataene.

I motsetning til rett avhengighet av butikkens omsetning på sitt område, er den funnet avhengighet omvendt (prinsippet "jo mer, jo mindre"), og dette faktum avsløres umiddelbart av det negative skråningen. Funksjon forteller oss at med en økning i en viss indikator med 1 enhet, synker verdien av den avhengige indikatoren gjennomsnitt med 0,65 enheter. Som de sier, jo høyere pris på bokhvete, jo mindre selges den.

For å plotte grafen til den tilnærmede funksjonen finner vi dens to verdier:

og utfør tegningen:


Den konstruerte rette linjen kalles trendlinje (nemlig en lineær trendlinje, dvs. i det generelle tilfellet er en trend ikke nødvendigvis en rett linje). Alle er kjent med uttrykket "å være i trend", og jeg tror at dette begrepet ikke trenger ytterligere kommentarer.

La oss beregne summen av kvadrerte avvik mellom empiriske og teoretiske verdier. Geometrisk er dette summen av kvadratene av lengdene til "bringebær"-segmentene (hvorav to er så små at de ikke engang er synlige).

La oss oppsummere beregningene i en tabell:


Igjen, de kan gjøres manuelt; i tilfelle vil jeg gi et eksempel for det første punktet:

men det er mye mer effektivt å gjøre det på den allerede kjente måten:

Vi gjentar nok en gang: Hva er meningen med det oppnådde resultatet? Fra alle lineære funksjoner y funksjon indikatoren er den minste, det vil si i sin familie er den den beste tilnærmingen. Og her, forresten, er det endelige spørsmålet om problemet ikke tilfeldig: hva om den foreslåtte eksponentielle funksjonen ville det vært bedre å bringe de eksperimentelle punktene nærmere?

La oss finne den tilsvarende summen av kvadrerte avvik - for å skille, vil jeg betegne dem med bokstaven "epsilon". Teknikken er nøyaktig den samme:


Og igjen, for sikkerhets skyld, beregningene for 1. poeng:

I Excel bruker vi standardfunksjonen EXP (syntaks finner du i Excel Hjelp).

Konklusjon: , som betyr at eksponentialfunksjonen tilnærmer forsøkspunktene dårligere enn en rett linje .

Men her bør det bemerkes at "verre" er betyr ikke ennå, hva er galt. Nå har jeg bygget en graf av denne eksponentialfunksjonen – og den passerer også nærme punktene – så mye at uten analytisk forskning er det vanskelig å si hvilken funksjon som er mer nøyaktig.

Dette avslutter løsningen, og jeg kommer tilbake til spørsmålet om naturverdiene til argumentet. I ulike studier, vanligvis økonomiske eller sosiologiske, brukes naturlige "X'er" til å telle måneder, år eller andre like tidsintervaller. Tenk for eksempel på følgende problem.

Metoden for minste kvadraters (OLS) tilhører feltet regresjonsanalyse. Den har mange bruksområder da den tillater omtrentlig representasjon gitt funksjon andre er enklere. LSM kan være ekstremt nyttig for å behandle observasjoner, og det brukes aktivt til å estimere noen mengder basert på resultatene av målinger av andre som inneholder tilfeldige feil. I denne artikkelen lærer du hvordan du implementerer minste kvadraters beregninger i Excel.

Forklaring av problemet ved hjelp av et spesifikt eksempel

Anta at det er to indikatorer X og Y. Ytterligere avhenger Y av X. Siden OLS interesserer oss fra et synspunkt om regresjonsanalyse (i Excel implementeres metodene ved hjelp av innebygde funksjoner), bør vi umiddelbart gå videre til å vurdere en spesifikt problem.

Så la X være butikklokalet til en dagligvarebutikk, målt i kvadratmeter, og Y er den årlige omsetningen, bestemt i millioner av rubler.

Det kreves å lage en prognose for hvilken omsetning (Y) butikken vil ha dersom den har et eller annet butikkareal. Det er klart at funksjonen Y = f (X) øker, siden hypermarkedet selger flere varer enn boden.

Noen få ord om riktigheten av de første dataene som brukes til prediksjon

La oss si at vi har en tabell bygget ved hjelp av data for n butikker.

I følge matematisk statistikk, vil resultatene være mer eller mindre korrekte dersom data på minst 5-6 objekter undersøkes. I tillegg kan ikke "anomale" resultater brukes. Spesielt kan en liten elitebutikk ha en omsetning som er flere ganger større enn omsetningen til store utsalgssteder i "masmarket"-klassen.

Essensen av metoden

Tabelldataene kan avbildes på et kartesisk plan i form av punktene M 1 (x 1, y 1), ... M n (x n, y n). Nå vil løsningen på problemet reduseres til valget av en tilnærmet funksjon y = f (x), som har en graf som passerer så nært som mulig punktene M 1, M 2, .. M n.

Selvfølgelig kan du bruke et polynom i høy grad, men dette alternativet er ikke bare vanskelig å implementere, men også rett og slett feil, siden det ikke vil gjenspeile hovedtrenden som må oppdages. Den mest fornuftige løsningen er å søke etter den rette linjen y = ax + b, som best tilnærmer de eksperimentelle dataene, eller mer presist koeffisientene a og b.

Nøyaktighetsvurdering

Med enhver tilnærming er det spesielt viktig å vurdere nøyaktigheten. La oss betegne med e i forskjellen (avvik) mellom funksjonelle og eksperimentelle verdier for punkt x i, dvs. e i = y i - f (x i).

For å vurdere nøyaktigheten til tilnærmingen kan du selvsagt bruke summen av avvik, dvs. når du velger en rett linje for en omtrentlig representasjon av avhengigheten til X av Y, bør du gi preferanse til den med den minste verdien av sum e i på alle punkter under vurdering. Imidlertid er ikke alt så enkelt, siden det sammen med positive avvik også vil være negative.

Problemet kan løses ved å bruke avviksmoduler eller deres kvadrater. Den siste metoden er den mest brukte. Den brukes på mange områder, inkludert regresjonsanalyse (implementert i Excel ved hjelp av to innebygde funksjoner), og har lenge bevist sin effektivitet.

Minste kvadratiske metode

Excel, som du vet, har en innebygd AutoSum-funksjon som lar deg beregne verdiene til alle verdier som er plassert i det valgte området. Dermed vil ingenting hindre oss i å beregne verdien av uttrykket (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

I matematisk notasjon ser dette slik ut:

Siden beslutningen opprinnelig ble tatt om å tilnærme ved hjelp av en rett linje, har vi:

Oppgaven med å finne den rette linjen som best beskriver den spesifikke avhengigheten av mengdene X og Y kommer ned til å beregne minimum av en funksjon av to variabler:

For å gjøre dette, må du likestille de partielle deriverte med hensyn til de nye variablene a og b til null, og løse et primitivt system som består av to ligninger med 2 ukjente av formen:

Etter noen enkle transformasjoner, inkludert divisjon med 2 og manipulering av summer, får vi:

Ved å løse det, for eksempel ved å bruke Cramers metode, får vi et stasjonært punkt med visse koeffisienter a * og b *. Dette er minimum, dvs. for å forutsi hvilken omsetning en butikk vil ha for et bestemt område, er den rette linjen y = a * x + b * egnet, som er regresjonsmodell for det aktuelle eksemplet. Selvfølgelig vil det ikke tillate deg å finne det nøyaktige resultatet, men det vil hjelpe deg med å få en ide om hvorvidt det vil lønne seg å kjøpe et bestemt område på butikkkreditt.

Hvordan implementere minste kvadrater i Excel

Excel har en funksjon for å beregne verdier ved å bruke minste kvadrater. Den har følgende form: "TREND" (kjente Y-verdier; kjente X-verdier; nye X-verdier; konstant). La oss bruke formelen for å beregne OLS i Excel på tabellen vår.

For å gjøre dette, skriv inn "="-tegnet i cellen der resultatet av beregningen ved hjelp av minste kvadraters metode i Excel skal vises og velg "TREND" -funksjonen. Fyll ut de aktuelle feltene i vinduet som åpnes, og uthev:

• rekke kjente verdier for Y (in i dette tilfellet data for handelsomsetning);
• rekkevidde x 1 , …x n , dvs. størrelsen på butikklokaler;
• både kjente og ukjente verdier av x, som du trenger for å finne ut størrelsen på omsetningen (for informasjon om deres plassering på regnearket, se nedenfor).

I tillegg inneholder formelen den logiske variabelen "Const". Hvis du skriver inn 1 i det tilsvarende feltet, vil dette bety at du skal utføre beregningene, forutsatt at b = 0.

Hvis du trenger å finne ut prognosen for mer enn én x-verdi, bør du ikke trykke "Enter" etter å ha skrevet inn formelen, men du må skrive kombinasjonen "Shift" + "Control" + "Enter" på tastaturet.

Noen funksjoner

Regresjonsanalyse kan være tilgjengelig selv for dummies. Excel-formelen for å forutsi verdien av en rekke ukjente variabler – TREND – kan brukes selv av de som aldri har hørt om minste kvadrater. Det er nok bare å kjenne noen av funksjonene i arbeidet. Spesielt:

• Hvis du ordner rekkevidden av kjente verdier av variabelen y i en rad eller kolonne, vil hver rad (kolonne) med kjente verdier av x bli oppfattet av programmet som en separat variabel.
• Hvis et område med kjent x ikke er spesifisert i TREND-vinduet, vil programmet, når du bruker funksjonen i Excel, behandle det som en matrise bestående av heltall, hvor nummeret tilsvarer området med de gitte verdiene til variabel y.
• For å sende ut en matrise med "forutsagte" verdier, må uttrykket for beregning av trenden angis som en matriseformel.
• Hvis nye verdier av x ikke er spesifisert, anser TREND-funksjonen dem som lik de kjente. Hvis de ikke er spesifisert, tas matrise 1 som et argument; 2; 3; 4;..., som er i samsvar med rekkevidden med allerede gitte parametere y.
• Området som inneholder de nye x-verdiene må ha samme eller flere rader eller kolonner som området som inneholder de gitte y-verdiene. Den må med andre ord være proporsjonal med de uavhengige variablene.
• En matrise med kjente x-verdier kan inneholde flere variabler. Imidlertid, hvis vi snakker om bare én, kreves det at områdene med de gitte verdiene av x og y er proporsjonale. Ved flere variabler er det nødvendig at området med de gitte y-verdiene passer i en kolonne eller en rad.

PREDIKTION funksjon

Regresjonsanalyse i Excel implementeres ved hjelp av flere funksjoner. En av dem heter "PREDICTION". Det ligner på "TREND", det vil si at det gir resultatet av beregninger med minste kvadraters metode. Imidlertid bare for en X, der verdien av Y er ukjent.

Nå kjenner du formler i Excel for dummies som lar deg forutsi den fremtidige verdien av en bestemt indikator i henhold til en lineær trend.

Minste kvadraters metode (LS) er basert på å minimere summen av kvadrerte avvik for den valgte funksjonen fra dataene som studeres. I denne artikkelen vil vi tilnærme de tilgjengelige dataene ved hjelp av en lineær funksjony = en x + b .

Minste kvadratiske metode(Engelsk) Vanlig Minst Firkanter , O.L.S.) er en av de grunnleggende metodene for regresjonsanalyse når det gjelder å estimere ukjente parametere regresjonsmodeller i henhold til prøvedata.

La oss vurdere tilnærming med funksjoner som bare avhenger av én variabel:

• Lineær: y=ax+b (denne artikkelen)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Merk: Tilfeller av tilnærming med et polynom fra 3. til 6. grad vurderes i denne artikkelen. Tilnærming med et trigonometrisk polynom vurderes her.

Lineær avhengighet

Vi er interessert i sammenhengen mellom 2 variabler X Og y. Det er en antagelse om det y kommer an på X i henhold til lineær lov y = øks + b. For å bestemme parametrene for dette forholdet, gjorde forskeren observasjoner: for hver verdi av x i ble det foretatt en måling av y i (se eksempelfil). La det følgelig være 20 par med verdier (x i; y i).

Merk: Hvis endringstrinnet er X er konstant, så å bygge spredeplott kan brukes, hvis ikke, må du bruke diagramtypen Få øye på .

Det er tydelig fra diagrammet at forholdet mellom variablene er nær lineært. For å forstå hvilken av de mange rette linjene som mest "riktig" beskriver forholdet mellom variabler, er det nødvendig å bestemme kriteriet som linjene skal sammenlignes med.

Som et slikt kriterium bruker vi uttrykket:

Hvor ŷ Jeg = en * x i + b ; n – antall verdipar (i vårt tilfelle n=20)

Uttrykket ovenfor er summen av kvadrerte avstander mellom de observerte verdiene av y i og ŷ i og er ofte betegnet som SSE ( Sum av Kvadrat Feil (Rester), summen av kvadrerte feil (rester)) .

Minste kvadratiske metode er å velge en slik linje ŷ = øks + b, som uttrykket ovenfor tar minimumsverdien for.

Merk: Enhver linje i todimensjonalt rom er unikt bestemt av verdiene til 2 parametere: en (helling) og b (skifte).

Det antas at jo mindre summen av kvadrerte avstander er, desto bedre tilnærmer den tilsvarende linjen de tilgjengelige dataene og kan videre brukes til å forutsi verdiene til y fra x-variabelen. Det er klart at selv om det i virkeligheten ikke er noen sammenheng mellom variablene eller forholdet er ikke-lineært, vil OLS fortsatt velge den "beste" linjen. Dermed sier minste kvadraters metode ikke noe om tilstedeværelsen av et reelt forhold mellom variabler; metoden lar deg ganske enkelt velge slike funksjonsparametere en Og b , som uttrykket ovenfor er minimalt for.

Ved å utføre ikke veldig komplekse matematiske operasjoner (for mer detaljer, se), kan du beregne parametrene en Og b :

Som man kan se fra formelen, parameteren en representerer forholdet mellom kovarians og , derfor i MS EXCEL for å beregne parameteren EN kan bli brukt følgende formler(cm. Eksempelfil på lineært ark):

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

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

Også for å beregne parameteren EN du kan bruke formelen = TILT(C26:C45;B26:B45). For parameter b bruk formelen = LEG(C26:C45;B26:B45) .

Til slutt lar LINEST()-funksjonen deg beregne begge parameterne samtidig. For å angi en formel LINJE(C26:C45;B26:B45) Du må velge 2 celler på rad og klikke CTRL + SKIFTE + TAST INN(se artikkel om). Verdien vil bli returnert i venstre celle EN , til høyre - b .

Merk: For å unngå å rote med inngangen matriseformler du må i tillegg bruke INDEX()-funksjonen. Formel = INDEKS(LINJE(C26:C45;B26:B45);1) eller bare = LINJE(C26:C45;B26:B45) vil returnere parameteren som er ansvarlig for helningen på linjen, dvs. EN . Formel = INDEKS(LINJE(C26:C45;B26:B45);2) vil returnere parameteren som er ansvarlig for skjæringen av linjen med Y-aksen, dvs. b .

Etter å ha beregnet parametrene, punktdiagram du kan tegne den tilsvarende linjen.

En annen måte å plotte en rett linje ved å bruke minste kvadraters metode er grafverktøyet Trendlinje. For å gjøre dette, velg diagrammet, velg fra menyen Fanen Layout, V gruppeanalyse klikk Trendlinje, deretter Lineær tilnærming .

Ved å krysse av for "vis ligning i diagram"-boksen i dialogboksen, kan du forsikre deg om at parametrene ovenfor samsvarer med verdiene i diagrammet.

Merk: For at parameterne skal samsvare, må diagramtypen være . Poenget er at når man konstruerer et diagram Rute X-akseverdier kan ikke spesifiseres av brukeren (brukeren kan bare spesifisere etiketter som ikke påvirker plasseringen av punktene). I stedet for X-verdier brukes sekvensen 1; 2; 3; ... (for nummereringskategorier). Derfor, hvis du bygger trendlinje på et typediagram Rute, i stedet for de faktiske verdiene til X vil verdiene til denne sekvensen bli brukt, noe som vil føre til et feil resultat (med mindre, selvfølgelig, faktiske verdier X samsvarer ikke med sekvens 1; 2; 3; ...).

Den har mange applikasjoner, da den tillater en omtrentlig representasjon av en gitt funksjon med andre enklere. LSM kan være ekstremt nyttig for å behandle observasjoner, og det brukes aktivt til å estimere noen mengder basert på resultatene av målinger av andre som inneholder tilfeldige feil. I denne artikkelen lærer du hvordan du implementerer minste kvadraters beregninger i Excel.

Forklaring av problemet ved hjelp av et spesifikt eksempel

Anta at det er to indikatorer X og Y. Ytterligere avhenger Y av X. Siden OLS interesserer oss fra et synspunkt om regresjonsanalyse (i Excel implementeres metodene ved hjelp av innebygde funksjoner), bør vi umiddelbart gå videre til å vurdere en spesifikt problem.

Så la X være butikklokalet til en dagligvarebutikk, målt i kvadratmeter, og Y være den årlige omsetningen, målt i millioner av rubler.

Det kreves å lage en prognose for hvilken omsetning (Y) butikken vil ha dersom den har et eller annet butikkareal. Det er klart at funksjonen Y = f (X) øker, siden hypermarkedet selger flere varer enn boden.

Noen få ord om riktigheten av de første dataene som brukes til prediksjon

La oss si at vi har en tabell bygget ved hjelp av data for n butikker.

I følge matematisk statistikk vil resultatene være mer eller mindre korrekte dersom data på minst 5-6 objekter undersøkes. I tillegg kan ikke "anomale" resultater brukes. Spesielt kan en liten elitebutikk ha en omsetning som er flere ganger større enn omsetningen til store utsalgssteder i "masmarket"-klassen.

Essensen av metoden

Tabelldataene kan avbildes på et kartesisk plan i form av punktene M 1 (x 1, y 1), ... M n (x n, y n). Nå vil løsningen på problemet reduseres til valget av en tilnærmet funksjon y = f (x), som har en graf som passerer så nært som mulig punktene M 1, M 2, .. M n.

Selvfølgelig kan du bruke et polynom i høy grad, men dette alternativet er ikke bare vanskelig å implementere, men også rett og slett feil, siden det ikke vil gjenspeile hovedtrenden som må oppdages. Den mest fornuftige løsningen er å søke etter den rette linjen y = ax + b, som best tilnærmer de eksperimentelle dataene, eller mer presist koeffisientene a og b.

Nøyaktighetsvurdering

Med enhver tilnærming er det spesielt viktig å vurdere nøyaktigheten. La oss betegne med e i forskjellen (avvik) mellom funksjonelle og eksperimentelle verdier for punkt x i, dvs. e i = y i - f (x i).

For å vurdere nøyaktigheten til tilnærmingen kan du selvsagt bruke summen av avvik, dvs. når du velger en rett linje for en omtrentlig representasjon av avhengigheten til X av Y, bør du gi preferanse til den med den minste verdien av sum e i på alle punkter under vurdering. Imidlertid er ikke alt så enkelt, siden det sammen med positive avvik også vil være negative.

Problemet kan løses ved å bruke avviksmoduler eller deres kvadrater. Den siste metoden er den mest brukte. Den brukes på mange områder, inkludert regresjonsanalyse (implementert i Excel ved hjelp av to innebygde funksjoner), og har lenge bevist sin effektivitet.

Minste kvadratiske metode

Excel, som du vet, har en innebygd AutoSum-funksjon som lar deg beregne verdiene til alle verdier som er plassert i det valgte området. Dermed vil ingenting hindre oss i å beregne verdien av uttrykket (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

I matematisk notasjon ser dette slik ut:

Siden beslutningen opprinnelig ble tatt om å tilnærme ved hjelp av en rett linje, har vi:

Oppgaven med å finne den rette linjen som best beskriver den spesifikke avhengigheten av mengdene X og Y kommer ned til å beregne minimum av en funksjon av to variabler:

For å gjøre dette, må du likestille de partielle deriverte med hensyn til de nye variablene a og b til null, og løse et primitivt system som består av to ligninger med 2 ukjente av formen:

Etter noen enkle transformasjoner, inkludert divisjon med 2 og manipulering av summer, får vi:

Ved å løse det, for eksempel ved å bruke Cramers metode, får vi et stasjonært punkt med visse koeffisienter a * og b *. Dette er minimum, dvs. for å forutsi hvilken omsetning en butikk vil ha for et bestemt område, er den rette linjen y = a * x + b * egnet, som er en regresjonsmodell for det aktuelle eksemplet. Selvfølgelig vil det ikke tillate deg å finne det nøyaktige resultatet, men det vil hjelpe deg med å få en ide om hvorvidt det vil lønne seg å kjøpe et bestemt område på butikkkreditt.

Hvordan implementere minste kvadrater i Excel

Excel har en funksjon for å beregne verdier ved å bruke minste kvadrater. Den har følgende form: "TREND" (kjente Y-verdier; kjente X-verdier; nye X-verdier; konstant). La oss bruke formelen for å beregne OLS i Excel på tabellen vår.

For å gjøre dette, skriv inn "="-tegnet i cellen der resultatet av beregningen ved hjelp av minste kvadraters metode i Excel skal vises og velg "TREND" -funksjonen. Fyll ut de aktuelle feltene i vinduet som åpnes, og uthev:

• rekke kjente verdier for Y (i dette tilfellet data for handelsomsetning);
• rekkevidde x 1 , …x n , dvs. størrelsen på butikklokaler;
• både kjente og ukjente verdier av x, som du trenger for å finne ut størrelsen på omsetningen (for informasjon om deres plassering på regnearket, se nedenfor).

I tillegg inneholder formelen den logiske variabelen "Const". Hvis du skriver inn 1 i det tilsvarende feltet, vil dette bety at du skal utføre beregningene, forutsatt at b = 0.

Hvis du trenger å finne ut prognosen for mer enn én x-verdi, bør du ikke trykke "Enter" etter å ha skrevet inn formelen, men du må skrive kombinasjonen "Shift" + "Control" + "Enter" på tastaturet.

Noen funksjoner

Regresjonsanalyse kan være tilgjengelig selv for dummies. Excel-formelen for å forutsi verdien av en rekke ukjente variabler – TREND – kan brukes selv av de som aldri har hørt om minste kvadrater. Det er nok bare å kjenne noen av funksjonene i arbeidet. Spesielt:

• Hvis du ordner rekkevidden av kjente verdier av variabelen y i en rad eller kolonne, vil hver rad (kolonne) med kjente verdier av x bli oppfattet av programmet som en separat variabel.
• Hvis et område med kjent x ikke er spesifisert i TREND-vinduet, vil programmet, når du bruker funksjonen i Excel, behandle det som en matrise bestående av heltall, hvor nummeret tilsvarer området med de gitte verdiene til variabel y.
• For å sende ut en matrise med "forutsagte" verdier, må uttrykket for beregning av trenden angis som en matriseformel.
• Hvis nye verdier av x ikke er spesifisert, anser TREND-funksjonen dem som lik de kjente. Hvis de ikke er spesifisert, tas matrise 1 som et argument; 2; 3; 4;…, som er i samsvar med området med allerede spesifiserte parametere y.
• Området som inneholder de nye x-verdiene må ha samme eller flere rader eller kolonner som området som inneholder de gitte y-verdiene. Den må med andre ord være proporsjonal med de uavhengige variablene.
• En matrise med kjente x-verdier kan inneholde flere variabler. Imidlertid, hvis vi snakker om bare én, kreves det at områdene med de gitte verdiene av x og y er proporsjonale. Ved flere variabler er det nødvendig at området med de gitte y-verdiene passer i en kolonne eller en rad.

PREDIKTION funksjon

Implementert ved hjelp av flere funksjoner. En av dem heter "PREDICTION". Det ligner på "TREND", det vil si at det gir resultatet av beregninger med minste kvadraters metode. Imidlertid bare for en X, der verdien av Y er ukjent.

Nå kjenner du formler i Excel for dummies som lar deg forutsi den fremtidige verdien av en bestemt indikator i henhold til en lineær trend.