Wie erstelle ich diese Formel, damit sie auf Spalte C angewendet wird (und einen Booleschen Wert zurückgibt)?
Wenn B2 "Apfel" enthält, prüfen Sie, ob die Zahl in A2 in anderen A-Zellen vorhanden ist, deren Zeilenicht„Apfel“ in der Spalte B enthalten. (Vorausgesetzt, andere Zeilen haben auch „Apfel“ in der Spalte B und möglicherweise eine doppelte Nummer in der Spalte A.)
Hier sind einige Beispieldaten und das gewünschte Ergebnis in Spalte C:
Antwort1
Verwenden Sie WENN und ZÄHLENWENNS:
=IF(B2="apple",COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,"")
Zuerst prüfen wir, ob B2 "Apfel" ist
Als nächstes prüfen wir, ob es welche gibt, die Apfel sind. Dann sehen wir, ob es welche gibt, die mit A2 übereinstimmen und in Spalte B nicht "Apfel" enthalten.
Antwort2
Schnelle Antwort:
Sie können dies mithilfe von Array-Formeln tun. C2Schreiben Sie in Ihre Zelle Folgendes:
=IF(B2="apple",SUM(ISNUMBER(A:A)*(A:A=A2)*(B:B<>B2))>0,"")
Registrieren Sie es dann mit Strg+Umschalt+Eingabe als Matrixformel. Kopieren Sie es anschließend in den Rest der Spalte.
Erläuterung:
Die IFFunktion ist die normale Sortierung: Wenn in der Spalte B „Apfel“ steht, machen wir etwas, andernfalls schreiben wir einfach die leere Zeichenfolge. Der interessante Teil ist das zweite Argument – das, das uns im Fall „Apfel“ das Ergebnis liefert.
Betrachten wir nun zunächst, was Excel mit einer einfacheren Formel machen würde: SUM(A:A=A2). Innerhalb der SUMsieht Excel eine Gleichheit der Form „Bereich = Wert“. Wenn beide Seiten einfache Werte wären, würde dies zu einem Booleschen Wert ausgewertet, aber hier, weil Sie Strg+Umschalt+Eingabe verwendet haben, um Array-Formeln zu aktivieren, Excelwendet die Operation auf jedes Element auf der linken Seite einzeln an, und speichert sie im Speicher in einem temporären Array, das dies SUMgerne akzeptiert. Das Ergebnis ist also die Anzahl der Zellen in der Spalte A, die gleich sind mit A2...
Naja, fast. Das wäre der Fall, wenn Werte als 1 und Werte als 0 SUMbehandelt würden . Wenn das nicht der Fall wäre, müssten wir die Booleschen Werte in Zahlen umwandeln. Eine Möglichkeit wäre eine doppelte Negation wie folgt: . Der Operator in der eigentlichen Formel kümmert sich jedoch auch darum.TRUEFALSESUM(--(A:A=A2))*
Zurück zur eigentlichen Summe, wir haben den zusätzlichen Term ISNUMBER(A:A). Das Prinzip ist ähnlich: Dies wird wieder elementweise auf die Spalte angewendet, Ada es normalerweise nicht weiß, was mit einem Bereich zu tun ist. Dann *„multipliziert“ der Operator die beiden temporären Bereiche, die Boolesche Werte enthalten, elementweise – wendet also im Grunde die ANDOperation auf sie an – und gibt uns einen neuen Bereich von Booleschen Werten. (Dies dient hier nur dazu, sicherzustellen, dass leere Zellen nicht als gleich 0 gezählt werden, und NOT(ISBLANK(A:A))würde genauso funktionieren.)
Schließlich machen wir dasselbe mit dem zusätzlichen Bereich von Booleschen Werten, die beschreiben, ob jeder einzelne Wert in der BSpaltenichtgleich B2. Schließlich SUMwirkt dann auf den resultierenden Bereich.
Zusammenfassend haben wir Zeilen mit Zahlen in der ersten Spalte gezählt, wobei die aktuelle Zeile in Spalte übereinstimmt A, aber nicht in Spalte B. Ihre Bedingung besagt lediglich, dass die Anzahl dieser Zahlen positiv ist.
Bemerkung 1:
Wir verwenden den *Operator anstelle der ANDFunktion, da letztere standardmäßig Bereiche als Argument akzeptiert und daher einfach die temporären Bereiche aufbrauchen würde, anstatt den „Array-Modus“ zu aktivieren und eine elementweise Operation auszuführen.
Bemerkung 2:
Sie können die Spalten beliebig absolut machen, um die Formel in andere Spalten zu kopieren, (absolute) Zeilennummern zu den Bereichen hinzufügen A:Aund B:Bandere Dinge, die sich möglicherweise in derselben Spalte befinden (sowie die Leistung), ignorieren – oder sogar benannte Bereiche der beiden Spalten erstellen, wie IDsund Fruits, und dann diese Namen anstelle der Bereiche in die Formel einfügen.