PQ: Letzter Wert jeder Zeile

Ab und zu wird in Ex­cel-Fo­ren die Fra­ge ge­stellt, wel­chen In­halt die let­zte Spal­te mit ei­nem beliebi­gen Wert hat. Lee­re Zel­len oder ein Leer­String ("") sol­len nicht berück­sichtigt wer­den. Da ich auf dem ge­mie­te­ten Web-Serv­er Platz spa­ren muss, habe ich Ih­nen hier eine Mus­ter-Da­tei im *.csv – For­mat bere­it ge­stellt. Und da es in Plain Ex­cel bere­its di­ver­se Lösun­gen in Fo­ren und auch im weit­eren Netz gibt habe ich mich für eine Pow­er Que­ry-Lö­sung entsch­ieden. Falls Sie Hil­fe beim Im­port in PQ brau­chen, schau­en Sie bit­te hier nach.

Se­hen Sie sich die 20 Dat­en-Zeilen ein­mal in Ruhe an und erken­nen Sie, dass unter­schiedliche Spal­ten den let­zten Wert der Zei­le enthal­ten und auch ganz lee­re Zei­len da­bei sind. Ich stel­le Ih­nen hier 2 sehr ähn­liche Lösun­gen vor, wel­che ‑jede für sich- ih­ren eige­nen Reiz hat. Für bei­de Lö­sungs­we­ge gilt, dass Sie nach dem Importhaupt­säch­lich  mit ein­er Arbeit­skopie arbeit­en.

Lö­sung 1

Nach dem Im­port in den PQ-Edi­tor er­stel­len Sie die Arbeit­skopie per Du­pli­zie­ren und benen­nen die­se gle­ich zu Let­zter­W­ertSpalte (Arbeit­skopie) um. Bis auf Wider­ruf wer­den Sie nur in die­ser Que­ry arbeit­en. – Mar­kie­ren Sie alle Spal­ten, Recht­sklick in eine der Über­schriften und Spal­ten ent­piv­otieren. Die ers­te Idee kön­nte sein, nach Attrib­ut zu Grup­pieren und das Max­i­mum bei Vor­gang und bei Spal­te  Wert aus­zu­wäh­len. Aber das ist ein Denk­fehler, denn das klappt zwar bei den ers­ten bei­den Zei­len (de ers­ten Grup­pe) aber beim näch­sten Block (Zei­le 2 bis Zei­le 10) stimmt das Ergeb­nis nicht. 🙁 

Also ist ein neu­er Denk­an­satz erforder­lich. Irgend­wie muss dem Attrib­ut noch die jew­eilige Zeilen­num­mer der Roh­dat­en zu­ge­wie­sen wer­den. Klick­en Sie dazu im recht­en Seit­en­fen­ster bei Angewen­dete Schrit­te auf den Schritt vor dem Ent­piv­otieren (also Geän­dert­er Typ) und wech­seln Sie im Men­üband zu Spal­te hinzufü­gen. Ein Klick auf Indexs­palte und nach der Bestä­ti­gung per Klick auf Ein­fü­gen ist die In­dex-Spal­te einge­fügt. Klick­en Sie im Seit­en­fen­ster wie­der auf die let­zte Zei­le der Lis­te und Über­raschung, es hat sich nichts ge­tan. 🙁 Der aus mein­er Sicht ein­fach­ste Weg ist nun, die Ent­piv­otieren-Zei­le zu lö­schen und jet­zt die Spal­te In­dex mar­kie­ren, Recht­sklick und An­de­re Spal­ten ent­piv­otieren. Und nun ist je­dem Wert in Attrib­ut eine Zeilen­zahl (In­dex) zuge­ord­net.

Durch das Ent­piv­otieren sind ja alle lee­ren Zel­len eli­m­iniert wor­den. Nun geht es dar­um, die jew­eils let­zte Zei­le ei­nes Blocks fest­zu­stel­len und ide­al­er­weise die an­de­ren Zei­len zu lö­schen. Die eine Mög­lich­keit wäre, jede Grup­pe in sich fort­laufend zu num­merieren und dann den höch­sten Wert her­auszu­fil­tern. Ich bie­te Ih­nen hier ei­nen an­de­ren Weg an:

• Wech­seln Sie zum Menü Trans­formieren
• Klick­en Sie in der Men­üband-Grup­pe Ta­bel­le auf den Ein­trag Zei­len um­keh­ren
• Mar­kie­ren Sie die Spal­te In­dex
• Recht­sklick in die Über­schrift und Dup­likate ent­fer­nen
• Um nun die Orig­i­nal-Rei­hen­folge her­zu­stel­len wie­der­um Zei­len um­keh­ren
• … und jet­zt die Spal­te Wert (irgend­wie) der Ursprungsta­belle anfü­gen.

„Irgend­wie” ist ja nun kei­ne detail­lierte Beschrei­bung. 😎 Und auf direk­tem Wege geht das auch nicht. Aber wenn Sie auch hier ei­nen In­dex, der mit Sicher­heit iden­tisch zu dem in der Arbeit­skopie erstell­ten In­dex ist er­stel­len, dann klappt das auch. Aus­nahm­sweise reicht es hier, dass Sie di­rekt mit dem Er­stel­len ei­nen (auch) null­basierten In­dex er­stel­len. Da­nach Start | Abfra­gen zusam­men­führen und wäh­len Sie als zwei­te Ab­fra­ge Let­zter­W­ertSpalte (Arbeit­skopie). Mar­kie­ren Sie in bei­den Abfrage-Fen­stern die Spal­te In­dex und bestäti­gen mit OK. Erweit­ern Sie die neu er­stell­te Spal­te durch ei­nen Klick auf und las­sen Sie im ge­sam­ten Dialogfen­ster auss­chließlich das Häk­chen bei Wert beste­hen. Als let­zten Schritt kön­nen Sie noch die Über­schrift auf (beispiel­sweise) Wert let­zte Spal­te än­dern. Und eine Sichtkon­trolle wird Ih­nen zei­gen, dass das Ergeb­nis kor­rekt ist, auch wenn ein­mal eine lee­re Zel­le mit­ten­drin ist oder gar kein Wert vorhan­den war. Je nach Wun­sch kön­nen Sie nun die Ab­fra­ge in ei­nem existieren­den oder ei­nem neu­en Abeits­blatt spe­ich­ern.

Hier noch ein Hin­weis: So ganz „sau­ber” ist die­se Vorge­hensweise mit dem nach­träg­lich einge­tra­ge­nen In­dex in der Basista­belle nicht. Prinzip­iell soll­te der In­dex dort vor dem Du­pli­zie­ren er­stellt wer­den; es gibt ei­ni­ge Sit­u­a­tio­nen, wo das son­st zu fal­schen Ergeb­nis­sen füh­ren kann. Oder Sie ge­hen den nach­fol­gend ge­zeig­ten Weg.

▲ nach oben …

Lö­sung 2

Wie ge­habt im­por­tie­ren Sie in ein­er neu­en Map­pe die *.csv. Im PQ-Edi­tor er­stel­len Sie nun eben­falls als ers­tes eine Arbeit­skopie. Aber Sie ge­hen nicht den Weg über Du­pli­zie­ren son­dern über Ver­weis. Auch hier ver­ge­be ich den Na­men Let­zter­W­ertSpalte (Arbeit­skopie). Sie se­hen, dass bei­de Abfra­gen iden­tisch sind. Wech­seln Sie nun zur Ur­sprungs-Ab­fra­ge und fü­gen Sie hier ei­nen In­dex ein. Wech­seln Sie nun zur Arbeit­skopie und sie­he da, wie von Geis­ter­hand ist nun auch hier die­se Spal­te vorhan­den. Pri­ma! Mar­kie­ren Sie hier nun In­dex und über ei­nen Recht­sklick An­de­re Spal­ten ent­piv­otieren. Da ich ger­ne dem Prin­zip fol­ge, der Ver­ar­bei­tungs­ge­schwin­dig­keit/Per­for­mance we­gen über­flüs­sige Dat­en so früh wie mög­lich zu ent­fer­nen, lö­sche ich jet­zt schon die Spal­te Attrib­ut. Nun wie­der Zei­len um­keh­ren, in der Spal­te In­dex Dup­likate ent­fer­nen und die ur­sprüng­li­che Rei­hen­folge wie­der her­stellen (auch wenn das gar nicht erforder­lich ist).

▲ nach oben …

Mög­lich­keit 1

Nun geht es dar­um, die bei­den Abfra­gen wie­der zusam­men­zufü­gen. Sie wech­seln also zur Ursprungsta­belle Let­zter­W­ertSpalte und Start | Abfra­gen zusam­men­führen. Und bei der Aus­wahl der zu verknüpfend­en Ta­bel­le kommt die gro­ße Über­raschung, denn die Arbeit­skopie ist nicht in der Aus­wahl ver­füg­bar. Und das liegt dar­an, dass kein Dup­likat er­stellt wor­den ist son­dern ein Ver­weis. Also ge­hen Sie über Ab­bre­chen, um den Dia­log zu schlie­ßen. Und jet­zt bie­ten sich Ih­nen 2 Wege an, die ich Ih­nen kurz vor­stel­le:

• Start | Abfra­gen zusam­men­führen  | Abfra­gen als neue Ab­fra­ge zusam­men­führen
• Aus­wahl der Ab­fra­ge Let­zter­W­ertSpalte
• In bei­den Abfra­gen In­dex mar­kie­ren
• … und den Dia­log per OK schlie­ßen.
• Die neu er­stell­te Spal­te per Klick auf den Dop­pelpfeil erweit­ern und auss­chließlich das Häk­chen bei Wert ste­hen las­sen.

Das Vorge­hen ent­spricht weit­ge­hend dem aus dem ers­ten Teil weit­er oben. Und auch hier wird die vi­su­el­le Kon­trolle zei­gen, dass die Wer­te stim­men. Das An­pas­sen der Über­schrift über­lasse ich Ih­nen nach eigen­em Ge­schmack. Zwei Anmerkun­gen sei­en mir an die­ser Stel­le ges­tat­tet: Es sind jet­zt 3 Abfra­gen im Edi­tor und das Ergeb­nis hat 20 Zei­len. Das ist selb­stver­ständlich? Na ja…

▲ nach oben …

Mög­lich­keit 2

Die zwei­te Mög­lich­keit der Lö­sung 2 ist et­was an­ders. Wie­der­um am Schnitt­punkt, wo die Dat­en zusam­menge­führt wer­den sol­len, blei­ben Sie in der Que­ry Let­zter­W­ertSpalte (Arbeit­skopie). Der Trans­parenz we­gen kön­nen Sie auch ger­ne die eben er­stell­te Ab­fra­ge lö­schen, da­mit wie­der nur 2 Abfra­gen ge­ge­ben sind. Sie blei­ben in die­ser Ar­beits-Ab­fra­ge und

• Start | Abfra­gen zusam­men­führen
• Aus­wahl der Ab­fra­ge Let­zter­W­ertSpalte
• In bei­den Abfra­gen In­dex mar­kie­ren
• … und den Dia­log per OK schlie­ßen.
• Die neu er­stell­te Spal­te per Klick auf den Dop­pelpfeil erweit­ern und nun auss­chließlich das Häk­chen bei In­dex ent­fer­nen.

Sie erken­nen, dass sich ei­gent­lich gar nicht so viel in der Vorge­hensweise geän­dert hat. Aber das Ergeb­nis ist auf den ers­ten Blick ziem­lich unstruk­turi­ert. In­dex und Wert ste­hen promi­nent ganz links und die Rei­hen­folge der Zei­len stimmt auch nicht so wirk­lich. 😎 Und es sind nur 18 Zei­len in der Ab­fra­ge! War­um feh­len 2 Zei­len und wel­che sind das? Nun ja, es sind ge­nau die 2 Zei­len, wo in den Ursprungs­dat­en kei­ne ein­zi­ge Spal­te ge­füllt war, alle Spal­ten enthiel­ten den Wert null. Das kön­nen Sie als Vor­teil oder auch als Nach­teil se­hen; eine Än­de­rung wäre mit et­was Auf­wand dur­chaus mög­lich. – Bleibt ei­gent­lich nur noch, die Que­ry nach In­dex auf­steigend zu sor­tie­ren, die­se Spal­te da­nach zu lö­schen und Wert an das Ende (als let­zte Spal­te) zu ver­schieben.

Wäh­rend Sie bei Lö­sung 1 und der Mög­lich­keit 1 bei der Lö­sung 2 die Aus­gabe der Dat­en auf die Spal­te mit dem berech­neten Wert be­schrän­ken kön­nten und die­se dann im Arbeits­blatt ne­ben die Excel-Ursprungs­dat­en plat­zie­ren kön­nten weil die Rei­hen­folge und die An­zahl der Ergeb­nisse ja gle­ich ist, wür­de die ver­kürz­te Darstel­lung der Mög­lich­keit 2 der gewoll­ten Aus­sage nicht ge­recht wer­den.

▲ nach oben …