Wie sucht man schnell einen Wert in mehreren Tabellenblättern oder Arbeitsmappen?

Müssen Sie jemals einen bestimmten Wert finden, der in verschiedenen Tabellenblättern oder sogar über mehrere Arbeitsmappen in Excel verteilt sein könnte? Dies ist ein häufiges Szenario, insbesondere bei der Arbeit an großen Projekten, monatlichen Berichten oder beim Zusammenführen von Informationen aus mehreren Dateien. Manuell jedes Tabellenblatt oder jede Datei durchzusehen ist nicht nur zeitaufwendig, sondern auch fehleranfällig. In diesem Tutorial erfahren Sie mehrere effektive Methoden, um Daten effizient zu durchsuchen – ob innerhalb einer Arbeitsmappe, über mehrere Arbeitsmappen hinweg oder mit formelbasierten oder konsolidierten Datenauswertungen. Diese Ansätze decken praktische Bedürfnisse ab, die Sie bei Routine-Excel-Workflows oder Datenanalyseprojekten möglicherweise erleben.

Suche eines Werts in mehreren Tabellenblättern einer Arbeitsmappe mit der Funktion „Suchen und Ersetzen“.

Suche eines Werts in allen Arbeitsmappen eines Ordners mit VBA.

Schnelle Suche eines Werts in mehreren geöffneten Arbeitsmappen mit Kutools für Excel.

Suche eines Werts über mehrere Tabellenblätter mit Excel-Formeln.

Suche eines Werts in mehreren Tabellenblättern einer Arbeitsmappe mit der Funktion „Suchen und Ersetzen“.

Excels Funktion „Suchen und Ersetzen“ ist eine einfache, aber effektive Möglichkeit, schnell spezifische Werte in mehreren Arbeitsblättern innerhalb derselben Arbeitsmappe zu finden. Diese Methode ist am nützlichsten, wenn Sie wissen, welche Blätter Sie durchsuchen möchten oder wenn Ihre Daten relativ gut strukturiert sind innerhalb einer einzigen Datei. Sie unterstützt keine Suche über verschiedene Dateien oder geschlossene Arbeitsmappen hinweg, bietet aber eine unkomplizierte Lösung für schnelle Suchvorgänge.

1. Beginnen Sie damit, die Registerkarten der Tabellenblätter, die Sie in Ihre Suche einbeziehen möchten, auszuwählen, indem Sie die Strg-Taste gedrückt halten und jedes Arbeitsblatt in der Registerkartenleiste einzeln anklicken. Dadurch wird sichergestellt, dass die Suche gleichzeitig auf alle ausgewählten Blä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 dann auf die Schaltfläche „Alle suchen“. Excel zeigt sofort eine Liste aller Zellen auf den ausgewählten Blättern an, die Ihren Suchwert enthalten, zusammen mit ihren Positionen. Siehe Screenshot:

Tipp: Das Werkzeug „Suchen und Ersetzen“ durchsucht nur die ausgewählten Blätter, die offen und sichtbar sind. Wenn Sie Ihre Suche auf zusätzliche Blätter ausdehnen möchten, stellen Sie sicher, dass Sie diese wie oben beschrieben auswählen. Wenn Sie versehentlich ein Blatt übersehen haben, wiederholen Sie den Vorgang einfach, nachdem Sie es in Ihre Auswahl aufgenommen haben.

Vorsichtsmaßnahmen: Diese Methode durchsucht keine geschlossenen oder ausgeblendeten Arbeitsmappen und hebt Zellen nicht automatisch hervor – sie listet nur die Ergebnisse zur Navigation auf.

Fehlerbehebung: Wenn Sie keine erwarteten Ergebnisse sehen, überprüfen Sie Ihre Blattaufteilung und bestätigen Sie, dass Sie keine Filter oder Zellschutzmaßnahmen angewendet haben, die die Suche möglicherweise ausblenden oder einschränken könnten.

Werte mühelos über mehrere Blätter und Arbeitsmappen finden und ersetzen

Die erweiterte Funktion „Suchen und Ersetzen“ von Kutools für Excel bietet eine effiziente Möglichkeit, Werte über mehrere Blätter oder sogar über alle geöffneten Arbeitsmappen hinweg zu suchen und zu ersetzen. Mit dieser fortschrittlichen Funktion können Sie Zeit sparen und Fehler vermeiden, wenn Sie mit großen Datensätzen arbeiten, wodurch Ihre Excel-Aufgaben schneller und präziser werden.

Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt

Suche eines Werts in allen Arbeitsmappen eines Ordners mit VBA.

Wenn Sie einen bestimmten Wert in mehreren Arbeitsmappen suchen müssen, die in einem Ordner gespeichert sind – einschließlich Dateien, die Sie noch nicht geöffnet haben – können Excels integrierte Tools dies nicht direkt tun. In diesem Fall kann ein VBA-Makro (Visual Basic for Applications) den Prozess automatisieren, systematisch jede Arbeitsmappe im Ordner zu öffnen, alle Arbeitsblätter zu scannen und festzustellen, wo Übereinstimmungen gefunden werden. Dieser Ansatz ist äußerst praktisch für regelmäßige Überprüfungen oder zum Prüfen von Werten in archivierten oder Batch-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 Microsoft Visual Basic for Applications-Editor-Fenster zu starten.

2. Gehen Sie im VBA-Editor zu Einfügen > Modul, um ein neues Modul zu erstellen, und fügen Sie 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 F5-Taste oder klicken Sie auf die Schaltfläche Ausführen, um dieses Makro auszuführen. Ein Fenster „Ordner auswählen“ erscheint, in dem Sie den Ordner auswählen können, der die Arbeitsmappen enthält, die Sie durchsuchen möchten. Siehe Screenshot:

4. Klicken Sie auf OK. Nachdem die Suche abgeschlossen ist, informiert Sie ein Meldungsfeld über die Gesamtzahl der gefundenen Zellen, die Ihren angegebenen Wert enthalten. Siehe Screenshot:

5. Klicken Sie auf OK, um die Nachricht zu schließen. Alle Stellen, an denen der Wert gefunden wurde, werden in einem neuen Arbeitsblatt aufgelistet, einschließlich des Arbeitsmappennamens, des Arbeitsblattnamens, der Zellreferenz und des genauen Zellinhalts zur Überprüfung.

Tipp: Der aktuelle Suchbegriff ist im VBA als „KTE“ festgelegt. Sie können ihn anpassen, indem Sie die Zeile xStrSearch = "KTE" im Code auf den gewünschten Wert ändern.

Vorsichtsmaßnahmen: Stellen Sie vor dem Ausführen sicher, dass alle relevanten Arbeitsmappen gespeichert und geschlossen sind (außer derjenigen, in der Sie Ihr Makro ausführen, die geöffnet sein kann). Große Ordner mit vielen oder komplexen Excel-Dateien können einige Zeit zur Verarbeitung benötigen. Unterbrechen Sie das Makro während der Ausführung nicht.

Fehlerbehebung: Wenn Sie auf Fehler stoßen, überprüfen Sie, ob alle Dateien echte Excel-Arbeitsmappen sind (nicht beschädigt oder passwortgeschützt), und dass Ihre Makrosicherheitseinstellungen die Codeausführung zulassen. Wenn das Makro nicht abschließt, versuchen Sie, es zunächst auf einer kleineren Anzahl von Dateien auszuführen.

Schnelles Suchen 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 dedizierten Bereich „Suchen und Ersetzen“, der den Prozess viel einfacher und organisiert gestaltet. Dies ist besonders hilfreich, um Ihre Arbeitsmappen schnell zu durchsuchen, ohne dass Skripte oder komplizierte Setup-Schritte erforderlich sind – ideal für Benutzer, die oft mit mehreren Dateien gleichzeitig arbeiten und ein benutzerfreundliches, unkompliziertes Tool zur Verwaltung von Suchvorgängen benötigen.

1. Gehen Sie in einer Ihrer geöffneten Arbeitsmappen zum Kutools Reiter und wählen Sie Navigation. Klicken Sie dann auf die Suchen und Ersetzen Schaltfläche um den Suchen und Ersetzen Bereich aufzurufen, der normalerweise auf der linken Seite Ihres Excel-Fensters angezeigt wird. Siehe Screenshot:

2. Im Suchen Reiter geben Sie den zu suchenden Wert in das Suchen nachTextfeld ein. Wählen Sie Alle Arbeitsmappen aus dem Innerhalb Dropdown-Menü, um die Suche über alle offenen Arbeitsmappen durchzuführen. Klicken Sie dann auf Alle suchen , um sofort eine Liste aller übereinstimmenden Zellen sowie deren 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 offenen Arbeitsmappen, sondern auch in speziell ausgewählten Blättern, in der aktiven Arbeitsmappe, im aktuellen Arbeitsblatt oder sogar nur in Ihrer aktuellen Auswahl zu suchen und zu ersetzen. Dies gibt Ihnen maßgeschneiderte Kontrolle je nach Bedarf.

Vorsichtsmaßnahmen: Stellen Sie sicher, dass alle Arbeitsmappen, die Sie durchsuchen möchten, geöffnet sind, bevor Sie die Suche beginnen, da dieses Tool Dateien, die nicht in Excel geöffnet sind, nicht durchsuchen kann.

Fehlerbehebung: Wenn einige Dateien nicht in Ihren Suchergebnissen erscheinen, überprüfen Sie, ob sie vollständig in Excel geladen sind und nicht im geschützten Modus oder im Nur-Lesen-Modus sind, was den Suchzugriff einschränken könnte.

Demo: Suche nach Werten in mehreren geöffneten Arbeitsmappen mit Kutools für Excel.

Suche eines Werts über mehrere Tabellenblätter mit Excel-Formeln.

In Situationen, in denen Sie mehrere bekannte Tabellenblattnamen in Ihrer Arbeitsmappe haben und überprüfen müssen, ob und wo ein bestimmter Wert unter diesen Blättern existiert, können Sie Excel-Formeln verwenden, um dynamisch über sie zu suchen. Dieser Ansatz ist besonders geeignet, wenn Sie möchten, dass Ihre Suchergebnisse automatisch aktualisiert werden und Ihre Liste der Blätter relativ statisch ist oder in einer separaten Tabelle verwaltet wird.

Diese Methode setzt voraus, dass Sie bereits die Namen aller zu durchsuchenden Blätter kennen oder auflisten. Sie ist am effektivsten für automatisierte Prüfungen, Dashboards oder wann immer Sie eine Zusammenfassungsreferenz erstellen möchten, ohne jedes Mal eine vollständige Überprüfung durchzuführen.

Vorteile: Ergebnisse aktualisieren sich automatisch bei Datenänderungen; kein Skript oder Add-In erforderlich; alles innerhalb der Arbeitsmappe erledigt.
Nachteile: Nicht geeignet für Situationen, in denen sich Tabellenblattnamen häufig ändern oder wenn Sie eine sehr große Anzahl von Blättern haben.

Beispielszenario: Angenommen, Sie haben drei Blätter namens Blatt1, Blatt2 und Blatt3. Sie möchten wissen, in welchem Blatt(en) ein bestimmter Wert, wie „Rechnung123“, in Zelle A1 erscheint, oder einfach prüfen, ob der Wert irgendwo in diesen Blättern existiert.

Schritt1. Angenommen, Sie haben eine Liste der Blattnamen in D2:D4 (D2: Blatt1, D3: Blatt2, D4: Blatt3). Geben Sie den zu suchenden Wert (z.B. „Rechnung123“) in E1 ein. Geben Sie dann in F2 diese Formel ein:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

Schritt2. Ziehen Sie die Formel von F2 bis F4 herunter, um alle in D2:D4 aufgelisteten Blätter zu überprüfen. Dies gibt „Gefunden“ oder „Nicht Gefunden“ für jedes Blatt zurück.

So funktioniert es: Die Formel verwendet INDIREKT, um einen Verweis auf jedes aufgelistete Arbeitsblatt zu erstellen, und ZÄHLENWENN, um zu überprüfen, ob der Wert in E1 in Spalte A jedes Blatts erscheint. Passen Sie den Bereich A:A an, um eine andere Spalte oder einen bestimmten Bereich (z.B., A1:Z100) zu targetieren, falls nötig.

Zusätzlicher Tipp: Um die Namen der Blätter zu erhalten, die den Wert enthalten, verwenden Sie die folgende Array-Formel (eingeben mit Strg + Umschalt + Enter in älteren Excel-Versionen oder als normale Formel in Microsoft 365/Excel 2021+):

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

Dies gibt eine durch Kommas getrennte Liste aller Blattnamen zurück, in denen der Wert gefunden wurde. Seien Sie vorsichtig mit INDIREKT – es funktioniert nur mit offenen Arbeitsmappen und erlaubt keine Suche in geschlossenen Dateien.

Vorsichtsmaßnahmen: Wenn sich Blattnamen ändern oder gelöscht werden, gibt die Formel einen #BEZUG!-Fehler zurück; überprüfen Sie immer, ob die Liste der Blattnamen korrekt ist. Bei größeren Arbeitsmappen können INDIREKT-basierte Formeln die Leistung Ihrer Arbeitsmappe verlangsamen.

Fehlerbehebung: Wenn Sie Fehler sehen, überprüfen Sie, dass alle referenzierten Blätter existieren und dass Ihr Suchbereich korrekt ist. Für dynamische Blattlisten sollten Sie benannte Bereiche oder Datenvalidierung in Betracht ziehen, um die Blattliste automatisch zu aktualisieren.

Verwandte Artikel:

• Wie findet und ersetzt man schnell über mehrere Arbeitsblätter oder die gesamte Arbeitsmappe hinweg?
• Wie findet und ersetzt man schnell in mehreren geöffneten Excel-Dateien?
• Wie findet man den größten negativen Wert (kleiner als 0) in Excel?
• Wie extrahiert man alle Duplikate aus einer Spalte in Excel?