Hoe verwijst u naar dezelfde cel vanuit meerdere werkbladen in Excel?

Stel dat u een werkboek heeft dat meerdere werkbladen bevat, en nu wilt u gegevens uit dezelfde cel over verschillende werkbladen extraheren naar één hoofdwerkblad. Bijvoorbeeld, cel B8 van Sheet1, Sheet2, Sheet3, Sheet4… naar een hoofdblad zoals in de volgende schermafbeelding te zien is. Hoe kunt u deze taak snel en gemakkelijk in Excel oplossen?

Verwijs naar dezelfde cel vanuit meerdere werkbladen naar één hoofdblad met een formule

Verwijs naar dezelfde cel vanuit meerdere werkbladen naar één hoofdblad met VBA-code

Verwijs naar dezelfde cel vanuit meerdere werkbladen naar één hoofdblad met een geweldige functie

Verwijs naar dezelfde cel vanuit meerdere werkbladen naar één hoofdblad met een formule

Als uw werkbladnamen standaard bladnamen zijn, zoals Sheet1, Sheet2, Sheet3..., kunt u een formule gebruiken om deze taak snel in Excel af te handelen.

1. Maak eerst een lijst met hulpcijfers aan door 1, 2, 3, 4... reeksnummers in te voeren die de werkbladen aangeven waarnaar u wilt verwijzen, zie onderstaande schermafbeelding:

2. Voer vervolgens de onderstaande formule in de cel in waar u de gegevens wilt extraheren, en sleep het vulhandvat omlaag naar de cellen waarop u deze formule wilt toepassen, en alle waarden uit dezelfde cel vanuit meerdere werkbladen zijn geëxtraheerd, zie onderstaande schermafbeelding:

Opmerking: In bovenstaande formule is E2 het hulpnummer dat u in stap 1 hebt ingevoerd, en B8 is de celverwijzing die u wilt extraheren. Deze formule werkt alleen goed als de bladnamen Sheet1, Sheet2, Sheet3... zijn.

Verwijs naar dezelfde cel vanuit meerdere werkbladen naar één hoofdblad met VBA-code

Als er tientallen werkbladen zijn waarvan de bladnamen verschillende inhoud bevatten in plaats van Sheet1, Sheet2, dan kan de volgende VBA-code u helpen om dezelfde celverwijzingen vanuit meerdere werkbladen in één werkblad tegelijk in te vullen.

1. Klik in het Hoofdwerkblad op cel B8, dit is de cel die u wilt extraheren vanuit andere werkbladen.

2. Houd de toetsen ALT + F11 ingedrukt, en het venster Microsoft Visual Basic for Applications wordt geopend.

3. Klik op Invoegen > Module, en plak de volgende code in het Modulevenster.

VBA-code: verwijzing naar dezelfde cel vanuit meerdere werkbladen

Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub

4. Druk vervolgens op de F5-toets om deze code uit te voeren, en alle waarden van cel B8 vanuit andere werkbladen zijn getrokken naar het Hoofdwerkblad. Zie onderstaande schermafbeelding:

Opmerking: Deze VBA-code wordt toegepast voor het invullen van de cellen waarop u klikt. Bijvoorbeeld, als u op cel A1 in een specifiek werkblad klikt, worden alle waarden van cel A1 vanuit andere werkbladen ingevuld in dit werkblad.

Verwijs naar dezelfde cel vanuit meerdere werkbladen naar één hoofdblad met een geweldige functie

Als u niet bekend bent met de VBA-code, hier introduceer ik u een nuttig hulpmiddel - Kutools voor Excel, met de functie Automatisch werkbladverwijzingen verhogen, kunt u eenvoudig celverwijzingen vanuit meerdere werkbladen in Excel invullen.

Nadat u Kutools voor Excel hebt geïnstalleerd, doet u het volgende:

1. Klik op cel B8 binnen het Hoofdblad waarvan u wilt extraheren vanuit andere werkbladen.

2. Klik vervolgens op Kutools > Meer > Automatisch werkbladverwijzingen verhogen, zie onderstaande schermafbeelding:

3. Selecteer in het dialoogvenster Vul werkbladverwijzingen in de optie Vul verticaal cel na cel in de Vulvolgorde, en klik op het kleine slotje naast het formuletekstvak, en het grijze slotje wordt geel, wat betekent dat de formule en celverwijzing zijn vergrendeld. Vervolgens kunt u op elke cel klikken om de cel B8-verwijzingen vanuit andere werkbladen te extraheren, in dit voorbeeld klik ik op cel B2.  Vink vervolgens de werkbladen aan waaruit u de celverwijzingen wilt extraheren. Zie onderstaande schermafbeelding:

Opmerking: Als u dit kleine slotje niet vergrendelt, zal de celverwijzing die u klikt veranderen met de geselecteerde cel.

4. Klik vervolgens op de knop Vul bereik in, en de celwaarden van cel B8 vanuit andere werkbladen zijn verticaal weergegeven in het Hoofdwerkblad, sluit vervolgens dit dialoogvenster. Zie onderstaande schermafbeelding:

Klik hier om Kutools voor Excel te downloaden en gratis te proberen!

Meer artikelen: