In ei­nem Fo­rum (Her­ber, Link-Zu­griff lei­der nicht mehr mög­lich) wur­de un­ter dem Ti­tel „Ta­bel­len zusam­men­fassen” eine schein­bar recht ein­fache Fra­ge ge­stellt. Zumin­d­est auf den ers­ten Blick. Beim zweit­en Hin­se­hen zeig­te sich, dass doch et­was mehr „Gehirn­schmalz” erforder­lich ist, um eine einiger­maßen „schlan­ke” und auch nachvol­lziehbare Lö­sung zu find­en.

Da­mit Sie gle­ich jene Da­tei zur Ver­fü­gung ha­ben, wel­che ich für die­ses für die­se Muster­lö­sung ver­wende, la­den Sie ide­al­er­weise die­ses File von un­se­rem Serv­er her­un­ter. Wenn sie nun im Fo­rum den ers­ten Bei­trag zum The­ma durch­le­sen wer­den sie erken­nen, dass in den Zei­len 2:12 die auszuw­er­tenden Dat­en ste­hen, in Zei­le 1 (zumin­d­est teil­weise) die Über­schriften. In den Zei­len 14:21 ist das Wun­schergeb­nis des Fra­ge­stel­lers dar­ge­stellt.

Da es ein „un­ge­schrie­be­nes Ge­setz” (grund­sät­zlich eine Voraus­set­zung) ist, dass in ein­er Lis­te jede Spal­te eine ein­deutige Über­schrift ha­ben muss, habe ich in den ers­ten drei Spal­ten ein­fach eine ei­ge­ne, hof­fentlich sin­nvolle Über­schrift einge­fügt: Spal­te A: Song, Spal­te_B: Band/In­ter­pret und Spal­te C ein­fach ein Frageze­ichen ?, da ich die Bedeu­tung nicht ken­ne. Das stellt sich nun so dar:

Die Roh-Dat­en vor dem Im­port

… Und da ja bekan­ntlich vie­le Wege nach Rom füh­ren, stel­le ich Ih­nen hier die­sen oder je­nen Weg zum Er­pro­ben vor.

Vor­ar­beit für jede der Mög­lich­kei­ten

Im ers­ten Schritt wer­den Sie eine be­lie­bi­ge Zel­le der Roh­dat­en mar­kie­ren und StrgT oder StrgL Klick­en, um dar­aus eine so genan­nte Intel­li­gente Ta­bel­le zu ma­chen. Den gle­ichen Ef­fekt erre­ichen Sie, wenn sie im Reg­is­ter Start Grup­pe For­matvor­la­gen auf das Sym­bol Als Ta­bel­le for­matieren Klick­en. Und die­se Ta­bel­le wer­den Sie jet­zt in den Pow­er Que­ry-Edi­tor im­por­tie­ren. Das geht am bes­ten, wenn Sie entwed­er das Reg­is­ter Pow­er Que­ry (Ex­cel ²⁰¹⁰⁄₁₃) bzw. in neue­ren Ver­sio­nen das Reg­is­ter Dat­en ak­ti­vie­ren und dort auf Von Ta­bel­le bzw. Aus Ta­bel­le Klick­en. Die Dat­en wer­den in den Edi­tor im­por­tiert und es stellt sich nun so dar:

Die Dat­en sind in den Pow­er Que­ry-Edi­tor im­por­tiert

Die For­de­rung des Fra­ge­stel­lers das war ja, dass zu­erst die Spal­te 1 (Song) und an­schlie­ßend die Spal­te 2 (Band/In­ter­pret) jew­eils auf­steigend sor­tiert wer­den soll. Dar­um Klick­en Sie zu­erst in die Spal­te Song, erweit­ern die Über­schrift durch ei­nen Klick auf  und sor­tie­ren die Dat­en auf­steigend. An­schlie­ßend gle­icher­maßen die Spal­te Band/In­ter­pret auf­steigend sor­tie­ren. Sie erken­nen, dass die­se Vorge­hensweise an­ders ist als in Ex­cel, in Pow­er Que­ry wird stets die logis­che Rei­hen­folge des Sor­tie­ren angewen­det.  😎 

Falls Sie sich wun­dern, dass ich trotz offen­sichtlich beste­hen­der kor­rek­ter Sor­tie­rung die­sen Vor­gang noch ein­mal durch­führe, dann hat es dur­chaus sei­nen Grund: Zu oft er­le­be ich, dass irgend­wann neue Dat­en hinzuge­fügt wer­den und die Sor­tie­rung dann nicht mehr so ist, wie sie sein soll. Auf die­se Wei­se er­le­digt Pow­er Que­ry für Sie die Ar­beit ganz al­lei­ne bei je­dem Aktu­al­isieren der Da­tei.

▲ nach oben …

(1) Si­cher ist Si­cher

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

Sie ken­nen das vielle­icht von Flug­hä­fen oder an­de­ren sicher­heit­srel­e­van­ten Insti­tu­tio­nen, die Sicher­heit­skon­trollen sind für alle Be­tei­lig­ten im­mer mit et­was mehr Auf­wand ver­bun­den aber da­für gilt der Satz „safe­ty first” (Sicher­heit geht vor). Und so ist es auch hier in Pow­er Que­ry. Et­was mehr Auf­wand, da­für aber die Sicher­heit, dass nicht irgend­wo ein Maus­klick ver­ges­sen wird und es da­durch zu ver­fälschen Ergeb­nis­sen kommt. Und im End­ef­fekt bie­tet solch eine Lö­sung fast im­mer mehr Flex­i­bil­ität für kün­ftige Wün­sche, wel­che mit die­sen Basis­dat­en ver­wirk­licht wer­den sol­len.

Der aus mein­er Sicht sich­er­ste Weg geht über den Vor­gang des Ent­piv­otieren und an­schlie­ßend (nach ein­er Bear­beitung) wie­der Piv­otieren der Dat­en. Und das gro­ße „Aber” da­bei ist, dass sie ide­al­er­weise ei­ni­ge Ken­nt­nisse in Pow­er Que­ry ha­ben soll­ten oder wenn sie Ein­steiger in Sa­chen PQ sind, ein gerüt­telt Maß an Lern­bere­itschaft mit­brin­gen. Aber wie ge­sagt, die­ser Weg ist sich­er, wenn Sie die fol­gende An­wei­sung haar­klein nachvol­lziehen. – Und im kom­menden Ab­schnitt ist auch ein Weg für Ein­steiger be­schrie­ben.

Um mir die fol­gen­den Schrit­te et­was leich­ter zu gestal­ten, füh­re ich die Spal­ten Song und Band/In­ter­pret der­art zusam­men, dass sie in ein­er einzi­gen Spal­te ste­hen und mit Tab­stopp als Trennze­ichen verse­hen sind. Den automa­tisch gener­ierten Na­men Zusam­menge­führt be­las­se ich der Klar­heit we­gen. In ein­er der let­zten Schrit­te bekom­men die wie­der getren­nten Spal­ten dann die ur­sprüng­li­chen Beze­ich­nun­gen zu­rück.

Nun mar­kie­ren Sie die Spal­te Zusam­menge­führt, Recht­sklick in die Über­schrift und an­schlie­ßend An­de­re Spal­ten ent­piv­otieren. Im An­schluss stellt sich das so dar:

Die Dat­en di­rekt nach dem ent­piv­otieren

Die­sen Stand in der Ab­fra­ge wer­den sie spä­ter noch ein­mal brau­chen. Von die­ser Que­ry stel­len Sie vor weit­eren Schrit­ten ein Dup­likat auf der Ba­sis des Ver­weis­es. Klick­en Sie zu Be­ginn in den Strei­fen links der Ab­fra­ge (in den Text Abfra­gen) und es öff­net sich ein Seit­en­fen­ster. Recht­sklick in den Na­men des einzi­gen Ein­trags Ta­bel­le1, Ver­weis und es wird automa­tisch eine neue Ta­bel­le mit dem Na­men Ta­bel­le1 (2) er­stellt. An der Mar­kie­rung so­wie im recht­en Seit­en­fen­ster erken­nen Sie, dass die neue Ta­bel­le auch die ak­tu­el­le ist. Fil­tern Sie nun die Spal­te Attrib­ut so, dass nur die Zei­len mit dem Ein­trag ? Sicht­bar sind.

Mar­kie­ren Sie die Spal­te Zusam­menge­führt, Grup­pieren nach und im Dia­log ge­ben Sie als Neu­er Spal­tenname beispiel­sweise Sum­me ein, bei Vor­gang wäh­len Sie eben­falls Sum­me. Als Spal­te wäh­len Sie na­tur­ge­mäß die Spal­te Wert. Schlie­ßen Sie den Dia­log mit OK. Sie erken­nen, dass die iden­tis­chen Zei­len aus Zusam­menge­führt zu 1 Zei­le zusam­menge­fasst wor­den sind und die in der Spal­te Sum­me ent­spre­chend ad­diert wor­den sind:

Grup­piert und ad­diert, die Wer­te zwei­er Spal­ten

Nach die­sem ers­ten Schritt ist es an der Zeit, die Abfra­gen zu sich­ern. Mar­kie­ren Sie dazu Ab­fra­ge1, Da­tei, Schlie­ßen & la­den in… | Nur Ver­bin­dung. Öff­nen Sie an­schlie­ßend wie­der Ab­fra­ge1 auf be­lie­bi­ge Wei­se. 

Er­stel­len Sie er­neut ein Ver­weis-Dup­likat der Ta­bel­le1, wel­che jet­zt automa­tisch den Na­men Ta­bel­le1 (3) er­hält. Fil­tern Sie hier die Spal­te Attrib­ut so, dass die Zei­len mit dem In­halt ? nicht mehr an­ge­zeigt wer­den (und auch nicht mehr in der Ab­fra­ge enthal­ten sind). An die­ser Stel­le ist es beson­ders deut­lich sicht­bar, dass beim Ent­piv­otieren jene Spal­ten nicht über­nom­men wor­den sind, wo auss­chließlich null enthal­ten war, die also kom­plett leer wa­ren bzw. sind. Durch Ihr Tun sind jet­zt nur noch die Spal­ten 1977 und 1978 dar­ge­stellt, er­fasst wor­den.

Mar­kie­ren Sie die Spal­ten Zusam­menge­führt und Attrib­ut, dann ein Klick auf Grup­pieren nach. Bei Neu­er Spal­tenname ge­ben Sie beispiel­sweise Sum­me ein, un­ter Vor­gang wäh­len Sie eben­falls Sum­me. Im Feld Spal­te wäh­len Sie wie beim vorheri­gen Bei­spiel Wert und schlie­ßen an­schlie­ßend den Dia­log. Und das Ergeb­nis stellt sich nun so dar:

Grup­pieren, die­ses Mal mit ei­nem an­de­ren Ergeb­nis

Sie wer­den jet­zt schon bei ein­er klei­nen Stich­probe fest­stellen, dass die Wer­te dem Wun­sch-Ergeb­nis des Fra­ge­stel­ler es ent­spre­chen. Im näch­sten Schritt wer­den die Dat­en wie­der piv­otiert, also in eine Kreuzta­belle über­tra­gen. Mar­kie­ren Sie dar­um jene Spal­te, wel­che die Über­schriften ent­hält: Attrib­ut. An­schlie­ßend ein Wech­sel zum Reg­is­ter Trans­formieren. Wäh­len Sie hier in der Grup­pe Be­lie­bi­ge Spal­te das Sym­bol für Piv­otieren . Im Dia­log än­dern Sie die Wer­te-Spal­te auf Sum­me und an­schlie­ßend OK. Wenn sie nun in Ge­dan­ken die drit­te Spal­te der Roh­dat­en (?) aus­blenden, dann stimmt das Ergeb­nis weitest­ge­hend mit dem Wun­sch-Ergeb­nis übere­in. (Ja, die ers­te Spal­te muss natür­lich noch ge­teilt wer­den, klar…)

Nun wer­den Sie die feh­len­de Spal­te in ge­nau die­se Ab­fra­ge ein­fü­gen. Zu Be­ginn acht­en Sie dar­auf, dass die Ab­fra­ge1 (3) ak­ti­viert ist. Start, Kom­binieren | Abfra­gen zusam­men­führen | Abfra­gen als neue Ab­fra­ge zusam­men­führen. Im Dia­log Zusam­men­führen wäh­len Sie im mit­tleren, klei­nen Kom­bi­na­tions­feld den Wert Ta­bel­le1 (2). Ver­knüp­fen Sie nun im obe­ren als auch un­te­ren Block die bei­den Fel­der Zusam­menge­führt, in­dem sie jew­eils in die Über­schrift oder ein be­lie­bi­ges Feld in der Spal­te Klick­en. Das stellt sich nun so dar:

Der ers­te Schritt beim Zusam­men­führen der bei­den Abfra­gen

Der vorgeschla­gene Wert bei Join-Art kann so blei­ben. Nach dem Schlie­ßen des Dia­logs sieht die Ab­fra­ge so aus:

Vielle­icht nicht Ih­ren Erwartun­gen ent­spre­chend, aber kor­rekt

Sie erken­nen, dass im An­schluss an die bish­erige let­zte Spal­te eine neue Spal­te mit dem Na­men der Ab­fra­ge Ta­bel­le1 (2) hin­zu gekom­men ist. In jed­er Zei­le der Spal­te ste­ht Ta­ble (Ta­bel­le) als In­halt. Erweit­ern Sie nun die Über­schrift in die­ser Spal­te durch ei­nen Klick auf den Dop­pelpfeil . Ent­fer­nen Sie im Dialogfen­ster das Häk­chen bei Zusam­menge­führt und unter­halb des Kas­tens bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den. Der ein­zig mar­kier­te Wert An­zahl wird so­fort der In­halt der ak­tu­el­len Spal­te sein. Zie­hen Sie die­se Spal­te An­zahl an die 2. Posi­tion von links (also vor 1977).

Bleibt noch die Tren­nung der zu­vor zusam­menge­führten zwei Spal­ten. Mar­kie­ren Sie dazu die Spal­te Zusam­menge­führt, Start und Spal­te tei­len | Nach Trennze­ichen. Die Vor­gabe ist der Tab­stopp und Sie wer­den sich erin­nern, dass das ge­nau das Trennze­ichen beim Zusam­men­fü­gen der Spal­ten war. Also akzep­tieren Sie das und schlie­ßen den Dia­log.

Bin­go! So soll es sein, das Ergeb­nis ist abso­lut dem Wun­sch ent­spre­chend. Jet­zt bleibt nur noch, die Que­ry via Schlie­ßen & la­den in ein neu­es Sheet zu schrei­ben oder per Schlie­ßen & la­den in… an be­lie­bi­ger Posi­tion zu plat­zie­ren. – Mir ist be­wusst, dass auf die­se Wei­se nur die Jah­re dar­ge­stellt wer­den, wo auch Dat­en enthal­ten sind. Es wäre dur­chaus mög­lich, auch die „lee­ren” Jah­re im Ergeb­nis mit dar­zu­stel­len, aber ein­er­seits ist das nicht unbe­d­ingt aus­sagekräftig und ander­er­seits war es nicht ge­for­dert (wo­bei ich da­von aus­ge­he, dass in der Orig­i­nal-Datei alle Spal­ten Dat­en enthal­ten).

▲ nach oben …

(2) Grup­pie­ren, Grup­pie­ren

  Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, kei­ne/kaum Er­fah­rung   

Die­ser Ab­schnitt ist ei­gent­lich nur ent­standen, weil im Fo­rum je­mand schrieb, dass die Lö­sung „nur” mit der Funk­tion­al­ität des Grup­pierens, also ohne (ent)pi­vo­tie­ren mög­lich sei. Es gab ei­ni­ges an Wider­spruch ander­er Hel­fer und das hat mei­nen Ehr­geiz anges­tachelt …  😎 Ich räu­me aber auch ein, dass ich mir das et­was ein­fach­er vor­ge­stellt hat­te, ohne die erforder­lichen Um­we­ge. Aber schau­en Sie selb­st. Und im End­ef­fekt sieht die Lö­sung ganz ein­fach aus. Wie so oft im Le­ben, „man” muss nur wis­sen, wie es geht…  💡 

Wie Sie die Basis-Dat­en in den Pow­er Que­ry-Edi­tor im­por­tie­ren, ist bere­its ganz zu Be­ginn die­ses Bei­tra­ges detail­liert erk­lärt. Was den ers­ten Schritt, das Zusam­men­führen der bei­den ers­ten Spal­ten bet­rifft: Den Punkt bzw. Schritt kön­nen und soll­ten Sie hier auch ger­ne anwen­den, ob­wohl es auch ei­nen et­was aufwändi­geren Weg ohne das zusam­men­führen und spä­te­rem entkop­peln der bei­den Spal­ten gibt. Da wür­den Sie dann in der Grup­pierung stets bei­de Spal­ten mar­kie­ren. – Zu Be­ginn stellt sich die Ab­fra­ge so dar:

Um die bei­den Spal­ten Song und Band/In­ter­pret zusam­men­zuführen, mar­kie­ren Sie erforder­lichen­falls erst die Spal­te Song und da­nach Shift und ein Klick in die Über­schrift der Spal­te Band/In­ter­pret. Wech­seln Sie nun zum Reg­is­ter Trans­formieren und wäh­len dort in der Grup­pe Text­spal­te den Ein­trag Spal­ten zusam­men­führen. Im Dia­log wäh­len Sie im Kom­bi­na­tions­feld Trennze­ichen den Ein­trag Tab­stopp. Bei Neue Spal­tenname (option­al) ist der Name Zusam­menge­führt vorgeschla­gen, da­bei kön­nen, soll­ten Sie es be­las­sen. Das Ergeb­nis stellt sich zu Be­ginn so dar:

Die bei­den ers­ten Über­schriften sind zu 1 Spal­te zusam­menge­fasst

Mar­kie­ren Sie nun auss­chließlich die ers­te Spal­te Zusam­menge­führt. Wäh­len Sie im Men­üband des Sym­bol Grup­pieren nach, un­ten tra­gen in das Feld Neu­er Spal­tenname beispiel­sweise Sum­me ein. Sie kön­nten auch ein ? schrei­ben, denn hier wird ge­nau die­se Spal­te sum­miert. Ich blei­be aber im Script beim von mir vorge­se­henen Na­men. Bei Vor­gang wäh­len Sie im Kom­bi­na­tions­feld na­tur­ge­mäß auch Sum­me und bei Spal­te wäh­len Sie ?, denn die­se Spal­te soll für je­den der Be­grif­fe der Spal­te Zusam­menge­führt die Addi­tion der einzel­nen Wer­te repräsen­tieren. Oder an­ders aus­ge­drückt: Gle­iche In­hal­te in der ers­ten Spal­te wer­den zusam­menge­fasst und sum­miert aus­gegeben, ein­zel­ne Wer­te blei­ben na­tur­ge­mäß so, wie sie der­zeit sind.

Wenn Sie jet­zt das Dia­log-Fen­ster mit OK jet­zt schlie­ßen wür­den, dann sähe das Ergeb­nis so aus:

Das Ergeb­nis stimmt, aber …

Die Zah­len stim­men. Aber es feh­len natür­lich ganz entschei­dende Spal­ten, die Jah­res-Zah­len. Auf je­den Fall sind es die Wer­te jen­er Jah­re, die nicht auss­chließlich lee­re Fel­der enthal­ten. Dar­um ist noch ein weit­er­er Schritt im Grup­pieren-Dia­log erforder­lich. – So­weit Sie den Dia­log nicht ge­schlos­sen und den Text hier­über auss­chließlich zur Ken­nt­nis genom­men ha­ben, ge­hen Sie di­rekt zum fol­gen­den Ab­satz und füh­ren die dargestell­ten Schrit­te aus. Wenn Sie ein we­nig exper­i­men­tiert ha­ben, um das Ergeb­nis nachvol­lziehen zu kön­nen, dann Klick­en Sie in der let­zten Zei­le des Bere­ichs Angewen­dete Schrit­te im recht­en Seit­en­fen­ster auf das Zah­n­rad-Sym­bol rechts und der Dia­log wird sich im vorheri­gen Zus­tand wie­der öff­nen:

„Darf es et­was mehr sein?” 😉

Auf den ers­ten Blick scheint es so zu sein, dass sich dort nicht viel än­dern lässt. Aber wenn sie ge­nau hin­se­hen wer­den sie erken­nen, dass di­rekt un­ter der Über­schrift Grup­pieren nach das Options­feld Stan­dard mar­kiert ist; da­ne­ben gibt es aber auch noch das Feld Weit­ere. Ge­nau die­ses wer­den sie nun per Klick ak­ti­vie­ren. Das Fen­ster ver­größert sich und zusät­zlich zur Schalt­fläche Grup­pierung hinzufü­gen gibt es im un­te­ren Bere­ich eine weit­ere, neue Schalt­fläche: Aggre­ga­tion hinzufü­gen. Ein Klick dar­auf wird unter­halb der eben aus­ge­füll­ten Zei­le mit den drei Fel­dern eine neue Zei­le mit der gle­ichen An­zahl von Fel­dern Fel­dern dar­stel­len.

Wie zu­vor bere­its ein­mal ange­merkt, muss je­des Feld in ein­er Lis­te (und natür­lich auch auch in Pow­er Que­ry) ei­nen ein­deuti­gen Na­men ha­ben. Dar­um ge­ben Sie in die­se neue Zei­le un­ter Neu­er Spal­tenname eine be­lie­bi­ge Beze­ich­nung ein. Ich ver­wende dazu meis­tens nur ein -, weil ich die­sem Feld­na­men Prinzip­iell nicht mehr ver­wen­den wer­de (Pow­er Que­ry aber sehr wohl). Bei Vor­gang wäh­len Sie in die zwei­te Zei­le den Be­griff Alle Zei­len, das Feld Spal­te ist nicht aus­wähl­bar. Klick­en Sie nun auf OK und das Ergeb­nis wird sich so dar­stel­len:

„Ta­ble” ste­ht in der neu­en Spal­te für „Ta­bel­le”

Klick­en Sie in der Über­schrift der neu­en Spal­te - auf das Sym­bol mit dem Dop­pelpfeil , um den In­halt der Spal­te zu erweit­ern. Das stellt sich an­schlie­ßend so dar:

Erst ein­mal et­was unge­wohnt, aber gar nicht so schlimm … 😉

Ak­ti­vie­ren Sie im ers­ten Schritt das Options­feld Agg­re­gie­ren (ganz oben) und ent­fer­nen Sie dann das Häk­chen bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den (ganz un­ten). Sie wer­den beim Durch­scrol­len der Lis­te erken­nen, dass es zwei unter­schiedliche füh­ren­de Zei­chen gibt: die Rau­te # so­wie das Sum­men­ze­ichen ∑. Set­zen Sie das Häk­chen bei den einzi­gen Jah­ren mit Dat­en, also bei 1977 und 1978, alle an­de­ren Fel­der blei­ben leer:

Die Jahres-Spal­ten agg­re­gie­ren

Das Ergeb­nis wird auch Sie überzeu­gen. Der Zu­satz „Sum­me von ” in der Über­schrift wur­de durch Pow­er Que­ry automa­tisch hinzuge­fügt, kann aber aus mein­er Sicht beste­hen blei­ben. Oder aber Sie än­dern die Über­schriften so, wie Sie die­se ger­ne hät­ten; entwed­er per Dop­pelk­lick oder mar­kie­ren und dann F2. Es ist aber auch eine Über­legung wert, eine sol­che Än­de­rung erst in der erstell­ten Ex­cel-Ta­bel­le per Erset­zen durch­zu­füh­ren.

Die­se Lö­sung kann sich ge­wiss se­hen las­sen

Jet­zt bleibt prak­tisch nur noch, die Spal­te Zusam­menge­führt wie­der in ihre ei­gent­li­chen Ur­sprün­ge zu­rück zu „ver­wan­deln”. Dazu mar­kie­ren Sie die­se Spal­te durch ei­nen Klick in die Über­schrift, Start, Spal­te tei­len | Nach Trennze­ichen | Tab­stopp | Bei je­dem Vorkom­men des Trennze­ichens und dann OK. Jet­zt noch die Über­schriften der bei­den ers­ten Spal­ten wie­der auf Song und Band/In­ter­pret än­dern und jet­zt be­darf es ge­wiss kein­er weit­eren Än­de­rung der Ab­fra­ge.

Start oder Da­tei, Schlie­ßen & la­den führt dazu, dass die Ab­fra­ge in ein Excel-Arbeits­blatt ge­schrie­ben wird. Wenn Sie ger­ne eine an­de­re Posi­tion hät­ten, kön­nen Sie die er­zeug­te Ta­bel­le prob­lem­los auss­chnei­den und an gewün­schter Posi­tion wie­der ein­fü­gen.

Sie wer­de erken­nen, dass sich die­ser Auf­wand wirk­lich lohnt, wenn Sie die Dat­en und der Ursprungsta­belle än­dern oder er­gän­zen. Nach ei­nem Öff­nen der *.xlsx wird sich zu Be­ginn kei­ne Än­de­rung zei­gen. Das ist ge­wollt so, denn es kann ja sein, dass Sie Ihre Ar­beit am Vor­abend unter­brochen ha­ben und nun am fol­gen­den Mor­gen ge­nau an der Stel­le weit­er­ma­chen wol­len. Wenn Sie bei entsprechen­der Gele­gen­heit aber ei­nen Klick auf Aktu­al­isieren durch­führen, dann ha­ben Sie so­fort alle neu berech­neten und aktu­al­isierten Dat­en auf dem Bild­schirm.

Mein Tipp an die­ser Stel­le: Arbeit­en Sie sich mit der Zeit in die fort­geschrit­tene Lö­sung des ers­ten Ab­schnitts ein. Es lohnt sich ge­wiss, denn Sie wer­den auch in vie­len an­de­ren Fäl­len von den erlern­ten Funk­tion­al­itäten des Pow­er Que­ry prof­i­tieren.

Hin­weis: Im Fo­rum wur­de die­ses The­ma heiß disku­tiert. Wäh­rend ich die­ses Skript ver­fasste, ka­men di­ver­se Bei­trä­ge und Lö­sungs­vor­schlä­ge dazu. Ich habe be­wusst wäh­rend der Schreib-Pha­se nicht im Fo­rum nach­ge­schaut, was sich dort „ge­tan hat”. Allerd­ings wur­de mir mit schön­er Re­gel­mä­ßig­keit an­ge­zeigt, dass dort ein neu­er Bei­trag (mit ei­nem Aus­zug des Ti­tels) ein­ge­stellt wor­den ist. Ich wer­de mich im neu­en Jahr (2019) dort ein­mal „durch­wühlen”. Vielle­icht gibt es ja ähn­liche oder auch bes­se­re Lösun­gen. Wo­bei ich ge­ne­rell mit der Qual­i­fizierung „bess­er” mei­ne Prob­leme habe, denn wenn das Ergeb­nis auch in Gren­zfällen stimmt, dann ist jed­er Weg gut. Ob nun kürz­er oder län­ger, ver­schlun­gener oder ger­ad­er, das ist eher eine Fra­ge des Ge­schmacks. Ich ver­tre­te die Überzeu­gung, dass ein Weg, eine Funk­tion oder For­mel bzw. ein Code trans­par­ent sein soll. Über­sicht kos­tet mit­un­ter die eine oder an­de­re Zei­le oder auch manch ei­nen Schritt mehr. Nut­zer, die nur das Ergeb­nis wol­len, ohne den An­spruch zu ha­ben, selb­st am Pro­jekt weit­er zu arbeit­en, wer­den das nicht unbe­d­ingt zu schät­zen wis­sen und wahrschein­lich den kürz­eren, ger­aden Weg wäh­len. Die­sem An­spruch will und wer­de ich nie ge­recht wer­den, denn ich habe an mei­ne Le­ser den An­spruch, dass sie aus mei­ne Beiträ­gen Wis­sen schöp­fen, um auf die­sem Fun­da­ment ihr ei­ge­nes Schaf­fens­ge­bäude auf­zu­bau­en. (Das Da­tum der Veröf­fentlichung ist nicht ohne Grund Sil­vester 2018… 💡 )

▲ nach oben …

(3) Übung mit rea­lis­ti­schen Da­ten, Mut zur Lü­cke  😉 

  Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, kei­ne/kaum Er­fah­rung   

Da wir heu­te am let­zten Tag des Jah­res 2018 ste­hen ist schon am Hor­i­zont sicht­bar, dass eine weit­ere Spal­te für das kom­mende Jahr einge­fügt wer­den wird. Gle­icher­maßen für das Jahr 2020, und, und, und… Dar­um ist es hil­fre­ich, wenn der Auf­bau der Ab­fra­ge so ist, dass automa­tisch alle Spal­ten, also alle Jah­re für die Berech­nung berück­sichtigt wer­den. Selb­st dann, wenn da ein­mal ein Jahr kei­ne Dat­en ent­hält. Für den Wis­sens­stand des ers­ten Ab­schnitts (et­was Er­fah­rung) wäre das prinzip­iell kein Prob­lem, für Ein­steiger ist vielle­icht der eine oder an­de­re klei­ne Um­weg ganz hil­fre­ich. 🙂 

Ich habe Ih­nen im Mus­ter-File eine tabel­lar­ische Auf­stel­lung zusam­mengestellt, wo zwar durch die Funk­tion ZU­FALLS­BE­REICH() Fan­tasie-Zahlen gener­iert wor­den sind, die aber den­noch denk­bar sein kön­nen. In den Jah­ren 2012, 2016 und 2019 sind (noch) kei­ne Dat­en er­fasst, die Spal­ten sol­len aber in der endgülti­gen Darstel­lung im Daten­bere­ich auch leer an­ge­zeigt wer­den.  Ge­hen Sie dazu so vor:

• La­den Sie die­ses File von un­se­rem Serv­er und im­por­tie­ren Sie die Dat­en auf dem bekan­nten Wege in den Pow­er Que­ry-Edi­tor.
• Sor­tie­ren Sie die ers­te und auch zwei­te Spal­te auf­steigend.
• Mar­kie­ren Sie alle Spal­ten ab der drit­ten Spal­te (?) bis zur let­zten Spal­te (der­zeit 2019).
• Auch wenn bei Daten­typ: Gan­ze Zahl an­ge­zeigt wird, expliz­it noch ein­mal Gan­ze Zahl wäh­len.
• Wenn Sie ein neu­es Jahr in den Roh­dat­en er­stellt ha­ben und dort noch kei­ne Dat­en enthal­ten sind, muss für die­se Spal­te (beispiel­sweise 2020) auch ein­ma­lig die­ser Daten­typ geset­zt wer­den. 
• Bestäti­gen Sie mit OK.

Im näch­sten Schritt er­stel­len Sie aus die­ser Ab­fra­ge ein (1) Dup­likat als Ver­weis: Reg­is­ter Start, Grup­pe Ab­fra­ge | Ver­wal­ten | Ver­weis. Wech­seln Sie erforder­lichen­falls zur neu erstell­ten Ab­fra­ge mit der Beze­ich­nung Ta­bel­le 1 (2) durch ei­nen Klick im lin­ken Seit­en­fen­ster auf die­sen Ein­trag. Falls links der Ab­fra­ge nur ein schma­ler Strei­fen mit dem Text Ab­fra­ge sicht­bar ist, ein­fach dar­auf Klick­en. – Hin­weis: Auch wenn die der Erstel­lung der Ver­weis-Abfrage nicht in je­dem Fall erforder­lich ist so hal­te ich das für eine gute Vor­sichts­maß­nahme. „Man” weiß ja nie, ob die Orig­i­nal-Dat­en noch ein­mal ge­braucht wer­den oder irgend­wie im Kern an­ge­passt wer­den sol­len.

Mar­kie­ren Sie hier in Ta­bel­le1 (2) die Spal­ten Song und Band/In­ter­pret und in der Grup­pe Trans­formieren wäh­len Sie Grup­pieren nach (das geht übri­gens auch über ei­nen Recht­sklick). Den In­halt des Fel­des Neu­er Spal­tenname (An­zahl) kön­nen Sie so be­las­sen. Bei Vor­gang wäh­len Sie den Ein­trag Alle Zei­len. Spal­te ist zwar nicht aus­ge­graut, Sie kön­nen aber dort den­noch nichts ein­tra­gen.

Klick­en Sie nun auf Aggre­ga­tion hinzufü­gen und tra­gen Sie in das lee­re Feld unter­halb  Neu­er Spal­tenname beispiel­sweise Sum­me ein. Vor­gang ist hier na­tur­ge­mäß Sum­me und bei Spal­te wäh­len Sie in der gle­ichen Zei­le die Spal­te ?. Das Gan­ze zeigt sich nun so:

Eine et­was aufwändi­gere Grup­pierung der Dat­en

Das Ergeb­nis wird Sie vielle­icht et­was über­raschen:

Das Ergeb­nis ist vielle­icht irri­tierend, aber kor­rekt

Ver­schieben Sie nun die Spal­te Sum­me an die 3. Posi­tion, also nach Band/In­ter­pret. Entwed­er Klick­en Sie dazu in die Über­schrift der Spal­te Sum­me und zie­hen Sie die­se per Hand dort hin, alter­na­tiv geht das auch über das Kon­textmenü, wenn Sie die Über­schrift mar­kiert ha­ben: Ver­schieben | Links.

Die nun let­zte Spal­te hat ja die vielle­icht irri­tierende Über­schrift An­zahl, aber das ist in die­sem Fall nicht wirk­lich rel­e­vant. Aber falls es Ih­nen lie­ber ist, än­dern Sie die Über­schrift auf Jah­re. Ich habe das auch ein­fach ein­mal der Trans­parenz we­gen ge­tan. Was aber ge­wiss be­fremd­lich sein kann ist, dass jede ein­zel­ne Zei­le den In­halt Ta­ble hat. Klick­en Sie dort zum Erweit­ern auf den Dop­pelpfeil . Und der Dia­log, welch­er sich nun öff­net, ist für sie wahrschein­lich auch nicht viel aus­sagekräftiger. 😉 Aber in die­sem Fall sieht es vielle­icht schlim­mer aus, als es ist; Sie wer­den gar nicht so über­mäßig viel tun müs­sen. 😎 

Ei­ni­ge Häk­chen set­zen und ent­fer­nen, und das klappt dann auch … 😉

Zu­erst acht­en Sie dar­auf, dass die Options-Schalt­fläche Agg­re­gie­ren ak­ti­viert ist. An­schlie­ßend mar­kie­ren Sie die ober­ste Aus­wahl (Alle Spal­ten aus­wäh­len). Ent­fer­nen Sie dann alle Häk­chen, wo kei­ne Jah­res­zahl ste­ht (also bei den ers­ten 3 Spal­tenna­men) und auch das Häk­chen ganz un­ten bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den. Klick­en Sie nun auf OK, um den Dia­log zu schlie­ßen.

Das war’s prinzip­iell auch schon. Schlie­ßen & la­den wird die Ab­fra­ge schlie­ßen und in ein Arbeits­blatt schrei­ben. Aber da hier mit Sicher­heit auch „Spe­zis” mitle­sen …

▲ nach oben …

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

Und weil das Jahr ei­nen gu­ten Ab­schluss ha­ben soll, hier noch ein­mal die Lö­sung für Anwen­derin­nen und Anwen­der mit fort­geschrit­ten­em Wis­sens­stand in Sa­chen Pow­er Que­ry. Eine klei­ne Ein­schränkung muss ich allerd­ings ma­chen: Mein Ehr­geiz ist es, die­sen Bei­trag noch im Jahr 2018 zu veröf­fentlichen. Und da ab ca. 20:00 Uhr wirk­lich nur Fam­i­lie ange­sagt ist, blei­ben mir nur noch we­ni­ger als 4 Stun­den, um den Text zu ver­fassen, al­les Kor­rek­tur zu le­sen (und natür­lich bei Be­darf zu ver­bes­sern) und schluss­end­lich zu veröf­fentlichen. Dar­um wird manch­es eher in Stich­worten behan­delt, nicht so detail­liert sein, wie vielle­icht an an­de­ren Stel­len hier im Blog. Bit­te ha­ben Sie da­für Ver­ständ­nis. – Sie dür­fen mich aber ger­ne dar­auf hin­weisen, wenn et­was unver­ständlich oder all­zu knapp dar­ge­stellt wur­de, da wer­de ich dann ge­wiss nach­bes­sern. 😳 

OK, let’s go! (Hin­weis: Hier ist die Vorge­hensweise ab Ex­cel 2016 be­schrie­ben!)

• Die im­por­tier­te Da­tei liegt auf C:\Data. 
• Neue, lee­re Ex­cel-Map­pe, Dat­en, Aus Da­tei | Aus Ar­beits­map­pe.
• Wech­sel zu C:\Data, die eben ge­la­de­ne Da­tei mar­kie­ren und auf Im­por­tie­ren Klick­en. (Even­tu­ell den Pfad an­pas­sen)
• In der lin­ken Hälf­te Ta­bel­le1 mar­kie­ren und rechts ver­gle­ichen, ob es die richti­gen Dat­en sind.
• Erweit­ern Sie die Schalt­fläche La­den und wäh­len Sie La­den in…
• Öff­nen Sie die eben er­stell­te und durch PQ ge­schlos­se­ne Ab­fra­ge wie­der.
• „Reg­istri­eren” Sie für sich sel­ber, dass die ers­te Zei­le eine durch Pow­er Que­ry er­stell­te, nicht aus­sagekräftige Über­schrift bekom­men hat.
• Start, Grup­pe Trans­formieren, Ers­te Zei­le als Über­schrift ver­wen­den.
• Sor­tie­ren Sie die ers­te und auch zwei­te Spal­te auf­steigend.
• Reg­is­ter (nicht Spal­te!) Trans­formieren, die ers­ten bei­den Spal­ten mar­kie­ren und Spal­ten zusam­men­führen. Hin­weis: Geht auch per Recht­sklick)
• Als Trennze­ichen beispiel­sweise den Tab­stopp aus­wäh­len, dann OK.
• Start, Grup­pe Ab­fra­ge | Sym­bol Ver­wal­ten | Ver­weis. Alter­na­tive: Im lin­ken Seit­en­fen­ster per Recht­sklick.
• Zusam­menge­führt mar­kie­ren, Recht­sklick in die Über­schrift, und Grup­pieren nach…
• Neu­er Spal­tenname: Sum­me, Vor­gang: Sum­me, Spal­te: ?

Die­se Ab­fra­ge beste­ht nun aus 2 Spal­ten: Zusam­menge­führt und Sum­me (die sum­mierte Spal­te ?). Weit­er geht’s…

• Er­stel­len Sie ei­nen weit­eren Ver­weis der Ta­bel­le1.
• Lö­schen Sie die Spal­te ?.
• Spal­te Zusam­menge­führt mar­kie­ren, Recht­sklick in die Über­schrift, An­de­re Spal­ten ent­piv­otieren.
• Spal­te Attrib­ut mar­kie­ren und Auf­steigend sor­tie­ren.
• Reg­is­ter Trans­formieren, Grup­pe Be­lie­bi­ge Spal­te | Spal­te piv­otieren.
• Wer­te-Spal­te: Wert.
• Klick auf den Text Erweit­erte Optio­nen, Aggre­gatwert­funk­tion: Sum­me und OK.
• Ak­ti­vie­ren Sie Ta­bel­le1 (2).
• Start, Kom­binieren | Abfra­gen als neue Ab­fra­ge zusam­men­führen.
• Wäh­len Sie im mit­tleren, klei­nen Kom­bi­na­tions­feld Ta­bel­le1 (3).
• Mar­kie­ren Sie in bei­den gro­ßen Kas­ten jew­eils die Spal­te Zusam­menge­führt durch ei­nen Klick in die Dat­en oder die Über­schrift.
• Be­las­sen Sie Join-Art bei der Vor­gabe, per OK schlie­ßen.
• Erweit­ern Sie in der Ab­fra­ge Mer­ge1 die Spal­te Ta­bel­le1 (3) durch Klick auf den Dop­pelpfeil.
• Ent­fer­nen Sie das Häk­chen bei Zusam­menge­führt und unter­halb des Kas­tens bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den.
• Schlie­ßen Sie den Dia­log.
• Zusam­menge­führt mar­kie­ren, Start, Spal­te Tei­len | Nach Trennze­ichen | Tab­stopp akzep­tieren und Dia­log schlie­ßen. (→ auch per Recht­sklick mög­lich)
• Start, Schlie­ßen & la­den 
• Lö­schen oder ver­steck­en Sie die Tabel­len­blät­ter Ta­bel­le1 .. Ta­bel­le3 und benen­nen Sie Ta­bel­le4 nach eigen­em Wun­sch um.

Das war’s. Viel Text, ich weiß. Aber ger­ade für Ein­steiger ist es mit­un­ter wich­tig, dass der Weg aus­führlich, Schritt für Schritt be­schrie­ben wird. Und im Ab­schnitt di­rekt hier­über habe ich ja auch ge­zeigt, dass es auch mit weni­gen Stich­worten mach­bar ist. 😉 

Dan­ke fürs Le­sen und vor eini­gen viel Er­folg beim umset­zen in die Prax­is, falls Sie die­ses The­ma so inter­es­sant gefun­den ha­ben, dass sie es sel­ber nachvol­lziehen wol­len. Und even­tu­ell kön­nen Sie ja den ei­nen oder an­de­ren Tipp auch in Ih­ren eige­nen Pro­jek­ten ein­set­zen.

▲ nach oben …

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 4,00 € freu­en …