Wie solch eine Auf­gabe per VBA zu lö­sen ist, kön­nen Sie beispiel­sweise hier in un­se­rem Blog nach­le­sen. „Zu Fuß” ist ge­wiss auch kei­ne Op­ti­on für Sie, son­st wür­den Sie hier nicht nach­schauen. Es geht mit Pow­er Que­ry vol­lkom­men ohne eine Zei­le VBA-Code. Und Sie ha­ben nach wie vor eine *.xlsx-Da­tei, was bei man­chen restrik­tiv­en Ein­stel­lun­gen in Fir­men­net­zw­erken nicht zu unter­schätzen ist. Dazu kommt, dass bei Änderun­gen kei­ne Pro­gram­mierken­nt­nisse erforder­lich sind. – Um die­ses Train­ing nachvol­lziehen zu kön­nen, la­den Sie bit­te die­se als *.zip gepack­te Da­tei (ent­hält das kom­plette Verze­ich­nis mit di­ver­sen Files) her­un­ter und ent­pack­en sie in ein Laufw­erk bzw. Verze­ich­nis Ih­rer Wahl oder auf dem Desk­top. Mer­ken Sie sich die­se Posi­tion.

▲ nach oben …

Vor­be­rei­tung

Ide­al­er­weise öff­nen oder er­stel­len Sie nun eine neue, lee­re Map­pe. Hier hin­ein sol­len die eben ent­pack­ten Da­tei­en im­por­tiert wer­den. Be­vor das ge­schieht, se­hen Sie sich aber ein­mal das eben ent­pack­te Verze­ich­nis an. Ih­nen wird ge­wiss auf­fall­en, dass da schon durch den Na­men auf­fal­l­end ein „Aus­reißer” drin ist. Die Mo­na­te Jan­u­ar bis Dezem­ber sind die Um­sät­ze der Bäck­erei Klein­brot und jew­eils ex­akt gle­ich aufge­baut. Die Ex­cel-Da­tei Bun­destagsmit­glieder hat sich irgend­wie dor­thin „verir­rt” 😉 und passt in kein­er Wei­se zu den Um­sät­zen. – Prinzip­iell soll­ten Sie nun auch ein, zwei Files öff­nen, um den Auf­bau ken­nen zu ler­nen. Selb­st wenn Sie die­se eigen­händig er­stellt ha­ben, kann es in Fir­men-Net­zw­erken schon ein­mal pas­sie­ren, dass ein Kol­lege die­se oder jene „Ver­bes­se­rung” vorgenom­men hat. Und zu­ge­ge­ben, wenn ich län­gere Zeit mit ein­er Ex­cel-Map­pe nicht gear­beit­et habe, dann trügt die Erin­nerung mit­un­ter.

▲ nach oben …

Der Im­port

Hin­weis: Die Bil­der und Ein­zel­schrit­te die­ses Train­ings be­zie­hen sich auf die Ver­sion Ex­cel 2016. Der Haupt-Unter­schied zu den frü­he­ren Ver­sio­nen beste­ht dar­in, dass jet­zt die einzel­nen Punk­te im Menü Dat­en (2016) zu find­en sind, früherdi­rekt im Me­nü­punkt Pow­er Que­ry (2010/13).

Wäh­len Sie Neue Ab­fra­ge | Aus Da­tei | Aus Ord­ner:

Aus­wahl des zu importieren­den Ord­ners

So­fort öff­net sich ein Dia­log, wo Sie den Pfad des zu öff­nen­den Ord­ners per Hand ein­ge­ben oder per Schalt­fläche auf Ih­rem Rech­n­er oder im Net­zw­erk aus­suchen kön­nen:

Über die Schalt­fläche ist der si­che­re­re Weg …

Wenn Sie den Ord­ner bzw. das Verze­ich­nis nicht umbe­nan­nt ha­ben, dann hat er den von mir vergebe­nen Na­men Klein­brot 12_­Mo­na­te. Sie kön­nen übri­gens auss­chließlich den Ord­ner aus­wäh­len und se­hen, nicht die dar­in enthal­te­nen Files. Bestäti­gen Sie mit OK:

Die Win­dows-typ­is­che Aus­wahl ei­nes Verze­ich­niss­es

Nach ei­nem OK sind alle Ex­cel-Da­tei­en, die vom Auf­bau her iden­tisch sind, also nur die 12 Mo­na­te mit den Um­sät­zen der Bäck­erei Klein­brot in ein­er Ta­bel­le er­fasst. Kor­rek­tur: Es sind die Dateina­men und weit­ere Infor­ma­tio­nen. Sie wer­den aber die Da­tei mit den Bun­destags-Abge­ord­neten in die­ser Auf­stel­lung verge­blich su­chen. Die Lis­te eine Rei­he von File­na­men mit den unter­schiedlich­sten Argu­menten. Hin­nehmen und abnick­en, Sie brau­chen für die­se Auf­gabe nicht das Wis­sen um die De­tails.  😎 

Alle im­por­tier­ten Files in ein­er Ta­bel­le

Klick­en Sie auf Bear­beit­en (äl­te­re Ver­sio­nen) oder auf Dat­en trans­formieren in neue­ren Ver­sio­nen (hier liegt noch kei­ne Abbil­dung vor) und alle hier an­ge­zeig­ten Da­tei­en wer­den so im­por­tiert, dass Sie die­se spä­ter bear­beit­en oder er­gän­zen kön­nen. Und das ge­schieht in ei­nem spe­zi­el­len Edi­tor, dem Ab­fra­ge- oder Que­ry-Edi­tor. Hin­weis: Wenn Sie et­was ge­üb­ter sind wer­den Sie ver­mut­lich (wie auch ich) die Schalt­fläche La­den erweit­ern und dann La­den in wäh­len, um dann die Op­ti­on Nur Ver­bin­dung anzuk­lick­en. Das ist flex­i­bler.

Wenn Sie bere­its ein­mal mit Pow­er Que­ry gear­beit­et ha­ben, kommt Ih­nen das al­les weit­ge­hend bekan­nt vor. Nur dass Sie hier nicht so viel entschei­den kon­nten. Es kann aber auch nicht scha­den, wenn Sie sich für an­de­re Zwe­cke mer­ken, wel­che Infor­ma­tio­nen in die­ser Lis­te preis­gegeben wer­den:

Die­se Auf­stel­lung um­fasst alle zu importieren­den Da­tei­en

▲ nach oben …

Im näch­sten Schritt wer­den Sie in die Ab­fra­ge eine neue Spal­te ein­fü­gen. Dazu wäh­len Sie den Me­nü­punkt Spal­te hinzufü­gen und im Men­üband gle­ich den ers­ten Me­nü­punkt Be­nut­zer­de­fi­nier­te Spal­te hinzufü­gen. Wenn Sie die­ses Ele­ment der Me­nü­leis­te anklick­en, er­scheint ein Dia­log. Dort ge­ben Sie im Feld Neu­er Spal­tenname beispiel­sweise Da­tei-In­halt ein, es kann aber auch ein be­lie­bi­ger ander­er Wert sein. In das Feld Be­nut­zer­de­fi­nier­te Spal­tenformel ge­ben Sie eine For­mel ein:

Ganz ohne For­meln geht es auch bei Pow­er Que­ry nicht im­mer

Das Gle­ich­heit­sze­ichen wird bere­its vor­ge­ge­ben, da­nach tra­gen Sie ein:
Ex­cel.Work­book(
dann im Bere­ich Ver­füg­bare Spal­ten entwed­er auf Con­tent ei­nen Dop­pelk­lick oder mar­kie­ren und « Ein­fü­gen. An­schlie­ßend die schlie­ßen­de Klam­mer set­zen und mit OK bestäti­gen. Hin­weis: Die Groß- Klein­schrei­bung der For­mel ist wich­tig! Acht­en Sie im Dialogfen­ster un­ten links auf den Hin­weis be­züg­lich even­tu­el­ler Syn­taxfehler. Augen­blick­lich wird die neue Spal­te einge­fügt und mar­kiert:

Eine neue Spal­te wur­de zuge­fügt

Nun ja, so wirk­lich aus­sagekräftig ist der In­halt der Spal­te nun wirk­lich nicht. Ta­ble, Ta­ble, Ta­ble … Die Spal­tenüber­schrift ist aber jene, die Sie vor­her so ein­ge­ge­ben ha­ben. Rechts im Über­schrift-Bere­ich ist das Sym­bol für ein Erweit­ern  zu se­hen. Klick­en Sie ein Mal dar­auf. Es öff­net sich ein neu­es Dialogfen­ster:

Die De­fault-Aus­wahl ist ge­nau rich­tig

Ide­al­er­weise be­las­sen Sie es bei den gegebe­nen Ein­stel­lun­gen und Sie Klick­en auf OK. Umge­hend wird für je­den der ange­hak­ten Möglichkeit­en in der Ab­fra­ge eine neue Spal­te er­zeugt, jede der neu­en Spal­ten ist auch automa­tisch mar­kiert:

Di­ver­se neue Spal­ten sind nach dem Ein­fü­gen noch mar­kiert

Auch hier ist es für den zu erzie­len­den Er­folg nicht rel­e­vant, die Bedeu­tung der einzel­nen neu­en Spal­tenüber­schriften zu ken­nen. Vielle­icht ist es mer­kens­wert, dass das Prä­fix (Da­tei-In­halt) ge­nau dem Na­men der Ur­sprungs-Spal­te ent­spricht. Das ver­hin­dert auch Ver­wech­selun­gen, wenn beispiel­sweise eine Spal­te der Import­dat­en auch Data als Über­schrift hat. Die­se Wer­te sind aber für den fol­gen­den, mitentschei­den­den Schritt wich­tig.

▲ nach oben …

Ich möch­te mit Ih­nen ein klei­nes Gedanken­ex­per­i­ment ma­chen. Angenom­men, Sie wür­den jet­zt jede der in der Ta­bel­le aufge­lis­teten Da­tei­en di­rekt von Ih­rer Fest­plat­te oder aus dem Net­zw­erk nacheinan­der in eine Lis­te im­por­tie­ren. Was wür­de da unge­mein stö­ren, was wür­de nicht in die Auf­stel­lung ge­hö­ren? Wenn Sie sich die Ursprungs­dat­en noch ein­mal ins Gedächt­nis ru­fen oder di­rekt nach­se­hen, dann gibt es eine ein­zi­ge Zei­le, die in jed­er Ta­bel­le nach dem Im­port über­flüs­sig ist: Die Sum­me-Zei­le.

Und die Über­schrift, wie sieht es da­mit aus? Die ist nur in elf von zwölf Fäl­len über­flüs­sig, denn ein Mal, in der ers­ten im­por­tier­ten Ta­bel­le brau­chen wir sie ja. Um die Über­schrift nur ein ein­zi­ges Mal zu im­por­tie­ren er­stel­len Sie wie­der­um eine neue Spal­te. Den Weg ken­nen Sie: Menü Spal­te hinzufü­gen | Be­nut­zer­de­fi­nier­te Spal­te hinzufü­gen. Und auch das Dialogfen­ster ken­nen Sie schon. Ge­ben Sie dort als Spal­tenname Über­schrift und bei Be­nut­zer­de­fi­nier­te Spal­tenformel: erst ein­mal die­se An­wei­sung ein:
Ta­ble.Pro­mote­Hea­ders(
Wäh­len Sie im recht­en Fen­ster­bere­ich bei Ver­füg­bare Spal­ten per Dop­pelk­lick oder über die Schalt­fläche den Ein­trag Da­tei-In­halt.Data und der oblig­a­torisch Kon­troll­blick nach un­ten …

In der For­mel fehlt noch et­was …

… zeigt Ih­nen, dass die For­mel noch nicht voll­ständig ist. Er­gän­zen Sie die schlie­ßen­de run­de Klam­mer, noch ein­mal nach­schauen, ob nun al­les in Ord­nung ist und dann OK. Die be­nut­zer­de­fi­nier­te Spal­te wird er­stellt und das sieht nun so aus:

Die neue Spal­te mit der gewün­scht­en Über­schrift

Dass der In­halt der Zei­len solch ein­er Spal­te nicht beson­ders aus­sagekräftig ist, das ken­nen Sie schon. Und Sie wer­den sich den­ken kön­nen, dass Sie als näch­sten Schritt wie­der­um auf das Erweit­ern-Sym­bol  in der Über­schrift Klick­en wer­den. Und natür­lich auch ein Dia­log, der sich nun aber sit­u­a­tions­be­d­ingt et­was an­ders dar­stellt:

Wel­che Spal­ten sol­len über­nom­men wer­den?

Sie erken­nen die einzel­nen Spal­tenna­men, wie sie in den Monats­bericht­en vorhan­den sind. Die let­zte Spal­te Sum­me brau­chen Sie nicht in der Auswer­tung, also lö­schen Sie die Mar­kie­rung. Und falls das Häk­chen bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den geset­zt ist, ent­fer­nen Sie auch die­ses, denn die Spal­tenna­men sol­len unverän­dert über­nom­men wer­den. Das war’s, also OK.

So­fort wer­den alle ge­wähl­ten Spal­ten und nun (end­lich) die richti­gen Dat­en in die Ab­fra­ge einge­fügt. Die Spal­tenüber­schriften sind auch kor­rekt, also kön­nte der Im­port da­mit been­det sein:

Die­se (mar­kier­ten) Spal­ten wer­den für die Auswer­tung ge­braucht

Ja, die Dat­en sind kor­rekt vorhan­den. Aber da ist ja im­mer noch ein Wust von Spal­ten, die in der endgülti­gen Zusam­men­fas­sung nichts zu su­chen ha­ben. Sie brau­chen ja nur die eben erstell­ten Spal­ten, mehr nicht. Wenn die Mar­kie­rung (wie im Screen­shot hier­über sicht­bar) noch exis­tiert be­las­sen Sie es da­bei, son­st Klick­en Sie in die Über­schrift Da­tum, Shift und ein Klick in die Über­schrift Son­stiges; Die Über­schrift Da­tum muss da­nach mar­kiert sein, son­st kann die Rei­hen­folge der Spal­ten ein­fach ver­tauscht wer­den. Jet­zt wie hier ge­zeigt vorge­hen:

So lö­schen Sie die nicht mar­kier­ten Spal­ten aus der Ab­fra­ge

Im Menü Start | Spal­ten ent­fer­nen | An­de­re Spal­ten ent­fer­nen anklick­en, und es blei­ben nur noch die gewün­scht­en Spal­ten in der Ab­fra­ge. Natür­lich kön­nen Sie auch die ers­ten Spal­ten bis vor Da­tum mar­kie­ren und die­se dann lö­schen, das bleibt Ih­nen über­lassen. Das Ergeb­nis ist iden­tisch.

▲ nach oben …

Wenn Sie nun in dem Fen­ster et­was nach un­ten scrol­len wer­den Sie dar­an erin­nert, dass Sie zwar die Spal­te mit den Sum­men aber nicht die entsprechen­den Zei­len ent­fer­nt ha­ben. Zei­le 31 ist solch ein Bei­spiel. Und se­hen Sie sich doch ein­mal die Zei­le 32 an. Kom­plett leer, nur null.

Lee­re Zei­len ge­hö­ren ja nun ein­mal nicht in eine or­dent­li­che Ta­bel­le/Lis­te. Punkt (oder wie der Ex-Bun­deskan­zler Schrö­der wahrschein­lich von sich ge­ben wür­de: „bas­ta”). Dar­um wer­den Sie erst ein­mal über das Menü Start alle Leer­zei­len lö­schen:

Alle kom­plett lee­ren Zei­len ein­fach ge­löscht

Das klappt schon ein­mal her­vor­ra­gend. Und selb­stre­dend blei­ben alle Son­ntage erhal­ten, auch wenn in al­len Pro­duk­ten null ver­merkt ist. Die Zei­le ist nicht leer, da ein Da­tum in der ers­ten Spal­te ste­ht. – Bleibt noch, die Sum­me-Zei­len nach dem jew­eili­gen Ulti­mo zu ent­fer­nen. Und das geht ähn­lich unprob­lema­tisch wie eben, aber über ei­nen an­de­ren Weg:

Das Häk­chen bei „Sum­me” ent­fer­nen und OK

Fol­gen­des Vorge­hen ist eine gute Mög­lich­keit (von meh­re­ren):

• Mar­kie­ren Sie die Über­schrift Da­tum oder Klick­en Sie gle­ich auf das Drop­Down-Sym­bol  in der Über­schrift.
• Zie­hen Sie in der Aus­wahl den Roll­balken (Scroll­bar) ganz nach un­ten, da­mit der Ein­trag Sum­me zu se­hen ist.
• Ent­fer­nen Sie das Häk­chen beim let­zten Ein­trag Sum­me
• Bestäti­gen Sie mit OK.

Jet­zt bie­tet es sich natür­lich von al­lei­ne an, in die­ser Spal­te im gle­ichen Drop­Down das Da­tum auf­steigend zu sor­tie­ren. Die­ser Vor­gang be­darf ge­wiss kein­er Erk­lärung, Sie ken­nen das wahrschein­lich aus den Intel­li­gen­ten Ta­bel­len / Lis­ten in Ex­cel.

▲ nach oben …

Fer­tig. (End­lich? 😎 ) Zum Ab­schluss also zur Über­nahme der Dat­en aus der Ab­fra­ge Klick­en Sie nun entwed­er auf den Text im Sym­bol Schlies­sen & la­den (nicht auf das Bild) und dann Schlies­sen & la­den in…:

Mög­lich­keit 1 zum Schlie­ßen und la­den in…

Oder Sie Klick­en auf den Me­nü­punkt Da­tei und wäh­len dann den zweit­en Punkt in der Aus­wahl:

Der Weg zum gle­ichen Ziel über das Menü Da­tei

Da Sie ja im let­zten Schritt Schlies­sen & la­den in… ge­wählt ha­ben, wird nicht automa­tisch ein neu­es Tabel­len­blatt er­stellt son­dern Ih­nen wird ange­boten, wo Sie denn die Dat­en spe­ich­ern wol­len:

Wo soll das Ergeb­nis der Ab­fra­ge gespe­ichert wer­den?

Im ak­tu­el­len Tabel­len­blatt gle­ich in A1, das passt schon. La­den und die Ergeb­nisse ste­hen an der de­fi­nier­ten Stel­le. Was allerd­ings vielle­icht et­was irri­tiert: In Spal­te A ste­ht zwar der kor­rek­te Wert des Da­tums, die For­matierung ist aber als se­ri­el­le Zahl:

Die Er­war­tung war ge­wiss an­ders …

Natür­lich wäre es „ein Klacks”, die Darstel­lung hier in der endgülti­gen Ta­bel­le anzu­passen. Aber das hät­te ei­nen entschei­den­den Nach­teil: Wenn Sie in der Ab­fra­ge im Nach­hinein et­was än­dern, dann wer­den Sie das Zahlen­for­mat er­neut an­pas­sen müs­sen. Ein klein­er Trost: Bei ein­er Aktu­al­isierung bleibt das ak­tu­el­le For­mat erhal­ten.

▲ nach oben …

Sie soll­ten aber auch den „vernün­fti­gen” Weg ken­nen ler­nen. Ne­ben dem „gle­ich rich­tig ma­chen” 😳  bleibt ja nur noch der Weg der Än­de­rung in der Ab­fra­ge. Entwed­er ein Dop­pelk­lick auf den grün hin­ter­legten Bere­ich im recht­en Seit­en­fen­ster:

Ein Dop­pelk­lick hier öff­net wie­der die Ab­fra­ge

Oder Menü Abfrage­tools | Ab­fra­ge | Bear­beit­en aus­wäh­len. So oder so öff­net sich wie­der der Abfrage-Edi­tor und Sie kön­nen dort weit­er arbeit­en, wo Sie ‑wann auch im­mer- aufge­hört hat­ten. Das klappt auch dann, wenn Sie vor ei­nem Jahr zulet­zt mir der Da­tei gear­beit­et ha­ben. Den­noch stellt sich die Fra­ge, war­um in der eben er­zeug­ten Ta­bel­le die kalen­darischen Dat­en nicht in der gewün­scht­en Form dar­ge­stellt wur­den. Hier in der Ab­fra­ge ist es doch klar er­sicht­lich ein Da­tum. Die rechts­bündi­ge Aus­rich­tung spricht auch da­für. Und über­haupt, beim Sor­tie­ren hat Pow­er Que­ry (PQ) ja auch erkan­nt, dass es sich um kalen­darische Dat­en han­delt!

Ach ja, wenn doch im­mer al­les so ein­fach wäre, wie es zu sein scheint. Die Wege des Bill Gates sind manch­mal uner­gründlich. 🙄 Ich räu­me ein, dass ich beim ers­ten Ver­such auch leicht irri­tiert war. Die Lö­sung ist auf den ers­ten Blick nicht unbe­d­ingt lo­gisch, aber sie ist ziel­füh­rend. Mar­kie­ren Sie erst ein­mal die Spal­te Da­tum. Im Menü Start, Grup­pe Trans­formieren erken­nen Sie, dass als Daten­typ Be­lie­big ver­merkt ist. Das trifft übri­gens auch auf die rest­li­chen Spal­ten zu. Und da habe ich mich auch ge­fragt, war­um PQ bei an­de­ren Im­por­ten den Daten­typ meist kor­rekt erken­nt. Gut, bei kalen­darischen Dat­en wird fast im­mer Da­tum/Zeit erkan­nt, aber der Grund­typ ist ja gle­ich. Als Aus­gle­ich für die Irri­ta­tion bie­te ich Ih­nen eine logis­che Erk­lärung: Beim erst­ma­li­gen Ein­le­sen der Dat­en wa­ren ja gemis­chte Daten­typen in den Zei­len. Die Leer­zei­len zäh­len da­bei nicht, aber die „Sum­me” ist ja ein­deutig Text. Dar­um der Daten­typ Be­lie­big, der dann nicht mehr an­ge­passt wur­de.

Okay, än­dern Sie den Daten­typ der ers­ten Spal­te auf Da­tum. Und wenn Sie ge­nau hinge­se­hen ha­ben wer­den Sie eine ganz klei­ne Än­de­rung gese­hen ha­ben: In der Über­schrift links ist nicht mehr das Sym­bol für Be­lie­big son­dern ein klein­er sym­bol­is­ch­er Kalen­der, also Da­tum. Und wenn Sie schon ein­mal da­bei sind, mar­kie­ren Sie doch die Über­schriften Brot, Shift (bis) Son­stiges und kor­rigieren Sie auch die­sen Daten­typ, allerd­ings auf Wäh­rung. Dann hat al­les sei­ne Ord­nung. 😉

Die durch den Que­ry-Edi­tor er­zeug­te Ta­bel­le exis­tiert ja schon an der gewün­scht­en Stel­le, Sie ha­ben die zuge­hörige Ab­fra­ge ja nur verän­dert. Dar­um reicht ein ein­fach­er Klick auf Schlies­sen & la­den um die Auswer­tungsta­belle zu aktu­al­isieren. Jet­zt ste­ht dort das Da­tum auch im kor­rek­ten For­mat.

Last but not least eine An­mer­kung: Dass am 3. Okto­ber, ei­nem Fei­er­tag in Deutsch­land für je­des Pro­dukt ge­nau 100,00 € Um­satz ge­macht wor­den sind, ist natür­lich mehr als unwahrschein­lich. Die­se Zah­len sind für eine an­de­re Schu­lung „ein­ge­schleust” wor­den, wo nach der­ar­ti­gen Unregelmäßigkeit­en ge­sucht wer­den soll.  💡 

▲ 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 (1,00€ bis 2,50€) Ihrer­seits freu­en …

Bun­desweite  ✉ Schu­lun­gen ✉  durch un­se­ren Spon­sor GMG Com­put­er-Con­sult­ing