PQ: Definierte Anzahl leerer Spalten einfügen

In ei­nem Ex­cel-Fo­rum (Her­ber.de) wur­de eine An­fra­ge ge­stellt, die zwar schon durch For­meln/Funk­tio­nen ge­löst wor­den ist mich aber reiz­te, sie durch Pow­er Que­ry zu lö­sen. Ich zi­tie­re hier den Text der An­fra­ge und stel­le auch die (Orig­i­nal-) Mus­ter-xlsx auf un­se­rem Serv­er für Ih­ren Down­load zur Ver­fü­gung:

Hal­lo,
wie kann ich Dat­en von Spal­te A und B in die Zei­le 1 und 2 Trans­po­nie­ren und Zwis­chenspal­ten ein­fü­gen?
Mit der M Trans For­mel kann ich zwar die Dat­en trans­po­nie­ren, aber kei­ne lee­re Zwis­chenspal­ten ein­fü­gen.
Ich hab eine Lis­te Spal­te A mit vie­len hun­derten Ein­trä­gen bei de­nen im­mer wie­der wel­che dazukom­men und ich die­se in der Zei­le 1 und 2 ha­ben soll­te, allerd­ings mit Zwis­chenspal­ten.

„Ei­gent­lich ganz ein­fach” dach­te ich zu Be­ginn; ob­wohl ich kon­sta­tieren kann: Den ei­gent­li­chen Sinn des Vor­ha­bens habe ich bis heu­te nicht klar erkan­nt, denn das genan­nte Ziel wider­spricht aus mein­er Sicht ein­fach den Re­geln ein­er or­dent­li­chen Daten­hal­tung.

Wie auch im­mer, recht schnell merk­te ich, dass das gewün­schte Ergeb­nis doch nicht so ganz ein­fach mit Pow­er Que­ry zu gener­ieren ist. Zusam­menge­fasst und auch er­gän­zend noch ein­mal fol­gende Infor­ma­tio­nen: Eine zweis­paltige Lis­te vari­abler Län­ge soll trans­po­niert wer­den (also die Spal­ten zu Zei­len) und es soll eine de­fi­nier­te An­zahl von (lee­ren) Spal­ten in der an­schlie­ßend zweizeili­gen Ta­bel­le nach jed­er ur­sprüng­li­chen Zei­le einge­fügt sein. In der Mus­ter-Da­tei des Fra­ge­stel­lers ist das sehr gut er­kenn­bar.

Ers­te Schrit­te

Die Aus­gangslage (hier in mein­er Da­tei):

Die Beispiel-Aus­gangslage

Der Bere­ich A2:B9 ist bere­its als Ta­bel­le for­matiert und kann dar­um ohne weit­ere Vorar­beit in den Pow­er Que­ry-Edi­tor im­por­tiert wer­den. Im ers­ten Schritt ist es für mich (und für Sie) wich­tig, die An­zahl der Daten­zeilen (der Daten­sätze) fest­zu­stel­len. Natür­lich reicht ein Blick in den Edi­tor, denn ne­ben der unter­sten Zei­le ist ja auch die Zeilen­num­mer ver­merkt. Da die­se Ta­bel­le/Lis­te aber dy­na­misch gestal­tet wer­den soll, muss die­ser nu­me­ri­sche Wert in ir­gend ein­er Form fest­ge­hal­ten, gespe­ichert wer­den. Um das zu erre­ichen ge­hen Sie so vor:

• Falls Sie das lin­ke Seit­en­fen­ster mit den Abfra­gen nicht se­hen, blen­den Sie es ein.¿
• Recht­sklick auf den einzi­gen Ein­trag Ta­bel­le1 und wäh­len Sie im Kon­textmenü Ver­weis.
• Ge­ben Sie die­ser neu­en Ab­fra­ge den Na­men AnzZei­len.
• Mar­kie­ren Sie die Spal­te Dat­en, Recht­sklick in die Über­schrift und im Kon­textmenü ein Klick auf Drill­down aus­führen.
• Im recht­en Seit­en­fen­ster bei Angewen­dete Schrit­te wur­de der Schritt Da­ten1 (bzw. in Ver­sion 2016 Nav­i­ga­tion)einge­fügt, die Ab­fra­ge beste­ht nur noch aus ein­er Spal­te mit der Über­schrift Lis­te.
• Mar­kie­ren Sie die­se Spal­te durch ei­nen Klick in die Über­schrift, Sym­bol Sta­tis­tiken | Wer­te zäh­len.
• Spe­ich­ern Sie die­se Ab­fra­ge jet­zt über Schlie­ßen & la­den in… als Nur Ver­bin­dung, da­mit die­se Lis­te spä­ter nicht in ei­nem eige­nen Arbeits­blatt gespe­ichert son­dern nur im Arbeitsspe­ich­er gehal­ten wird.
• Öff­nen Sie wie­der den Pow­er Que­ry-Edi­tor und wech­seln Sie erforder­lichen­falls zu Ab­fra­ge Ta­bel­le1.

▲ nach oben …

Zwi­schen­er­geb­nis als Lis­te/Ta­bel­le

Da­mit sind die Vorar­beit­en prinzip­iell er­le­digt. Im näch­sten Schritt wer­den Sie eine ganz neue Ta­bel­le er­stel­len wel­che im End­ef­fekt so vie­le lee­re Zel­le ent­hält, wie spä­ter lee­re Spal­ten er­zeugt wer­den sol­len. Also bei den 7 Daten­sätzen der ur­sprüng­li­chen Ab­fra­ge jew­eils 2, also ge­samt 14 Daten­sätze. Dazu ge­hen Sie über Menü Start, Grup­pe Neue Ab­fra­ge | Neue Quel­le | An­de­re Quel­len | Lee­re Ab­fra­ge. Im lin­ken Seit­en­fen­ster wird ein Ein­trag Ab­fra­ge1 er­stellt und das Haupt­fen­ster, wo typ­is­cher­weise die Dat­en der Ab­fra­ge sicht­bar sind, ist abso­lut leer.

Ober­halb die­ser gro­ßen grau­en Flä­che ist nur das lee­re Text­feld für die For­meln zu se­hen. Ge­ben Sie hier in die Ein­ga­be­zei­le fol­gende For­mel ein­schließlich des führen­den Gle­ich­heit­sze­ichens ein:  = {1..AnzZeilen * 2}. Hin­weis: Da es auf dem Bild­schirm nicht im­mer opti­mal er­kenn­bar ist, es han­delt sich bei die­ser For­mel um die ge­schweif­ten Klam­mern, wel­che Sie über Alt­Gr7 und Alt­Gr0 ein­fü­gen kön­nen. Umge­hend wird eine Lis­te mit den Zah­len 1 bis 14 er­stellt.

Sie fra­gen sich nun vielle­icht, wie­so, war­um, wes­halb … Grund­sät­zlich soll eine Lis­te er­zeugt wer­den, wo bezo­gen auf die An­zahl der existieren­den Daten­sätze (in die­sem Fall 7) jew­eils 2 lee­re Zel­len/Zei­len er­zeugt wer­den. Hät­ten Sie sich die An­zahl der Daten­sätze nur ge­merkt und nicht in ein­er Ab­fra­ge gespe­ichert dann wür­de sich die For­mel so dar­stel­len: = {1..7*2}. Pow­er Que­ry erken­nt al­lei­ne, dass das zwei­te Argu­ment die Mul­ti­p­lika­tion 7*2 ist und führt die Berech­nung durch, be­vor der Teil der For­mel nach den bei­den Punk­ten (..) aus­ge­führt wird. Und der Wert 7 wird bei der ei­gent­li­chen For­mel aus der gespe­icherten Lis­te AnzZei­len ge­holt.

Da Sie (in die­sem Fall) 14 lee­re Zel­len ha­ben wol­len, Klick­en Sie im Men­üband auf Zu Ta­bel­le und im näch­sten Schritt soll­ten Sie auch gle­ich den Na­men die­ser Ab­fra­ge auf Leer­zei­len än­dern. Dass in der einzi­gen Spal­te die nu­me­ri­schen Wer­te von 1 bis 14 ste­hen, ist rich­tig und erwün­scht. Fü­gen Sie nun über Spal­te hinzufü­gen | Be­nut­zer­de­fi­nier­te Spal­te eine neue Spal­te ein und als Über­schrift ver­wen­den Sie Dat­en. In das Feld für Be­nut­zer­de­fi­nier­te Spal­tenformel tra­gen Sie nach dem bere­its existieren­den = nur in Klein­schrift den Wert null ein. OK und an­schlie­ßend ent­fer­nen Sie die ers­te Spal­te Co­lum­n1, Sie brau­chen ja nur die 14 Zei­len mit dem Wert null (also leer).

▲ nach oben …

Durch Trans­po­nie­ren zum Ziel

Wech­seln Sie nun wie­der zur Ab­fra­ge Ta­bel­le1. Menü Start, Grup­pe Kom­binieren | Abfra­gen anfü­gen und wäh­len Sie bei Anzufü­gende Ta­bel­le die Ab­fra­ge Leer­zei­len. Da­nach stellt sich die ur­sprüng­li­che Ab­fra­ge so dar:

Die gewün­schte Zahl von Leer­zei­len wur­de ange­fügt

Im fol­gen­den Schritt wer­den Sie da­für sor­gen, dass je zwei von die­sen Leer­zei­len nach jed­er der (noch) 7 ers­ten Zei­len der ehe­ma­li­gen Über­schriften einge­fügt bzw. ver­schoben wer­den. Im ers­ten Schritt ge­hen Sie über Spal­ten hinzufü­gen | Indexs­palte ei­nen In­dex, welch­er mit 0 begin­nt. Weit­er­hin im Menü Spal­te hinzufü­gen mar­kie­ren Sie nun die Spal­te In­dex und in der Grup­pe Aus Zahl wäh­len Sie Stan­dard | Mod­u­lo. In das Feld Wert ge­ben Sie erst ein­mal den Wert 2 ein; das ist nur ein Platzhal­ter, da­mit Pow­er Que­ry nach dem Ab­schluss eine funk­tion­ierende For­mel und ein dar­auf basieren­des Ergeb­nis er­stel­len kann. Das Ergeb­nis:

Das Ergeb­nis, der Dum­my-Wert 2 ist von mir gelb gemark­ert wor­den

Sie se­hen, dass eine Art Grup­pierung in Zweier­grup­pen er­stellt wor­den ist. Es bie­ten sich nun zwei unter­schiedliche Wege an, die gle­icher­maßen zum Ziel der dynamis­chen Grup­pen­größe füh­ren. Entwed­er Sie än­dern di­rekt in der Ein­ga­be­zei­le den (von mir gelb mar­kier­ten) Wert 2 zu AnzZei­len und Klick­en dann irgend­wo in den grau­en Bere­ich dar­un­ter. Oder Sie füh­ren alter­na­tiv im recht­en Seit­en­fen­ster ei­nen Dop­pelk­lick auf die zulet­zt er­stell­te Zei­le Rest einge­fügt durch, lö­schen im Edi­tor die 2 und schrei­ben statt­des­sen den Na­men der Lis­te AnzZei­len an die Stel­le. Nach ei­nem OK wer­den Sie erken­nen, dass in der Spal­te Mod­u­lo Grup­pen in jen­er Grö­ße ge­bil­det wor­den sind, die vor­her in der Ab­fra­ge/Lis­te AnzZei­len berech­net wor­den ist.

Um die Leer­zei­len an die gewün­scht­en Stel­len zu posi­tion­ieren, sor­tie­ren Sie zu Be­ginn die Spal­te Mod­u­lo auf­steigend. An­schlie­ßend sor­tie­ren Sie nun auch noch die Spal­te In­dex auf­steigend und das Ergeb­nis passt. Ent­fer­nen Sie nun auf be­lie­bi­ge Wei­se die bei­den let­zten Spal­ten, so­dass nur Dat­en und Zah­len erhal­ten blei­ben. Wech­seln Sie nun zum Menü Trans­formieren und Klick­en in der Grup­pe Ta­bel­le auf den Ein­trag Ver­tauschen. Die­ser Vor­gang ent­spricht dem, was Sie in Ex­cel un­ter Trans­po­nie­ren ken­nen.

Schlie­ßen & la­den und Sie wer­den ver­mut­lich se­hen, dass Sie nichts se­hen. 😉 Der Abfrage-Edi­tor ist zwar ge­schlos­sen und Sie befind­en sich in der Ex­cel-Ar­beits­map­pe aber die eben gener­ierte Kreuzta­belle mit den entsprechen­den Leerspal­ten ist wed­er im ak­tu­el­len Blatt noch in ei­nem geson­dert er­zeug­ten Sheet zu se­hen. Das liegt dar­an, dass Sie vor­her die Ab­fra­ge AnzZei­len im Mo­dus Schlie­ßen & la­den in… | Als Ver­bin­dung ge­si­chert ha­ben und Pow­er Que­ry die an­de­ren Abfra­gen automa­tisch auch in die­sem Mo­dus geschal­tet hat. Falls Ih­nen nicht gegen­wär­tig ist, wie Sie die­se eine Ta­bel­le1 sicht­bar an gewün­schter Posi­tion plat­zie­ren kön­nen, se­hen Sie ein­fach ein­mal hier in un­se­rem Blog nach.

An­mer­kung: Genau­so „eigen­willig” und aus mein­er Sicht auch regel­widrig wie die kom­plett lee­ren Spal­ten in ein­er Kreuzta­belle ist der Wun­sch des Fra­ge­stel­lers, das Ergeb­nis (auch noch) ohne Über­schriften dar­zu­stel­len. Die eine Mög­lich­keit ist dann natür­lich, die kom­plette Zei­le aus­zu­blen­den. Das wäre aber nicht so gut, wenn irgend­wo in die­sem Arbeits­blatt links oder rechts in die­ser Zei­le noch Wer­te ste­hen, die sicht­bar blei­ben sol­len. Die zwei­te Mög­lich­keit sehe ich dar­in, dass Sie in der Ex­cel-Ta­bel­le die Über­schriften alle mit dem Be­nut­zer­de­fi­nier­ten Zahlen­for­mat ;;; verse­hen und da­mit unsicht­bar und auch nicht druck­bar ma­chen; dann noch das Tabel­len­for­mat so an­pas­sen, dass wed­er Über­schriften noch die Fil­ter-Schalt­flächen sicht­bar sind. Da wäre dann aber händis­che Nachar­beit erforder­lich, wenn sich die An­zahl der Spal­ten der Kreuzta­belle er­höht. Die sauber­ste und da­mit bes­te Mög­lich­keit ist natür­lich, noch ein­mal in den Pow­er Que­ry-Edi­tor zu wech­seln und dort die Über­schriften mit ei­nem jew­eils ein­ma­li­gen und sin­nvollen Text zu gestal­ten.

Fer­tig. – Es bleibt bei mir das Unbe­ha­gen, dass ich dort eine Auf­gabe ge­löst habe, die ich an sich für nicht nachvol­lziehbar oder gar unsin­nig er­ach­te. Ich sehe erst ein­mal kei­nen Sinn dar­in, ein „Werk” zu er­stel­len, was ge­gen meh­re­re Re­geln ein­er or­dent­li­chen Daten­hal­tung ver­stößt. Aber es hat­te mich ein­fach ge­reizt, solch eine Auf­gaben­stel­lung mit Pow­er Que­ry zu lö­sen. Und als Idee im Hin­terkopf und even­tu­el­le Prog­nose hat­te ich, dass die lee­ren Spal­ten im Lau­fe des Leben­szyk­lus der Ta­bel­le vielle­icht nach und nach mit Dat­en ge­füllt und natür­lich dann auch ent­spre­chen­de Über­schriften in der Ti­tel­zei­le ste­hen wer­den.

▲ 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. 2,50 € freu­en … (← Klick mich!)