Bedingte Summen in Excel (Teil 3) 9

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.



Schreibe einen Kommentar

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

9 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ß Autor des Beitrags

    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

  • Reiner Herold

    Hallo Herr Weiß,
    Habe mit =Monat(A1) die Monatszahl erhalten. Wenn ich jetzt die Zahl mit MMMM formatiere erhalte ich immer Januar, egal welches
    Datum. Woran kann das liegen?

    • Martin Weiß Autor des Beitrags

      Hallo Herr Herold,

      die MONAT-Funktion liefert ja nur noch eine einzelne Zahl zurück, jedoch keinen Datumswert. Daher können Sie diese Zahl auch nicht sinnvoll als Datum (bzw. Monat etc) formatieren.

      Der Januar kommt daher zustande, da die Excel-Zeitrechnung mit dem 01.01.1900 beginnt, was dem numerischen Wert 1 entspricht. Der Wert 2 wäre dann der 02.01.1900, drei = 03.01.1900 usw. Daher wird eine Zahl zwischen 1 und 12 immer im Januar 1900 liegen.

      Schöne Grüße,
      Martin

      • Reiner Herold

        Hallo Herr Weiß
        Vielen Dank für den Tipp, Habe jetzt =TEXT(A1) mit dem Format “MMMM” verwendet und es funktioniert. Manchmal sieht man den Wald vor lauter Bäumen nicht.
        Schöne Grüße
        Reiner

  • Reiner Herold

    Hallo, Herr Weiß
    Hier eine Formel die sich auf den Vergleich bezieht.

    =SUMME(INDEX(A2:M10;C13;C14):INDEX(A2:M10;C14;C15))

    • Martin Weiß Autor des Beitrags

      Hallo Herr Herold,

      vielen Dank für diese Variante mit INDEX, das ist eine sehr schöne und auch leichter zu durchschauende Alternative. Lediglich ein kleiner Fehler hat sich eingeschlichen (zumindest bezogen auf die Beispieltabelle). Die Bezügen müssten statt in Spalte A erst in Spalte B beginnen:
      =SUMME(INDEX(B2:M10;C13;C14):INDEX(B2:M10;C14;C15))

      Aber eine sehr gute Lösung!

      Schöne Grüße,
      Martin

      • Reiner Herold

        Hallo Herr Weiß
        Sie haben natürlich völlig recht. Habe mittlerweile alle Ihre E-Books gekauft und damit viel gelernt. Auch diese Seite ist für mich die Nr. 1 im Netz. Ihre Erklärungen der Wirkungsweise von Formeln sind sehr verständlich und nachvollziehbar.Deshalb mein herzlicher Dank für Ihre Arbeit.
        Schöne Grüße
        Reiner

        • Martin Weiß Autor des Beitrags

          Vielen Dank für das Lob und das schöne Feedback, das freut mich sehr.

          Schöne Grüße,
          Martin