Beschrijving van de numerieke verbetering in Analysis ToolPak ANOVA-programma's in Excel

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

Op deze pagina

Samenvatting

In dit artikel worden de rekenkundige verbeteringen in elk van de drie Analysis ToolPak ANOVA-hulpprogramma's beschreven. Dit artikel illustreert ook onnauwkeurige resultaten in Microsoft Excel 2002 en oudere versies in extreme situaties.

Meer informatie

Bij veel functies wordt de som van kwadratische afwijkingen ten opzichte van een gemiddelde berekend Daarvoor gebruiken Microsoft Office Excel 2003 en nieuwere versies een procedure met twee stappen waarbij in de eerste stap het gemiddelde wordt bepaald en vervolgens op basis daarvan in de tweede stap de som van de kwadratische afwijkingen wordt berekend.

In precisieberekeningen wordt hetzelfde resultaat verkregen in oudere versies van Excel die de 'rekenmachineformule' gebruiken. Deze formule is zo genoemd omdat deze op grote schaal werd gebruikt in de tijd dat statistici rekenmachines in plaats van computers gebruikten. Met de rekenmachineformule wordt in oudere versies van Excel de som van de kwadraten van de metingen bepaald, waarna van dit totaal de volgende hoeveelheid wordt afgetrokken:
((som van metingen)^2) / aantal metingen
Deze berekening wordt uitgevoerd in één stap.

In beperkte precisieberekeningen treden bij de rekenmachineformule afrondingsfouten op in extreme gevallen. Excel 2002 en oudere versies gebruiken de rekenmachineformule voor de meeste functies waarvoor een som van kwadratische afwijkingen ten opzichte van een gemiddelde nodig is (zoals VAR, STDEV, RICHTING en PEARSON). Deze versies van Excel gebruiken echter ook de numeriek krachtigere procedure in twee stappen voor de functies CORRELATIE, COVARIANTIE en DEV.KWAD.

Experts in statistische berekeningen raden het gebruik van de rekenmachineformule af. In teksten over statische berekening wordt de rekenmachineformule gepresenteerd als voorbeeld van een ongeschikte methode. Helaas maken alle drie de Analysis ToolPak (ATP) ANOVA-programma's in Excel 2002 en oudere versies veel gebruik van de rekenmachineformule of een daarmee vergelijkbaar enkelvoudig gegevensverwerkingsproces.

Excel 2003 en nieuwere versies gebruiken een procedure met twee stappen voor alle drie de ATP ANOVA-modellen. In dit artikel worden de volgende rekenkundige verbeteringen in de drie ATP ANOVA-modellen beschreven:
  • Single Factor
  • Two-Factor with Replication
  • Two-Factor without Replication
Deze modellen worden verderop in dit artikel besproken.

Omdat Excel voor DEV.KWAD altijd de procedure in twee stappen heeft gebruikt, wordt deze procedure in dit artikel geregeld gebruikt om de verbeterde procedures te beschrijven. Deze herziene procedures roepen ofwel DEV.KWAD aan, ofwel een gebruikerscode waarvan de functionaliteit exact overeenkomt met de functionaliteit van DEV.KWAD.

Voor elk ANOVA-programma bevat de ATP-uitvoer een samenvattingstabel met de waarden van Aantal, Som, Gemiddelde en Variantie, en een ANOVA-tabel met verschillende sommen van kwadraten en waarden van SS, df, MS, F en P-waarde. Resultaten in de samenvattingstabel worden berekend door het aanroepen van de Excel-functies AANTAL, SOM, GEMIDDELDE en VAR. Van deze vier functies heeft alleen VAR te maken met afrondingsfouten.

Excel 2002 en oudere versies implementeren VAR met de rekenmachineformule. In het volgende artikel over VAR worden de verbeteringen beschreven die in Excel 2003 en nieuwere versies zijn doorgevoerd. Dit artikel laat u ook experimenteren met numerieke gegevens zodat u ziet wanneer afrondingsfouten mogelijk optreden in oudere versies van Excel.

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


Aangezien in dit artikel de drie ANOVA-modellen worden behandeld, wordt hier vooral gekeken naar de resulterende ANOVA-tabellen. De samenvattingstabellen werken in alle gevallen naar behoren in Excel 2003 en nieuwere versies. In Excel 2002 en oudere versies treden problemen op in de kolom Variantie wanneer gegevens extreme waarden hebben.

In dit artikel zijn de samenvattingstabellen echter opgenomen in de modelsecties omdat deze tabellen handig kunnen zijn ter vergelijking wanneer u de gewijzigde voorbeelden in de Bijlage bekijkt.

Model 1: Single Factor

Hier volgt een eenvoudig gegevensvoorbeeld.
Deze tabel samenvouwenDeze tabel uitklappen
ANOVA 1, BASISMODEL:
123
244
365
486
57
68
Anova: Single Factor
SAMENVATTING
GroepenAantalSomGemiddeldeVariantie
Kolom 16213.53.5
Kolom 242056.666667
Kolom 36335.53.5
ANOVA
Bron van variatieSSdfMSFP-waardeF crit
Tussen groepen12.7526.3751.5068180.2578973.805567
Binnen groepen55134.230769
Totaal67.7515
Excel 2002 en oudere versies gebruiken de volgende pseudocode om de kwadraatsom te berekenen:
GrandSum = 0;
GrandSumOfSqs = 0;
GrandSampleMeanSqrd = 0;
GrandMeanSqrd = 0;
GrandSampleSize = 0;

For s = 1 to Number_of_Samples do
   GrandSum = GrandSum + som van metingen in steekproef s-th;
   GrandSumOfSqs = GrandSumOfSqs + som van de kwadraten van alle metingen in steekproef s-th;
   GrandSampleMeanSqrd = GrandSampleMeanSqrd  +
      (som van metingen in steekproef s-th^2)/grootte van steekproef s-th;
   GrandSampleSize = GrandSampleSize + grootte van steekproef s-th
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs ? GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd ? GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs ? GrandSampleMeanSqrd;
Deze benadering is essentieel voor de rekenmachineformule. In deze benadering worden de sommen van de kwadraten van metingen berekend en wordt daarvan vervolgens een hoeveelheid afgetrokken, net zoals VAR de som van de kwadraten van de metingen berekent en vervolgens som van metingen^2/steekproefgrootte aftrekt. Een gelijksoortige pseudocode voor model 2 en model 3 is niet opgenomen.

Ook bij model 2 en 3 wordt de som van kwadraten berekend en wordt van de som van kwadraten een hoeveelheid afgetrokken, zoals bij de rekenmachineformule. Helaas worden in algemene statistische teksten vaak benaderingen van ANOVA voorgesteld die vergelijkbaar zijn met de benadering die eerder in dit artikel werd weergegeven.

Excel 2003 en nieuwere versies gebruiken een andere benadering voor het berekenen van de verschillende waarden in de SS-kolom van de ANOVA-tabel. Ter illustratie wordt er in dit artikel vanuit gegaan dat de numerieke gegevens uit het eerdere voorbeeld verschijnen in cel A2:C7, met ontbrekende gegevens in cel B6 en B7.
  • Totaal SS is slechts DEV.KWAD toegepast op alle gegevens, zoals DEV.KWAD(A2:C7). DEV.KWAD werkt correct, hoewel er gegevens ontbreken.
  • Tussen groepen SS is Totaal SS minus de som van DEV.KWAD toegepast op elke kolom, bijvoorbeeld DEV.KWAD(A2:A7) + DEV.KWAD(B2:B7) + DEV.KWAD(C2:C7).
  • Binnen groepen SS is Totaal SS minus Tussen groepen SS.
Als de waarden in de SS-kolom van de ANOVA-tabel juist worden berekend, volgt daaruit de nauwkeurigheid van de overige waarden in de tabel.

Model 2: Two-Factor with Replication

Hier volgt een eenvoudig gegevensvoorbeeld.
Deze tabel samenvouwenDeze tabel uitklappen
ANOVA 2, BASISMODEL:groep 1groep 2groep 3
proef 1123
244
365
proef 2486
5107
6128
Anova: Two-Factor With Replication
SAMENVATTINGgroep 1groep 2groep 3Totaal
proef 1
Aantal3339
Som6121230
Gemiddelde2443.333333
Variantie1412.5
proef 2
Aantal3339
Som15302166
Gemiddelde51077.333333
Variantie1416.25
Totaal
Aantal666
Som214233
Gemiddelde3.575.5
Variantie3.5143.5
ANOVA
Bron van variatieSSdfMSFP-waardeF crit
Steekproef72172366.22E-054.747221
Kolommen37218.59.250.0037093.88529
Interactie924.52.250.1479733.88529
Binnen24122
Totaal14217
Nogmaals, als de waarden in de SS-kolom juist worden berekend, volgt daaruit de nauwkeurigheid van alle andere waarden in het ANOVA-gedeelte van het resultaat.

Hier volgt de rekenkundige procedure voor Excel 2003 en nieuwere versies. Deze procedure gebruikt DEV.KWAD voor de berekening van de verschillende waarden in de SS-kolom van de ANOVA-tabel. Ter illustratie wordt in dit voorbeeld aangenomen dat de numerieke gegevens verschijnen in de cellen B2:D7.
  • Totaal SS is slechts DEV.KWAD toegepast op alle gegevens, zoals DEV.KWAD(B2:D7).
  • Steekproef SS is Totaal SS minus de som van DEV.KWAD toegepast op elke steekproef, bijvoorbeeld DEV.KWAD(B2:D4) + DEV.KWAD(B5:D7).
  • Kolommen SS is Totaal SS minus de som van DEV.KWAD toegepast op elke kolom, bijvoorbeeld DEV.KWAD(B2:B7) + DEV.KWAD(C2:C7) + DEV.KWAD(D2:D7).
  • Binnen SS is de som van DEV.KWAD toegepast op elke proef of elk groepenpaar, zoals DEV.KWAD(B2:B4) + DEV.KWAD(C2:C4) + DEV.KWAD(D2:D4) + DEV.KWAD(B5:B7) + DEV.KWAD(C5:C7) + DEV.KWAD(D5:D7).
  • Interactie SS is gelijk aan Totaal SS minus Steekproef SS minus Kolommen SS minus Binnen SS.

Model 3: Two-Factor without Replication

Hier volgt een eenvoudig gegevensvoorbeeld.
Deze tabel samenvouwenDeze tabel uitklappen
ANOVA 3, BASISMODEL:LAAG GEM HOOG
MATIG123
244
365
GEMIDDELD486
5107
6128
GOED71410
8126
9102
Anova: Two-Factor Without Replication
SAMENVATTINGAantalSomGemiddeldeVariantie
MATIG3621
3103.3333331.333333
3144.6666672.333333
GEMIDDELD31864
3227.3333336.333333
3268.6666679.333333
GOED33110.3333312.33333
3268.6666679.333333
321719
LAAG 94557.5
GEM 9788.66666716
HOOG9515.6666676.25
ANOVA
Bron van variatieSSdfMSFP-waardeF crit
Rijen176.6667822.083335.760870.0014762.591094
Kolommen68.66667234.333338.9565220.0024553.633716
Fout61.33333163.833333
Totaal306.666726
Als de waarden in de SS-kolom juist worden berekend, volgt daaruit de nauwkeurigheid van alle overige gegevens in de ANOVA-tabel.

Excel 2003 en nieuwere versies gebruiken de volgende rekenkundige procedure. De procedure gebruikt DEV.KWAD voor de berekening van de waarden in de SS-kolom van de ANOVA-tabel. Ter illustratie wordt er in dit voorbeeld vanuit gegaan dat het cellenbereik uit het eerdere voorbeeld A1:D10 is. De numerieke gegevens verschijnen derhalve in de cellen B2:D10.
  • Totaal SS is slechts DEV.KWAD toegepast op alle gegevens, zoals DEV.KWAD(B2:D10).
  • Rijen SS is Totaal SS minus de som van DEV.KWAD toegepast op elke rij, bijvoorbeeld DEV.KWAD(B2:D2) + DEV.KWAD(B3:D3) + DEV.KWAD(B4:D4) + DEV.KWAD(B5:D5) + DEV.KWAD(B6:D6) + DEV.KWAD(B7:D7) + DEV.KWAD(B8:D8) + DEV.KWAD(B9:D9) + DEV.KWAD(B10:D10).
  • Kolommen SS is Totaal SS minus de som van DEV.KWAD toegepast op elke kolom, bijvoorbeeld DEV.KWAD(B2:B10) + DEV.KWAD(C2:C10) + DEV.KWAD(D2:D10).
  • Fout SS is Totaal SS minus Rijen SS minus Kolommen SS.

Resultaten in Excel 2002 en oudere versies

In extreme gevallen, wanneer de gegevens veel significante cijfers maar tevens een kleine variantie bevatten, leidt de rekenmachineformule tot onnauwkeurige resultaten. De bijlage verderop in dit artikel bevat voorbeelden van afrondingsproblemen in dergelijke extreme gevallen.

Resultaten in Excel 2003 en nieuwere versies

Excel 2003 en nieuwere versies gebruiken een gegevensverwerkingsproces in twee stappen. In de eerste stap berekenen Excel 2003 en nieuwere versies de som en het aantal van de gegevenswaarden. Op basis van dit resultaat kan Excel het steekproefgemiddelde (gemiddelde) berekenen.

Bij de tweede gegevensverwerking wordt vervolgens het kwadratisch verschil tussen elk gegevenspunt en het steekproefgemiddelde berekend. Deze kwadratische verschillen worden opgeteld. Het gevolg is dat de resultaten in Excel 2003 en nieuwere versies numeriek stabieler zijn.

Conclusies

Een uit twee stappen bestaande benadering verbetert de numerieke prestaties in alle drie de ATP ANOVA-hulpprogramma's in Excel 2003 en nieuwere versies ten opzichte van oudere versies van Excel. De resultaten die u verkrijgt met Excel 2003 en nieuwere versies zijn nooit minder nauwkeurig dan de resultaten die u verkrijgt met oudere versies van Excel.

In de praktijk zijn de resultaten echter doorgaans hetzelfde. Dat komt omdat de gegevens doorgaans niet van die ongebruikelijke aard zijn die in de volgende bijlage wordt beschreven. Numerieke onstabiliteit treedt in oudere versies van Excel het meest op wanneer gegevens zowel een groot aantal significante cijfers bevatten als weinig variatie tussen de gegevenswaarden.

Als u met een oudere versie van Excel werkt en wilt controleren of Excel 2003 of een nieuwere versie andere ANOVA-resultaten oplevert, kunt u de resultaten die u verkrijgt met de ANOVA-hulpprogramma's in uw oudere versie van Excel, vergelijken met de resultaten van de procedures die DEV.KWAD gebruiken.

Opmerking De procedures die DEV.KWAD gebruiken, zijn eerder in dit artikel beschreven voor de ANOVA-tabel die betrekking heeft op het betreffende hulpprogramma.

Als u wilt controleren of variantiewaarden in de samenvattingstabel voor elk bereik correct zijn, gebruikt u DEV.KWAD(bereik)/(AANTAL(bereik) ? 1).

Bijlage: Numerieke voorbeelden van de prestaties van Excel 2002 en oudere versies

Voor elk basisvoorbeeld uit de modellen 1, 2 en 3 werd eerder in dit artikel het resultaat van het ATP-programma weergegeven. De resultaten bevatten de samenvattingstabellen en ANOVA-tabellen. In elk voorbeeld werden gegevens gewijzigd om een extreem voorbeeld te geven. Dit werd gedaan door 10^8 aan elke gegevenswaarde toe te voegen. Door een constante zoals 10^8 aan elke gegevenswaarde toe te voegen, wordt de Variantie in de samenvattingstabel niet beïnvloed (maar natuurlijk wel het resultaat van Gemiddelde en Som). Evenmin zouden waarden in de ANOVA-tabel beïnvloed mogen worden.

Als u de Variantie in de samenvattingstabellen en SS in de ANOVA-tabellen vergelijkt, zult u zien dat al deze waarden onjuist werden berekend in alle drie de volgende extreme modellen, met uitzondering van één waarde in model 3, die wordt aangeduid met '<---'.

In alle extreme gevallen komen de ANOVA-resultaten uit Excel 2003 en nieuwere versies overeen met de eerdere resultaten voor de basisgevallen (zoals verwacht).

ANOVA 1, extreem model met grote gegevenswaarden

Deze tabel samenvouwenDeze tabel uitklappen
100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
Anova: Single Factor
SAMENVATTING
GroepenAantalSomGemiddeldeVariantie
Kolom 166000000211E+084.8
Kolom 244000000201E+088
Kolom 366000000331E+081.6
ANOVA
Bron van variatieSSdfMSFP-waardeF crit
Tussen groepen020013.805567
Binnen groepen64134.923077
Totaal6415

ANOVA 2, extreem model met grote gegevenswaarden

Deze tabel samenvouwenDeze tabel uitklappen
groep 1groep 2groep 3
proef 1100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
proef 2100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
Anova: Two-Factor With Replication
SAMENVATTINGgroep 1groep 2groep 3Totaal
proef 1
Aantal3339
Som3000000063000000123000000129E+08
Gemiddelde1000000021000000041000000041E+08
Variantie0404
proef 2
Aantal3339
Som3000000153000000303000000219E+08
Gemiddelde1000000051000000101000000071E+08
Variantie0406
Totaal
Aantal666
Som600000021600000042600000033
Gemiddelde100000004100000007100000005.5
Variantie4.814.41.6
ANOVA
Bron van variatieSSdfMSFP-waardeF crit
Steekproef64164240.0003674.747221
Kolommen3221660.0156253.88529
Interactie3221660.0156253.88529
Binnen32122.666666667
Totaal12817

ANOVA 3, extreem model met grote gegevenswaarden

Deze tabel samenvouwenDeze tabel uitklappen
LAAG GEM HOOG
MATIG100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
GEMIDDELD100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
GOED100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
Anova: Two-Factor Without Replication
SAMENVATTINGAantalSomGemiddeldeVariantie
Rij 133000000061000000020
Rij 233000000101000000032
Rij 333000000141000000052
Rij 433000000181000000064<---
Rij 533000000221000000076
Rij 6330000002610000000910
Rij 7330000003110000001012
Rij 8330000002610000000910
Rij 9330000002110000000718
Kolom 199000000451000000058
Kolom 2990000007810000000914
Kolom 399000000511000000064
ANOVA
Bron van variatieSSdfMSFP-waardeF crit
Rijen12881620.1132812.591094
Kolommen3221620.1677723.633716
Fout128168
Totaal28826

Eigenschappen

Artikel ID: 829215 - Laatste beoordeling: dinsdag 13 maart 2007 - Wijziging: 2.0
De informatie in dit artikel is van toepassing op:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
Trefwoorden: 
kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215

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