In mijn vorige artikel (Van rijen naar kolommen) heb ik oplossingen aangedragen voor het ‘verplaatsen’ van gegevens van rijen/records naar kolommen/velden; een oplossing in Excel én eentje in Access.
Voor mijn Access-oplossing had ik het gevoel, dat het slimmer/handiger zou moeten kunnen, maar tot nu heb ik nog geen alternatief mogen ontvangen.
Wel heb ik zelf voor de Excel-routine een betere oplossing bedacht; die wil ik jullie niet onthouden.
Probleem
Iemand had mij het volgende gevraagd:
“Ik heb een bestand met leningnummers. Daar staan ook polisnummers en poliswaarden in, maar ze staan per lening onder elkaar;
ik wil voor iedere lening 1 regel met daarachter alle polisgegevens.”
Een voorbeeldje zie je hiernaast; de bedoeling is dat alle polissen en bijbehorende waardes van leningnummer 1 achter dit leningnummer komen, voor 2 idem etc.
Niet alle leningen hebben evenveel polissen.
Excel
Bij mijn vorige oplossing (zie Van rijen naar kolommen en het Voorbeeldbestand 1) had ik enkele tussenstappen nodig om tot het juiste resultaat te komen. Ik realiseerde me later dat het makkelijker en eleganter zou moeten kunnen.
Het bovenstaande probleem is eigenlijk terug te brengen tot de vraag: als we in een bepaalde rij kijken, kunnen we dan achterhalen wat het volgnummer is van de polis bij het betreffende leningnummer?
In het Voorbeeldbestand 2 heb ik daartoe in het tabblad Basis een nieuwe kolom (E) toegevoegd. In de eerste regel komt de formule:
=AANTAL.ALS($B$3:B3;B3)
Ik moet toegeven: dat ziet er wel wat vreemd uit. We turven daar hoe vaak de inhoud van B3 (achter de punt-komma) voorkomt in het bereik B3:B3 (voor de punt-komma). Dat is natuurlijk 1 keer.
Maar wanneer we deze formule naar beneden kopiëren, zien we het nut hiervan (dankzij het absoluut maken van de eerste B3 met behulp van de $-tekens). In E4 komt dan de formule
=AANTAL.ALS($B$3:B4;B4)
Hoe vaak komt de waarde uit B4 voor in het bereik B3:B4. Dit is precies wat we wilden weten: zo krijgt ieder polisnummer een volgnummer per lening zonder dat we het bestand hoeven te sorteren.
De gegevens (inclusief kolom E) gebruiken we als basis voor een draaitabel:
- zet de cursor ‘ergens’ in het gegevensblok, bijvoorbeeld cel B3
kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel- Excel heeft het hele blok gegevens dan geselecteerd (in dit geval B2:E21); het enige wat we hoeven doen is op OK te klikken
- in een nieuw tabblad komt de lay-out van de draaitabel.
Rechts kunnen we de inhoud aanpassen: sleep LenNr naar de Rijlabels, VolgNr naar de Kolomlabels en PolisNr en Waarde naar het Waardegebied. - Nog wat aanpassingen (verwijder de rij- en kolomtotalen) en het gewenste resultaat is klaar.
In het Voorbeeldbestand 2 in het tabblad Ovz is dit resultaat terug te vinden, inclusief een vergelijking met de Access-uitkomst.
De ‘moderne’ standaard-lay-out van een draaitabel is niet altijd even duidelijk (wat doen die kopjes Kolom- en Rijlabels daar?).
In het tabblad Ovz2 staat dezelfde draaitabel maar met een andere indeling:
- zet de cursor ergens in de draaitabel
- kies in de menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen
- binnen Rapportindeling kiezen we dan Tabelweergave
- nog wat kleurtjes en lijnen et voilà:
Cadeau
Los van de inhoud van dit artikel: ik kwam pas op de site www.myonlinetraininghub.com terecht. Daar is een e-book met Tips & Tricks te downloaden.
Altijd interessant en het mag verder verspreid worden, dus bij deze: Excel_Tips&Tricks_e-BookV1.1