XLS/PQ: Berechnung prozentualer Anteile (4)

Ba­sis: Zei­len (Power Que­ry)

Wenn Sie min­destens Ex­cel 2016 ver­wen­den, kön­nen Sie für die­se ge­stell­te Auf­gabe auch das inte­gri­erte Pow­er Que­ry (Dat­en | Ab­ru­fen und trans­formieren) ein­set­zen, in ²⁰¹⁰⁄₂₀₁₃ ist ein von Mi­cro­soft kosten­los down­load­bares Add-In erforder­lich. Die Vorge­hensweise mit PQ ist deut­lich an­ders, aus mein­er Sicht aber auch kom­fort­abler.

Zu Be­ginn wer­den Sie na­tur­ge­mäß die­se Ex­cel-Da­tei la­den (öff­nen) und an­schlie­ßend (für die­se Übung) den kom­plet­ten Dat­en-Bere­ich A1:I21 in den Pow­er Que­ry-Edi­tor im­por­tie­ren. Be­las­sen Sie es in dem Rah­men bei der Vor­gabe, dass der Bere­ich kei­ne Über­schriften hat. Wie auch schon im drit­ten Teil die­ser klei­nen Se­rie be­schrie­ben, müs­sen die Dat­en erst ein­mal ent­piv­otiert wer­den. Die­se Vorge­hensweise an die­ser Stel­le aber nur im Schnell­durch­gang ohne umfan­gre­iche Erk­lärung. Wenn Sie das Prin­zip inter­essiert, su­chen Sie im Blog ger­ne un­ter dem Stich­wort ent­piv­otieren oder Sie schau­en sich ein oder meh­re­re Tei­le die­ses Work­shops an, wo auch die Dat­en der Bäck­erei Klein­brot auf Monats­ba­sis für eine Piv­ot-Auswer­tung auf­bere­it­et wor­den sind.

Di­rekt nach dem Im­port der Dat­en in den PQ-Edi­tor stellt sich das so dar:

Di­rekt nach dem Im­port seigt sich PQ so wie hier dar­ge­stellt

Da Sie mein­er Wei­sung fol­gend vie­le un­nö­ti­ge Dat­en (Zei­len und Spal­ten) im­por­tiert ha­ben, wer­den Sie erst ein­mal die Dat­en „fi­le­tie­ren”, also auf das wirk­lich notwendi­ge Maß re­du­zie­ren. Das be­deu­tet, alle Spal­ten ober­halb der ei­gent­li­chen Über­schriften (die Pro­duk­te, Berech­nun­gen) und unter­halb der Mo­nats­wer­te für Dezem­ber lö­schen. Da­nach auch die bei­den Spal­ten rechts der Spal­te Son­stiges ent­fer­nen. Und last but not least noch die ers­te Zei­le als Über­schrift hoch­stu­fen und eine sin­nvolle Über­schrift für die ers­te Spal­te ver­ge­ben, beispiel­sweise Mo­nat oder (bess­er noch) Da­tum.

Um­stel­lung der Da­ten (ent­pi­vo­tie­ren) und auf­be­rei­ten

Mar­kie­ren Sie die Spal­te Da­tum, Recht­sklick in die Über­schrift und im Kon­textmenü ein Klick auf den Ein­trag An­de­re Spal­ten ent­piv­otieren. Für je­den Mo­nat, an dem ein Um­satz ge­tä­tigt wor­den ist, exis­tiert nun eine Zei­le mit den Monat­sna­men, den Pro­duk­ten und dem entsprechen­den Um­satz. Ide­al­er­weise wer­den Sie die Über­schriften der zweit­en und drit­ten Spal­te ent­spre­chend an­pas­sen. Ich ver­wende Pro­dukt und Um­satz.

Start | Schlie­ßen & la­den | Schlie­ßen & la­den in… und wäh­len Sie Piv­ot­Table-Bericht, als Ziel bie­tet sich beispiel­sweise die Zel­le K1 des ak­tu­el­len Excel-Arbeits­blatts an. Zie­hen Sie im recht­en Seit­en­fen­ster das Feld Da­tum in den Bere­ich Zei­len, Pro­dukt nach Spal­ten und Um­satz nach Wer­te. Da­mit ha­ben sie erst ein­mal die fast iden­tis­che Ta­bel­le wie in den Ursprungs­dat­en, nur dass hier die rei­nen Umsatz­dat­en ver­wen­det wur­den und die Berech­nun­gen der Sum­men durch die Piv­ot­Table automa­tisch durchge­führt wor­den sind und ei­ni­ge Berech­nun­gen nicht (automa­tisch) ge­macht wur­den.

So, das war erst ein­mal zum „aufwär­men“ und zur Darstel­lung des Prin­zips in Sa­chen ent­piv­otieren. Schlie­ßen Sie ger­ne die eben er­stell­te Da­tei, mit oder ohne spe­ich­ern. La­den Sie nun das für die Lek­tion ei­gent­lich vorge­se­hene File hier her­un­ter; es ist die gle­iche Da­tei wie im vorheri­gen Kapi­tel, also die Jahres-Umsatz­zahlen auf der Ba­sis der einzel­nen Tage des Jah­res. Und es sind auss­chließlich die auszuw­er­tenden Dat­en ohne un­nö­ti­ge Über­schriften, Zusam­men­fas­sun­gen, Berech­nun­gen, … Der Im­port in den Pow­er Que­ry Edi­tor kann also di­rekt und ohne Um­we­ge erfol­gen.

Än­dern Sie nun im ers­ten Schritt den Daten­typ der Spal­te Da­tum auf (nur) Da­tum, da­mit die durch­gän­gi­ge Uhr­zeit 00:00:00 „ver­schwindet“. An­schlie­ßend ent­fer­nen Sie die let­zte Spal­te Ge­samt; die Piv­ot Ta­bel­le berech­net das von al­lei­ne. Mar­kie­ren Sie nun wie bere­its eben durchge­führt die Spal­te Da­tum, Recht­sklick in die Über­schrift und An­de­re Spal­ten ent­piv­otieren. Attrib­ut zu Pro­dukt umbe­nen­nen und Wert zu Um­satz.Dass die­se Ab­fra­ge mit dem 2. Jan­u­ar begin­nt und auch weit­ere kalen­darische Dat­en nicht aufge­führt sind liegt dar­an, dass nur Tag/Pro­dukt - Kom­bi­na­tio­nen aufgenom­men wer­den, wo die Zel­le mit dem Um­satz nicht leer (also In­halt null) ist.

Ähn­lich wie auch eben schon durchge­führt, in direk­tem Wege ei­nen Piv­ot­Table-Bericht er­stel­len. Der der bes­se­ren Trans­parenz we­gen schla­ge ich vor, die Piv­ot­ta­belle auf ei­nem neu­en Tabel­len­blatt zu plat­zie­ren. Die 3 Piv­ot­Table-Felder wie ge­habt in die entsprechen­den Bere­iche zie­hen.

Um über­haupt ist erst ein­mal ein Ge­fühl für die Dat­en zu bekom­men, Klick­en Sie in ein be­lie­bi­ges kalen­darisches Da­tum und wäh­len Sie dann über die Schalt­fläche Grup­pieren bzw. Aus­wahl grup­pieren die Optio­nen Quar­tale und Mo­na­te. So weit, so gut. Allerd­ings ste­hen dort ja die bekan­nten, ab­so­lu­ten Um­satz-Zah­len mit prinzip­iell der gle­ichen Aus­sagekraft wie in der Orig­i­nal-Ta­bel­le auf dem ers­ten Arbeits­blatt. Ge­fragt war ja der prozen­tuale An­teil der einzel­nen Pro­duk­te im Ver­hält­nis zum Gesam­tum­satz des Zeit­raums. Und das kön­nte dann als Piv­ot­Table-Ergeb­nis so ausse­hen:

Ein möglich­es Endergeb­nis der prozen­tualen Berech­nung in der Piv­ot­Table

An­hand die­ses Bei­spiels zei­ge ich Ih­nen ein­mal den Weg auf, wie Sie von der Darstel­lung der ab­so­lu­ten Zah­len mit weni­gen Maus­klicks zu dem hier­über ge­zeig­ten Ergeb­nis kom­men. Ich be­gin­ne da­mit, in Zel­le A2 die Über­schrift auf Da­tum zu än­dern. Ich fin­de das pas­sen­der. An­schlie­ßend sor­ge ich da­für, dass die Um­satz-Zah­len im Wäh­rung- Oder Buch­hal­tung-For­mat dar­ge­stellt wer­den, wo­bei die tau­sen­der Punk­te automa­tisch einge­fügt und die Nachkom­mas­tellen in je­dem Fall auf zwei Stel­len for­matiert wer­den. Ich räu­me ein, dass die­ser Schritt für das end­gül­ti­ge Vor­ha­ben nicht erforder­lich ist, aber es kön­nte ja sein, dass Sie eine zwei­te Piv­ot Ta­bel­le mit den rea­len Wer­ten er­stel­len wol­len und da sieht das schon deut­lich pro­fes­sioneller aus. 😉

Okay, weit­er zum angedacht­en Ziel. Nun mar­kie­re ich eine be­lie­bi­ge Umsatz­zahl, kli­cke im Bere­ich Wer­te auf den einzi­gen Ein­trag Sum­me von Um­satz | Wert­felde­in­stel­lun­gen | (im Dia­log: Reg­is­ter) Wer­te an­zei­gen als. Das Feld Benutzedefiniert­er Name än­de­re ich auf Um­satz Prozen­tu­al und erweit­ere nun Wer­te an­zei­gen als; im nun sicht­baren Drop­down wäh­le ich % des Zeilenge­samtergeb­niss­es und be­stä­ti­ge dann mit OK. A1 än­de­re ich noch auf Um­sät­ze, B1 än­de­re ich auf ein Leerze­ichen und das Ergeb­nis ist mei­nem Wun­sch ent­spre­chend.

Pro­bieren Sie ger­ne weit­ere Optio­nen, um die Berech­nung nach Ih­ren Wün­schen anzu­passen. Wenn Sie noch et­was „Nach­hil­fe” in Sa­chen Piv­ot­Table Ba­sics ha­ben möcht­en, bie­te ich Ih­nen meh­re­re Bei­trä­ge aus un­se­rem Blog an, Start wäre beispiel­sweise hier.

Lei­der bie­tet die nor­male Piv­ot­Ta­belle kei­ne direk­te Mög­lich­keit, bei gegebe­nen kalen­darischen Dat­en die Grup­pierung auf Kalen­der­wochen anzuwen­den. Das geht über ei­ni­ge Um­we­ge und es stellt sich mir die Fra­ge, ob es den Auf­wand wert ist.

Im näch­sten Ab­schnitt wer­den wir eine ähn­liche Auswer­tung nur mit Pow­er Que­ry ma­chen, wo­bei der Schw­er­punkt dar­auf liegt, dass meh­re­re unter­schiedliche Auswer­tun­gen in ein­er Ar­beits­map­pe er­stellt wer­den.

Mein bis hier­her erar­beit­etes Ergeb­nis kön­nen Sie hier herun­ter­laden. Und die­ser Stand der Din­ge ist auch (zumin­d­est für die Pow­er Que­ry-Auswer­tung) die Ba­sis der Auf­gabe im näch­sten  (let­zten) Teil die­ses Work­shops. Dort kön­nen Sie dann auch nach­le­sen, war­um schein­bar kei­ne PQ-Ab­fra­ge exis­tiert und wie Sie dann doch sicht­bar ge­macht wer­den kann. Hier nur das Stich­wort: Dat­en | Abfra­gen und Verbindun­gen.

▲ nach oben …