Statistische functies van Excel: GROEI

Samenvatting

In dit artikel wordt de functie GROEI in Excel beschreven, wordt uitgelegd hoe de functie wordt gebruikt en worden de resultaten van de functie voor Excel 2003 en voor latere versies van Excel vergeleken met de resultaten van GROEI in eerdere versies van Excel. GROEI wordt geëvalueerd door de gerelateerde functie LIJNSCH aan te roepen. Uitgebreide wijzigingen in LINEST voor Excel 2003 en latere versies van Excel worden samengevat en de gevolgen voor GROEI worden genoteerd.

Informatie over Microsoft Excel 2004 voor Macintosh

De statistische functies in Excel 2004 voor Mac zijn bijgewerkt met behulp van dezelfde algoritmen die zijn gebruikt om de statistische functies in Excel 2003 en in latere versies van Excel bij te werken. Alle informatie in dit artikel waarin wordt beschreven hoe een functie werkt of hoe een functie is gewijzigd voor Excel 2003 of voor latere versies van Excel, is ook van toepassing op Excel 2004 voor Mac.

Meer informatie

De functie GROEI(known_y, known_x, new_x, constante) wordt gebruikt om een regressieanalyse uit te voeren waarbij een exponentiële curve wordt toegepast. Er wordt een criterium met de methode van de kleinste kwadraten gebruikt, en GROEI tracht de beste pasvorm binnen dat criterium te vinden. Known_y vertegenwoordigt gegevens op de 'afhankelijke variabele' en known_x gegevens voor een of meer 'onafhankelijke variabelen'. In het HELP-bestand GROEI worden zeldzame gevallen besproken waarbij het tweede of derde argument kan worden weggelaten.

Ervan uitgaande dat er p predictor variabelen zijn, roept GROEI feitelijk LOGEST aan. LOGEST past bij een vergelijking van de vorm:

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

Waarden van de coëfficiënten, b, m1, m2, ..., mp worden bepaald die de beste pasvorm geven aan de y-gegevens.

Als het laatste argument 'constant' is ingesteld op TRUE, wilt u dat het regressiemodel de vermenigvuldigende coëfficiënt b in het regressiemodel opneemt. Als deze optie is ingesteld op ONWAAR, wordt b uitgesloten door deze in wezen in te stellen op 1. Het laatste argument is optioneel; als het argument wordt weggelaten, wordt dit geïnterpreteerd als WAAR.

Voor gemak in de rest van dit artikel wordt ervan uitgegaan dat gegevens in kolommen zijn gerangschikt, zodat known_y een kolom met y-gegevens is en known_x een of meer kolommen met x-gegevens is. De afmetingen (lengten) van elk van deze kolommen moeten gelijk zijn. New_x wordt er ook van uitgegaan dat deze in kolommen worden gerangschikt en dat er hetzelfde aantal kolommen moet zijn voor new_x als voor known_x. Al onze opmerkingen hieronder zijn even waar als de gegevens niet in kolommen zijn gerangschikt, maar het is eenvoudiger om de enkele (meest gebruikte) case te bespreken.

Nadat u het best passende regressiemodel hebt berekend (door de functie LOGEST van Excel aan te roepen), retourneert GROWTH voorspelde waarden die zijn gekoppeld aan new_x' s.

In dit artikel worden voorbeelden gebruikt om te laten zien hoe GROEI zich verhoudt tot LOGEST en om problemen aan te wijzen met LOGEST in versies van Excel die ouder zijn dan Excel 2003 die zich vertalen naar problemen met GROEI. GROEI roept LOGEST effectief aan, voert LOGEST uit, gebruikt regressiecoëfficiënten in LOGEST-uitvoer in de berekening van voorspelde y-waarden die zijn gekoppeld aan elke rij van new_x en presenteert deze kolom met voorspelde y-waarden aan u. Daarom moet u weten over problemen bij het uitvoeren van LOGEST. Wanneer LOGEST wordt aangeroepen, wordt het op zijn beurt aangeroepen door LINEST. Hoewel code voor GROEI en LOGEST niet is herschreven voor Excel 2003 en voor latere versies van Excel, zijn uitgebreide wijzigingen (en verbeteringen) in REGELST-code aangebracht.

Als aanvulling op dit artikel wordt het volgende artikel over LINEST ten zeerste aanbevolen. Het bevat verschillende voorbeelden en documentenproblemen met REGELST in versies van Excel die ouder zijn dan Excel 2003.

Voor meer informatie over LINEST selecteert u het volgende artikelnummer om het artikel in de Microsoft Knowledge Base te openen:

828533 Beschrijving van de functie LIJNSCH in Excel 2003 en in Excel 2004 voor Mac

Het LINEST-helpbestand, dat is bijgewerkt voor Excel 2003, wordt ook aanbevolen.

In het volgende artikel over LOGEST wordt uitgelegd hoe LOGEST communiceert met LINEST. Deze details worden hier weggelaten.

Zie statistische functies van Excel voor meer informatie: LOGEST.

Omdat de focus in dit artikel ligt op numerieke problemen in versies van Excel die ouder zijn dan Excel 2003, bevat dit artikel niet veel praktische voorbeelden van het gebruik van GROEI. Het Help-bestand in GROEI bevat nuttige voorbeelden.

Syntaxis

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

De argumenten, known_y, known_x en new_x moeten matrices of celbereiken met gerelateerde dimensies zijn. Als known_y één kolom met m rijen is, zijn known_x kolommen met m rijen waarbij c groter is dan of gelijk is aan één. C is het aantal predictorvariabelen; m is het aantal gegevenspunten. New_x moeten vervolgens bestaan uit c kolommen bij r rijen, waarbij r groter dan of gelijk is aan één. (Vergelijkbare relaties in dimensies moeten gelden als gegevens zijn ingedeeld in rijen in plaats van kolommen.) De constante is een logisch argument dat moet worden ingesteld op WAAR of ONWAAR (of 0 of 1 die in Excel wordt geïnterpreteerd als ONWAAR of WAAR, respectievelijk). De laatste drie argumenten voor GROEI zijn allemaal optioneel; zie het Help-bestand GROEI voor opties voor het weglaten van het tweede argument, het derde argument of beide; het weglaten van het vierde argument wordt geïnterpreteerd als WAAR.

Het meest voorkomende gebruik van GROEI omvat twee cellenbereiken die de gegevens bevatten, zoals GROEI(A1:A100, B1:F100, B101:F108, WAAR). Omdat er doorgaans meer dan één voorspellingsvariabele is, bevat het tweede argument in dit voorbeeld meerdere kolommen. In dit voorbeeld zijn er 100 onderwerpen, één afhankelijke variabelewaarde (known_y) voor elk onderwerp en vijf afhankelijke variabelewaarden (known_x's) voor elk onderwerp. Er zijn nog acht hypothetische onderwerpen waar u GROEI wilt gebruiken om voorspelde y-waarden te berekenen.

Voorbeeld van gebruik

Er wordt een Excel-werkbladvoorbeeld gegeven om de volgende belangrijke concepten te illustreren:

• Hoe GROEI communiceert met LOGEST
• Problemen die optreden met GROEI (of LOGEST en LINEST) door collineaire known_x's in versies van Excel die ouder zijn dan Excel 2003

Opmerking

Een uitgebreide bespreking van het tweede punt in de context van LIJNSCH vindt u in het artikel over LIJNSCH.

Als u de functie GROEI wilt illustreren, maakt u een leeg Excel-werkblad, kopieert u de volgende tabel, selecteert u cel A1 in het lege Excel-werkblad en plakt u de items, zodat de volgende tabel de cellen A1:K35 in het werkblad vult.

Een	B	C	D	E	F	G	H	Ik	J	K
y:	x's
=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
Nieuwe x'en:	9	11
	12	14
GROEI met behulp van kolommen B,C:				Waarden voor Excel 2002 en voor eerdere versies van Excel:
			Waarden voor Excel 2003 en voor latere versies van Excel:
=GROEI(A2:A6;B2:C6;B7:C8;WAAR)				#NUM!				472.432432563203
=GROEI(A2:A6;B2:C6;B7:C8;WAAR)				#NUM!				3400.16400895377
GROEI met alleen kolom B
=GROEI(A2:A6;B2:B6;B7:B8;WAAR)				472,432432563203				472,432432563203
=GROEI(A2:A6;B2:B6;B7:B8;WAAR)				3400.16400895377				3400.16400895377
Aangepaste waarden uit de LOGEST-resultaten in Excel 2003 en in latere versies van Excel
Kolom B, C gebruiken		Kolom B gebruiken
=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 met behulp van kolommen B,C:				Waarden voor Excel 2002 en voor eerdere versies van Excel:				Waarden voor Excel 2003 en voor latere versies van 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 met alleen kolom 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

Opmerking

Nadat u deze tabel in uw nieuwe Excel-werkblad hebt geplakt, selecteert u de knop Plakopties en selecteert u vervolgens Doelopmaak vergelijken. Als het geplakte bereik nog steeds is geselecteerd, gebruikt u een van de volgende procedures, passend bij de versie van Excel die u gebruikt:

• Selecteer in Microsoft Office Excel 2007 het tabblad Start , selecteer Opmaak in de groep Cellen en selecteer vervolgens Kolombreedte autoaanpassen.
• Wijs in Excel 2003 de optie Kolom aan in het menu Opmaak en kies Selectie Autofit.

Gegevens voor GROEI bevinden zich in de cellen A1:C8. (Vermeldingen in de cellen D2:D6 maken geen deel uit van de gegevens, maar worden gebruikt voor afbeelding hieronder.) Resultaten van GROEI voor twee verschillende modellen voor zowel eerdere versies van Excel als voor latere versies van Excel worden respectievelijk weergegeven in de cellen E10:E16 en I10:116. Resultaten in cellen A10:A16 komen overeen met de versie van Excel die u gebruikt. Richt u voorlopig op de resultaten voor Excel 2003 en voor latere versies van Excel wanneer u onderzoekt hoe GROEI LOGEST aanroept en hoe GROEI LOGEST-resultaten gebruikt.

GROEI en LOGEST kunnen worden beschouwd als interactie volgens de volgende stappen:

1. U roept GROEI(known_y's, known_x's, new_x's, constant) aan.
2. GROEI gebruikt de functie LOGEST(known_y's, known_x's, constant, TRUE)
3. Regressiecoëfficiënten van deze aanroep van LOGEST zijn verkregen. Deze coëfficiënten worden weergegeven in de eerste rij van de uitvoertabel van LOGEST.
4. Voor elke rij van new_x wordt de voorspelde y-waarde berekend op basis van deze LOGEST-coëfficiënten en de waarden van de new_x in die rij.
5. De berekende waarde in stap 4 wordt geretourneerd in de juiste cel voor de GROWTH-uitvoer die overeenkomt met de rij van die new_x.

Als GROEI de juiste resultaten oplevert, moet LOGEST de juiste resultaten genereren in stap 3. Omdat voor de evaluatie van LOGEST in stap 3 een aanroep naar LIJNSCH is vereist, is het essentieel dat LINEST goed werkt. Problemen met LINEST in versies van Excel die ouder zijn dan Excel 2003, ontstaan door collineaire predictor-kolommen. (Er zijn andere problemen met REGELST en LOGEST in de eerdere versies van Excel die optreden wanneer het laatste argument op GROEI is ingesteld op ONWAAR. Deze problemen hebben echter geen invloed op de resultaten van GROEI en worden hier niet besproken.)

Predictorkolommen (known_x's) zijn lineair afhankelijk als ten minste één kolom, c, kan worden uitgedrukt als een som van veelvouden van andere kolommen, c1, c2 en andere kolommen. Kolom c wordt vaak redundant genoemd omdat de informatie die deze bevat, kan worden samengesteld uit de kolommen c1, c2 en andere kolommen. Het fundamentele principe in het bestaan van collineariteit is dat de resultaten niet zouden moeten worden beïnvloed door of een redundante kolom in de oorspronkelijke gegevens is opgenomen of uit de oorspronkelijke gegevens is verwijderd. Omdat LINEST in versies van Excel die ouder zijn dan Excel 2003 niet op zoek was naar collineariteit, werd dit principe eenvoudig geschonden. Predictorkolommen zijn bijna collineair als ten minste één kolom, c, kan worden uitgedrukt als bijna gelijk aan een som van veelvouden van andere kolommen, zoals c1, c2 en andere kolommen. In dit geval betekent "bijna gelijk" een kleine som van kwadratische afwijkingen van vermeldingen in c van overeenkomstige vermeldingen in de gewogen som van c1, c2 en andere kolommen. 'Zeer klein' is bijvoorbeeld minder dan 10^(-12), bijvoorbeeld.

Het eerste model, in rij 10 tot en met 12, gebruikt kolommen B en C als voorspellers en vraagt Excel om de constante te modelleren (laatste argument ingesteld op TRUE). Vervolgens wordt in Excel een extra voorspellende kolom ingevoegd die lijkt op de cellen D2:D6. U ziet dat vermeldingen in kolom C in rijen 2 tot en met 6 exact gelijk zijn aan de som van de overeenkomende vermeldingen in kolom B en D. Daarom is er sprake van een multiplexeariteit omdat kolom C een som is van veelvouden van de volgende items:

• Kolom B
• De extra kolom met 1-en die in Excel wordt ingevoegd omdat het derde argument voor LOGEST was weggelaten of TRUE is (het 'normale' geval)

Dit veroorzaakt dergelijke numerieke problemen dat versies van Excel die ouder zijn dan Excel 2003 geen rekenresultaten kunnen berekenen. Daarom wordt de GROWTH-uitvoertabel gevuld met #NUM!.

Het tweede model, in rijen 14 tot en met 16, is een model dat elke versie van Excel met succes kan verwerken. Er is geen collineariteit en de gebruiker vraagt Excel opnieuw om de constante te modelleren. Dit model is hier om de volgende redenen opgenomen:

• Ten eerste is het meest typisch voor praktische gevallen dat er geen collineariteit aanwezig is. Deze gevallen worden voldoende verwerkt in alle versies van Excel. Het moet geruststellend zijn om te weten dat numerieke problemen waarschijnlijk niet optreden in de meest voorkomende praktische situatie als u een eerdere versie van Excel hebt.
• Ten tweede wordt dit voorbeeld gebruikt om het gedrag van Excel 2003 en latere versies van Excel in de twee modellen te vergelijken. In de meeste grote statistische pakketten wordt collineariteit geanalyseerd, wordt een kolom verwijderd die lineair afhankelijk is van andere kolommen, en wordt de gebruiker gewaarschuwd met een bericht zoals 'kolom C is lineair afhankelijk van andere voorspellende kolommen en is verwijderd uit de analyse'.

In Excel 2003 en in latere versies van Excel wordt een dergelijk bericht niet overgebracht in een waarschuwing of in een tekenreeks, maar in de uitvoertabel LOGEST. GROEI heeft geen mechanisme voor het leveren van een dergelijk bericht aan de gebruiker. In de LOGEST-uitvoertabel komt een regressiecoëfficiënt die één is en waarvan de standaardfout nul is, overeen met een coëfficiënt voor een kolom die uit het model is verwijderd. LOGEST-uitvoertabellen zijn opgenomen in rijen 23 tot en met 35 die overeenkomen met de GROEI-uitvoer in rij 10 tot en met 16. De vermeldingen in de cellen I24:I25 tonen dat een overbodige voorspellingskolom is verwijderd. In dit geval heeft LOGEST ervoor gekozen kolom C te verwijderen (coëfficiënten in de cellen I24, J24, K24 komen overeen met respectievelijk kolom C, B en de constante kolom van Excel). Wanneer er sprake is van multicollineariteit, kan een van de betrokken kolommen worden verwijderd en is de keuze willekeurig.

In het tweede model in rijen 30 tot 35 is er geen multicollineariteit en is er geen kolom verwijderd. U kunt zien dat de voorspelde y-waarden in beide modellen hetzelfde zijn. Dit probleem treedt op omdat het verwijderen van een redundante kolom die een som is van veelvouden van anderen, de geschiktheid van het resulterende model niet vermindert. Dergelijke kolommen worden juist verwijderd omdat ze geen toegevoegde waarde vertegenwoordigen bij het zoeken naar de best passende kleinste-kwadratenpassing. Als u ook de LOGEST-uitvoer in de cellen I23:K35 in Excel 2003 en in latere versies van Excel bekijkt, ziet u dat de laatste drie rijen van de uitvoertabellen hetzelfde zijn. Daarnaast vallen de vermeldingen in de cellen I31:J32 en de cellen J24:K25 samen. Het laat zien dat dezelfde resultaten worden verkregen wanneer kolom C is opgenomen in het model, maar bleek redundant te zijn (uitvoer in de cellen I24:K28) als wanneer kolom C werd geëlimineerd voordat LOGEST werd uitgevoerd (uitvoer in cellen I31:J35). Dit voldoet aan het fundamentele principe voor het bestaan van collineariteit.

In de cellen A18:C21 gebruikt Microsoft gegevens uit Excel 2003 en latere versies van Excel om te illustreren hoe GROEI LOGEST-uitvoer gebruikt en de relevante voorspelde y-waarden berekent. Door de formules in de cellen A20:A21 en de cellen C20:C21 te onderzoeken, kunt u zien hoe LOGEST-coëfficiënten worden gecombineerd met de gegevens van new_x in de cellen B7:C8 voor elk van de twee modellen (met behulp van kolom B, C als voorspeller; alleen kolom B als voorspellingsfunctie).

Collineariteit wordt geïdentificeerd in LOGEST in Excel 2003 en in latere versies van Excel, omdat LOGEST LINEST aanroept. LINEST gebruikt een andere benadering om de regressiecoëfficiënten op te lossen. Deze benadering is QR-ontbinding. Het LINEST-artikel bevat een stapsgewijze uitleg van het QR-Decompositie-algoritme voor een klein voorbeeld.

Samenvatting van resultaten in eerdere versies van Excel

GROEIresultaten worden negatief beïnvloed in versies van Excel die ouder zijn dan Excel 2003 vanwege onnauwkeurige resultaten in LOGEST die op zijn beurt van onjuiste resultaten in LIJNT komen.

LINEST-functie werd berekend met behulp van een benadering die geen aandacht besteedde aan problemen met betrekking tot collineariteit. Het bestaan van collineariteit veroorzaakte afrondingsfouten, ongepaste standaardfouten van regressiecoëfficiënten en ongepaste vrijheidsgraden. Soms zijn afrondingsproblemen zo ernstig dat LINEST de uitvoertabel met #NUM! heeft gevuld. Als u, zoals in de meeste gevallen in de praktijk, erop kunt vertrouwen dat er geen collineaire (of bijna collineaire) predictorkolommen waren, zou LIJNSCH over het algemeen acceptabele resultaten opleveren. Daarom kunnen gebruikers van GROWTH op dezelfde manier gerustgesteld worden als ze de afwezigheid van collineaire (of bijna collineaire) voorspellerkolommen kunnen zien.

Samenvatting van resultaten in Excel 2003 en in latere versies van Excel

Verbeteringen in LINEST zijn het overschakelen naar de QR-decompositiemethode voor het bepalen van regressiecoëfficiënten. QR-ontleding heeft de volgende voordelen:

• Betere numerieke stabiliteit (over het algemeen kleinere afrondingsfouten)
• Analyse van problemen met betrekking tot collineariteit

Alle problemen met versies van Excel die ouder zijn dan Excel 2003 die in dit artikel worden geïllustreerd, zijn gecorrigeerd voor Excel 2003 en voor latere versies van Excel. Deze verbeteringen in LINEST worden omgezet in verbeteringen in LOGEST en GROEI.

Conclusies

De prestaties van GROEI zijn verbeterd omdat LINEST aanzienlijk is verbeterd voor Excel 2003 en voor latere versies van Excel. Verbeteringen in LINEST zijn ook van invloed op LOGEST, omdat LOGEST wordt aangeroepen door GROEI. Gebruikers van eerdere versies van Excel moeten controleren of predictorkolommen niet collineair zijn voordat ze de functie GROEI gebruiken.

Veel van het materiaal dat in dit artikel en in het LINEST-artikel wordt gepresenteerd, kan gebruikers van oudere Excel-versies aanvankelijk zorgen baren, vooral die van vóór Excel 2003. Er moet echter worden opgemerkt dat collineariteit slechts in een klein percentage van de gevallen een probleem is. Eerdere versies van Excel geven acceptabele GROEI-resultaten wanneer er geen colineariteit is.

Gelukkig zijn verbeteringen in LINEST ook van invloed op het lineaire regressiehulpmiddel van Analysis ToolPak (dit hulpprogramma roept LIJNSCH aan) en twee andere gerelateerde Excel-functies: LOGEST en TREND.