Wie findet man schnell einen bestimmten Wert in mehreren Blättern oder Arbeitsmappen?
Mussten Sie jemals einen bestimmten Wert finden, der sich über verschiedene Blätter oder sogar mehrere Arbeitsmappen in Excel verteilt? Das ist ein alltägliches Szenario – besonders bei großen Projekten, Monatsberichten oder wenn Daten aus mehreren Dateien zusammengeführt werden müssen. Jedes Blatt oder jede Datei manuell zu durchsuchen, kostet nicht nur wertvolle Zeit, sondern birgt auch ein hohes Fehlerpotenzial. In diesem Tutorial zeigen wir Ihnen mehrere effektive Methoden, um Ihre Daten zielgenau und effizient zu finden – ob innerhalb einer Arbeitsmappe, über mehrere Arbeitsmappen hinweg oder mithilfe formelbasierter und konsolidierter Lösungen. Diese Ansätze decken genau die praktischen Anforderungen ab, auf die Sie in Ihren täglichen Excel-Workflows oder Datenanalyse-Projekten stoßen.
Suche eines Werts in mehreren Blättern einer Arbeitsmappe mithilfe der Suchen und Ersetzen-Funktion
Suche eines Werts in allen Arbeitsmappen eines Ordners mithilfe von VBA
Schnelle Suche eines Werts über mehrere geöffnete Arbeitsmappen hinweg mit Kutools für Excel 
Suche eines Werts über mehrere Blätter hinweg mithilfe von Excel-Formeln
Suche eines Werts in mehreren Blättern einer Arbeitsmappe mithilfe der Suchen und Ersetzen-Funktion
Die Suchen und Ersetzen-Funktion von Excel ist eine einfache, aber effektive Methode, um schnell bestimmte Werte über mehrere Arbeitsblätter innerhalb derselben Arbeitsmappe zu finden. Sie eignet sich besonders gut, wenn Sie genau wissen, welche Blätter Sie durchsuchen möchten, oder wenn Ihre Daten innerhalb einer einzelnen Datei übersichtlich und gut strukturiert sind. Zwar unterstützt sie keine Suche über verschiedene Dateien oder geschlossene Arbeitsmappen hinweg – doch für schnelle Abfragen bietet sie eine unkomplizierte Lösung.
1. Wählen Sie zunächst die Blattregisterkarten aus, die Sie in Ihre Suche einbeziehen möchten, indem Sie die Strg-Taste gedrückt halten und nacheinander auf jedes Arbeitsblatt in der Blattregister-Tab-Leiste klicken. Dadurch wird sichergestellt, dass die Suche gleichzeitig auf alle ausgewählten Arbeitsblätter angewendet wird. Siehe Screenshot:
2. Sobald Sie die gewünschten Blätter ausgewählt haben, drücken Sie Strg + F, um das Dialogfeld Suchen und Ersetzen zu öffnen. Geben Sie den gesuchten Wert in das Textfeld Suchen nach unter dem Reiter Suchen ein und klicken Sie anschließend auf die Schaltfläche Alle suchen. Excel zeigt sofort eine Liste aller Zellen auf den ausgewählten Arbeitsblättern an, die Ihren Suchwert enthalten, sowie deren Positionen. Siehe Screenshot:
Tipp: Das Suchen-und-Ersetzen-Tool durchsucht nur die geöffneten und sichtbaren ausgewählten Arbeitsblätter. Wenn Sie die Suche auf weitere Blätter ausdehnen möchten, stellen Sie sicher, dass Sie diese wie oben beschrieben auswählen. Sollten Sie versehentlich ein Blatt übersehen haben, wiederholen Sie den Vorgang einfach, nachdem Sie es in Ihre Auswahl aufgenommen haben.
Hinweis: Diese Methode durchsucht weder geschlossene noch ausgeblendete Arbeitsmappen und hebt gefundene Zellen nicht automatisch hervor – sie listet lediglich die Ergebnisse zur Navigation auf.
Fehlerbehebung: Falls Sie die erwarteten Ergebnisse nicht sehen, überprüfen Sie Ihre Blattauswahl und stellen Sie sicher, dass keine Filter oder Zellschutzmaßnahmen aktiv sind, die die Suche behindern oder Ergebnisse ausblenden könnten.
Werte mühelos über mehrere Arbeitsblätter und Arbeitsmappen hinweg Suchen und Ersetzen |
Kutools für Excels erweiterte Suchen und Ersetzen-Funktion bietet eine effiziente Möglichkeit, Werte über mehrere Arbeitsblätter oder sogar über alle geöffneten Arbeitsmappen hinweg zu suchen und zu ersetzen. Mit dieser erweiterten Funktion können Sie Zeit sparen und Fehler bei der Arbeit mit großen Datensätzen vermeiden, sodass Ihre Excel-Aufgaben schneller und präziser erledigt werden. |
 |
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Werkzeugen, um Ihre Arbeit schneller und einfacher zu gestalten, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen |
Suche eines Werts in allen Arbeitsmappen eines Ordners mithilfe von VBA
Wenn Sie einen bestimmten Wert in mehreren Arbeitsmappen durchsuchen müssen, die in einem Ordner gespeichert sind – einschließlich solcher, die nicht geöffnet sind – stoßen die integrierten Excel-Tools an ihre Grenzen. In einem solchen Fall übernimmt ein VBA-Makro (Visual Basic for Applications) die Arbeit für Sie: Es öffnet systematisch jede Arbeitsmappe im Ordner, durchsucht alle Arbeitsblätter und protokolliert präzise, wo Übereinstimmungen gefunden wurden. Dieser Ansatz ist ideal für regelmäßige Prüfungen oder das Auffinden von Werten in archivierten oder stapelverarbeiteten Dateien.
1. Öffnen Sie zunächst eine neue (oder leere) Arbeitsmappe in Excel. Wählen Sie die Zelle aus, in der die Suchergebnisse erscheinen sollen (normalerweise Zelle A1). Drücken Sie Alt + F11, um das Editorfenster für Microsoft Visual Basic for Applications zu starten.
2. Gehen Sie im VBA-Editor zu Einfügen > Modul, um ein neues Modul zu erstellen, und fügen Sie anschließend den folgenden VBA-Code in das Modulfenster ein.
VBA: Suche eines Werts in allen Arbeitsmappen eines Ordners.
Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
Dim xFso As Object
Dim xFld As Object
Dim xStrSearch As String
Dim xStrPath As String
Dim xStrFile As String
Dim xOut As Worksheet
Dim xWb As Workbook
Dim xWk As Worksheet
Dim xRow As Long
Dim xFound As Range
Dim xStrAddress As String
Dim xFileDialog As FileDialog
Dim xUpdate As Boolean
Dim xCount As Long
Dim xAWB As Workbook
Dim xAWBStrPath As String
Dim xBol As Boolean
Set xAWB = ActiveWorkbook
xAWBStrPath = xAWB.Path & "\" & xAWB.Name
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a forlder"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
xStrSearch = "KTE"
xUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
Set xOut = Worksheets.Add
xRow = 1
With xOut
.Cells(xRow, 1) = "Workbook"
.Cells(xRow, 2) = "Worksheet"
.Cells(xRow, 3) = "Cell"
.Cells(xRow, 4) = "Text in Cell"
Set xFso = CreateObject("Scripting.FileSystemObject")
Set xFld = xFso.GetFolder(xStrPath)
xStrFile = Dir(xStrPath & "\*.xls*")
Do While xStrFile <> ""
xBol = False
If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
xBol = True
Set xWb = xAWB
Else
Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
End If
For Each xWk In xWb.Worksheets
If xBol And (xWk.Name = .Name) Then
Else
Set xFound = xWk.UsedRange.Find(xStrSearch)
If Not xFound Is Nothing Then
xStrAddress = xFound.Address
End If
Do
If xFound Is Nothing Then
Exit Do
Else
xCount = xCount + 1
xRow = xRow + 1
.Cells(xRow, 1) = xWb.Name
.Cells(xRow, 2) = xWk.Name
.Cells(xRow, 3) = xFound.Address
.Cells(xRow, 4) = xFound.Value
End If
Set xFound = xWk.Cells.FindNext(After:=xFound)
Loop While xStrAddress <> xFound.Address
End If
Next
If Not xBol Then
xWb.Close (False)
End If
xStrFile = Dir
Loop
.Columns("A:D").EntireColumn.AutoFit
End With
MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
Set xOut = Nothing
Set xWk = Nothing
Set xWb = Nothing
Set xFld = Nothing
Set xFso = Nothing
Application.ScreenUpdating = xUpdate
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
3. Drücken Sie die Taste F5 oder klicken Sie auf die Schaltfläche Ausführen, um das Makro auszuführen. Anschließend öffnet sich das Dialogfenster Ordner auswählen, in dem Sie den Ordner festlegen können, der die zu durchsuchenden Arbeitsmappen enthält. Siehe Screenshot:
4. Klicken Sie auf OK. Nach Abschluss der Suche informiert Sie ein Meldungsfeld über die Gesamtanzahl der Zellen, die Ihren angegebenen Wert enthalten. Siehe Screenshot:
5. Klicken Sie auf OK, um die Meldung zu schließen. Alle Fundstellen des gesuchten Werts werden in einem neuen Arbeitsblatt aufgelistet – inklusive Arbeitsmappenname, Arbeitsblattname, Zellreferenz und des genauen Zellinhalts zur Überprüfung.
Tipp: Der aktuelle Suchbegriff ist im VBA-Code auf „KTE“ festgelegt. Sie können ihn anpassen, indem Sie die Codezeile xStrSearch = "KTE" durch Ihren gewünschten Suchbegriff ersetzen.
Hinweis: Stellen Sie vor der Ausführung sicher, dass alle relevanten Arbeitsmappen gespeichert und geschlossen sind (außer derjenigen, in der Sie Ihr Makro ausführen – diese darf geöffnet bleiben). Große Ordner mit vielen oder komplexen Excel-Dateien können etwas Zeit zur Verarbeitung benötigen. Brechen Sie die Ausführung des Makros nicht ab.
Fehlerbehebung: Sollten Fehler auftreten, stellen Sie sicher, dass alle Dateien echte Excel-Arbeitsmappen sind (nicht beschädigt oder passwortgeschützt) und Ihre Makrosicherheitseinstellungen die Ausführung von Code zulassen. Falls das Makro nicht vollständig ausgeführt wird, testen Sie es zunächst mit einer kleineren Dateimenge.
Schnelle Suche eines Werts in mehreren geöffneten Arbeitsmappen mit Kutools für Excel
Wenn Sie eine Suche über mehrere Arbeitsmappen durchführen möchten, die bereits in Ihrer Excel-Sitzung geöffnet sind, bietet Kutools für Excel einen speziellen Suchen und Ersetzen-Bereich, der den Vorgang deutlich einfacher und übersichtlicher gestaltet. Das ist besonders praktisch, um Ihre Arbeitsmappen im Handumdrehen zu durchsuchen – ganz ohne Skripte oder komplizierte Einrichtungen. Ideal für alle, die regelmäßig mit mehreren Dateien gleichzeitig arbeiten und ein benutzerfreundliches, unkompliziertes Werkzeug zur effizienten Verwaltung ihrer Suchvorgänge suchen.
1. Wechseln Sie in einer Ihrer geöffneten Arbeitsmappen zur Registerkarte Kutools und wählen Sie Navigation. Klicken Sie dann auf die Schaltfläche Suchen und Ersetzen
, um den Suchen und Ersetzen-Bereich anzuzeigen, der normalerweise links in Ihrem Excel-Fenster positioniert ist. Siehe Screenshot:
2. Geben Sie im Reiter Suchen den gesuchten Wert in das Textfeld Suchen nach ein. Wählen Sie im Dropdown-Menü unter Innerhalb die Option Alle Arbeitsmappen, um die Suche über alle geöffneten Arbeitsmappen hinweg durchzuführen. Klicken Sie anschließend auf Alle suchen, um sofort eine Liste aller übereinstimmenden Zellen samt ihren Positionen anzuzeigen. Siehe Screenshot:
Tipp: Die erweiterte Suchen-und-Ersetzen-Funktion von Kutools für Excel ermöglicht es Ihnen, Daten nicht nur in allen geöffneten Arbeitsmappen zu suchen und zu ersetzen, sondern auch gezielt in ausgewählten Arbeitsblättern, in der aktuellen Arbeitsmappe, im aktuellen Arbeitsblatt oder sogar nur innerhalb Ihrer aktuellen Auswahl. So behalten Sie stets die maßgeschneiderte Kontrolle – ganz nach Ihren Anforderungen.
Hinweis: Stellen Sie sicher, dass alle Arbeitsmappen, die Sie durchsuchen möchten, geöffnet sind, bevor Sie die Suche starten – denn dieses Tool kann nur geöffnete Dateien in Excel durchsuchen.
Fehlerbehebung: Falls einige Dateien nicht in Ihren Suchergebnissen erscheinen, überprüfen Sie, ob sie vollständig in Excel geladen und weder im Schutzmodus noch im schreibgeschützten Modus geöffnet sind – denn das könnte den Zugriff auf die Suche einschränken.
Demo: Suchwert Über mehrere geöffnete Arbeitsmappen hinweg mit Kutools für Excel
Suche eines Werts über mehrere Blätter hinweg mithilfe von Excel-Formeln
Wenn Sie mehrere bekannte Arbeitsblattnamen in Ihrer Arbeitsmappe haben und prüfen möchten, ob und wo ein bestimmter Wert in diesen Blättern vorkommt, können Sie Excel-Formeln nutzen, um dynamisch über alle diese Blätter hinweg zu suchen. Dieser Ansatz eignet sich besonders gut, wenn Ihre Suchergebnisse automatisch aktualisiert werden sollen und Ihre Liste der Arbeitsblätter entweder relativ stabil ist oder zentral in einer separaten Tabelle verwaltet wird.
Diese Methode setzt voraus, dass Sie die Namen aller zu durchsuchenden Blätter bereits kennen oder aufgelistet haben. Sie eignet sich besonders gut für automatisierte Prüfungen, Dashboards oder Situationen, in denen Sie eine Zusammenfassungsreferenz erstellen möchten, ohne jedes Mal einen vollständigen Scan durchführen zu müssen.
Vorteile: Die Ergebnisse aktualisieren sich automatisch, sobald sich die Daten ändern – ohne Skripte oder Add-Ins. Alles läuft vollständig innerhalb der Arbeitsmappe ab.
Nachteile: Nicht geeignet für Szenarien, in denen sich die Arbeitsblattnamen häufig ändern oder wenn Sie eine sehr große Anzahl von Blättern verwalten.
Beispielszenario: Angenommen, Sie haben drei Blätter mit den Namen Sheet1, Sheet2 und Sheet3. Sie möchten herausfinden, in welchem bzw. welchen Blatt/Blättern ein bestimmter Wert wie „Invoice123“ in Zelle A1 vorkommt – oder einfach prüfen, ob der Wert überhaupt in diesen Blättern enthalten ist.
Schritt 1. Angenommen, Sie haben eine Liste der Arbeitsblattnamen in D2:D4 (D2: Sheet1, D3: Sheet2, D4: Sheet3). Geben Sie den zu suchenden Wert (z. B. „Invoice123“) in E1 ein. Geben Sie dann in F2 folgende Formel ein:
=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")Schritt 2. Ziehen Sie die Formel von F2 bis F4 nach unten, um alle in D2:D4 aufgeführten Blätter zu prüfen – so erhalten Sie für jedes Blatt sofort „Gefunden“ oder „Nicht gefunden“.
So funktioniert’s: Die Formel nutzt INDIREKT, um einen Bezug auf jedes aufgelistete Arbeitsblatt zu erstellen, und ZÄHLENWENN, um zu prüfen, ob der in Zelle E1 angegebene Wert in Spalte A des jeweiligen Blatts vorhanden ist. Passen Sie den Bereich A:Aan, um stattdessen eine andere Spalte oder einen bestimmten Bereich (z. B.)A1:Z100) gezielt zu durchsuchen – falls erforderlich.
Zusätzlicher Tipp:Um die Namen der Blätter abzurufen, die den gesuchten Wert enthalten, verwenden Sie die folgende Matrixformel (in älteren Excel-Versionen mit Strg + Umschalt + Enter eingegeben oder als reguläre Formel in Microsoft 365/Excel 2021+):
=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))Dadurch wird eine kommagetrennte Liste aller Arbeitsblattnamen zurückgegeben, in denen der Wert gefunden wurde. Seien Sie vorsichtig mit INDIREKT – es funktioniert nur mit geöffneten Arbeitsmappen und ermöglicht keine Suche in geschlossenen Dateien.
Vorsichtsmaßnahmen: Wenn Arbeitsblattnamen geändert oder gelöscht werden, gibt die Formel einen #BEZUG!-Fehler zurück. Stellen Sie daher stets sicher, dass die Liste der Arbeitsblattnamen korrekt ist. Bei größeren Arbeitsmappen können Formeln mit INDIREKT die Leistung Ihrer Arbeitsmappe beeinträchtigen.
Fehlerbehebung: Sollten Fehlermeldungen erscheinen, prüfen Sie, ob alle referenzierten Arbeitsblätter vorhanden sind und ob Ihr Suchbereich korrekt festgelegt ist. Nutzen Sie für dynamische Blattlisten benannte Bereiche oder die Datenüberprüfung, um die Liste automatisch aktuell zu halten.
Verwandte Artikel:
- Wie kann man schnell Suchen und Ersetzen über mehrere Arbeitsblätter oder die gesamte Arbeitsmappe hinweg durchführen?
- Wie kann man schnell Suchen und Ersetzen in mehreren geöffneten Excel-Dateien durchführen?
- Wie findet man den größten negativen Wert (kleiner als 0) in Excel?
- Wie extrahiert man alle Duplikate aus einer Spalte in Excel?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins