Dela via

Statistiska Excel-funktioner: TILLVÄXT

  • Gäller för: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Excel 2004 for Mac

Sammanfattning

Den här artikeln beskriver funktionen GROWTH i Excel, illustrerar hur funktionen används och jämför resultatet av funktionen för Excel 2003 och för senare versioner av Excel med resultatet av TILLVÄXT i tidigare versioner av Excel. TILLVÄXT utvärderas genom att anropa den relaterade funktionen LINEST. Omfattande ändringar av LINEST för Excel 2003 och senare versioner av Excel sammanfattas och deras konsekvenser för TILLVÄXT noteras.

Microsoft Excel 2004 för Macintosh-information

De statistiska funktionerna i Excel 2004 för Mac uppdaterades med samma algoritmer som användes för att uppdatera de statistiska funktionerna i Excel 2003 och i senare versioner av Excel. All information i den här artikeln som beskriver hur en funktion fungerar eller hur en funktion har ändrats för Excel 2003 eller senare versioner av Excel gäller även för Excel 2004 för Mac.

Mer information

Funktionen GROWTH(known_y's, known_x's, new_x's, constant) används för att utföra en regressionsanalys där en exponentiell kurva monteras. Ett kriterium med minst kvadrat används, och GROWTH försöker hitta den bästa passformen enligt det kriteriet. Known_y representerar data på den "beroende variabeln" och known_x representerar data på en eller flera "oberoende variabler". I growth-hjälpfilen beskrivs sällsynta fall där det andra eller tredje argumentet kan utelämnas.

Förutsatt att det finns p-förutsägelsevariabler anropar GROWTH i princip LOGEST. LOGEST anpassar en ekvation av formen:

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

Värdena för koefficienterna, b, m1, m2, ..., mp bestäms som passar bäst för y-data.

Om det sista argumentet "konstant" är inställt på TRUE vill du att regressionsmodellen ska inkludera multiplicativ koefficient b i regressionsmodellen. Om värdet är FALSE undantas b genom att det i princip anges till 1. Det sista argumentet är valfritt. om argumentet utelämnas tolkas det som SANT.

För att underlätta utläggningen i resten av den här artikeln förutsätter du att data ordnas i kolumner så att known_y är en kolumn med y-data och known_x är en eller flera kolumner med x data. Dimensionerna (längderna) för var och en av dessa kolumner måste vara lika med varandra. New_x antas också vara ordnade i kolumner och det måste finnas samma antal kolumner för new_x som för known_x. Alla våra observationer nedan är lika sanna om data inte är ordnade i kolumner, men det är lättare att diskutera det enskilda (oftast använda) fallet.

När du har beräknat den bästa regressionsmodellen (genom att i princip anropa Excels LOGEST-funktion) returnerar GROWTH förutsagda värden som är associerade med new_x.

Den här artikeln använder exempel för att visa hur TILLVÄXT relaterar till LOGEST och för att peka på problem med LOGEST i versioner av Excel som är tidigare än Excel 2003 och som leder till problem med TILLVÄXT. GROWTH anropar effektivt LOGEST, kör LOGEST, använder regressionskoefficienter i LOGEST-utdata i sin beräkning av förutsagda y-värden som är associerade med varje rad i new_x och presenterar den här kolumnen med förutsagda y-värden för dig. Därför måste du känna till problem vid utförandet av LOGEST. När LOGEST anropas anropas i sin tur LINEST. Även om koden för GROWTH och LOGEST inte har skrivits om för Excel 2003 och för senare versioner av Excel, har omfattande ändringar (och förbättringar) i LINEST-kod gjorts.

Som tillägg till den här artikeln rekommenderas följande artikel om LINEST starkt. Den innehåller flera exempel och dokumentproblem med LINEST i versioner av Excel som är tidigare än Excel 2003.

Om du vill ha mer information om LINEST väljer du följande artikelnummer för att visa artikeln i Microsoft Knowledge Base:

828533 Beskrivning av funktionen LINEST i Excel 2003 och Excel 2004 för Mac

Linest-hjälpfilen, som har reviderats för Excel 2003, rekommenderas också.

I följande artikel om LOGEST förklaras hur LOGEST interagerar med LINEST. Den här informationen utelämnas här.

Mer information finns i Statistiska Excel-funktioner: LOGEST.

Eftersom fokus i den här artikeln ligger på numeriska problem i versioner av Excel som är tidigare än Excel 2003, har den här artikeln inte många praktiska exempel på användningen av TILLVÄXT. Hjälpfilen i GROWTH innehåller användbara exempel.

Syntax

GROWTH(known_y's, known_x's, new_x's, constant)

Argumenten, known_y, known_x och new_x måste vara matriser eller cellområden som har relaterade dimensioner. Om known_y's är en kolumn med m rader, är known_x's c kolumner med m rader där c är större än eller lika med ett. C är antalet förutsägelsevariabler. m är antalet datapunkter. New_x måste sedan vara c kolumner och r rader där r är större än eller lika med en. (Liknande relationer i dimensioner måste gälla om data anges i rader i stället för kolumner.) Konstant är ett logiskt argument som måste anges till TRUE eller FALSE (eller 0 eller 1, vilket Excel tolkar som FALSE respektive TRUE). De tre sista argumenten för TILLVÄXT är alla valfria. se tillväxthjälpfilen för alternativ för att utelämna det andra argumentet, det tredje argumentet eller båda; utelämna det fjärde argumentet tolkas som SANT.

Den vanligaste användningen av GROWTH innehåller två cellområden som innehåller data, till exempel GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Eftersom det vanligtvis finns fler än en förutsägelsevariabel innehåller det andra argumentet i det här exemplet flera kolumner. I det här exemplet finns det 100 ämnen, ett beroende variabelvärde (known_y) för varje ämne och fem beroende variabelvärden (known_x) för varje ämne. Det finns ytterligare åtta hypotetiska ämnen där du vill använda TILLVÄXT för att beräkna förutsagda y-värden.

Exempel på användning

Ett Excel-kalkylbladsexempel tillhandahålls för att illustrera följande viktiga begrepp:

  • Hur TILLVÄXT interagerar med LOGEST
  • Problem som uppstår med TILLVÄXT (eller LOGEST och LINEST) på grund av kollinära "known_x" i versioner av Excel som är äldre än Excel 2003.

Anmärkning

En omfattande diskussion om det andra punktobjektet i samband med LINEST finns i artikeln om LINEST.

För att illustrera funktionen GROWTH skapar du ett tomt Excel-kalkylblad, kopierar följande tabell, väljer cell A1 i ditt tomma Excel-kalkylblad och klistrar sedan in posterna så att tabellen efter fyller cellerna A1:K35 i kalkylbladet.

A B C D E F G H Jag J K
y: Xs:
=EXP(F2) 1 2 1 1
=EXP(F3) 3 4 1 2
=EXP(F4) 4 5 1 3
=EXP(F5) 6 7 1 4
=EXP(F6) 7 8 1 5
nya x: 9 11
12 14
TILLVÄXT med kolumnerna B,C: Värden för Excel 2002 och tidigare versioner av Excel:
Värden för Excel 2003 och för senare versioner av Excel:
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 472,432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 3400.16400895377
Tillväxt med endast kolumn B
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE) 472,432432563203 472,432432563203
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE) 3400.16400895377 3400.16400895377
Anpassade värden från LOGEST resulterar i Excel 2003 och senare versioner av Excel
Använd kolumner B, C Använda kolumn B
=EXP(LN(K24)*1 + LN(J24)*B7 + LN(I24)*C7) =EXP(LN(J31)*1 + LN(I31)*B7)
=EXP(LN(K24)*1 + LN(J24)*B8 + LN(I24)*C8) =EXP(LN(J31)*1 + LN(I31)*B8)
LOGEST med kolumnerna B,C: Värden för Excel 2002 och tidigare versioner av Excel: Värden för Excel 2003 och för senare versioner av Excel:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 1 1.9307233720034 1,26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 225 3 #N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LOGEST med endast kolumn B
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 1.9307233720034 1,26724101129183 1.9307233720034 1,26724101129183
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 224.999999999999 3 225 3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421

Anmärkning

När du har klistrat in den här tabellen i det nya Excel-kalkylbladet väljer du knappen Klistra in alternativ och väljer sedan Matcha målformatering. När det inklistrade intervallet fortfarande är valt använder du någon av följande procedurer, beroende på vilken version av Excel du kör:

  • I Microsoft Office Excel 2007 väljer du fliken Start , väljer Format i gruppen Celler och väljer sedan Autoanpassa kolumnbredd.
  • I Excel 2003 pekar du på Kolumn på menyn Format och väljer sedan Autopassning för markering.

Data för TILLVÄXT finns i cellerna A1:C8. (Poster i celler D2:D6 är inte en del av data, men används som illustration nedan.) Resultat av TILLVÄXT för två olika modeller för båda tidigare versioner av Excel och för senare versioner av Excel presenteras i cellerna E10:E16 respektive I10:116. Resultat i celler A10:A16 motsvarar den version av Excel som du använder. För tillfället fokuserar du på resultaten för Excel 2003 och för senare versioner av Excel när du undersöker hur GROWTH anropar LOGEST och hur TILLVÄXT använder LOGEST-resultat.

TILLVÄXT och LOGEST kan ses som interagerande i följande steg:

  1. Du använder GROWTH(known_y's, known_x's, new_x's, constant)
  2. TILLVÄXT anropar LOGEST(known_y, known_x, konstant, TRUE)
  3. Regressionskoefficienter från det här anropet till LOGEST hämtas. Dessa koefficienter visas på den första raden i LOGEST:s utdatatabell.
  4. För varje new_x rad beräknas det förutsagda y-värdet baserat på dessa LOGEST-koefficienter och new_x värden på den raden.
  5. Det beräknade värdet i steg 4 returneras i den lämpliga cellen för TILLVÄXT-resultat som motsvarar den new_x:s rad.

Om TILLVÄXT ska returnera lämpliga resultat måste LOGEST generera lämpliga resultat i steg 3. Eftersom utvärderingen av LOGEST i steg 3 kräver ett anrop till LINEST är det viktigt att LINEST fungerar väl. Problem med LINEST i versioner av Excel som är tidigare än Excel 2003 kommer från kolumner med kollineära prediktorer. (Det finns andra problem med LINEST och LOGEST i de tidigare versionerna av Excel som uppstår när det sista argumentet till TILLVÄXT är inställt på FALSE. Dessa problem påverkar dock inte resultatet av TILLVÄXT och beskrivs inte här.)

Förutsägelsekolumner (known_x) är kolliniska om minst en kolumn, c, kan uttryckas som en summa av flera av andra, c1, c2 och andra kolumner. Kolumn c kallas ofta redundant eftersom informationen som den innehåller kan konstrueras från kolumnerna c1, c2 och andra kolumner. Den grundläggande principen i förekomsten av kollinearitet är att resultaten inte ska påverkas av huruvida en redundant kolumn ingår i de ursprungliga data eller tas bort från de ursprungliga data. Eftersom LINEST i versioner av Excel som är tidigare än Excel 2003 inte sökte efter collinearity, överträddes den här principen lätt. Prediktorkolumner är nästan kolliniska om minst en kolumn, c, kan uttryckas som nästan lika med en summa av multiplar av andra, c1, c2 och andra kolumner. I det här fallet är "nästan lika" en liten summa av kvadratavvikelser för poster i c från motsvarande poster i den viktade summan av c1, c2 och andra kolumner. "Mycket liten" kan till exempel vara mindre än 10^(-12).

Den första modellen, i raderna 10 till 12, använder kolumnerna B och C som prediktorer och begär att Excel modellerar konstanten (det sista argumentet är inställt på TRUE). Excel infogar sedan effektivt ytterligare en förutsägelsekolumn som ser ut precis som cellerna D2:D6. Det är lätt att märka att poster i kolumn C i rad 2 till 6 är exakt lika med summan av motsvarande poster i kolumnerna B och D. Därför finns det kollinearitet eftersom kolumn C är en summa av multiplar av följande objekt:

  • Kolumn B
  • Excels ytterligare kolumn med ettor som infogas eftersom det tredje argumentet till LOGEST utelämnades eller var SANT (det "normala" fallet)

Detta orsakar sådana numeriska problem som versioner av Excel som är tidigare än Excel 2003 inte kan beräkna resultat. Därför fylls utdatatabellen GROWTH med #NUM!.

Den andra modellen, i raderna 14 till 16, är en modell som alla versioner av Excel kan hantera. Det finns ingen enhetlighet och användaren begär återigen att Excel ska modellera konstanten. Den här modellen ingår här av följande skäl:

  • För det första är det mest typiskt för praktiska fall: att det inte finns någon kollinearitet närvarande. Dessa fall hanteras tillräckligt i alla versioner av Excel. Det bör vara betryggande att veta att numeriska problem sannolikt inte kommer att uppstå i det vanligaste praktiska fallet om du har en tidigare version av Excel.
  • För det andra används det här exemplet för att jämföra beteendet för Excel 2003 och senare versioner av Excel i de två modellerna. De flesta större statistiska paket analyserar kollinearitet, tar bort en kolumn som är en summa av flera andra från modellen och varnar användaren med ett meddelande som "kolumn C är linjärt beroende av andra förutsägelsekolumner och har tagits bort från analysen".

I Excel 2003 och i senare versioner av Excel förmedlas ett sådant meddelande inte i en avisering eller i en textsträng, utan i LOGEST-utdatatabellen. TILLVÄXT har ingen mekanism för att leverera ett sådant meddelande till användaren. I logest-utdatatabellen motsvarar en regressionskoefficient som är en, och vars standardfel är noll, en koefficient för en kolumn som har tagits bort från modellen. LOGEST-utdatatabeller ingår i raderna 23 till 35 som motsvarar TILLVÄXT-utdata i raderna 10 till 16. Posterna i cellerna I24:I25 visar en borttagen redundant prediktorkolumn. I det här fallet valde LOGEST att ta bort kolumn C (koefficienter i cellerna I24, J24, K24 motsvarar kolumnerna C, B respektive Excels konstanta kolumn). Om det finns kollinearitet kan någon av de berörda kolumnerna tas bort och valet är godtyckligt.

I den andra modellen i raderna 30 till 35 finns det ingen kollinearitet och ingen kolumn har tagits bort. Du kan se att de förutsagda y-värdena är desamma i båda modellerna. Det här problemet beror på att borttagning av en redundant kolumn som är en summa av flera av andra inte minskar lämpligheten i den resulterande modellen. Sådana kolumner tas bort just för att de inte representerar något värde när man försöker hitta den bästa kvadratanpassningen. Om du undersöker LOGEST-utdata i celler I23:K35 i Excel 2003 och i senare versioner av Excel ser du också att de tre sista raderna i utdatatabellerna är desamma. Dessutom sammanfaller posterna i cellerna I31:J32 och cellerna J24:K25. Det visar att samma resultat erhålls när kolumn C ingår i modellen, men visar sig vara redundant (utdata i celler I24:K28) som när kolumn C eliminerades innan LOGEST kördes (utdata i celler I31:J35). Detta uppfyller den grundläggande principen i förekomsten av kollinearitet.

I celler A18:C21 använder Microsoft data från Excel 2003 och från senare versioner av Excel för att illustrera hur GROWTH tar LOGEST-utdata och beräknar relevanta förutsagda y-värden. Genom att undersöka formler i cellerna A20:A21 och cellerna C20:C21 kan du se hur LOGEST-koefficienter kombineras med new_x data i cellerna B7:C8 för var och en av de två modellerna (med kolumnerna B, C som prediktorer; endast kolumn B som prediktor).

Kollinearitet identifieras i LOGEST i Excel 2003 och i senare versioner av Excel eftersom LOGEST anropar LINEST. LINEST använder en annan metod för att lösa regressionskoefficienterna. Den här metoden är QR-nedbrytning. LINEST-artikeln innehåller en genomgång av QR-dekompositionsalgoritmen för ett litet exempel.

Sammanfattning av resultat i tidigare versioner av Excel

Tillväxtresultat påverkas negativt i versioner av Excel som är tidigare än Excel 2003 på grund av felaktiga resultat i LOGEST som i sin tur härrör från felaktiga resultat i LINEST.

LINEST beräknades med hjälp av en metod som inte ägnade någon uppmärksamhet åt problem med kollinearitet. Förekomsten av kollinearitet orsakade avrundningsfel, olämpliga standardfel i regressionskoefficienter och olämpliga frihetsgrader. Ibland är avrundningsproblem tillräckligt allvarliga för att LINEST ska fylla utdatatabellen med #NUM!. Om du, som i de flesta fall i praktiken, kan vara säker på att det inte fanns kolumner med kollinära (eller nästan kollinära) prediktorer, skulle LINEST i allmänhet ge acceptabla resultat. Därför kan användare av GROWTH försäkras på liknande sätt om de kan se avsaknaden av kollinära (eller nästan kollinära) förutsägelsekolumner.

Sammanfattning av resultat i Excel 2003 och senare versioner av Excel

Förbättringar i LINEST omfattar växling till QR-nedbrytningsmetoden för att fastställa regressionskoefficienter. QR-nedbrytning har följande fördelar:

  • Bättre numerisk stabilitet (vanligtvis mindre avrundningsfel)
  • Analys av problem med multikollinearitet

Alla problem med versioner av Excel som är tidigare än Excel 2003 som illustreras i den här artikeln har korrigerats för Excel 2003 och för senare versioner av Excel. Dessa förbättringar i LINEST översätts till förbättringar i LOGEST och TILLVÄXT.

Slutsatser

GROWTHs prestanda har förbättrats eftersom LINEST har förbättrats avsevärt för Excel 2003 och för nyare versioner av Excel. Förbättringar i LINEST påverkar även LOGEST, eftersom LOGEST anropas av GROWTH. Användare av tidigare versioner av Excel bör kontrollera att prediktorkolumner inte är kollinära innan de använder GROWTH.

Mycket av det material som presenteras i den här artikeln och i LINEST-artikeln kan först verka larm för användare av versioner av Excel som är tidigare än Excel 2003. Det bör dock noteras att kollinearitet är ett problem i endast en liten procentandel av fallen. Tidigare versioner av Excel ger acceptabla TILLVÄXT-resultat när det inte finns någon kollinearitet.

Lyckligtvis påverkar förbättringar i LINEST även Analysis ToolPaks linjära regressionsverktyg (det här verktyget anropar LINEST) och två andra relaterade Excel-funktioner: LOGEST och TREND.