Hoe verwijzingen te transponeren tijdens automatisch invullen naar beneden/rechts in Excel?
Wanneer u werkt met formules en automatisch invullen in Excel, is het standaardgedrag dat kolomverwijzingen verhogen wanneer u de vulgreep horizontaal naar rechts sleept, en rijverwijzingen verhogen wanneer u verticaal naar beneden sleept. Soms moet u echter verwijzingen transponeren — bijvoorbeeld een verticaal bereik refereren maar horizontaal invullen, of vice versa. Deze bewerking is vooral handig bij het herorganiseren van gegevensindelingen of het maken van dynamische rapporten die gegevens op een getransponeerde manier kruislings moeten weergeven. Dit artikel onderzoekt verschillende praktische methoden om deze getransponeerde automatische invulling te bereiken, inclusief formules, VBA-macros en handige tools zoals Kutools. Zie hieronder voor een typisch scenario van getransponeerd automatisch invullen:
Verwijzingen transponeren tijdens invullen naar beneden of rechts
Excel-formule – Gebruik INDEX of OFFSET om handmatig getransponeerde verwijzingen te construeren
Verwijzingen transponeren tijdens invullen naar beneden of rechts
Om een verwijzing te transponeren terwijl u over rijen of kolommen vult, kan de functie TRANSPOSE in Excel worden gebruikt. Deze methode is geschikt wanneer u gegevens die in een kolom zijn gerangschikt wilt omzetten in een rij (of vice versa), waarbij de formule automatisch de richting van de verwijzing aanpast wanneer u automatisch invult.
Selecteer het bereik waarin u de getransponeerde verwijzing wilt toepassen. Voer de volgende formule in in de formulebalk: =TRANSPOSE(Sheet2!$B$1:B12) en druk vervolgens op Ctrl + Shift + Enter gelijktijdig (voor versies ouder dan Microsoft 365 / Excel 2021). In nieuwe versies die dynamische matrices ondersteunen, is het voldoende om gewoon op Enter te drukken. Zie de onderstaande schermafbeelding:
In deze formule B1:B12 vertegenwoordigt het bereik dat u wilt transponeren van een verticaal bereik naar een horizontale indeling. U kunt de naam van het werkblad en het specifieke bereik aanpassen om te passen bij uw daadwerkelijke databron.
Tip: Om formulefouten te voorkomen, zorg ervoor dat het doelbereik voor de getransponeerde gegevens correct is gesized (bijvoorbeeld, als uw bron één kolom met 12 rijen is, moet uw doelbereik één rij met 12 kolommen zijn).
Excel-formule – Gebruik INDEX of OFFSET om handmatig getransponeerde verwijzingen te construeren
Soms is het gebruik van matrixformules zoals TRANSPOSE niet gewenst, of hebt u meer controle nodig over hoe verwijzingen worden getransponeerd, vooral bij niet-aaneengesloten gegevens of bij het werken in oudere Excel-versies. Met de functies INDEX en OFFSET kunt u rechtstreeks naar getransponeerde gegevens verwijzen met behulp van standaardvulinstructies, zonder Ctrl + Shift + Enter te hoeven gebruiken.
Toepasbare scenario's: Aanbevolen wanneer u per cel een een-op-een getransponeerde verwijzing wilt, bijvoorbeeld cel A1 verticaal naar B1 horizontaal verwijzen (of vice versa). Nuttig om array-items te vermijden, of voor dynamische verwijzingen over complexe werkbladen.
Voordelen: Geen array-invoer vereist, formules zijn eenvoudig om te slepen en invullen.
Potentiële nadelen: Kan iets moeilijker zijn om de initiële formule in te stellen; let goed op hoe u de rij- en kolomindex aanpast.
Hoe INDEX te gebruiken voor getransponeerde verwijzingen:
1. Stel dat u gegevens heeft in A1:A5 (verticaal), en u wilt deze getransponeerd invullen in B1:F1 (horizontaal). Selecteer cel B1 en voer de volgende formule in:
=INDEX($A$1:$A$5,COLUMN(A1))2. Druk op Enter, en sleep de formule vervolgens naar rechts tot cel F1. De formule gebruikt COLUMN(A1) om te verhogen wanneer u naar rechts gaat, waarmee A1, A2, ..., A5 wordt gerefereerd over B1:F1.
Hoe OFFSET te gebruiken voor getransponeerde verwijzingen:
1. Stel dat uw gegevens zich in rij B1:F1 (horizontaal) bevinden, en u wilt ze verticaal invullen in kolom G1:G5. Voer in cel G1 in:
=OFFSET($B$1,0,ROW(A1)-1)2. Druk op Enter, en sleep de formule vervolgens naar beneden tot G5. Wanneer u naar beneden vult, zal ROW(A1)-1 vanaf 0 verhogen, waardoor de kolomindex van de OFFSET-functie verschuift om B1, C1, D1, etc. op te halen.
Pas het formulebereik of de startpositie zo nodig aan om overeen te komen met uw gegevensindeling. Controleer altijd de juiste verwijzing, vooral bij het kopiëren of verplaatsen van formules, om verwijzingsfouten te voorkomen.
Problemen oplossen: Als u een #VERW!-fout krijgt, controleer dan de verwijzingsbereiken en zorg ervoor dat de formule geen poging doet om buiten uw doelgegevensset te verwijzen. Ook kunnen deze formules bij grote datasets de rekenbelasting van het werkboek vergroten.
Samenvattend, of u nu formules, VBA of een Excel-invoegtoepassing kiest, hangt af van uw gegevensstructuur, de frequentie waarmee u deze bewerking moet uitvoeren, en uw comfortniveau met geavanceerde Excel-functies. Als één aanpak niet past bij uw behoeften, probeer dan een andere methode zoals hierboven beschreven. Als u blijvende formule- of verwijzingsproblemen tegenkomt, controleer dan uw formules op vergrendelde celverwijzingen (gebruik van $-symbolen) en pas ze zo nodig aan voor de juiste richting bij transponeren.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
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