Messwerte – Maximum aus (beispielsweise) 25er-Blöcken mit Power Query berechnen
Hinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.
Beachten Sie bitte: Dieses Begleit-Video enthält zusätzliche Informationen, die im folgenden Beitrag nicht dokumentiert sind!
Aus einer beliebig großen Liste, die auch schon einmal über 1.000.000 Messwerte umfassen kann, sollen (beispielsweise) 25 er-Blöcke gebildet und aus jedem dieser logischen Einheiten soll der Maximal-Wert in einer getrennten Spalte ausgegeben werden. Und da die 1- Mio.-Grenze überschritten werden kann, bedarf es entweder Power Query oder Power Pivot, um derartige Datenmengen verarbeiten zu können. Wichtig ist nur, dass das Ergebnis in das Arbeitsblatt passt. 😉
In dieser Beispiel-Datei sind 500 unterschiedliche Messwerte erfasst. Beispielsweise im gleichen Blatt in den Spalten D:E sollen die numerischen Eckwerte der Blocknummern (1 – 25, 26 – 50, …) sowie das jeweilige Maximum des Bereichs angezeigt werden. Je nach Wunsch kann auch die Spalte C mit der fortlaufenden Nummer der Blöcke gefüllt werden. Im Endeffekt soll das dann so aussehen, eventuell auch ohne die Spalte D:
Ergebnis der Berechnung
Wie immer werden sie als 1. Schritt die vorhandenen Daten in eine formatierte Liste/Tabelle umwandeln StrgL, StrgT oder Als Tabelle formatieren im Menü Start sind Ihnen dabei dienlich. Anschließend importieren Sie diese Tabelle durch einen Klick auf Aus Tabelle in den Power Query Editor.
Im folgenden Schritt werden Sie die 25 er-Blöcke definieren und generieren. Dazu wechseln Sie in das Menü-Register Spalte hinzufügen und im Menüband erweitern Sie die Auswahl Indexspalte 
durch einen Klick auf das Dreieck. Nun ein Klick auf Von 1 und automatisch wird eine Spalte mit der Überschrift Index und dem Startwert 1 erstellt. Dieser Schritt ist wichtig, da sie in Power Query zwar die Zeilennummer angezeigt bekommen darauf aber nicht direkt zugreifen können, wie beispielsweise in Excel mit der Funktion ZEILE(). Dieser Weg hat außerdem auch noch den Vorteil, dass der Wert auch nach einem Sortiervorgang bestehen bleibt. Die eigentliche Blockbildung erreichen Sie, wenn Sie im gleichen Menüband auf Benutzerdefinierte Spalte Klicken und dort als Neuer Spaltenname beispielsweise Block eintragen und dann folgende Benutzerdefinierte Spaltenformel in exakt dieser Groß-Kleinschreibung tippen:
Number.RoundUp([Index]/25)
wobei Sie die Spaltenüberschrift [Index] am besten durch einen Doppelklick auf den Spaltennamen im rechten Kasten übernehmen. Nach einem OK wird eine weitere Spalte mit jeweils 25 Zahlen der Block-Nummer automatisch erstellt. Das ist die Basis für eine Gruppierung.
Wechseln Sie zum Register Start, lassen Sie die Spalte Block markiert und wählen dort Gruppieren nach. Block ist in der 1. Auswahl korrekt, als Neuer Spaltenname verwende ich Max oder Maximum/Block und bei Vorgang wähle ich natürlich Max. Das war’s auch schon. Das Ergebnis ist eine 2‑spaltige Tabelle, wo in der 1. Spalte (Block) die fortlaufende Block-Nummer steht und in der 2. Spalte (Maximum/Block) der Höchstwert des jeweiligen 25er-Blocks.
Für die „schmale” Version war es das dann auch schon. Schließen & laden und anschließend können Sie die gespeicherte Tabelle an den Wunschort verschieben.
Als Option bietet sich natürlich noch an, den numerischen Bereich unter der Überschrift Von .. Bis oder Range darzustellen. Und natürlich sollen die Eckwerte nicht von Hand eingegeben, sondern der Power Query berechnet werden. Und zugegeben, das ist schon etwas anspruchsvoller, wenn Sie es aus eigener Initiative und ohne Hilfe erstellen wollen. Die gleich folgende Formel werden sie wiederum über Spalte hinzufügen | Benutzerdefinierte Spalte einfügen. Setzen Sie sich mit der gleich aufgeführten Formel intensiv auseinander oder übernehmen Sie den Formel-Text einfach, um zum Ziel zu gelangen:
Text.From(([Block]-1)*25) & " - " & Text.From([Block]*25)
und verschieben sie diese Spalte an die Position 2; ich mache das direkt per Drag and Drop, sie können das aber auch per Rechtsklick und dem entsprechenden Kontextmenü oder über das Menü-Register Transformieren erledigen.
Die Aufgabe ist damit auch im 2., dem optionalen Teil erfolgreich erledigt. Bei Bedarf noch einmal Schließen & laden, um den neuesten Stand in der Tabelle zu fixieren. – Dass es mit anderen Aggregat-Funktionen wie Minimum, Summe, Mittelwert, etc. gleichermaßen funktioniert, bedarf keiner besonderen Erwähnung.