Zusammenfassung
Um Zeilen mit ODER-Logik zu zählen, können Sie eine Formel verwenden, die auf der Funktion SUMPRODUCT basiert. Im gezeigten Beispiel lautet die Formel in G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
Dabei werden Gruppe (B5: B15), Farbe1 (C5: C15) und Farbe2 (D5: D15) als Bereiche bezeichnet.
Erläuterung
Eines der schwierigeren Probleme in Excel besteht darin, Zeilen in einem Datensatz mit "ODER-Logik" zu zählen. Es gibt zwei grundlegende Szenarien: (1) Sie möchten Zeilen zählen, bei denen ein Wert in einer Spalte "x" ODER "y" ist. (2) Sie möchten Zeilen zählen, bei denen ein Wert "x" in einer Spalte ODER einer anderen vorhanden ist .
In diesem Beispiel besteht das Ziel darin, Zeilen zu zählen, in denen group = "a" AND Color1 ODER Color2 "red" sind. Dies bedeutet, dass wir mit Szenario 2 oben arbeiten.
Mit COUNTIFS
Möglicherweise greifen Sie zunächst nach der COUNTIFS-Funktion, die mehrere Kriterien nativ behandelt. Die COUNTIFS-Funktion verknüpft jedoch Bedingungen mit der AND-Logik, sodass alle Kriterien TRUE sein müssen, um in die Zählung aufgenommen zu werden:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Dies macht COUNTIFS nicht funktionsfähig, es sei denn, wir verwenden mehrere Instanzen von COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Übersetzung: Zähle Zeilen, in denen Gruppe "a" und Farbe1 "rot" ist + Zähle Zeilen, in denen Gruppe "a" und Farbe2 "rot" ist - Zähle Zeilen, in denen Gruppe "a" und Farbe1 "rot" und Farbe2 "ist" rot "(um Doppelzählungen zu vermeiden).
Dies funktioniert, aber Sie können sehen, dass dies eine etwas komplizierte und redundante Formel ist.
Mit boolescher Logik
Eine bessere Lösung besteht darin, die Boolesche Logik zu verwenden und das Ergebnis mit der Funktion SUMPRODUCT zu verarbeiten. (Wenn Sie einen Primer für die Boolesche Algebra benötigen, bietet dieses Video eine Einführung.) In dem gezeigten Beispiel lautet die Formel in G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
Dabei werden Gruppe (B5: B15), Farbe1 (C5: C15) und Farbe2 (D5: D15) als Bereiche bezeichnet.
Der erste Teil des Problems besteht darin, auf group = "a" zu testen, was uns so gefällt:
(group="a")
Da der Bereich B5: B15 11 Zellen enthält, gibt dieser Ausdruck ein Array von 11 TRUE- und FALSE-Werten wie folgt zurück:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Jeder TRUE repräsentiert eine Zeile, in der die Gruppe "A" ist.
Als nächstes müssen wir entweder in Spalte1 oder in Spalte2 nach dem Wert "rot" suchen. Wir tun dies mit zwei Ausdrücken, die durch Addition (+) verbunden sind, da Addition der ODER-Logik in der Booleschen Algebra entspricht:
(color1="red")+(color2="red")
Excel wertet TRUE- und FALSE-Werte während jeder mathematischen Operation automatisch als 1s und 0s aus. Das Ergebnis des obigen Ausdrucks ist also ein Array wie das folgende:
(2;0;0;1;1;0;1;0;0;0;1)
Die erste Zahl im Array ist 2, da sowohl Farbe1 als auch Farbe2 in der ersten Zeile "rot" sind. Aus den unten erläuterten Gründen müssen wir uns vor dieser Situation schützen, indem wir nach Werten suchen, die größer als Null sind:
((2;0;0;1;1;0;1;0;0;0;1))>0
Jetzt haben wir wieder ein Array von TRUE- und FALSE-Werten:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Die folgende Tabelle fasst zusammen, wie Excel die oben erläuterte Farblogik bewertet:
Zu diesem Zeitpunkt haben wir Ergebnisse aus dem Testen von Group = "a" in einem Array:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Und Ergebnisse aus dem Testen von "Rot" in Farbe1 oder Farbe2 in einem anderen Array:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Der nächste Schritt besteht darin, diese beiden Arrays mit "UND-Logik" zusammenzubringen. Dazu verwenden wir die Multiplikation (*), da die Multiplikation der UND-Logik in der Booleschen Algebra entspricht.
Nach dem Multiplizieren der beiden Arrays haben wir ein einzelnes Array von Einsen und Nullen, das direkt an die Funktion SUMPRODUCT übergeben wird:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Die Funktion SUMPRODUCT gibt als Endergebnis die Summe der Zahlen 2 zurück. Dies ist die Anzahl der Zeilen, in denen group = "a" AND Color1 OR Color2 "red" sind.
Um Doppelzählungen zu vermeiden
Wir möchten keine Zeilen doppelt zählen, in denen sowohl Farbe1 als auch Farbe2 "rot" sind. Aus diesem Grund überprüfen wir die Ergebnisse von (color1 = "red") + (color2 = "red") im folgenden Code auf Werte größer als Null:
((color1="red")+(color2="red"))>0
Ohne diese Überprüfung würde die 2 aus der ersten Zeile in den Daten im endgültigen Array angezeigt und die Formel würde fälschlicherweise 3 als endgültige Zählung zurückgeben.
Option FILTER
Eine schöne Sache an der Booleschen Logik ist, dass sie perfekt mit den neuesten Funktionen von Excel wie XLOOKUP und FILTER funktioniert. Beispielsweise kann die FILTER-Funktion genau dieselbe Logik verwenden, die oben erläutert wurde, um übereinstimmende Zeilen zu extrahieren:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Das Ergebnis von FILTER sind die beiden Zeilen, die die folgenden Kriterien erfüllen:
Wenn Sie mehr über diese neuen Funktionen erfahren möchten, haben wir eine Übersicht und ein Videotraining.
