Autor: Beate Schmitz --- Aus Excel Standard - Gruppe:
Häufige Fragen Schnittpunktsumme - Alternative zu Summenprodukt
Autor: Beate Schmitz - Erstellt: 2005-01 - Letzte Revision: ?
"Schnittpunktssumme" - eine interessante arrayfreie Alternative zu SUMMENPRODUKT
Um Summen zu bilden unter Berücksichtigung mehrerer Kriterien haben Sie die interessante Funktion SUMMENPRODUKT kennengelernt.
SUMMENPRODUKT hat aber den Nachteil, dass es eine Arrayformel ist und somit zu den langsameren Funktionen gehört.
Rob van Gelder hat eine Formel entwickelt, die diesen Nachteil ausräumt und die ich hier vorstellen möchte.
Gehen wir zunächst von dieser Tabelle aus:
|
| |
| | A | B | C | D | E | F | | 1 | | | | | | | | 2 | | Produktdaten | Jan | Feb | Mrz | | | 3 | | Nägel | 89,76 € | 2,06 € | 19,44 € | | | 4 | | Schrauben | 48,17 € | 9,77 € | 99,04 € | | | 5 | | Muttern | 81,06 € | 65,36 € | 48,65 € | | | 6 | | | | | | | |
| |
Um den Umsatz der Nägel und Muttern im Februar herauszufinden, hätten wir bisher SUMMENPRODUKT wie folgt eingesetzt
(die Zellen, die die Bedingungen erfüllen, sind oben dunkelgelb hinterlegt):
|
| |
| | F | G | H | I | J | | 2 | | | | | | | 3 | | Umsätze von Nägel und Muttern im Februar: | entweder: | 67,42 € | |
|---|
| 4 | | oder: | 67,42 € | | | 5 | | | | | | | Formeln der Tabelle | I3 : =SUMMENPRODUKT((C2:E2="Feb")*((B3:B5="Nägel")+(B3:B5="Muttern"))*D3:D5)
I4 : =SUMMENPRODUKT((C2:E2=D2)*((B3:B5=B3)+(B3:B5=B5))*D3:D5)
|
|
|
| |
Das Gleiche können wir arrayfrei erreichen, indem wir zunächst für die einzelnen Zeilen und Spalten Namen (über
Menü/Einfügen/Namen/Namen definieren...) wie folgt definieren, wobei wir uns hier im Blatt Tabelle1 befinden:
|
| |
| | A | B | C | D | | 6 | | | | | | 7 | | Namensdefinitionen: | | | | 8 | | Nägel | =Tabelle1!$3:$3 | | | 9 | | Schrauben | =Tabelle1!$4:$4 | | | 10 | | Muttern | =Tabelle1!$5:$5 | | | 11 | | Jan | =Tabelle1!$B:$B | | | 12 | | Feb | =Tabelle1!$C:$C | | | 13 | | Mrz | =Tabelle1!$D:$D | | | 14 | | | | | |
| |
Nun reicht folgende Formel aus, um die Umsätze von Nägeln und Muttern im Februar auszugeben, dabei werden die definierten Namen
in der Formel eingesetzt. Wichtig ist die Beachtung des Leerzeichens (wird als Operator benutzt) hinter der ersten schließenden Klammer:
|
| |
| | D | E | F | G | H | | 7 | | | | | | | 8 | | Umsätze von Nägel und Muttern im Februar: | entweder: | 67,42 € | |
|---|
| 9 | | oder: | 67,42 € | | | 10 | | | | | | | Formeln der Tabelle | G8 : =SUMME((Nägel;Muttern) Feb)
G9 : =SUMME((INDIREKT($B$3);INDIREKT($B$5)) INDIREKT($D$2))
|
|
|
| |
Gerade in Dateien, die aufgrund ihres Inhalts (Formatierungen/Formeln/Objekten) drohen, schwerfällig zu laufen,
bietet sich diese Alternative zur Erleichterung an, dann lohnt sich der Aufwand der Namensdefinition.
Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben
