PQ: Liste gegen Kreuztabelle abgleichen

  Wis­sens­stand: Lev­el 2 ⇒ So­li­des Basiswis­sen in Ex­cel, et­was Er­fah­rung in PQ   

Die (bere­its for­matierte) Orig­i­nal-Ta­bel­le

In ei­nem Ex­cel-Fo­rum wur­de die An­fra­ge ge­stellt wie es mög­lich sei, die Lis­te in den Spal­ten A:D mit den Dat­en der Kreuzta­belle in den Spal­ten F:I abzu­gle­ichen. In der Spal­te D (check) soll spä­ter ein Hin­weis ste­hen, ob die Rech­nungs­dat­en in (A:C) iden­tisch sind mit den Men­gen, die aus dem La­ger aus­ge­bucht wor­den sind (F:I). Da ein solch­er Abgle­ich werk­täglich stat­tfind­en soll, bie­tet sich Pow­er Que­ry als Werk­zeug für die wie­der­keh­ren­de Auswer­tung an.

Und die bei­den Daten­bere­iche sind bere­its (in die­ser Da­tei) jew­eils als Intel­li­gente Ta­bel­le mit dem Na­men Ta­bel­le1 und Ta­bel­le2 for­matiert. Klick­en Sie zu­erst in die Lis­te (links) und im­por­tie­ren Sie die­se in den Pow­er Que­ry-Edi­tor. Der Name der Ab­fra­ge ist na­tur­ge­mäß Ta­bel­le1. Erweit­ern sie im Men­üband den Text Schlie­ßen & la­den und wäh­len Sie Schlie­ßen & La­den in… | Nur Ver­bin­dung er­stel­len.

Im­por­tie­ren Sie an­schlie­ßend auch die Kreuzta­belle, wel­che dann im Edi­tor natür­lich den Na­men Ta­bel­le2 be­kommt. Aus Grün­den der Trans­parenz soll­ten Sie die bei­den Abfra­gen umbe­nen­nen in Rech­nung (die Lis­te) und La­ger (die Kreuzta­belle). Spe­ich­ern Sie die­se Ab­fra­ge auf die gle­iche Wei­se wie die ers­te Que­ry.

Öff­nen Sie wie­der die Ab­fra­ge Rech­nung. Ih­nen wird sich­er auf­fall­en, dass in der Spal­te Da­tum jed­er ein­zel­ne Datum­swert mit der Uhr­zeit 00:00:00 einge­tra­gen ist. Der Daten­typ die­ser Spal­te ist Da­tum/Uhr­zeit. Und da in die­ser Spal­te nur das rei­ne Da­tum ge­braucht wird, än­dern Sie den Daten­typ auf (nur) Da­tum. Die Spal­te check wird zwar prinzip­iell ge­braucht aber es ist we­sent­lich effizien­ter, die­se nur durch Pow­er Que­ry er­stel­len und berech­nen zu las­sen. Und da es nicht mög­lich ist, in ein­er (1) Ab­fra­ge zwei Spal­ten mit dem gle­ichen Na­men zu ha­ben, lö­schen Sie die exis­tie­ren­de, zur­zeit lee­re Spal­te. Da­mit ist das Prob­lem be­ho­ben. 😉 

Erweit­ern Sie das lin­ke Seit­en­fen­ster durch ei­nen Klick auf den Text Abfra­gen. Klick­en Sie auf die Ab­fra­ge La­ger, um die­se zur ak­tu­el­len Que­ry zu ma­chen. Im ers­ten Schritt wer­den sie in PQ aus der Kreuzta­belle eine Ta­bel­le in Form ein­er Lis­te er­stel­len. Dazu mar­kie­ren Sie die Spal­te Kennnum­mern und füh­ren ei­nen Recht­sklick in der Über­schrift durch. Im Kon­textmenü wäh­len Sie den Punkt An­de­re spal­ten ent­piv­otieren. Da­durch entste­ht aus den mar­kier­ten Dat­en eine drei Spal­ten um­fas­sen­de Ab­fra­ge:

Die Ab­fra­ge nach dem ent­piv­otieren

Ein Klick in die Wer­te der Spal­te Attrib­ut wird Ih­nen zei­gen, dass die Dat­en zwar wie ein Da­tum ausse­hen je­doch vom Daten­typ Text sind. Wan­deln Sie die­se Dat­en in ein “echt­es” Da­tum um. Der Klar­heit we­gen kön­nen Sie die Über­schrift die­ser Spal­te auch in Da­tum än­dern, auch wenn es nicht wirk­lich erforder­lich ist. (Sie wer­den et­was spä­ter erken­nen, war­um…)

Mar­kie­ren Sie (in die­ser Rei­hen­folge) Kennnum­mer und Da­tum. Sym­bol Grup­pieren nach und sor­gen Sie da­für, dass in den un­te­ren Text­fel­dern die­se Wer­te ste­hen:

• Neu­er Spal­tenname: Sum­me,
• Vor­gang: Sum­me,
• Spal­te: Wert.

Die­se Wer­te aus­wäh­len bzw. ein­tra­gen

• Schlie­ßen Sie das Fen­ster per OK.
• Wech­seln Sie an­schlie­ßend wie­der zur Ab­fra­ge Rech­nung.
• Ak­ti­vie­ren sie erforder­lichen­falls und das Reg­is­ter Home/Start. Kom­binieren und erweit­ern Sie die Aus­wahl Abfra­gen zusam­men­führen.
• Klick­en sie nun auf Abfra­gen als neue Ab­fra­ge zusam­men­führen.
• Wäh­len Sie im Dia­log als zwei­te Ab­fra­ge La­ger.
• Mar­kie­ren Sie in bei­den Abfra­gen in ge­nau die­ser Rei­hen­folge: Da­tum und Kennnum­mer.

Und das Ergeb­nis ist eine neue Ab­fra­ge mit dem Na­men Mer­ge1, wo in der neu­en, let­zten Spal­te (La­ger) der In­halt jed­er einzel­nen Zei­le nur Ta­ble ist.

Erweit­ern Sie die Über­schrift La­ger durch ei­nen Klick in den Dop­pelpfeil . Im Dia­log ent­fer­nen Sie alle Häk­chen (ein­schließlich ganz un­ten Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den) je­doch nicht das Häk­chen bei Sum­me:

Nur die­se Häk­chen sol­len üb­rig blei­ben

Nach ei­nem OK ist die­se Spal­te zu Sum­me umbe­nan­nt wor­den und ent­hält jene Wer­te, die in der Grup­pierung agg­re­giert wor­den sind. An die­ser Stel­le be­nen­ne ich die Ab­fra­ge um, sie be­kommt den Na­men Abgle­ich.

Nun Spal­te hinzufü­gen | Bed­ingte Spal­te und tra­gen Sie im Dia­log fol­gende Wer­te ein:

Die­se Wer­te soll­ten vor ei­nem OK dort ste­hen

• Neu­er Spal­tenname: Abgle­ich,
• Spal­tenname: Men­ge,
• Oper­a­tor: ist gle­ich,
• Wert: Hier zu­erst auf das Sym­bol Klick­en und Spal­te aus­wäh­len mar­kie­ren, dann Sum­me,
• Aus­gabe: OK und
• Son­st: Feh­ler!.

Nach dem Schlie­ßen des Fen­sters hat Pow­er Que­ry die Spal­te Abgle­ich er­stellt und dort bei Übere­in­stim­mung der bei­den vorheri­gen Spal­ten den Wert OK einge­tra­gen, bei Dif­feren­zen den Text Feh­ler!. Sin­nvoller­weise wer­den sie nun die let­zte Spal­te so fil­tern, dass nur die Zei­len mit dem Feh­ler ste­hen blei­ben.

Im let­zten Akt soll­ten Sie die Über­schriften Sum­me zu Rech­nung und Abgle­ich zu La­ger umbe­nen­nen da­mit je­dem, der die Dat­en er­hält, so­fort klar ist, wor­um es in der an­schlie­ßend erstell­ten Ta­bel­le im End­ef­fekt geht. 😎 

Sie blei­ben in der Ab­fra­ge Abgle­ich. Da­tei | Schlie­ßen & la­den und Pow­er Que­ry schreibt in ein neu er­stell­tes Tabel­len­blatt eine Intel­li­gente Ta­bel­le mit den eben ge­fil­ter­ten Dat­en. Natür­lich hät­ten sie auch Schlie­ßen und La­den in… ver­wen­den und den Ziel­ort für die zu schrei­ben­de Da­tei sel­ber bes­tim­men kön­nen. Da­mit ist die Auf­gabe end­gül­tig er­le­digt.

▲ nach oben …