Waarschijnlijk bent u wel bekend met de mogelijkheid om met behulp van kopiëren > plakken > transponeren om uw tabel te transponeren. Maar wist u dat er ook formules voor bestaan? Cm: legt uit hoe dat in zijn werk gaat.

Met deze formule is het mogelijk om de waardes in de basistabel geautomatiseerd over te zetten naar jouw getransponeerde tabel en zelfs de hele formule te transponeren. Transponeren is bijvoorbeeld handig als een tabel in de loop der tijd te ver in de breedte is doorgelopen.

Wat is transponeren?

Transponeren is in feite niet meer dan het omwisselen van de rijen en kolommen. In Excel ziet dat er zo uit:

Transponeren met kopiëren plakken

Om simpel te transponeren selecteert u de rijen en kolommen die u wilt transponeren. In dit voorbeeld kiezen we de selectie A1: F6. Vervolgens selecteert u in het menu dat verschijnt als u met de rechtermuisknop klikt, de functie Kopiëren. Daarna selecteert u het gebied waarin u deze selectie wilt transponeren. In dit voorbeeld is dat A9:F10. In het geselecteerde gebied klikt u weer met de rechtermuisknop en selecteert u onder Plakopties (Paste) de functie Transponeren (Transpose). Nu verschijnt de getransponeerde tabel. Als er formules in de basistabel zaten, worden alleen de resultaten van die formules eenmalig overgenomen, niet de formule zelf. Ook verandert de waarde in de getransponeerde tabel niet als de waarde in de basistabel verandert.

Tot zover de basis.

Lees ook: 7 simpele Excel-tips die tijd besparen

Transponeren met functie TRANSPONEREN

Door middel van kopiëren > plakken worden de gegevens eenmalig van de ene tabel naar de andere overgenomen. Maar als we nu de waarde in de cel B2 veranderen naar het getal 3, verandert het getal in de cel B10 niet ook naar 3. Daarvoor is de functie Transponeren (Transpose) nodig. De formule van deze functie is simpel: {=TRANSPONEREN(gebied)}.

In Excel ziet dat er voor ons voorbeeld zo uit:

Om deze functie te gebruiken begint u met het selecteren van het gebied waar u uw tabel wilt transponeren. In dit voorbeeld is dat het gebied A8:F9. Klik nu nergens in de tabel, maar ga naar de functiebalk. Schrijf: ‘=TRANSPONEREN(‘ en selecteer nu het gebied in de basistabel dat je wilt transponeren. In dit voorbeeld is dat A1:B6. Sluit de functie met ). Het betreft hier een matrix-functie, dus afsluiten met alleen Enter werkt niet. Toets daarom Crtl-Shift-Enter in.  Er verschijnen nu twee accolades {} om de functie. De getransponeerde tabel verschijnt. Als er formules in de basistabel waren opgenomen, worden deze ook getransponeerd. Maar alleen steeds het resultaat. Niet de gehele functie. Om die over te nemen, gebruikt u de Index-formule.

Lees ook: Excel-tip: 10 ondergewaardeerde Excel-functies

Transponeren met de functie Index

Als er een teller zit in een spreadsheet, zoals mogelijk is met de functie =KOLOMMEN(gebied), krijgen we in ons voorbeeld het getal 1. Als we die functie naar beneden kopiëren, blijft het resultaat steeds 1.  Maar door een kleine aanpassing in het gebied te doen met ‘=KOLOMMEN($A$1:A1)’ verandert dat. Hetzelfde geldt voor de functie =RIJEN(gebied). Deze truc is ook te gebruiken in de functie INDEX om een tabel te transponeren. Dat ziet er in Excel zo uit:

Begin door te klikken in de cel waar de getransponeerde formule moet beginnen. In dit voorbeeld in dat A8. Hier beginnen we met de functie ‘=INDEX en geven we vervolgens het afgebakende gebied aan. In dit voorbeeld geeft dat de voorlopige functie  ‘=INDEX($A$1:$B$6’ maar nu moet nog aangegeven worden uit welke specifieke rij en kolom moet worden opgehaald. Daarom moet de functie verder worden uitgebreid.

In dit voorbeeld moet uit kolom A en rij 1 worden opgehaald. Daarom wordt de formule nu: ‘=INDEX($A$1:$B$6;KOLOMMEN($A$1:A1);RIJEN($A$1:A1)’. U vertelt dus dat de eerste keer moet worden opgehaald uit kolom A, rij 1, maar als de formule wordt uitgebreid naar de andere cellen met behulp van de vulgreep, wordt dat telkens de volgende kolom en/of rij. Op deze manier wordt dus de formule uit de cel ook overgenomen, en kan bijvoorbeeld ook voorwaardelijk opmaak worden gekopieerd.

Sluit als laatste uw formule nog af met het een hekje dat u begonnen was aan het begin van de formule. Uiteindelijk is de formule dus: =INDEX($A$1:$B$6;KOLOMMEN($A$1:A1);RIJEN($A$1:A1), sluit af met Ctrl-Shift-Enter. Deze sleept u vervolgens met de vulgreep tot F9. Nu is de tabel geheeld getransponeerd, inclusief formules en eventuele opmaak.

Let op: als u deze formule te ver door trekt, naar een gebied dat geen inhoud bevat in de originele tabel, geeft Excel de foutmelding #VERW!.