Transponeren: zoals vaker heeft ook dit woord verschillende betekenissen, afhankelijk van de context.

In de muziekwereld is Transponeren het verplaatsen van een stuk muziek naar een andere toonsoort. De muziek klinkt daardoor hoger of lager. Als je gitaar speelt met een Capo transponeer je ook.

In Excel wordt er mee bedoeld dat we gegevens, die eerst horizontaal zijn weergegeven, zodanig verplaatsen dat het een verticaal overzicht wordt (of andersom).

In dit artikel enkele eenvoudige Excel-transponeer-tips, waaronder een bespreking van de functie TRANSPONEREN en een alternatief met behulp van de veelzijdige functie INDEX.

Transponeren m.b.v. kopiëren/plakken

Laten we maar eens beginnen met een simpele praktijk-situatie (zie het tabblad TransP1 van het Voorbeeldbestand).
In de loop van 2017 hebben we een maandoverzicht gemaakt; geen probleem. Maar nu we weer aan een nieuw jaar zijn begonnen, merken we dat het niet zo handig was om het overzicht “in de breedte” te maken. Binnenkort past het niet meer op een A4. Het overzicht hadden we niet horizontaal moeten maken, maar verticaal.

Dus we moeten alles opnieuw invoeren? Nee, natuurlijk niet! Het toverwoord is transponeren:

1. selecteer de cellen die omgezet moeten worden; in het voorbeeld B2:P4
2. klik met de rechter muisknop op de selectie en kies Kopiëren of
   druk op Ctrl-C
3. klik met de rechter muisknop in de cel waar het nieuwe overzicht moet komen (bijvoorbeeld in cel B6) en kies de button  (de R-toets indrukken geeft hetzelfde resultaat)
   Een andere manier: wanneer u na het rechts-klikken Plakken speciaal …. kiest, dan krijgt u een nieuw venster waarin u de optie Transponeren kunt aanvinken
   Klik nog wel even op OK.
4. verwijder eventueel de rijen met het oude overzicht.

NB wanneer je in stap 2 Knippen kiest (of Ctrl-X) dan is de optie Transponeren niet beschikbaar

Transponeren met opmaak en formules 1

Wanneer we hetzelfde doen met een overzicht met opmaak en formules kan het resultaat wel eens tegenvallen.
In het voorbeeld op tabblad TransP2 van het Voorbeeldbestand bestaat de opmaak uit dikkere randen, een vet lettertype en getallen die opgemaakt zijn met 2 decimalen en er staan formules in rij 5.
Niet alle opmaak wordt getransponeerd; wel worden formules omgezet (zie bijvoorbeeld cel E12).

Gelukkig hebben we nog wel wat alternatieven achter de hand:

1. volg in stap 3 (zie hierboven) de omslachtige methode (dus Plakken speciaal kiezen of 2 keer op de punt-toets drukken) en vink in het pop-up-scherm niet alleen Transponeren aan, maar ook de optie Waarden.
   Groot nadeel is dat alle opmaak weg is en er geen formules meer in het overzicht staan, maar harde waardes (zie tabblad TransP2).
2. volg in stap 3 (zie hierboven) de omslachtige methode (dus Plakken speciaal kiezen of 2 keer op de punt-toets drukken) en vink in het pop-up-scherm niet alleen Transponeren aan, maar ook de optie Alles behalve randen.
   Het enige wat je nu nog moet doen is opnieuw de randen aanbrengen.

Transponeren met opmaak en formules 2

Nog een voorbeeld maar met een formule, die naar een cel buiten het overzicht verwijst (zie het tabblad TransP3 van het Voorbeeldbestand).

Wanneer we dit overzicht Transponeren (met de optie Alles behalve randen) dan zien we iets vreemds in de laatste 2 rijen. De formules leveren fout-meldingen op. Als we naar de ‘bron’ kijken (cellen O7 en P7) dan blijkt daar een relatieve verwijzing naar cel C2 te staan (dus zonder de $-tekens). Bij het Transponeren gaan deze verwijzingen ‘de mist in’.

Wanneer het Transponeren plaats vindt naar een ander tabblad dan zullen alle formules foutieve waardes opleveren: de nieuwe formules verwijzen naar cel C2 in het nieuwe tabblad en daar staat waarschijnlijk geen BTW-percentage.

De handigste oplossing voor dit soort problemen is om zoveel mogelijk Namen te gebruiken in formules:

1. selecteer cel C2
2. klik in het Naamvak links boven
3. vervang daar C2 door de naam BTW en druk op Enter
4. vervang in de formules in rij 7 alle verwijzingen naar C2 door de naam BTW
5. Transponeren mag nu geen problemen meer opleveren

NB wanneer je in een formule een verwijzing naar een zelf-gedefinieerde naam gebruikt dan is dit altijd automatisch een absolute verwijzing.

Transponeren met opmaak en formules 3

Nog een voorbeeld: een overzicht per maand en regio. In het tabblad TransP4 van het Voorbeeldbestand kunt u zien dat er ook nog totaal-formules onder en rechts er van staan.

NB Totalen zijn altijd snel aan een dergelijk overzicht toegevoegd:

1. klik met de muis in een van de cellen van het overzicht
2. druk op Ctrl-A; alle cellen van het overzicht zullen worden geselecteerd
3. druk op Shift (vasthouden) en pijl naar rechts; de selectie wordt met 1 kolom uitgebreid
4. druk op Shift (vasthouden) en pijl naar beneden; de selectie wordt met 1 rij uitgebreid
5. klik met de muis op 

In een andere rapportage hebben we hetzelfde overzicht nodig, maar dan anders gerangschikt: de maanden in de rijen en de regio’s in de kolommen.

Hierboven hebben we gezien hoe het transponeren, inclusief opmaak en formules in zijn werk gaat.

Het grote nadeel van de methode zit hem in het feit, dat er nu twee overzichten zijn zonder koppeling. Als er cijfers veranderen moeten deze wijzigingen op 2 plaatsen worden doorgevoerd. Dit is natuurlijk fout-gevoelig; daarom zal ik hieronder enkele alternatieve methoden laten zien.

De functie Transponeren

In het tabblad TransP5a van het Voorbeeldbestand is nogmaals het regio-maand-overzicht opgenomen.

Dit overzicht gaan we Transponeren met de Excel-functie met diezelfde naam:

1. klik in de cel waar het nieuwe overzicht moet komen, in het voorbeeld is dit B11
2. druk nu de Shift-toets in en hou die ingedrukt en druk zo vaak op de pijl naar rechts als er in het oorspronkelijke overzicht rijen zijn
3. nog steeds met de Shift-toets ingedrukt zo vaak op de pijl naar beneden drukken als er in het oorspronkelijke overzicht kolommen zijn
4. voer dan de volgende formule in =TRANSPONEREN(B2:N6)
   LET OP dit is een zogenaamde matrix- of array formule; deze moeten we activeren door niet alleen op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter (de CSE-methode). In alle cellen komt dezelfde formule te staan; Excel plaatst er automatisch accolades omheen.

NB hebt u geen zin om de originele rijen en kolommen te tellen, selecteer dan eerst met de muis alle cellen van het oorspronkelijke overzicht en kijk (zonder de muisknop los te laten) in het naamvak links boven. In dit voorbeeld zien we dan 5R x 13K staan, dus 5 rijen en 13 kolommen; het nieuwe overzicht krijgt dan 13 rijen en 5 kolommen.

Wijzigt nu één van de bedragen in het oorspronkelijke overzicht dan wordt deze mutatie in het nieuwe overzicht automatisch overgenomen.

Misschien had u het al gezien: het oorspronkelijke overzicht is in de vorm van een Excel-tabel ingevoerd (voor de voordelen hiervan zie onder andere de artikelen Kunst en Excel en Tabellen deel 2).
Wanneer we de regio’s nu uitbreiden met bijvoorbeeld Noord-Oost dan weet Excel dat de tabel groter is geworden (tik de omschrijving in cel B7 en druk op Enter).
Maar hoe zit het met het getransponeerde overzicht? Klik op één van de cellen en u ziet dat de formule automatisch is gewijzigd in =TRANSPONEREN(B2:N7).

Helaas hebben we bij het opstellen van dit overzicht geen rekening gehouden met deze uitbreiding, dus moeten we dit overzicht nog corrigeren:

1. klik met de muis in één van de cellen van het getransponeerde overzicht
2. druk op Ctrl-A; alle cellen van het overzicht zullen worden geselecteerd
3. druk op Shift (vasthouden) en pijl naar rechts; de selectie wordt met 1 kolom uitgebreid
4. klik in de formulebalk achter de bestaande formule (of druk F2)
5. druk tegelijkertijd Ctrl-Shift-Enter

LET OP hebt u teveel rijen of kolommen geselecteerd bij het aanmaken van het nieuwe overzicht (of bij de uitbreiding daarvan) dan plaatst Excel de foutboodschap #N/B in de betreffende cellen (Niet Beschikbaar).

In het tabblad Trans5b van het Voorbeeldbestand is dit probleem opgelost door middel van Voorwaardelijke opmaak. De gehanteerde voorwaarde is daarbij =ISNB(B2) met als opmaak een witte kleur bij Lettertype (klik maar eens op Ctrl-A).

Wanneer de oorspronkelijke tabel een totaal-rij heeft en/of een totaal-kolom, is dit voor deze methode geen enkel probleem. De inhoud daarvan wordt bij het transponeren overgenomen, maar niet als formule (zie het tabblad TransP6).

NB helaas geeft de functie Transponeren alleen de inhoud van de oorspronkelijke cellen terug; het is op geen enkele manier mogelijk om de opmaak automatisch over te nemen.  Ook kun je van het resultaat geen Excel-tabel maken.

Transponeren met de functie Index

Voordat we deze functie hier gaan gebruiken, moeten we nog twee andere Excel-functies bespreken: Kolommen en Rijen.

LET OP deze functies niet verwarren met de functies Kolom en Rij, die respectievelijk het kolomnummer en rijnummer van een cel opleveren.

Wanneer we ergens in een spreadsheet de formule =KOLOMMEN(A1) intikken, dan krijgen we als resultaat 1: het aantal kolommen in dat bereik.
Met de vulgreep naar beneden kopiëren en naar rechts levert allemaal 1’en op; de functie blijft telkens maar naar 1 cel kijken (dus 1 kolom).
Maar tikken we nu als formule in =KOLOMMEN($A$1:A1) en kopiëren we die met de vulgreep naar beneden en naar rechts, dan krijgen we een heel ander resultaat (zie kolommen R tot en met U in het tabblad TransP7 van het Voorbeeldbestand).
Iets vergelijkbaars gebeurt er als we de formule =RIJEN ($A$1:A1) kopiëren.
Dus: als we de KOLOMMEN-formule naar beneden kopiëren dan verandert er niets, kopiëren we naar rechts dan neemt het resultaat iedere keer met 1 toe. Bij RIJEN is dit net andersom.
Deze eigenschap van KOLOMMEN en RIJEN wordt in veel (geavanceerde) toepassingen gebruikt, wanneer een teller nodig is in een formule.

Deze ’truc’ gaan we gebruiken om met behulp van de functie INDEX te transponeren:

1. klik met de muis in de cel waar het eerste resultaat moet komen (in het voorbeeld van tabblad TransP7 is dit cel B14)
2. daar beginnen we de formule met =INDEX($B$2:$O$9;
   Dan moeten we opgeven uit welke rij van het bereik B2:O9 er iets opgehaald moet worden: hier moet dat rij 1 zijn, maar als we de formule dadelijk naar beneden ’trekken’ dan moet dit 1 blijven; trekken we naar rechts dan moet er telkens een volgende rij gekozen worden. Dat is precies wat we hierboven met de functie KOLOMMEN hadden bereikt.
3. Dus de formule wordt nu =INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);
   Uit welke kolom moet de formule iets ophalen? De eerste keer uit kolom 1, maar als de formule naar beneden gekopieerd wordt dan moet dat telkens een volgende kolom zijn; bij het kopiëren naar rechts mag de kolom niet veranderen.
4. In cel B14 komt =INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);RIJEN($A$1:A1))
5. Kopieren we deze formule te ver naar rechts of naar beneden dan zal Excel een foutmelding geven (de kolom of rij bestaat niet in B2:O9).
   Daarom hebben we in het voorbeeld de formule uitgebreid met een foutafhandeling:

   =ALS.FOUT(INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);RIJEN($A$1:A1));””)

Nog even wat opmaak regelen en het getransponeerde overzicht is klaar.

In het tabblad TransP7 heb ik van een getransponeerd overzicht (vanaf rij 40) een Excel-tabel gemaakt zodat er weinig meer gedaan hoeft te worden aan de opmaak.
Opletten dat je de tabel maakt zonder kolomkoppen (dat zijn immers formules). Er ontstaat dan een aparte kop-regel, die met behulp van Gegevens/Overzicht/Groeperen, wanneer gewenst, kan worden ‘verborgen’.
Door middel van de nieuwe kopjes kan er makkelijk gefilterd worden; zorg dan dat de eerste regel (de oude kop) blijft staan, eventuele lege regels niet worden meegenomen en de totalen ook niet (want die kloppen niet met de filtering!)