Hoe plak je getransponeerd en behoud je formuleverwijzingen in Excel?
Bij het werken met Excel wordt de Transpose-functie vaak gebruikt om de oriëntatie van gegevens van kolommen naar rijen of vice versa te wijzigen. Echter, een veelvoorkomend probleem doet zich voor wanneer deze gegevens formules bevatten – Excel zal standaard de celverwijzingen aanpassen om overeen te komen met de nieuwe oriëntatie. Zoals in de onderstaande schermafbeelding te zien is, leidt deze automatische aanpassing vaak tot verbroken berekeningen, vooral bij complexe of gekoppelde datasets. Het begrijpen hoe je getransponeerde gegevens kunt plakken terwijl je de originele formuleverwijzingen behoudt, is essentieel voor iedereen die financiële modellen, technische berekeningen of gekoppelde dashboards beheert waarbij de integriteit van de formules belangrijk is. Dit artikel legt verschillende praktische methoden uit om dit resultaat te bereiken, bespreekt hun beste- en slechtste-gevalsgebruiksscenario's en biedt oplossingen voor soepelere operaties.
Transponeer en behoud verwijzingen met de Zoeken en vervangen-functie
Transponeer en behoud verwijzingen met Kutools voor Excel
VBA-code - Transponeer cellen terwijl formuleverwijzingen (relatief of absoluut) behouden blijven
Excel-formule - Handmatig getransponeerde formules opnieuw maken met INDIRECT of adresconstructie
Gebruik de F4-toets om formuleverwijzingen naar absolute te converteren en de gegevens te transponeren
1. Selecteer de Formulecel
Klik op de cel die de formule bevat die je wilt aanpassen.
2. Open de Formulebalk
Klik in de formulebalk om je cursor in de formule te plaatsen.
3. Converteer naar Absolute Verwijzingen
Selecteer de hele formule in de formulebalk en druk op de F4-toets.
Dit schakelt het verwijzingsformaat tussen relatief, absoluut en gemengd.
Herhaal dit voor alle celverwijzingen in de formule totdat ze volledig absoluut zijn.
4. Kopieer de Gegevens
Selecteer het gegevensbereik dat je wilt kopiëren en druk op Ctrl + C.
5. Plak als Getransponeerde Gegevens
Klik met de rechtermuisknop op de doelcel en selecteer Plakken Speciaal → Transponeer.
Tip:
Absolute verwijzingen zorgen ervoor dat de formule altijd naar dezelfde cellen verwijst, zelfs wanneer deze worden gekopieerd of verplaatst. Het transponeren van de gegevens wisselt rijen naar kolommen of kolommen naar rijen – perfect voor het herorganiseren van de indeling van gegevens.
Transponeer en behoud verwijzingen met de Zoeken en vervangen-functie
Om een bereik van cellen te transponeren en de originele formuleverwijzingen in Excel te behouden, kun je de functie Zoeken en vervangen gebruiken om tijdelijk formules naar tekst te converteren, ze te herbekijken en vervolgens weer terug te zetten naar formules. Deze aanpak is geschikt voor kleine tot middelgrote datasets en handig wanneer je geen extra invoegtoepassingen hebt geïnstalleerd of liever geen VBA gebruikt.
1. Selecteer eerst het bereik van cellen met de formules die je wilt transponeren. Druk op Ctrl + H om het dialoogvenster Zoeken en vervangen te openen.
2. In het Zoeken en vervangen dialoogvenster, typ = in het Zoeken naar veld, en typ #= in het Vervangen door veld. Deze stap converteert actieve formules naar platte tekst door het gelijkteken te vervangen. Hierdoor voorkom je dat Excel formule-celverwijzingen aanpast tijdens het kopiëren en transponeren.
3. Klik Alles vervangenEen dialoogvenster verschijnt waarin het aantal vervangingen wordt weergegeven. Klik OK en daarna Sluiten om de dialoogvensters te sluiten.
4. Met de nu geconverteerde tekstcellen nog steeds geselecteerd, druk Ctrl + C om ze te kopiëren. Ga naar de gewenste plaklocatie, klik met de rechtermuisknop en selecteer in het contextmenu Plakken speciaal > Transponeer om te transponeren en te plakken. Houd er rekening mee dat als je een grote dataset hebt of je formules vluchtige functies gebruiken, je de geplakte resultaten zorgvuldig moet controleren.
5. Nadat je hebt geplakt, druk Ctrl + H nogmaals om het dialoogvenster Zoeken en vervangen te openen. Keer nu de oorspronkelijke vervanging om: typ #= in het Zoeken naar vak en = in het Vervangen door vak. Dit zet de tekst terug naar functionele formules.
6. Klik Alles vervangen, daarna OK > Sluiten om af te ronden. Je formules zijn nu getransponeerd en blijven de verwijzingen zoals in het originele bereik.
Deze handmatige methode werkt het beste voor kleine datasets. Voor meer complexe bereiken of wanneer je te maken hebt met gemengde verwijzingsstijlen, controleer dan de resultaten zorgvuldig om ervoor te zorgen dat de formules opnieuw berekenen zoals verwacht. Als je benoemde bereiken of externe verwijzingen hebt, moeten deze mogelijk worden beoordeeld na transponering.
Transponeer en behoud verwijzingen met Kutools voor Excel
Als je regelmatig gegevens die formules bevatten moet transponeren, biedt Kutools voor Excel een gestroomlijnde aanpak. De Conversie-verwijzingen-hulpprogramma stelt je in staat om snel alle formuleverwijzingen naar absolute verwijzingen te converteren voordat je transponeert. Dit zorgt ervoor dat de originele verwijzingen intact blijven na het transponeren, waardoor handmatige tussenkomst en het risico op gebroken formules worden geminimaliseerd.
Na het installeren van Kutools voor Excel, volg deze stappen:
1. Selecteer de cellen met de formules die je wilt transponeren, klik daarna Kutools > Meer (in de Formulegroep) > Converteer celverwijzingenDit opent het conversiedialoogvenster voor verwijzingen.
2. In het Converteer formuleverwijzingen dialoogvenster, selecteer de optie Naar absoluut en klik OKDeze stap zorgt ervoor dat alle celverwijzingen in je geselecteerde formules worden ingesteld als absolute verwijzingen (met $-tekens). Als gevolg hiervan zullen de verwijzingen niet verschuiven wanneer je de cellen transponeert.
3. Selecteer nu de cellen opnieuw en druk Ctrl + C om ze te kopiëren. Ga naar de gewenste plaklocatie, klik met de rechtermuisknop en selecteer Transponeer uit het Plakken speciaal submenu in het contextmenu. Dit zal je gegevens transponeren en de juiste formuleverwijzingen behouden.
De Kutools-oplossing is het meest effectief als je dergelijke taken regelmatig uitvoert, vooral met grotere hoeveelheden gegevens of meer complexe spreadsheets met meerdere formules. Ter voorzorg controleer je altijd of absolute verwijzingen gewenst zijn na transponering; je moet de verwijzingen eventueel terugzetten naar relatief met dezelfde functie. Als je originele formules relatieve en absolute verwijzingen mengen, controleer ze dan op nauwkeurigheid na de conversie en transponeeroperatie.
VBA-code - Transponeer cellen terwijl formuleverwijzingen (relatief of absoluut) behouden blijven
Voor geavanceerde scenario's maakt het schrijven van een VBA-macro het mogelijk om het proces van het transponeren van formules te automatiseren terwijl de originele verwijzingstypen – of ze nu relatief, absoluut of gemengd zijn – behouden blijven. Deze oplossing is geschikt voor gebruikers die bekend zijn met macro’s en vooral nuttig voor grotere databereiken of wanneer deze operatie vaak wordt uitgevoerd. VBA biedt flexibiliteit, ondersteunt complexe patronen van verwijzingen en behandelt diverse formulestructuren direct.
1. Schakel eerst het tabblad Ontwikkelaar in Excel in als het nog niet zichtbaar is. Ga naar Ontwikkelaar > Visual Basic om de VBA-editor te openen.
2. In de VBA-editor, klik Invoegen > Module om een nieuw modulevenster te openen, kopieer en plak vervolgens de volgende VBA-code in 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, klik op de 
knop of druk op F5Volg de prompts: selecteer de brongegevens (inclusief formules) die je wilt transponeren en de startcel voor de output. De macro zal alle formules kopiëren en transponeren, waarbij de verwijzingen hetzelfde blijven als in de oorspronkelijke locatie. Als je formules relatieve verwijzingen gebruiken, houd er dan rekening mee dat hun context kan veranderen (resulterende waarden komen mogelijk niet overeen met de originele), maar de formuletijd zelf zal niet worden aangepast, waardoor het verwijzingstype behouden blijft.
Deze aanpak is bijzonder nuttig voor grote datasets, herhaalde bewerkingen of wanneer fijnmazige controle nodig is. Als er een fout optreedt (zoals niet correct een bestemmingsgebied van voldoende grootte selecteren), voer de macro dan opnieuw uit en controleer je bereikselecties zorgvuldig.
Samenvattend biedt Excel verschillende manieren om gegevens te transponeren terwijl de originele formuleverwijzingen behouden blijven, inclusief handmatig zoeken en vervangen, geavanceerde tools zoals Kutools, VBA-automatisering en formule-gebaseerde benaderingen met INDIRECT of ADRES. Bij het kiezen van een methode, houd rekening met de grootte van je gegevens, de complexiteit van de formules en de noodzaak van automatisering versus handmatige controle. Controleer altijd het resultaat – vooral met relatieve verwijzingen – om ervoor te zorgen dat berekeningen correct blijven, en maak een back-upkopie van je werkboek voordat je bulkwijzigingen uitvoert of macro's draait. Als je problemen tegenkomt zoals "ref"-fouten of onverwachte waarden, controleer dan of geen verwijzingen buiten het juiste bereik vallen of dat gemengde absolute/relatieve verwijzingen niet incorrect zijn verschoven. Wanneer je twijfelt, probeer dan je methode eerst op een klein voorbeeld om vertrouwen in het proces op te bouwen.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen