Een bereik met gegevens transponeren (= rijen en kolommen verwisselen) met gebruik van de functies INDEX, KOLOMMEN en RIJEN. Als je de functie TRANSPONEREN niet wil gebruiken is dit een goed alternatief.
De functie INDEX geeft als resultaat de waarde van een item in een tabel. De waarde van het item wordt gevonden op het snijpunt van het rijnummer en het kolomnummer.
Voorbeeld:
=INDEX(Tabel;Rijnummer;Kolomnummer)
Ofwel:
=INDEX(A1:E10;10;5) = Waarde
Stel, een tabel in het bereik B5:O10.
1 = het rijnummer en 2 is het kolomnummer.
De functie INDEX vindt in onderstaande tabel de waarde van het item op het snijpunt van de 1-ste rij en de 2e kolom. Die waarde = 48
Onder de tabel staan nog 4 voorbeelden.
De functie KOLOMMEN(Bereik) geeft het aantal kolommen in het opgegeven bereik. Het zelfde geldt voor de functie RIJEN(Bereik).
Voorbeeld:
Kolommen(C1:E4) geeft 3, namelijk kolom C, D en E.
Rijen(C1:E4) geeft 4, namelijk rij 1, 2, 3 en 4.
Deze 2 functies zijn ideaal om als een zogenaamde teller te fungeren. Ik bedoel daarmee, als je een waarde met telkens 1 wilt verhogen.
Bijvoorbeeld: 1 – 2 – 3 – 4 – 5 óf 22 – 23 – 24 – 25 – 26.
Hoe gaat dat in zijn werk? Plaats onderstaande formule in een cel Bijvoorbeeld in A10:
=KOLOMMEN($A$2:A2)
Door die formule 5x naar rechts te kopiëren, krijg je de volgende reeks:
A10=KOLOMMEN($A$2:A2), B10=KOLOMMEN($A$2:B2), C10=KOLOMMEN($A$2:C2), D10=KOLOMMEN($A$2:D2), E10=KOLOMMEN($A$2:E2)
Je ziet dat de eerste parameter van KOLOMMEN($A$2) een absolute verwijzing is (mét dollartekens). De tweede parameter verschuift telkens omdat het een relatieve verwijzing is (geen dollartekens). Namelijk A2, B2, C2, D2 en E2. Hierdoor wordt het bereik, en het aantal kolommen, steeds groter wat resulteert in de waarden
1 – 2 – 3 – 4 – 5
Je kunt met de functie INDEX dus iets opzoeken.
Je kunt echter ook je gegevens anders ordenen bijvoorbeeld van vertikaal naar horizontaal. Hiervoor combineer je de functies INDEX, KOLOMMEN en RIJEN.
Maar let op ! ! ! Het paradoxale is dat als je in dit voorbeeld de rijen telkens met 1 wil laten oplopen je de functie KOLOMMEN gebruikt en als je de kolommen met 1 wil laten oplopen je de functie RIJEN moet gebruiken. Kijk maar naar onderstaand voorbeeld.
in cel A14 plaats je de volgende formule:
=INDEX($A$1:$B$11;KOLOMMEN($A$1:A1);RIJEN($A$1:$A1))
Deze formule trek je eerst naar rechts en vervolgens 1 rij naar beneden.