[EXCEL] Draaitabel van gegevens maken

[Pindakaas]

Hoi Tweakers,

Ik heb een werkblad waar er in grote lijnen ongeveer uitziet zoals het voorbeeld hieronder. Hier wil ik graag een draaitabel van maken zodat ik kan zien hoeveel er per maand naar welke categorie gaat. Ik ben alleen op zoek hoe ik deze gegevens het beste (met 1 druk op de knop) kan organiseren.



Zelf heb ik geprobeerd om na de kolom 'Omschrijving' een kolom met de categorie toe te voegen. Een formule er in te doen om te kijken welke cel gevuld is en de desbetreffende categorie per regel te laten zien. Probleem is dan dat er soms ook meerdere categorieën op 1 regel staan, en daar loop ik vast. Iemand suggesties?

[SilencMol]

Alles optellen van de hele maand. =Som

Begrijp dan niet wat je wilt organiseren?


Of je wilt helemaal iets anders of je denkt te moeilijk.

[ Voor 255% gewijzigd door SilencMol op 29-11-2017 14:07 . Reden: 10de edit ]

[Pindakaas]

Hoezo niet? Als ik de som van dat bedrag wil zien gaat er toch niets verkeerd?

[breew]

Je data staat in "wide" formaat. Dat is niet geschikt om een draaitabel mee te maken; daarvoor moet je data in "long" formaat staan.
http://www.theanalysisfactor.com/wide-and-long-data/

Er zijn verschillende topics geweest over het omzetten van Wide naar Long (ook recent nog hier op GoT).
Daar kwam o.a. naar voren: https://www.excel-university.com/unpivot-excel-data/
Als je met google zoekt op "excel convert wide to long" of "excel unpivot data", dan kom je vast een heel eind.

Succes!

voorbeelddata nu:


wat het moet worden om een fatsoenlijke draaitabel mee te maken:


Met powerquery in excel (zie de link hierboven), is dat binnen 5 seconden gefixt.

Dan nog even een paar kolommen maken met daarin jaar (=LINKS()), maand (=DEEL()) en dag (=RECHTS()),


en je fixt die draaitabel in no time

[ Voor 56% gewijzigd door breew op 29-11-2017 15:20 ]

[Pindakaas]

Kijk daar was ik naar opzoek!
Eerst van Wide data naar Long data inderdaad. Ga ik vanavond even mee aan de slag. Hier op het werk doet de Query het niet en thuis gebruik ik alleen maar Mac dus ik ben benieuwd of het daar wel werkt.

Ik laat het weten of het is gelukt in ieder geval! Super bedankt

[breew]

Pindakaas schreef op woensdag 29 november 2017 @ 15:44:
Kijk daar was ik naar opzoek!
Eerst van Wide data naar Long data inderdaad. Ga ik vanavond even mee aan de slag. Hier op het werk doet de Query het niet en thuis gebruik ik alleen maar Mac dus ik ben benieuwd of het daar wel werkt.

Ik laat het weten of het is gelukt in ieder geval! Super bedankt

Ai.. volgens mij is PowerQuery niet beschikbaar voor Mac...
bron

Power Query is not yet available for the Mac versions of Excel.

Als je er zin in hebt; is het een koud kunstje met R (dat is iig cross-platform). Maar dan heb je wel even een leercurve..

[Lustucru]

Zonder powerquery of VBA kan het ook nog met formules
De rijlabels haal je op met een variant van:

code:

=VERSCHUIVING($A$2;(RIJ()-1)/3;0)
'A2->eerste label
'rij(-1)/3 zorgt ervoor dat het label 3x herhaald wordt. In jouw geval zou dat 7 moeten zijn)
'-1 als de formule op rij 1 staat, anders uiteraard aanpassen.

De kolomlabels haal je op met:

code:

=VERSCHUIVING($B$1;0;REST(RIJ()-1;3))
'zorgt ervoor dat hij na drie keer opnieuw begint

en de waarden haal je dan op met een combinatie

code:

=VERSCHUIVING($B$2;(RIJ()-1)/3;REST(RIJ()-1;3))

Doortrekken en je hebt je longtabel. In dit geval met een hoop lege regels die je er nog uit kunt halen.

[ Voor 4% gewijzigd door Lustucru op 29-11-2017 17:09 ]

[Pindakaas]

@Lustucru Ik probeer jou formules te begrijpen en toe te passen maar ik snap hem nog niet helemaal.

Rijlabels snap ik en werken ook goed.

De waarden heb ik ook nog niet goed maar ik verwacht dat dit hetzelfde probleem is als bij de kolomlabels.

Kolomlabels heb ik nog niet werkend. Ik zal hieronder even uitleggen hoe ik hem begrijp en wat ik denk dat er fout gaat:

code:

=VERSCHUIVING($B$1;0;REST(RIJ()-1;3))

Hij begint bij cel B1. Vervolgens schuift hij 0 rijen naar beneden en "x" kolommen naar rechts. Hoeveel kolommen hij precies naar rechts is een oplopend getal van 1 t/m 3.

Ik wil graag beginnen bij cel D1 en ik heb in totaal 22 kolommen. Ik heb de formule dus als volgt aangepast:

code:

=VERSCHUIVING($C$1;0;REST(RIJ()-1;22))

Dit lijkt op het eerste gezicht goed te gaan maar jammer genoeg werkt hij net niet helemaal. Probleem ligt hem in het aantal kolommen dat hij dient te verschuiven. De eerste reeks is namelijk netjes van 1-21 maar daarna begint hij bij 0 en loopt hij door t/m 21, hierdoor komt de verkeerde kolomlabel erbij te staan.

Is er een mogelijkheid om die 0'en te verwijderen uit de formule?

Hopelijk heb ik het een beetje duidelijk uitgelegd. Dit is geen dagelijkse kost voor mij

EDIT:
De kolomlabels heb ik nu werkend. De eerste waarde simpelweg handmatig ingevoerd en vervolgens de formule aangepast naar dit:

code:

=VERSCHUIVING($D$1;0;REST(RIJ()-2;22))

(er is ongetwijfeld een mooiere oplossing maar dit voldoet )

Nu nog even aan de slag met de waarden.

EDIT2:
Waardes werken nu ook. Wederom de eerste waarde er hard in gezet en vervolgens de formule aangepast.

code:

=VERSCHUIVING($D$2;(RIJ()-2)/22;REST(RIJ()-2;22))

Hartstikke bedankt allebei!

[ Voor 16% gewijzigd door Pindakaas op 30-11-2017 15:49 ]

[Lustucru]

Pindakaas schreef op donderdag 30 november 2017 @ 15:30:
De eerste reeks is namelijk netjes van 1-21 maar daarna begint hij bij 0 en loopt hij door t/m 21, hierdoor komt de verkeerde kolomlabel erbij te staan.
...

code:

=VERSCHUIVING($D$1;0;REST(RIJ()-2;22))

(er is ongetwijfeld een mooiere oplossing maar dit voldoet )
...

Het is inderdaad de aard van het beestje REST(X;N) dat de resultaten lopen van 0 tot N-1.

De broncel van je verschuiving moet dus de eerste cel zijn die je wilt hebben, omdat de verschuiving loopt van 0 tot N-1.

Omdat je nooit kunt beginnen op rij 0 is die -1 (of beter gezegd -[eerste rij waarin de formule voorkomt]noodzakelijk om te compenseren voor dat feit. Het enige wat ik dan nog niet snap is dat je de eerste rij met de hand in zou moeten vullen.

[ Voor 5% gewijzigd door Lustucru op 30-11-2017 20:21 ]

[Pindakaas]

Als ik alleen de formule =REST(RIJ()-1;3) invoer in Excel dan krijg ik de volgende reeks te zien

code:

1
2
0
1
2
0
1
2

De eerste reeks begint dus schijnbaar niet bij 0, maar bij 1. Daarom heb ik de eerste regel handmatig ingevoerd.

[Lustucru]

Pindakaas schreef op vrijdag 1 december 2017 @ 08:44:
Als ik alleen de formule =REST(RIJ()-1;3) invoer in Excel dan krijg ik de volgende reeks te zien

En waar voer je die reeks in: vanaf A2 en verder? Dan is t dus rij()-2

Lustucru schreef op donderdag 30 november 2017 @ 20:20:

Omdat je nooit kunt beginnen op rij 0 is die -1 (of beter gezegd -[eerste rij waarin de formule voorkomt]) noodzakelijk om te compenseren voor dat feit.

[Pindakaas]

Hum ja, klinkt logisch.

Vandaag weinig tijd, maar ga in het weekend zeker nog even er naar kijken. Het is toch mooier als er niks handmatig hoeft natuurlijk.

In ieder geval hartelijk bedankt! De draaitabellen doen het super