Statistische functies van Excel: LOGEST

Samenvatting

In dit artikel wordt de functie LOGEST in Microsoft Office Excel 2003 en in latere versies van Excel beschreven. Het illustreert hoe de functie wordt gebruikt en vergelijkt de resultaten van de functie in Excel 2003 en in latere versies van Excel met de resultaten van de functie in eerdere versies van Excel.

LOGEST wordt geëvalueerd door de gerelateerde functie, LINEST, aan te roepen. Uitgebreide wijzigingen in LINEST voor Excel 2003 en voor latere versies van Excel worden samengevat en hun gevolgen voor LOGEST worden vermeld.

Informatie over Microsoft Excel 2004 voor Macintosh

De statistische functies in Microsoft Excel 2004 voor Macintosh zijn bijgewerkt met behulp van dezelfde algoritmen als Excel 2003 en latere versies van Excel. Alle informatie in dit artikel waarin wordt beschreven hoe een functie werkt of die beschrijft hoe een functie is gewijzigd voor Excel 2003 en voor latere versies van Excel, is ook van toepassing op Excel 2004 voor Macintosh.

Meer informatie

De functie LOGEST(known_y, known_x, constante, statistieken) wordt gebruikt om regressie uit te voeren waarbij een exponentiële curve is geplaatst. Er wordt een criterium voor minimale kwadraten gebruikt en LOGEST probeert de beste geschiktheid onder dat criterium te vinden. Known_y vertegenwoordigt gegevens op de afhankelijke variabele en known_x staat voor gegevens op een of meer onafhankelijke variabelen. Het tweede argument is optioneel. Als deze wordt weggelaten, wordt ervan uitgegaan dat het een matrix is van dezelfde grootte als known_y die de waarden (1, 2, 3, ...) bevatten.

Ervan uitgaande dat er p predictor-variabelen zijn, past LOGEST in een vergelijking van de volgende vorm:

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

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

Het laatste argument voor LOGEST is ingesteld op WAAR als u aanvullende statistieken wilt, waaronder verschillende sommen van kwadraten, r-kwadraat, f-statistiek en standaardfouten van de regressiecoëfficiënten. In dit geval moet LOGEST worden ingevoerd als een matrixformule. Het laatste argument is optioneel. Als deze wordt weggelaten, wordt deze geïnterpreteerd als ONWAAR. De afmetingen van de matrix zijn vijf rijen met een aantal kolommen dat gelijk is aan het aantal onafhankelijke variabelen plus één als het derde argument is ingesteld op WAAR (plus 0 als het derde argument niet is ingesteld op WAAR).

Als het derde argument 'constant' is ingesteld op WAAR, wilt u dat het regressiemodel de constante b in de vorige vergelijking in het model opneemt. Als deze is ingesteld op ONWAAR, wordt de vermenigvuldigde constante b uitgesloten van het regressiemodel door deze gelijk te stellen aan één. Het derde argument is optioneel. Als deze wordt weggelaten, wordt deze geïnterpreteerd als WAAR.

In 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 of lengten van elk van deze kolommen moeten gelijk zijn. Alle volgende waarnemingen zijn even waar als de gegevens niet in kolommen zijn gerangschikt, maar het is gemakkelijker om de meest gebruikte case te bespreken. Stel ook dat het laatste argument voor LOGEST altijd WAAR is en dat u altijd geïnteresseerd bent in gedetailleerde uitvoer. Deze veronderstelling helpt om numerieke problemen in eerdere versies van Excel aan het licht te brengen. Sommige numerieke problemen zijn nog steeds aanwezig als u ONWAAR gebruikt voor dit argument.

In dit artikel worden de volgende voorbeelden gebruikt om te laten zien hoe LOGEST zich verhoudt tot LINEST en om te wijzen op problemen met LINEST in eerdere versies van Excel die zich vertalen in problemen met LOGEST. Hoewel de code voor LOGEST niet is herschreven voor Excel 2003 en voor latere versies van Excel, zijn er uitgebreide wijzigingen en verbeteringen aangebracht in de code voor LINEST. LOGEST roept EFFECTIEF LINEST aan, voert LINEST uit, wijzigt de LINEST-uitvoer en presenteert deze aan u. Daarom moet u op de hoogte zijn van problemen bij de uitvoering van LINEST. Voor meer informatie over LINEST klikt u op het volgende artikelnummer om het artikel in de Microsoft Knowledge Base weer te geven:

828533 Beschrijving van de functie LINEST in Excel 2003 en in latere versies van Excel

Het HELP-bestand van LINEST is ook herzien voor Excel 2003 en voor latere versies van Excel en is handig voor meer informatie. Omdat de focus van dit artikel ligt op numerieke problemen in eerdere versies van Microsoft Excel, bevat dit artikel niet veel praktische voorbeelden van LOGEST. Het LOGEST Help-bestand bevat twee nuttige voorbeelden.

Syntaxis

LOGEST(known_y's, known_x's, constant, statistics)

De argumenten, known_y en known_x, moeten matrices of celbereiken zijn met gerelateerde dimensies. Als known_y één kolom per m rij is, moet known_x c-kolommen zijn op m rijen waarbij c groter is dan of gelijk is aan één. C is het aantal predictorvariabelen; m is het aantal gegevenspunten. Vergelijkbare relaties moeten stand houden als known_y in één rij is ingedeeld; in dat geval moet known_x in r rijen staan. (R moet groter dan of gelijk zijn aan 1.) Constante en statistieken zijn logische argumenten die moeten worden ingesteld op WAAR of ONWAAR. In Excel moeten deze argumenten zijn ingesteld op 0 of 1 en door Excel worden geïnterpreteerd als ONWAAR of WAAR. De laatste drie argumenten voor LOGEST zijn allemaal optioneel. Als u het tweede argument weglaat, gaat LOGEST uit van één predictor die de vermeldingen {1, 2, 3, ...} bevat. Als u het derde argument weglaat, wordt dit geïnterpreteerd als WAAR. Als u het vierde argument weglaat, wordt dit geïnterpreteerd als ONWAAR.

Het meest voorkomende gebruik van LOGEST omvat twee cellenbereiken die de gegevens bevatten, zoals LOGEST(A1:A100, B1:F100, TRUE, TRUE). Omdat er doorgaans meer dan één predictorvariabele is, bevat het tweede argument in dit voorbeeld meerdere kolommen. In dit voorbeeld zijn er 100 onderwerpen, één afhankelijke variabelewaarde, known_y's, voor elk onderwerp en vijf afhankelijke variabelewaarden, known_x, voor elk onderwerp.

Voorbeeld van gebruik

Er worden twee afzonderlijke voorbeelden van Excel-werkbladen gegeven om de volgende belangrijke concepten te illustreren:

• Hoe LOGEST communiceert met LINEST
• Problemen in Microsoft Excel 2002 en in eerdere versies van Excel wanneer het derde argument op LOGEST of LINEST is ingesteld op ONWAAR of wordt weggelaten.
• Problemen vanwege collineaire known_x in LOGEST of LINEST in Excel 2002 en eerder.

Klik op het volgende artikelnummer in de Microsoft Knowledge Base voor meer informatie over het tweede en derde concept in deze lijst:

828533 Beschrijving van de functie LINEST in Excel 2003 en in latere versies van Excel

Voer de volgende stappen uit om LOGEST te illustreren met het derde argument dat is ingesteld op ONWAAR:

1. Maak een leeg Excel-werkblad en kopieer de tabel verderop in deze sectie.

2. Klik op cel A1 in het lege Excel-werkblad. Plak vervolgens de vermeldingen zodat de tabel de cellen A1:H17 in het werkblad vult.

3. Nadat u de tabel in het nieuwe Excel-werkblad hebt geplakt, klikt u op Plakopties en vervolgens op Doelopmaak overeenkomen.

4. Terwijl het geplakte bereik nog steeds is geselecteerd, gebruikt u een van de volgende procedures, indien van toepassing voor de versie van Excel die u uitvoert:

   • Klik in Microsoft Office Excel 2007 op het tabblad Start , klik op Opmaak in de groep Cellen en klik vervolgens op Kolombreedte automatisch aanpassen.
   • Wijs in Excel 2003 en in eerdere versies van Excel kolom aan in het menu Opmaak en klik vervolgens op Selectie automatisch aanpassen.

Gegevens voor LOGEST bevinden zich in cellen A4:B6. Gedetailleerde resultaten voor Excel 2002 en voor eerdere versies van Excel en voor Excel 2003 en voor latere versies van Excel worden weergegeven in respectievelijk cellen D4:E8 en cellen G4:H8. In cellen A11:B13 worden dezelfde known_x weergegeven, maar de known_y in cellen A4:A6 zijn getransformeerd door hun natuurlijke logaritme te nemen met behulp van de functie Excel LN. LINEST wordt vervolgens aangeroepen op deze getransformeerde gegevens en de resultaten worden weergegeven in de cellen G11:H15. U ziet dat de resultaten in de cellen G12:H15 hetzelfde zijn als de LOGEST-resultaten in de cellen G5:H8. De LINEST-coëfficiënten in de cellen G11:H11 worden getransformeerd door exponentiatie. Dat wil dus dat de LINET-coëfficiënten in de cellen worden getransformeerd door in feite de Excel EXP-functie te gebruiken om de LOGEST-coëfficiënten in de cellen G4:H4 te berekenen. U kunt deze relatie controleren met behulp van de functie EXP in de cellen G17:H17. Als u wilt samenvatten hoe LOGEST en LINEST samenwerken, volgt u de volgende reeks stappen:

1. U roept LOGEST(known_y, known_x, constant, TRUE) aan.
2. LOGEST roept LINEST(LN van known_y's, known_x's, constant, TRUE) aan.
3. LOGEST ontvangt de resultatentabel van deze aanroep naar LINEST.
4. LOGEST wijzigt de LINEST-coëfficiënten in de eerste rij van de resultaattabel LINEST door exponentiatie. Vervang bijvoorbeeld elke LINET-coëfficiënt, m, door EXP(m).
5. LOGEST retourneert deze gewijzigde RESULTAATtabel LINEST als de LOGEST-resultaattabel.

Als LOGEST de juiste resultaten moet retourneren, moet LINEST de juiste resultaten genereren in stap 3. Bekijk de cellen D13:D15. Cel D13 bevat een r-kwadraatwaarde, cel D14 bevat een f-statistiek en cel D15 bevat de regressiesom van kwadraten MET LIJNEN.

Wanneer IN Excel 2002 en in eerdere versies van Excel LINEST wordt aangeroepen met het derde argument ingesteld op FALSE, wordt altijd een regressiesom van kwadraten berekend die niet juist is omdat er een formule wordt gebruikt die niet juist is. Dit probleem is opgelost in Excel 2003 en in latere versies van Excel. U ziet dat de waarden in de eerste twee rijen van de uitvoertabel niet worden beïnvloed door dit probleem. In Excel 2002 en in eerdere versies van Excel wordt in het artikel LINEST een tijdelijke oplossing beschreven voor het genereren van de juiste waarden in de laatste drie rijen van de LINEST-uitvoertabel. Als u een eerdere versie van Excel gebruikt en u het derde argument wilt instellen op LOGEST op FALSE, raden we u aan stap 2 en 3 van de vorige procedure expliciet uit te voeren en vervolgens de tijdelijke oplossing in het artikel LINEST te gebruiken om de laatste drie rijen van de LINEST-uitvoertabel te wijzigen.

Problemen treden op vanwege collineaire known_x in LOGEST of LINEST in Excel 2002 en in eerdere versies van Excel. Predictorkolommen, known_x zijn collineair als ten minste één kolom, c, kan worden uitgedrukt als een som van veelvouden van andere waarden (c1, c2 en andere waarden). Kolom c wordt redundant genoemd omdat de informatie die deze bevat kan worden samengesteld uit de kolommen (c1, c2 en andere waarden). Het fundamentele principe bij de aanwezigheid van collineariteit is dat de resultaten niet mogen worden beïnvloed door het feit of een redundante kolom wordt opgenomen in de oorspronkelijke gegevens of uit de oorspronkelijke gegevens wordt verwijderd. Omdat in de versie van LINEST in Excel 2002 en in eerdere versies van Excel niet werd gezocht naar collineariteit, kon dit principe gemakkelijk worden geschonden. Predictorkolommen zijn bijna collineair als ten minste één kolom, c, kan worden uitgedrukt als bijna gelijk aan een som van veelvouden van andere waarden (c1, c2 en andere waarden). In dit geval betekent "bijna gelijk" een zeer kleine som van kwadratische afwijkingen van vermeldingen in c van de overeenkomstige vermeldingen in de gewogen som van c1, c2 en andere waarden. 'Zeer klein' kan bijvoorbeeld kleiner zijn dan 10^(-12).

Voer de volgende stappen uit om de collineariteit van LOGEST te illustreren:

1. Maak een leeg Excel-werkblad en kopieer de volgende tabel.

2. Klik op cel A1 in het lege Excel-werkblad. Plak vervolgens de vermeldingen zodat de tabel de cellen A1:N27 in het werkblad vult.

3. Nadat u de tabel in het nieuwe Excel-werkblad hebt geplakt, klikt u op Plakopties en vervolgens op Doelopmaak overeenkomen.

4. Terwijl het geplakte bereik nog steeds is geselecteerd, gebruikt u een van de volgende procedures, indien van toepassing voor de versie van Excel die u uitvoert:

   • Klik in Excel 2007 op het tabblad Start , klik op Opmaak in de groep Cellen en klik vervolgens op Kolombreedte automatisch aanpassen.
   • Wijs in Excel 2003 en in eerdere versies van Excel kolom aan in het menu Opmaak en klik vervolgens op Selectie automatisch aanpassen.

Gegevens worden opgenomen in cellen A1:C6. Vermeldingen in cellen D2:D6 maken geen deel uit van de gegevens. Deze vermeldingen worden gebruikt ter illustratie in de volgende alinea. Resultaten van twee verschillende aanroepen naar LOGEST worden weergegeven voor Excel 2002 en voor eerdere versies van Excel in cellen E8:G20, en voor Excel 2003 en voor latere versies van Excel in cellen I8:K20.

Het eerste model, in de rijen 8 tot en met 13, gebruikt kolommen B en C als voorspellers en vraagt Excel om de constante te modelleren waarbij het derde argument is ingesteld op WAAR. In Excel wordt vervolgens effectief een extra voorspellingskolom ingevoegd die er net zo uitziet als cellen D2:D6. U ziet dat vermeldingen in kolom C in de rijen 2 tot en met 6 exact gelijk zijn aan de som van de overeenkomende vermeldingen in kolommen B en D. Daarom treedt collineariteit op omdat kolom C een som is van veelvouden van kolom B en de extra kolom van Excel van 1 wordt ingevoegd omdat het derde argument voor LOGEST is weggelaten of WAAR dat het 'normale' geval is. Deze collineariteit veroorzaakt numerieke problemen en Excel 2002 en eerdere versies van Excel kunnen geen resultaten berekenen en de LOGEST-uitvoertabel is gevuld met #NUM!.

Elke versie van Excel kan het tweede model verwerken in de rijen 15 tot en met 20. Collineariteit treedt niet op met dit model en de gebruiker vraagt Excel om de constante te modelleren. Dit voorbeeld is hier opgenomen om de volgende twee redenen:

• Dit voorbeeld is het meest typische praktijkvoorbeeld: er is geen collineariteit aanwezig en het derde argument voor LOGEST is WAAR of weggelaten. Als u een eerdere versie van Excel hebt, treden er waarschijnlijk geen numerieke problemen op in het meest voorkomende praktische geval.
• Ten tweede wordt dit voorbeeld gebruikt om het gedrag van Excel 2003 en van latere versies van Excel in de twee modellen te vergelijken. De meeste belangrijke statistische pakketten analyseren collineariteit, verwijderen een kolom die een som is van veelvouden van andere uit het model en waarschuwen u 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 ontvangt u de informatie in de LOGEST-uitvoertabel, niet in een bericht of in een tekenreeks. Een regressiecoëfficiënt die één is en waarvan de standaardfout 'nul' is, komt overeen met een coëfficiënt voor een kolom die uit het model is verwijderd. (Zie de vermeldingen in cellen I9:I10 voor een voorbeeld.) In dit geval verwijdert LOGEST kolom C (coëfficiënten in de cellen I9, J9, K9 komen overeen met respectievelijk de kolommen C, B en in de constantekolom van Excel). Wanneer collineariteit optreedt, kan een van de betrokken kolommen worden verwijderd.

In het tweede model in de rijen 16 tot en met 20 treedt collineariteit niet op en worden geen van de kolommen verwijderd. De voorspelde y-waarden zijn in beide modellen hetzelfde, omdat het verwijderen van een redundante kolom die een som is van veelvouden van andere modellen (het eerste en tweede model) de geschiktheid van het resulterende model niet vermindert. Dergelijke kolommen worden juist verwijderd omdat ze geen toegevoegde waarde vertegenwoordigen bij het vinden van de meest geschikte vierkanten. In de uitvoer van Excel 2003 en latere versies van Excel in de cellen I8:K20 zijn de laatste drie rijen van de uitvoertabellen hetzelfde en vallen de vermeldingen in de cellen I16:J17 en de cellen J9:K10 samen. Dit toont aan dat dezelfde resultaten worden verkregen wanneer kolom C is opgenomen in het model, maar overbodig is (uitvoer in I9:K13) als toen kolom C werd geëlimineerd voordat LOGEST werd uitgevoerd (uitvoer in I16:J20). Deze uitvoer voldoet aan het fundamentele principe in aanwezigheid van collineariteit.

Collineariteit wordt geïdentificeerd in LINEST in Excel 2003 en in latere versies van Excel door een volledig andere benadering, QR-decompositie, te gebruiken om de regressiecoëfficiënten op te lossen. In het artikel LINEST wordt een overzicht van het QR-decompositie-algoritme beschreven voor een klein voorbeeld.

Samenvatting van resultaten in eerdere versies van Excel

LOGEST-resultaten worden nadelig beïnvloed in Excel 2002 en in eerdere versies van Excel door resultaten in LINEST die niet nauwkeurig zijn.

LINEST heeft een formule gebruikt die niet juist is voor de totale som van kwadraten wanneer het derde argument in LINEST is ingesteld op ONWAAR. Deze formule heeft geresulteerd in waarden van de regressiesom van kwadraten die niet juist zijn. Ook de waarden die afhankelijk zijn van de regressiesom van kwadraten, r kwadraat en de statistiek f, zijn niet juist. (Zie de tijdelijke oplossing in het artikel LINEST als u een eerdere versie van Excel gebruikt.) Daarom moeten gebruikers van LOGEST deze tijdelijke oplossing gebruiken wanneer ze LOGEST aanroepen met het derde argument ingesteld op ONWAAR.

Ongeacht de waarde van het derde argument is LINEST berekend met behulp van een benadering die geen collineariteitsproblemen heeft opgelost. Collineariteit veroorzaakte afrondingsfouten, standaardfouten van regressiecoëfficiënten die niet geschikt waren en vrijheidsgraden die niet geschikt waren. In sommige gevallen waren de afrondingsfouten zo ernstig dat de LINEST-uitvoertabel werd gevuld met #NUM!. LINEST biedt over het algemeen acceptabele resultaten als aan de volgende voorwaarden wordt voldaan:

• Gebruikers zijn ervan overtuigd dat de predictorkolommen niet collineair (of bijna collineair) zijn.
• Het derde argument voor LINEST is WAAR of wordt weggelaten.

Daarom biedt LOGEST over het algemeen acceptabele resultaten als de predictorkolommen niet collineair (of bijna collineair) zijn en als het derde argument voor LOGEST WAAR is of wordt weggelaten.

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

De volgende verbeteringen in LINEST zijn aangebracht:

• De formule voor de totale som van kwadraten waarbij het derde argument op LINEST is ingesteld op ONWAAR, is gecorrigeerd.
• De QR-decompositiemethode wordt gebruikt om de regressiecoëfficiënten te bepalen.

QR-ontleding heeft twee voordelen:

• Betere numerieke stabiliteit (of over het algemeen kleinere afrondingsfouten).
• Analyse van collineariteitsproblemen.

Alle problemen met Excel 2002 en eerdere versies van Excel die in dit artikel worden besproken, zijn opgelost in Excel 2003 en in latere versies van Excel.

Conclusies

De prestaties van LOGEST zijn verbeterd omdat LINEST aanzienlijk is verbeterd in Excel 2003 en in latere versies van Excel. Als u een eerdere versie van Excel gebruikt, controleert u of de voorspellende kolommen niet collineair zijn voordat u LOGEST gebruikt. Wees ook voorzichtig met het gebruik van de tijdelijke oplossing die wordt weergegeven in het artikel LINEST wanneer het derde argument op LOGEST is ingesteld op ONWAAR. Hoewel deze informatie in dit artikel en in het ARTIKEL LINEST alarmerend kan lijken voor gebruikers van Excel 2002 en eerdere versies van Excel, is collineariteit in een klein percentage van de gevallen een probleem. Aanroepen naar LOGEST met het derde argument ingesteld op ONWAAR zijn in de praktijk waarschijnlijk ook relatief zeldzaam. Eerdere versies van Excel geven acceptabele LOGEST-resultaten wanneer er geen collineariteit is en wanneer het derde argument van LOGEST WAAR is of weggelaten.