xls & PQ: Summe, Minimum, Maximum, Mittelwert jeder Zeile berechnen (aggregieren)
Gegeben ist eine Tabelle mit Umsatz-Werten, hier auf unserem Server zum Download bereit liegend. In diesem Beispiel sind es erst einmal 5 Kalenderwochen und 6 Produktgruppen. Als Liste/Tabelle formatiert stellt sich das in Excel so dar:
Die Roh-Daten für diese Aufgabe
Wie in der Überschrift bereits angemerkt, soll für jede Produktgruppe die Summe, das Minimum, Maximum und der Durchschnitt berechnet werden. Das Ergebnis soll sich dann etwa so darstellen (vor der Berechnung):
Muster für das künftige Ergebnis
Plain Excel
Wie Sie auf dem Bild hierüber erkennen, ist das Gerüst für die Auswertung bereits vorhanden. Eine formatierte Tabelle, wo ich auch schon die Formatierung als Währung (derzeit für Sie nicht sichtbar) bereits vorgenommen habe. Die einzutragenden Formeln sind absolute Basics und sollten Ihnen bekannt sein. So tragen Sie folgende Formel in die entsprechenden Zellen ein:
- I1:
=SUMME(B2:F2) - J1:
=MIN($B2:$F2) - K1:
=MAX($B2:$F2)
- L1:
=MITTELWERT($B2:$F2)
Hinweis: Wenn Sie die Zell-Adressen nicht von Hand eintragen haben, sondern durch einen Klick auf die jeweilige Zelle der Spalte B und Spalte F, dann wird Excel automatisch den Bezeichner der Liste übernehmen: =SUMME(Tabelle1[@[KW 1]:[KW 5]]). Lassen Sie sich dadurch nicht irritieren.
Ich selber bin faul 😉 und klicke nach dem Eintrag der Formel in J1 in das Ausfüllkästchen, ziehe die Formel bis zur Spalte L nach rechts rüber und passe dann nur die eigentlichen Funktionsnamen an. Durch die Funktionalität der Intelligenten Tabelle werden die Formeln nach der Änderung automatisch nach unten ausgefüllt. Das Ergebnis ist korrekt und stellt sich so dar:
Die Lösung mit Plain Excel
Damit ist die Aufgabe im Basis-Excel gelöst und erfüllt. Zugegeben, es ist die einfachste der möglichen Varianten …
Power Query (Lösung 1)
Die erste Möglichkeit, die mir im Rahmen der Power Query-Lösung eingefallen ist, gehört nun nicht unbedingt zu den wirklich empfehlenswerten. Es gibt 2 entscheidende Einschränkungen: 1. ist diese Vorgehensweise bei größeren Datenmengen einfach unzumutbar und 2. sind die Formeln nun auch nicht jedermanns Sache. 🙄 Aber des Prinzips wegen zeige ich Ihnen den Lösungsweg hier auf, denn vielleicht lässt sich die eine oder andere Funktion einmal an anderer Stelle einsetzen.
Beginnen Sie damit, die (blaue) Tabelle1 in den Power Query-Editor zu importieren. Im Anschluss stellt sich das nun so dar:
Direkt nach dem Import im Power Query-Editor
Wählen Sie anschließend im Menü-Register Spalte hinzufügen | Benutzerdefinierte Spalte. Als Überschrift tragen Sie Summe ein und als Benutzerdefinierte Spaltenformel ein:
[KW 1]+[KW 2]+[KW 3]+[KW 4]+[KW 5]
wobei sie die Spaltennamen (Überschriften) jeweils aus dem rechten Kasten übernehmen und nur das + von Hand eingeben:
Fast wie in Plain Excel: Verknüpfung der Felder mittels Plus-Zeichen
Danach bestätigen Sie mit OK. Wie gesagt, bei 5, maximal 10 Positionen ist das noch tragbar. Bei 52 Kalenderwochen hört aber wirklich der Spaß auf. Da ist dann die Lösung 2 entschieden besser.
Um jeweils das Minimum, Maximum und den Durchschnitt jeder Zeile zu berechnen, gehen Sie ähnlich vor. Nur werden hier die einzelnen Spaltennamen nicht durch das + verbunden sondern es bedarf eines etwas aufwändigeren Funktionsnamen. Für das Minimum geht das beispielsweise so:
Die bereits eingetragene Formel im Dialog
Die Funktion an sich: List.Min({[KW 1],[KW 2],[KW 3],[KW 4],[KW 5]}) wobei sie hier stets auf die exakte Groß-Kleinschreibung achten müssen. – Hier bin ich ähnlich faul wie beim Beispiel in Plain Excel und ich kopiere die Formel in die Zwischenablage. Für das Maximum und den Mittelwert ändern Sie dann die Formel nach dem eintragen in das Feld der Benutzerdefinierte Spaltenformel so ab:
List.Max({[KW 1],[KW 2],[KW 3],[KW 4],[KW 5]})
bzw.
List.Average({[KW 1],[KW 2],[KW 3],[KW 4],[KW 5]})
Wenn Sie jetzt Schließen & laden Klicken, werden die ursprünglichen Werte der einzelnen Wochen ebenfalls mit in die Tabelle geschrieben. Das kann ganz sinnvoll sein, ist hier aber nicht Teil der eigentlichen Aufgabenstellung. Darum löschen Sie die Spalten KW 1 bis KW 5 und erst danach Schließen & laden oder Schließen & laden in…, um die Daten in eine Tabelle ‑gegebenenfalls an gewünschter Position- zu schreiben.
Power Query (Lösung 2)
Wie ich weiter oben schon angedeutet habe, diese zweite Lösung ist (aus meiner Sicht) um einiges leichter und auch eleganter. Beginnen Sie auch hier damit, die Basisdaten in eine Abfrage zu importieren. Falls Sie wieder beim Stand 0 anfangen, wird die Abfrage den Namen Tabelle1 bekommen, sonst wird es vermutlich der Name Tabelle1 (2) sein.
Nach dem Import wechseln Sie auch hier zum Register Spalte hinzufügen. Klicken Sie nun in die Überschrift KW 1, Shift (Großschreibung) und dann ein Klick in die Überschrift der letzten Datenspalte, hier also KW 5 oder wenn das volle Jahr eingetragen ist KW 52. Jetzt sind alle Spalten mit den Umsätzen markiert. Nun ein Klick auf Statistiken | Summe und es wird automatisch eine neue Spalte mit den entsprechenden Berechnungen erzeugt. Die automatisch vergebene Überschrift Addition habe ich in Summe geändert. – Für das Minimum, das Maximum und den Mittelwert ist das Vorgehen identisch, natürlich werden Sie sie entsprechenden Aggregat-Funktionen auswählen; die Spalten-Überschriften sind m.E. sogar passend. 😛
Schließen & laden in… um das Ergebnis an gewünschter Position zu platzieren und Sie sind fertig. Und das Ganze funktioniert natürlich auch, wenn sie einzelne Spalten per Strg aus dem Verbund herausnehmen; der Mittelwert wird dann auch nur von den markierten Spalten berechnet. Und noch ein Unterschied zu den Berechnungen in Plain Excel: Ist eine Zelle innerhalb der Markierung (wirklich) leer, enthält also in der Editor-Ansicht den Wert null, dann wird die jeweilige Zelle der Spalte auch nicht mit in die Berechnung einbezogen. Dadurch sind diese Ergebnisse automatisch korrekt; es wird nicht der Wert 0 in die Berechnung mit einbezogen.
Alle drei fertigen Lösungen liegen hier zum Download in 1 Arbeitsmappe bereit.