Kriterienbasiertes Zählen in einer gefilterten Spalte (Microsoft Excel)

Marty hat ein großes Arbeitsblatt, in dem alle Mitarbeiter (Vergangenheit und Gegenwart) seines Unternehmens aufgelistet sind. Das Arbeitsblatt enthält verschiedene Informationen zu jedem Mitarbeiter, z. B. Name, Adresse, Abteilung, Geschlecht, Status usw.

Marty filtert die Daten häufig, um seine Anforderungen zu erfüllen. Er möchte einen Weg finden, um den Prozentsatz der männlichen und weiblichen Mitarbeiter zu bestimmen, basierend darauf, welche Zeilen nach dem Filtern angezeigt werden. Die SUBTOTAL-Funktion kann an einer gefilterten Spalte arbeiten, um verschiedene Zählungen bereitzustellen, erlaubt ihm jedoch nicht, Zählungen basierend auf dem Inhalt („M“ oder „F“) der gefilterten Spalte zu bestimmen.

Ein Ansatz besteht darin, eine PivotTable zu verwenden, um die Prozentsätze zu bestimmen.

PivotTables sind relativ einfach zu verwenden, insbesondere um eine einzelne Frage wie diese zu beantworten. Sie sind jedoch nicht besonders gut darin, Ihnen detaillierte Informationen zu Ihren Mitarbeitern zu ermöglichen. Sie können die Gesamtantwort auf die männliche / weibliche Frage sehen, aber Sie können nicht gleichzeitig die Details zu diesen Mitarbeitern sehen. Daher möchte ich mich darauf konzentrieren, bei der Beantwortung von Martys Problem gerade Formeln zu verwenden.

Das Erstellen einer Formel, um die gewünschten Prozentsätze zu erhalten, ist schwieriger als es zunächst erscheint. Es ist beispielsweise einfach, die Anzahl zu bestimmen, wenn alle Mitarbeiterdatensätze sichtbar sind. Sie können beispielsweise einfach so etwas verwenden, vorausgesetzt, das Geschlecht befindet sich in Spalte C, um zu bestimmen, wie viel Prozent der Datensätze für männliche Mitarbeiter bestimmt sind:

Das Problem besteht darin, dass Sie die Datensätze nach einer anderen Spalte als Spalte C filtern. Sie können sie beispielsweise nach den in der Statusspalte enthaltenen Elementen filtern. Die obige Formel gibt Ihnen weiterhin einen Prozentsatz basierend auf allen Datensätzen in der Spalte Geschlecht, nicht nur denjenigen, die derzeit aufgrund der Filterung sichtbar sind.

Sie könnten an diesem Punkt denken, dass die SUBTOTAL-Funktion möglicherweise funktioniert, aber Marty hat herausgefunden, dass dies nicht der Fall ist. Wenn sich das Geschlecht in Spalte C befindet, können Sie am Ende von Spalte C Folgendes einfügen:

Dies funktioniert jedoch nicht für die gewünschten Anforderungen. Das Hauptproblem besteht darin, dass Sie mit der Funktion SUBTOTAL angeben können, welche Datensätze sichtbar sind. Es wird jedoch nicht unterschieden, ob diese Datensätze in Spalte C „M“ oder „F“ enthalten. Das sekundäre Problem besteht darin, dass COUNTA alle Datensätze zählt, nicht nur die angezeigten. Daher gibt die Formel nicht den Prozentsatz der angezeigten Datensätze an, die „M“ oder „F“

enthalten Stattdessen wird ein Prozentsatz der Anzahl der Datensätze aus der Gesamtzahl der Datensätze angezeigt.

Sie können versuchen, eine Hilfsspalte zu verwenden, wenn Sie möchten. Fügen Sie einfach eine Spalte hinzu, um den Status „M“ oder „F“ jedes Mitarbeiters als 1 oder 0 darzustellen.

Dies könnte mit einer einfachen Formel wie der folgenden geschehen:

Diese Formel setzt wiederum voraus, dass die Spalte Geschlecht C ist. Angenommen, diese Formel befindet sich in Spalte X (Ihrer Hilfsspalte), könnten Sie dann auf diese Weise zwei SUBTOTAL-Funktionen verwenden:

Bei der ersten Verwendung von SUBTOTAL erhalten Sie die Anzahl der Zeilen, die „M“

enthalten und die zweite Verwendung von SUBTOTAL gibt Ihnen die Gesamtzahl der sichtbaren Zeilen. Das Ergebnis der Formel ist ein Prozentsatz männlicher Arbeitnehmer, deren Aufzeichnungen sichtbar sind. Sie können den Prozentsatz der Arbeitnehmerinnen bestimmen, indem Sie das männliche Ergebnis von 1 abziehen.

Wenn Sie keine Hilfsspalte verwenden können (oder keine Hilfsspalte verwenden möchten), ist es etwas schwieriger, die Mängel der zuvor beschriebenen Ansätze zu umgehen. Es braucht eine Formel, die etwas komplizierter ist. Das Folgende ist eine Formel, die funktionieren wird, aber ich habe die einzelne Formel in vier Zeilen aufgeteilt, um die Erklärung zu vereinfachen.

Ich werde sehr detailliert darauf eingehen, wie diese Formel funktioniert. Nehmen Sie sich also ein wenig Zeit für mich – dies wird eine Weile dauern. Schauen wir uns zunächst diesen Teil der Formel an, der sich über das Ende der ersten Zeile und die Gesamtheit der zweiten Zeile erstreckt:

Die Verwendung der OFFSET-Funktion beruht hier auf 3 Parametern. Der erste entspricht einem Startpunkt für die Berechnung der zurückgegebenen Referenz (in diesem Fall ist der Startpunkt C2: C9999). Der zweite Parameter ist die Anzahl der Zeilen, die vom Anfang des im ersten Parameter definierten Bereichs versetzt werden sollen. In diesem Fall werden die zu versetzenden Zeilen definiert, indem die niedrigste Zeilennummer im Bereich (die immer den Wert 2 zurückgibt) von der tatsächlich analysierten Zeile subtrahiert wird. Wenn die zu analysierende Zeile beispielsweise Zeile 10 ist, ergibt das Subtrahieren von 2 (der Startzeile) davon einen Versatz von 8 Zeilen vom Anfang des im ersten Parameter angegebenen Bereichs.

Der dritte Parameter ist leer, daher ist der Standardwert 0. Dies ist die Anzahl der Spalten, die von der ersten Spalte in dem im ersten Parameter angegebenen Bereich versetzt werden sollen. Schließlich ist der vierte Parameter die Nummer 1, die angibt, dass OFFSET einen Bereich zurückgeben soll, der nur 1 Zelle hoch ist.

Unter dem Strich ist dieser gesamte Teil der Formel enthalten, sodass ein Verweis auf eine einzelne Zelle in der zu analysierenden Spalte zurückgegeben wird. Zur Erklärung in dieser Formel nennen wir die zurückgegebene Datei „SingleCell“. Wenn wir dies in die ursprüngliche Formel einfügen, erhalten wir Folgendes:

Die erste SUBTOTAL-Funktion gibt dann das COUNTA-Ergebnis (angezeigt durch den Wert 3, der für den ersten Parameter verwendet wird) für die einzelne Zelle zurück. Der Effekt ist, dass SUBTOTAL entweder eine 0 oder eine 1 zurückgibt, je nachdem, ob die Zelle sichtbar ist oder nicht. (Wenn die Zelle aus den Ergebnissen herausgefiltert wird, wird 0 zurückgegeben. Wenn sie nicht herausgefiltert wird – es ist sichtbar -, wird 1 zurückgegeben.)

Der nächste Teil der Formel basiert auf den Funktionen ISNUMBER und SEARCH. Dieser Teil des Codes gibt entweder eine 0 oder eine 1 zurück, je nachdem, ob die Zelle den Buchstaben „M“ enthält oder nicht. Am Ende läuft es also darauf hinaus:

Im Fall dieser einzelnen Zeile würde SUMPRODUCT 0 zurückgeben, was bedeutet, dass die Zeile in der Gesamtanzahl „nicht zählt“. Da SUMPRODUCT eine Array-basierte Funktion ist, berechnet es ein Produkt basierend auf der Multiplikation jeder Zeile im ursprünglichen Bereich einzeln. Somit wird eine Anzahl aller Zeilen bestimmt, die die beiden Bedingungen erfüllen: Die Zeile ist sichtbar und die Zeile enthält den Buchstaben „M“.

Dies wird schließlich durch das Ergebnis der endgültigen SUBTOTAL-Funktion geteilt, die das COUNTA-Ergebnis der Anzahl der sichtbaren Zeilen ist. Das Endergebnis ist der Prozentsatz der Anzahl der sichtbaren Zeilen, in denen das Zeichen „M“ in Spalte C sichtbar ist – das genaue Ergebnis, das Marty wollte.

Um den Prozentsatz der Frauen in den sichtbaren Zeilen zu ermitteln, müssen Sie lediglich ändern, wonach gesucht wird: Ändern Sie in der Formel „M“ in „F“, und es sollte Ihnen gut gehen.

Es gibt jedoch eine Einschränkung, die erwähnt werden sollte. Die SEARCH-Funktion unterscheidet nicht zwischen Groß- und Kleinschreibung. Wenn Sie also „Männlich“ anstelle von „M“ und „Weiblich“ anstelle von „F“ in Ihrer Geschlechtsspalte (Spalte C) verwenden, stimmt die Suche nach „Männlich“ in der Formel mit jeder einzelnen Zeile überein, da Zellen, die “ Weiblich „enthält die Zeichen“ männlich „. Daher ist es am besten, bei „M“ und „F“ zu bleiben. Wenn Sie „Männlich“ und „Weiblich“ verwenden müssen, verwenden Sie einfach „Weiblich“ in der Formel und berechnen Sie den Prozentsatz der männlichen Datensätze mit 1 Minus der weibliche Prozentsatz.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der WordTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13550) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.