Hin­weis: Pow­er Que­ry ist erst ab der Ver­sion Ex­cel 2010 ein­set­zbar. Mehr dazu kön­nen Sie hier nach­le­sen. Wenn Sie eine frü­he­re Ver­sion ver­wen­den oder Ex­cel für den Mac, dann zei­gen wir Ih­nen in die­sem Bei­trag ei­nen Weg auf, wie Sie die Dat­en von ein­er Kreuzta­belle in eine piv­ot-gerechte Form brin­gen kön­nen.

Der Stand der Din­ge

Meh­re­re Abteilun­gen ei­nes Un­ter­neh­mens lie­fern Verkaufs­dat­en in eine ein­zi­ge Ex­cel-Ta­bel­le. Das führt dazu, dass ein Kun­de am gle­ichen Tag oder an ver­schiede­nen Ta­gen mehr­fach in der Monat­süber­sicht auf­taucht. Der Kun­de 4711 ist ein­er der „Kan­di­dat­en”. Die Ta­bel­le kann hier her­un­ter ge­la­den wer­den und stellt sich etwa so dar:

Auss­chnitt aus der Roh-Dat­en Ta­bel­le

Sie erken­nen, dass in Spal­te A die Kun­den­num­mern einge­tra­gen sind, in Zei­le 1 ab Spal­te B die Arbeit­stage, hier für den Jan­u­ar 2014. Unter­halb des Da­tums sind die ver­schiede­nen Um­sät­ze einge­tra­gen.

▲ nach oben …

Die For­de­rung

So, wie die Ta­bel­le der­zeit vor­liegt, lässt sie sich nicht wirk­lich leicht aus­wer­ten. Dar­um wird fol­gende For­de­rung auf­ge­stellt: Statt in ein­er Kreuzta­belle sol­len alle Kun­den mit ih­ren jew­eili­gen Um­sät­zen und dem Da­tum in ein­er neu­en Ta­bel­le in drei Spal­ten er­fasst wer­den. Jede Kun­den­num­mer (ist hier iden­tisch mit der Zei­le, auch wenn der Kun­de mehr­fach in Spal­te A er­scheint) soll in eine Zei­le, da­ne­ben das Da­tum des Kaufs und der Um­satz. Für den ers­ten Kun­den aus der Lis­te (KdNr. 4582) sähe das dann so aus:

Die ers­ten Zei­len des Wun­sch-Ergeb­niss­es

Der Über­sicht we­gen habe ich hier auch die ers­te Zei­le des 2. Kun­den mit einge­fügt.

▲ nach oben …

Die Lö­sung

Zu­ge­ge­ben, es geht mit rei­nen Excel-Funk­tio­nen. Aber die For­mel ist nicht wirk­lich sehr über­sichtlich und trans­par­ent. Und wenn sich dann ein­mal et­was än­dert, ist ex­trem viel Aufmerk­samkeit erforder­lich. Dar­um wäh­len wir hier den Weg über das Add-In Pow­er Que­ry bzw. die ent­spre­chen­de Funk­tion­al­ität ab Ex­cel 2016.

Apro­pos For­mel: In Ta­bel­le Mo­nat ste­ht in Zei­le 1 ab Spal­te C eine For­mel. Vielle­icht kön­nen auch Sie da­von prof­i­tieren, auf die Schnel­le nur die  typ­is­chen Arbeit­stage einzu­tra­gen. In B1 das ers­te Da­tum des Zeit­raums, in C1 die For­mel und dann ein­fach nur nach rechts rü­ber­zie­hen. Spal­ten mit Feierta­gen allerd­ings müs­sen von Hand ge­löscht wer­den, aber das ist ja nicht ganz so auf­wen­dig.

Öff­nen Sie erforder­lichen­falls die Da­tei mit den Um­sät­zen, wel­che in ein­er Kreuzta­belle for­matiert sind. Acht­en Sie bit­te dar­auf, dass eine Zel­le im Daten­bere­ich ak­tiv, also mar­kiert ist. Klick­en Sie nun auf den Me­nü­punkt Pow­er Que­ry. Bei die­ser Gele­gen­heit ein Hin­weis: Alle Weisun­gen be­zie­hen sich auf Ex­cel 2013, in der 2016^er Ver­sion ist es prinzip­iell ähn­lich, aber nicht gle­ich; hier sind die Funk­tion­al­itäten im Menü Dat­en unterge­bracht. Wie ge­wohnt zeigt sich ein neu­es Men­üband, wo Sie in der Grup­pe Excel-Dat­en auf die Schalt­fläche Von Ta­bel­le Klick­en. Die Dat­en sol­len ja von ein­er Ta­bel­le „im­por­tiert” wer­den.

Es öff­net sich das Dialogfen­ster Von Ta­bel­le, Ex­cel mar­kiert den zusam­men­hän­gen­den Bere­ich der Dat­en und trägt die Wer­te in das Ein­ga­be­feld ein. Hier soll­ten Sie kon­trol­lieren, ob die Dat­en kor­rekt sind. Acht­en Sie auch dar­auf, dass das Häk­chen bei den Über­schriften geset­zt ist. Da­nach mit OK bestäti­gen.

Ein neu­es Fen­ster, der Abfrage-Edi­tor tut sich auf. Das ist der zen­trale Arbeits­bere­ich für das, was Sie vor­ha­ben. Die Dat­en wer­den sehr ähn­lich wie im „nor­malen” Ex­cel dar­ge­stellt, allerd­ings ist das Ver­hal­ten viel­fach ein an­de­res. Das bet­rifft in ers­ter Lin­ie die Über­schriften. Und For­meln kön­nen durch „Otto Nor­malan­wen­der” auch nicht ver­wen­det wer­den.

Die ers­te Spal­te (KdNr.) ist mar­kiert. Klick­en Sie nun im Menü auf Trans­formieren. In der neu­en Rib­bon-Leiste Klick­en Sie nun in der Grup­pe Be­lie­bi­ge Spal­te auf den Drop­Down-Pfeil bei Spal­te ent­piv­otieren. Die aus­gewählte Spal­te soll ja prinzip­iell beste­hen blei­ben, nur die entsprechen­den Wer­te sol­len trans­po­niert und dem entsprechen­den Kun­den zuge­ord­net wer­den. Dar­um wäh­len Sie den Me­nü­punkt An­de­re Spal­ten ent­piv­otieren. Schnel­ler als Sie hin­se­hen kön­nen, wird ge­nau das ge­sche­hen, die Dat­en wer­den umgrup­piert und die Leer­zel­len aufge­füllt.

Ei­gent­lich ist das Ziel erre­icht. Aber aus prinzip­iellen Erwä­gun­gen soll­ten Sie an die­ser Stel­le bere­its eine klei­ne Anpas­sung vor­neh­men: Spal­te 2 und Spal­te 3 wur­den durch Pow­er Que­ry mit Stan­dard-Über­schriften verse­hen. Ein Dop­pelk­lick auf Attrib­ut und än­dern Sie den Na­men auf Da­tum. In der Spal­te Wert Klick­en Sie ein Mal in die Zel­le mit der Über­schrift und dann F2, um den Na­men auf Um­satz anzu­passen. Bestäti­gen Sie beispiel­sweise mit Ein­ga­be. – Eine Klei­nig­keit kön­nen Sie noch erledi­gen: Die Spal­te Um­satz ist als Daten­typ: Be­lie­big for­matiert. Ein Klick auf die­ses Feld im Men­üband und dann die Aus­wahl Wäh­rung, das bringt Sicher­heit.

Last but not least sol­len die Dat­en vom Edi­tor in eine neue Ta­bel­le ge­schrie­ben wer­den. Dazu wech­seln Sie in das Menü Start und Klick­en Sie auf das ers­te Sym­bol ganz links: Schlies­sen & la­den. Umge­hend schlie­ßt sich das Edi­tor-Fen­ster und es wird ein neu­es Arbeits­blatt er­stellt und mit den Dat­en ge­füllt. Es emp­fiehlt sich nun, die Ta­bel­le mit ei­nen sin­nvollen Na­men zu verse­hen. Wenn bei den Tabel­len­tools das Reg­is­ter Ent­wurf ak­ti­viert ist, dann in der Grup­pe Eigen­schaften den Tabel­len­na­men beispiel­sweise auf tbl_Um­sät­ze än­dern. Und wenn Sie schon da­bei sind, kön­nen Sie auch gle­ich den Blat­tna­men auf ei­nen „sprechen­den” Na­men än­dern. – Das war es dann auch schon. Auf die­ser Ba­sis lässt sich her­vor­ra­gend eine Piv­ot-Auswer­tung er­stel­len.

▲ nach oben …

Sie­he auch: Kreuzta­belle piv­ot-fähig umgestal­ten, Ta­bel­le zu Piv­ot­Table , Pow­er Que­ry Quick­ies