Kurzfassung: Mittelwert eines bestimmten/definierten Zeitraums (letzte n Tage mit Werten) bilden, Zellen ohne einen numerischen Wert sollen nicht berücksichtigt werden.
Hinweis: Die eigentliche Aufgabe/Problemstellung habe ich einem Forum entnommen, wo ich mich an der Erstellung der Lösung beteiligt habe.
In dieser Datei sind aufeinanderfolgende kalendarischen Daten in Spalte A eingetragen, in Spalte B sind dieses beliebige Werte (ganz Zahlen). Diese Werte sind allerdings nicht durchgängig vorhanden, an unterschiedlichen Stellen sind Lücken. Diese Zellen sind meistens mit einem - versehen, mitunter sind sie auch leer.
Für einen definierten Zeitraum der sich aus einer bestimmten Anzahl von Tagen mit einem eingegebenen (numerischen) Wert in Spalte B ergibt soll der Mittelwert des jeweiligen Zeitraums berechnet werden.
Das hört sich zwar zu Beginn ganz simpel an stellt sich jedoch rasch als schwieriger dar. Insbesondere wenn kein VBA verwendet werden soll, ist das ein anspruchsvolles Unterfangen. Es gibt nämlich noch eine weitere Einschränkung: Es sollen aus der Liste nur die letzten (die jüngsten) 10 (bzw. 30) Tage mit tatsächlichen Werten in Spalte B verwendet werden, also nicht die letzten 30 Kalendertage sondern die letzten 30 (bzw. 10) Produktionstage; und die Eingaben (Spalte B) in der Liste werden täglich um das aktuelle Datum erweitert. Der erste einzuschließende Wert (also das letzte/jüngste Datum in der Tabelle mit einem numerischen Wert in Spalte B) kann entsprechend täglich ein anderer sein.
Los geht’s
Öffnen Sie spätestens jetzt diese Datei und verinnerlichen den Aufbau sowie die Zielsetzung. In Spalte A sind die kalendarischen Daten des 2. Halbjahres 2017 aufgeführt. In der Spalte B stehen als Zahlen ausschließlich positive, ganzzahlige Werte; in den meisten Fällen sind Tage ohne angegebene Werte mit einem - ausgefüllt, an manchen Tagen (insbesondere in jenen, die in der Zukunft liegen) ist das Feld Wert jedoch leer.
Es sollen nun 2 Durchschnittswerte errechnet werden: Aus den letzten 10 und den letzten 30 Tagen soll der Mittelwert berechnet werden. Dabei sollen nur jene Tage gewertet werden, wo in der Spalte Wert auch eine Zahl eingetragen ist. In der Beispieldatei ist also der 19. Oktober der erste einzubeziehen der Tag, der 17. Oktober der zweite, der 15. Oktober der dritte, der 14. Oktober der vierte Tag, usw. Wenn dann am 20. Oktober eine Zahl in Spalte B eingetragen wird, beginnt die Zählung natürlich an diesem Tag. Diese Dynamik gilt für beide genannten Anzahl von Tagen.
Da diese Auswertung täglich erfolgen soll ist es müßig, immer und immer wieder das Arbeitsblatt zu kopieren, die leeren oder mit einem - ausgefüllten Zeilen in Spalte B zu löschen und die letzten 10 bzw. 30 Zeilen für die Durchschnittsberechnung stehen zu lassen. Hier bietet sich Power Query als gute und effiziente Lösung an. Auch wenn es mehrere Lösungswege gibt, zeige ich Ihnen nur zwei, die ich für effizient und dennoch nachvollziehbar erachte.
Die Muster-Datei ist geladen. Achten Sie darauf, dass eine Zelle innerhalb der Daten markiert ist und anschließend entweder über den Menüpunkt Power Query | Von Tabelle (Excel 2010⁄2013) oder Menü Daten | Aus Tabelle in den neueren Versionen die Daten in den Abfrage-Editor laden. Das sieht dann vermutlich auch bei Ihnen so aus:
Der Abfrage Editor mit den importierten Daten
Zwei Dinge werden Ihnen auffallen: In der 1. Spalte fehlt die Angabe des Wochentages, dafür ist stets die Uhrzeit 00:00:00 Uhr eingetragen und in der Spalte Wert sind die Zahlen rechts- und die Minuszeichen linksbündig dargestellt. Das mit dem Datum sollte nicht nur der Optik wegen dahingehend geändert werden, dass ausschließlich das Datum in der Spalte steht. Dazu ein Rechtsklick in die Überschrift und im Kontextmenü wählen Sie Typ ändern ► | Datum.
Im nächsten Schritt geht es nun darum, alle Zeilen ohne einen numerischen Wert aus der Abfrage zu löschen. Dazu ein Klick in das Dropdown-Symbol 
der Spalte Wert. Um das Häkchen bei (NULL) und beim - zu entfernen gehe ich über die Auswahl Filter und klicke dann auf Ist nicht gleich… So ist gewährleistet, dass auch neu hinzugekommene Zahlenwerte, die bislang noch nicht in der Liste enthalten waren, berücksichtigt werden. Geben Sie zuerst neben entspricht nicht das Minuszeichen - ein, belassen Sie es danach bei der Options-Auswahl Und, wählen im Kombinationsfeld wiederum entspricht nicht und geben in das danebenliegende Feld den Wert null ein. Also den Text und alles klein, was als Suchkriterium einem leeren Feld entspricht:
So filtern Sie die nicht erwünschten Zeilen
Nach einer Bestätigung werden nur noch die 54 Zeilen angezeigt, wo ein numerischer Wert in Spalte Wert enthalten ist. Diese Abfrage dient nun als Basis für die beiden kommenden Filterungen. Um nicht allzu viel überflüssiges Datenmaterial zu speichern, klicke ich nun auf den Text (nicht das Symbol) Schließen & laden und anschließend auf den Punkt Schließen & laden in… Ein Klick auf die Options-Schaltfläche Nur Verbindung erstellen und dann Laden. Und bei der Gelegenheit benenne ich die Abfrage beispielsweise im rechten Seitenfenster auch gleich um; der neue Name ist: Alle Tage.
Es sollen ja 2 Werte berechnet werden, dazu bedarf es in Power Query zweier neu zu erstellender Abfragen. Klicken Sie dazu im Menü Start auf das Symbol Verwalten und hier auf die untere Auswahl Verweis, da ja stets ein dynamischer Bezug zur Basis-Tabelle gegeben sein soll. Benennen Sie nun im linken oder rechten Seitenfenster die neu erstellte Abfrage um, der Name sollte 10 Tage sein. Wählen Sie nun wiederum die Abfrage Alle Tage und erstellen auch hiervon auf dem gleichen Wege wie eben eine dynamische Kopie. Diese Abfrage bekommt naturgemäß den Namen 30 Tage.
Aktivieren Sie nun die Abfrage 10 Tage, wo ich Ihnen die erste der beiden hier vorgestellten Möglichkeiten zur Filterung aufzeige. Da die kalendarischen Daten bereits sortiert sind, können Sie sich diesen Vorgang sparen. Und da dank der vorher durchgeführten Filterung auch nur noch jene Tage in der Abfrage enthalten sind, die in der Spalte Wert auch eine Zahl enthalten, können Sie folgende Aussage als gegeben hinnehmen: „Wenn alle Zeilen mit Ausnahme der letzten 10 gelöscht werden, verbleiben in der Abfrage nur noch jene Werte, aus denen der Durchschnitt berechnet werden soll.” Und genau das können Sie so erreichen:
- Menü Zeilen verringern | Zeilen beibehalten | Letzte Zeilen beibehalten.
- Geben Sie bei Anzahl von Zeilen die Zahl 10 ein
- Bestätigen Sie mit OK.
Sie erkennen, dass exakt die letzten 10 Zeilen in der Abfrage erhalten geblieben sind.
Für den 30-Tage-Block zeige ich Ihnen eine andere Methode auf die vielleicht etwas aufwendiger ist aber dafür auch eine höhere Flexibilität gewährleistet. Außerdem können Sie das einfügen einer Index-Spalte in vielen anderen Situationen auch verwenden, darum halte ich diese Übung für ganz nützlich. 😎 Noch einmal zur Erinnerung: Es sollen die letzten 30 der hier aufgeführten 54 Tage zur Berechnung des Mittelwerts herangezogen werden. Sollten sich durch die Filterung weniger relevante Datensätze ergeben, so sind (nur) diese zu verwenden. (Die Regelung gilt auch für die 10-Tage Abfrage.) Gehen Sie dazu so vor:
- Aktivieren Sie die Abfrage für die 30 Tage.
- Erweitern Sie per die Überschrift Datum & Wochentag | Absteigend sortieren.
- Menü Spalte hinzufügen | den Punkt Indexspalte (erweitern) | Von 1.
- Wenn jetzt der Index mit 0 beginnt, weil sie zu schnell geklickt haben, 😎 löschen Sie im rechten Seitenfenster diesen Schritt und führen ihn noch einmal korrekt aus.
- Erweitern Sie die Spalte Index durch einen Klick auf .
- Zahlenfilter ► | Kleiner oder gleich… und die Zahl 30 in das Textfeld eingeben.
- Die Spalte Datum & Wochentag nun aufsteigend sortieren. Alternativ könnten sie natürlich auch die Spalte Index absteigend sortieren…
- Die Spalte Index hat ihren Zweck erfüllt und kann bzw. sollte gelöscht werden. Ich mache das durch Rechtsklick in die Überschrift und im Kontextmenü Entfernen.
Die Mittelwert-Berechnung
Jetzt geht es darum, das endgültige Ziel, jeweils den Mittelwert der beiden kalendarischen Blöcke zu berechnen. Da die Reihenfolge der Abarbeitung egal ist, können Sie auch in der Abfrage 30 Tage bleiben. Markieren Sie erforderlichenfalls die Spalte Wert. Wechseln Sie zum Menüpunkt Transformieren und Klicken in der Gruppe Zahlenspalte auf das Symbol Statistiken. Sie werden sich gewiss denken können, dass die Auswahl Mittelwert zum gewünschten Ergebnis führen wird. Dennoch wird sie die Darstellung des Ergebnisses vielleicht etwas irritieren:
Die Berechnung stimmt, aber die Darstellung ist ungewohnt…
Das Ergebnis ist garantiert korrekt, es muss nur noch so verarbeitet werden, dass es in ein Excel-Arbeitsblatt integriert werden kann. Ein Klick auf das erste Symbol in der Symbolleiste Zu Tabelle und die bislang unfangreichere Abfrage ist auf eine Zeile geschrumpft. Außerdem ist die einzig übrig gebliebene Spalte auch noch mit einer anderen Überschrift (Column1) versehen worden. Ein Klick in die Überschrift, F2 und Durchschnitt 30 Tage eintragen.
Für die Abfrage 10 Tage könnten Sie natürlich gleichermaßen vorgehen, ich zeige Ihnen jedoch eine 2. Möglichkeit auf:
- Aktivieren Sie die 10-Tage-Abfrage, beispielsweise durch einen Klick im linken Seitenfenster.
- Menü Spalte hinzufügen | Benutzerdefinierte Spalte.
- Belassen Sie es gerne bei Benutzerdefiniert in Neuer Spaltenname, tragen Sie bei Benutzerdefinierte Spaltenformel: folgendes ein:
"Mittelwert"(also mit den Anführungsstrichen).
Den Inhalt für die neue, benutzerdefinierte Spalte eingeben
- Rechtsklick in die Überschrift Benutzerdefiniert und Gruppieren nach…
- Schreiben sie hier bei Neuer Spaltenname den Text Mittelwert 10 Tage.
- Ändern Sie Vorgang auf Mittelwert.
- Wählen Sie den Eintrag Wert bei Spalte.
- Schließen Sie mit OK ab.
- Löschen Sie die erste Spalte der auf 1 Zeile „geschrumpften” Abfrage, sie wird nicht mehr benötigt.
Prinzipiell ist die Aufgabe damit gelöst. Für beide Zeiträume liegt das Ergebnis der Durchschnittsberechnung in jeweils einer getrennten Abfrage vor. Menü Datei | Schließen & laden und die beiden eben erstellten Abfragen werden in jeweils ein Tabellenblatt gespeichert.
Sinnvollerweise sollen die Ergebnisse jedoch auf dem ersten Tabellenblatt neben der Tabelle mit den Basisdaten erscheinen. Darum markieren Sie die erstellte Tabelle für die 10 Tage komplett (mit Überschrift) und StrgX, um den Bereich auszuschneiden. Wechseln Sie in das erste Arbeitsblatt, aktivieren Sie die Zelle, wo diese Tabelle abgelegt werden soll und StrgV zum einfügen der Daten. – Verfahren Sie mit dem Ergebnis der 30 Tage genauso. Die beiden nun leeren Tabellenblätter können Sie löschen.
Dynamik!
Für eine einmalige Aktion wäre das ganz schön viel Arbeit. Aus meiner Sicht wahrscheinlich zu viel. Aber es ist ja so, dass täglich neue Daten hinzukommen; an diesen Tagen bleibt dann entweder die Zelle der Spalte B leer bzw. wird mit einem - versehen oder ein User trägt einen numerischer Wert ein. Und im letztgenannten Fall kommt der Vorteil von Power Query voll zum tragen. Wenn die Quelldaten sich ändern wird sich ja auch der berechnete Mittelwert für die beiden Zeitspannen ändern.
Fügen Sie gerne in eine leere Zeile unterhalb des letzten numerischen Eintrages einen markant hohen Wert ein, beispielsweise 999. Dadurch wird mit Sicherheit der Mittelwert bei der Berechnung der Zeiträume deutlich verändert. Aber wie sie sehen: Sie sehen nichts. Die Ergebnisse der beiden Abfragen verändern sich nicht. Das ist gewollt und auch gut so, denn es ja durchaus möglich, dass mit den bisherigen Ergebnissen weitere Berechnungen erstellt werden sollen. Eine Aktualisierung der Abfrage-Ergebnisse muss „zu Fuß” angestoßen werden. Klicken Sie auf die jeweilige Ergebnis-Tabelle, Menüpunkt Abfrage und dort Aktualisieren. – Sollen alle Abfrage-Ergebnisse der Arbeitsmappe „auf einen Schlag” aktualisiert werden, dann geht das zur Zeit nur über eine kleine VBA-Routine.
In naher Zukunft ist geplant, einen zweiten Teil dieser Auswertung zu erstellen. Dann soll unter Verwendung von Power Query ein Mittelwert der einzelnen Wochntage erstellt werden. Dabei soll die Quell-Tabelle nicht um den berechneten Wochentag ergänzt sondern ausschließlich Power Query genutzt werden.