PQ: Bedingtes Minimum berechnen / filtern

Im Her­ber-Forum wurde vom Fragesteller Tom diese Frage gestellt:

Den Link habe ich in der zitierten Frage deak­tiviert, da ein direk­ter Zugriff nach einiger Zeit nicht mehr möglich ist. Die Kopie der Orig­i­nal-Datei find­en Sie in unserem Blog, wenn sie hier Klick­en. Die Prob­lem­beschrei­bung ist zwar nicht unbe­d­ingt mein Stil aber ich denke, dass Sie das Ziel trotz­dem klar erken­nen wer­den. Und die Anmerkung, dass das in VBA gelöst wer­den soll, habe ich geflissentlich „über­lesen”.  😎 

Nach­dem Sie die Datei geladen haben wer­den sie rasch fest­stellen, dass sie den Bere­ich A1:E12 nicht so ohne weit­eres in eine Intel­li­gente Tabelle kon­vertieren kön­nen. Obwohl kein Fil­ter geset­zt bzw. aktiviert ist, sind in Zeile 1 die Schalt­flächen zum Erweit­ern der Über­schrift einge­blendet und somit ist eine Fil­ter-Möglichkeit gegeben. Ide­al­er­weise wer­den Sie über das Menü Dat­en gehen und dort auf die Schalt­fläche Fil­tern Klick­en. Dadurch wer­den die Möglichkeit­en der Schnell­fil­terung und die Erweit­ern-Schalt­flächen ent­fer­nt. Sie markieren nun den Bere­ich A1:E12 und erstellen daraus per Tas­tenkom­bi­na­tion (StrgT oder StrgL) oder über das Menü eine Intel­li­gente Tabelle. Die Tabelle hat (natür­lich) Über­schriften.

Importieren Sie nun diese Tabelle in den Pow­er Query-Edi­tor. Dazu gehen Sie in das Menü Pow­er Query (Excel ²⁰¹⁰⁄₂₀₁₃) oder aber in das Menü Dat­en (alle neueren Ver­sio­nen) und anschließend ein Klick auf Aus Tabelle bzw. Von Tabelle. Das Ganze stellt sich nun so dar:

Die Dat­en direkt nach dem Import in den Abfrage-Edi­tor

Ein­er der eis­er­nen Grund­sätze ein­er ordentlichen Tabelle ist, dass keine Leerzeilen enthal­ten sein dür­fen. Darum wer­den sie im ersten Schritt dafür sor­gen, dass die leere Zeile unter­halb der Über­schriften ent­fer­nt wird. Dazu Klick­en sie im Reg­is­ter Start im Menüband auf das Sym­bol Zeilen ver­ringern und wählen dort Zeilen ent­fer­nen | Leere Zeilen ent­fer­nen.

Klick­en Sie nun in die Über­schrift Datum, wählen Sie das Reg­is­ter Trans­formieren und in der Gruppe Datums – & Uhrzeitspalte ein Klick auf Datum | Nur Datum. Dadurch wird die Uhrzeit (00:00 Uhr) aus dem Datum ent­fer­nt und es bleibt der reine Datum­swert erhal­ten. Das ist übri­gens nicht nur ein optis­ch­er Effekt son­dern verän­dert den Inhalt der Zellen. Anschließend einen Recht­sklick in die Über­schrift Uhrzeit, Typ ändern | Zeit und aus den Dez­i­malzahlen wird jew­eils die kor­re­spondierende Uhrzeit gener­iert. Und bei der Gele­gen­heit wird Ihnen vielle­icht auch auf­fall­en, dass die Zellen in der let­zten Spalte alle den Wert null enthal­ten. Diese Zellen sind wirk­lich leer und enthal­ten keinen durch eine Formel gener­ierten Leer­String "".

Die bis hier­her durchge­führten Schritte sind ‑mit Aus­nahme des Ent­fer­nens der Leerzeile- prinzip­iell für Pow­er Query nicht notwendig, sie erle­ichtern uns Men­schen aber doch etwas die Arbeit, weil die Optik eher den gewohn­ten Werten, dem Stan­dard-Ausse­hen entspricht.  😉 

Im näch­sten Schritt wer­den Sie nacheinan­der und in genau dieser Rei­hen­folge die Spal­ten Datum, Uhrzeit, Num­mer und Anzahl auf­steigend sortieren. Markieren Sie nun in dieser Rei­hen­folge die Spal­ten Num­mer und Anzahl. Reg­is­ter Spalte hinzufü­gen, Gruppe Aus Text | Spal­ten zusam­men­führen. Im Dia­log  kön­nen Sie es bei den Vor­gaben belassen und gle­ich auf OK Klick­en.

Derzeit sind es 10 Zeilen mit Dat­en; einige Zeilen sind in der Spalte Zusam­menge­führt dop­pelt, gle­iche Num­mer und gle­iche Anzahl. Recht­sklick in die Über­schrift der Spalte Zusam­menge­führt und dann Dup­likate ent­fer­nen. Der Effekt ist, dass nur noch 8 Zeilen übrig bleiben; bei den Dublet­ten ist jew­eils der erste Wert erhal­ten geblieben. – Diese Spalte hat ihren Zweck erfüllt, darum löschen Sie diese Spalte beispiel­sweise per Entf oder Recht­sklick in die Über­schrift und Ent­fer­nen.

Reg­is­ter Start, Gruppe Abfrage | Ver­wal­ten | Ver­weis und es wird eine neue Abfrage mit dem Namen Tabelle1 (2) erstellt. Gle­ichzeit­ig öffnet sich das linke Seit­en­fen­ster, wo bei­de Abfra­gen auch angezeigt wer­den. Ein Klick in die Über­schrift der Spalte Num­mer, Grup­pieren nach und belassen Sie es im Dia­log bei der Vor­gabe für die Spalte Num­mer. Bei Neuer Spal­tenname geben Sie beispiel­sweise Min­i­mum ein, bei Vor­gang wählen Sie Min. Im Drop­Down Spalte Klick­en Sie auf Anzahl, weil ja der kle­in­ste Wert der Spalte Anzahl je Num­mer selek­tiert wer­den soll. Nach einem OK ist die Tabelle doch ziem­lich geschrumpft:

Sehr „über­sichtlich”… 😎

Diese Query ist nun die Basis für das endgültige Ergeb­nis. Sie bleiben im Reg­is­ter Start und wählen im Menüband die Schalt­fläche Kom­binieren | Abfra­gen zusam­men­führen und es öffnet sich der Zusam­men­führen-Dia­log. Im Drop­Down zwis­chen den großen Rah­men wählen Sie die Abfrage Tabelle1. Klick­en Sie nun in bei­den Abfrage-Bere­ichen in die Spalte Num­mer, worauf die bei­den Spal­ten markiert wer­den. Anschließend Strg und im oberen Kas­ten Min­i­mum und unten auch per Strg Anzahl markieren:

Im Zusam­men­führen-Dia­log sind die Markierun­gen gemacht

Join-Art kann so bleiben, denn grund­sät­zlich stim­men ja die bei­den Felder aus Tabelle1 (2) mit dem gewün­scht­en Ergeb­nis übere­in. Also mit OK bestäti­gen und den Dia­log schließen. In der Tabelle wird einen neue Spalte erzeugt, deren einziger Inhalt der Begriff Table ist. Erweit­ern Sie die Über­schrift durch einen Klick auf den Dop­pelpfeil  und ent­fer­nen Sie auss­chließlich das Häkchen bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den:

Diese Ein­stel­lung ist eine der Möglichkeit­en…

Der wohl ein­fach­ste Weg ist nun gewiss, die bei­den ersten Spal­ten Num­mer und Min­i­mum zu ent­fer­nen und dann Nummer.1 dahinge­hend umzube­nen­nen, dass der Anhang .1 ent­fer­nt wird. Das geht beispiel­sweise per Dop­pelk­lick oder F2. Jet­zt ein Klick in Schließen & laden und in je einem neuen Arbeits­blatt wer­den die bei­den Tabellen gespe­ichert.

Falls Sie ein wenig „aufräu­men” wollen, kön­nen Sie die gefilterte Tabelle aus dem Arbeits­blatt Tabelle3 auss­chnei­den und an beliebiger Stelle in beispiel­sweise Tabelle1 ein­fü­gen. Und im recht­en Seit­en­fen­ster ein Recht­sklick auf den Ein­trag Tabelle1, Laden in… und Nur Verbindung erstellen. Nach der Bestä­ti­gung des Warn-Dialogs kön­nen Sie dieses Arbeits­blatt und eventuell auch das dritte Work­Sheet löschen, falls Sie die Tabelle ver­schoben hat­ten.

Wenn jet­zt Änderun­gen in der (ursprünglichen) Fil­terung der Roh-Dat­en vorgenom­men wer­den oder diese sich verän­dern, dann reicht ein Klick auf Aktu­al­isieren und das Ergeb­nis wird „wie von Geis­ter­hand” angepasst. 💡 

▲ nach oben …