Metode minste kvadrater(MNC)

Et system med m lineære ligninger med n ukjente har formen:

Tre tilfeller er mulige: m n. Saken da m=n ble vurdert i de foregående avsnittene. Når m

Hvis m>n og systemet er konsistent, så har matrise A minst m - n lineært avhengige rader. Her kan løsningen fås ved å velge n eventuelle lineært uavhengige ligninger (hvis de finnes) og bruke formelen X = A -1 CV, det vil si å redusere problemet til en tidligere løst. I dette tilfellet vil den resulterende løsningen alltid tilfredsstille de gjenværende m - n ligningene.

Men når du bruker en datamaskin, er det mer praktisk å bruke en mer generell tilnærming - minste kvadraters metode.

Algebraisk minste kvadraters metode

Den algebraiske minste kvadraters metode er en metode for å løse systemer av lineære ligninger

ved å minimere den euklidiske normen

Øks? b? >inf. (1.2)

Analyse av eksperimentelle data

La oss vurdere et eksperiment som til tider

For eksempel måles temperatur Q(t). La måleresultatene spesifiseres av en matrise

La oss anta at de eksperimentelle forholdene er slik at målinger utføres med en kjent feil. I disse tilfellene søkes loven om temperaturendringer Q(t) ved å bruke et bestemt polynom

P(t) = + + + ... +,

bestemme de ukjente koeffisientene, ..., fra hensynet til at verdien E(, ...,), definert av likheten

gauss algebraisk exel tilnærming

tok minimumsverdien. Siden summen av kvadrater er minimert, kalles denne metoden minste kvadraters tilnærming til data.

Hvis vi erstatter P(t) med uttrykket, får vi

La oss sette oppgaven med å definere en matrise slik at verdien er minimal, dvs. La oss definere matrisen ved å bruke minste kvadraters metode. For å gjøre dette, likestiller vi de partielle deriverte til null:

Hvis du skriver inn m × n-matrisen A = (), i = 1, 2..., m; j = 1, 2, ..., n, hvor

I = 1, 2..., m; j = 1, 2, ..., n,

da vil den skriftlige likheten ta formen

La oss omskrive den skriftlige likheten når det gjelder operasjoner med matriser. Ved definisjonen av å multiplisere en matrise med en kolonne, har vi

For en transponert matrise ser et lignende forhold slik ut

La oss introdusere notasjonen: vi vil betegne den i-te komponenten av vektoren Axe I samsvar med de skrevne matriselikhetene vil vi ha

I matriseform kan denne likheten omskrives som

A T x=A T B (1,3)

Her er A en rektangulær m×n matrise. Dessuten, i datatilnærmingsproblemer, som regel, m > n. Ligning (1.3) kalles en normalligning.

Det var mulig helt fra begynnelsen, ved å bruke den euklidiske normen for vektorer, å skrive problemet i ekvivalent matriseform:

Målet vårt er å minimere denne funksjonen i x. For at et minimum skal nås ved et løsningspunkt, må de førstederiverte med hensyn til x på dette punktet være lik null. Derivatene av denne funksjonen er

2A T B + 2A T Axe

og derfor må løsningen tilfredsstille systemet med lineære ligninger

(A T A)x = (A T B).

Disse ligningene kalles normale ligninger. Hvis A er en m× n matrise, så er A>A - n × n en matrise, dvs. matrise normal ligning alltid en kvadratisk symmetrisk matrise. Dessuten har den egenskapen positiv bestemthet i den forstand at (A>Ax, x) = (Ax, Ax) ? 0.

Kommentar. Noen ganger kalles løsningen til en ligning av formen (1.3) en løsning til systemet Ax = B, der A er en rektangulær m × n (m > n) matrise ved bruk av minste kvadraters metode.

Minste kvadraters problem kan tolkes grafisk som å minimere de vertikale avstandene fra datapunkter til en modellkurve (se figur 1.1). Denne ideen er basert på antakelsen om at alle feil i tilnærmingen tilsvarer feil i observasjonene. Hvis det også er feil i de uavhengige variablene, kan det være mer hensiktsmessig å minimere den euklidiske avstanden fra dataene til modellen.

MNC i Excel

Algoritmen nedenfor for å implementere OLS i Excel forutsetter at alle innledende data allerede er kjent. Vi multipliserer begge sider av matriseligningen AЧX=B til systemet til venstre med den transponerte matrisen til systemet А Т:

A SKAT = A T B

Deretter multipliserer vi begge sider av ligningen til venstre med matrisen (AT A) -1. Hvis denne matrisen eksisterer, er systemet definert. Vurderer

(A T A) -1 *(AT A)=E, får vi

X=(AT A) -1 A T B.

Den resulterende matriseligningen er en løsning på et system med m lineære ligninger med n ukjente for m>n.

La oss vurdere bruken av algoritmen ovenfor ved å bruke et spesifikt eksempel.

Eksempel. La det være nødvendig å løse systemet

I Excel ser løsningsarket i formelvisningsmodus for dette problemet slik ut:

Beregningsresultater:

Den nødvendige vektoren X er plassert i området E11:E12.

Når du løser et gitt system med lineære ligninger, ble følgende funksjoner brukt:

1. MOBR - returnerer den inverse matrisen for matrisen som er lagret i matrisen.

Syntaks: MOBR(array).

Matrise er en numerisk matrise med like mange rader og kolonner.

2. MULTIPULT - returnerer produktet av matriser (matriser er lagret i matriser). Resultatet er en matrise med samme antall rader som matrise1 og samme antall kolonner som matrise2.

Syntaks: MULTIPLE(matrise1;matrise2).

Matrise1, matrise2 er multipliserbare matriser.

Etter å ha skrevet inn en funksjon i cellen øverst til venstre i et matriseområde, velg matrisen, start med cellen som inneholder formelen, trykk F2 og trykk deretter CTRL+SHIFT+ENTER.

3. TRANSPORT - konverterer et vertikalt sett med celler til et horisontalt, eller omvendt. Som et resultat av å bruke denne funksjonen, vises en matrise med antall rader lik antall kolonner i den opprinnelige matrisen, og antall kolonner lik antall rader i den opprinnelige matrisen.

Minste kvadraters metode er en matematisk prosedyre for å konstruere lineær ligning, som vil samsvare mest med et sett med to tallserier. Hensikten med å bruke denne metoden er å minimere den totale kvadratfeilen. Excel har verktøy du kan bruke til denne metoden under beregninger. La oss finne ut hvordan dette gjøres.

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.

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

Betingelsene for problemet

La oss beskrive bruken av LSM ved å bruke et 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.

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.

Metoden for minste kvadrater (OLS) refererer til sfæren regresjonsanalyse. 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 for 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, hvis nummer 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

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 anslår vi tilgjengelige data ved hjelp av lineær funksjon y = 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 Du kan bruke følgende formler (se 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, så 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 de faktiske verdiene til X ikke sammenfaller med sekvensen 1; 2; 3; ...).

4.1. Bruker innebygde funksjoner

Beregning regresjonskoeffisienter utføres ved hjelp av funksjonen

LINJEST(Verdier_y; x-verdier; Konst; statistikk),

Verdier_y- rekke y-verdier,

x-verdier- valgfri rekke med verdier x, hvis array X er utelatt, antas det at dette er en matrise (1;2;3;...) av samme størrelse som Verdier_y,

Konst- en boolsk verdi som indikerer om konstanten er nødvendig b var lik 0. Hvis Konst har betydningen EKTE eller utelatt, da b beregnes på vanlig måte. Hvis argumentet Konst er da FALSKT b antas å være 0 og verdiene en velges slik at relasjonen oppfylles y=aks.

Statistikk er en boolsk verdi som indikerer om ytterligere regresjonsstatistikk må returneres. Hvis argumentet Statistikk har betydningen EKTE, deretter funksjonen LINJEST returnerer ytterligere regresjonsstatistikk. Hvis argumentet Statistikk har betydningen Å LIGGE eller utelatt, deretter funksjonen LINJEST returnerer kun koeffisienten en og konstant b.

Det må huskes at resultatet av funksjonene LINEST() er et sett med verdier - en matrise.

For beregning korrelasjonskoeffisient funksjonen brukes

CORREL(Matrise1;Matrise2),

returnerer verdiene til korrelasjonskoeffisienten, hvor Matrise1- rekke verdier y, Matrise2- rekke verdier x. Matrise1 Og Matrise2 må ha samme størrelse.

EKSEMPEL 1. Avhengighet y(x) er presentert i tabellen. Bygge regresjonslinje og beregne korrelasjonskoeffisient.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

La oss legge inn en verditabell i et MS Excel-ark og bygge et spredningsplott. Regnearket vil ha formen vist i fig. 2.

For å beregne verdiene av regresjonskoeffisienter EN Og b velg cellene A7:B7, La oss gå til funksjonsveiviseren og i kategorien Statistisk velge en funksjon LINJEST. La oss fylle ut dialogboksen som vises som vist i fig. 3 og trykk OK.

Som et resultat vil den beregnede verdien bare vises i cellen A6(Fig. 4). For at verdien skal vises i cellen B6 du må gå inn i redigeringsmodus (tast F2), og trykk deretter på tastekombinasjonen CTRL+SHIFT+ENTER.

For å beregne verdien av korrelasjonskoeffisienten i en celle C6 følgende formel ble introdusert:

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

Kjenne til regresjonskoeffisientene EN Og b la oss beregne funksjonsverdiene y=øks+b for gitt x. For å gjøre dette introduserer vi formelen

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

og kopier den til området C5:J5(Fig. 5).

La oss plotte regresjonslinjen på diagrammet. Velg de eksperimentelle punktene på grafen, høyreklikk og velg kommandoen Innledende data. I dialogboksen som vises (fig. 5), velg fanen Rad og klikk på knappen Legg til. La oss fylle ut inndatafeltene som vist i fig. 6 og trykk på knappen OK. En regresjonslinje vil bli lagt til den eksperimentelle datagrafen. Som standard vil grafen tegnes som punkter som ikke er forbundet med jevne linjer.

Ris. 6

For å endre utseendet på regresjonslinjen, utfør følgende trinn. Høyreklikk på punktene som viser linjegrafen og velg kommandoen Diagramtype og still inn typen spredningsdiagram, som vist i fig. 7.

Linjetype, farge og tykkelse kan endres som følger. Velg en linje på diagrammet, høyreklikk og velg kommandoen i hurtigmenyen Dataserieformat... Deretter gjør du innstillinger, for eksempel som vist i fig. 8.

Som et resultat av alle transformasjoner får vi en graf med eksperimentelle data og en regresjonslinje i ett grafisk område (fig. 9).

4.2. Bruke en trendlinje.

Konstruksjonen av forskjellige tilnærmede avhengigheter i MS Excel er implementert som en diagramegenskap - trendlinje.

EKSEMPEL 2. Som et resultat av eksperimentet ble en viss tabellavhengighet bestemt.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Velg og konstruer en tilnærmet avhengighet. Konstruer grafer av tabellformede og utvalgte analytiske avhengigheter.

Å løse problemet kan deles inn i følgende stadier: legge inn innledende data, konstruere et spredningsplott og legge til en trendlinje i denne grafen.

La oss se på denne prosessen i detalj. La oss legge inn de første dataene i regnearket og plotte eksperimentelle data. Velg deretter de eksperimentelle punktene på grafen, høyreklikk og bruk kommandoen Legg til l trendlinje(Fig. 10).

Dialogboksen som vises lar deg bygge en tilnærmet avhengighet.

Den første fanen (fig. 11) i dette vinduet indikerer typen tilnærmet avhengighet.

På den andre (fig. 12) bestemmes konstruksjonsparametrene:

· navnet på den tilnærmede avhengigheten;

· prognose fremover (bakover) av n enheter (denne parameteren bestemmer hvor mange enheter fremover (bakover) trendlinjen må utvides);

om man skal vise skjæringspunktet for en kurve med en rett linje y=konst;

· vise den tilnærmede funksjonen på diagrammet eller ikke (alternativet for å vise ligningen på diagrammet);

· om du skal plassere verdien av standardavviket på diagrammet eller ikke (alternativet til å plassere verdien av tilnærmingspåliteligheten på diagrammet).

La oss velge et polynom av andre grad som en tilnærmet avhengighet (fig. 11) og vise ligningen som beskriver dette polynomet på en graf (fig. 12). Det resulterende diagrammet er vist i fig. 1. 3.

Bruker på samme måte trendlinjer du kan velge parametere for slike avhengigheter som

lineær y=a∙x+b,

logaritmisk y=a∙ln(x)+b,

· eksponentiell y=a∙e b,

· beroligende y=a∙x b,

polynom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d og så videre, opp til et polynom av 6. grad inklusive,

· lineær filtrering.

4.3. Ved hjelp av en løserblokk

Av betydelig interesse er implementeringen i MS Excel av å velge parametere ved å bruke minste kvadraters metode ved å bruke en løserblokk. Denne teknikken lar deg velge parametrene for en funksjon av enhver type. La oss vurdere denne muligheten ved å bruke følgende problem som eksempel.

EKSEMPEL 3. Som et resultat av eksperimentet ble avhengigheten z(t) oppnådd, presentert i tabellen

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Velg avhengighetskoeffisienter Z(t)=At 4 +Bt3 +Ct2 +Dt+K minste kvadraters metode.

Dette problemet tilsvarer problemet med å finne minimum av en funksjon av fem variabler

La oss vurdere prosessen med å løse optimaliseringsproblemet (fig. 14).

La verdiene EN, I, MED, D Og TIL lagret i celler A7:E7. La oss beregne de teoretiske verdiene til funksjonen Z(t)=Ved 4 +Bt3 +Ct2 +Dt+K for gitt t(B2:J2). For å gjøre dette, i cellen B4 skriv inn verdien av funksjonen ved det første punktet (celle B2):

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

La oss kopiere denne formelen inn i området C4:J4 og få den forventede verdien av funksjonen på punktene hvis abscisser er lagret i cellene B2:J2.

Til celle B5 La oss introdusere en formel som beregner kvadratet på forskjellen mellom de eksperimentelle og beregnede punktene:

B5=(B4-B3)^2,

og kopier den til området C5:J5. I en celle F7 vi vil lagre den totale kvadratiske feilen (10). For å gjøre dette, skriv inn formelen:

F7 = SUM(B5:J5).

La oss bruke kommandoen Service®Søk etter en løsning og løse optimaliseringsproblemet uten begrensninger. La oss fylle ut inndatafeltene i dialogboksen vist i Fig. tilsvarende. 14 og trykk på knappen Henrette. Hvis en løsning blir funnet, vil vinduet vist i fig. 15.

Resultatet av beslutningsblokken vil sendes ut til celler A7:E7parameterverdier funksjoner Z(t)=Ved 4 +Bt3 +Ct2 +Dt+K. I celler B4:J4 vi får forventet funksjonsverdi ved startpunktene. I en celle F7 vil bli lagret total kvadratfeil.

Du kan vise eksperimentelle punkter og en tilpasset linje i ett grafisk område ved å velge et område B2:J4, anrop Diagramveiviser og formater deretter utseende mottatte grafer.

Ris. 17 viser MS Excel-regnearket etter at beregningene er utført.

5. REFERANSER

1. Alekseev E.R., Chesnokova O.V., Løse problemer med beregningsmatematikk i Mathcad12, MATLAB7, Maple9-pakkene. – NT Press, 2006.–596 s. :il. -(Opplæringen)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, løse tekniske og matematiske problemer. –M., BINOM, 2008.–260 s.

3. Berezin I.S., Zhidkov N.P., Computational methods. – M.: Nauka, 1966. – 632 s.

4. Garnaev A.Yu., bruker MS EXCEL og VBA i økonomi og finans. – St. Petersburg: BHV - Petersburg, 1999.–332 s.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Numeriske metoder analyse – M.: Nauka, 1967. – 368 s.

6. Korn G., Korn T., Håndbok i matematikk for forskere og ingeniører – M., 1970, 720 s.

7. Alekseev E.R., Chesnokova O.V. Retningslinjer for implementering laboratoriearbeid i MS EXCEL. For studenter av alle spesialiteter. Donetsk, DonNTU, 2004. 112 s.