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
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
Das Gleiche haben wir natürlich auch für den Verkäufer gemacht:
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
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
Diese Ergebnisse verwenden wir nun in der folgenden Formel, um zu berechnen, wie weit der Summenbereich verschoben werden soll:
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
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
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.
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.
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
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
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?
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
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
Hallo, Herr Weiß
Hier eine Formel die sich auf den Vergleich bezieht.
=SUMME(INDEX(A2:M10;C13;C14):INDEX(A2:M10;C14;C15))
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
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
Vielen Dank für das Lob und das schöne Feedback, das freut mich sehr.
Schöne Grüße,
Martin
Ihre Anleitungen sind immer sehr wertvoll – Hut ab.
zu nachfolgendem Problem habe ich leider 🙁 noch keine Lösung.:
Ich möchte in ein Feld oder Zeile neue auftrage anlegen (auftragswert, Beginn und Ende) und Excel soll mir in einer Tabelle die Auftragswerte auf die Monate verteilen. d.h. die Tabelle soll Monate und die Summe der aufragswerte enthalten.
Können Sie mir da weiterhelfen??
Hallo Herr Yildiz,
vielen Dank für das Lob 🙂
Eine mögliche Lösung für Ihr Problem könnte beispielsweise so aussehen:
Die Formel in den Monatsspalten ist immer die gleiche:
=WENN(UND($B2<=D$1;$C2>=D$1);$A2/(DATEDIF($B2;$C2;“M“)+1);0)
Damit wird der Betrag durch die Anzahl der ganzen Monate geteilt; die Tage spielen dabei keine Rolle.
Wichtig ist nur, dass Sie die Dollarzeichen korrekt setzen und dass in den Monatsspalten oben echte Datumswerte stehen (also immer der 1. des Monats). Und dann stellen Sie einfach das Zahlenformat entsprechend um, so dass nur die Monate angezeigt werden.
Schöne Grüße,
Martin
Moin Martin,
kann man das auch mit SUMMEWENN machen?
Ich will eigentlich nur jeden Tag die Spalte aus der die SUMME kommen soll um eines nach rechts verschieben.
=SUMMEWENN(Confirmed!C:C;’Auswertung nach Christian‘!D11;(BEREICH.VERSCHIEBEN(Confirmed!1:1048576;Confirmed!1:1048576;’Auswertung nach Christian‘!E1;;))) ergibt aber einen #BEZUG.
Zur Erklärung: Confirmed ist eine Tabelle, die sich automatisch die Daten zieht (Web). Jeden Tag kommt eine Spalte dazu. E1 ist ein Feld, dass die Spalte mit dem maximalen Datum ermittelt [=HEUTE()-1].
Ich werde noch wahnsinnig dabei! 🙂
Hallo Olaf,
sicherlich geht das auch mit SUMMEWENN. In deinem Beispiel stimmt aber mit der BEREICH.VERSCHIEBEN-Funktion irgendetwas nicht, aber mir erschließt sich der Aufbau deiner Tabelle zwar noch nicht so ganz: was steht in Spalte C und in Zelle D11? Daher tue ich mir mit einem Tipp noch sehr schwer…
Schöne Grüße,
Martin