XLS/PQ: Berechnung prozentualer Anteile (4)

Basis: Zeilen (Power Query)

Wenn Sie min­destens Excel 2016 ver­wen­den, kön­nen Sie für diese gestellte Auf­gabe auch das inte­gri­erte Pow­er Query (Dat­en | Abrufen und trans­formieren) ein­set­zen, in ²⁰¹⁰⁄₂₀₁₃ ist ein von Microsoft kosten­los down­load­bares Add-In erforder­lich. Die Vorge­hensweise mit PQ ist deut­lich anders, aus mein­er Sicht aber auch kom­fort­abler.

Zu Beginn wer­den Sie naturgemäß diese Excel-Datei laden (öff­nen) und anschließend (für diese Übung) den kom­plet­ten Dat­en-Bere­ich A1:I21 in den Pow­er Query-Edi­tor importieren. Belassen Sie es in dem Rah­men bei der Vor­gabe, dass der Bere­ich keine Über­schriften hat. Wie auch schon im drit­ten Teil dieser kleinen Serie beschrieben, müssen die Dat­en erst ein­mal ent­piv­otiert wer­den. Diese Vorge­hensweise an dieser Stelle aber nur im Schnell­durch­gang ohne umfan­gre­iche Erk­lärung. Wenn Sie das Prinzip inter­essiert, suchen Sie im Blog gerne unter dem Stich­wort ent­piv­otieren oder Sie schauen sich ein oder mehrere Teile dieses 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.

Direkt nach dem Import der Dat­en in den PQ-Edi­tor stellt sich das so dar:

Direkt nach dem Import seigt sich PQ so wie hier dargestellt

Da Sie mein­er Weisung fol­gend viele unnötige Dat­en (Zeilen und Spal­ten) importiert haben, wer­den Sie erst ein­mal die Dat­en „filetieren”, also auf das wirk­lich notwendi­ge Maß reduzieren. Das bedeutet, alle Spal­ten ober­halb der eigentlichen Über­schriften (die Pro­duk­te, Berech­nun­gen) und unter­halb der Monatswerte für Dezem­ber löschen. Danach auch die bei­den Spal­ten rechts der Spalte Son­stiges ent­fer­nen. Und last but not least noch die erste Zeile als Über­schrift hochstufen und eine sin­nvolle Über­schrift für die erste Spalte vergeben, beispiel­sweise Monat oder (bess­er noch) Datum.

Umstellung der Daten (entpivotieren) und aufbereiten

Markieren Sie die Spalte Datum, Recht­sklick in die Über­schrift und im Kon­textmenü ein Klick auf den Ein­trag Andere Spal­ten ent­piv­otieren. Für jeden Monat, an dem ein Umsatz getätigt wor­den ist, existiert nun eine Zeile mit den Monat­sna­men, den Pro­duk­ten und dem entsprechen­den Umsatz. Ide­al­er­weise wer­den Sie die Über­schriften der zweit­en und drit­ten Spalte entsprechend anpassen. Ich ver­wende Pro­dukt und Umsatz.

Start | Schließen & laden | Schließen & laden in… und wählen Sie Piv­ot­Table-Bericht, als Ziel bietet sich beispiel­sweise die Zelle K1 des aktuellen Excel-Arbeits­blatts an. Ziehen Sie im recht­en Seit­en­fen­ster das Feld Datum in den Bere­ich Zeilen, Pro­dukt nach Spal­ten und Umsatz nach Werte. Damit haben sie erst ein­mal die fast iden­tis­che Tabelle wie in den Ursprungs­dat­en, nur dass hier die reinen 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 einige Berech­nun­gen nicht (automa­tisch) gemacht wur­den.

So, das war erst ein­mal zum „aufwär­men“ und zur Darstel­lung des Prinzips in Sachen ent­piv­otieren. Schließen Sie gerne die eben erstellte Datei, mit oder ohne spe­ich­ern. Laden Sie nun das für die Lek­tion eigentlich vorge­se­hene File hier herunter; es ist die gle­iche Datei wie im vorheri­gen Kapi­tel, also die Jahres-Umsatz­zahlen auf der Basis der einzel­nen Tage des Jahres. Und es sind auss­chließlich die auszuw­er­tenden Dat­en ohne unnötige Über­schriften, Zusam­men­fas­sun­gen, Berech­nun­gen, … Der Import in den Pow­er Query Edi­tor kann also direkt und ohne Umwege erfol­gen.

Ändern Sie nun im ersten Schritt den Daten­typ der Spalte Datum auf (nur) Datum, damit die durchgängige Uhrzeit 00:00:00 „ver­schwindet“. Anschließend ent­fer­nen Sie die let­zte Spalte Gesamt; die Piv­ot Tabelle berech­net das von alleine. Markieren Sie nun wie bere­its eben durchge­führt die Spalte Datum, Recht­sklick in die Über­schrift und Andere Spal­ten ent­piv­otieren. Attrib­ut zu Pro­dukt umbe­nen­nen und Wert zu Umsatz.Dass diese Abfrage mit dem 2. Jan­u­ar begin­nt und auch weit­ere kalen­darische Dat­en nicht aufge­führt sind liegt daran, dass nur Tag/Pro­dukt - Kom­bi­na­tio­nen aufgenom­men wer­den, wo die Zelle mit dem Umsatz nicht leer (also Inhalt null) ist.

Ähn­lich wie auch eben schon durchge­führt, in direk­tem Wege einen Piv­ot­Table-Bericht erstellen. Der der besseren Trans­parenz wegen schlage ich vor, die Piv­ot­ta­belle auf einem neuen Tabel­len­blatt zu platzieren. Die 3 Piv­ot­Table-Felder wie gehabt in die entsprechen­den Bere­iche ziehen.

Um über­haupt ist erst ein­mal ein Gefühl für die Dat­en zu bekom­men, Klick­en Sie in ein beliebiges kalen­darisches Datum und wählen Sie dann über die Schalt­fläche Grup­pieren bzw. Auswahl grup­pieren die Optio­nen Quar­tale und Monate. So weit, so gut. Allerd­ings ste­hen dort ja die bekan­nten, absoluten Umsatz-Zahlen mit prinzip­iell der gle­ichen Aus­sagekraft wie in der Orig­i­nal-Tabelle auf dem ersten Arbeits­blatt. Gefragt war ja der prozen­tuale Anteil der einzel­nen Pro­duk­te im Ver­hält­nis zum Gesam­tum­satz des Zeitraums. 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

Anhand dieses Beispiels zeige ich Ihnen ein­mal den Weg auf, wie Sie von der Darstel­lung der absoluten Zahlen mit weni­gen Mausklicks zu dem hierüber gezeigten Ergeb­nis kom­men. Ich beginne damit, in Zelle A2 die Über­schrift auf Datum zu ändern. Ich finde das passender. Anschließend sorge ich dafür, dass die Umsatz-Zahlen im Währung- Oder Buch­hal­tung-For­mat dargestellt wer­den, wobei die tausender Punk­te automa­tisch einge­fügt und die Nachkom­mas­tellen in jedem Fall auf zwei Stellen for­matiert wer­den. Ich räume ein, dass dieser Schritt für das endgültige Vorhaben nicht erforder­lich ist, aber es kön­nte ja sein, dass Sie eine zweite Piv­ot Tabelle mit den realen Werten erstellen wollen und da sieht das schon deut­lich pro­fes­sioneller aus. 😉

Okay, weit­er zum angedacht­en Ziel. Nun markiere ich eine beliebige Umsatz­zahl, klicke im Bere­ich Werte auf den einzi­gen Ein­trag Summe von Umsatz | Wert­felde­in­stel­lun­gen | (im Dia­log: Reg­is­ter) Werte anzeigen als. Das Feld Benutzedefiniert­er Name ändere ich auf Umsatz Prozen­tu­al und erweit­ere nun Werte anzeigen als; im nun sicht­baren Drop­down wäh­le ich % des Zeilenge­samtergeb­niss­es und bestätige dann mit OK. A1 ändere ich noch auf Umsätze, B1 ändere ich auf ein Leerze­ichen und das Ergeb­nis ist meinem Wun­sch entsprechend.

Pro­bieren Sie gerne weit­ere Optio­nen, um die Berech­nung nach Ihren Wün­schen anzu­passen. Wenn Sie noch etwas „Nach­hil­fe” in Sachen Piv­ot­Table Basics haben möcht­en, biete ich Ihnen mehrere Beiträge aus unserem Blog an, Start wäre beispiel­sweise hier.

Lei­der bietet die nor­male Piv­ot­Ta­belle keine direk­te Möglichkeit, bei gegebe­nen kalen­darischen Dat­en die Grup­pierung auf Kalen­der­wochen anzuwen­den. Das geht über einige Umwege und es stellt sich mir die Frage, ob es den Aufwand wert ist.

Im näch­sten Abschnitt wer­den wir eine ähn­liche Auswer­tung nur mit Pow­er Query machen, wobei der Schw­er­punkt darauf liegt, dass mehrere unter­schiedliche Auswer­tun­gen in ein­er Arbeitsmappe erstellt wer­den.

Mein bis hier­her erar­beit­etes Ergeb­nis kön­nen Sie hier herun­ter­laden. Und dieser Stand der Dinge ist auch (zumin­d­est für die Pow­er Query-Auswer­tung) die Basis der Auf­gabe im näch­sten  (let­zten) Teil dieses Work­shops. Dort kön­nen Sie dann auch nach­le­sen, warum schein­bar keine PQ-Abfrage existiert und wie Sie dann doch sicht­bar gemacht wer­den kann. Hier nur das Stich­wort: Dat­en | Abfra­gen und Verbindun­gen.

▲ nach oben …