Excel

SUMMENPRODUKT mit IF

Sumproduct With If

Excel-Formel: SUMMENPRODUKT mit IFAllgemeine Formel |_+_| Zusammenfassung

Um Ergebnisse von SUMPRODUCT nach bestimmten Kriterien zu filtern, können Sie einfache logische Ausdrücke direkt auf Arrays in der Funktion anwenden. anstatt die IF-Funktion zu verwenden . Im gezeigten Beispiel lauten die Formeln in H5:H7:





= SUMPRODUCT (expression,range)

wo die folgenden benannte Bereiche sind festgelegt:

 
= SUMPRODUCT (--(color='red'),quantity,price) = SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price) = SUMPRODUCT (--(state='co'),--(color='blue'),quantity,price)

Wenn Sie benannte Bereiche lieber vermeiden möchten, verwenden Sie die oben eingegebenen Bereiche als absolute Referenzen . Die logischen Ausdrücke in H6 und H7 können wie unten erläutert kombiniert werden.





Erläuterung

Dieses Beispiel veranschaulicht eine der wichtigsten Stärken der SUMPRODUCT-Funktion – die Möglichkeit, Daten mit einfachen logischen Ausdrücken anstelle der IF-Funktion zu filtern. Im Inneren von SUMPRODUCT, dem ersten Array ist ein logischer Ausdruck, um nach der Farbe 'Rot' zu filtern:

 
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14

Dies führt zu einem Array oder TRUE FALSE-Werten, die mit dem . in Einsen und Nullen gezwungen werden Doppel negativ (--) Betrieb. Das Ergebnis ist dieses Array:



 
--(color='red')

Beachten Sie, dass das Array 10 Werte enthält, einen für jede Zeile. Eine Eins zeigt eine Reihe an, in der die Farbe „Rot“ ist, und eine Null zeigt eine Reihe mit einer anderen Farbe an.

Als nächstes haben wir zwei weitere Arrays: eines für die Menge und eines für den Preis. Zusammen mit diesen Ergebnissen aus dem ersten Array haben wir:

 
{1010001000}

Wenn wir die Arrays erweitern, haben wir:

 
= SUMPRODUCT ({1010001000},quantity,price)

Das Kernverhalten von SUMPRODUCT besteht darin, Arrays zu multiplizieren und dann zu summieren. Da wir mit drei Arrays arbeiten, können wir die Operation wie in der Tabelle unten gezeigt visualisieren, wobei die Ergebnisspalte das Ergebnis der Multiplikation ist Array1 * Array2 * Array3 :

Array1 Array2 Array3 Ergebnis
1 10 fünfzehn 150
0 6 18 0
1 14 fünfzehn 210
0 9 16 0
0 elf 18 0
0 10 18 0
1 8 fünfzehn 120
0 9 16 0
0 elf 18 0
0 10 16 0

Notiz Array1 arbeitet als Filter – Nullwerte hier 'zero out'-Werte in Zeilen, in denen die Farbe nicht 'rot' ist. Wenn wir die Ergebnisse in SUMPRODUCT zurückgeben, haben wir:

So filtern Sie Daten in Excel mithilfe einer Formel
 
= SUMPRODUCT ({1010001000},{1061491110891110},{15181516181815161816})

Was ein Endergebnis von 480 zurückgibt.

Hinzufügen zusätzlicher Kriterien

Sie können Kriterien erweitern, indem Sie einen weiteren logischen Ausdruck hinzufügen. Um beispielsweise den Gesamtumsatz zu ermitteln, bei dem die Farbe 'Rot' und der Status 'TX' ist, enthält H6:

 
= SUMPRODUCT ({1500210000120000})

Hinweis: Bei SUMPRODUCT wird die Groß-/Kleinschreibung nicht beachtet.

Vereinfachen mit einem einzigen Array

Excel-Profis vereinfachen oft die Syntax in SUMPRODUCT ein wenig, indem sie Arrays direkt darin multiplizieren Array1 so was:

 
= SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price)

Dies funktioniert, weil die mathematische Operation (Multiplikation) die TRUE- und FALSE-Werte der ersten beiden Ausdrücke automatisch in Einsen und Nullen umwandelt.

Autor Dave Bruns


^