Mittelwert – Spezial (2)

Zusam­men­fas­sung: 2. Teil der Mit­tel­w­ert­berech­nun­gen per Pow­er Query mit definierten Bedin­gun­gen. Hier: Mit­tel­w­ert jedes Wochen­t­ages sowie auch die Berech­nung der Summe der Wochen­t­age.

Mittelwerte der einzelnen Wochentage

Auf­bauend auf diesem Beitrag ist eine weit­ere Auf­gaben­stel­lung, dass der Mit­tel­w­ert jedes Wochen­t­ages, wo ein numerisch­er Wert einge­tra­gen wor­den ist, berech­net wer­den soll. Und da die grundle­gen­den Fil­ter-Funk­tio­nen bere­its in Pow­er Query durchge­führt wor­den sind (im 1. Teil), kön­nen auch gle­ich diese Dat­en dafür ver­wen­det wer­den. Wenn Sie die Tabelle derzeit nicht zur Hand haben, fil­tern Sie bitte diese Dat­en dergestalt, dass alle Zellen der Spalte B ent­fer­nt wer­den, die entwed­er leer sind oder ein – enthal­ten.

Die ein­fach­ste Möglichkeit wäre gewiss, aus den Dat­en der Abfrage Alle Tage eine Piv­ot­Table zu erstellen und hierüber den Mit­tel­w­ert zu gener­ieren. Allerd­ings wer­den sie in der Orig­i­nal-Tabelle (oder ein­er Kopie davon) eine neue Spalte mit dem jew­eili­gen Wochen­tag als Text gener­ieren müssen, bevor eine vernün­ftige Auswer­tung möglich ist. Und zugegeben, in vie­len Fällen ist es gewiss die ein­fach­ste und kom­fort­a­bel­ste Lösung. Aber da sie mit diesen Dat­en nun schon ein­mal mit Pow­er Query ange­fan­gen haben und vielle­icht auch noch mehr mit Bear­beitun­gen über dieses Tool „anstellen” wollen, zeige ich Ihnen eine Lösung nur mit Pow­er Query auf.

Der Ein­stieg in dieser Auf­gabe ist ‑wie bere­its oben erwäh­nt- die Tabelle mit den gefüll­teten Dat­en des 2. Hal­b­jahres 2017. Und es sollen 7 Mit­tel­w­erte berech­net wer­den, jew­eils Mon­tag .. Son­ntag. Wie gehabt wer­den sie zu Beginn die Tabelle in den Abfrage-Edi­tor laden. Falls noch nicht geschehen, ändern Sie den Daten­typ der 1. Spalte (Datum & Wochen­tag) auf Datum. Anschließend fügen Sie hier im Edi­tor eine neue Spalte ein, wo der Wochen­tag als Text einge­tra­gen wird (imPrinzip also auch eine Hil­f­ss­palte):

• Menü Spalte hinzufü­gen | Benutzerdefinierte Spalte.
• Bei Neuer Spal­tenname tra­gen Sie Wochen­tag ein.
• In das große Textfeld Benutzerdefinierte Spal­tenformel: geben Sie nach dem = fol­gende Formel in exakt dieser Groß- Klein­schrei­bung ein:
  DayOfWeekName(
  dann ein Dop­pelk­lick auf Datum & Wochen­tag im recht­en Kas­ten des Dialogs und anschließend die schließende runde Klam­mer ). Die kom­plette Formel sieht nun so aus:
  = Date.DayOfWeekName([#"Datum & Wochentag"])

Nach einem OK hat Pow­er Query eine neue Spalte mit der Über­schrift Wochen­tag gener­iert und in jed­er Zeile ste­ht der Text des entsprechen­den Wochen­t­ages. Aktivieren Sie den Menüpunkt Start, Gruppe Trans­formieren Sym­bol Grup­pieren nach. Da die Spalte mit dem Wochen­tag noch aktiviert ist (erkennbar in der grü­nen Hin­ter­legung), wird bei Grup­pieren nach auch bere­its diese Spalte aus­gewählt. Bei Neuer Spal­tenname geben Sie beispiel­sweise Mit­tel­w­ert Wochen­t­age ein und bei Vor­gang wählen Sie Mit­tel­w­ert aus. Bleibt noch das Feld Spalte, hier soll selb­stre­dend der Durch­schnitt der Spalte Wert berech­net wer­den. Nach einem Klick auf OK bleibt prinzip­iell nur noch Schließen & laden übrig.

Warum „prinzip­iell”? Nun ja, spätestens jet­zt wer­den sie erken­nen, dass die Rei­hen­folge der Sortierung in der erzeugten Tabelle etwas eigen­willig ist. Und obwohl Excel nor­maler­weise auch die Rei­hen­folge der Wochen­t­age sortieren kann, ist es hier nicht so ganz ohne Umwege möglich. Es sollte ein Weg gefun­den wer­den, damit die Dat­en auch nach ein­er Neu­berech­nung wieder in gewohn­ter Rei­hen­folge Mon­tag bis Son­ntag ange­ord­net sind. Dazu wech­seln sie wieder in die Abfrage, zum Modus Bear­beit­en. Bei Angewen­dete Schritte markieren Sie durch einen ein­fachen Klick die vor­let­zte Zeile Hinzuge­fügte Benutzerdefinierte… und fügen erneut eine Benutzerdefinierte Spalte hinzu. Die Sicher­heitsabfrage Schritt ein­fü­gen bestäti­gen Sie mit Ein­fü­gen. Der Spal­tenname kann beispiel­sweise #KW sein und die Formel lautet:
= Date.DayOfWeek([#"Datum & Wochentag"])
wobei Sie diese auf gle­iche Weise wie vorher erstellen. Sie ken­nen vielle­icht den Satz: „Ver­trauen ist gut, Kon­trolle ist bess­er.” Also soll­ten Sie fest­stellen, ob die Num­merierung auch tat­säch­lich deutschen Gegeben­heit­en noch nicht der US-Norm entspricht. Der 1. Tag der Woche muss ja der Mon­tag sein, nicht der Son­ntag.

Vielle­icht wer­den Sie nun denken, dass Pow­er Query die Woche mit einem Dien­stag begin­nen lässt, weil dort die Zahl 1 berech­net wor­den ist. Aber ich ver­sichere Ihnen, das ist kor­rekt. Denn im Gegen­satz zu Excel begin­nt eine Num­merierung in Pow­er Query stets mit 0 und nicht mit 1.

Sortieren Sie nun die Spalte #KW auf­steigend und sie wer­den sofort erken­nen, dass alles seine Richtigkeit hat. Wenn Sie nun im recht­en Seit­en­fen­ster auf Grup­pierte Zeilen Klick­en, wer­den Sie immer noch die bish­erige Rei­hen­folge der Sortierung sehen. Aber es gibt (natür­lich) einen Ausweg: In der Markierung im recht­en Seit­en­fen­ster erken­nen sie neben dem Text Grup­pierte Zeilen ganz rechts ein graues Stern-Sym­bol. Ein Klick darauf und es öffnet sich wieder der Dia­log für das Grup­pieren. Aktivieren Sie zu Beginn ganz oben das Options­feld Erweit­ert und sie erken­nen eine neue Schalt­fläche Grup­pierung hinzufü­gen. Klick­en Sie darauf und wählen Sie im 2. Lis­ten­feld den Ein­trag #KW. Alle weit­eren Ein­stel­lun­gen kön­nen so bleiben; ein­fach nur ein Klick auf OK.

… Immer noch die alte Sortier­rei­hen­folge 😥 . Aber es ist wirk­lich nur noch ein klein­er Schritt zum Ziel. Sortieren Sie die Spalte #KW auf­steigend und löschen Sie diese Spalte gle­ich im Anschluss. Schließen & laden und das Wun­schergeb­nis ist nun wirk­lich und auch endgültig erre­icht.

Und wo wir ja ger­ade schon ein­mal beim Grup­pieren sind stellt sich doch die Frage ob es auch möglich ist, die Summe der Umsätze pro Wochen­tag zu berech­nen. Und das sog­ar in der gle­ichen Abfrage in ein­er weit­eren Spalte. Nun ja, der Weg dahin ist mit wesentlich weniger Dor­nen aus­ges­tat­tet als der bish­erige. Markieren Sie wiederum den Schritt Grup­pierte Zeilen, Klick auf das Stern-Sym­bol rechts und im Dia­log find­en Sie unter­halb Neuer Spal­tenname die Schalt­fläche Aggre­ga­tion hinzufü­gen, worauf Sie nun auch Klick­en. Als Neuer Spal­tenname beispiel­sweise Summe, bei Vor­gang wählen Sie eben­falls Summe aus und bei Spalte ver­wen­den Sie natur­genäß Wert weil ja genau diese Spalte berech­net wer­den soll. Nach dem Abschluss noch ein­mal auf Schließen & laden und das Ergeb­nis ste­ht in der 2. berech­neten Spalte.

▲ nach oben …