Beschrijving van de effecten van de verbeterde statistische functies voor het Analysis ToolPak in Excel

Vertaalde artikelen Vertaalde artikelen
Artikel ID: 829208 - Bekijk de producten waarop dit artikel van toepassing is.
Alles uitklappen | Alles samenvouwen

Op deze pagina

Samenvatting

In dit artikel wordt het effect beschreven van de numerieke verbeteringen in de statistische functies van Microsoft Office Excel 2003 en nieuwere versies op Analysis ToolPak (ATP)-hulpprogramma's. De meeste ATP-hulpprogramma's roepen statistische functies van Excel aan tijdens het berekenen van resultaten. In veel gevallen verwijst dit artikel alleen naar artikelen met specifieke informatie over de diverse statistische functies van Excel. Daarnaast wordt voor sommige ATP-programma's informatie verstrekt over toekomstige verbeteringen.

Informatie over Microsoft Excel 2004 voor de Macintosh

De statistische functies in Excel 2004 voor de Macintosh zijn bijgewerkt met dezelfde algoritmen die ook zijn gebruikt om de statistische functies bij te werken in Microsoft Office Excel 2003 en in nieuwere versies van Excel. Alle informatie in dit artikel die beschrijft hoe een functie werkt of hoe een functie is gewijzigd voor Excel 2003 en nieuwere versies is ook van toepassing op Excel 2004 voor Mac.

Meer informatie

Programmacode voor het ATP is niet direct aangepast, behalve voor het aanbrengen van verbeteringen in de drie ANOVA-hulpprogramma's in het ATP.

Voor een aantal ATP-hulpprogramma's zijn de numerieke prestaties verbeterd voor Excel 2003 en nieuwere versies omdat het hulpprogramma een statistische functie van Excel aanroept die is verbeterd voor Excel 2003 en nieuwere versies. In de gevallen waarin de resultaten tussen de oudere en nieuwere versies van Excel verschillen, zijn de waarden voor Excel 2003 en nieuwere versies altijd nauwkeuriger.

De meeste gebruikers merken echter geen verschil in de resultaten tussen de versies van Excel. Dat komt omdat verschillen doorgaans worden veroorzaakt door afrondingsfouten die alleen in extreme gevallen significant zijn. Het is echter belangrijk eerst een situatie te beschrijven waarin verschillen optreden als gevolg van een onjuiste formule in Microsoft Excel 2002 en oudere versies van Excel. Het is raadzaam het hulpprogramma niet te gebruiken in die versies.

Een tweede voorbeeld heeft betrekking op een onjuiste formule in Excel 2002 en oudere versies die nog aanwezig is in Excel 2003 en nieuwere versies. In dit scenario is het verstandig het ATP-programma voor geen enkele versie van Excel te gebruiken.

In de eerste plaats is het belangrijk het hulpprogramma Regression niet te gebruiken wanneer u op het selectievakje Constante is nul moet klikken om de optie in te schakelen. Dit is gecorrigeerd in Excel 2003 en nieuwere versies. U kunt het programma Regression gewoon gebruiken wanneer het selectievakje Constante is nul is uitgeschakeld (wat in de praktijk doorgaans het geval is).

In de tweede plaats is het belangrijk dat gebruikers van alle versies van Excel het programma ATP t-Test: Paired Two Sample for Means niet uitvoeren, tenzij bekend is dat er geen gegevensmetingen ontbreken. Het programma resulteert namelijk in onjuiste antwoorden (of helemaal geen antwoorden) als er een of meer metingen ontbreken.

Klik voor meer informatie over het hulpprogramma ATP Matched Pairs Two Sample t-Test op het volgende artikelnummer in de Microsoft Knowledge Base:
829252U kunt onjuiste resultaten en misleidende labels krijgen wanneer u het Analysis ToolPak t-Test gebruikt in Excel
Als u dit programma wilt gebruiken en er ontbreken gegevens (of zelfs als er een kans is dat er gegevens ontbreken), wordt het programma op de juiste manier verwerkt door de functie T.TOETS van Excel.

Verderop in dit artikel worden enkele ATP-programma's afzonderlijk besproken. De verbeteringen in Excel 2003 en nieuwere versies hebben geen invloed op de hulpprogramma's die niet worden vermeld.

ANOVA: Single Factor, Two-Factor with Replication en Two-Factor without Replication

Deze drie ANOVA-programma's zijn herschreven om de rekenkundige procedure om te zetten in een tweevoudig algoritme dat numerieke gegevens beter verwerkt. Deze verbeteringen zijn vergelijkbaar met verbeteringen in statistische functies die de som van kwadratische afwijkingen berekenen ten opzichte van een gemiddelde (bijvoorbeeld: VAR, STDEV, RICHTING, PEARSON).

Klik voor meer informatie over ATP ANOVA op het volgende artikelnummer in de Microsoft Knowledge Base:
829215Beschrijving van de numerieke verbetering in Analysis ToolPak ANOVA-programma's in Excel

Correlatie

Dit programma is niet gewijzigd. Er bestaat echter een klein verschil tussen het programma Correlatie en het programma Covariantie, dat beschikbaar is in alle versies van Excel. Het programma Correlatie resulteert in een 'lower triangular' (alle cellen boven de diagonaal nul) correlatietabel met 1'en op de diagonaal en correlaties ernaast. Het programma gebruikt CORRELATIE om gegevens naast de diagonaal te berekenen en gebruikt voor deze gegevens de waarden die worden geretourneerd door CORRELATIE. (Dit houdt in dat een wijziging van de gegevensinvoer geen gevolgen heeft voor de gegevens in de tabel. Het programma Covariantie werkt precies tegenovergesteld.)

Covariantie

Het programma Covariantie resulteert in een 'lower triangular' covariatietabel met variaties op de diagonaal en covariaties ernaast. Cellen op de diagonaal bevatten een formule '=VARP(...)', wat betekent dat het wijzigen van de gegevensinvoer een wijziging van het resultaat in de tabel tot gevolg heeft. VARP is verbeterd voor Office Excel 2003 en voor nieuwere versies van Excel.

Klik voor meer informatie over VARP op het volgende artikelnummer in de Microsoft Knowledge Base:
826393Statistische functies in Excel: VARP
Het programma Covariantie gebruikt COVARIANTIE om gegevens buiten de diagonaal te berekenen en gebruikt voor deze gegevens de waarden die worden geretourneerd door COVARIANTIE. Het wijzigen van de gegevensinvoer betekent dus dat de gegevens buiten de diagonaal ongewijzigd blijven.

Descriptive Statistics

Dit hulpprogramma maakt voor alle berekeningen gebruik van statistische functies van Excel. Aangezien VAR en STDEV zijn verbeterd voor Excel 2003 en nieuwere versies, zijn er in extreme gevallen afwijkende waarden mogelijk door afrondingsverschillen.

Klik op het volgende artikelnummer in de Microsoft Knowledge Base voor meer informatie over VAR:
826112Statistische functies in Excel: VAR

F-Test Two-Sample for Variances

Net als het programma Descriptive Statistics, maakt F-Test Two-Sample for Variances gebruik van VAR. Ook hier zijn in extreme gevallen afwijkende waarden mogelijk door afrondingsverschillen.

Random Number Generation

Met dit programma kan een bereik worden gevuld met willekeurige metingen. De waarden van deze metingen worden rechtstreeks in de cellen geplaatst, zodat deze celwaarden niet opnieuw worden berekend en vervangen door nieuwe metingen wanneer het blad opnieuw wordt berekend. De ingebouwde ASELECT-functie in Excel daarentegen, vervangt bestaande willekeurige getallen door nieuwe op het moment dat het blad opnieuw wordt berekend. U kunt ASELECT echter gebruiken zonder waarden te vervangen. Kopieer hiervoor de resultaten in een bereik en gebruik vervolgens de opdracht Plakken speciaal om de waarden in hetzelfde bereik te plakken.

Het programma Random Number Generation (RNG) produceert ook willekeurige metingen uit verschillende kansverdelingen, terwijl ASELECT overeenkomt met slechts één optie van het programma: Uniform with range between 0 and 1. In dit artikel wordt beschreven hoe u ASELECT kunt combineren met de statistische functies van Excel om dergelijke metingen te genereren.

Qua functionaliteit is het dus mogelijk de werking van RNG uit het ATP te emuleren met behulp van ASELECT en voldoende inzicht. Dit kan soms handig zijn, met name in situaties waarin veel willekeurige getallen nodig zijn.

Het is bekend dat in Excel 2002 en lager zowel het programma RNG uit het ATP als ASELECT slecht presteert in standaardtoetsen van willekeurigheid. De prestaties zijn slecht omdat de cyclus die voorafgaat aan het punt waarop de reeks van pseudo-willekeurige getallen wordt herhaald, te kort is. Dit is alleen een probleem wanneer er veel willekeurige getallen nodig zijn.

ASELECT is verbeterd voor Excel 2003 en nieuwere versies zodat ASELECT nu aan alle standaardtests voldoet. De reeks willekeurige getallen die wordt gegenereerd door ASELECT wordt pas herhaald nadat er meer dan 1 biljoen getallen zijn gegenereerd.

Klik voor meer informatie over ASELECT op het volgende artikelnummer in de Microsoft Knowledge Base:
828795Beschrijving van de functie ASELECT in Excel
Het programma voor het genereren van willekeurige getallen (RNG) in het ATP is echter niet bijgewerkt. Net als in de versie van ASELECT die in Excel 2002 en oudere versies is opgenomen, presteert RNG (generator van willekeurige getallen) slecht in standaardtests van willekeurigheid en is de repetitiecyclus te kort. Deze nadelen zijn alleen merkbaar als u een uitzonderlijk lange reeks willekeurige getallen nodig hebt (bijvoorbeeld 1 miljoen).

Het hulpprogramma RNG levert willekeurige metingen uit diverse kansverdelingen en Uniform[0,1], de verdeling die wordt gebruikt voor het genereren van willekeurige getallen via ASELECT. Het ATP-programma tekent eerst een Uniform[0,1] willekeurig getal (of meerdere van dergelijke getallen) en converteert het antwoord vervolgens naar een meting uit een van de volgende specifieke verdelingen. Voor gebruikers die de voorkeur geven aan ASELECT omdat ze veel metingen moeten genereren, bevat de onderstaande tabel formules waarin ASELECT wordt toegepast. Onder de tabel staan enkele waarschuwingen voor het gebruik van de standaardnormaalverdeling uit het Analysis ToolPak.
Deze tabel samenvouwenDeze tabel uitklappen
VerdelingExcel-formule met ASELECT()
Bernoulli(p)=ALS(ASELECT() <= p, 1, 0)
Binomiaal(n,p)=CRIT.BINOM(n; p; ASELECT())
DiscreetZie hieronder
Normaal(mu; sigma)=NORM.INV(ASELECT(); mu; sigma)
PatroonNiet echt willekeurig
Poisson(gemiddelde)Zie hieronder
Uniform(laag; hoog)= laag + (hoog ? laag) * ASELECT()
Er zijn twee redenen waarom u voor de verdeling Normaal(mu; sigma) misschien de voorkeur geeft aan ASELECT plus de formule in deze tabel in plaats van het ATP-programma RNG. In de eerste plaats presteert ASELECT beter als het gaat om het genereren van Uniform[0,1] willekeurige getallen. In de tweede plaats maakt het ATP-programma geen gebruik van de functie NORM.INV van Excel, maar van een ingebouwde versie van de inverse normaalverdeling. Deze is niet zo nauwkeurig als de versie van NORM.INV in Excel 2003 en nieuwere versies. De verdeling presteert niet alleen slechter qua nauwkeurigheid van de benadering die wordt gebruikt voor de normaalverdeling (Excel gebruikt de sterk verbeterde STAND.NORM.VERD-functie), maar ook ten aanzien van de verfijning van binaire zoekbewerkingen (Excel voert deze veel grondiger uit om een waarde te kunnen garanderen die beter aansluit bij het kansargument van NORM.INV). Kort gezegd betekent het toepassen van ATP in dit geval dat er geen voordeel wordt gehaald uit de verbeteringen voor Excel 2003 en nieuwere versies in de functies NORM.INV, STAND.NORM.VERD en ASELECT.

Voor metingen uit een discrete verdeling nemen we aan dat de waarden in kolom B staan en de bijbehorende kansen in kolom C. Vervolgens gaan we ervan uit dat u alle rijen van kolom A wilt vullen met de kans dat er een waarde wordt ingevuld die lager is dan de waarde in kolom B in die rij. Uitgaande van 10 waarden, bevinden deze gegevens zich in cellen A1:C10. Aangezien A1 de kans bevat dat er een waarde wordt ingevuld die lager is dan de eerste waarde, moet deze cel worden ingesteld op 0. U kunt hiervoor VERT.ZOEKEN(ASELECT(); A1:C10; 2) gebruiken. Het vierde argument voor VERT.ZOEKEN is optioneel en kunt u weglaten of instellen op TRUE. De '2' betekent dat u de waarde wilt weergeven in de tweede kolom (kolom B in dit voorbeeld).

Het ATP-programma maakt gebruik van een variant van de methode voor het genereren van Poisson-verdelingen die wordt beschreven in Numerical Recipes in C, The Art of Scientific Computing van W.H. Press, S.A. Teukolsky, W. T. Vetterling en B.P. Flannery, 2e editie, Cambridge University Press, 1992, blz. 293-295. Er zijn twee manieren om eenvoudig gebruik te maken van bestaande Excel-functies.

Bij de eerste manier wordt ervan uitgegaan dat een willekeurige POISSON-variabele met het gemiddelde m een verdeling heeft die vrij goed kan worden benaderd met een BINOMIAAL(n; m/n) voor grote n. Vervolgens kunt u CRIT.BINOM(n; m/n; ASELECT()) aanroepen. De keuze van n is afhankelijk van m. Het moet voldoende zijn als n groter is dan 1000 maal m.

Bij de tweede manier wordt de POISSON-verdeling gerelateerd aan een exponentiële verdeling. Als gebeurtenissen plaatsvinden volgens een POISSON-proces met een snelheid m per tijdseenheid, heeft de tijd tussen gebeurtenissen een exponentiële verdeling met het gemiddelde 1/m. Voor een POISSON-meting kunt u een reeks observaties uit deze exponentiële verdeling nemen en tellen hoeveel er plaatsvinden voordat de som groter is dan 1. Gebruik GAMMA.INV(ASELECT(); 1; 1/m) om een observatie te verkrijgen uit deze exponentiële verdeling. Deze manier is geschikt wanneer m relatief dicht bij 0 ligt.

Regression

Het hulpprogramma Regression gebruikt de functie LIJNSCH van Excel. In het artikel over LIJNSCH worden uitgebreide verbeteringen voor Excel 2003 en nieuwere versies beschreven.

Klik op het volgende artikelnummer in de Microsoft Knowledge Base voor meer informatie over LIJNSCH:
828533Beschrijving van de functie LIJNSCH in Excel
Als u Excel 2002 of een oudere versie gebruikt, gelden voor zowel het programma Regression uit het ATP als LIJNSCH dezelfde twee nadelen:
  • Waarden voor kwadraatsommen voor regressie, r-kwadraat en f-statistiek zijn altijd onjuist in situaties waarin de regressie wordt afgedwongen via het punt van oorsprong.

    Voor LIJNSCH betekent dit 'derde argument ingesteld op ONWAAR in plaats van WAAR of weggelaten'. Voor het ATP-programma betekent dit 'selectievakje Constante is nul is ingeschakeld'.
  • LIJNSCH en het ATP-programma zijn niet gevoelig voor problemen met collineariteit. Het artikel over LIJNSCH behandelt de rekenkundige benadering van LIJNSCH in Excel 2003 en nieuwere versies die is bedoeld om collineariteit of bijna-collineariteit op te sporen en dienovereenkomstig te handelen.
Beide tekortkomingen van LIJNSCH zijn verholpen in Excel 2003 en nieuwere versies. De prestaties van het ATP-programma Regression zijn daardoor ook verbeterd. Er zijn geen aanpassingen doorgevoerd in de code van het programma. Het programma is verbeterd doordat een verbeterde Excel-functie wordt aangeroepen. De auteur van het artikel is van mening dat de verbetering van LIJNSCH de belangrijkste is van alle verbeteringen die zijn doorgevoerd in de statistische functies van Excel.

De volgende tabel bevat de uitvoer van het programma Regression voor oudere en nieuwere versies van Excel met het selectievakje Constante is nul ingeschakeld. De tabel vormt een verduidelijking van het hierboven beschreven eerste nadeel. In eerdere versies van Excel zijn de kwadraatsommen voor regressie negatief, net als de waarde voor r-kwadraat.
Deze tabel samenvouwenDeze tabel uitklappen
X-enY-en
111
212
313
Excel 2002 en lager
OVERZICHT VAN UITVOER
Regressiestatistieken
Meervoudig r-kwadraat65535
R-kwadraat-20.4285714
Aangepast r-kwadraat-20.9285714
Standaardfout4.629100499
Metingen3
ANOVA
dfSSMSFSignificantie F
Regressie1-40.85714286-40.85714286-1.90666667#NUM!
Rest242.8571428621.42857143
Totaal32
Excel 2003 en nieuwere versies van Excel
OVERZICHT VAN UITVOER
Regressiestatistieken
Meervoudig r-kwadraat0.949342311
R-kwadraat0.901250823
Aangepast r-kwadraat0.401250823
Standaardfout4.629100499
Metingen3
ANOVA
dfSSMSFSignificantie F
Regressie1391.1428571391.142857118.253333330.14637279
Rest242.8571428621.42857143
Totaal3434

t-Test: Paired Two Sample for Means

Zoals eerder gezegd, is het beter dit programma niet te gebruiken als er een of meer gegevenswaarden (mogelijk) ontbreken. De prototypische toepassing van deze test is een experiment met metingen van proefpersonen vóór en na (Before en After) een behandeling, bijvoorbeeld het gewicht vóór en na een dieet van 60 dagen. Als er geen metingen ontbreken, werkt het programma prima. Als het aantal ontbrekende Before- en After-metingen niet gelijk is, verschijnt er een foutbericht en levert het programma geen resultaat op. Als er metingen ontbreken maar het aantal ontbrekende Before- en After-metingen gelijk is, retourneert het programma antwoorden die verschillende fouten bevatten.

De standaardprocedure bestaat eruit een proefpersoon uit de gegevens te verwijderen als de Before- of After-meting ontbreekt en de gegevens te analyseren die uitsluitend betrekking hebben op proefpersonen met zowel Before- als After-metingen. De TTEST-functie van Excel verwerkt metingen met ontbrekende gegevens volgens deze standaardprocedure.

Dit probleem doet zich niet voor bij de andere twee t-Test-programma's, Two-Sample Assuming Equal Variances en Two-Sample Assuming Unequal Variances.

z-Test: Two Sample for Means

Eerder in dit artikel is toegelicht dat de standaardnormaalverdeling van het programma voor het genereren van willekeurige getallen (RNG) geen gebruikmaakt van de functie NORMS.INV (eigenlijk is het zo dat NORM.INV de functie NORMS.INV aanroept). In plaats daarvan beschikt RNG over een ingebouwde procedure voor het zoeken van inverse normaalwaarden. Het probleem is echter dat deze procedure slecht presteert.

Het programma z-Test maakt wel gebruik van NORMS.INV en profiteert dus ook van de verbeteringen die zijn doorgevoerd voor Excel 2003 en nieuwere versies.

Resultaten in oudere versies van Excel

Er zijn ATP-hulpprogramma's waarvan de numerieke prestaties zijn verbeterd voor Excel 2003 en nieuwere versies omdat ze een statistische functie van Excel aanroepen die is verbeterd voor Excel 2003 en nieuwere versies. Een van deze verbeteringen heeft betrekking op LIJNSCH wanneer het derde argument van deze functie is ingesteld op FALSE. Deze verbetering impliceert dat het ATP-programma Regression onjuiste resultaten oplevert in Excel 2002 en oudere versies wanneer het selectievakjeConstante is nul is ingeschakeld. In andere gevallen waarin Excel-functies zijn verbeterd, zullen gebruikers van oudere versies meestal geen verschil merken (de meeste verschillen hebben te maken met afrondingsfouten die optreden in uitzonderlijke situaties).

De drie ANOVA-programma's in het ATP zijn verbeterd door het implementeren van een algoritme dat beter overweg kan met numerieke gegevens (vergelijkbaar met de verbetering van de Excel-functie VAR). Gebruikers van deze hulpprogramma's in eerdere versies van Excel zullen alleen in uitzonderlijke gevallen verschil merken.

Waarschuwing voor gebruikers van alle versies: maak geen gebruik van t-Test: Paired Two Sample for Means als de kans bestaat dat er gegevens ontbreken.

Resultaten in Excel 2003 en nieuwere versies

De statistische functies van Excel zijn aanzienlijk verbeterd. Hierdoor zijn ook de prestaties verbeterd van verschillende ATP-programma's die deze functies aanroepen tijdens de verwerking. Alleen het ATP-programma voor het genereren van willekeurige getallen (RNG) heeft geen baat bij de verbeterde ASELECT-functie (simpelweg omdat RNG ASELECT niet aanroept maar een ingebouwde generator gebruikt). Dit is vervelend, maar nog vervelender is het speciale geval van normale verdeling van willekeurige metingen. De inverse normaalverdeling maakt eveneens gebruik van een ingebouwd mechanisme en profiteert dus evenmin van de sterk verbeterde NORMS.INV-functie.

In de volgende tabel ziet u een overzicht van de ATP-programma's en de Excel-functies die ze aanroepen, die zijn verbeterd voor Excel 2003 en nieuwere versies. Lezers wordt verwezen naar afzonderlijke artikelen voor elke aangeroepen Excel-functie.
Deze tabel samenvouwenDeze tabel uitklappen
ATP-programmaAangeroepen Excel-functies
ANOVA: Single FactorVAR, F.INVERSE
ANOVA: Two-Factor With ReplicationVAR, F.INVERSE
ANOVA: Two-Factor Without ReplicationVAR, F.INVERSE
Correlatie
Covariantie
Descriptive StatisticsSTDEV, T.INV, VAR
Exponential Smoothing
F-Test Two-Sample for VariancesVAR, F.INVERSE
Fourier-analyse
Histogram
Voortschrijdend gemiddelde
RNG
Rang en Percentiel
RegressieLIJNSCH
SteekproevenASELECT
t-Test: Paired Two Sample for MeansVAR, PEARSON, T.INV
t-Test: Two-Sample Assuming Equal VariancesVAR, T.INV
t-Test: Two-Sample Assuming Unequal VariancesVAR, T.INV
z-Test: Two Sample for MeansSTAND.NORM.VERD, NORMS.INV
Voor alle functies in de tabel behalve LIJNSCH en ASELECT zijn de enige verschillen tussen oudere en nieuwere versies van Excel het gevolg van afrondingsfouten in uitzonderlijke situaties. LIJNSCH is sterk verbeterd, zoals eerder is toegelicht. ASELECT is ook verbeterd. Vreemd genoeg maakt het programma Steekproeven wel gebruik van ASELECT, maar beschikt RNG over een ingebouwde generator die beduidend minder presteert wanneer een grote reeks willekeurige metingen noodzakelijk is.

Conclusies

Afgezien van code-aanpassingen in het ATP voor de drie ANOVA-programma's, is de ATP-code niet herschreven. De ATP-programma's presteren echter toch beter omdat ze verbeterde Excel-functies aanroepen (zie de bovenstaande tabel). Fouten in de test t-Test: Paired Two Sample for Means-tests zijn niet gecorrigeerd in Excel 2003 of nieuwere versies. De verbeteringen van het programma Regression vallen het meest op. Zo levert LIJNSCH geen onjuiste resultaten meer op wanneer het selectievakje Constante is nul is ingeschakeld en kan de functie nu goed overweg met collineariteit.

Eigenschappen

Artikel ID: 829208 - Laatste beoordeling: dinsdag 13 maart 2007 - Wijziging: 4.0
De informatie in dit artikel is van toepassing op:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
Trefwoorden: 
kbformula kbexpertisebeginner kbfunctions kbfuncstat kbinfo KB829208

Geef ons feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com