Verwendung der Excel-Funktion SUMPRODUCT -

Zusammenfassung

Die Funktion SUMPRODUCT multipliziert Bereiche oder Arrays miteinander und gibt die Summe der Produkte zurück. Das klingt langweilig, aber SUMPRODUCT ist eine unglaublich vielseitige Funktion, mit der Sie wie COUNTIFS oder SUMIFS zählen und summieren können, jedoch mit mehr Flexibilität. Andere Funktionen können problemlos in SUMPRODUCT verwendet werden, um die Funktionalität noch weiter zu erweitern.

Zweck

Multiplizieren Sie dann die Arrays

Rückgabewert

Das Ergebnis multiplizierter und summierter Arrays

Syntax

= SUMPRODUCT (Array1, (Array2),…)

Argumente

• array1 - Das erste Array oder der erste Bereich, der multipliziert und dann hinzugefügt wird.
• array2 - (optional) Das zweite Array oder der zweite Bereich, der multipliziert und dann hinzugefügt werden soll.

Ausführung

Excel 2003

Verwendungshinweise

Die SUMPRODUCT-Funktion funktioniert mit Arrays, erfordert jedoch nicht die normale Array-Syntax (Strg + Umschalt + Eingabetaste). Der Zweck der SUMPRODUCT-Funktion besteht darin, Arrays zu multiplizieren und dann zu summieren. Wenn nur ein Array angegeben wird, summiert SUMPRODUCT einfach die Elemente im Array. Es können bis zu 30 Arrays geliefert werden.

Wenn Sie SUMPRODUCT zum ersten Mal begegnen, kann es langweilig, komplex und sogar sinnlos erscheinen. Aber SUMPRODUCT ist eine erstaunlich vielseitige Funktion mit vielen Verwendungsmöglichkeiten. Da Arrays ordnungsgemäß und ohne Beanstandung verarbeitet werden, können Sie damit Zellbereiche auf clevere und elegante Weise verarbeiten (siehe Links zu Formelbeispielen auf dieser Seite).

Um zu veranschaulichen, wie SUMPRODUCT funktioniert, finden Sie hier einige Beispiele.

SUMPRODUCT für bedingte Summen und Zählungen

Angenommen, Sie haben einige Auftragsdaten in A2: B6, mit Status in Spalte A, Umsatz in Spalte B:

	EIN	B.
1	Zustand	Der Umsatz
2	UT	75
3	CO	100
4	TX	125
5	CO	125
6	TX	150

Mit SUMPRODUCT können Sie den Gesamtumsatz für Texas ("TX") mit folgender Formel zählen:

=SUMPRODUCT(--(A2:A6="TX"))

Mit dieser Formel können Sie den Gesamtumsatz für Texas ("TX") summieren:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Hinweis: Lassen Sie sich nicht vom Doppel-Negativ verwirren. Dies ist ein häufiger Trick, der in fortgeschritteneren Excel-Formeln verwendet wird, um TRUE- und FALSE-Werte in Einsen und Nullen zu zwingen. Weitere Informationen finden Sie weiter unten…

Für das obige Summenbeispiel ist hier eine virtuelle Darstellung der beiden Arrays, wie sie zuerst von SUMPRODUCT verarbeitet wurden:

array1	array2
FALSCH	75
FALSCH	100
WAHR	125
FALSCH	125
WAHR	150

Jedes Array enthält 5 Elemente. Das erste Array enthält die TRUE / FALSE-Werte, die sich aus dem Ausdruck A2: A6 = "TX" ergeben, und das zweite Array enthält den Inhalt von B2: B6. Jedes Element im ersten Array wird mit dem entsprechenden Element im zweiten Array multipliziert. Im aktuellen Status ist das Ergebnis von SUMPRODUCT jedoch Null, da die Werte TRUE und FALSE als Null behandelt werden. Die Elemente in Array1 müssen numerisch sein - sie müssen in Einsen und Nullen "gezwungen" werden. Hier kommt das Doppel-Negativ ins Spiel.

Durch die Verwendung des doppelten Negativs - (doppelt unär, für Sie technische Typen) können wir TRUE / FALSE in die numerischen Werte Eins und Null zwingen, wie in der folgenden virtuellen Darstellung gezeigt. Die letzte Spalte "Produkt" repräsentiert das Ergebnis der Multiplikation der beiden Arrays miteinander. Das summierte Ergebnis 275 ist der Wert, den SUMPRODUCT zurückgibt.

array1		array2		Produkt
0	* *	75	=	0
0	* *	100	=	0
1	* *	125	=	125
0	* *	125	=	0
1	* *	150	=	150
Summe				275

Unter Verwendung der Syntax für geschweifte Klammern für Arrays sieht das Beispiel nach Zwang folgendermaßen aus:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150)) 

und so nach der Multiplikation:

=SUMPRODUCT((0,0,125,0,150))

Dieses Beispiel erweitert die obigen Ideen ausführlicher.

SUMPRODUCT mit anderen Funktionen

SUMPRODUCT kann andere Funktionen direkt verwenden. Möglicherweise wird SUMPRODUCT mit der LEN-Funktion zum Zählen der Gesamtzeichen in einem Bereich oder mit Funktionen wie ISBLANK, ISTEXT usw. verwendet. Dies sind normalerweise keine Array-Funktionen, aber wenn ihnen ein Bereich zugewiesen wird, wird ein "Ergebnis-Array" erstellt. Da SUMPRODUCT für die Arbeit mit Arrays entwickelt wurde, kann es Berechnungen für die Arrays direkt durchführen. Dies kann ein guter Weg sein, um Platz in einem Arbeitsblatt zu sparen, da keine "Hilfsspalte" erforderlich ist.

Angenommen, Sie haben 10 verschiedene Textwerte in A1: A10 und möchten die Gesamtzahl der Zeichen für alle 10 Werte zählen. Sie können in Spalte B eine Hilfsspalte hinzufügen, die diese Formel verwendet: LEN (A1), um die Zeichen in jeder Zelle zu berechnen. Dann könnten Sie SUM verwenden, um alle 10 Zahlen zu addieren. Mit SUMPRODUCT können Sie jedoch eine Formel wie die folgende schreiben:

=SUMPRODUCT(LEN(A1:A10))

Bei Verwendung mit einem Bereich wie A1: A10 gibt LEN ein Array mit 10 Werten zurück. Dann summiert SUMPRODUCT einfach alle Werte und gibt das Ergebnis zurück, ohne dass eine Hilfsspalte erforderlich ist.

In den folgenden Beispielen finden Sie viele andere Möglichkeiten zur Verwendung von SUMPRODUCT.

Anmerkungen:

1. SUMPRODUCT behandelt nicht numerische Elemente in Arrays als Nullen.
2. Array-Argumente müssen dieselbe Größe haben. Andernfalls generiert SUMPRODUCT einen #WERT! Fehlerwert.
3. Logische Tests in Arrays erzeugen TRUE- und FALSE-Werte. In den meisten Fällen möchten Sie diese zu Einsen und Nullen zwingen.
4. SUMPRODUCT kann häufig das Ergebnis anderer Funktionen direkt verwenden (siehe Formelbeispiele unten).