✅ SUMPRODUCT Excel - Zahlenfelder multiplizieren und summieren

Inhaltsverzeichnis

Übersicht über die SUMPRODUCT-Funktion
SUMPRODUKT in Google Tabellen
SUMPRODUCT Beispiele in VBA

Beispielarbeitsbuch herunterladen

Laden Sie die Beispielarbeitsmappe herunter

Dieses Tutorial zeigt, wie Sie das Excel-SUMMENPRODUKT-Funktion im Excel-Format.

Übersicht über die SUMPRODUCT-Funktion

Die SUMPRODUCT-Funktion multipliziert Arrays von Zahlen und summiert das resultierende Array.

Um die Excel-Arbeitsblattfunktion SUMPRODUCT zu verwenden, wählen Sie eine Zelle aus und geben Sie Folgendes ein:

(Beachten Sie, wie die Formeleingaben angezeigt werden)

SUMPRODUCT-Funktion Syntax und Eingaben:

1	=SUMMENPRODUKT(Array1,Array2,Array3)

Array1 - Zahlenreihen.

Was ist die SUMPRODUCT-Funktion?

Die SUMMENPRODUKT-Funktion ist eine der leistungsfähigeren Funktionen in Excel. Der Name lässt Sie vielleicht glauben, dass er nur für grundlegende mathematische Berechnungen gedacht ist, aber er kann für so viel mehr verwendet werden.

Arrays

SUMPRODUCT erfordert Eingaben von Arrays.

Was meinen wir also zunächst mit „Array“? Ein Array ist einfach eine Gruppe von Elementen (z. B. Zahlen), die in einer bestimmten Reihenfolge angeordnet sind, genau wie ein Bereich von Zellen. Wenn Sie also die Zahlen 1, 2, 3 in den Zellen A1:A3 hätten, würde Excel dies als Array {1,2,3} lesen. Tatsächlich können Sie {1,2,3} direkt in Excel-Formeln eingeben und das Array wird erkannt.

Wir werden weiter unten mehr über Arrays sprechen, aber schauen wir uns zuerst ein einfaches Beispiel an.

Grundlegende Mathematik

Schauen wir uns ein einfaches Beispiel für SUMPRODUCT an, um den Gesamtumsatz zu berechnen.

Wir haben unsere Produkttabelle und möchten den Gesamtumsatz berechnen. Sie sind versucht, einfach eine neue Spalte hinzuzufügen, die verkaufte Menge * Preis zu nehmen und dann die neue Spalte zu summieren. Stattdessen können Sie jedoch einfach die SUMPRODUCT-Funktion verwenden. Gehen wir die Formel durch:

1	=SUMMENPRODUKT(A2:A4,B2:B4)

Die Funktion lädt die Zahlenbereiche in Arrays, multipliziert sie gegeneinander und summiert dann die Ergebnisse:

1234	=SUMMENPRODUKT({100, 50, 10}, {6, 7, 5})=SUMMENPRODUKT({100 * 6, 50 * 7, 10 * 5})=SUMMENPRODUKT({600, 350, 50}=1000

Die SUMPRODUCT Funciton konnte alle Zahlen für uns multiplizieren UND summieren.

Gewichteter Durchschnitt

Ein weiterer Fall, in dem es hilfreich ist, SUMPRODUCT zu verwenden, ist, wenn Sie einen gewichteten Durchschnitt berechnen müssen. Dies tritt am häufigsten bei Schulaufgaben auf. Betrachten wir also die folgende Tabelle.

Wir können sehen, wie viel die Quizfragen, Tests und Hausaufgaben für die Gesamtnote wert sind und wie hoch der aktuelle Durchschnitt für jedes einzelne Element ist. Die Gesamtnote können wir dann schriftlich berechnen

1	=SUMMENPRODUKT(B2:B4, C2:C4)

Unsere Funktion multipliziert erneut jedes Element in den Arrays, bevor die Summe summiert wird. Das klappt so

123	=SUMMENPRODUKT({30%, 50%, 20%}, {73%, 90%, 95%})=SUMMENPRODUKT({22%, 45%, 19%})=86 %

Mehrere Spalten

Ein anderer Ort, an dem wir SUMPRODUCT verwenden könnten, sind noch mehr Spalten, die alle miteinander multipliziert werden müssen. Schauen wir uns ein Beispiel an, bei dem wir das Volumen in Holzstücken berechnen müssen.

Anstatt eine Hilfsspalte zu erstellen, um den Gesamtverkauf für jede Zeile zu berechnen, können wir dies mit einer einzigen Formel tun. Unsere Formel wird sein

1	=SUMMENPRODUKT(B2:B5, C2:C5, D2:D5)

Die ersten Elemente jedes Arrays werden miteinander multipliziert (z. B. 4 * 2 * 1 = 8). Dann die 2. (4 * 2 * 2 = 16) und 3^rd, usw. Insgesamt ergibt dies die Reihe von Produkten, die wie {8, 16, 16, 32) aussehen. Dann wäre das Gesamtvolumen die Summe dieses Arrays, 72.

Ein Kriterium

Okay, fügen wir eine weitere Ebene der Komplexität hinzu. Wir haben gesehen, dass SUMPRODUCT mit Zahlenarrays umgehen kann, aber was ist, wenn wir nach Kriterien suchen möchten? Nun, Sie können auch Arrays für boolesche Werte erstellen (Boolesche Werte sind Werte, die WAHR oder FALSCH sind).

Nehmen wir zum Beispiel ein einfaches Array {1, 2, 3}. Erstellen wir ein entsprechendes Array, das angibt, ob jede Zahl größer als 1 ist. Dieses Array würde wie folgt aussehen: {FALSE, TRUE, TRUE}.

Dies ist in Formeln äußerst hilfreich, da wir TRUE / FALSE leicht in 1 / 0 umwandeln können. Schauen wir uns ein Beispiel an.

Anhand der folgenden Tabelle möchten wir berechnen, "Wie viele verkaufte Einheiten waren Rot?"

Wir können dies mit dieser Formel tun:

1	=SUMMENPRODUKT(A2:A4, --(B2:B4="Rot"))

"Festhalten! Was hat es mit dem doppelten Minuszeichen auf sich?“ du sagst. Erinnern Sie sich, wie ich sagte, wir könnten von True/False in 1/0 konvertieren? Wir tun dies, indem wir den Computer zwingen, eine mathematische Operation durchzuführen. In diesem Fall sagen wir: „Nimm den negativen Wert und dann den negativen wieder“. Wenn Sie das ausschreiben, wird sich unser Array wie folgt ändern:

123	{Richtig, wahr, falsch}{-1, -1, 0}{1, 1, 0}

Also, zurück zur vollständigen SUMPRODUCT-Formel, sie wird in unsere Arrays geladen und dann wie folgt multipliziert

123	=SUMMENPRODUKT({100, 50, 10}, {1, 1, 0})=SUMMENPRODUKT({100, 50, 0})=150

Beachten Sie, wie die 3^rd item wurde zu einer 0, weil alles, was mit 0 multipliziert wird, zu Null wird.

Mehrere Kriterien

Wir können bis zu 255 Arrays in unsere Funktion laden, also können wir sicherlich mehr Kriterien laden. Schauen wir uns diese größere Tabelle an, in der wir den verkauften Monat hinzugefügt haben.

Wenn wir wissen wollen, wie viele verkaufte Artikel rot waren und im Monat Februar waren, könnten wir unsere Formel schreiben wie

1	=SUMMENPRODUKT(A2:A4, --(B2:B4="Rot"), --(C2:C4="Feb"))

Der Computer würde dann unsere Arrays auswerten und vervielfachen. Wir haben bereits behandelt, wie True/False-Arrays in 1/0 geändert werden, daher überspringe ich diesen Schritt vorerst.

123	=SUMMENPRODUKT({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=SUMMENPRODUKT({0, 50, 0})=50

In unserem Beispiel hatten wir nur eine Zeile, die allen Kriterien entsprach, aber bei echten Daten mussten Sie möglicherweise mehrere Zeilen addieren.

Komplexe Kriterien

Okay, bis zu diesem Punkt werden Sie vielleicht nicht beeindruckt sein, da alle unsere Beispiele mit anderen Funktionen wie SUMIF oder COUNTIF hätten erstellt werden können. Jetzt machen wir etwas mit diesen anderen Funktionen kippen tun. Zuvor hatte unsere Spalte "Monat" die tatsächlichen Namen der Monate. Was wäre, wenn es stattdessen Daten hätte?

Wir können jetzt kein SUMIF erstellen, weil SUMIF die von uns benötigten Kriterien nicht verarbeiten kann. SUMPRODUCT kann jedoch damit umgehen, dass wir das Array manipulieren und einen tieferen Test durchführen. Wir haben bereits Arrays manipuliert, als wir True/False in 1/0 übersetzt haben. Wir werden dieses Array mit der MONTH-Funktion manipulieren. Hier ist die vollständige Formel, die wir verwenden werden

1	=SUMMENPRODUKT(A2:A4, --(B2:B4="Rot"), --(MONAT(C2:C4)=2))

Schauen wir uns die 3 an^rd näher anordnen. Zuerst extrahiert unsere Formel die Monatsnummer aus jedem Datum in C2:C4. Dadurch erhalten wir {1, 2, 2}. Als nächstes prüfen wir, ob dieser Wert gleich 2 ist. Jetzt sieht unser Array wie {False, True, True} aus. Wir machen wieder das doppelte Minus und haben {0, 1, 1}. Wir befinden uns jetzt wieder an einem ähnlichen Punkt wie in Beispiel 3, und unsere Formel wird uns sagen können, dass im Februar 50 rote Einheiten verkauft wurden.

Doppeltes Minus vs. Multiplizieren

Wenn Sie die SUMPRODUCT-Funktion schon einmal gesehen haben, ist Ihnen möglicherweise eine etwas andere Schreibweise aufgefallen. Anstatt ein doppeltes Minus zu verwenden, können Sie schreiben

1	=SUMMENPRODUKT(A2:A4(B2:B4="Rot")(MONAT(C2:C4)=2))

Die Formel funktioniert immer noch genauso, wir teilen dem Computer nur manuell mit, dass wir die Arrays multiplizieren möchten. SUMPRODUCT wollte dies sowieso tun, also ändert sich nichts an der mathematischen Funktionsweise. Das Ausführen der mathematischen Operation wandelt unser Wahr/Falsch in 1/0 um. Warum also der Unterschied?

Meistens ist es nicht so wichtig, und es kommt auf die Vorlieben des Benutzers an. Es gibt jedoch mindestens einen Fall, in dem eine Multiplikation erforderlich ist.

Wenn Sie SUMPRODUCT verwenden, erwartet der Computer, dass alle Argumente (Array1, Array2 usw.) dieselbe Größe haben. Dies bedeutet, dass sie die gleiche Anzahl von Zeilen oder Spalten haben. Sie können jedoch mit SUMPRODUCT eine sogenannte zweidimensionale Array-Berechnung durchführen, die wir im nächsten Beispiel sehen werden. Wenn Sie dies tun, haben die Arrays unterschiedliche Größen, daher müssen wir die Überprüfung auf „alle gleich groß“ umgehen.

Zwei Dimensionen

Bei allen vorherigen Beispielen gingen unsere Arrays in die gleiche Richtung. SUMPRODUCT kann Dinge in zwei Richtungen verarbeiten, wie wir in der nächsten Tabelle sehen werden.

Hier ist unsere Tabelle der verkauften Einheiten, aber die Daten sind neu angeordnet, wo die Kategorien oben stehen. Wenn wir herausfinden möchten, wie viele Artikel Rot und in Kategorie A waren, können wir schreiben

1	=SUMMENPRODUKT((A2:A4="Rot")(B1:C1="A")B2:C4)

Was geht hier vor sich?? Es stellt sich heraus, dass wir in zwei verschiedene Richtungen multiplizieren werden. Dies zu visualisieren ist mit nur einem geschriebenen Satz schwieriger, daher haben wir ein paar Bilder, die uns helfen. Zuerst multiplizieren sich unsere Zeilenkriterien (ist es Rot?) über jede Zeile im Array.

1	=SUMMENPRODUKT((A2:A4="ROT")*B2:C4)

Als nächstes werden die Spaltenkriterien (ist es Kategorie A?)

1	=SUMMENPRODUKT((A2:A4="Rot")(B1:C1="A")B2:C4)

Nachdem diese beiden Kriterien ihre Arbeit getan haben, sind nur noch die 5 und 10 übrig, die nicht Null sind. SUMPRODUCT gibt uns dann die Gesamtsumme von 15 als unsere Antwort.

Erinnern Sie sich, wie wir darüber gesprochen haben, dass die Arrays die gleiche Größe haben müssen, es sei denn, Sie arbeiten mit zwei Dimensionen? Das war teilweise richtig. Betrachtet noch einmal die Arrays, die wir in unserer Formel verwendet haben. Die Höhe von zwei unserer Arrays ist gleich, und die Breite von zwei unserer Arrays sind gleich. Sie müssen also immer noch sicherstellen, dass die Dinge richtig ausgerichtet sind, aber Sie können dies in verschiedenen Dimensionen tun.

Zweidimensional und komplex

Oft werden uns Daten präsentiert, die nicht das beste Layout haben, das für unsere Formeln geeignet ist. Wir könnten versuchen, es manuell neu anzuordnen, oder wir können mit unseren Formeln klüger sein. Betrachten wir die folgende Tabelle.

Hier haben wir die Daten für unsere Artikel und Verkäufe für jeden Monat zusammengemischt. Wie würden wir herausfinden, wie viele Artikel Bob das ganze Jahr über verkauft hat?

Dazu verwenden wir zwei zusätzliche Funktionen: SEARCH und ISNUMBER. Die SEARCH-Funktion lässt uns nach unserem Schlüsselwort „items“ in den Header-Zellen suchen. Die Ausgabe dieser Funktion erfolgt entweder durch eine Zahl oder einen Fehler (wenn das Schlüsselwort nicht gefunden wird). Dann verwenden wir die ISNUMBER zum Konvertieren das Ausgabe in unsere booleschen Werte. Unsere Formel wird wie folgt aussehen.

Sie sollten mit dem ersten Array inzwischen ziemlich vertraut sein. Es wird eine Ausgabe wie {0, 1, 0, 1} erstellt. Das nächste Kriterien-Array, über das wir gerade gesprochen haben. Es wird eine Zahl für alle Zellen mit „Items“ darin erstellt und ein Fehler für die anderen {5, #N/A!, 5, #N/A!}. Die ISNUMBER wandelt dies dann in Boolean {True, False, True, False} um. Wenn wir dann multiplizieren, werden nur die Werte aus der ersten und dritten Spalte beibehalten. Nachdem sich alle Arrays miteinander multipliziert haben, sind die einzigen Zahlen ungleich Null die hier hervorgehobenen:

1	=SUMMENPRODUKT((A2:A5="Bob")(ISNUMBER(SUCHE("Artikel",B1:E1))B2:E5))

Das SUMMENPRODUKT addiert diese dann alle und wir erhalten unser Endergebnis von 29.

SUMPRODUKT Oder

Es gibt viele Situationen, in denen wir Werte summieren möchten, wenn unsere Kriterienspalte einen Wert ODER einen anderen Wert hat. Dies können Sie in SUMPRODUCT erreichen, indem Sie zwei Kriterien-Arrays gegeneinander addieren.

In diesem Beispiel möchten wir die verkauften Einheiten für Rot und Blau addieren.

Unsere Formel wird so aussehen

1	=SUMMENPRODUKT(A2:A7, (B2:B7="Rot")+(B2:B7="Blau"))

Schauen wir uns das rote Kriterien-Array an. Es erzeugt ein Array, das wie folgt aussieht: {1, 1, 0, 0, 0, 0}. Das blaue Kriterien-Array sieht aus wie {0, 0, 1, 0, 1, 0}. Wenn Sie sie zusammenfügen, sieht das neue Array wie {1, 1, 1, 0, 1, 0} aus. Wir können sehen, wie die beiden Arrays zu einem einzigen Kriterien-Array zusammengefügt wurden. Die Funktion multipliziert das dann mit unserem ersten Array und wir erhalten {100, 50, 10, 0, 75, 0}. Beachten Sie, dass die Werte für Grün auf Null gesetzt wurden. Der letzte Schritt des SUMMENPRODUKTS besteht darin, alle Zahlen zusammenzuzählen, um unsere Lösung von 235 zu erhalten.

Ein Wort der Vorsicht hier. Seien Sie vorsichtig, wenn sich die Kriterienarrays nicht gegenseitig ausschließen. In unserem Beispiel könnten die Werte in Spalte B entweder Rot oder Blau sein, aber wir wussten, dass es niemals beides sein konnte. Überlegen Sie, ob wir diese Formel geschrieben hätten:

1	=SUMMENPRODUKT(A2:A7, (A2:A7>=50)+(B2:B7="Blau"))

Unsere Absicht ist es, blaue Artikel zu finden, die verkauft wurden oder in einer Menge von mehr als 50 waren. Diese Bedingungen sind jedoch nicht ausschließlich, da eine einzelne Zeile in Spalte A beide über 50 sein kann und blau sein. Dies würde dazu führen, dass das erste Kriterienarray wie {1, 1, 0, 1, 1, 0} aussieht, während das zweite Kriterienarray {0, 0, 1, 0, 1, 0} ist. Wenn man sie zusammenzählt, erhält man {1, 1, 1, 1, 2, 0}. Siehst du, wie wir jetzt eine 2 drin haben? Wenn es allein gelassen würde, würde das SUMPRODUCT den Wert in dieser Zeile verdoppeln, die 75 in eine 150 ändern, und wir würden das falsche Ergebnis erhalten. Um dies zu korrigieren, platzieren wir eine Prüfung der äußeren Kriterien auf unserem Array, wie folgt:

1	=SUMMENPRODUKT(A2:A7, --((A2:A7>=50)+(B2:B7="Blau")>0))

Nachdem die beiden inneren Kriterien-Arrays addiert wurden, prüfen wir, ob das Ergebnis größer als 0 ist. Dadurch wird die 2, die wir zuvor hatten, entfernt und stattdessen haben wir ein Array wie {1, 1, 1 , 1, 1, 0}, die das korrekte Ergebnis liefert.

SUMMENPRODUKT Exakt

Bei den meisten Funktionen in Excel muss die Groß-/Kleinschreibung nicht beachtet werden, aber manchmal müssen wir in der Lage sein, eine Suche unter Berücksichtigung der Groß-/Kleinschreibung durchzuführen. Wenn das gewünschte Ergebnis numerisch ist, können wir dies erreichen, indem wir EXACT in der SUMPRODUCT-Funktion verwenden. Betrachten Sie die folgende Tabelle:

Wir wollen die Punktzahl für Item „ABC123“ finden. Normalerweise vergleicht die EXACT-Funktion zwei Elemente und gibt eine boolesche Ausgabe zurück, die angibt, ob die beiden Elemente Exakt das gleiche. Da wir uns jedoch in einem SUMPRODUCT befinden, weiß unser Computer, dass wir es mit Arrays zu tun haben und kann ein Element mit jedem Element in einem Array vergleichen. Unsere Formel wird so aussehen

1	=SUMMENPRODUKT(--GENAU("ABC123", A2:A5), B2:B5)

Die EXACT-Funktion überprüft dann jedes Element in A2:A5, um zu sehen, ob es mit Wert und Groß-/Kleinschreibung übereinstimmt. Dies erzeugt ein Array, das wie {0, 1, 0, 0} aussieht. Bei Multiplikation mit B2:B5 wird das Array zu {0, 2, 0, 0}. Nach der letzten Summation erhalten wir unsere Lösung von 2.