Bedingte Summen in Excel (Teil 3) 2

Maximale Flexibilität bei der Bildung von Summen in Excel: Sowohl Zeilen als auch Spalten können variabel sein!
 

Letzte Woche habe ich dir hier gezeigt, wie man mit der geschickten Kombination der Funktionen SUMME und BEREICH.VERSCHIEBEN in Excel Werte unter bestimmten Bedingungen aufsummieren kann.

Dabei war die zu summierende Zeile variabel, die Spalten hingegen fix. Heute gehen wir noch einen Schritt weiter und wollen zusätzlich auch die zu summierenden Spalten möglichst flexibel halten.

Und so geht’s:

Zur Erinnerung:
Im Beispiel in der letzten Woche haben wir in einer Umsatz-Tabelle einen beliebigen Verkäufer ausgewählt und uns dann dessen Umsätze über alle Monate berechnen lassen. Und jetzt kann ich es auch verraten: Dazu hätten wir keine BEREICH.VERSCHIEBEN-Funktion benötigt. Eine zusätzliche Summenspalte und ein kleiner SVERWEIS darauf hätte auch gereicht. Aber wir wollen ja etwas lernen…

…nämlich, wie man die Umsätze eines Verkäufers nur für ganz bestimmte Monate aufsummiert. Und dazu brauchen wir doch die BEREICH.VERSCHIEBEN-Funktion.

Die heutige Beispieldatei kannst du dir hier herunterladen.

Anfang und Ende

Wir wollen maximale Flexibilität und bereiten deshalb neben dem Verkäufer auch für den Anfangs- und Endmonat jeweils ein eigenes Eingabefeld vor:

Die Ausgangslage

Die Ausgangslage

Um das Ganze möglichst anwenderfreundlich zu gestalten, definieren wir für die beiden Eingabefelder auch eine Dropdown-Liste, in der die Monate zur Auswahl angeboten werden:

Dropdown-Liste für die Monatsauswahl

Dropdown-Liste für die Monatsauswahl

Das Gleiche haben wir natürlich auch für den Verkäufer gemacht:

Dropdown-Liste für die Verkäuferauswahl

Dropdown-Liste für die Verkäuferauswahl

Vorbereitung

Zuerst ermitteln wir noch über die VERGLEICH-Funktion, in welcher Zeile der ausgewählte Verkäufer steht:

Zeile des Verkäufers ermitteln

Zeile des Verkäufers ermitteln

Da ich das schon im letzten Artikel beschrieben habe, gehe ich hier nicht mehr näher darauf ein.

Kommen wir nun zur eigentlichen Summenformel. Auch diesmal führt uns eine Kombination aus SUMME und BEREICH.VERSCHIEBEN zum Ziel. Dabei habe ich zwei Varianten im Angebot.

Variante 1: Mit Zwischenschritt

Wem verschachtelte Funktionen noch etwas Schwierigkeiten bereiten, kann mit dieser Variante arbeiten. Dazu ermitteln wir – ähnlich wie beim Verkäufer – über einen Zwischenschritt, in welcher Spalte sich der ausgewählte Monat befindet:

Spalten von Anfangs- und Endmonat ermitteln

Spalten von Anfangs- und Endmonat ermitteln

Diese Ergebnisse verwenden wir nun in der folgenden Formel, um zu berechnen, wie weit der Summenbereich verschoben werden soll:

BEREICH.VERSCHIEBEN, Variante 1

BEREICH.VERSCHIEBEN, Variante 1

Was genau passiert hier? Sehen wir uns die 4 Parameter in der BEREICH.VERSCHIEBEN-Funktion genauer an:

Erster Parameter: B2:M10
Klar, das ist der komplette Bereich, in dem die Umsatzzahlen stehen.

Zweiter Parameter: C13-1
Der Verkäufer steht in der vierten Zeile, wie wir im Feld C13 berechnet haben. Den Summenbereich verschieben wir aber nur um drei Zeilen, daher C13-1.

Dritter Parameter: C14-1
Der Anfangsmonat März steht in der dritten Spalte (siehe Feld C14). Den Summenbereich dürfen wir daher wieder nur um zwei Spalten verschieben. Also ziehen wir von C14 wieder den Wert 1 ab.

Vierter Parameter: 1
Wir wollen nur die Umsätze eines Verkäufers, also nur die Werte einer Zeile berechnen. Daher steht hier der Festwert 1.

Fünfter Parameter: C15-C14+1
Jetzt wird’s etwas spannender. Der Endmonat im Beispiel ist der Juni, also Monat 6. Der Anfangsmonat ist der März, also Monat 3. Das heißt, unser Summenbereich erstreckt sich über 4 Monate: März, April, Mai und Juni. Die Subtraktion Ende minus Anfang (also C15-C14) ergibt jedoch den Wert 3. Daher addieren wir wieder die Zahl 1.

Und schon haben wir das gewünschte Ergebnis:

Ergebnis der Variante 1

Ergebnis der Variante 1

Variante 2: Ohne Zwischenschritt

Wer vor etwas komplexeren Formeln keine Angst hat, kann sich die Zwischenberechnungen aus Variante 1 auch sparen. Über die MONAT-Funktion kommt man nämlich auf die gleichen Ergebnisse:

=MONAT(Datum)

Da unsere Spaltenüberschriften bereits den Monat beinhalten, kann man sich mit Hilfe der MONAT-Funktion den jeweiligen Monat als Zahl ausgeben lassen, also 1 für Januar bis 12 für Dezember. Und damit haben wir wieder die entsprechenden Spaltenangaben, die wir in der BEREICH.VERSCHIEBEN-Funktion nutzen können.

Und so sieht unsere abgewandelte Formel dann aus:

BEREICH.VERSCHIEBEN, Variante 2

BEREICH.VERSCHIEBEN, Variante 2

Die Logik dahinter ist die gleiche wie in Variante 1. Die Formel ist zwar etwas länger, aber dafür eleganter, da keine Zwischenberechnung notwendig ist.

Der spitzfindige Leser wird noch einen Schönheitsfehler finden: Wenn man im Feld “Bis Monat” einen kleineren Wert angibt, als im Feld “Von Monat”, dann liefert die Formel einen #BEZUG!-Fehler.

Schreib mir doch unten in den Kommentaren, wie du diesen Fall ausschließen würdest.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

2 Gedanken zu “Bedingte Summen in Excel (Teil 3)

  • Gerhard Pundt

    Hallo Martin,

    spontan fällt mir erst mal nur eine Ergänzung der Formel in B17 um eine WENN-Abfrage ein.

    Die Formel sollte so aussehen:

    =WENN(WERT(B15)<WERT(B14);"FEHLER: Bis Monat < Von Monat";SUMME(BEREICH.VERSCHIEBEN(B2:M10;C13-1;MONAT(B14)-1;1;MONAT(B15)-MONAT(B14)+1)))

    Dann steht dort ein Text, der weiteres Rechnen mit dem Zellinhalt verhindert.

    Viele Grüße

    Gerhard Pundt

  • Martin Weiß Beitragsautor

    Hallo Gerhard,

    die WENN-Abfrage ist eine sehr gute Idee, denn damit erhält der Anwender ein entsprechendes Feedback und weiß, wo der Fehler liegt.

    Danke für die Ergänzung!

    Schöne Grüße,
    Martin