gemiddelden van maanden,kwartalen en jaren

[Hansvdvoort]

Beste,

Ik heb een tabel in excel met

Duur (in dagen) en de einddatum (bv. 03-05-2006)
Totaal 400 rijen.

Klein voorbeeldje..
36 | 3-5-06
48 | 15-5-06
20 | 28-5-06
55 | 1-6-06

Nu wil ik de gemiddelde uitrekenen van de duur per maand, per kwartaal en per jaar. Uiteraard kan dit 'handmatig',maar ik zal graag willen dat ik niet steeds hoef te kijken welke rijen bij welke maand/kwartaal/jaar horen.

Iemand een idee hoe dit makkelijker kan?

 

[arafy]

hans,

vooreerst welkom. doorzoek het forum eens. er zijn al dergelijke vragen gesteld. een voorbeeldje werkt ook altijd handig. je moet het wel zippen of rarren.

steven

 

[Finch]

dat kan dmv een draaitabel (ENG: pivot table)

 

[Hansvdvoort]

Bedankt alvast,

Hier mijn bestand in .rar.

Ik heb nog nooit van een pivot gehoord,maar ik zal eens kijken of ik dat ga snappen.
Maar als iemand mij kan helpen door iets in mijn bestand te doen, dan graag...

 

[Wigi]

Hoe zou je dit manueel uitrekenen? Bv. als er een duur is van 54 dagen. Tot welke maand(en) behoort dit dan?

 

[Hansvdvoort]

Het gaat dan om de einddatum.

Als iets op 10 december 2006 begint, en eindigt op 6 januari 2007. Dan telt deze duur voor de maand januari 2007

 

[arafy]

hans,

het blijft voor mij chinees (en dat beheers ik niet). het voorbeeld maakt ook niets duidelijker. een aantal cijfertjes intikken, kan iedereen, maar wat wil je waar als resultaat? heb je het forum als eens doorzocht over kwartalen?

steven

 

[danny147]

Best Hansvdvoort

Heb bestandje voor jou.
Eerst heb ik een extra kolom bijgemaakt voor de maanden en jaren een code te geven.
Vb. Maart 2003 is code 33 3 van maart en 3 van 2003, dus november 2007 is dan 117.

In de toekomst ga je misschien problemen krijgen vanaf het jaar 2013 want voor jan 2013 krijg je code 113 is ook gelijk aan november 2003.

Je moet dan zelf in kolom D de code handmatig toevoegen. :thumb:

Groetjes Danny.

 

[Wigi]

Ik heb nog nooit van een pivot gehoord,maar ik zal eens kijken of ik dat ga snappen.

Hoe staat het hier mee? Want dat is echt wel beter dan de alternatieven met formules.

 

[Wigi]

Best Hansvdvoort

Heb bestandje voor jou.
Eerst heb ik een extra kolom bijgemaakt voor de maanden en jaren een code te geven.
Vb. Maart 2003 is code 33 3 van maart en 3 van 2003, dus november 2007 is dan 117.

In de toekomst ga je misschien problemen krijgen vanaf het jaar 2013 want voor jan 2013 krijg je code 113 is ook gelijk aan november 2003.

Je moet dan zelf in kolom D de code handmatig toevoegen. :thumb:

Groetjes Danny.

Bedankt voor de moeite Danny.

Maar er zijn toch nog een aantal dingen die zullen aangepast moeten worden.

- het moeten gemiddelden zijn, en geen sommen
- januari heeft nooit getallen?
- de 23, 33, 43, ... in de bovenste tabel zou ik niet handmatig inkloppen, maar met een formule doen. Op basis van rij en kolomkop moet dat doenbaar zijn.
- in rij 24 moeten het gemiddelden zijn, is veel simpeler dan die SOM en zelf te delen door het aantal. (dat dan nog niet echt op de beste plaats staat).

Snap je nu mijn suggestie van een draaitabel? (en Finch zei dat ook al)

Wigi

 

[danny147]

Beste Hansvdvoort

Heb de fouten weggewerkt in de vorige versie. (zie bestandje).
Kolom D is nu aangemaakt met een formule.
Vb. nov 2007 is dan code 1107.
Daarmee is ook de fout weggewerkt voor het jaar 2013 en daarop volgende jaren.

Hopelijk heb je hier wat aan Hansvdvoort

Beste Wigi en Finch, waar blijven jullie idieën

Snap je nu mijn suggestie van een draaitabel?

dat kan dmv een draaitabel

Groetjes Danny.

 

[Wigi]

Beste Wigi en Finch, waar blijven jullie idieën

Hier blijft het:

Ik heb nog nooit van een pivot gehoord,maar ik zal eens kijken of ik dat ga snappen.

Ik geef op forums heel vaak een volledig uitgewerkte oplossing, maar ook niet altijd

 

[Wigi]

Beste Hansvdvoort

Heb de onvolkomenheden weggewerkt in de vorige versies van Danny

- de 23, 33, 43, ... in de bovenste tabel zou ik niet handmatig inkloppen, maar met een formule doen. Op basis van rij en kolomkop moet dat doenbaar zijn.

In F4 zet je 1/1/07.

Dan met de vulgreep, voer je door in maanden tot aan F15.

Dan in G4:

=ALS(AANTAL.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000);SOM.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000;$C$2:$C$1000)/AANTAL.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000);"")

Dan doorvoeren zonder de opmaak, dan moet je geen formules meer aanpassen en ook geen opmaak.

Wigi

 

[Wigi]

Aanvulling

In G17:

=ALS(ISFOUT(GEMIDDELDE(G$4:G$6));"";GEMIDDELDE(G$4:G$6))

In G18:

=ALS(ISFOUT(GEMIDDELDE(G$7:G$9));"";GEMIDDELDE(G$7:G$9))

In G19:

=ALS(ISFOUT(GEMIDDELDE(G$10:G$12));"";GEMIDDELDE(G$10:G$12))

In G20:

=ALS(ISFOUT(GEMIDDELDE(G$13:G$15));"";GEMIDDELDE(G$13:G$15))

In G22:

=ALS(ISFOUT(GEMIDDELDE(G$4:G$15));"";GEMIDDELDE(G$4:G$15))

Dan die cellen doorvoeren naar rechts zonder opmaak.

Wigi

 

[GiMe]

Of een gemiddelde over iedere gewenste periode (maand / kwartaal / jaar / periode ...)

GiMe

 

[Finch]

Beste Hansvdvoort

Beste Wigi en Finch, waar blijven jullie idieën

Bijgevoegd een voorbeeldje met een pivot table. Groepering van datum kan je met een eenvoudige rechtermuisknop veranderen naar jaren - kwartalen - maanden, maar zelfs ook naar andere indelingen bv. jaren - maanden

 

[Hansvdvoort]

Bedankt voor de goede reacties!

@Finch. Een mooie draaitabel, een hele mooie zelfs. Volgens mij klopt het helemaal, en is ook precies wat ik bedoel. Tevens weet ik nu ongeveer wat een draaitabel is. Bedankt dus

@danny. Ook erg mooi, maar klopt net niet. De gemiddelden per maand kloppen wel. maar vervolgens worden de kwartaal gemiddelden gemeten aan de hand van de gemiddelden van de maand. En omdat niet elke maand evenveel data heeft (niet even zwaar mee telt dus), klopt dat kwartaal gemiddelde net niet

@wigi: Als ik die formule =ALS(AANTAL.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000);SOM.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000;$C$2:$C$1000)/AANTAL.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000);"") doorvoer zonder opmaak naar beneden dan worden alle vakjes leeg...

 

[Wigi]

@wigi: Als ik die formule =ALS(AANTAL.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000);SOM.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000;$C$2:$C$1000)/AANTAL.ALS($D$2:$D$1000;MAAND($F4)&G$2-2000);"") doorvoer zonder opmaak naar beneden dan worden alle vakjes leeg...

En heb je dit ook gedaan:

In F4 zet je 1/1/07.

Dan met de vulgreep, voer je door in maanden tot aan F15.

Wigi

 

[Hansvdvoort]

zie bijlage. Volgens mij heb ik precies gedaan wat jij zei

 

[Wigi]

Ik had de TEKST functie er niet bijgezet.

=ALS(AANTAL.ALS($D$2:$D$1000;MAAND($F4)&TEKST(G$2-2000;"0#"));SOM.ALS($D$2:$D$1000;MAAND($F4)&TEKST(G$2-2000;"0#");$C$2:$C$1000)/AANTAL.ALS($D$2:$D$1000;MAAND($F4)&TEKST(G$2-2000;"0#"));"")

Wigi