Hoe plakt u getransponeerd en behoudt u tegelijkertijd de formuleverwijzingen in Excel?
Bij het werken met Excel wordt de functie **Transponeren** vaak gebruikt om de oriëntatie van gegevens te wijzigen – van kolommen naar rijen of vice versa. Een veelvoorkomend probleem doet zich echter voor wanneer deze gegevens formules bevatten: Excel past standaard de celverwijzingen automatisch aan zodat ze overeenkomen met de nieuwe oriëntatie. Zoals in de onderstaande schermafbeelding wordt gedemonstreerd, leidt deze automatische aanpassing vaak tot foutieve berekeningen, vooral bij complexe of gekoppelde datasets.
Het is essentieel om te weten hoe u getransponeerde gegevens kunt plakken **zonder** dat de oorspronkelijke formuleverwijzingen worden gewijzigd – met name bij financiële modellen, technische berekeningen of gekoppelde dashboards waarbij de integriteit van formules cruciaal is.
Dit artikel beschrijft verschillende praktische methoden om dit resultaat te bereiken, behandelt scenario’s waarin deze methoden het beste (of juist het slechtst) werken en biedt handige tips voor probleemoplossing om het proces soepeler te laten verlopen.
Transponeer en behoud verwijzingen met de Zoeken en Vervangen-functie
Transponeer en behoud verwijzingen met Kutools voor Excel
VBA-code – Transponeer cellen en behoud formuleverwijzingen (relatief of absoluut)
Excel-formule – Bouw getransponeerde formules handmatig opnieuw met INDIRECT of adresconstructie
Gebruik de F4-toets om Converteer celverwijzingen om absolute verwijzingen te maken en de gegevens te transponeren
1. Selecteer de cel met de formule
Klik op de cel met de formule die u wilt aanpassen.
2. Open de formulebalk
Klik in de formulebalk om uw cursor direct in de formule te plaatsen.
3. Zet om naar absolute verwijzingen
Selecteer de volledige formule in de formulebalk en druk op de F4-toets.
Schakel hiermee eenvoudig tussen relatieve, absolute en gemengde celverwijzingen.
Herhaal dit voor alle celverwijzingen in de formule totdat ze volledig absoluut zijn.
4. Kopieer de gegevens
Selecteer het gegevensbereik dat u wilt kopiëren en druk op Ctrl+C.
5. Plak als getransponeerde gegevens
Klik met de rechtermuisknop op de doelcel en kies **Plakken speciaal → Transponeren**.
Tip:
Absolute verwijzingen zorgen ervoor dat de formule altijd naar dezelfde cellen verwijst, zelfs als je deze kopieert of verplaatst. Met transponeren zet je rijen om in kolommen of kolommen in rijen – perfect om de lay-out van je gegevens compleet te herstructureren.
Transponeer en behoud verwijzingen met de Zoeken en Vervangen-functie
Om een bereik van cellen te transponeren en de oorspronkelijke formuleverwijzingen in Excel te behouden, kunt u de Zoeken en Vervangen-functie gebruiken: converteer formules tijdelijk naar tekst, verplaats ze daarna naar hun nieuwe locatie en zet ze vervolgens weer terug naar formules. Deze methode is ideaal voor kleine tot middelgrote datasets en bijzonder handig als u geen extra invoegtoepassingen heeft geïnstalleerd of liever geen VBA gebruikt.
1. Selecteer eerst het bereik met de formules die u wilt transponeren. Druk op Ctrl + H om het dialoogvenster Zoeken en Vervangen te openen.
2. Typ in het dialoogvenster Zoeken en Vervangen in het veld = Zoeken naar en typ #= in het veld Vervangen door. Hierdoor worden actieve formules tijdelijk omgezet in platte tekst door het gelijkteken te vervangen, zodat Excel de formuleverwijzingen niet wijzigt tijdens het kopiëren en transponeren.
3. Klik op Alles vervangen. Er verschijnt een dialoogvenster met het aantal uitgevoerde vervangingen. Klik op OK en daarna op Sluiten om de dialoogvensters af te sluiten.
4. Houd de nu omgezette tekstcellen geselecteerd en druk op Ctrl + C om ze te kopiëren. Ga naar de gewenste plaklocatie, klik met de rechtermuisknop en selecteer in het contextmenu Plakken speciaal > Transponeren om de gegevens getransponeerd te plakken. Let op: bij grote datasets of formules met vluchtige functies dient u de geplakte resultaten zorgvuldig te controleren.
5. Druk na het plakken opnieuw op Ctrl + H om het dialoogvenster Zoeken en Vervangen te openen. Draai nu de oorspronkelijke vervanging om: typ #= in het vak Zoeken naar en = in het vak Vervangen door. Zo worden de tekstregels weer omgezet in werkende formules.
6. Klik op Alles vervangen, daarna op OK > Sluiten om af te ronden. Uw formules zijn nu getransponeerd en behouden dezelfde verwijzingen als in het oorspronkelijke bereik.
Deze handmatige methode werkt het beste bij kleine datasets. Bij complexere bereiken of wanneer u werkt met gemengde verwijzingstypen, controleert u de resultaten zorgvuldig om te garanderen dat de formules zoals verwacht worden herberekend. Indien u benoemde bereiken of externe verwijzingen gebruikt, dient u deze na het transponeren te controleren.
Transponeer en behoud verwijzingen met Kutools voor Excel
Als u regelmatig gegevens met formules moet transponeren, biedt Kutools voor Excel een gestroomlijnde oplossing. Met de Converteer celverwijzingen-functie zet u in één keer alle formuleverwijzingen om naar absolute verwijzingen vóór de transpositie. Zo blijven de oorspronkelijke verwijzingen na het transponeren intact en minimaliseert u handmatige aanpassingen én het risico op defecte formules.
Na installatie van Kutools voor Excel, volgt u deze stappen:
1. Selecteer de cellen met de formules die u wilt transponeren en klik op Kutools > Meer (in de groep Formule) > Converteer celverwijzingen. Hiermee opent u het dialoogvenster voor verwijzingsconversie.
2. Selecteer in het dialoogvenster Converteer celverwijzingen de optie Naar absoluut en klik op OK. Hierdoor worden alle celverwijzingen in de geselecteerde formules omgezet naar absolute verwijzingen (met $-tekens), zodat ze niet meer meeschuiven wanneer u de cellen transponeert.
3. Selecteer nu opnieuw de cellen en druk op Ctrl + C om ze te kopiëren. Klik met de rechtermuisknop op de gewenste plaklocatie en kies Transponeren in het submenu Plakken speciaal van het contextmenu. Zo transponeert u uw gegevens én behoudt u de juiste formuleverwijzingen.
De Kutools-oplossing is het meest effectief als u dergelijke taken regelmatig uitvoert, vooral bij grotere hoeveelheden gegevens of complexere werkbladen met meerdere formules. Controleer altijd of absolute verwijzingen na transpositie wenselijk zijn; u moet verwijzingen mogelijk weer terugzetten naar relatief indien nodig via dezelfde functie. Als uw oorspronkelijke formules een combinatie van relatieve en absolute verwijzingen bevatten, controleert u deze na conversie en transpositie op nauwkeurigheid.
VBA-code – Transponeer cellen en behoud formuleverwijzingen (relatief of absoluut)
Voor geavanceerde scenario’s stelt het schrijven van een VBA-macro u in staat om het transponeren van formules volledig te automatiseren, waarbij de oorspronkelijke verwijzingstypen — relatief, absoluut of gemengd — intact blijven. Deze oplossing is ideaal voor gebruikers met ervaring in macro’s en vooral waardevol bij grotere bereiken of wanneer deze bewerking regelmatig moet worden uitgevoerd. VBA biedt flexibiliteit, ondersteunt complexe verwijzingspatronen en verwerkt diverse formulestructuren direct en efficiënt.
1. Schakel eerst het Ontwikkelaar-tabblad in Excel in als dit nog niet zichtbaar is. Ga naar Ontwikkelaar > Visual Basic om de VBA-editor te openen.
2. Klik in de VBA-editor op Invoegen > Module om een nieuw modulevenster te openen en kopieer vervolgens de volgende VBA-code naar dit venster:
Sub TransposeFormulasPreserveReferences()
Dim ws As Worksheet
Dim sourceRange As Range
Dim destRange As Range
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long
Dim tempArray As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set sourceRange = Application.InputBox("Select the range you want to transpose", xTitleId, Selection.Address, Type:=8)
If sourceRange Is Nothing Then Exit Sub
numRows = sourceRange.Rows.Count
numCols = sourceRange.Columns.Count
Set destRange = Application.InputBox("Select the upper-left cell for the transposed output", xTitleId, , Type:=8)
If destRange Is Nothing Then Exit Sub
tempArray = sourceRange.Formula ' Store original formulas
' Transpose formulas, cell by cell
For i = 1 To numRows
For j = 1 To numCols
destRange.Offset(j - 1, i - 1).Formula = tempArray(i, j)
Next j
Next i
End Sub3. Om de code uit te voeren, klikt u op de 
knop of drukt u op F5. Volg de aanwijzingen: selecteer de brongegevens (inclusief formules) die u wilt transponeren en geef de begincel voor de uitvoer op. De macro kopieert en transponeert alle formules en behoudt daarbij de verwijzingen zoals ze in de oorspronkelijke locatie waren. Als uw formules relatieve verwijzingen gebruiken, houd er dan rekening mee dat hun context kan veranderen (de resulterende waarden kunnen afwijken van de originele), maar de formuletekst zelf wordt niet aangepast, zodat het verwijzingstype behouden blijft.
Deze aanpak is vooral nuttig bij grote datasets, herhaalde bewerkingen of wanneer fijne controle nodig is. Als er een fout optreedt (bijvoorbeeld doordat u geen bestemmingsgebied van voldoende grootte hebt geselecteerd), voer dan de macro opnieuw uit en controleer uw bereikselecties zorgvuldig.
Kort samengevat biedt Excel verschillende manieren om gegevens te transponeren terwijl de oorspronkelijke formuleverwijzingen behouden blijven, waaronder handmatige Zoeken en Vervangen, Geavanceerde Hulpmiddelen zoals Kutools, VBA-automatisering en formule-gebaseerde methoden met INDIRECT of ADRES. Houd bij het kiezen van een methode rekening met de grootte van uw gegevens, de complexiteit van de formules en de noodzaak van automatisering versus handmatige controle. Controleer het resultaat altijd dubbel—vooral bij relatieve verwijzingen—om te waarborgen dat de berekeningen correct blijven, en sla een back-upkopie van uw werkmap op voordat u bulkwijzigingen doorvoert of macro’s uitvoert. Als u problemen ondervindt, zoals „ref”-fouten of onverwachte waarden, controleer dan of geen verwijzingen buiten het juiste bereik terechtkomen of of absolute/relatieve gemengde verwijzingen onjuist zijn verschoven. Probeer bij twijfel eerst uw methode uit op een klein voorbeeld om vertrouwen in het proces op te bouwen.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins