PQ: Bedingtes Minimum berechnen / filtern

Foren Geflüster: Aus einer Liste in das früheste Minimum einer Nummer-Spalte filtern

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

Im Herber-Forum wurde vom Fragesteller Tom diese Frage gestellt:

Hallo zusammen,

ich möchte gerne nach dem Minimum in einer Spalte, in Abhängigkeit einer zweiten Spalte filtern und anschließend die Zeilen, die nicht benötigt werden, ausblenden um ein „übersichtlicheres Diagramm“ erstellen zu können.
Ich habe eine Tabelle angehängt, in der das Ganze näher beschrieben ist.
Gelöst werden soll es in VBA!

Ich hoffe, ihr könnt mir irgendwie weiterhelfen!

http://www.herber.de/bbs/user/127316.xlsx 

Liebe Grüße

Den Link habe ich in der zitierten Frage deaktiviert, da ein direkter Zugriff nach einiger Zeit nicht mehr möglich ist. Die Kopie der Original-Datei finden Sie in unserem Blog, wenn sie hier klicken. Die Problembeschreibung ist zwar nicht unbedingt mein Stil aber ich denke, dass Sie das Ziel trotzdem klar erkennen werden. Und die Anmerkung, dass das in VBA gelöst werden soll, habe ich geflissentlich „überlesen“.  😎 

Nachdem Sie die Datei geladen haben werden sie rasch feststellen, dass sie den Bereich A1:E12 nicht so ohne weiteres in eine Intelligente Tabelle konvertieren können. Obwohl kein Filter gesetzt bzw. aktiviert ist, sind in Zeile 1 die Schaltflächen zum Erweitern der Überschrift eingeblendet und somit ist eine Filter-Möglichkeit gegeben. Idealerweise werden Sie über das Menü Daten gehen und dort auf die Schaltfläche Filtern klicken. Dadurch werden die Möglichkeiten der Schnellfilterung und die Erweitern-Schaltflächen entfernt. Sie markieren nun den Bereich A1:E12 und erstellen daraus per Tastenkombination (StrgT oder StrgL) oder über das Menü eine Intelligente Tabelle. Die Tabelle hat (natürlich) Überschriften.

Importieren Sie nun diese Tabelle in den Power Query-Editor. Dazu gehen Sie in das Menü Power Query (Excel 2010/2013) oder aber in das Menü Daten (alle neueren Versionen) und anschließend ein Klick auf Aus Tabelle bzw. Von Tabelle. Das Ganze stellt sich nun so dar:

Die Daten direkt nach dem Import in den Abfrage-Editor

Die Daten direkt nach dem Import in den Abfrage-Editor

Einer der eisernen Grundsätze einer ordentlichen Tabelle ist, dass keine Leerzeilen enthalten sein dürfen. Darum werden sie im ersten Schritt dafür sorgen, dass die leere Zeile unterhalb der Überschriften entfernt wird. Dazu klicken sie im Register Start im Menüband auf das Symbol Zeilen verringern und wählen dort Zeilen entfernen | Leere Zeilen entfernen.

Klicken Sie nun in die Überschrift Datum, wählen Sie das Register Transformieren und in der Gruppe Datums – & Uhrzeitspalte ein Klick auf Datum | Nur Datum. Dadurch wird die Uhrzeit (00:00 Uhr) aus dem Datum entfernt und es bleibt der reine Datumswert erhalten. Das ist übrigens nicht nur ein optischer Effekt sondern verändert den Inhalt der Zellen. Anschließend einen Rechtsklick in die Überschrift Uhrzeit, Typ ändern | Zeit und aus den Dezimalzahlen wird jeweils die korrespondierende Uhrzeit generiert. Und bei der Gelegenheit wird Ihnen vielleicht auch auffallen, dass die Zellen in der letzten Spalte alle den Wert null enthalten. Diese Zellen sind wirklich leer und enthalten keinen durch eine Formel generierten Leerstring "".

Die bis hierher durchgeführten Schritte sind -mit Ausnahme des Entfernens der Leerzeile- prinzipiell für Power Query nicht notwendig, sie erleichtern uns Menschen aber doch etwas die Arbeit, weil die Optik eher den gewohnten Werten, dem Standard-Aussehen entspricht.  😉 

Im nächsten Schritt werden Sie nacheinander und in genau dieser Reihenfolge die Spalten Datum, Uhrzeit, Nummer und Anzahl aufsteigend sortieren. Markieren Sie nun in dieser Reihenfolge die Spalten Nummer und Anzahl. Register Spalte hinzufügen, Gruppe Aus Text | Spalten zusammenführen. Im Dialog  können Sie es bei den Vorgaben belassen und gleich auf OK klicken.

Derzeit sind es 10 Zeilen mit Daten; einige Zeilen sind in der Spalte Zusammengeführt doppelt, gleiche Nummer und gleiche Anzahl. Rechtsklick in die Überschrift der Spalte Zusammengeführt und dann Duplikate entfernen. Der Effekt ist, dass nur noch 8 Zeilen übrig bleiben; bei den Dubletten ist jeweils der erste Wert erhalten geblieben. – Diese Spalte hat ihren Zweck erfüllt, darum löschen Sie diese Spalte beispielsweise per Entf oder Rechtsklick in die Überschrift und Entfernen.

Register Start, Gruppe Abfrage | Verwalten | Verweis und es wird eine neue Abfrage mit dem Namen Tabelle1 (2) erstellt. Gleichzeitig öffnet sich das linke Seitenfenster, wo beide Abfragen auch angezeigt werden. Ein Klick in die Überschrift der Spalte Nummer, Gruppieren nach und belassen Sie es im Dialog bei der Vorgabe für die Spalte Nummer. Bei Neuer Spaltenname geben Sie beispielsweise Minimum ein, bei Vorgang wählen Sie Min. Im DropDown Spalte klicken Sie auf Anzahl, weil ja der kleinste Wert der Spalte Anzahl je Nummer selektiert werden soll. Nach einem OK ist die Tabelle doch ziemlich geschrumpft:

Sehr "übersichtlich"…

Sehr „übersichtlich“… 😎

Diese Query ist nun die Basis für das endgültige Ergebnis. Sie bleiben im Register Start und wählen im Menüband die Schaltfläche Kombinieren | Abfragen zusammenführen und es öffnet sich der Zusammenführen-Dialog. Im DropDown zwischen den großen Rahmen wählen Sie die Abfrage Tabelle1. Klicken Sie nun in beiden Abfrage-Bereichen in die Spalte Nummer, worauf die beiden Spalten markiert werden. Anschließend Strg und im oberen Kasten Minimum und unten auch per Strg Anzahl markieren:

Im Zusammenführen-Dialog sind die Markierungen gemacht

Im Zusammenführen-Dialog sind die Markierungen gemacht

Join-Art kann so bleiben, denn grundsätzlich stimmen ja die beiden Felder aus Tabelle1 (2) mit dem gewünschten Ergebnis überein. Also mit OK bestätigen und den Dialog schließen. In der Tabelle wird einen neue Spalte erzeugt, deren einziger Inhalt der Begriff Table ist. Erweitern Sie die Überschrift durch einen Klick auf den Doppelpfeil Doppelpfeil und entfernen Sie ausschließlich das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden:

Diese Einstellung ist eine der Möglichkeiten…

Diese Einstellung ist eine der Möglichkeiten…

Der wohl einfachste Weg ist nun gewiss, die beiden ersten Spalten Nummer und Minimum zu entfernen und dann Nummer.1 dahingehend umzubenennen, dass der Anhang .1 entfernt wird. Das geht beispielsweise per Doppelklick oder F2. Jetzt ein Klick in Schließen & laden und in je einem neuen Arbeitsblatt werden die beiden Tabellen gespeichert.

Falls Sie ein wenig „aufräumen“ wollen, können Sie die gefilterte Tabelle aus dem Arbeitsblatt Tabelle3 ausschneiden und an beliebiger Stelle in beispielsweise Tabelle1 einfügen. Und im rechten Seitenfenster ein Rechtsklick auf den Eintrag Tabelle1, Laden in… und Nur Verbindung erstellen. Nach der Bestätigung des Warn-Dialogs können Sie dieses Arbeitsblatt und eventuell auch das dritte Worksheet löschen, falls Sie die Tabelle verschoben hatten.

Wenn jetzt Änderungen in der (ursprünglichen) Filterung der Roh-Daten vorgenommen werden oder diese sich verändern, dann reicht ein Klick auf Aktualisieren und das Ergebnis wird „wie von Geisterhand“ angepasst. 💡 

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Daten zusammenführen, Datentyp anpassen, Datum & Zeit, Filtern & Sortieren, Foren-Q&A, Power Query abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.