Mittelwert – Spezial (2)

Zusammenfassung: 2. Teil der Mittelwertberechnungen per Power Query mit definierten Bedingungen. Hier: Mittelwert jedes Wochentages sowie auch die Berechnung der Summe der Wochentage.


Mittelwerte der einzelnen Wochentage

Aufbauend auf diesem Beitrag ist eine weitere Aufgabenstellung, dass der Mittelwert jedes Wochentages, wo ein numerischer Wert eingetragen worden ist, berechnet werden soll. Und da die grundlegenden Filter-Funktionen bereits in Power Query durchgeführt worden sind (im 1. Teil), können auch gleich diese Daten dafür verwendet werden. Wenn Sie die Tabelle derzeit nicht zur Hand haben, filtern Sie bitte diese Daten dergestalt, dass alle Zellen der Spalte B entfernt werden, die entweder leer sind oder ein enthalten.

Die einfachste Möglichkeit wäre gewiss, aus den Daten der Abfrage Alle Tage eine PivotTable zu erstellen und hierüber den Mittelwert zu generieren. Allerdings werden sie in der Original-Tabelle (oder einer Kopie davon) eine neue Spalte mit dem jeweiligen Wochentag als Text generieren müssen, bevor eine vernünftige Auswertung möglich ist. Und zugegeben, in vielen Fällen ist es gewiss die einfachste und komfortabelste Lösung. Aber da sie mit diesen Daten nun schon einmal mit Power Query angefangen haben und vielleicht auch noch mehr mit Bearbeitungen über dieses Tool „anstellen“ wollen, zeige ich Ihnen eine Lösung nur mit Power Query auf.

Der Einstieg in dieser Aufgabe ist -wie bereits oben erwähnt- die Tabelle mit den gefüllteten Daten des 2. Halbjahres 2017. Und es sollen 7 Mittelwerte berechnet werden, jeweils Montag .. Sonntag. Wie gehabt werden sie zu Beginn die Tabelle in den Abfrage-Editor laden. Falls noch nicht geschehen, ändern Sie den Datentyp der 1. Spalte (Datum & Wochentag) auf Datum. Anschließend fügen Sie hier im Editor eine neue Spalte ein, wo der Wochentag als Text eingetragen wird (imPrinzip also auch eine Hilfsspalte):

  • Menü Spalte hinzufügen | Benutzerdefinierte Spalte.
  • Bei Neuer Spaltenname tragen Sie Wochentag ein.
  • In das große Textfeld Benutzerdefinierte Spaltenformel: geben Sie nach dem = folgende Formel in exakt dieser Groß- Kleinschreibung ein:
    DayOfWeekName(
    dann ein Doppelklick auf Datum & Wochentag im rechten Kasten des Dialogs und anschließend die schließende runde Klammer ). Die komplette Formel sieht nun so aus:
    = Date.DayOfWeekName([#"Datum & Wochentag"])

Nach einem OK hat Power Query eine neue Spalte mit der Überschrift Wochentag generiert und in jeder Zeile steht der Text des entsprechenden Wochentages. Aktivieren Sie den Menüpunkt Start, Gruppe Transformieren Symbol Gruppieren nach. Da die Spalte mit dem Wochentag noch aktiviert ist (erkennbar in der grünen Hinterlegung), wird bei Gruppieren nach auch bereits diese Spalte ausgewählt. Bei Neuer Spaltenname geben Sie beispielsweise Mittelwert Wochentage ein und bei Vorgang wählen Sie Mittelwert aus. Bleibt noch das Feld Spalte, hier soll selbstredend der Durchschnitt der Spalte Wert berechnet werden. Nach einem Klick auf OK bleibt prinzipiell nur noch Schließen & laden übrig.

Warum „prinzipiell“? Nun ja, spätestens jetzt werden sie erkennen, dass die Reihenfolge der Sortierung in der erzeugten Tabelle etwas eigenwillig ist. Und obwohl Excel normalerweise auch die Reihenfolge der Wochentage sortieren kann, ist es hier nicht so ganz ohne Umwege möglich. Es sollte ein Weg gefunden werden, damit die Daten auch nach einer Neuberechnung wieder in gewohnter Reihenfolge Montag bis Sonntag angeordnet sind. Dazu wechseln sie wieder in die Abfrage, zum Modus Bearbeiten. Bei Angewendete Schritte markieren Sie durch einen einfachen Klick die vorletzte Zeile Hinzugefügte Benutzerdefinierte… und fügen erneut eine Benutzerdefinierte Spalte hinzu. Die Sicherheitsabfrage Schritt einfügen bestätigen Sie mit Einfügen. Der Spaltenname kann beispielsweise #KW sein und die Formel lautet:
= Date.DayOfWeek([#"Datum & Wochentag"])
wobei Sie diese auf gleiche Weise wie vorher erstellen. Sie kennen vielleicht den Satz: „Vertrauen ist gut, Kontrolle ist besser.“ Also sollten Sie feststellen, ob die Nummerierung auch tatsächlich deutschen Gegebenheiten noch nicht der US-Norm entspricht. Der 1. Tag der Woche muss ja der Montag sein, nicht der Sonntag.

Vielleicht werden Sie nun denken, dass Power Query die Woche mit einem Dienstag beginnen lässt, weil dort die Zahl 1 berechnet worden ist. Aber ich versichere Ihnen, das ist korrekt. Denn im Gegensatz zu Excel beginnt eine Nummerierung in Power Query stets mit 0 und nicht mit 1.

Sortieren Sie nun die Spalte #KW aufsteigend und sie werden sofort erkennen, dass alles seine Richtigkeit hat. Wenn Sie nun im rechten Seitenfenster auf Gruppierte Zeilen klicken, werden Sie immer noch die bisherige Reihenfolge der Sortierung sehen. Aber es gibt (natürlich) einen Ausweg: In der Markierung im rechten Seitenfenster erkennen sie neben dem Text Gruppierte Zeilen ganz rechts ein graues Stern-Symbol. Ein Klick darauf und es öffnet sich wieder der Dialog für das Gruppieren. Aktivieren Sie zu Beginn ganz oben das Optionsfeld Erweitert und sie erkennen eine neue Schaltfläche Gruppierung hinzufügen. Klicken Sie darauf und wählen Sie im 2. Listenfeld den Eintrag #KW. Alle weiteren Einstellungen können so bleiben; einfach nur ein Klick auf OK.

… Immer noch die alte Sortierreihenfolge 😥 . Aber es ist wirklich nur noch ein kleiner Schritt zum Ziel. Sortieren Sie die Spalte #KW aufsteigend und löschen Sie diese Spalte gleich im Anschluss. Schließen & laden und das Wunschergebnis ist nun wirklich und auch endgültig erreicht.

Und wo wir ja gerade schon einmal beim Gruppieren sind stellt sich doch die Frage ob es auch möglich ist, die Summe der Umsätze pro Wochentag zu berechnen. Und das sogar in der gleichen Abfrage in einer weiteren Spalte. Nun ja, der Weg dahin ist mit wesentlich weniger Dornen ausgestattet als der bisherige. Markieren Sie wiederum den Schritt Gruppierte Zeilen, Klick auf das Stern-Symbol rechts und im Dialog finden Sie unterhalb Neuer Spaltenname die Schaltfläche Aggregation hinzufügen, worauf Sie nun auch klicken. Als Neuer Spaltenname beispielsweise Summe, bei Vorgang wählen Sie ebenfalls Summe aus und bei Spalte verwenden Sie naturgenäß Wert weil ja genau diese Spalte berechnet werden soll. Nach dem Abschluss noch einmal auf Schließen & laden und das Ergebnis steht in der 2. berechneten Spalte.

▲ nach oben …

 
Dieser Beitrag wurde unter Allgemein, Verschiedenes, Filtern & Sortieren, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M), Rechnen & Zahlen abgelegt und mit , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar