Generische Formel
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Zusammenfassung
Um Zellen zu zählen, die nicht viele verschiedene Zeichenfolgen enthalten, können Sie eine ziemlich komplexe Formel verwenden, die auf der MMULT-Funktion basiert. In dem gezeigten Beispiel lautet die Formel in F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Dabei ist "Daten" der benannte Bereich B5: B14 und "Ausschließen" der benannte Bereich D5: D7.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden
Vorwort
Diese Formel wird durch die Anforderung "enthält" kompliziert. Wenn Sie nur eine Formel benötigen, um Zellen zu zählen, die nicht * gleich * vielen Dingen sind, können Sie eine einfachere Formel verwenden, die auf der MATCH-Funktion basiert. Wenn Sie eine begrenzte Anzahl von Zeichenfolgen ausschließen möchten, können Sie die COUNTIFS-Funktion wie folgt verwenden:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Bei diesem Ansatz müssen Sie jedoch für jede auszuschließende Zeichenfolge ein neues Paar von Bereichs- / Kriterienargumenten eingeben. Im Gegensatz dazu kann die unten erläuterte Formel eine große Anzahl von Zeichenfolgen verarbeiten, um die Eingabe direkt im Arbeitsblatt auszuschließen.
Schließlich ist diese Formel komplex. Lassen Sie mich wissen, wenn Sie eine einfachere Formel vorschlagen können :)
Erläuterung
Der Kern dieser Formel ist ISNUMBER und SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Hier transponieren wir die Elemente im benannten Bereich "ausschließen" und geben das Ergebnis dann als "Text suchen" an SEARCH weiter, wobei "Daten" "innerhalb von Text" sind. Die SEARCH-Funktion gibt ein 2d-Array von TRUE- und FALSE-Werten zurück, 10 Zeilen mal 3 Spalten, wie folgt:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Für jeden Wert in "Daten" haben wir 3 Ergebnisse (eines pro Suchzeichenfolge), die entweder # VALUE-Fehler oder Zahlen sind. Zahlen stellen die Position einer gefundenen Textzeichenfolge dar, und Fehler stellen nicht gefundene Textzeichenfolgen dar. Übrigens wird die TRANSPOSE-Funktion benötigt, um das 10 x 3-Array mit vollständigen Ergebnissen zu generieren.
Dieses Array wird in ISNUMBER eingespeist, um TRUE FALSE-Werte zu erhalten, die wir mit einem doppelt negativen (-) Operator in 1s und 0s konvertieren. Das Ergebnis ist ein Array wie folgt:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
Das geht in die MMULT-Funktion als Array1. Nach den Regeln der Matrixmultiplikation muss die Anzahl der Spalten in Array1 der Anzahl der Zeilen in Array2 entsprechen. Um Array2 zu generieren , verwenden wir die ROW-Funktion wie folgt :
ROW(exclude)^0
Dies ergibt ein Array von 1s, 3 Zeilen mal 1 Spalte:
(1;1;1)
das geht in MMULT als Array2 . Nach der Array-Multiplikation haben wir ein Array, das so dimensioniert ist, dass es mit den Originaldaten übereinstimmt:
(2;1;0;0;1;1;0;0;0;2)
In diesem Array stellt jede Zahl ungleich Null einen Wert dar, bei dem mindestens eine der ausgeschlossenen Zeichenfolgen gefunden wurde. Nullen zeigen an, dass keine ausgeschlossenen Zeichenfolgen gefunden wurden. Um alle Werte ungleich Null auf 1 zu setzen, verwenden wir größer als Null:
(2;1;0;0;1;1;0;0;0;2)>0
Dadurch wird ein weiteres Array oder TRUE- und FALSE-Werte erstellt:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Unser letztes Ziel ist es, nur Textwerte zu zählen, bei denen keine ausgeschlossenen Zeichenfolgen gefunden wurden. Daher müssen wir diese Werte umkehren. Dazu subtrahieren wir das Array von 1. Dies ist ein Beispiel für eine boolesche Logik. Die mathematische Operation zwingt TRUE- und FALSE-Werte automatisch zu 1s und 0s, und wir haben endlich ein Array, um zur SUMME-Funktion zurückzukehren:
=SUM((0;0;1;1;0;0;1;1;1;0))
Die SUMME-Funktion gibt ein Endergebnis von 5 zurück.
