[Excel] Normale verdeling maken (Gauss-verdeling)

[Kiebus]

Ik ben bezig met een afstudeeronderzoek in een bedrijf.
Nu ben ik de storingstijden van een bepaalde machine bij aan het houden van het afgelopen half jaar in 2006. Dit zijn in het totaal 805 records in dat half jaar. (n = aantal waarnemingen = 805)

Dit zijn waarnemingen in aantal min. Hiervan zijn er natuurlijk een hoop hetzelfde. Ik wil deze records in een normaal verdeling kunnen zetten (Gauss-verdeling) in Excel. Nu is mijn vraag hoe doe ik dit precies?

Ik heb alleen basiskennis van excel (dus geen kennis van vba). Ik ben al zo ver dat ik het gemiddelde en de standaard deviatie berekend heb ( =AVERAGE(A3:A807)=22,21192547
en =STDEV(A3:A807)=37,0336)

Deze grafiek zal niet helemaal een 'normaal' verdeling worden, aangezien de meeste stoptijden liggen tussen de 0 en de 22 min.(ongeveer 600 waarnemingen) De rest zit erboven en er zijn enkele uitschieters naar een paar uur. (Ik vraag me sowieso af, of hier wel een normale grafiek van te maken valt, aangezien 1 keer de standaard deviatie meer is dan van 0 tot aan het gemiddelde)

Ik heb GOT doorzocht op het maken van een Gauss grafiek, maar ik heb hier niets bruikbaars uit kunnen halen. Ook op google heb ik veel info gevonden over gauss grafieken, maar niet over het specifiek maken van een grafiek in Excel.

Dus concreet, welke stappen moet ik doorlopen om van een (ongebruikelijke, niet normaal verdeelde) lijst met waarnemening, een grafiek te genereren die lijkt op een gauss grafiek?

(Ik weet niet of het handig is om de lijst met waarnemingen hier te posten?)
(ik gebruik de Engelse versie van Excel (Excel 2000))

[under-world]

Ik zie dat je engelse versie gebruikt. Volgens mij onder /tools /data analysis/descriptive statistics
kun je standaard normale verdeling als formule gebruiken. Je kan dan namelijk kiezen uit de volgende opties.

Mean { Standard Error { Median { Mode { Standard Deviation
Sample Variance { Kurtosis { Skewness { Range
Minimum { Maximum { Sum { Count { Con¯dence Level(95.0%).

In het Nederlands is het volgens mij =stand.norm.verd(….))

[brekki]

Als ik het snap, zoek je gewoon een histogram met op X een verdeling in stoptijden en op Y hoevaak het voorgekomen is.

Dat gaat het handigst via "tools -> data analysis -> histogram". (moet wel het analysis toolpak geinstalleerd zijn)

Daar heb je verschillende opties ... iig handig om de grafiek aan te vinken

[Kiebus]

brekki schreef op donderdag 01 maart 2007 @ 10:07:
Als ik het snap, zoek je gewoon een histogram met op X een verdeling in stoptijden en op Y hoevaak het voorgekomen is.

Dat gaat het handigst via "tools -> data analysis -> histogram". (moet wel het analysis toolpak geinstalleerd zijn)

Daar heb je verschillende opties ... iig handig om de grafiek aan te vinken

voor zover ik weet heb ik geen toolpack geinstalleerd. Hoe en waar installeer ik dat toolpack? (Als ik daar uberhaupt rechten voor heb.... )

Wat bedoel je met: "iig handig om de grafiek aan te vinken ;)"

[brekki]

Als je in het "tools" menu geen "data analysis" ziet staan zal het niet geinstalleerd zijn. Via "tools" "add-ins" kun je zien wat er geinstalleerd staat, en wel aanwezige niet geinstalleerde packs installeren. Als het daar niet staat weet ik het niet.


Als je er niet aan kunt komen moet je eens kijken naar de "frequency" functie, staat wel in excel help. Daarmee kun je zelf makkelijk tellen hoevaak een getal binnen een range voorkomt.

[Dido]

Ook zonder toolpacks en dergelijk is dit prima te doen.

Maak een kolom (zeg kolom met waarden van 0 to je max. aantal minuten.
Daarnaast een kolom met iets als =COUNTIF($A$3:$A$807,"<="&B2)-C1

Dan heb je een lijstje van hoe vaak elke waarde voorkomt, zet dat in een grafiek, en klaar is Klara.

Je kunt hetzelfde doen met ranges, volgens hetzelfde principe.

[Janoz]

Je wilt de storing in een normale verdeling zetten, maar je weet niet eens of hij uberhaupt wel normaal verdeeld is? Het is heel leuk dat je het gemiddelde en een standaard deviatie uit kunt rekenen, maar dat wil helemaal nog niet zeggen dat de uiteindelijke klok kromme ook daadwerkelijk de werkelijkheid voorsteld.

Het lijkt me veel voor de hand liggender om een histogram te maken. Zorg dat je de balkjes breed genoeg maakt zodat er genoeg samples in zitten, maar smal genoeg om nog een beetje een fatsoenlijk plaatje te krijgen. Het zou best kunnen dat heir een klok kromme uitkomt, maar als ik enkel al naar de gegevens kijk die je afgeeft (meeste tussen 0 en 22 en de rest is veel langer) denk ik niet dat er een echte klok kromme uit gaat komen.

[Kiebus]

@Janoz
Ik heb ook niet gezegt dat het een normale verdeling betreft. Ik wil alleen een grafiek maken, die je normaal gesproken gebruikt bij een normale verdeling (gauss kromme dus). Als ik een goede voorspelling doe dan komt er een hele rare soort gauss kromme uit (met een grote berg aan de linkerkant, en een steile snel aflopende lijn aan de rechterkant, met een hele lange lijn tegen de x-as aan), omdat het een niet normale verdeling is.
Als ik een histogram maak (wat ik dus ook prima vind) dan kan ik daar een trendlijn overheen zetten, en dan heb ik ook mijn kromme... Dus die oplossing is ook prima.


@Dido
Ik ben helaas een redelijke leek op Excel gebied. De countif funktie heb ik al vaker gebruikt, maar dan voor maar 1 argument. Jij hebt op de een of andere manier er voor gezorgt dat er meerdere argumenten in zitten. Maar hoe je dat gedaan hebt begrijp ik niet helemaal.
Als ik dit in mijn cel zet naast mijn eerste record:

=COUNTIF($A$3:$A$807;("<="&B2)-C1)

Dan snap ik dat het eerste stuk de rij is waarin die kijkt, en het 2de stuk na de ; het ergument waar die naar kijkt. Alleen hoe moet ik dit stukje lezen, en begrijpen? ("<="&B2)-C1)
Als ik hier ff een klein stukje uitleg over krijg, denk ik dat ik het wel begrijp.

Ik neem aan dat ik deze formule als ik hem goed begrijp door moet trekken naar beneden toe, over de gehele kolom met waarnemingen?


@brekki
Ik heb momenteel mogelijkheid om het toolpack te kunnen installeren. Ik kan dit wel regelen als het noodzakelijk is voor de oplossing.

[ Voor 5% gewijzigd door Kiebus op 01-03-2007 11:57 ]

[Janoz]

Kiebus schreef op donderdag 01 maart 2007 @ 11:55:
@Janoz
Ik heb ook niet gezegt dat het een normale verdeling betreft. Ik wil alleen een grafiek maken, die je normaal gesproken gebruikt bij een normale verdeling (gauss kromme dus).

Ah, een naamgevings probleempje. De grafiek die je maakt is niet een gauss kromme. Je maakt een frequentie histogram. Wanneer je verdeling een normale verdeling benaderd dan zal de uitkomst van dat frequentie histogram de form van een gauss kromme gaan benaderen.

[Kiebus]

Janoz schreef op donderdag 01 maart 2007 @ 12:04:
[...]

Ah, een naamgevings probleempje. De grafiek die je maakt is niet een gauss kromme. Je maakt een frequentie histogram. Wanneer je verdeling een normale verdeling benaderd dan zal de uitkomst van dat frequentie histogram de form van een gauss kromme gaan benaderen.

Inderdaad! Zoiets is mijn bedoeling ja!
Alleen nu blijft de vraag nog, hoe maak ik die grafiek?? Dat is mij momenteel nog (te) onduidelijk... (ben ik dom? )

[Dido]

Kiebus schreef op donderdag 01 maart 2007 @ 11:55:
@Dido
Ik ben helaas een redelijke leek op Excel gebied. De countif funktie heb ik al vaker gebruikt, maar dan voor maar 1 argument. Jij hebt op de een of andere manier er voor gezorgt dat er meerdere argumenten in zitten. Maar hoe je dat gedaan hebt begrijp ik niet helemaal.
Als ik dit in mijn cel zet naast mijn eerste record:

=COUNTIF($A$3:$A$807;("<="&B2)-C1)

Dan snap ik dat het eerste stuk de rij is waarin die kijkt, en het 2de stuk na de ; het ergument waar die naar kijkt. Alleen hoe moet ik dit stukje lezen, en begrijpen? ("<="&B2)-C1)
Als ik hier ff een klein stukje uitleg over krijg, denk ik dat ik het wel begrijp.

Ik neem aan dat ik deze formule als ik hem goed begrijp door moet trekken naar beneden toe, over de gehele kolom met waarnemingen?

Die -C1 hoort niet binnen je COUNTIF

Je hebt je data in kolom A, je vult kolom B vanaf B2 met oplopense cijfertjes (1, 2, 3, enz. tot je maximaal aantal minuten).

Dan zet je in kolom C de countif.

Je telt in range A3 tot A807 het aantal waarden dat lager is dan de waarde in je B kolom.
In B2 dus alle waarnemingen <= 1 minuut, in B3 alle waarnemingen <= 2 minuten, etc.
Je trekt daar de waarde van de voorgaande tellingen vanaf, natuurlijk (omdat je anders waarnemingen dubbel telt.).

Ik bedenk me net dat -C1 niet juist is: -SUM(C$1:C1) moet dat zijn.

Voor je range A3 tot A807 gebruik je ook "$" om te voorkomen dat de waarden aangepast worden als je, inderdaad, de hele formule naar beneden kopieert.

[freddifish]

ik zou zelf ook even bij de toolpaks het analysis toolpak toevoegen en dan de module histogram gebruiken

maar als het moeilijk moet, lees dan hier hoe: http://www.chemistry.scie...08_histogram_in_excel.doc

[Kiebus]

@dido,

Ontzettend bedankt voor je heldere uitleg!

Ik heb nu: =COUNTIF($A$3:$A$807;("<="&B4))-SUM(C$3:C3) enz enz. Dit lijkt inderdaad gewoon goed te werken.
Ik denk dat ik 'klasses' van 2 neem, dus 2,4,6,8 enz.

Voor alle andere mensen die me geholpen hebben, bedankt!

[ Voor 1% gewijzigd door Kiebus op 06-03-2007 09:48 . Reden: typo ]