Beitrag aus SmartTools Excel Weekly
Spaltenwerte je nach Kriterium multiplizieren und daraus die Summe bilden
Excel 365 2024 2021 2019 2016 2013
FRAGE Ich habe eine umfangreiche Statistik, in der ich bei einer Auswertung nicht weiterkomme. Vielleicht können Sie mir helfen: Generell möchte ich die Werte aus Spalte T mit denen in Spalte AI multiplizieren. Das soll aber nur passieren, wenn in Spalte N ein Textcode steht, der den Buchstaben "z" enthält. Genau da ist schon das erste Problem, denn ich schaffe es zwar zu prüfen, ob in Spalte N ein einzelnes "z" steht, nicht aber, ob dort ein "z" enthalten ist – wie etwa bei einem Textcode wie "bz" oder "aaz".
Außerdem kann es passieren, dass in Spalte T ein Wert fehlt oder mit 0 erfasst wurde. In diesem Fall soll der Wert in Spalte AI mit dem Wert aus Spalte J multipliziert werden. Natürlich auch unter der Prämisse, dass in Spalte N ein "z" vorhanden ist.
Am Ende möchte ich die Summe aus den Ergebnissen bilden. Schön wäre es, wenn sich die Summe in einer einzigen Formel berechnen ließe. Akzeptabel wäre es aber auch, wenn die Multiplikation in einer Hilfsspalte stattfinden würde und ich dann eine einfache Summe aus diesen Ergebnissen bilden müsste. Haben Sie einen entsprechenden Formelansatz?
I. Mair
ANTWORT Bei einer so komplexen Berechnung wäre es vermutlich sinnvoll, eine Hilfsspalte einzufügen, in der zunächst nur die passenden Werte multipliziert werden. Daran wäre erkennbar, aus welchen Einzelwerten sich die Gesamtsumme zusammensetzt. Es wäre aber auch möglich, die Gesamtsumme ohne Hilfsspalte in einer einzigen Formel zu berechnen. In älteren Excel-Versionen müssten Sie dafür eine Arrayformel verwenden.
Da alle Formeln auf derselben Rechenlogik basieren, beginnen wir mit den Formeln für eine Hilfsspalte. Später stellen wir Ihnen die (Array-)Formel vor, die gleich die Gesamtsumme berechnet.
Für das folgende Beispiel gehen wir von dem von Ihnen skizzierten Tabellenaufbau aus: In Spalte N stehen Textcodes, die den Buchstaben "z" enthalten können. Wenn das der Fall ist, sollen die Werte aus Spalte T und Spalte AI multipliziert werden; - es sei denn, in Spalte T steht kein Wert. Dann soll der Wert aus Spalte AI mit dem Wert in Spalte J multipliziert werden.
Das Ergebnis für Zeile 2 erhalten Sie mit folgender Formel, die Sie zum Beispiel in Zelle AJ2 eingeben können:
=WENN(ISTFEHLER(FINDEN("z";N2));"";AI2*WENN(T2=0;
J2;T2))
Die Formel prüft mit der Funktion FINDEN, ob ein "z" in Zelle N2 enthalten ist. Sollte das nicht der Fall sein, gibt die Funktion einen Fehler zurück, was die Formel mit der Funktion ISTFEHLER abfängt. Da N2 dann kein "z" enthält, liefert die Formel aufgrund der WENN-Abfrage einfach eine leere Zeichenfolge – also gar kein Ergebnis.
Hinweis: Die Funktion FINDEN unterscheidet nach Groß- und Kleinschreibung. Wenn es für Ihre Berechnung egal ist, ob Zelle N2 ein kleines "z" oder ein großes "Z" enthält, ersetzen Sie FINDEN durch SUCHEN. Die Funktion SUCHEN differenziert nicht zwischen Groß- oder Kleinschreibung.
Falls in N2 ein "z" enthalten ist und die Funktion FINDEN keinen Fehler zurückgibt, wird der Formelteil "AI2*WENN(T2=0;J2;T2)" ausgeführt. Das bedeutet: Wenn in T2 eine 0 steht (was auch zutrifft, wenn T2 leer ist), dann wird der Wert aus AI2 mit dem Wert in J2 multipliziert. Wenn T2 etwas anderes als 0 enthält, wird der Wert aus AI2 mit dem Wert in T2 multipliziert.
Die Formel in AJ2 können Sie nach unten bis zum Ende Ihrer Statistik kopieren, um für jede Zeile ein Multiplikationsergebnis zu erhalten. Die Summe dieser Werte ist das von Ihnen gesuchte Gesamtergebnis.
Wenn Sie auf die Hilfsspalte verzichten wollen oder müssen, können Sie die Summe der Multiplikationen auch in einer einzigen Formel berechnen lassen. Dazu ersetzen Sie in der oben genannten Formel die Bezüge auf einzelne Zellen durch Bezüge auf entsprechende Zellbereiche. Außerdem umgeben Sie den Ausdruck mit einer SUMME-Funktion. In Zelle AL2 Ihrer Tabelle könnten Sie dafür folgende Formel eingeben:
=SUMME(WENN(ISTFEHLER(FINDEN("z";N2:N8));"";
AI2:AI8*WENN(T2:T8=0;J2:J8;T2:T8)))
In Excel 2019 und früher müssen Sie die Formeleingabe mit Strg + Umschalt + Return abschließen, damit der Ausdruck als Arrayformel erfasst wird. In neueren Excel-Versionen reicht die Eingabe mit Return.
Wie Sie sehen, verwendet die Formel anstelle der Bezüge auf N2, AI2, T2 und J2 jetzt Bezüge auf die Bereiche N2:N8, AI2:AI8, T2:T8 und J2:J8. Die Bereichsadressen müssen Sie in Ihrer Tabelle natürlich so anpassen, dass die Zellbereiche bis zur letzten Zeile Ihrer Statistik reichen – statt N2:N8 müssten Sie zum Beispiel N2:N100 eingeben, sofern sich Ihre Daten von Zeile 2 bis Zeile 100 erstrecken.
Die (Array-)Formel macht ansonsten nichts anderes als die weiter oben vorgestellte Formel für einzelne Zeilen. Sie fasst nur alle Multiplikationen zusammen und bildet mit Hilfe der umgebenden SUMME-Funktion automatisch die Gesamtsumme.