Maandelijks archief: oktober 2015

Van rijen naar kolommen, deel 2

Excel AccessIn 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; Rijen1ik 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?

Rijen1In 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:

  1. zet de cursor ‘ergens’ in het gegevensblok, bijvoorbeeld cel B3
  2. Rijen2kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Excel heeft het hele blok gegevens dan geselecteerd (in dit geval B2:E21); het enige wat we hoeven doen is op OK te klikken
  4. in een nieuw tabblad komt de lay-out van de draaitabel. Rijen3Rechts kunnen we de inhoud aanpassen: sleep LenNr naar de Rijlabels, VolgNr naar de Kolomlabels en PolisNr en Waarde naar het Waardegebied.
  5. 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:

  1. zet de cursor ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen
  3. binnen Rapportindeling kiezen we dan Tabelweergave
  4. nog wat kleurtjes en lijnen et voilà:

Rijen4

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


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Van rijen naar kolommen

Iemand vroeg mij vorige week of ik kon helpen:

“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.”

Nog net voordat ik begon te lachen (“Dat is zo geregeld!”, dacht ik) hoorde ik dat het bestand in Access stond. Oeps, dat wordt lastiger.
Ik zal alvast verklappen: het heeft me wel wat hoofdbrekens gekost. Ik denk ook nog steeds dat het simpeler moet kunnen. Dus bij deze een oproep aan Access-kenners: kom met een betere oplossing!

Excel-oplossing

Rijen1Voordat we het probleem in Access gaan aanpakken, eerst maar even een oplossing in Excel; dat is iets makkelijker!

Hiernaast staat een voorbeeldje van het bestand: zoals te zien is horen bij LenNr=1 meerdere Polisnr’s etc.

In het Excel-voorbeeldbestand zijn deze gegevens in het tabblad Basis terug te vinden.

In het tweede tabblad (Tussen) is een kopie gemaakt van deze gegevens en heb ik de kop van de tabel al uitgebreid met Polis1, Waarde1, Polis2 etc. In het voorbeeld is het maximaal aantal polissen per lening 4, maar ik heb al ruimte gemaakt voor 5 polissen:

Rijen2

Via wat handige formules kunnen we het bestand uitbreiden; maar zorg wel eerst dat alle regels van dezelfde lening onder elkaar komen, dus sorteren op LenNr.

  1. onder het kopje Polis1 komt het polisnummer van de eerste lening; eigenlijk zouden we in die cel (E3) kunnen verwijzen naar C3, maar ik heb daar een formule geplaatst, die direct naar beneden gekopieerd kan worden:
    =ALS(B3<>B2;C3;””)
    Dus als in kolom B een ander LenNr staat dan in de vorige regel, dan de inhoud van kolom C kopiëren, anders de cel leeg laten (2 “-tekens achter elkaar).
  2. onder Waarde1 (cel F3 dus) staat de formule
    =ALS(E3<>””;D3;””)
    Als in de vorige stap een polisnummer in ingevuld dan ook de waarde daarvan ophalen.
  3. Dan onder Polis2 (cel G3):
    =ALS(E3<>””;ALS(B4=B3;C4;””);””)
    We willen alle polisnummers op eenzelfde regel krijgen, dus Polis2 mag alleen maar ingevuld worden als er ook een Polis1 is (ALS(E3<>””).
    Daarna vergelijken we het LenNr van de huidige regel met de volgende regel; als dat hetzelfde nummer is dan halen we het betreffende polisnummer op
  4. Voor Waarde2 geldt hetzelfde als hiervoor bij Waarde1.
  5. De formule onder Polis3 mag niet meer verrassend zijn:
    =ALS(G3<>””;ALS(B5=B4;C5;””);””)
  6. Ook de andere kolommen krijgen vergelijkbare formules en dan alles naar beneden kopieren.Rijen3
Bijna klaar: alle polissen zijn mooi van de rijen naar de kolommen verplaatst, maar we hebben nog wat overbodige regels over:

Rijen4

  1. klik met de muis ergens in de tabel met gegevens
  2. kies de menutab Gegevens
  3. en binnen het vak Sorteren en filteren de optie Filter
  4. klik op het ‘vinkje’ achter Polis1 (de filteropties voor deze kolom) Rijen5
  5. zorg dat helemaal onderaan het vinkje bij (Lege cellen) uit staat
  6. selecteer de overblijvende gegevens en kopieer die naar een ander tabblad (zie Resultaat)

Access

Nu gaan we hetzelfde doen in Access.
In het Access-voorbeeldbestand (dit is gezipt, omdat de download anders vast kan lopen) staat hetzelfde basisbestand, in de tabel Basis.

Rijen6Om problemen te voorkomen bij het wegschrijven van de tussenstappen, creëren we eerst de Tussen-tabel met per veld het juiste gegevenstype.
NB Wanneer we Access de tabel laten aanmaken via een Tabelmaak-query, gaat Access ‘gokken’ welke type het veld moet krijgen; dat strookt lang niet altijd met onze wensen!

Rijen7Dan de eerste stap: via een query selecteren we van alle leningnummers het kleinste polisnummer (had ook de maximum-waarde kunnen zijn).
Via de button Rijen8 wordt dit een Toevoeg-query; records toevoegen aan de hiervoor aangemaakte tabel Tussen.

NB Access groepeert zelf per leningsnummer; de records hoeven dan ook niet gesorteerd te zijn, in tegenstelling tot de Excel-oplossing.

Het ophalen van een mogelijk tweede polisnummer is al wat ingewikkelder:

Rijen9Vanuit Basis selecteren we opnieuw de minimale waarde voor het polisnummer, maar via de zogenaamde outer-joins naar de Tussen-tabel wordt er voor gezorgd, dat combinaties van LenNr en PolisNr, die al voorkomen, niet meer meedoen (zie bij Criteria de voorwaarde Is Null).

Ook voor de volgende polisnummers zijn op vergelijkbare manier query’s gebouwd, waarbij meerdere koppelingen met dezelfde Tussen-tabel zijn aangemaakt.

Rijen10De tussentabel bevat nu records, waarbij leningnummers nog wel in verschillende records voorkomen, maar waar wel al de polisnummers op de ‘juiste’ plaats staan.

Ook missen we de poliswaardes nog in de tussentabel. Dat is opgelost door 5 Bijwerk-query’s, waarmee vanuit de Tussen-tabel aan de hand van LenNr en PolisNr de overeenkomende waarde in Basis wordt opgehaald:
Rijen11Rijen12
Om het gewenste overzicht te krijgen (alle leningnummers uniek met daarachter één of meerdere polisnummers) hebben we nog een laatste query nodig.

Transponeren

Voor diegene, die zich door de titel van het artikel (Van Rijen naar Kolommen) hebben laten misleiden, nog een toegift:
soms wil je in Excel gegevens, die onder elkaar staan, naast elkaar hebben of andersom. Hiervoor zijn geen ingewikkelde formules nodig: Excel heeft daar een ingebouwd commando voor:

  1. selecteer in het Excel-voorbeeldbestand alle relevante gegevens in het tabblad Basis
  2. klik met de rechter muisknop en kies Kopiëren
  3. selecteer de cel waar het resultaat moet komen; dit kan ook in een ander tabblad of in een andere werkmap zijn.
  4. Rijen13klik daar opnieuw met de rechter muisknop
  5. en kies nu Plakken speciaal…
  6. in het vervolgscherm staat een optie Transponeren; zet het vinkje aan en kies OK

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: