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
De eerste functie die we zullen bekijken is AANTAL.ALS().
De eerste parameter/argument van de functie is het bereik van de gegevens, waarvan u de aantallen wilt weten en het tweede argument is de voorwaarde waaraan ze moeten voldoen om mee te mogen tellen.
In het voorbeeld hiernaast ziet u dat u op deze manier snel een jaaroverzicht kunt maken; op de plaats van de voorwaarde staat een verwijzing naar een cel, die de gewenste voorwaarde bevat.
Hiernaast staan drie voorbeelden van het gebruik van het * (sterretje; dit joker-teken geeft aan, dat wat er vanaf die positie komt niet relevant is): in het eerste geval tellen we de aantallen van de maanden die beginnen met een ‘j’, in de tweede regel alle regio’s, die eindigen op een ‘d’ (Noord en Zuid dus) en als laatste alle regio’s, die eindigen op een ‘t’ (Oost en West).
In de praktijk komt het gebruik van een ander joker-teken (het vraagteken, ?) minder voor: waar het sterretje (*) een willekeurig aantal tekens vervangt, geeft het vraagteken aan, dat het niet relevant is wat er op die ene positie staat. In het voorbeeld zoeken we alle agenten, waar op de tweede plaats een ‘a’ staat.
Wanneer we een voorwaarde opgeven voor getallen dan is het niet alleen mogelijk om exacte getallen te zoeken (in het eerste voorbeeld hierboven moest de omzet exact gelijk zijn aan 7.500), maar ook vergelijkingen als kleiner dan (<), groter of gelijk aan (>=) zijn mogelijk.
In het voorbeeld hiernaast wordt door de formule =SOM.ALS(E3:E202;7500) alle omzet, die voldoet aan de voorwaarde, dat de omzet gelijk is aan 7.500, opgeteld. Aangezien we hiervoor hebben gezien dat dit in het voorbeeldbestand 4 keer voorkomt, is de som gelijk aan 30.000.
De SOM.ALS-formule is echter nog krachtiger: het is mogelijk om een derde argument aan de functie mee te geven, het zogenaamde Optelbereik.
), zijn optionele argumenten lichtgrijs.
Het eerste argument van SOMMEN.ALS is het optelbereik, het tweede een criteriumbereik en het derde de voorwaarde. Op deze manier is de werking van deze functie exact gelijk aan SOM.ALS. De kracht van de uitgebreidere functie is, dat er meerdere voorwaarden als argument meegegeven kunnen worden.