Excel statistiska funktioner: GROWTH

Anteckning

Office 365 ProPlus byter namn till Microsoft 365-appar för företag. Mer information om den här ändringen finns i det här blogginlägget.

Sammanfattning

I den här artikeln beskrivs funktionen TILLVÄXT i Microsoft Office Excel 2003 och i senare versioner av Excel, hur funktionen används och funktionens resultat för Excel 2003 och för senare versioner av Excel jämförs med resultat av FUNKTIONEN TILLVÄXT i tidigare versioner av Excel. FUNKTIONEN GROWTH utvärderas genom att anropa den relaterade funktionen, LINEST. Omfattande ändringar av LINEST för Excel 2003 och för senare versioner av Excel sammanfattas och deras konsekvenser för TILLVÄXT observeras.

Microsoft Excel 2004 för Macintosh

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

Mer information

Funktionen FUNKTIONEN KNOWN_Y, known_x, new_x, konstant används för att utföra en regressionsanalys där en exponentiell kurva är anpassad. Ett villkor med minst kvadrater används och TILLVÄXT försöker hitta den bästa passningen under det villkoret. Known_y representera data på den "beroende variabeln" och known_x representera data på en eller flera "oberoende variabler". I filen GROWTH Help beskrivs sällsynta fall där det andra eller tredje argumentet kan utelämnas.

Om vi utgår från att det finns p prediktörsvariabler, så anropar TILLVÄXT i princip LOGEST. LOGEST passar in en ekvation i formuläret:

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

Koefficientens värden, b, m1, m2, ..., mp bestäms som bäst passar y-data.

Om det sista argumentet "konstant" är inställt på SANT vill du att regressionsmodellen ska inkludera den multiplicativa koefficienten b i regressionsmodellen. Om detta är inställt på FALSKT utesluts b genom att i princip ange det till 1. Det sista argumentet är valfritt. om argumentet utelämnas tolkas det som SANT.

För att göra det lättare att flytta informationen i resten av den här artikeln förutsätter du att data är ordnade i kolumner så att known_y är en kolumn med y-data och known_x-data är en eller flera kolumner med x-data. Naturligtvis måste dimensionerna (längderna) för var och en av dessa kolumner vara lika. New_x antas också vara ordnade i kolumner och det måste finnas samma antal kolumner för new_x-kolumner som för known_x kolumner. Alla våra observationer nedan är lika sanna om data inte är ordnade i kolumner, men det är enklare att diskutera det här enstaka (vanligast) fallet.

När du har beräknat den regressionsmodell som passar bäst (genom att i princip anropa Excel:s LOGEST-funktion) returnerar FUNKTIONEN TILLVÄXT förutsagda värden som är new_x-värden.

I den här artikeln används 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 som omvandlar till problem med TILLVÄXT. GROWTH effectively calls LOGEST, executes LOGEST, uses regression coefficients in LOGEST output in its calculation of predicted y values that are associated with each row of new_x's, and presents this column of predicted y values to you. Därför måste du känna till problem med körningen av LOGEST. När LOGEST anropas, anropas LINEST i sin tur effektivt. Även om kod 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. Den innehåller flera exempel och dokumentproblem med RADLINJER i versioner Excel som är tidigare än Excel 2003.

Om du vill ha mer information om LINEST klickar du på följande artikelnummer för att visa artikeln i Microsoft Knowledge Base:

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

Hjälpfilen FÖR LINEST rekommenderas också, enligt Excel 2003.

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

Om du vill ha mer information klickar du på följande artikelnummer för att visa artikeln i Microsoft Knowledge Base:

828528 Excel statistiska funktioner: LOGEST

Eftersom fokus i den här artikeln ligger på numeriska problem i tidigare versioner av Excel än Excel 2003, innehåller den här artikeln inte många praktiska exempel på användning av GROWTH. 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 kolumner är en kolumn efter m rader är c known_x kolumnerna efter m rader där c är större än eller lika med en. C är antalet prediktörsvariabler; m är antalet datapunkter. New_x kolumner måste sedan vara c-kolumner efter r rader där är större än eller lika med en. (Liknande relationer i dimensioner måste innehålla om data anges i rader i stället för i kolumner.) Konstant är ett logiskt argument som måste ställas in på SANT eller FALSKT (eller 0 eller 1 som Excel som FALSKT respektive SANT). De tre sista argumenten för TILLVÄXT är alla valfria. i hjälpfilen GROWTH om du vill ha alternativ för att utelämna det andra argumentet, det tredje argumentet eller båda Om du utelämnar det fjärde argumentet tolkas det som SANT.

Den vanligaste användningen av GROWTH omfattar två cellområden som innehåller data, t.ex. TILLVÄXT(A1:A100, B1:F100, B101:F108, SANT). Eftersom det oftast finns fler än en predikatorvariabel 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 variabla värden (known_x) för varje ämne. Det finns åtta ytterligare försöksä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 av kalkylblad ges som illustrerar följande viktiga begrepp:

  • Hur GROWTH interagerar med LOGEST
  • Problem som uppstår med GROWTH (eller LOGEST och LINEST) på grund av kolinjärer known_x i tidigare versioner av Excel än Excel 2003

Anteckning

En omfattande diskussion av det andra punktobjektet i sammanhanget med RADLINJER finns i artikeln om RADLINJER.

Du illustrerar funktionen TILLVÄXT genom att skapa ett tomt Excel-kalkylblad genom att kopiera följande tabell, markera cell A1 i det tomma kalkylbladet i Excel och sedan klistra in posterna så att tabellen nedan fyller cellerna A1:K35 i kalkylbladet.

A B C D E F G H I J K
y: x:
=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 kol B,C: Värden för Excel 2002 och för tidigare versioner Excel:
Värden för Excel 2003 och för senare versioner av Excel:
=TILLVÄXT(A2:A6;B2:C6;B7:C8;SANT) #NUM! 472.432432563203
=TILLVÄXT(A2:A6;B2:C6;B7:C8;SANT) #NUM! 3400.16400895377
GROWTH using col B only
=TILLVÄXT(A2:A6;B2:B6;B7:B8;SANT) 472.432432563203 472.432432563203
=TILLVÄXT(A2:A6;B2:B6;B7:B8;SANT) 3400.16400895377 3400.16400895377
Värdena från LOGEST resulterar i Excel 2003 och i senare versioner av Excel
Använda kolen B, C Använda kol 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 kol B,C: Värden för Excel 2002 och för tidigare versioner Excel: Värden för Excel 2003 och för senare versioner av Excel:
=LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) #NUM! #NUM! #NUM! 1 1.9307233720034 1.26724101129183
=LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) #NUM! #NUM! #NUM! 225 3 #N/A
=LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) =LOGEST(A2:A6;B2:C6;SANT;SANT) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LOGEST med endast kolumn B
=LOGEST(A2:A6;B2:B6;SANT;SANT) =LOGEST(A2:A6;B2:B6;SANT;SANT) 1.9307233720034 1.26724101129183 1.9307233720034 1.26724101129183
=LOGEST(A2:A6;B2:B6;SANT;SANT) =LOGEST(A2:A6;B2:B6;SANT;SANT) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=LOGEST(A2:A6;B2:B6;SANT;SANT) =LOGEST(A2:A6;B2:B6;SANT;SANT) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=LOGEST(A2:A6;B2:B6;SANT;SANT) =LOGEST(A2:A6;B2:B6;SANT;SANT) 224.999999999999 3 225 3
=LOGEST(A2:A6;B2:B6;SANT;SANT) =LOGEST(A2:A6;B2:B6;SANT;SANT) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421

Anteckning

När du har klistrat in tabellen i Excel klickar du på knappen Inklistringsalternativ och sedan på Matcha målformatering. När det klistrade området fortfarande är markerat använder du en av följande procedurer, efter vad som är lämpligt för den version Excel du kör:

  • I Microsoft Office Excel 2007 klickar du på fliken Start, sedan på Format i gruppen Celler och sedan på Autopassa kolumnbredd.
  • I Excel 2003 pekar du Kolumn på Format-menyn och klickar sedan på Autopassa markering.

Data för TILLVÄXT finns i cellerna A1:C8. (Posterna i cellerna D2:D6 är inte en del av dessa data, utan används som illustration nedan.) Resultat av GROWTH för två olika modeller för både tidigare versioner av Excel och för senare versioner av Excel presenteras i cellerna E10:E16 respektive I10:116. Resultatet i cellerna A10:A16 motsvarar den version Excel som du använder. Nu kan du fokusera på resultaten för Excel 2003 och till senare versioner av Excel när du undersöker hur GROWTH anropar LOGEST och hur GROWTH använder LOGEST-resultat.

DU kan se TILLVÄXT och LOGEST som interagerar i följande steg:

  1. Du kallar tillväxt(known_y, known_x, new_x, konstant)
  2. GROWTH-anrop till LOGEST(known_y,known_x, konstant, SANT)
  3. Regressionskoefficienter från detta anrop till LOGEST inhämtas. Dessa koefficienter visas i den första raden i LOGEST-utdatatabellen.
  4. För varje new_x rad beräknas det förutsagda y-värdet utifrån dessa LOGEST-koefficienter och new_x-värdena på den raden.
  5. Det beräknade värdet i steg 4 returneras i lämplig cell för GROWTH-utdata som motsvarar den new_x raden.

Om TILLVÄXT ska returnera rätt resultat måste LOGEST generera rätt resultat i steg 3. Eftersom utvärderingen av FUNKTIONEN LOGEST i steg 3 kräver ett anrop till LINEST är det viktigt att LINEST fungerar bra. Problem med RADLINJER i Excel som är tidigare än Excel 2003 kommer från kolinjär prognoskolumner. (Det finns andra problem med LINEST och LOGEST i tidigare versioner Excel som inträffar när det sista argumentet till TILLVÄXT är inställt på FALSKT. Men dessa problem påverkar inte resultaten av TILLVÄXT, och diskuteras inte här.)

Prediktörskolumner (known_x) är kolinjära om minst en kolumn, c, kan uttryckas som en summa av multiplar av andra, c1, c2 och andra kolumner. Kolumn c kallas ofta redundant eftersom informationen som den innehåller kan vara uppbyggd från kolumnerna c1, c2 och andra kolumner. Den grundläggande principen vid förekomst av kolinjäritet är att resultaten inte ska påverkas av om en redundant kolumn ingår i de ursprungliga data eller tas bort från originaldata. Eftersom RADLINJER i tidigare Excel än 2003 Excel inte efter kolinjäritet var den principen enkel att bryta mot. Predikatorkolumner är nästan kolinjära 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 innebär "nästan lika" en liten summa kvadrat på avvikelser av posterna i c från motsvarande poster i den viktade summan av c1, c2 och andra kolumner. "Mycket litet" kan till exempel vara mindre än 10^(-12).

I den första modellen, i raderna 10 till 12, används kolumnerna B och C som prediktörer och förfrågningar Excel att modellera konstanten (det sista argumentet är inställt på SANT). Excel infogar sedan en ytterligare predikatorkolumn som ser ut precis som cellerna D2:D6. Det är lätt att märka att posterna i kolumn C i rad 2 till och med 6 är exakt lika med summan av motsvarande poster i kolumn B och D. Därför finns det kolinjäritet eftersom kolumn C är en summa av multiplar av följande objekt:

  • Kolumn B
  • Excel andra kolumnen med 1:ar som infogas eftersom det tredje argumentet till LOGEST har utelämnats eller SANT (det "normala" fallet)

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

Den andra modellen, i rad 14 till 16, är den som alla versioner av Excel kan hantera. Det finns ingen kolinjäritet och användaren begär återigen Excel att modellera konstanten. Den här modellen finns med här av följande anledningar:

  • För det första är det mest typiskt för praktiska ärenden: att det inte finns någon kolinjäritet. Ärendena hanteras tillräckligt i alla versioner av Excel. Den bör återkövas för att vara säker på att numeriska problem inte sannolikt förekommer i det vanligaste praktiska fallet om du har en tidigare version av Excel.
  • Det andra exemplet används för att jämföra beteendet hos Excel 2003 och senare versioner av Excel i de två modellerna. De flesta större statistiska paket analyserar kolinjäritet, tar bort en kolumn som är en summa av multiplar av andra från modellen och ger användaren ett meddelande, till exempel att kolumn C är linjärt beroende av andra predikatorkolumner 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 utdatatabellen LOGEST. GROWTH har ingen mekanism för att leverera ett sådant meddelande till användaren. I utdatatabellen LOGEST motsvarar en regressionskoefficient en och vars standardfel är noll en koefficient för en kolumn som har tagits bort från modellen. LOGEST-utdatatabellerna ingår i raderna 23 till 35 som motsvarar UTDATA FÖR TILLVÄXT i raderna 10 till 16. Posterna i cellerna I24:I25 visar en överflödig redundant predikatorkolumn. I det här fallet valde LOGEST att ta bort kolumn C (koefficienterna i cellerna I24, J24, K24 motsvarar kolumn C, B och Excel konstantkolumn respektive). Om det finns kolinjäritet kan någon av kolumnerna tas bort och valet är godtyckligt.

I den andra modellen, i rad 30 till och med 35, finns det ingen kolinjäritet och ingen kolumn borttagen. Du kan se att de förutsagda y-värdena är desamma i båda modeller. Det här problemet uppstår eftersom borttagning av en redundant kolumn som är en summa av multiplar av andra inte minskar godheten i passningen av den resulterande modellen. Sådana kolumner tas bort exakt eftersom de inte representerar något värde som lagts till för att försöka hitta den bästa minsta kvadratpassningen. Om du undersöker LOGEST-resultatet i cellerna I23:K35 i Excel 2003 och i senare versioner av Excel märker du att de tre sista raderna i utdatatabellerna är desamma. Posterna i cellerna I31:J32 och cellerna J24:K25 sammanfaller. Det här visar att samma resultat erhålls när kolumn C ingår i modellen, men visar sig vara redundant (utdata i cellerna I24:K28) som när kolumn C togs bort innan LOGEST körts (utdata i cellerna I31:J35). Det motsvarar den grundläggande principen om det finns kolinjäritet.

I cellerna 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 prognosterade y-värden. När du granskat formlerna 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 (om kolumn B, C används som prediktanter, endast kolumn B som försägelse).

Kolinjäritet identifieras i LOGEST Excel 2003 och i senare versioner av Excel eftersom LOGEST anropar LINEST. REGR använder en annan metod för att lösa regressionskoefficienter. Den här metoden är nedbrytning av QR. Artikeln LINEST innehåller en genomgång av algoritmen för QR-nedbrytning för ett litet exempel.

Sammanfattning av resultat i tidigare versioner av Excel

GROWTH-resultat påverkas negativt i versioner av Excel som är tidigare än Excel 2003 på grund av felaktiga resultat i LOGEST som i sin tur beror på felaktiga resultat i LINEST.

LINEST beräknades med en metod som inte uppmärksammade kolinjäritetsproblem. Förekomsten av kolinjäritet orsakade avrundningsfel, olämpliga standardfel i regressionskoefficienter och olämpliga frihetsgrader. Ibland är avrundningsproblem tillräckligt allvarliga att LINEST fyllt utdatatabellen med #NUM!. Om du, som i de flesta fall i praktiken, kan vara säker på att det inte finns några kolinjära (eller nästan kolinjär) förutsägelsekolumner, ger LINEST i allmänhet godtagbara resultat. Därför kan användare av GROWTH känna igen sig på samma sätt om de kan se frånvaro av kolinjära (eller nästan kolinjär) förutsägelsekolumner.

Sammanfattning av resultaten från Excel 2003 och i senare versioner av Excel

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

  • Bättre numerisk stabilitet (vanligtvis mindre fel avrundade)
  • Analys av kolinjäritetsproblem

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 senare versioner av Excel. Dessa förbättringar i LINEST innebär förbättringar i LOGEST och GROWTH.

Beslut

Growth's performance has been improved because LINEST has been greatly improved for Excel 2003 and for later versions of Excel. Förbättringar i LINEST påverkar även LOGEST, eftersom LOGEST anropas av TILLVÄXT. Användare av tidigare versioner av Excel kontrollera att predikatorkolumner inte är kolinjära innan de använder GROWTH.

Mycket av materialet som presenteras i den här artikeln och i LINEST-artikeln kan först visas som alarm för användare av versioner av Excel som är tidigare än Excel 2003. Observera dock att kolinjäritet endast är ett problem i en liten procentandel av fallen. Tidigare versioner av Excel godtagbara GROWTH-resultat när det inte finns någon kolinjäritet.

Som tur är har förbättringarna av REGR även påverkat Analysis ToolPaks linjära regressionsverktyg (det här verktyget anropar REGR) och två andra relaterade Excel funktioner: LOGEST och TREND.