Wie verweist man in Excel auf dieselbe Zelle aus mehreren Arbeitsblättern?
Beim Arbeiten mit Excel ist es sehr üblich, zusammengehörige Daten auf mehrere Arbeitsblätter innerhalb einer einzigen Arbeitsmappe zu verteilen. Manchmal müssen Sie möglicherweise Daten kombinieren oder vergleichen, indem Sie dieselbe Zelle – wie zum Beispiel B8 – aus all diesen Arbeitsblättern in ein einzelnes Zusammenfassungs- oder Hauptblatt übernehmen. Dieser Ansatz kann Ihnen helfen, konsistente Informationen wie monatliche Ergebnisse oder Abteilungssummen in eine umfassende Übersicht zu konsolidieren, um die Analyse und Berichterstellung zu erleichtern. Zum Beispiel könnten Sie monatliche Zahlen, die in Zelle B8 von mehreren Blättern (Tabelle1, Tabelle2, Tabelle3, Tabelle4 usw.) gespeichert sind, extrahieren und gemeinsam in einem Hauptblatt auflisten, wie unten gezeigt.
 |  |  |  |  |  |
Excel bietet mehrere Möglichkeiten, auf dieselbe Zelle aus mehreren Arbeitsblättern zu verweisen und die Ergebnisse in einem Master-Arbeitsblatt zu sammeln. Je nach Ihrer Vertrautheit mit Formeln, VBA oder Drittanbietertools können Sie die Methode wählen, die Ihren spezifischen Bedürfnissen am besten entspricht. Im Folgenden finden Sie praktische Ansätze zur effizienten Erledigung dieser Aufgabe. Darüber hinaus werden Sie im Inhaltsverzeichnis zwei weitere empfohlene Lösungen finden, die ähnliche Szenarien abdecken können:
Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Masterblatt mit Formel
Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Masterblatt mit VBA-Code
Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Masterblatt mit Formel
Wenn Ihre Arbeitsblattnamen dem Standardmuster folgen (Tabelle1, Tabelle2, Tabelle3 usw.), bieten Excel-Formeln eine schnelle Möglichkeit, auf dieselbe Zelle aus einer Reihe von Arbeitsblättern zu verweisen. Dieser Ansatz ist einfach, dynamisch und erfordert keinen Code, was ihn besonders geeignet macht, wenn Sie häufig Blätter hinzufügen oder entfernen, die demselben Namensschema folgen.
1. Beginnen Sie damit, eine Liste von Hilfszahlen in einer Spalte zu erstellen, zum Beispiel 1, 2, 3, 4 usw. Jede Zahl entspricht einem der Arbeitsblattsuffixe, auf die Sie verweisen möchten. Dieser Schritt automatisiert den Referenzierungsprozess und vermeidet das manuelle Schreiben von Formeln für jedes Blatt. Siehe Screenshot:
2. Geben Sie in der Zelle, in der Sie den extrahierten Wert anzeigen möchten (z. B. in Zelle B2 Ihres Hauptblatts), die folgende Formel ein. Ziehen Sie dann den Ausfüllknauf nach unten, um automatisch auf jedes entsprechende Arbeitsblatt zu verweisen. Alle Zielzellwerte (hier B8 aus jedem Blatt) werden auf einmal abgerufen.
Hinweis: In dieser Formel bezieht sich E2 auf Ihre Hilfszahl (1, 2, 3, …), die dem Suffix des Arbeitsblattnamens entspricht, und B8 ist die Adresse der Zelle, die Sie übernehmen möchten. Passen Sie E2 und B8 je nach Ihrer tatsächlichen Layout- und Zelladresse an. Diese Methode funktioniert nur direkt, wenn Ihre Arbeitsblätter dem Namensmuster "Tabelle1", "Tabelle2" usw. folgen.
Diese formelbasierte Lösung ist schnell und einfach für strukturierte Arbeitsmappen, kann aber umständlich werden, wenn die Blätter nicht-numerische Namen haben oder häufig hinzugefügt/entfernt werden. Überprüfen Sie doppelt auf #BEZUG!-Fehler, die auf eine Diskrepanz zwischen Ihren Hilfswerten und vorhandenen Blattnamen hinweisen könnten.
Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Masterblatt mit VBA-Code
Wenn Ihre Arbeitsmappe viele Arbeitsblätter enthält und deren Namen keinem vorhersehbaren Muster folgen (d. h., die Blattnamen sind angepasst oder nicht sequentiell), bietet die Verwendung von VBA einen direkten Ansatz, um denselben Zellbezug aus allen Blättern in Ihr Master-Arbeitsblatt zu extrahieren. Diese Methode ermöglicht die Batch-Verarbeitung unabhängig von den Blattnamen und ist hilfreich zum Konsolidieren großer oder nicht einheitlicher Arbeitsmappen.
1. Klicken Sie in Ihrem Master-Arbeitsblatt auf die Zelle (z. B. B8), in der Sie die extrahierten Daten aus anderen Arbeitsblättern anzeigen möchten. Stellen Sie sicher, dass unter dieser Zelle genügend leere Zeilen vorhanden sind, um die Ergebnisse aufzunehmen, die aus jedem Arbeitsblatt extrahiert werden.
2. Drücken Sie die Tasten ALT + F11 gleichzeitig, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
3. Klicken Sie im VBA-Editor auf Einfügen > Modul. Kopieren 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 Sub4. Nachdem Sie den Code eingefügt haben, drücken Sie die Taste F5 oder klicken Sie auf die Schaltfläche Ausführen, um den Code auszuführen. Alle Werte aus Zelle B8 jedes Arbeitsblatts (außer dem Masterblatt selbst, sofern im Code angegeben) werden gesammelt und in das Master-Arbeitsblatt eingefügt, beginnend bei der ausgewählten Zelle nach unten. Die Ergebnisse werden vertikal aufgelistet, sodass sie leicht überprüft oder weiter analysiert werden können.
Hinweis: Diese VBA-Lösung funktioniert für die Zelle, die Sie vor dem Ausführen des Codes auswählen. Wenn Sie zum Beispiel Zelle A1 anklicken, werden alle entsprechenden A1-Werte aus den anderen Blättern übernommen.
Verweis auf dieselbe Zelle aus mehreren Arbeitsblättern in ein Masterblatt mit einem fantastischen Feature
Für Benutzer, die nach einer komfortableren Lösung ohne Formeln oder Codierung suchen, bietet Kutools für Excel eine intuitive Funktion namens Dynamische Arbeitsblattreferenzierung. Mit diesem Tool können Sie den gleichen Zellbezug aus Dutzenden oder sogar Hunderten von Arbeitsblättern mit nur wenigen Klicks extrahieren, unabhängig davon, wie die Blätter benannt sind. Dies ist ideal für Benutzer, die häufig Daten konsolidieren und einen visuellen, schrittweisen Prozess gegenüber dem Schreiben von Formeln oder dem Bearbeiten von VBA-Code bevorzugen.
Nach der Installation von Kutools für Excel folgen Sie diesen Schritten, um Daten aus mehreren Arbeitsblättern zu konsolidieren:
1. Wählen Sie in Ihrem Masterblatt die Zelle aus (z. B. B8), in der Sie die zu füllenden Referenzen aus anderen Arbeitsblättern anzeigen möchten.
2. Gehen Sie zur Registerkarte Kutools, wählen Sie Mehr und dann Dynamische Arbeitsblattreferenzierung, wie unten gezeigt:
3. Wählen Sie im Dialogfeld Arbeitsblattreferenzen ausfüllen die Option Zelle für Zelle vertikal ausfüllen aus dem Ausfüllreihenfolge-Menü aus, und klicken Sie auf das kleine Schloss neben dem Formeltextfeld, und das graue Schloss wird zu einem gelben Schloss, was bedeutet, dass die Formel und der Zellbezug gesperrt sind. Dann können Sie auf eine beliebige Zelle klicken, um die B8-Referenzen aus anderen Arbeitsblättern zu extrahieren. In diesem Beispiel klicke ich auf Zelle B2. Aktivieren Sie dann die Arbeitsblätter, aus denen Sie die Zellreferenzen extrahieren möchten. Siehe Screenshot:
Hinweis: Klicken Sie immer auf das Schloss, um Ihren gewählten Zellbezug festzulegen. Wenn das Schloss nicht gesetzt ist, kann sich der Zellbezug basierend auf Ihren Auswahlmöglichkeiten verschieben, was möglicherweise zu falschen Ergebnissen führt.
4. Klicken Sie auf die Schaltfläche Bereich ausfüllen. Das Programm extrahiert sofort die gewählten Zellwerte (z. B. B8) aus allen ausgewählten Arbeitsblättern und listet sie in Ihrem Masterblatt auf, jeder in seiner eigenen Zeile. Sobald dies abgeschlossen ist, können Sie das Dialogfeld schließen und die Formatierung Ihres Zusammenfassungsblatts nach Bedarf anpassen.
Dieser Ansatz ist besonders nützlich, wenn Sie mit einer großen Anzahl von Blättern arbeiten oder wenn die Blattnamen unregelmäßig sind und häufige manuelle Referenzierung oder Formelanpassungen unpraktisch sind. Der Prozess ist visuell und leicht nachvollziehbar, was die Konsolidierung von Arbeitsmappen viel übersichtlicher macht.
Klicken Sie hier, um Kutools für Excel herunterzuladen und jetzt kostenlos zu testen!
Weitere Artikel:
- Kopieren von Zeilen aus mehreren Arbeitsblättern basierend auf Kriterien in ein neues Blatt
- Angenommen, Sie haben eine Arbeitsmappe mit mehreren Arbeitsblättern, die alle das gleiche Format teilen. Wenn Sie alle Zeilen, in denen Spalte C den Text „Abgeschlossen“ enthält, in ein neues Arbeitsblatt kopieren möchten, hilft Ihnen dieses Handbuch dabei, dies effizient zu erreichen – keine manuelle Kopier- und Einfügearbeit mehr, was viel Zeit spart.
- Erstellen einer Liste eindeutiger Werte aus mehreren Arbeitsblättern
- Suchen Sie nach einer schnellen Möglichkeit, eine Liste eindeutiger Werte aus allen Arbeitsblättern einer Arbeitsmappe zu erstellen? Wenn zum Beispiel mehrere Blätter Listen von Namen enthalten, die sich überschneiden, hilft Ihnen diese Technik, jeden eindeutigen Namen in eine einzige neue Liste zur Analyse zu extrahieren.
- Countif eines bestimmten Wertes über mehrere Arbeitsblätter
- Wenn Sie mehrere Arbeitsblätter mit ähnlichen Daten haben und wissen möchten, wie oft ein bestimmter Wert (z. B. „Excel“) in allen Blättern vorkommt, zeigt Ihnen dieser Artikel effektive Ansätze, die Sie verwenden können.
- Einfügen desselben Bildes in mehrere Arbeitsblätter
- Das Einfügen eines Bildes in ein Arbeitsblatt ist einfach, aber was tun Sie, wenn Sie möchten, dass dasselbe Bild in jedem Arbeitsblatt Ihrer Arbeitsmappe erscheint? Dieser Leitfaden demonstriert eine Methode, um dies effizient in wenigen Schritten zu erreichen.
- Gleichzeitiges Ausführen derselben Makros in mehreren Arbeitsblättern in Excel
- Standardmäßig verarbeitet das Ausführen eines Makros nur das aktive Blatt, aber Sie müssen es möglicherweise gleichzeitig auf mehrere Blätter anwenden. Dieser Artikel zeigt Methoden, um dasselbe Makro auf mehrere Arbeitsblätter gleichzeitig auszuführen und die Effizienz zu steigern.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins