Datensätze in Tabellenform bringen

Vor­ab ange­merkt: Ich habe mehr­fach hin und her über­legt, ob ich die­sen Bei­trag über­haupt in mei­nem Blog veröf­fentlichen soll und falls ja, ob ich den Hin­weis auf das Fo­rum set­zen soll oder nicht. Let­z­tendlich habe ich mich bei­de Male für ein „Ja” entsch­ieden, denn ein­er­seits den­ke ich, dass solch eine Lö­sung hier am Blog recht gut aufge­hoben ist und ander­er­seits ist es aus mein­er Sicht eine ethis­che Pflicht, den Ur­sprung ein­er Idee (hier: Fragestel­lung) zu nen­nen. Dass ich kei­nen Link zu dem Fo­rum set­ze liegt dar­an, dass die Diskus­sion Wege genom­men hat, die nicht mei­nen Vorstel­lun­gen ent­spre­chen. Und ich habe den Ti­tel ein­fach ein­mal so über­nom­men, wie er in den (di­ver­sen) Fo­ren ver­wen­det wor­den ist. Mir fiel auch kein besser­er ein, der kurz ge­nug wäre.  😎 

▲ nach oben …

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

Die Auf­ga­be

Wie schon erwäh­nt, die­se Auf­gabe ist eine Fragestel­lung aus ei­nem Fo­rum. Ich möch­te auch nicht den Text umfor­mulieren, dar­um füge ich hier den In­halt des ers­ten Bei­trags (also der ur­sprüng­li­chen Fra­ge) die­ses Th­re­ads ein:

Hal­lo zusam­men
Ihr kön­nt mei­ne ak­tu­el­le Her­aus­forderung bes­timmt spie­lend leicht lö­sen. Ich weiss man kann dies im Ex­cel han­deln, aber ich fin­de ein­fach nicht die pas­sen­de Lö­sung:

Prob­lem­stel­lung:
Ich habe tau­sen­de Daten­sätze, die ich aus ei­nem Text­file in Ex­cel im­por­tie­re. Hier­bei sind die Über­schriften in der Spal­te A untere­inan­der aufge­führt (im­mer der gle­iche In­halt). In der Spal­te B sind dann die sich ändern­den Dat­en (unter­schiedliche In­hal­te). Zwis­chen den Über­schriften in Spal­te A ist im­mer noch ir­gend ein Text, der für die Ta­bel­le nicht ver­wen­det wer­den soll. Ich möch­te nun, dass die Über­schriften im Ex­cel in Zei­le 1 aufge­führt wird und an­schlies­send über eine automa­tis­che Funk­tion erre­ichen, dass die vari­ablen Dat­en aus Spal­te B unter­halb der Über­schrift einge­fügt wer­den. Da es tau­sen­de Dat­en sind, ist es mir nicht mög­lich über Ko­pie­ren / In­hal­te Ein­fü­gen / Trans­po­nie­ren die Daten­sätze ma­nu­ell zu er­fas­sen. Das geht bes­timmt ir­gend über eine Funk­tion oder ein Ma­kro.

Ziel:
Die vari­ablen Daten­sätze aus Spal­te B sol­len in eine Ta­bel­le einge­fügt wer­den, da­mit an­schlies­send der Fil­ter / Piv­ot über die Ta­bel­le ge­legt wer­den kann.

Um das Gan­ze für euch anschaulich­er zu ma­chen, habe ich eine stark vere­in­fachte Bei­spiel-Da­tei er­stellt, wor­aus die Aus­gangslage und das Ziel er­sicht­lich sind.

Ich dan­ke euch jet­zt schon für Eure Hil­fe.

Ich habe dar­auf geant­wortet, dass ich mich haar­ge­nau an die genan­nten Vor­gaben gehal­ten habe ob­wohl ich der Überzeu­gung sei, dass die Real­ität sich dann doch ganz an­ders dar­stellt (und nicht nur „stark vere­in­facht”). Was sich dann lei­der auch bewahrheit­ete … 😥 Die Mus­ter-Da­tei kön­nen Sie hier in un­se­rem Blog herun­ter­laden. Ein Screen­shot der Auf­gabe:

Die Aus­gangslage stellt sich in der Da­tei so dar

▲ nach oben …

Aus mein­er Sicht mehr als irri­tierend ist die „Ziel”-Be­schrei­bung; die zweis­paltige Lis­te ist ‑so­fern ohne über­flüs­sige Zei­len- ide­al für eine Piv­ot-Auswer­tung ge­eig­net, die an­ge­streb­te Kreuzta­belle ist es in kein­er Wei­se…  😕 Aber wie so oft im Le­ben gilt der Satz: „Des Men­schen Wil­le ist sein Him­mel­re­ich”.

Der Lö­sungs­weg

Sie brau­chen für die Auswer­tung ja auss­chließlich die Dat­en aus dem Bere­ich A4:B25. Dar­um mar­kie­ren Sie den ge­sam­ten Bere­ich (ein­schließlich der Leer­zei­len), StrgL oder StrgT, um dar­aus eine for­matierte Ta­bel­le zu er­stel­len; set­zen Sie auch das Häk­chen, dass die Ta­bel­le Über­schriften hat. An­schlie­ßend über das Menü Dat­en (bzw. Pow­er Que­ry in äl­te­ren Ver­sio­nen), Aus Ta­bel­le bzw. Von Ta­bel­le die Dat­en in den Pow­er Que­ry-Edi­tor im­por­tie­ren. Das stellt sich dann so dar:

Die Dat­en sind in den Abfrage-Edi­tor im­por­tiert

Sie erken­nen, dass dort in regelmäßi­gen Abstän­den jew­eils 2 Leer­zei­len (enthal­ten den Wert null) einge­fügt sind. Die­se gilt es erst ein­mal zu ent­fer­nen. Dazu wäh­len Sie im Men­üband das Sym­bol Zei­len ver­ringern | Zei­len ent­fer­nen | Lee­re Zei­len ent­fer­nen. Was jet­zt noch et­was „stört” sind die wieder­holten Zei­len, wo in der 1. Spal­te das Wort Über­schrift und in der 2. Spal­te das Wort Dat­en ste­ht. Das sind ja ganz offen­sichtlich Wieder­hol­un­gen der ei­gent­li­chen Über­schrift. Auch die­se Zei­len sol­len nun aus den im­por­tier­ten Dat­en ent­fer­nt wer­den. Erweit­ern Sie dazu die 1. Kopf­zei­le, die ei­gent­li­che Über­schrift der Ta­bel­le) → (Über­schrift   ) und ent­fer­nen Sie das Häk­chen beim Lis­ten-Ein­trag Über­schrift. So­fort lie­gen nur noch die rei­nen Dat­en in der Ta­bel­le vor.

Nun gilt es zu erken­nen, dass in den Dat­en eine Re­gel­mä­ßig­keit vor­liegt: Es sind 5^er Blö­cke, die sich in der Spal­te Über­schrift wieder­holen. Und so­mit ist das Ziel klar: Die 5 Über­schriften sol­len ein ein­zi­ges Mal trans­po­niert wer­den, also in der 1. Zei­le ein­er neu­en Ta­bel­le ein­ma­lig er­schei­nen und die dazuge­höri­gen Wer­te aus der Spal­te Dat­en sol­len dar­un­ter ge­schrie­ben wer­den. Wie auch in der Mus­ter-Da­tei dar­ge­stellt.

Um das zu erre­ichen, wech­seln Sie nun zum Reg­is­ter Spal­te hinzufü­gen und Klick­en im Men­üband auf Indexs­palte. Umge­hend wird eine neue Spal­te mit ei­nem 0‑ba­sier­ten In­dex er­stellt. Im Prin­zip sind das Zeilen­num­mern, nur dass es hier auch die Zei­le 0 gibt. Die links der Dat­en ste­hen­den „echt­en” Zeilen­num­mern kön­nen Sie nicht so ganz ein­fach abfra­gen und aus­wer­ten. Nun soll eine Art Grup­pierung er­stellt wer­den, allerd­ings hat die nichts mit dem Menüpunk­ten aus Start oder Trans­formieren zu tun. Jew­eils 5 zusam­menge­hörende Wer­te der Spal­te Über­schrift bil­den ja ei­nen Block, eine Grup­pe; und hier soll für je­den Ein­trag der einzel­nen Grup­pe ein nu­me­ri­sche Wert ver­ge­ben wer­den. Also für die ers­ten 5 Zei­len jew­eils der Wert 0, für die näch­sten der Wert 1, usw.

Hier kom­men sie nicht ohne eine selb­st er­stell­te For­mel aus. Spal­te hinzufü­gen, Be­nut­zer­de­fi­nier­te Spal­te und ge­ben Sie im Dia­log bei Neu­er Spal­tenname beispiel­sweise Idxx (oder wenn Ih­nen das lie­ber ist auch Block) als Über­schrift der neu­en Spal­te ein. Im gro­ßen Text­feld Be­nut­zer­de­fi­nier­te Spal­tenformel; schrei­ben Sie die­se For­mel ganz ge­nau so wie hier ge­zeigt nach dem vorgegebe­nen Gle­ich­heit­sze­ichen:
= Number.From(Number.RoundDown([Index]/5,0))

In die­sem Dia­log ge­ben Sie eine Über­schrift und die For­mel ein

Ver­kürzt aus­ge­drückt ent­spricht das der Ex­cel-For­mel AB­RUN­DEN(In­dex/5; 0); der In­halt der Spal­te In­dex soll also in jed­er Zei­le durch 5 divi­diert und dann auf 0 Stel­len abgerun­det wer­den. Das Ergeb­nis stellt sich dann so dar:

Die Spal­te für die Grup­pen-Bil­dung wur­de hinzuge­fügt

Die Spal­te In­dex brau­chen Sie jet­zt nicht mehr. Nicht nur aus die­sem Grun­de soll­ten Sie die­se Spal­te lö­schen, denn für den näch­sten Schritt ist es aus­ge­sprochen hil­fre­ich, wenn nur noch 3 Spal­ten auszuw­erten sind. Also mar­kie­ren Sie die Über­schrift In­dex und Entf oder Recht­sklick in die Über­schrift und Ent­fer­nen.

Der näch­ste Schritt macht Ein­steiger in Sa­chen Pow­er Que­ry öf­ter ein­mal Prob­leme. Sie soll­ten also ex­akt den Weg so ge­hen, wie er hier be­schrie­ben ist. Mar­kie­ren Sie die Spal­te, wel­che die kün­fti­gen Über­schriften enthal­ten soll durch ei­nen Klick in das Über­schrift-Feld. Das ist in die­sem Fall na­tur­ge­mäß die Spal­te Über­schrift.

Reg­is­ter Trans­formieren und ein Klick auf Spal­te piv­otieren . Als Wertes­palte wäh­len Sie jene Spal­te, wo die zu den Über­schriften gehöri­gen Wer­te einge­tra­gen sind, hier also Dat­en. Klick­en Sie an­schlie­ßend auf den Text Erweit­erte Optio­nen und wäh­len dort den Punkt Nicht agg­re­gie­ren. Bestäti­gen Sie mit OK.

Das Ergeb­nis ist fast schon per­fekt; jet­zt brau­chen Sie nur noch die 1. Spal­te Idxx lö­schen und an­schlie­ßend über das Reg­is­ter Da­tei Schlie­ßen & la­den in… wäh­len, um das Ergeb­nis die­ser Ab­fra­ge an gewün­schter Posi­tion zu plat­zie­ren. Sie erken­nen, dass das Ergeb­nis dem Wun­sch des Fra­ge­stel­lers ent­spricht.

Hin­weis: Dass die ei­gent­li­che Auf­gabe dann doch deut­lich mu­tier­te, hat mich ziem­lich geärg­ert. Ich habe an dem Punkt die weit­ere Hil­fe abge­brochen, weil ich es gar nicht mag, wenn aus beispiel­sweise Bequem­lichkeit eine Fragestel­lung so vere­in­facht, ver­kürzt wird, dass sich we­sent­li­che Eck­punk­te an­ders dar­stel­len und prak­tisch eine kom­plette neue Lö­sung er­stellt wer­den muss, um zum verän­derten Ziel zu gelan­gen. Hier wa­ren es „plöt­zlich” unter­schiedliche An­zahl von Zeilen­beze­ich­nun­gen der Spal­te A (Name) und die Leer­zei­len wa­ren auch kei­ne Leer­zei­len mehr son­dern enthiel­ten di­ver­se unter­schiedliche In­hal­te, Tex­te ver­schieden­ster Art. Selb­stre­dend ist auch solch ein Fall mit Pow­er Que­ry lös­bar, aber im Rah­men der (kosten­losen) Foren­hil­fe über­steigt das bei den gegebe­nen Umstän­den bei weit­em mei­ne Hil­fs­bere­itschaft.

▲ nach oben …