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

Mussten Sie jemals einen bestimmten Wert finden, der in verschiedenen Blä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, Monatsberichten oder beim Konsolidieren von Informationen aus mehreren Dateien. Das manuelle Durchsuchen jedes Blatts oder jeder Datei ist nicht nur zeitaufwendig, sondern auch fehleranfällig. In diesem Tutorial werden Sie mehrere effektive Methoden entdecken, um Daten effizient zu durchsuchen – egal, ob Sie innerhalb einer Arbeitsmappe, mehrerer Arbeitsmappen oder auf Basis von Formeln oder konsolidierten Datenlösungen suchen. Diese Ansätze decken praktische Bedürfnisse ab, die Sie bei der täglichen Excel-Arbeit oder bei Datenanalyseprojekten möglicherweise begegnen.

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

Suche eines Werts in allen Arbeitsmappen eines Ordners mit VBA

Schnelles Suchen eines Werts in mehreren geöffneten Arbeitsmappen mit Kutools für Excel

Suche eines Werts über mehrere Blätter hinweg mit Excel-Formeln

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

Excels Funktion „Suchen und Ersetzen“ ist eine grundlegende, aber effektive Möglichkeit, schnell spezifische Werte über mehrere Arbeitsblätter innerhalb derselben Arbeitsmappe hinweg 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. Die Suche über verschiedene Dateien oder geschlossene Arbeitsmappen hinweg wird nicht unterstützt, aber sie bietet eine einfache Möglichkeit für schnelle Nachschlagungen.

1. Beginnen Sie damit, die Registerkarten der gewünschten Blätter durch Halten der Strg-Taste und Anklicken jedes Arbeitsblatts einzeln in der Blattregisterleiste auszuwählen. 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 Wert, den Sie finden möchten, im 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 deren Positionen. Siehe Screenshot:

Tipp: Das Tool „Suchen und Ersetzen“ sucht nur innerhalb der ausgewählten, offenen und sichtbaren Blätter. Wenn Sie die Suche auf weitere Blätter ausweiten möchten, achten Sie darauf, diese wie oben beschrieben auszuwählen. Wenn Sie versehentlich ein Blatt übersehen haben, wiederholen Sie den Vorgang einfach, nachdem Sie es Ihrer Auswahl hinzugefügt haben.

Vorsichtsmaßnahmen: Diese Methode sucht nicht über geschlossene oder ausgeblendete Arbeitsmappen hinweg und hebt Zellen nicht automatisch hervor – sie listet lediglich die Ergebnisse zur Navigation auf.

Problembehandlung: 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 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 eingebaute Werkzeuge dies nicht direkt tun. In diesem Fall kann ein VBA-Makro (Visual Basic for Applications) den Prozess automatisieren, indem es systematisch jede Arbeitsmappe im Ordner öffnet, alle Arbeitsblätter durchsucht und aufzeichnet, wo Übereinstimmungen gefunden wurden. Dieser Ansatz ist sehr praktisch für regelmäßige Prüfungen oder das Überprü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 Dialogfeld „Ordner auswählen“ wird angezeigt, das Ihnen ermöglicht, den Ordner auszuwählen, der die Arbeitsmappen enthält, die Sie durchsuchen möchten. Siehe Screenshot:

4. Klicken Sie auf OK. Nachdem die Suche abgeschlossen ist, wird ein Meldungsfeld Sie über die Gesamtzahl der gefundenen Zellen informieren, 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 in einen beliebigen Wert ändern, nach dem Sie suchen möchten.

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 offen sein darf). Große Ordner mit vielen oder komplexen Excel-Dateien können einige Zeit zum Verarbeiten benötigen. Unterbrechen Sie das Makro während der Ausführung nicht.

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

Schnelles Suchen eines Werts in mehreren geöffneten Arbeitsmappen mit Kutools für Excel

Wenn Sie eine Suche über mehrere bereits geöffnete Arbeitsmappen in Ihrer Excel-Sitzung durchführen möchten, 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 Skripte oder komplizierte Einrichtungen zu benötigen, ideal für Benutzer, die oft mit mehreren Dateien gleichzeitig arbeiten und ein benutzerfreundliches, unkompliziertes Werkzeug zur Verwaltung von Suchvorgängen benötigen.

1. Gehen Sie in einer Ihrer geöffneten Arbeitsmappen zum Kutools Reiter und wählen Sie Navigation. Dann klicken Sie auf die Schaltfläche Suchen und Ersetzen   um den Bereich Suchen und Ersetzen zu öffnen, der normalerweise auf der linken Seite Ihres Excel-Fensters positioniert ist. Siehe Screenshot:

2. Geben Sie im Reiter Suchen den Wert, nach dem Sie suchen möchten, in das Textfeld Suchen nach ein. Wählen Sie Alle Arbeitsmappen aus dem Dropdown-Menü Innerhalb aus, um die Suche über alle geöffneten Arbeitsmappen durchzuführen. Klicken Sie dann auf Alle suchen um sofort eine Liste aller übereinstimmenden Zellen anzuzeigen, zusammen mit ihren Positionen. Siehe Screenshot:

Tipp: Die erweiterte Suchen- und Ersetzen-Funktion von Kutools für Excel ermöglicht es Ihnen, nicht nur in allen geöffneten Arbeitsmappen, sondern auch in ausgewählten Blättern, der aktiven Arbeitsmappe, dem aktuellen Arbeitsblatt oder sogar in Ihrer aktuellen Auswahl zu suchen und zu ersetzen. Dies bietet Ihnen maßgeschneiderte Kontrolle je nach Ihren Anforderungen.

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

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

Demo: Suche eines Werts in mehreren geöffneten Arbeitsmappen mit Kutools für Excel

Suche eines Werts über mehrere Blätter mithilfe von Excel-Formeln

In Situationen, in denen Sie mehrere bekannte Blattnamen 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 Überprüfungen, Dashboards oder wann immer Sie eine Zusammenfassungsreferenz erstellen möchten, ohne jedes Mal eine vollständige Suche durchzuführen.

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

Beispiel-Szenario: Angenommen, Sie haben drei Blätter namens Blatt1, Blatt2 und Blatt3. Sie möchten wissen, in welchem Blatt/ein bestimmter Wert, wie z.B. „Rechnung123“, in Zelle A1 erscheint, oder einfach überprüfen, ob der Wert überhaupt irgendwo auf diesen Blättern vorhanden ist.

Schritt 1. 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")

Schritt 2. 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 eine Referenz zu jedem aufgelisteten 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, die den Wert enthalten, abzurufen, 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 ermöglicht keine Suche in geschlossenen Dateien.

Vorsichtsmaßnahmen: Wenn Blattnamen geändert oder gelöscht werden, gibt die Formel einen #BEZUG!-Fehler zurück; überprüfen Sie immer, ob die Liste der Blattnamen korrekt ist. Für größere Arbeitsmappen können formelbasierte Ansätze mit INDIREKT die Leistung Ihrer Arbeitsmappe verlangsamen.

Problembehandlung: 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?