Aanmelden

Select the product you need help with

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

Artikel ID: 829215 - Bekijk de producten waarop dit artikel van toepassing is.

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.

Naar boven | Geef ons feedback

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:

826112

(http://support.microsoft.com/kb/826112/ )

Statistische 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:
1	2	3
2	4	4
3	6	5
4	8	6
5		7
6		8
Anova: Single Factor

SAMENVATTING
Groepen	Aantal	Som	Gemiddelde	Variantie
Kolom 1	6	21	3.5	3.5
Kolom 2	4	20	5	6.666667
Kolom 3	6	33	5.5	3.5


ANOVA
Bron van variatie	SS	df	MS	F	P-waarde	F crit
Tussen groepen	12.75	2	6.375	1.506818	0.257897	3.805567
Binnen groepen	55	13	4.230769

Totaal	67.75	15

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 1	groep 2	groep 3
proef 1	1	2	3
	2	4	4
	3	6	5
proef 2	4	8	6
	5	10	7
	6	12	8
Anova: Two-Factor With Replication

SAMENVATTING	groep 1	groep 2	groep 3	Totaal
proef 1
Aantal	3	3	3	9
Som	6	12	12	30
Gemiddelde	2	4	4	3.333333
Variantie	1	4	1	2.5

proef 2
Aantal	3	3	3	9
Som	15	30	21	66
Gemiddelde	5	10	7	7.333333
Variantie	1	4	1	6.25

Totaal
Aantal	6	6	6
Som	21	42	33
Gemiddelde	3.5	7	5.5
Variantie	3.5	14	3.5


ANOVA
Bron van variatie	SS	df	MS	F	P-waarde	F crit
Steekproef	72	1	72	36	6.22E-05	4.747221
Kolommen	37	2	18.5	9.25	0.003709	3.88529
Interactie	9	2	4.5	2.25	0.147973	3.88529
Binnen	24	12	2

Totaal	142	17

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
MATIG	1	2	3
	2	4	4
	3	6	5
GEMIDDELD	4	8	6
	5	10	7
	6	12	8
GOED	7	14	10
	8	12	6
	9	10	2

Anova: Two-Factor Without Replication

SAMENVATTING	Aantal	Som	Gemiddelde	Variantie
MATIG	3	6	2	1
	3	10	3.333333	1.333333
	3	14	4.666667	2.333333
GEMIDDELD	3	18	6	4
	3	22	7.333333	6.333333
	3	26	8.666667	9.333333
GOED	3	31	10.33333	12.33333
	3	26	8.666667	9.333333
	3	21	7	19

LAAG	9	45	5	7.5
GEM	9	78	8.666667	16
HOOG	9	51	5.666667	6.25


ANOVA
Bron van variatie	SS	df	MS	F	P-waarde	F crit
Rijen	176.6667	8	22.08333	5.76087	0.001476	2.591094
Kolommen	68.66667	2	34.33333	8.956522	0.002455	3.633716
Fout	61.33333	16	3.833333

Totaal	306.6667	26

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

100000001	100000002	100000003
100000002	100000004	100000004
100000003	100000006	100000005
100000004	100000008	100000006
100000005		100000007
100000006		100000008

Anova: Single Factor

SAMENVATTING
Groepen	Aantal	Som	Gemiddelde	Variantie
Kolom 1	6	600000021	1E+08	4.8
Kolom 2	4	400000020	1E+08	8
Kolom 3	6	600000033	1E+08	1.6


ANOVA
Bron van variatie	SS	df	MS	F	P-waarde	F crit
Tussen groepen	0	2	0	0	1	3.805567
Binnen groepen	64	13	4.923077

Totaal	64	15

ANOVA 2, extreem model met grote gegevenswaarden

Deze tabel samenvouwenDeze tabel uitklappen

	groep 1	groep 2	groep 3
proef 1	100000001	100000002	100000003
	100000002	100000004	100000004
	100000003	100000006	100000005
proef 2	100000004	100000008	100000006
	100000005	100000010	100000007
	100000006	100000012	100000008
Anova: Two-Factor With Replication

SAMENVATTING	groep 1	groep 2	groep 3	Totaal
proef 1
Aantal	3	3	3	9
Som	300000006	300000012	300000012	9E+08
Gemiddelde	100000002	100000004	100000004	1E+08
Variantie	0	4	0	4

proef 2
Aantal	3	3	3	9
Som	300000015	300000030	300000021	9E+08
Gemiddelde	100000005	100000010	100000007	1E+08
Variantie	0	4	0	6

Totaal
Aantal	6	6	6
Som	600000021	600000042	600000033
Gemiddelde	100000004	100000007	100000005.5
Variantie	4.8	14.4	1.6


ANOVA
Bron van variatie	SS	df	MS	F	P-waarde	F crit
Steekproef	64	1	64	24	0.000367	4.747221
Kolommen	32	2	16	6	0.015625	3.88529
Interactie	32	2	16	6	0.015625	3.88529
Binnen	32	12	2.666666667

Totaal	128	17

ANOVA 3, extreem model met grote gegevenswaarden

Deze tabel samenvouwenDeze tabel uitklappen

	LAAG	GEM	HOOG
MATIG	100000001	100000002	100000003
	100000002	100000004	100000004
	100000003	100000006	100000005
GEMIDDELD	100000004	100000008	100000006
	100000005	100000010	100000007
	100000006	100000012	100000008
GOED	100000007	100000014	100000010
	100000008	100000012	100000006
	100000009	100000010	100000002

Anova: Two-Factor Without Replication

SAMENVATTING	Aantal	Som	Gemiddelde	Variantie
Rij 1	3	300000006	100000002	0
Rij 2	3	300000010	100000003	2
Rij 3	3	300000014	100000005	2
Rij 4	3	300000018	100000006	4	<---
Rij 5	3	300000022	100000007	6
Rij 6	3	300000026	100000009	10
Rij 7	3	300000031	100000010	12
Rij 8	3	300000026	100000009	10
Rij 9	3	300000021	100000007	18

Kolom 1	9	900000045	100000005	8
Kolom 2	9	900000078	100000009	14
Kolom 3	9	900000051	100000006	4


ANOVA
Bron van variatie	SS	df	MS	F	P-waarde	F crit
Rijen	128	8	16	2	0.113281	2.591094
Kolommen	32	2	16	2	0.167772	3.633716
Fout	128	16	8

Totaal	288	26