Generische Formel
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Zusammenfassung
Um mehrere Arbeitsblätter in einer Arbeitsmappe nach einem Wert zu durchsuchen und eine Anzahl zurückzugeben, können Sie eine Formel verwenden, die auf den Funktionen COUNTIF und INDIRECT basiert. Mit einigen vorläufigen Einstellungen können Sie diesen Ansatz verwenden, um eine gesamte Arbeitsmappe nach einem bestimmten Wert zu durchsuchen. Im gezeigten Beispiel lautet die Formel in C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontext - Beispieldaten
Die Arbeitsmappe enthält insgesamt 4 Arbeitsblätter. Sheet1 , Sheet2 und Sheet3 enthalten jeweils 1000 zufällige Vornamen, die folgendermaßen aussehen:
Erläuterung
Der Bereich B7: B9 enthält die Blattnamen, die wir in die Suche einbeziehen möchten. Dies sind nur Textzeichenfolgen, und wir müssen einige Arbeiten ausführen, damit sie als gültige Blattreferenzen erkannt werden.
Dieser Ausdruck wird von innen nach außen verwendet, um eine vollständige Blattreferenz zu erstellen:
"'"&B7&"'!"&"1:1048576"
Die einfachen Anführungszeichen werden hinzugefügt, um Blattnamen mit Leerzeichen zuzulassen, und das Ausrufezeichen ist eine Standardsyntax für Bereiche, die einen Blattnamen enthalten. Der Text "1: 1048576" ist ein Bereich, der jede Zeile im Arbeitsblatt enthält.
Nachdem B7 ausgewertet und die Werte verkettet wurden, gibt der obige Ausdruck Folgendes zurück:
"'Sheet1'!1:1048576"
Dies geht in die INDIRECT-Funktion als 'ref_text'-Argument. INDIRECT wertet diesen Text aus und gibt eine Standardreferenz für jede Zelle in Sheet1 zurück . Dies geht in die COUNTIF-Funktion als Bereich. Die Kriterien werden als absoluter Verweis auf C4 bereitgestellt (gesperrt, damit die Formel in Spalte C kopiert werden kann).
COUNTIF gibt dann eine Anzahl aller Zellen mit einem Wert zurück, der gleich "Mary" ist, in diesem Fall 25.
Hinweis: COUNTIF unterscheidet nicht zwischen Groß- und Kleinschreibung.
Enthält vs. Gleich
Wenn Sie alle Zellen zählen möchten, die den Wert in C4 enthalten, können Sie anstelle aller Zellen, die C4 entsprechen, Platzhalter zu folgenden Kriterien hinzufügen:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Jetzt zählt COUNTIF Zellen mit der Teilzeichenfolge "John" an einer beliebigen Stelle in der Zelle.
Performance
Im Allgemeinen empfiehlt es sich nicht, einen Bereich anzugeben, der alle Arbeitsblattzellen enthält. Dies kann zu Leistungsproblemen führen, da der Bereich Millionen und Abermillionen von Zellen umfasst. In diesem Beispiel verschärft sich das Problem, da die Formel die INDIRECT-Funktion verwendet, die eine flüchtige Funktion ist. Flüchtige Funktionen werden bei jeder Änderung des Arbeitsblatts neu berechnet, sodass die Auswirkungen auf die Leistung enorm sein können.
Beschränken Sie Bereiche nach Möglichkeit auf eine sinnvolle Größe. Wenn Sie beispielsweise wissen, dass Daten nicht nach Zeile 1000 angezeigt werden, können Sie nur die ersten 1000 Zeilen wie folgt durchsuchen:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
