Wie verweise ich in Excel auf dieselbe Zelle aus mehreren Arbeitsblättern?

Angenommen, Sie haben eine Arbeitsmappe mit mehreren Arbeitsblättern und möchten nun Daten aus derselben Zelle über mehrere Arbeitsblätter in ein Master-Arbeitsblatt extrahieren. Zum Beispiel die Zelle B8 aus Tabelle1, Tabelle2, Tabelle3, Tabelle4… in ein Master-Arbeitsblatt wie im folgenden Screenshot zu sehen ist. Wie könnten Sie diese Aufgabe in Excel schnell und einfach lösen?

Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Master-Arbeitsblatt mit einer Formel

Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Master-Arbeitsblatt mit VBA-Code

Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Master-Arbeitsblatt mit einem erstaunlichen Feature

Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Master-Arbeitsblatt mit einer Formel

Wenn Ihre Arbeitsblattnamen Standardnamen wie Tabelle1, Tabelle2, Tabelle3... sind, können Sie eine Formel verwenden, um diese Aufgabe in Excel schnell zu erledigen.

1. Erstellen Sie zunächst eine Liste von Hilfszahlen. Geben Sie bitte die Sequenznummern 1, 2, 3, 4... ein, die die Arbeitsblätter angeben, auf die Sie verweisen möchten, siehe Screenshot:

2. Geben Sie dann die folgende Formel in die Zelle ein, in der Sie die Daten extrahieren möchten, und ziehen Sie den Ausfüllknauf nach unten in die Zellen, auf die Sie diese Formel anwenden möchten, und alle gleichen Zellwerte aus mehreren Arbeitsblättern wurden extrahiert, siehe Screenshot:

Hinweis: In der obigen Formel ist E2 die Hilfszahl, die Sie in Schritt 1 eingegeben haben, und B8 ist der Zellbezug, den Sie extrahieren möchten. Diese Formel funktioniert nur gut, wenn die Blattnamen Tabelle1, Tabelle2, Tabelle3... lauten.

Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Master-Arbeitsblatt mit VBA-Code

Wenn es Dutzende von Arbeitsblättern gibt, deren Namen verschiedene Inhalte statt Tabelle1, Tabelle2 enthalten, kann der folgende VBA-Code Ihnen helfen, die gleichen Zellbezüge aus mehreren Arbeitsblättern auf einmal in ein Arbeitsblatt einzufügen.

1. Klicken Sie im Master-Arbeitsblatt auf die Zelle B8, die die Zelle ist, die Sie aus anderen Arbeitsblättern extrahieren möchten.

2. Halten Sie die ALT + F11-Tasten gedrückt, und das Microsoft Visual Basic for Applications-Fenster öffnet sich.

3. Klicken Sie auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern

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. Drücken Sie dann die F5-Taste, um diesen Code auszuführen, und alle Werte der Zelle B8 aus anderen Arbeitsblättern wurden in das Master-Arbeitsblatt gezogen. Siehe Screenshot:

Hinweis: Dieser VBA-Code wird angewendet, um die Zellen zu füllen, auf die Sie klicken. Wenn Sie zum Beispiel die Zelle A1 in einem bestimmten Arbeitsblatt anklicken, werden alle Werte der Zelle A1 aus anderen Arbeitsblättern in dieses Arbeitsblatt eingefügt.

Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Master-Arbeitsblatt mit einem erstaunlichen Feature

Wenn Sie nicht mit dem VBA-Code vertraut sind, möchte ich Ihnen hier ein nützliches Tool vorstellen - Kutools für Excel. Mit seiner Funktion Automatische Inkrementierung von Arbeitsblattverweisen können Sie ganz einfach Zellbezüge aus mehreren Arbeitsblättern in Excel ausfüllen.

Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Klicken Sie auf die Zelle B8 innerhalb des Master-Arbeitsblatts, die Sie aus anderen Arbeitsblättern extrahieren möchten.

2. Klicken Sie dann auf Kutools > Mehr > Automatische Inkrementierung von Arbeitsblattverweisen, siehe Screenshot:

3. Wählen Sie im Dialogfeld „Arbeitsblattverweise ausfüllen“ die Option „Spalte füllen, dann Zeile füllen“ aus der Füllreihenfolge aus, und klicken Sie auf das kleine Schloss neben dem Formeltextfeld, und das graue Schloss wird gelb. Das bedeutet, dass die Formel und der Zellbezug gesperrt wurden. Dann können Sie auf eine beliebige Zelle klicken, um die Zellbezüge von B8 aus anderen Arbeitsblättern zu extrahieren. In diesem Beispiel werde ich auf die Zelle B2 klicken. Aktivieren Sie dann die Kontrollkästchen der Arbeitsblätter, aus denen Sie die Zellbezüge extrahieren möchten. Siehe Screenshot:

Hinweis: Wenn Sie dieses kleine Schloss nicht sperren, ändert sich der Zellbezug, auf den Sie geklickt haben, mit der ausgewählten Zelle.

4. Klicken Sie dann auf die Schaltfläche Bereich ausfüllen, und die Zellwerte von Zelle B8 aus anderen Arbeitsblättern wurden vertikal im Master-Arbeitsblatt aufgelistet, und schließen Sie dann dieses Dialogfeld. Siehe Screenshot:

Klicken Sie hier, um Kutools für Excel herunterzuladen und jetzt kostenlos zu testen!

Weitere Artikel: