Hallo ihr Excel-Verrückten.

Heute habe ich eine besondere Herausforderung für uns.

Ein Freund hat mich gebeten, einer seiner Verkaufsstatisiken etwas mehr Flexibilität einzuhauchen. Und dieser Bitte gehe ich doch gerne nach.

In diesem Artikel zeige ich dir wie man abhängig von einer Dropdown Liste, Werte automatisch nach Datumsbereichen summiert. Das erledigen wir mit den Funktionen SUMMENPRODUKT und INDIREKT.

Also lade dir die Übungsdatei herunter und schon kann es losgehen.

Das ist die Funktion SUMMENPRODUKT

Wie die Funktion aufgebaut ist und wie sie im Detail funktioniert, habe ich in diesem Artikel ausführlich behandelt, daher geh ich nur noch ganz grob auf den Aufbau ein.

Die Funktion SUMMENPRODUKT multipliziert jeden Wert eines Arrays mit einem Wert eines andern Arrays und addiert diese, bildet also die Summe. Das sieht vereinfacht dann so aus:

=SUMMENPRODUKT(B7:B11;C7:C11)

Hier wird jeder Werte aus dem Array B7:B11 mit den Werten aus dem Array C7:C11 mit einander multipliziert und anschließend addiert.

Wie bereits erwähnt, wenn du mehr Infos zu der Funktion SUMMENPRODUKT haben möchtest, dann schau doch in diesen Artikel.

Was wollen wir erreichen

Unsere Ausgansdaten sehen so aus:

Ziel soll es nun sein, ohne den Einsatz von VBA in folgender Übersicht Daten auszuwerten und gegenüber zu stellen.

Dabei sollen, um die Übersicht zu behalten maximal 2 Werte angezeigt werden können und die Auswertung soll monatsweise stattfinden.

Die zu berechnenden Werte sollen mit der Dropdown Liste bestimmt werden.

Was benötigen wir dazu

Wir möchten Werte aufsummieren, anhand der Daten, die wir auswählen und die zusätzlich von einem Datumsbereich abhängig sind.

Zur Auswahl benötigen wir als erstes unsere Dropdown Liste, mit der wir auswählen, welche Daten wir auswerten möchten.

Zusätzlich benötigen wir dann noch die monatsweise Auflistung der Werte.

Dann fehlt noch das Kernstück, eine Formel die uns anhand der beiden Daten eine Summe erstellt und diese in unsere Auswertung überträgt.

Um Trends und Verläufe besser zu erkennen, soll zusätzlich noch ein Diagramm in die Übersicht integriert werden.

Wie lösen wir das Problem einer SUMMENPRODUKT Funktion in Verbindung mit einer Dropdown Liste

Am besten Schritt für Schritt.

Dropdown Liste erstellen

Erstens, wir erstellen unsere Dropdown Liste. Dazu markierst du die beiden Zellen in welchen die Dropdown Liste erscheinen soll und wählst die Überschrift unserer Ausgangsdaten als Quelle.

Wenn du mehr über den Einsatz und die Erstellung von Dropdown Listen erfahren möchtest, darfst du dir meinen Artikel darüber nicht entgehen lassen. Den findest du hier: Alles, was du über Dropdown Listen wissen musst.

Datumswerte monatsweise anführen

Als nächstes brauchen wir die Monate nach denen wir die Daten summieren möchten.

Das könnten wir einfach machen, indem wir das Datum in die einzelnen Zellen eintragen, ich möchte aber dass es automatisch auf Basis unserer Ausgangsdaten gezogen wird. Somit müssen wir das Datum nicht jedes Mal ändern, wenn sich unsere Ausgangsdaten ändern.

Das erreichen wir mit Hilfe der Funktion MONATSENDE damit wir den ersten Tag des Monats ermitteln können. Ich weiß das klingt kompliziert aber eine Funktion in Excel für den Monatsanfang gibt es nicht.

Du könntest natürlich mit jedem beliebigen Tag in einem Monat rechnen, ich verwende aber gerne den Monatsersten.

Warum ich ausgerechnet den Monatsersten verwende und weitere hilfreiche Details zum Rechnen mit Datumsfunktionen habe ich dir in diesem Artikel beschreiben. Datumsfunktionen

Und wie immer findest  du hier alle Informationen zu Formeln und Funktionen.

Jetzt aber zur fertigen Formel:

=MONATSENDE(Ausgangsdaten!$D3;-1)+1

Die Funktion berechnet das Monatsende des Datums aus der Zelle D3 im Tabellenblatt „Ausgangsdaten“ und subtrahiert dabei mit der Angabe von -1 einen Monat. Das +1 addiert zu dem Datum nun einen Tag und das Gesamtergebnis ist nun das Monatsende des vorhergehenden Monats plus einem Tag, also der erste Tag des aktuellen Monats.

Das Ganze vielleicht noch mal etwas anschaulicher Schritt für Schritt:

=MONATSENDE(Ausgangsdaten!$D3;-1)+1

Monatsende vom 10.10.2019 wäre der 31.10.2019. Das (-1) in der Funktion subtrahiert davon einen Monat.

Also wäre das Ergebnis der 30.09.2019.

Wenn man jetzt wieder einen Tag dazu addiert, das (+1) in der Formel, erhält man den 01.10.2019.

So, den ersten Monat haben wir nun. Jetzt fehlen noch die folgenden.

Folgemonate auflisten

Um nun die Folgemonate in den folgenden Zeilen aufzulisten, müssen wir lediglich immer einen Monat dazu zählen.

Dazu nutzen wir die Formel =DATUM(Jahr;Monat;Tag).

Mit dieser Formel kannst du mittels Zahlen ein Datum aufbauen.

Zum Beispiel gibt die Formel =DATUM(2020;02;14) als Ergebnis das Datum 14.02.2020 aus.

Nun wieder zur Formel, die wir in Zelle B4 einfügen.

=DATUM(JAHR(B3);MONAT(B3)+1;1)

Wenn wir die Formel nun nach unten erweitern, erhalten wir folgendes Ergebnis.

Nähere Infos zu dieser Funktion findest du in diesem Artikel: Die Funktion DATUM

Die Rahmenbedingungen für die SUMMENPRODUKT Formel haben wir nun geschaffen.

Jetzt kommen wir zum Kern unserer Auswertung.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.

Die SUMMENPRODUKT Formel

Ich möchte euch gar nicht länger auf die Folter spannen, daher hier gleich die Formel.

Da ist die Schönheit:

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))*(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))))

Wenn du jetzt denkst „Was labert der da, Schönheit? Das ist ein Monster!“ Dann wundert mich das nicht. Aber keine Sorgen, wir bauen diese Formel nach und nach miteinander auf.

Willst du darauf nicht waten, kannst du sie ganz einfach hier aus dem Beitrag oder aus der Übungsdatei kopieren.

Wenn du zu denen gehörst, die Interesse an der Formel oder sogar Spaß an Excel haben, dann möchte ich dir in folgenden Absätzen den Aufbau und die Funktionsweise der Formel erklären.

Welche Bestandteile braucht unsere fertige Formel

Wenn du dich erinnerst, brauchen wir in der Funktion SUMMENPRODUKT Arrays, die miteinander multipliziert werden.

Was sind nun unsere Arrays?

Einerseits die einzelnen Tage des jeweiligen Monats und andererseits die Werte in den jeweiligen Spalten unserer Ausgangsdaten die wir über die Dropdown Liste bestimmen.

Beginnen wir mit den Datumswerten.

Das Datums Array

In unseren Ausgangsdaten ist für jeden Tag ein Eintrag vorhanden. Wir wollen aber nach Monaten auswerten.

Wie können wir den Monatsbereich definieren?

Ein Monat reicht immer vom Monatsanfang bis zum Monatsende.

Also müssen wir alle Datumswerte in unseren Ausgangsdaten wählen, die größer als unser Monatsanfang sind gleichzeitig aber kleiner als das Monatsende. Jede dieser Bedingungen ist ein Array unserer Formel.

Der erste Teil sieht also so aus:

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))

Keine Sorge, was die Formel genau macht erkläre ich dir weiter unten. Falls du es gleich wissen möchtest, folge diesem Link.

Als zweiten Schritt müssen wir die Datumswerte nun mit jenen aus den gewählten Spalten multiplizieren.

Das Werte Array

Quick and dirty würde das funktionieren, wenn wir die Formel folgendermaßen ergänzen.

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))*Ausgangsdaten!E3:E472)

Das ist aber sehr unflexibel außerdem nützt uns da die Dropdown Liste nichts. Also müssen wir die Formel nun etwas abändern und erweitern.

Wir haben nun das Problem, dass wir den Text aus einer Dropdown Liste in eine Formel verknüpfen müssen. Welche Formel biete sich da gerade an, richtig die INDIREKT Formel.

Wir müssen also den Verweis Ausgangsdaten!E3:E472 mittels Formel zusammensetzen, sodass sich dieser mit Änderung des Textes in der Dropdown Liste auch ändert.

Was sind denn die Fixen Bestandteile der Formel, naja der Blattname Ausgangsdaten! und die Zeilennummern sind immer gleich, egal welche Spalte wir auslesen wollen. Lediglich der Spaltenbuchstabe soll sich ändern. Die Formeln die uns da weiter helfen sind einerseits die Formel ADRESSE und VERGLEICH. Die Formel VERGLEICH gibt die Stelle in einem Bereich an, an der sich das Suchkriterium befindet und die Formel ADRESSE setzt zwei Zahlen zu einer Zelladeresse zusammen.

Ohne Umschweife, hier der fertige zweite Teil der SUMMENPRODUKT Formel, keine Angst, wie bereits erwähnt folgt weiter unten eine ausführliche Erklärung.

=INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))))

Hiermit definieren wir also den Bereich mit dem wir die Datumswerte multiplizieren.

Haben wir nun die drei Arrays definiert, müssen wir diese nur noch in der SUMMENPRODUKT-Formel zusammen führen.

So sieht die fertige Formel aus

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))*(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))))

Wenn wir die Formel nun bis in die letzte Zelle erweitern, erhalten wir folgendes Ergebnis:

Was macht diese Formel nun genau

Nun aber zum spannendsten Teil, wie funktioniert die Formel und was machen die einzelnen Teile davon genau?

Zur Formelanalyse kann ich dir auf jeden Fall die Taste F9 ans Herz legen um die Formel in Einzelteile aufzuspalten und zu erkennen, was die einzelnen Bestandteile berechnen.

Was genau du mit der Taste F9 machen kannst und noch weitere Tipps zur Formelanalyse findest du in diesem Artikel: Formelanalyse

Übergeordnet haben wir unsere SUMMENPRODUKT-Formel. Diese besteht in unserem Fall aus drei einzelnen Arrays, welche miteinander multipliziert und dann summiert werden.

Das erste Array

Das erste Array schließt alle Werte aus, welche kleiner als der Monatsanfang des jeweiligen Monats sind.

((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))

Das Ergebnis des Arrays sieht so aus:

{FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR}

Auf den ersten Blick sehr verwirrend, ist aber einfach erklärt. Es werden die Ergebnisse eines jeden Wertes in dem Array anzeigt.

Also es prüft jeden Datumswert in dem gewählten Bereich Ausgangsdaten!$D$3:$D$472 mit der Bedingung (MONATSENDE($B3;-1)+1) und gibt für alle Werte, die größer oder gleich der Bedingung sind WAHR aus und für alle die kleiner sind, FALSCH aus.

Das zweite Array

Genau das gleiche macht es auch mit dem zweiten Array, hier prüft die Formel jedoch alle Werte, ob diese kleiner oder gleich dem Monatsende des gewählten Monats sind.

((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))

Das dritte Array

Abschließend dann noch das dritte Array, welches angibt, mit welcher Spalte die Datumswerte multipliziert werden sollen. Wie macht die Formel das nun genau? Dazu zerlegen wir diese in die einzelnen Bestandteile.

(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))))

Das Array besteht aus 2 INDIREKT Formeln, eine vor dem „:“ und eine danach. Die INDIREKT Formel davor bestimmt, wo der Bereich beginnt.

INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))

Also wie du weißt wandelt die Funktion INDIREKT einen Text in einen Zellenbezug oder Adresse um, wie das funktioniert, habe ich dir in diesem Artikel erklärt: Funktion INDIREKT für jedermann einfach erklärt

Wie setzt sich unsere Adresse nun zusammen.

Eine Zellenadresse auf einem anderen Arbeitsblatt muss in folgendem Format vorhanden sein. Ausgangsdaten!C3

Also erst der Blattname gefolgt von einem „!“ und danach von der eigentlichen Zellenadresse.

Diese Adresse müssen wir mit unserer INDIREKT Funktion nun irgendwie zusammen basteln.

Was sind die fixen Bestandteile der Adresse? Einerseits der Blattname und andererseits die Zeilennummer.

Somit können wir den Beginn der Adresse als Text in die Formel einfügen.

INDIREKT(„Ausgangsdaten!

Gefolgt vom Spaltenbuchstaben. Wir wollen jedoch die jeweilige Spalte auf Basis der Dropdown Liste auswählen. Und nicht fest kodiert in der Formel hinterlegen.

Dazu nutzen wir den Umweg über die Funktion VERGLEICH, diese gibt die Stelle wieder, an der ein Wert in einem Bereich steht.

Als Ergebnis der Formel erhalten wir eine Zahl, nämlich an welcher Stelle im Bereich Ausgangsdaten!$2:$2 unser Suchkriterium C$2 steht. Steht der gesuchte Wert beispielsweise in Spalte E so gibt die Formel die Zahl 5 zurück.

VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)

Wir brauch aber den Buchstaben der Spalte für unser INDIREKT Funktion. Also ist eine zusätzliche Formel erforderlich, die eine Zahl in einen Spalten Buchstaben umwandelt. Das geht ganz einfach mit der Funktion ADRESSE.

Also packen wir unsere erhaltene Spaltenzahl in die Funktion ADRESSE. Diese Funktion wandelt zwei Zahlen in eine Zellenadresse um. Die Zahlen 4 und 7 ergeben beispielsweise die Zelle G4.

ACHTUNG: In der Funktion ADRESSE muss die Zeile vor der Spalte angeführt werden.

Außerdem kannst du noch definieren, ob es sich um einen absoluten oder relativen Zellbezug handeln soll und du kannst noch viele weitere nützliche Parameter festlegen, dazu aber in diesem Artikel mehr: Funktion ADRESSE

Wenn wir die beiden Formeln nun zusammen führen, sieht das so aus.

ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))

Die Zeile ist wieder als Zahl ausgeführt, da diese ja in jeder Spalte gleich ist und die Spaltennummer wird über die Dropdown Liste mit der Funktion VERGLEICH aus dem Bereich Ausgangsdaten!$2:$2 automatisiert bestimmt.

Verbinden wir dies nun wieder mit unserer begonnen INDIREKT Funktion ergibt sich folgende Formel.

INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))

Die einzelnen Bestandteile berechnet ergeben folgendes:

  • Die Funktion VERGLEICH gibt die Position unseres Textes aus der Dropdown Liste (C$2) in dem Bereich Ausgangsdaten!$2:$2

INDIREKT(„Ausgangsdaten!„&ADRESSE(3;5))

  • Die Funktion ADRESSE erzeugt aus der Zahl 3 und dem Ergebnis der VERGLEICH Funktion eine Zellenadresse.

INDIREKT(„Ausgangsdaten!„&“$E$3„)

  • Diese Zellenadresse wird in der Funktion INDIREKT mit dem Text kombiniert und erzeugt so eine finale Zellenreferenz auf ein anderes Arbeitsblatt.

INDIREKT(„Ausgangsdaten!$E$3„)

Der erste Teil des Arrays gibt nun also die erste Zelle des Bereichs an und der zweite Teil, die letzte Zelle des Bereiches.

(INDIREKT(„Ausgangsdaten!$E$3„): INDIREKT(„Ausgangsdaten!$E$472„))

Berechnung der gesamten Formel

Jetzt haben wir sehr ausführlich geklärt, was die einzelnen Arrays in der Funktion SUMMENPRODUKT machen. Was machen diese aber nun in Verbindung miteinander.

Ganz einfach.

Das erste Array bestimmt alle Werte die größer oder gleich dem Monatsanfang sind und gibt für jedes Datum auf das diese Bedingung zutrifft den Wert WAHR aus.

Das zweite bestimmt alle die kleiner oder gleich dem Monatsende sind und gibt für jedes Datum auf das diese Bedingung zutrifft ebenfalls den Wert WAHR aus.

Die Schnittmenge der beiden wird mit dem erstellten Bereich des dritten Arrays multipliziert.

Im dritten Array stehen Werte wie Umsatz oder Bestellmenge als Zahl, multipliziert man nun eine Zahl mit dem Wert WAHR, wird der Wert WAHR als 1 interpretiert.

Somit erhalten wir als Ergebnis die Summe aller Werte in der gewählten Spalte innerhalb des jeweiligen Datums Bereiches.

Puh. Erstmal durchatmen. Ich weiß, dass ist nicht ganz einfach nachzuvollziehen, ich hoffe trotzdem ich konnte es dir einigermaßen verständlich erklären.

Ich finde es aber einfach genial, wenn sich einzelne Teile der SUMMENPRODUKT Formel zu einem großen Ganzen verbinden.

Eine Formel zu analysieren kann manchmal sehr schwierig sein und man sollte es unbedingt einige Male üben. Wenn du dich dafür interessierst, kann ich dir auf jeden Fall diesen Artikel dazu empfehlen, dort habe ich dir den Ablauf einer Formelanalyse genauestens erklärt.

Wenn du zu den einzelnen Funktionen noch Fragen hast, kannst du mich jederzeit kontaktieren, benutze dazu einfach das Kontaktformular.

Alternativ kannst du auch die Funktionen in meinem Blog nutzen.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.

So sieht das Ergebnis der SUMMENPRODUKT Formel aus

Abschließend möchte ich die berechneten Daten auch noch optisch ansprechend aufbereiten. Somit sind Trends und Zusammenhänge viel besser zu erkennen.

Dazu kopieren wir einfach den Inhalt der Spalte C in die Spalte D. Somit können wir gleichzeitig zwei unterschiedliche Daten auswerten.

Nun wandeln wir unsere Liste mit der Tastenkombination STRG+T noch in eine Tabelle um und fügen ein Diagramm ein.

Und fertig ist die Übersicht über die Verkäufe.

Ich habe dir die fertige Datei als Vorlage zur Verfügung gestellt, solltest du daran Interesse haben, kannst du diese ganz einfach aus den Vorlagen downloaden.

Auswertung Verkaufszahlen.xlsx

Wie kann das Problem alternativ gelöst werden

Ich gebe zu, diese Vorgehensweise mit der SUMMENPRODUKT Formel ist etwas umständlich, aber meines Wissens nach die einzige Möglichkeit Daten anhand einer Dropdown Liste zu wählen und gleichzeitig zu berechnen.

Es gibt aber eine alternative, die zwar nicht mit Dropdown Listen arbeitet, aber um ein Vielfaches schneller zu erstellen ist.

Das Zauberwort lautet Pivot!!!

Dazu wählst du eine beliebige Zelle innerhalb unserer Ausgangsdaten.

Als nächsten Schritt fügst du über Einfügen–>Pivot Tabelle eine Pivot Tabelle ein.

Du wählst neues Arbeitsblatt und bestätigst mit „OK“.

In dem neu erstellten Arbeitsblatt kannst du nun dieselbe Auswertung mittels Pivot erstellen wie zuvor mit der SUMMENPRODUKT Funktion.

Aber nun Schritt für Schritt.

  • Ziehe die Spaltenüberschrift „Datum“ in das Feld „Spalten“ und die Überschriften „Verkäufe“ und „Umsatz“ ins Feld „Werte“.
  • Klicke mit der rechten Maustaste auf einen Datumswert, wähle gruppieren, gruppiere nach Monaten und Jahren und bestätige mit „Ok“.
  • Formatiere die Spalte Umsatz als Währung und du erhältst folgendes Ergebnis.
  • Abschließend fügst du wieder das Diagramm ein und fertig ist die Auswertung.

Der Vorteil dieser Lösung liegt ganz klar in der Geschwindigkeit des Erstellens und in den weiteren Möglichkeiten der Pivot Tabelle.

Der Nachteil darin, dass es kein Dropdown gibt um die Daten auszuwählen sondern nur das hinzufügen oder entfernen im „Werte“ Feld.

Wenn du die unzähligen Möglichkeiten der Pivot Tabelle nutzen möchtest, dann kannst du dir hier einen kleinen Vorgeschmack holen. Mit dieser Schritt-für-Schritt Anleitung gelingt dir jede Pivot Tabelle

Wenn du zu einzelnen Funktionen noch Fragen hast, melde dich gerne bei mir, das kannst du ganz einfach hier tun: Kontakt aufnehmen

Oder du besuchst einfach die Formelsammlung auf meinem Blog. Formeln

Fazit

Vergleichen wir das Ergebnis nun mit unseren Anforderungen, würde ich sagen Mission erfüllt.

Der Einsatz der Funktion SUMMENPRODUKT ist zwar nicht ganz einfach aber durchaus machbar. Ich bin mir sicher, dass du diese Funktion mit ein bisschen Übung sicher und effizient einsetzen kannst.

Sollte das zu umständlich sein, kannst du alternativ eine Pivot Tabelle einsetzen, die hat zwar nicht den gleichen Funktionsumfang ist aber dafür um einiges schneller und einfacher.

Als abschließenden Tipp kann ich dir folgendes mitgeben.

Excel hat viel mit logischer Denkweise zu tun, ich selbst musste hunderte Formeln aufbauen, bis ich mir die richtige Denkweise dazu angeeignet habe. Am effizientesten gelingt das indem man einfach loslegt, schnapp dir ein Tabellenblatt und fang an, dann wirst du schnell dahinter kommen. Alternativ kannst du dich gerne hier noch weiter in das Thema einlesen.

Baue Formeln immer Stück für Stück auf, versuche nicht von Beginn an alles perfekt zu machen. Wenn du dir zu Beginn Gedanken darüber machst, was dein Ergebnis sein soll und deine Formel Stück für Stück aufbaust, wirst du schneller dein Ziel erreichen als wenn du versuchst die gesamte Formel in einem Schwung zu erstellen.

Wenn du Blut geleckt hast und nicht mehr ohne Excel leben willst J oder einfach nur hin und wieder neugierig auf Updates bist, dann trag dich doch in meinen kostenlosen Newsletter ein. Das kannst hier tun.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps mit denen du in kurzer Zeit dein Excel Können auf das nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Dein Michael, alias Doktor-Excel der smarte Excel Problem Löser

PS.: Wenn der Artikel hilfreich für dich war, teil ihn doch mit der Welt da draußen damit auch andere davon profitieren können.

PPS.: Vergiss nicht, solltest du spezifische Probleme mit Excel haben, schreib mir eine Nachricht und wir lösen sie gemeinsam. Hier kannst du mich kontaktieren.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.