Bruke minste kvadraters metode for å tilnærme. Tilnærming av kildedata ved lineær avhengighet
KURSARBEID
disiplin: informatikk
Emne: Funksjonstilnærmingsmetode minste kvadrater
Introduksjon
1. Redegjørelse av problemet
Beregning ved hjelp av tabeller laget ved hjelp av midler Microsoft Excel
Algoritmediagram
Beregning i MathCad
Resultater oppnådd ved hjelp av den lineære funksjonen
Presentasjon av resultater i form av grafer
Introduksjon
Formålet med kursarbeidet er å utdype kunnskaper i informatikk, utvikle og konsolidere ferdigheter i arbeid med Microsoft Excel regnearkprosessor og programvareprodukt MathCAD og deres applikasjon for å løse problemer ved hjelp av en datamaskin fra fagområde knyttet til forskning.
Approksimasjon (fra latin "approximare" - "å komme nærmere") - et tilnærmet uttrykk for evt. matematiske objekter(for eksempel tall eller funksjoner) gjennom andre som er enklere, mer praktisk å bruke, eller rett og slett bedre kjent. I Vitenskapelig forskning tilnærming brukes til å beskrive, analysere, generalisere og videre bruke empiriske resultater.
Som kjent kan det være en eksakt (funksjonell) sammenheng mellom størrelser, når én spesifikk verdi tilsvarer én verdi av argumentet, og en mindre presis (korrelasjon) sammenheng, når én spesifikk verdi av argumentet tilsvarer en omtrentlig verdi eller et visst sett med funksjonsverdier, i en eller annen grad nær hverandre. Når du utfører vitenskapelig forskning, behandler resultatene av en observasjon eller et eksperiment, må du vanligvis forholde deg til det andre alternativet.
Når du studerer de kvantitative avhengighetene til forskjellige indikatorer, hvis verdier bestemmes empirisk, er det som regel en viss variasjon. Det bestemmes dels av heterogeniteten til de studerte gjenstandene av livløs og spesielt levende natur, og dels bestemmes det av observasjonsfeil og kvantitativ behandling av materialer. Den siste komponenten kan ikke alltid elimineres fullstendig, den kan bare minimeres ved nøye valg av en adekvat forskningsmetode og nøye arbeid. Derfor, når du utfører noe forskningsarbeid, oppstår problemet med å identifisere den sanne naturen til avhengigheten til de studerte indikatorene, denne eller den graden maskert av unnlatelsen av å ta hensyn til variabiliteten: verdier. Til dette formål brukes tilnærming - en omtrentlig beskrivelse av korrelasjonsavhengigheten til variabler ved hjelp av en passende funksjonell avhengighetsligning som formidler hovedtendensen til avhengigheten (eller dens "trend").
Ved valg av tilnærming bør man ta utgangspunkt i det konkrete forskningsproblemet. Vanligvis, jo enklere ligningen som brukes for tilnærming, jo mer tilnærmet blir den resulterende beskrivelsen av forholdet. Derfor er det viktig å lese hvor betydelig og hva som forårsaker avvikene til spesifikke verdier fra den resulterende trenden. Når man skal beskrive avhengigheten empirisk visse verdier du kan oppnå mye større nøyaktighet ved å bruke noen mer komplekse, mange parametrisk ligning. Det er imidlertid ingen vits i å strebe etter å formidle tilfeldige verdiavvik i spesifikke serier av empiriske data med maksimal nøyaktighet. Det er mye viktigere å forstå det generelle mønsteret i dette tilfellet er mest logisk og med akseptabel nøyaktighet uttrykt nøyaktig ved to-parameter-ligningen strømfunksjon. Når forskeren velger en tilnærmingsmetode, inngår derfor alltid et kompromiss: han bestemmer i hvilken grad det i dette tilfellet er tilrådelig og hensiktsmessig å "ofre" detaljer og følgelig hvor generelt avhengigheten til de sammenlignede variablene skal uttrykkes. Sammen med å identifisere mønstre maskert tilfeldige avvik empiriske data fra generelt mønster, tilnærmingen lar oss også løse mange andre viktige oppgaver: formalisere funnet avhengighet; finne ukjente verdier avhengig variabel ved interpolasjon eller eventuelt ekstrapolering.
I hver oppgave formuleres betingelsene for problemet, de første dataene, skjemaet for å gi resultater, og de viktigste matematiske avhengighetene for å løse problemet er angitt. I samsvar med metoden for å løse problemet utvikles en løsningsalgoritme, som presenteres i grafisk form.
1. Redegjørelse av problemet
1. Bruk minste kvadraters metode, tilnærme funksjonen gitt i tabellen:
a) et polynom av første grad;
b) et polynom av andre grad;
c) eksponentiell avhengighet.
For hver avhengighet, beregne koeffisienten for determinisme.
Beregn korrelasjonskoeffisienten (bare i tilfelle a).
Tegn en trendlinje for hver avhengighet.
Bruk LINEST-funksjonen for å beregne numeriske egenskaper avhengig av.
Sammenlign beregningene dine med resultatene oppnådd ved bruk av LINJE-funksjonen.
Konkluder hvilken av de resulterende formlene den beste måten tilnærmer funksjonen.
Skriv et program på et av programmeringsspråkene og sammenlign beregningsresultatene med de som er oppnådd ovenfor.
Alternativ 3. Funksjonen er gitt i tabellen. 1.
Tabell 1.
xyxyxyxyxy0.281.052.349.113.3329.434.2386.445.55187.540.872.872.6516.863.4137.454.8390.856.32200.645.72205.451. 4.9299.066.66212.971.998.962.8318.993.8556.945.14120.457.13275.742.088.083.0623.754.0175.085.2313 657. 25321,43
2. Beregningsformler
Ofte, når man analyserer empiriske data, er det behov for å finne en funksjonell sammenheng mellom mengdene x og y, som oppnås som et resultat av erfaring eller målinger.
Xi ( uavhengig variabel) settes av eksperimentatoren, og yi, kalt empiriske eller eksperimentelle verdier, oppnås som et resultat av eksperimentet.
Den analytiske formen for det funksjonelle forholdet som eksisterer mellom mengdene x og y er vanligvis ukjent, så en praktisk viktig oppgave oppstår - å finne en empirisk formel
(hvor er parametrene), hvis verdier vil avvike lite fra de eksperimentelle verdiene.
I henhold til minste kvadraters metode er de beste koeffisientene de for hvilke summen av kvadrerte avvik til den funnet empiriske funksjonen fra angi verdier funksjoner vil være minimale.
Ved hjelp av nødvendig tilstand ekstremum av en funksjon av flere variabler - lik null av partielle deriverte, finn et sett med koeffisienter som gir minimum av funksjonen definert av formel (2) og oppnå normalt system for å bestemme koeffisientene:
Dermed reduseres det å finne koeffisientene til å løse systemet (3).
Type system (3) avhenger av hvilken klasse empiriske formler vi leter etter avhengighet (1). I tilfelle av en lineær avhengighet vil system (3) ha formen:
I tilfelle av en kvadratisk avhengighet vil system (3) ha formen:
I noen tilfeller blir en funksjon der de usikre koeffisientene går inn ulineært tatt som en empirisk formel. I dette tilfellet kan noen ganger problemet lineariseres, dvs. redusere til lineær. Slike avhengigheter inkluderer eksponentiell avhengighet
hvor a1 og a2 er udefinerte koeffisienter.
Linearisering oppnås ved å ta logaritmen av likhet (6), hvoretter vi får relasjonen
La oss betegne og henholdsvis ved og, så kan avhengighet (6) skrives i formen, som lar oss bruke formler (4) med å erstatte a1 med og ved.
Grafen for den rekonstruerte funksjonelle avhengigheten y(x) basert på måleresultatene (xi, yi), i=1,2,...,n kalles en regresjonskurve. For å kontrollere samsvar mellom den konstruerte regresjonskurven og de eksperimentelle resultatene, introduseres vanligvis følgende numeriske egenskaper: korrelasjonskoeffisient (lineær avhengighet), korrelasjonsforhold og bestemmelseskoeffisient.
Korrelasjonskoeffisienten er et mål på det lineære forholdet mellom avhengige tilfeldige variabler: Den viser hvor godt en av størrelsene i gjennomsnitt kan representeres som en lineær funksjon av den andre.
Korrelasjonskoeffisienten beregnes ved å bruke formelen:
hvor er gjennomsnittet aritmetisk verdi henholdsvis i x, y.
Korrelasjonskoeffisienten mellom tilfeldige variabler i absolutt verdi overstiger ikke 1. Jo nærmere 1, jo nærmere lineær forbindelse mellom x og y.
I tilfelle av en ikke-lineær korrelasjon, er de betingede gjennomsnittsverdiene plassert nær den buede linjen. I dette tilfellet anbefales det å bruke et korrelasjonsforhold som en karakteristikk av styrken til forbindelsen, hvis tolkning ikke avhenger av typen avhengighet som studeres.
Korrelasjonsforholdet beregnes ved hjelp av formelen:
hvor a telleren karakteriserer spredningen av betingede gjennomsnitt rundt det ubetingede gjennomsnittet.
Alltid. Likhet = tilsvarer tilfeldige ukorrelerte verdier; = hvis og bare hvis det er en eksakt funksjonell sammenheng mellom x og y. Ved en lineær avhengighet av y av x, faller korrelasjonsforholdet sammen med kvadratet av korrelasjonskoeffisienten. Verdien brukes som en indikator på regresjonens avvik fra lineær.
Korrelasjonsforholdet er et mål på korrelasjonen mellom y og x i noen form, men kan ikke gi en ide om graden av nærhet av empiriske data til en spesiell form. For å finne ut hvor nøyaktig den konstruerte kurven reflekterer empiriske data, introduseres en annen egenskap - bestemmelseskoeffisienten.
hvor Srest = - restmengde kvadrater, som karakteriserer avviket mellom eksperimentelle data fra teoretiske.totalt - totale mengden kvadrater, hvor gjennomsnittsverdien er yi.
Regresjonssummen av kvadrater som karakteriserer spredningen av data.
Jo mindre restsummen av kvadrater sammenlignet med totale mengden firkanter, de mer verdi bestemmelseskoeffisient r2, som viser hvor god ligningen oppnådd vha regresjonsanalyse, forklarer sammenhengene mellom variabler. Hvis den er lik 1, så er det en fullstendig korrelasjon med modellen, dvs. det er ingen forskjell mellom de faktiske og estimerte verdiene for y. I motsatt tilfelle, hvis bestemmelseskoeffisienten er 0, vil regresjonsligningen ikke lykkes med å forutsi verdiene til y.
Determinismekoeffisienten overskrider alltid ikke korrelasjonsforholdet. I tilfellet når likhet er oppfylt, kan vi anta at den konstruerte empiriske formelen mest nøyaktig gjenspeiler empirien.
3. Beregning ved hjelp av tabeller laget med Microsoft Excel
For å utføre beregninger, er det tilrådelig å ordne dataene i form av tabell 2, ved hjelp av verktøyene bordprosessor Microsoft Excel.
tabell 2
ABCDEFGHI10,281,050,07840,2940,0219520,0061470,082320,048790,01366120,872,870,75692,49690,65850390,2720,65850390,2720,65850390,3720,65850390,3720,3720,65850390,2720 5131,656,432,722510,60954,4921257,41200617,505681,8609753,07060841 , 998,963,960117,83047,88059915,6823935,48252,192774,36361352,088,084,326416,80648,99891218,41974,39891218,41974,39891218,41974,39891218,41757,39891218,41774 62 ,349,115,475621,317412,812929,982249,882722,2093735,16993272,6516 , 867,022544,67918,6096349,31551118,39942,8249447,48610182,7717,977,672949,776921,2539358,8738721,8738721,8738721,8738712,8738102,8993802,8738721,87387213 83 18,998,008953,741722,6651964,14248152,0892,9439138, 331272103,0623,759,363672,67528,6526287,677222,38553,1675839,692803113,3329,4311,088998,00194316,3675839,692803113. 01511,26211123,4137,4511,6281127,704539,65182135,2127435 , 47233,62300712,35445133,5542,4412,6025150,66244,73888158,823534,855566639131341435555556661414, 43.99324.04199815.56169154.0175.0816.0801301.070864 , 4812258,56961207,2944,31855417,3174164,2386,4417,8929365,641275,68697320,15591546,6624,4594351,8074,4594351,8074,4594351,809433181,80944351,80944351,80944331,800000 43 8.8055112.6786544.23762119.4314.5092121.77948184.9299.0624, 2064487,3752119,0955585, 94982397,8864,59572622,61097195,14120,4526,4196619,113135,7967697,99533182,2414,79123524,6269527,62695219,62695219,62695219,6269529,6269524,6269529,6269524,6269529,6269520,6269520 5143,0557748,18113819,8324,93913925,8317215, 55187,5430,80251040, 847170,9539948,7945776,7015,23399229,04866226,32200,4539,94241266,844252,4361595,3958006,4545937,31459,373,45459,373,45459,372,45459,372,45459,370 44,35561418,38295,40831967,4199446,4125, 36115135,70527247,13275 ,7450,83691966,026362,46712584,3914017,775,61945840,06674257,25321,4352,56252330,368381,07891276,7881276,7881276,781276,78127,752 2652695,932089,99453,310511850,652417,56813982, 9971327.3490.97713415.0797 S U M M S La oss forklare hvordan tabell 2 er kompilert. Trinn 1. I cellene A1:A25 legger vi inn verdiene xi. Trinn 2. I cellene B1:B25 legger vi inn verdiene til yi. Trinn 3. I celle C1 skriver du inn formelen = A1^2. Trinn 4. Denne formelen kopieres inn i cellene C1:C25. Trinn 5. I celle D1 skriver du inn formelen = A1 * B1. Trinn 6. Denne formelen kopieres inn i cellene D1:D25. Trinn 7. I celle F1 skriver du inn formelen = A1^4. Trinn 8. Denne formelen kopieres inn i cellene F1:F25. Trinn 9. I celle G1 skriver du inn formelen = A1^2*B1. Trinn 10. Denne formelen kopieres inn i cellene G1:G25. Trinn 11. I celle H1 skriver du inn formelen = LN(B1). Trinn 12. Denne formelen kopieres inn i cellene H1:H25. Trinn 13. I celle I1 skriver du inn formelen = A1*LN(B1). Trinn 14. Denne formelen kopieres inn i cellene I1:I25. Vi utfører de neste trinnene ved hjelp av automatisk summering S .
Trinn 15. I celle A26 skriver du inn formelen = SUM(A1:A25). Trinn 16. I celle B26 skriver du inn formelen = SUM(B1:B25). Trinn 17. I celle C26 skriver du inn formelen = SUM(C1:C25). Trinn 18. I celle D26 skriver du inn formelen = SUM(D1:D25). Trinn 19. I celle E26 skriver du inn formelen = SUM(E1:E25). Trinn 20. I celle F26 skriver du inn formelen = SUM(F1:F25). Trinn 21. I celle G26 skriver du inn formelen = SUM(G1:G25). Trinn 22. I celle H26 skriver du inn formelen = SUM(H1:H25). Trinn 23. I celle I26 skriver du inn formelen = SUM(I1:I25). La oss tilnærme funksjonen lineær funksjon. For å bestemme koeffisientene og vi vil bruke system (4). Ved å bruke summene i tabell 2, plassert i cellene A26, B26, C26 og D26, skriver vi system (4) i skjemaet løse hvilke, får vi og. Systemet ble løst ved hjelp av Cramers metode. Essensen er som følger. Tenk på et system av n algebraisk lineære ligninger med n ukjente: Determinanten for systemet er determinanten for systemmatrisen: La oss betegne - determinanten som oppnås fra determinanten til systemet Δ ved å erstatte den j-te kolonnen med kolonnen Dermed har den lineære tilnærmingen formen Vi løser system (11) ved hjelp av Microsoft Excel. Resultatene er presentert i tabell 3. Tabell 3 ABCDE282595.932089.992995.93453.310511850.653031 Invers matrise320.212802-0.04503a1=-88.9208133-0.045030.0291764.9a=14974. I tabell 3, i cellene A32:B33 er formelen skrevet (=MOBR(A28:B29)). I cellene E32:E33 er formelen skrevet (=FLERE(A32:B33),(C28:C29)). Deretter tilnærmer vi funksjonen kvadratisk funksjon. For å bestemme koeffisientene a1, a2 og a3 bruker vi system (5). Ved å bruke summene i tabell 2, plassert i cellene A26, B26, C26, D26, E26, F26, G26, skriver vi system (5) i skjemaet løser som, får vi a1=10,663624, og Dermed har den kvadratiske tilnærmingen formen Vi løser system (16) ved hjelp av Microsoft Excel. Resultatene er presentert i tabell 4. Tabell 4 ABCDEF362595,93453,31052089,993795,93453,31052417,56811850,65538453,31052417,56813982,9971327,3453410,3453410,3453410. .033846a1=10.66362442-0.314390.184534-0.021712a2=-18, 924512430.033846-0.021710.002728a3=8.02723
I tabell 4, i cellene A41:C43 er formelen skrevet (=MOBR(A36:C38)). I cellene F41:F43 er formelen skrevet (=FLERE(A41:C43),(D36:D38)). La oss nå tilnærme funksjonen med en eksponentiell funksjon. For å bestemme koeffisientene og tar vi logaritmen til verdiene, og ved å bruke summene i tabell 2, plassert i cellene A26, C26, H26 og I26, får vi systemet Etter å ha løst system (18), får vi og. Etter potensering får vi. Dermed har den eksponentielle tilnærmingen formen Vi løser system (18) ved hjelp av Microsoft Excel. Resultatene er presentert i tabell 5. Tabell 5 BCDEF462595.9390.977134795.93453.3105415.07974849 SÅ MATRICAS = 0.667679 500,212802-0.04503A2 = 0.7743068 0.7743068 0.710.150 0.450 0.416 0.450 0.15. 7
I cellene A50:B51 er formelen skrevet (=MOBR(A46:B47)). I celle E51 er formelen =EXP(E49) skrevet. La oss beregne det aritmetiske gjennomsnittet ved å bruke formlene: Beregningsresultatene ved bruk av Microsoft Excel er presentert i tabell 6. Tabell 6 BC54Xav=3,837255Yav=83,5996 I celle B54 er formelen = A26/25 skrevet. I celle B55 er formelen skrevet = B26/25 Tabell 7 ABJKLMNO10,281,05293,645412,653676814,4365987,97624,444081,88177520,872,87239,54098,8042766517,4372167,4372167,26782167,2672617,2672100 656,43168,78534,7838445955,147448,035726,395820,32073741, 998,96137,87433,4121485571,0770,7358817,368220,02062652,088,08132,7033,0877525703,2112,1387142,2112,1387142,213,2112,138714,213,2112,138714,213,2112,138714,2014,212,138714,213 52582,2416085548,70151,488211,4985887,99584272,6516, 8679,233251,4094444454,174178,5730,000622,83382582,7717,9770,039911,1389164307,244311,46313,19790,86313,19790,86313,19790,86313,4729,86313,19790 4791,0144524174,4373,4915,7914362,382273103,0623,7546 , 515110,604043581,975620,344117,375498,423061113,3329,4327,474820,2572522934,346983,819852,32341,361441,19852,32441,361441,346983,819852,32941,361441 51 10,18252129,786725,90914,090409102,2541133,5542,4411, 821040; 90,02986 72.58358265.3212126.0007996.9257164.2386.441, 1157090,1542928 ,067872219,6288148,75781214,778174,8390,857,1981970,98565252,56831397,703245,695876,64891184,6529,7291184,6529,7291184,6529,729 0241103,718163,9776121,868195,14120,4548,00871, 6972881357, 952471,908425,17881258,6007205,23139,6578,0671,9398923141,64743,1629470,45155769,9408215,551787,301787,30178,304 3043,1629470,45155769,9408215,551787,301787,3047,304,304,551787,3047,3047,30443 1725,38421200,5291951,06226,32200,45290,11626,16429613654, 0227 ,28786126,28273577,409236,66212,97365,18687,968216736,76,038755767,788515795,87247,13275,7495330,19275,7495330,19275,7495330,19275,7495330,19275,7495330,19275,7495330,19275,7463302,19275,7495332,19275,746332,19275,746332,19275,746332,19275,746332,19247,13275. 47565,1469344766,92257,25321,43811,667611,647256563,37121, 842677, 966445516,82695,932089,93830,94585,207919964427404,823786,286115678,1C u m m Restbeløp XY lineær.quad.eksponering La oss forklare hvordan det er kompilert. Cellene A1:A26 og B1:B26 er allerede fylt. Trinn 1. I celle J1 skriv inn formelen = (A1-$B$54)*(B1-$B$55). Trinn 2. Denne formelen kopieres inn i cellene J2:J25. Trinn 3. I celle K1 skriver du inn formelen = (A1-$B$54)^2. Trinn 4. Denne formelen kopieres inn i cellene k2:K25. Trinn 5. I celle L1 skriver du inn formelen = (B1-$B$55)^2. Trinn 6. Denne formelen kopieres inn i cellene L2:L25. Trinn 7. I celle M1 skriver du inn formelen = ($E$32+$E$33*A1-B1)^2. Trinn 8. Denne formelen kopieres inn i cellene M2:M25. Trinn 9. I celle N1 skriver du inn formelen = ($F$41+$F$42*A1+$F$43*A1^2-B1)^2. Trinn 10. Denne formelen kopieres inn i cellene N2:N25. Trinn 11. I celle O1 skriver du inn formelen = ($E$51*EXP($E$50*A1)-B1)^2. Trinn 12. Denne formelen kopieres inn i cellene O2:O25. Vi utfører de neste trinnene ved hjelp av automatisk summering S .
Trinn 13. I celle J26 skriver du inn formelen = SUM(J1:J25). Trinn 14. I celle K26 skriver du inn formelen = SUM(K1:K25). Trinn 15. I celle L26 skriver du inn formelen = CUM(L1:L25). Trinn 16. I celle M26 skriver du inn formelen = SUM(M1:M25). Trinn 17. I celle N26 skriver du inn formelen = SUM(N1:N25). Trinn 18. I celle O26 skriver du inn formelen = SUM(O1:O25). La oss nå beregne korrelasjonskoeffisienten ved å bruke formel (8) (bare for lineær tilnærming) og determinasjonskoeffisienten ved å bruke formel (10). Resultatene av beregninger med Microsoft Excel er presentert i tabell 8. Tabell 8 AB57 Korrelasjonskoeffisient 0,92883358 Koeffisient av determinisme (lineær tilnærming) 0,8627325960 Koeffisient av determinisme (kvadratisk tilnærming) 0,9810356162 Koeffisient av determinisme (eksponentiell tilnærming) 0,42057863 I celle E57 er formelen skrevet =J26/(K26*L26)^(1/2). I celle E59 er formelen = 1-M26/L26 skrevet. I celle E61 er formelen = 1-N26/L26 skrevet. I celle E63 er formelen = 1-O26/L26 skrevet. Analyse av beregningsresultatene viser at den kvadratiske tilnærmingen best beskriver de eksperimentelle dataene. Algoritmediagram Ris. 1. Algoritmediagram for beregningsprogrammet. 5. Beregning i MathCad Lineær regresjon · linje (x, y) - vektor av to elementer (b, a) lineære regresjonskoeffisienter b+ax; · x er vektoren til reelle argumentdata; · y er en vektor av reelle dataverdier av samme størrelse. Figur 2. Polynomregresjon betyr å tilnærme dataene (x1, y1) med et polynom kth grad For k=i er polynomet en rett linje, for k=2 er det en parabel, for k=3 er det en kubisk parabel osv. Som regel er det i praksis k<5. · regress (x,y,k) - vektor av koeffisienter for å konstruere polynomregresjon av data; · interp (s,x,y,t) - resultatet av polynomregresjon; · s=regress(x,y,k); · x er en vektor av reelle argumentdata, hvis elementer er ordnet i stigende rekkefølge; · y er en vektor av reelle dataverdier av samme størrelse; · k - grad av regresjonspolynom (positivt heltall); · t er verdien av argumentet til regresjonspolynomet. Figur 3 I tillegg til de som er diskutert, er flere typer tre-parameter regresjon innebygd i Mathcad; implementeringen deres skiller seg noe fra regresjonsalternativene ovenfor ved at det for dem, i tillegg til datamatrisen, er nødvendig å spesifisere noen startverdier av koeffisientene a, b, c. Bruk riktig type regresjon hvis du har en god ide om hva slags avhengighet som beskriver datasettet ditt. Når en type regresjon ikke reflekterer en sekvens av data godt, er resultatet ofte utilfredsstillende og til og med svært forskjellig avhengig av valg av startverdier. Hver av funksjonene produserer en vektor av raffinerte parametere a, b, c. Resultater oppnådd ved bruk av LINJE-funksjonen La oss se på formålet med LINEST-funksjonen. Denne funksjonen bruker minste kvadrater for å beregne den rette linjen som passer best til de gitte dataene. Funksjonen returnerer en matrise som beskriver den resulterende linjen. Ligningen for en rett linje er: M1x1 + m2x2 + ... + b eller y = mx + b, tabellalgoritme Microsoft-programvare For å oppnå resultatene, må du lage en tabellformel som vil oppta 5 rader og 2 kolonner. Dette intervallet kan plasseres hvor som helst på regnearket. I løpet av dette intervallet må du gå inn i LINEST-funksjonen. Som et resultat bør alle cellene i intervallet A65:B69 fylles (som vist i tabell 9). Tabell 9. АВ6544,95997-88,9208663,73946615,92346670,86273234,5183168144,55492369172239,227404,82 La oss forklare formålet med noen av mengdene i tabell 9. Verdiene plassert i cellene A65 og B65 karakteriserer henholdsvis helning og forskyvning - bestemmelseskoeffisient - F-observert verdi - antall frihetsgrader - regresjonssum av kvadrater - restsum av kvadrater. Presentasjon av resultater i form av grafer Ris. 4. Lineær tilnærmingsgraf Ris. 5. Kvadratisk tilnærmingsgraf Ris. 6. Eksponentiell tilpasningsgraf konklusjoner La oss trekke konklusjoner basert på resultatene av de innhentede dataene. Analyse av beregningsresultatene viser at den kvadratiske tilnærmingen best beskriver de eksperimentelle dataene, fordi trendlinjen for den gjenspeiler mest nøyaktig funksjonen til funksjonen i dette området. Ved å sammenligne resultatene som er oppnådd ved bruk av LINEST-funksjonen, ser vi at de er fullstendig sammenfallende med beregningene utført ovenfor. Dette indikerer at beregningene er riktige. Resultatene oppnådd ved bruk av MathCad-programmet er fullstendig sammenfallende med verdiene gitt ovenfor. Dette indikerer nøyaktigheten av beregningene. Bibliografi
Tilnærming, eller tilnærming- en vitenskapelig metode som består i å erstatte noen gjenstander med andre, på en eller annen måte nær de originale, men enklere.
Tilnærming lar deg studere de numeriske egenskapene og kvalitative egenskapene til et objekt, og reduserer problemet til studiet av enklere eller mer praktiske objekter (for eksempel de hvis egenskaper er lett å beregne eller hvis egenskaper allerede er kjent). I tallteori studeres diofantiske tilnærminger, spesielt tilnærminger av irrasjonelle tall med rasjonelle. I geometri vurderes tilnærminger av kurver med stiplede linjer. Noen grener av matematikk er i hovedsak helt viet til tilnærming, for eksempel teorien om tilnærming av funksjoner, numeriske analysemetoder.
I overført betydning brukes det i filosofi som tilnærmingsmetode, en indikasjon av omtrentlig, ikke-endelig karakter. For eksempel, i denne forstand, ble begrepet "tilnærming" aktivt brukt av Søren Kierkegaard (1813-1855) i "The Final Unscientific Afterword..."
Hvis funksjonen bare brukes til interpolasjon, er det nok å tilnærme punktene med et polynom, for eksempel av femte grad:
Situasjonen er mye mer komplisert hvis de ovennevnte naturlige dataene fungerer som referansepunkter for å identifisere endringsloven med kjente grensebetingelser. For eksempel: og 
. Her avhenger kvaliteten på resultatet av profesjonaliteten til forskeren. I dette tilfellet vil den mest passende loven være:
For optimalt valg av ligningsparametere brukes vanligvis minste kvadraters metode.
Minste kvadraters metode (LSM,EngelskVanlig Minst Firkanter , O.L.S. ) - en matematisk metode som brukes til å løse ulike problemer, basert på å minimere summen av kvadrater av visse funksjoner til de ønskede variablene. Den kan brukes til å "løse" overbestemte ligningssystemer (når antall ligninger overstiger antall ukjente), for å finne en løsning i tilfellet med vanlige (ikke overbestemte) ikke-lineære ligningssystemer, for å tilnærme punktverdier med noen funksjon. OLS er en av de grunnleggende metodene for regresjonsanalyse for å estimere ukjente parametere for regresjonsmodeller fra prøvedata.
Hvis en viss fysisk mengde avhenger av en annen mengde, kan denne avhengigheten studeres ved å måle y ved forskjellige verdier av x. Som et resultat av målinger oppnås en rekke verdier:
x 1, x 2, ..., x i, ..., x n;
y 1 , y 2 , ... , y i , ... , y n .
Basert på dataene fra et slikt eksperiment er det mulig å konstruere en graf av avhengigheten y = ƒ(x). Den resulterende kurven gjør det mulig å bedømme formen til funksjonen ƒ(x). Imidlertid forblir de konstante koeffisientene som inngår i denne funksjonen ukjente. De kan bestemmes ved hjelp av minste kvadraters metode. Eksperimentelle punkter ligger som regel ikke nøyaktig på kurven. Minste kvadraters metode krever at summen av kvadratene av avvikene til forsøkspunktene fra kurven, dvs. 2 var den minste.
I praksis brukes denne metoden oftest (og enklest) ved lineær sammenheng, dvs. Når
y = kx eller y = a + bx.
Lineær avhengighet er svært utbredt i fysikk. Og selv når forholdet er ikke-lineært, prøver de vanligvis å konstruere en graf for å få en rett linje. For eksempel, hvis det antas at brytningsindeksen til glass n er relatert til lysbølgelengden λ ved forholdet n = a + b/λ 2, så plottes avhengigheten av n av λ -2 på grafen.
Vurder avhengigheten y = kx(en rett linje som går gjennom origo). La oss komponere verdien φ - summen av kvadratene av avvikene til punktene våre fra den rette linjen
.
Verdien av φ er alltid positiv og viser seg å være mindre jo nærmere punktene våre er den rette linjen. Minste kvadraters metode sier at verdien for k skal velges slik at φ har et minimum
eller (19)
Beregningen viser at rot-middel-kvadratfeilen ved å bestemme verdien av k er lik
, (20) hvor n er antall målinger.
La oss nå vurdere et litt vanskeligere tilfelle, når punktene må tilfredsstille formelen y = a + bx(en rett linje som ikke går gjennom origo).
Oppgaven er å finne de beste verdiene av a og b fra det tilgjengelige settet med verdier x i, y i.
La oss igjen komponere den kvadratiske formen φ, lik summen av de kvadrerte avvikene til punktene x i, y i fra den rette linjen
og finn verdiene til a og b som φ har et minimum for
;
.
Fellesløsningen av disse ligningene gir
(21)
Rotmiddelkvadratfeilene ved bestemmelse av a og b er like
(23)
. (24)
Når du behandler måleresultater ved hjelp av denne metoden, er det praktisk å oppsummere alle dataene i en tabell der alle beløpene som er inkludert i formlene (19)–(24) er foreløpig beregnet. Formene til disse tabellene er gitt i eksemplene nedenfor.
Eksempel 1. Den grunnleggende ligningen for dynamikken til rotasjonsbevegelse ε = M/J (en rett linje som går gjennom origo) ble studert. Ved forskjellige verdier av øyeblikket M ble vinkelakselerasjonen ε til en viss kropp målt. Det er nødvendig å bestemme treghetsmomentet til denne kroppen. Resultatene av målinger av kraftmomentet og vinkelakselerasjonen er oppført i andre og tredje kolonne tabell 5.
Tabell 5
Ved å bruke formel (19) bestemmer vi:
.
For å bestemme rotmiddelkvadratfeilen bruker vi formel (20)
0.005775 kg-1 · m -2 .
I henhold til formel (18) har vi
S J = (2,996 0,005775)/0,3337 = 0,05185 kg m 2 .
Etter å ha satt reliabiliteten P = 0,95, ved å bruke tabellen over studentkoeffisienter for n = 5, finner vi t = 2,78 og bestemmer den absolutte feilen ΔJ = 2,78 0,05185 = 0,1441 ≈ 0,2 kg m 2 .
La oss skrive resultatene i skjemaet:
J = (3,0 ± 0,2) kg m 2 ;
Eksempel 2. La oss beregne temperaturkoeffisienten for metallmotstand ved å bruke minste kvadraters metode. Motstand avhenger lineært av temperatur
Rt = R 0 (1 + a t°) = R 0 + R 0 a t°.
Frileddet bestemmer motstanden R 0 ved en temperatur på 0 ° C, og helningen er produktet av temperaturkoeffisienten α og motstanden R 0 .
Resultatene av målinger og beregninger er gitt i tabellen ( se tabell 6).
Tabell 6
|
(r - bt - a) 2,10 -6 |
|||||||
Ved å bruke formler (21), (22) bestemmer vi
R 0 = ¯R- α R 0 ¯ t = 1,4005 - 0,002645 85,83333 = 1,1735 Ohm .
La oss finne en feil i definisjonen av α. Siden , så i henhold til formel (18) har vi:
.
Ved å bruke formler (23), (24) har vi
;
0.014126 Ohm.
Etter å ha satt reliabiliteten til P = 0,95, ved å bruke tabellen med studentkoeffisienter for n = 6, finner vi t = 2,57 og bestemmer den absolutte feilen Δα = 2,57 0,000132 = 0,000338 hagl -1 .
a = (23 ± 4) 10-4 hagl-1 ved P = 0,95.
Eksempel 3. Det er nødvendig å bestemme krumningsradiusen til linsen ved å bruke Newtons ringer. Radiene til Newtons ringer r m ble målt og antallet av disse ringene m ble bestemt. Radiene til Newtons ringer er relatert til krumningsradiusen til linsen R og ringtallet ved ligningen
r 2 m = mλR - 2d 0 R,
hvor d 0 er tykkelsen på gapet mellom linsen og den planparallelle platen (eller deformasjonen av linsen),
λ er bølgelengden til det innfallende lyset.
X = (600 ± 6) nm; r2m = y; m = x; λR = b; -2d 0 R = a,
da vil ligningen ta formen y = a + bx.
Resultatene av målinger og beregninger legges inn tabell 7.
Tabell 7
|
y = r 2, 10 -2 mm 2 |
y - bx - a, 10 -4 |
(y - bx - a) 2, 10 -6 |
|||||
Vi beregner:
1. a og b i henhold til formlene (21), (22).
a = ¯r 2 - b¯m = (0,208548333 - 0,0594957 3,5) = 0,0003133 mm 2 .
2. Beregn rot-middel-kvadrat-feilene for verdier b og a ved å bruke formler (23), (24)
3. Med en reliabilitet på P = 0,95, ved å bruke tabellen over Student-koeffisienter for n = 6, finner vi t = 2,57 og bestemmer de absolutte feilene
Δb = 2,57 · 0,000211179 = 6,10 -4 mm 2 ;
Δa = 2,57 0,000822424 = 3 10 -3 mm 2 .
4. Registrer resultatene
b = (595 ± 6) 10-4 mm 2 ved P = 0,95;
a = (0,3 ± 3)·10 -3 mm 2 ved P = 0,95;
Fra de oppnådde eksperimentelle resultatene følger det at innenfor feilen til dette eksperimentet, går den rette linjen r 2 m = ƒ(m) gjennom origo for koordinater, fordi hvis feilen i verdien til en parameter viser seg å være sammenlignbar med eller overskrider verdien til parameteren, betyr dette at den reelle verdien av denne parameteren mest sannsynlig er null.
Under betingelsene for dette eksperimentet er verdien av a ikke av interesse. Derfor vil vi ikke forholde oss til det lenger.
5. Beregn krumningsradiusen til linsen:
R = b/A = 594,5/6 = 99,1 mm.
6. Siden det er gitt en systematisk feil for bølgelengden, la oss også beregne den systematiske feilen for R ved å bruke formel (16), og ta som den systematiske feilen for størrelsen b dens tilfeldige feil Δb.
Vi skriver ned det endelige resultatet R = (99 ± 2) mmε ≈ 3 % ved P = 0,95.
Eksempel.
Eksperimentelle data om verdiene til variabler X Og på er gitt i tabellen.
Som et resultat av deres justering oppnås funksjonen 
Ved hjelp av minste kvadrat-metoden, tilnærmet disse dataene ved en lineær avhengighet y=ax+b(finn parametere EN Og b). Finn ut hvilken av de to linjene som er best (i betydningen minste kvadraters metode) som justerer eksperimentelle data. Lag en tegning.
Essensen av minste kvadraters metode (LSM).
Oppgaven er å finne de lineære avhengighetskoeffisientene som funksjonen til to variabler EN Og b
tar den minste verdien. Det vil si gitt EN Og b summen av kvadrerte avvik av eksperimentelle data fra den funnet rette linjen vil være den minste. Dette er hele poenget med minste kvadraters metode.
Å løse eksemplet kommer altså ned til å finne ekstremumet til en funksjon av to variabler.
Utlede formler for å finne koeffisienter.
Et system med to ligninger med to ukjente er kompilert og løst. Finne de partielle deriverte av en funksjon 
etter variabler EN Og b, likestiller vi disse derivatene til null. 
Vi løser det resulterende ligningssystemet ved å bruke en hvilken som helst metode (for eksempel etter substitusjonsmetode eller Cramers metode) og få formler for å finne koeffisienter ved å bruke minste kvadraters metode (LSM). 
Gitt EN Og b funksjon 
tar den minste verdien. Beviset for dette faktum er gitt under i teksten på slutten av siden.
Det er hele metoden med minste kvadrater. Formel for å finne parameteren en inneholder summene ,,, og parameter n- mengde eksperimentelle data. Vi anbefaler å beregne verdiene av disse beløpene separat. Koeffisient b funnet etter beregning en.
Det er på tide å huske det originale eksemplet.
Løsning.
I vårt eksempel n=5. Vi fyller ut tabellen for å gjøre det lettere å beregne beløpene som er inkludert i formlene til de nødvendige koeffisientene. 
Verdiene i den fjerde raden i tabellen oppnås ved å multiplisere verdiene i den andre raden med verdiene i den tredje raden for hvert tall Jeg.
Verdiene i den femte raden i tabellen oppnås ved å kvadrere verdiene i den andre raden for hvert tall Jeg.
Verdiene i den siste kolonnen i tabellen er summene av verdiene på tvers av radene.
Vi bruker formlene til minste kvadraters metode for å finne koeffisientene EN Og b. Vi erstatter de tilsvarende verdiene fra den siste kolonnen i tabellen i dem: 
Derfor, y = 0,165x+2,184- den ønskede tilnærmede rette linjen.
Det gjenstår å finne ut hvilken av linjene y = 0,165x+2,184 eller 
tilnærmer de opprinnelige dataene bedre, det vil si gjør et estimat ved å bruke minste kvadraters metode.
Feil estimering av minste kvadraters metode.
For å gjøre dette må du beregne summen av kvadrerte avvik fra de opprinnelige dataene fra disse linjene 
Og 
, tilsvarer en mindre verdi en linje som bedre tilnærmer de opprinnelige dataene i betydningen minste kvadraters metode. 
Siden , da rett y = 0,165x+2,184 tilnærmer de opprinnelige dataene bedre.
Grafisk illustrasjon av minste kvadraters (LS) metode.
Alt er godt synlig på grafene. Den røde linjen er den funnet rette linjen y = 0,165x+2,184, er den blå linjen 
, rosa prikker er de opprinnelige dataene.
I praksis, når man modellerer ulike prosesser - spesielt økonomiske, fysiske, tekniske, sosiale - brukes en eller annen metode for å beregne omtrentlige verdier av funksjoner fra deres kjente verdier på visse faste punkter.
Denne typen funksjonstilnærmingsproblem oppstår ofte:
når du konstruerer omtrentlige formler for å beregne verdiene av karakteristiske mengder av prosessen som studeres ved å bruke tabelldata oppnådd som et resultat av eksperimentet;
i numerisk integrasjon, differensiering, løsning av differensialligninger, etc.;
om nødvendig, beregn verdiene til funksjoner på mellompunkter av det betraktede intervallet;
når du bestemmer verdiene for karakteristiske mengder av en prosess utenfor det betraktede intervallet, spesielt ved prognoser.
Hvis vi, for å modellere en bestemt prosess spesifisert av en tabell, konstruerer en funksjon som tilnærmet beskriver denne prosessen basert på minste kvadraters metode, vil den kalles en approksimerende funksjon (regresjon), og selve oppgaven med å konstruere approksimerende funksjoner vil bli kalt et tilnærmingsproblem.
Denne artikkelen diskuterer mulighetene til MS Excel-pakken for å løse denne typen problemer, i tillegg gir den metoder og teknikker for å konstruere (lage) regresjoner for funksjoner i tabellform (som er grunnlaget for regresjonsanalyse).
Excel har to alternativer for å bygge regresjoner.
Legge til utvalgte regresjoner (trendlinjer) til et diagram bygget på grunnlag av en datatabell for prosesskarakteristikken som studeres (bare tilgjengelig hvis et diagram er konstruert);
Ved å bruke de innebygde statistiske funksjonene i Excel-regnearket, kan du få regresjoner (trendlinjer) direkte fra kildedatatabellen.
Legge til trendlinjer i et diagram
For en tabell med data som beskriver en prosess og er representert av et diagram, har Excel et effektivt regresjonsanalyseverktøy som lar deg:
bygge på basis av minste kvadraters metode og legge til fem typer regresjoner til diagrammet, som modellerer prosessen som studeres med ulik grad av nøyaktighet;
legg til den konstruerte regresjonsligningen til diagrammet;
bestemme graden av samsvar mellom den valgte regresjonen og dataene som vises på diagrammet.
Basert på diagramdata lar Excel deg få lineære, polynomiske, logaritmiske, potensielle, eksponentielle typer regresjoner, som er spesifisert av ligningen:
y = y(x)
hvor x er en uavhengig variabel som ofte tar verdiene til en sekvens av naturlige tall (1; 2; 3; ...) og produserer for eksempel en nedtelling av tiden for prosessen som studeres (karakteristikker).
1 . Lineær regresjon er bra for modellering av egenskaper hvis verdier øker eller reduseres med en konstant hastighet. Dette er den enkleste modellen å konstruere for prosessen som studeres. Den er konstruert i samsvar med ligningen:
y = mx + b
hvor m er tangenten til den lineære regresjonshellingen til x-aksen; b - koordinat for skjæringspunktet for lineær regresjon med ordinataksen.
2 . En polynomtrendlinje er nyttig for å beskrive egenskaper som har flere distinkte ytterpunkter (maksima og minima). Valget av polynomgrad bestemmes av antall ekstrema for karakteristikken som studeres. Dermed kan et andregrads polynom godt beskrive en prosess som bare har ett maksimum eller minimum; polynom av tredje grad - ikke mer enn to ekstrema; polynom av fjerde grad - ikke mer enn tre ekstrema, etc.
I dette tilfellet er trendlinjen konstruert i samsvar med ligningen:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
hvor koeffisientene c0, c1, c2,... c6 er konstanter hvis verdier bestemmes under konstruksjon.
3 . Den logaritmiske trendlinjen brukes med hell ved modellering av egenskaper hvis verdier opprinnelig endres raskt og deretter gradvis stabiliseres.
y = c ln(x) + b
4 . En maktlovstrendlinje gir gode resultater dersom verdiene til forholdet som studeres er preget av en konstant endring i vekstraten. Et eksempel på en slik avhengighet er grafen for jevnt akselerert bevegelse av en bil. Hvis det er null eller negative verdier i dataene, kan du ikke bruke en strømtrendlinje.
Konstruert i samsvar med ligningen:
y = c xb
hvor koeffisientene b, c er konstanter.
5 . En eksponentiell trendlinje bør brukes når endringshastigheten i dataene øker kontinuerlig. For data som inneholder null eller negative verdier, er denne typen tilnærming heller ikke aktuelt.
Konstruert i samsvar med ligningen:
y = c ebx
hvor koeffisientene b, c er konstanter.
Når du velger en trendlinje, beregner Excel automatisk verdien av R2, som karakteriserer påliteligheten til tilnærmingen: jo nærmere R2-verdien er enhet, desto mer pålitelig tilnærmer trendlinjen prosessen som studeres. Om nødvendig kan R2-verdien alltid vises på diagrammet.
Bestemt av formelen:
Slik legger du til en trendlinje i en dataserie:
aktivere et diagram basert på en serie data, dvs. klikk innenfor kartområdet. Diagram-elementet vil vises i hovedmenyen;
etter å ha klikket på dette elementet, vises en meny på skjermen der du skal velge kommandoen Legg til trendlinje.
De samme handlingene kan enkelt implementeres ved å flytte musepekeren over grafen som tilsvarer en av dataseriene og høyreklikke; Velg kommandoen Legg til trendlinje i kontekstmenyen som vises. Trendlinje-dialogboksen vises på skjermen med kategorien Type åpen (fig. 1).
Etter dette trenger du:
Velg ønsket trendlinjetype på Type-fanen (Lineær-typen er valgt som standard). For polynomtypen, i Grad-feltet, spesifiser graden av det valgte polynomet.
1 . Feltet Bygget på serie viser alle dataserier i det aktuelle diagrammet. For å legge til en trendlinje til en bestemt dataserie, velg navnet i feltet Bygget på serie.
Om nødvendig, ved å gå til fanen Parameters (fig. 2), kan du angi følgende parametere for trendlinjen:
endre navnet på trendlinjen i feltet Navn på tilnærmet (utjevnet) kurve.
angi antall perioder (fremover eller bakover) for prognosen i Prognose-feltet;
vis ligningen til trendlinjen i diagramområdet, som du bør aktivere vis ligningen på diagrammet avkrysningsboksen for;
vis tilnærmingspålitelighetsverdien R2 i diagramområdet, som du bør aktivere avmerkingsboksen Plasser tilnærmingspålitelighetsverdien på diagrammet (R^2);
angi skjæringspunktet for trendlinjen med Y-aksen, som du bør aktivere avmerkingsboksen for skjæringspunktet for kurven med Y-aksen i et punkt;
Klikk OK-knappen for å lukke dialogboksen.
For å begynne å redigere en allerede tegnet trendlinje, er det tre måter:
bruk kommandoen Valgt trendlinje fra Format-menyen, etter å ha valgt trendlinjen tidligere;
velg kommandoen Formater trendlinje fra kontekstmenyen, som hentes frem ved å høyreklikke på trendlinjen;
dobbeltklikk på trendlinjen.
Dialogboksen Trendlinjeformat vises på skjermen (fig. 3), som inneholder tre faner: View, Type, Parameters, og innholdet i de to siste faller fullstendig sammen med de lignende fanene i Trend Line-dialogboksen (fig. 1) -2). På Vis-fanen kan du angi linjetype, farge og tykkelse.
For å slette en trendlinje som allerede er tegnet, velg trendlinjen som skal slettes og trykk på Delete-tasten.
Fordelene med det betraktede regresjonsanalyseverktøyet er:
den relative lette å konstruere en trendlinje på diagrammer uten å lage en datatabell for den;
en ganske bred liste over typer foreslåtte trendlinjer, og denne listen inkluderer de mest brukte regresjonstypene;
evnen til å forutsi oppførselen til prosessen som studeres med et vilkårlig (innenfor grensene for sunn fornuft) antall skritt fremover og også bakover;
evnen til å oppnå trendlinjeligningen i analytisk form;
muligheten for om nødvendig å få en vurdering av tilnærmelsens pålitelighet.
Ulempene inkluderer følgende:
konstruksjonen av en trendlinje utføres bare hvis det er et diagram bygget på en serie data;
prosessen med å generere dataserier for karakteristikken som studeres basert på trendlinjeligningene oppnådd for den er noe rotete: de nødvendige regresjonsligningene oppdateres med hver endring i verdiene til den opprinnelige dataserien, men bare innenfor diagramområdet , mens dataserien dannet på grunnlag av den gamle linjeligningstrenden forblir uendret;
I pivotdiagramrapporter bevarer ikke eksisterende trendlinjer ved å endre visningen av et diagram eller tilknyttet pivottabellrapport, noe som betyr at før du tegner trendlinjer eller på annen måte formaterer en pivotdiagramrapport, bør du sørge for at rapportoppsettet oppfyller de nødvendige kravene.
Trendlinjer kan brukes til å supplere dataserier presentert på diagrammer som grafer, histogram, flate ikke-standardiserte områdediagrammer, stolpediagrammer, punktdiagrammer, boblediagrammer og aksjediagrammer.
Du kan ikke legge til trendlinjer i dataserier i 3D-, normaliserte, radar-, kake- og smultringdiagrammer.
Bruker Excels innebygde funksjoner
Excel har også et regresjonsanalyseverktøy for å plotte trendlinjer utenfor diagramområdet. Det finnes en rekke statistiske regnearkfunksjoner du kan bruke til dette formålet, men alle lar deg bare bygge lineære eller eksponentielle regresjoner.
Excel har flere funksjoner for å konstruere lineær regresjon, spesielt:
SKRÅNING og KUT.
TREND;
I tillegg til flere funksjoner for å konstruere en eksponentiell trendlinje, spesielt:
LGRFPRIBL.
Det skal bemerkes at teknikkene for å konstruere regresjoner ved å bruke TREND- og GROWTH-funksjonene er nesten de samme. Det samme kan sies om funksjonsparet LINEST og LGRFPRIBL. For disse fire funksjonene bruker oppretting av en verditabell Excel-funksjoner som matriseformler, som noe roter prosessen med å bygge regresjoner. Legg også merke til at konstruksjonen av lineær regresjon, etter vår mening, er enklest oppnådd ved å bruke funksjonene SLOPE og INTERCEPT, der den første av dem bestemmer helningen til den lineære regresjonen, og den andre bestemmer segmentet som avskjæres av regresjonen på y -akser.
Fordelene med det innebygde funksjonsverktøyet for regresjonsanalyse er:
en ganske enkel, enhetlig prosess for å generere dataserier av karakteristikken som studeres for alle innebygde statistiske funksjoner som definerer trendlinjer;
standardmetodikk for å konstruere trendlinjer basert på genererte dataserier;
evnen til å forutsi oppførselen til prosessen som studeres ved det nødvendige antall skritt fremover eller bakover.
Ulempene inkluderer det faktum at Excel ikke har innebygde funksjoner for å lage andre (unntatt lineære og eksponentielle) typer trendlinjer. Denne omstendigheten tillater ofte ikke å velge en tilstrekkelig nøyaktig modell av prosessen som studeres, i tillegg til å oppnå prognoser som er nær virkeligheten. I tillegg, når du bruker funksjonene TREND og GROWTH, er ikke ligningene til trendlinjene kjent.
Det skal bemerkes at forfatterne ikke satte seg fore å presentere forløpet av regresjonsanalyse med noen grad av fullstendighet. Hovedoppgaven er å vise, ved hjelp av spesifikke eksempler, egenskapene til Excel-pakken når du løser tilnærmingsproblemer; demonstrere hvilke effektive verktøy Excel har for å bygge regresjoner og prognoser; illustrere hvordan slike problemer kan løses relativt enkelt selv av en bruker som ikke har omfattende kunnskap om regresjonsanalyse.
Eksempler på løsning av spesifikke problemer
La oss se på å løse spesifikke problemer ved å bruke de oppførte Excel-verktøyene.
Oppgave 1
Med en tabell over overskuddet til en biltransportbedrift for 1995-2002. du må gjøre følgende:
Bygg et diagram.
Legg til lineære og polynomiske (kvadratiske og kubiske) trendlinjer i diagrammet.
Ved å bruke trendlinjelikningene kan du få tabelldata om bedriftsfortjeneste for hver trendlinje for 1995-2004.
Lag en prognose for bedriftens resultat for 2003 og 2004.
Løsningen på problemet
I celleområdet A4:C11 i Excel-regnearket, skriv inn regnearket vist i fig. 4.
Etter å ha valgt celleområdet B4:C11, bygger vi et diagram.
Vi aktiverer det konstruerte diagrammet og, i henhold til metoden beskrevet ovenfor, etter å ha valgt type trendlinje i dialogboksen Trendlinje (se fig. 1), legger vi vekselvis til lineære, kvadratiske og kubiske trendlinjer til diagrammet. I den samme dialogboksen åpner du fanen Parameters (se fig. 2), i feltet Navn på den tilnærmede (utjevnede) kurven skriver du inn navnet på trenden som legges til, og i feltet Prognose fremover for: perioder angir du verdi 2, siden det planlegges å lage en resultatprognose for to år fremover. For å vise regresjonsligningen og tilnærmingspålitelighetsverdien R2 i diagramområdet, aktiver avmerkingsboksene for vis ligning på skjermen og plasser tilnærmingspålitelighetsverdien (R^2) på diagrammet. For bedre visuell oppfatning endrer vi type, farge og tykkelse på de konstruerte trendlinjene, som vi bruker fanen Vis i dialogboksen Trendlinjeformat (se fig. 3). Det resulterende diagrammet med ekstra trendlinjer er vist i fig. 5.
For å innhente tabelldata om bedriftsfortjeneste for hver trendlinje for 1995-2004. La oss bruke trendlinjeligningene presentert i fig. 5. For å gjøre dette, i cellene i området D3:F3, skriv inn tekstinformasjon om typen av den valgte trendlinjen: Lineær trend, Kvadratisk trend, Kubisk trend. Deretter skriver du inn den lineære regresjonsformelen i celle D4, og ved å bruke fyllmarkøren kopierer du denne formelen med relative referanser til celleområdet D5:D13. Det bør bemerkes at hver celle med en lineær regresjonsformel fra celleområdet D4:D13 har som argument en tilsvarende celle fra området A4:A13. På samme måte, for kvadratisk regresjon, fyll celleområdet E4:E13, og for kubisk regresjon fyll celleområdet F4:F13. Det er derfor utarbeidet en prognose for foretakets resultat for 2003 og 2004. ved hjelp av tre trender. Den resulterende verditabellen er vist i fig. 6.
Oppgave 2
Bygg et diagram.
Legg til logaritmiske, potensielle og eksponentielle trendlinjer i diagrammet.
Utled ligningene til de oppnådde trendlinjene, så vel som pålitelighetsverdiene til tilnærmingen R2 for hver av dem.
Ved å bruke trendlinjelikningene kan du få tabelldata om bedriftens resultat for hver trendlinje for 1995-2002.
Lag en prognose for selskapets resultat for 2003 og 2004 ved å bruke disse trendlinjene.
Løsningen på problemet
Ved å følge metodikken gitt ved løsning av oppgave 1 får vi et diagram med logaritmiske, potensielle og eksponentielle trendlinjer lagt til (fig. 7). Deretter, ved å bruke de oppnådde trendlinjeligningene, fyller vi ut en tabell med verdier for foretakets fortjeneste, inkludert de anslåtte verdiene for 2003 og 2004. (Fig. 8).
I fig. 5 og fig. det kan ses at modellen med en logaritmisk trend tilsvarer den laveste verdien av tilnærmingspålitelighet
R2 = 0,8659
De høyeste verdiene av R2 tilsvarer modeller med en polynomtrend: kvadratisk (R2 = 0,9263) og kubikk (R2 = 0,933).
Oppgave 3
Med tabellen med data om overskuddet til en motortransportbedrift for 1995-2002, gitt i oppgave 1, må du utføre følgende trinn.
Skaff dataserier for lineære og eksponentielle trendlinjer ved å bruke TREND- og GROW-funksjonene.
Ved å bruke funksjonene TREND og VEKST, lag en prognose for bedriftens resultat for 2003 og 2004.
Konstruer et diagram for de opprinnelige dataene og den resulterende dataserien.
Løsningen på problemet
La oss bruke arbeidsarket for oppgave 1 (se fig. 4). La oss starte med TREND-funksjonen:
velg celleområdet D4:D11, som skal fylles med verdiene til TREND-funksjonen som tilsvarer kjente data om fortjenesten til bedriften;
Kall opp funksjonskommandoen fra Sett inn-menyen. I dialogboksen Funksjonsveiviser som vises, velg TREND-funksjonen fra Statistical-kategorien, og klikk deretter OK-knappen. Den samme operasjonen kan utføres ved å klikke på (Sett inn funksjon)-knappen på standardverktøylinjen.
I dialogboksen Funksjonsargumenter som vises, skriv inn celleområdet C4:C11 i feltet Known_values_y; i feltet Known_values_x - celleområdet B4:B11;
For å få den angitte formelen til å bli en matriseformel, bruk tastekombinasjonen + + .
Formelen vi skrev inn i formellinjen vil se slik ut: =(TREND(C4:C11,B4:B11)).
Som et resultat blir celleområdet D4:D11 fylt med de tilsvarende verdiene til TREND-funksjonen (fig. 9).
Å lage en prognose for foretakets resultat for 2003 og 2004. nødvendig:
velg celleområdet D12:D13 der verdiene som er forutsagt av TREND-funksjonen skal legges inn.
kall opp TREND-funksjonen og i dialogboksen Funksjonsargumenter som vises, skriv inn i Known_values_y-feltet - celleområdet C4:C11; i feltet Known_values_x - celleområdet B4:B11; og i New_values_x-feltet - celleområdet B12:B13.
gjør denne formelen til en matriseformel ved å bruke tastekombinasjonen Ctrl + Shift + Enter.
Den angitte formelen vil se slik ut: =(TREND(C4:C11;B4:B11;B12:B13)), og celleområdet D12:D13 vil bli fylt med de forutsagte verdiene til TREND-funksjonen (se fig. 9).
Dataserien fylles på tilsvarende måte ut ved hjelp av funksjonen GROWTH, som brukes i analyse av ikke-lineære avhengigheter og fungerer på nøyaktig samme måte som dens lineære motstykke TREND.
Figur 10 viser tabellen i formelvisningsmodus.
For de første dataene og den oppnådde dataserien, diagrammet vist i fig. elleve.
Oppgave 4
Med tabellen med data om mottak av søknader om tjenester fra ekspedisjonstjenesten til et motortransportbedrift for perioden fra 1. til 11. i inneværende måned, må du utføre følgende handlinger.
Hent dataserier for lineær regresjon: bruk av SLOPE- og INTERCEPT-funksjonene; ved å bruke LINEST-funksjonen.
Skaff en serie data for eksponentiell regresjon ved å bruke LGRFPRIBL-funksjonen.
Ved å bruke funksjonene ovenfor, lag en prognose om mottak av søknader til ekspedisjonstjenesten for perioden fra 12. til 14. i inneværende måned.
Lag et diagram for den opprinnelige og mottatte dataserien.
Løsningen på problemet
Merk at, i motsetning til funksjonene TREND og GROWTH, er ingen av funksjonene oppført ovenfor (SLOPE, INTERCEPT, LINEST, LGRFPRIB) regresjon. Disse funksjonene spiller bare en støttende rolle, og bestemmer de nødvendige regresjonsparametrene.
For lineære og eksponentielle regresjoner bygget ved hjelp av funksjonene SLOPE, INTERCEPT, LINEST, LGRFPRIB, er utseendet til deres likninger alltid kjent, i motsetning til lineære og eksponentielle regresjoner som tilsvarer funksjonene TREND og GROWTH.
1 . La oss bygge en lineær regresjon med ligningen:
y = mx+b
ved å bruke SLOPE- og INTERCEPT-funksjonene, med regresjonshellingen m bestemt av SLOPE-funksjonen, og det frie leddet b av INTERCEPT-funksjonen.
For å gjøre dette, utfører vi følgende handlinger:
skriv inn den opprinnelige tabellen i celleområdet A4:B14;
verdien av parameter m vil bli bestemt i celle C19. Velg Slope-funksjonen fra Statistical-kategorien; angi celleområdet B4:B14 i feltet kjente_verdier_y og celleområdet A4:A14 i kjente_verdier_x-feltet. Formelen vil bli lagt inn i celle C19: =SLOPE(B4:B14,A4:A14);
Ved å bruke en lignende teknikk bestemmes verdien av parameter b i celle D19. Og innholdet vil se slik ut: =SEGMENT(B4:B14,A4:A14). Dermed vil verdiene til parametrene m og b som kreves for å konstruere en lineær regresjon lagres i henholdsvis cellene C19, D19;
Deretter skriver du inn den lineære regresjonsformelen i celle C4 i formen: =$C*A4+$D. I denne formelen er cellene C19 og D19 skrevet med absolutte referanser (celleadressen skal ikke endres under eventuell kopiering). Det absolutte referansetegnet $ kan skrives inn enten fra tastaturet eller ved å bruke F4-tasten, etter å ha plassert markøren på celleadressen. Bruk fyllhåndtaket og kopier denne formelen inn i celleområdet C4:C17. Vi får den nødvendige dataserien (fig. 12). På grunn av det faktum at antall forespørsler er et heltall, bør du sette tallformatet med antall desimaler til 0 på Number-fanen i Celleformat-vinduet.
2 . La oss nå bygge en lineær regresjon gitt av ligningen:
y = mx+b
ved å bruke LINEST-funksjonen.
For dette:
Skriv inn LINEST-funksjonen som en matriseformel i celleområdet C20:D20: =(LINEST(B4:B14,A4:A14)). Som et resultat får vi verdien av parameter m i celle C20, og verdien av parameter b i celle D20;
skriv inn formelen i celle D4: =$C*A4+$D;
kopier denne formelen ved hjelp av fyllmarkøren inn i celleområdet D4:D17 og få ønsket dataserie.
3 . Vi bygger en eksponentiell regresjon med ligningen:
ved å bruke LGRFPRIBL-funksjonen utføres det på samme måte:
I celleområdet C21:D21 legger vi inn LGRFPRIBL-funksjonen som en matriseformel: =( LGRFPRIBL (B4:B14,A4:A14)). I dette tilfellet vil verdien av parameter m bli bestemt i celle C21, og verdien av parameter b vil bli bestemt i celle D21;
formelen legges inn i celle E4: =$D*$C^A4;
ved å bruke fyllmarkøren kopieres denne formelen til celleområdet E4:E17, hvor dataserien for eksponentiell regresjon vil bli plassert (se fig. 12).
I fig. Figur 13 viser en tabell der du kan se funksjonene vi bruker med de nødvendige celleområdene, samt formler.
Omfanget R 2 kalt bestemmelseskoeffisient.
Oppgaven med å konstruere en regresjonsavhengighet er å finne vektoren av koeffisientene m til modell (1) der koeffisienten R får maksimalverdi.
For å vurdere betydningen av R brukes Fishers F-test, beregnet ved hjelp av formelen
Hvor n- prøvestørrelse (antall eksperimenter);
k er antall modellkoeffisienter.
Hvis F overskrider en kritisk verdi for dataene n Og k og den aksepterte konfidenssannsynligheten, så anses verdien av R som signifikant. Tabeller med kritiske verdier av F er gitt i oppslagsverk om matematisk statistikk.
Dermed bestemmes betydningen av R ikke bare av verdien, men også av forholdet mellom antall eksperimenter og antall koeffisienter (parametere) til modellen. Faktisk er korrelasjonsforholdet for n=2 for en enkel lineær modell lik 1 (en enkelt rett linje kan alltid trekkes gjennom 2 punkter på et plan). Imidlertid, hvis de eksperimentelle dataene er tilfeldige variabler, bør en slik verdi av R stoles på med stor forsiktighet. Vanligvis, for å oppnå signifikant R og pålitelig regresjon, streber de etter å sikre at antall eksperimenter betydelig overstiger antall modellkoeffisienter (n>k).
Å konstruere en lineær regresjonsmodell nødvendig:
1) lag en liste med n rader og m kolonner som inneholder eksperimentelle data (kolonne som inneholder utdataverdien Y må enten være først eller sist på listen); La oss for eksempel ta dataene fra forrige oppgave, legge til en kolonne kalt "Periode nr.", nummerere periodetallene fra 1 til 12. (disse vil være verdiene X)
2) gå til menyen Data/Dataanalyse/Regresjon
Hvis "Data Analysis"-elementet i "Verktøy"-menyen mangler, bør du gå til "Add-Ins"-elementet i samme meny og merke av for "Analysepakke".
3) i "Regresjon"-dialogboksen, sett:
· inndataintervall Y;
· inndataintervall X;
· utdataintervall - den øvre venstre cellen i intervallet der beregningsresultatene skal plasseres (det anbefales å plassere dem på et nytt regneark);
4) klikk "Ok" og analyser resultatene.
Angivelse av tilnærmingsproblemet ved bruk av minste kvadrater. Forutsetninger for best tilnærming.
Hvis et sett med eksperimentelle data oppnås med en betydelig feil, er interpolering ikke bare nødvendig, men også uønsket! Her kreves det å konstruere en kurve som vil gjengi grafen til det opprinnelige eksperimentelle mønsteret, dvs. ville være så nært forsøkspunktene som mulig, men ville samtidig være ufølsomme for tilfeldige avvik av den målte verdien.
La oss introdusere kontinuerlig funksjon φ(x) for tilnærming av diskret avhengighet f(x Jeg ) , i = 0... n. Det vil vi anta φ(x) bygget etter tilstanden beste kvadratiske tilnærming, Hvis
. (1)
Vekt ρ Til Jeg-poeng gir mening til målenøyaktighet gitt verdi: jo mer ρ , jo nærmere den tilnærmede kurven er "tiltrukket" et gitt punkt. I det følgende vil vi anta som standard ρ = 1 for alle poeng.
Vurder saken lineær tilnærming:
φ(x) = c 0 φ 0 (x) + c 1 φ 1 (x) + … + c m φ m (x), (2)
Hvor φ 0 …φ m- vilkårlig basisfunksjoner, c 0 …c m- ukjente koeffisienter, m < n. Hvis vi tar antall tilnærmingskoeffisienter lik tallet noder, vil rot-middel-kvadrat-tilnærmingen falle sammen med Lagrange-interpolasjon, mens, hvis beregningsfeilen ikke tas i betraktning, Q = 0.
Hvis den eksperimentelle (initielle) datafeilen er kjent ξ , deretter valget av antall koeffisienter, det vil si verdien m, bestemmes av betingelsen:
Med andre ord, hvis , antall tilnærmingskoeffisienter er ikke nok til å reprodusere grafen riktig eksperimentell avhengighet. Hvis , vil mange koeffisienter i (2) ikke ha fysisk betydning.
For å løse det lineære tilnærmingsproblemet i generell sak det er nødvendig å finne betingelsene for minimum av summen av kvadrerte avvik for (2). Problemet med å finne et minimum kan reduseres til problemet med å finne roten til et ligningssystem, k = 0…m. (4) .
Å erstatte (2) i (1) og deretter beregne (4) vil til slutt føre til neste system lineær algebraisk ligninger:
Deretter bør du løse den resulterende SLAE med hensyn til koeffisientene c 0 …c m. For å løse SLAE-er, kompileres vanligvis en utvidet matrise av koeffisienter, som kalles Gram matrise, hvis elementer er prikkprodukter basisfunksjoner og frie koeffisienter kolonne:
,
Hvor 
, 
, j = 0... m, k = 0…m.
Etter å ha brukt for eksempel Gaussmetoden finner man koeffisientene c 0 …c m, kan du bygge en tilnærmet kurve eller beregne koordinatene gitt poeng. Dermed er tilnærmingsproblemet løst.
Tilnærming ved kanonisk polynom.
La oss velge basisfunksjonene i form av en rekke potenser av argumentet x:
φ 0 (x) = x 0 = 1; φ 1 (x) = x 1 = x; φm(x) = x m, m < n.
Den utvidede Gram-matrisen for en kraftbasis vil se slik ut:
Det særegne ved beregningene av en slik matrise (for å redusere antall utførte handlinger) er at det er nødvendig å telle bare elementene i den første raden og de to siste kolonnene: de resterende elementene fylles ved å flytte den forrige raden (med unntak av de to siste kolonnene) en posisjon til venstre. I noen programmeringsspråk der det ikke er noen rask prosedyre for eksponentiering, er algoritmen for beregning av grammatrisen, presentert nedenfor, nyttig.
Valg av basisfunksjoner i form av potenser x er ikke optimalt fra synspunktet om å oppnå den minste feilen. Dette er en konsekvens ikke-ortogonalitet utvalgte basisfunksjoner. Eiendom ortogonalitet er at for hver type polynom er det et segment [ x 0, x n], der skalarproduktene til polynomer av forskjellige rekkefølger forsvinner:
, j ≠ k, ρ– noe vektfunksjon.
Hvis basisfunksjonene var ortogonale, ville alle ikke-diagonale elementer i Gram-matrisen være nær null, noe som ville øke nøyaktigheten til beregningene, ellers når determinanten til Gram-matrisen veldig raskt tenderer til null, dvs. systemet blir dårlig kondisjonert.
Tilnærming ved ortogonale klassiske polynomer.
Polynomene nedenfor knyttet til Jacobi-polynomer, har egenskapen ortogonalitet i betydningen beskrevet ovenfor. Det vil si å oppnå høy presisjon beregninger, anbefales det å velge basisfunksjoner for tilnærming i form av disse polynomene.
Tilnærming (fra latin "tilnærmet" - "å komme nærmere") er et tilnærmet uttrykk for alle matematiske objekter (for eksempel tall eller funksjoner) gjennom andre som er enklere, mer praktisk å bruke, eller rett og slett bedre kjent. I vitenskapelig forskning brukes tilnærming for å beskrive, analysere, generalisere og videre bruke empiriske resultater.
Som kjent kan det være en eksakt (funksjonell) sammenheng mellom størrelser når én verdi av argumentet tilsvarer én bestemt verdi.
Ved valg av tilnærming bør man ta utgangspunkt i det konkrete forskningsproblemet. Vanligvis, jo enklere ligningen som brukes for tilnærming, jo mer tilnærmet blir den resulterende beskrivelsen av forholdet. Derfor er det viktig å lese hvor betydelig og hva som forårsaker avvikene til spesifikke verdier fra den resulterende trenden. Når man beskriver avhengigheten av empirisk bestemte verdier, kan mye større nøyaktighet oppnås ved å bruke en mer kompleks ligning med flere parametere. Det er imidlertid ingen vits i å strebe etter å formidle tilfeldige verdiavvik i spesifikke serier av empiriske data med maksimal nøyaktighet. Når han velger en tilnærmingsmetode, inngår forskeren alltid et kompromiss: han bestemmer i hvilken grad det i dette tilfellet er tilrådelig og hensiktsmessig å "ofre" detaljer og følgelig hvor generelt avhengigheten til de sammenlignede variablene skal uttrykkes. Sammen med å identifisere mønstre maskert av tilfeldige avvik av empiriske data fra det generelle mønsteret, gjør tilnærming det også mulig å løse mange andre viktige problemer: formalisere den funnet avhengigheten; finne ukjente verdier for den avhengige variabelen ved interpolasjon eller, hvis det er hensiktsmessig, ekstrapolering.
Formålet med dette kursarbeidet er å studere teoretiske grunnlag tilnærme den tabellerte funksjonen ved å bruke minste kvadraters metode, og bruke teoretisk kunnskap, finne tilnærmende polynomer. Å finne approksimerende polynomer innenfor rammen av dette kursarbeidet bør gjøres ved å skrive et program i Pascal som implementerer den utviklede algoritmen for å finne koeffisientene til det approksimerende polynomet, og også løse det samme problemet ved hjelp av MathCad.
I dette kursarbeidet er programmet i Pascal-språket utviklet i PascalABC-skallet versjon 1.0 beta. Problemet ble løst i MathCad-miljøet ved hjelp av Mathcad versjon 14.0.0.163.
Formulering av problemet
I dette kurset må du fullføre følgende:
1. Utvikle en algoritme for å finne koeffisientene til tre tilnærmede polynomer (polynomer) av formen
for tabellfunksjon y=f(x):for grad av polynomer n=2, 4, 5.
2. Konstruer et blokkskjema av algoritmen.
3. Lag et program i Pascal som implementerer den utviklede algoritmen.
5. Konstruer grafer av de 3 oppnådde tilnærmelsesfunksjonene i ett koordinatsystem. Grafen skal også inneholde startpunktene (X Jeg , y jeg ) .
6. Løs problemet ved hjelp av MathCAD.
Resultatene av å løse problemet ved å bruke det opprettede programmet i Pascal-språket og i MathCAD-miljøet må presenteres i form av tre polynomer konstruert ved hjelp av de funnet koeffisientene; en tabell som inneholder verdiene til funksjonen i punktene xi og standardavvik oppnådd ved å bruke de funnet polynomene.
Konstruksjon av empiriske formler ved bruk av minste kvadraters metode
Svært ofte, spesielt når man analyserer empiriske data, er det behov for å eksplisitt finne et funksjonelt forhold mellom verdiene x og y, som ble oppnådd som et resultat av målinger.
I en analytisk studie av forholdet mellom to størrelser x og y, gjøres en serie observasjoner og resultatet er en verditabell:
| x | ¼ | ¼ | ||||
| y | ¼ | ¼ |
Denne tabellen er vanligvis oppnådd som et resultat av noen eksperimenter der
Oversettelse av resultater fra Unified State Exam på russisk
Hovedtyper av miljøpåvirkning - Kunnskapshypermarked Eksempler på miljøforhold
Slagskip av keiserinne Maria-klassen