XLS/PQ: Berechnung prozentualer Anteile (3)

Basis: Zeilen und Spalten (Excel)

Es liegt in der Natur der Sache, dass für eine der­ar­tige Auf­gabe andere Dat­en ver­wen­det wer­den müssen, als in Teil 1 oder Teil 2. Und „wie im richti­gen Leben” sind die zu importieren Dat­en nicht wirk­lich opti­mal für die weit­ere Ver­ar­beitung geeignet. Sie müssen erst ein­mal mehr oder weniger auf­bere­it­et wer­den. Laden Sie dazu dieses File der Bäck­erei Klein­brot und importieren Sie die tabel­lar­ische Auf­stel­lung in den Pow­er Query-Edi­tor.

Ich räume ein, dass dieses Zahlen­werk eine ide­ale Basis für eine Piv­ot­Ta­belle ist. Und bei genauer Betra­ch­tung ist Piv­ot­Table ja seit Gen­er­a­tio­nen ein nor­maler Teil des Excel. Und genau das soll auch im End­ef­fekt geschehen. Aber so gut, wie sich das zu Beginn anhört, so ernüchternd wird vielle­icht Ihr erster Ver­such aus­fall­en. – Ver­suchen Sie also erst ein­mal, aus der derzeit­i­gen Daten­struk­tur eine „vernün­ftige” Piv­ot­Ta­belle zu erstellen. Sie wer­den rasch erken­nen, dass die typ­is­chen Auswer­tun­gen für Pro­duk­te, deren prozen­tuale Anteile und das eventuell auch noch in zu definieren­den Zeiträu­men nicht möglich ist.

Dazu bedarf es ein­er Daten­satz-Liste, wie sie beispiel­sweise auch in ein­er Daten­bank gegeben ist. Um diese zu erstellen zeige ich Ihnen hier zwei Wege auf; zuerst in Plain Excel und im fol­gen­den Beitrag dieses Work­shops im Schnell­durch­gang die Vorge­hensweise unter Zuhil­fe­nahme des Tools Pow­er Query.

Entpivotieren in Plain Excel

Ja, es geht dur­chaus mit Excel-Formeln. Aber der Weg ist mir viel zu umständlich und für typ­is­che Anwen­der aus mein­er Sicht ein­fach zu intrans­par­ent (wenn Sie nicht ger­ade ein „exe­len­ter For­mu­lant” sind). 😉  Es geht mit­tel­bar über Piv­ot­Table! Und das aus mein­er Sicht recht kom­fort­a­bel. Und falls Sie vorhaben, öfter ein­mal oder sog­ar regelmäßig Kreuzta­bellen im „reinen” Excel-Umfeld oder auch XL-Ver­sio­nen vor 2013 für die Piv­ot-Auswer­tung zu nutzen, dann soll­ten Sie über­legen, statt der (gle­ich nach dieser Auflis­tung aufge­führten) Tas­tenkom­bi­na­tion den Aufruf des Piv­ot-Assis­ten­ten in die Schnel­lzu­griffs-Leiste zu leg­en:

• Klick­en Sie in dieser Leiste ganz rechts auf den Ein­trag mehr oder den nach unten weisenden Pfeil.
• Im Drop­Down Sym­bol­leiste für den Schnel­lzu­griff anpassen wählen Sie den Ein­trag Weit­ere Befehle.
• Erweit­ern Sie das Drop­Down Häu­fig ver­wen­dete Befehle und wählen dann die Zeile Alle Befehle.
• scrollen Sie herunter und wählen dann Piv­ot­Table- und Piv­otChart-Assis­tent.
• Klick­en Sie auf Hinzufü­gen » und anschließend OK.
• Nun kön­nen Sie per dieser Schalt­fläche in der Schnel­lzu­griff­sleiste diesen (in der fol­gen­den Abbil­dung gezeigten) Assis­ten­ten per Klick aufrufen

Bei einmaligen/seltenen Aktio­nen des Ent­piv­otierens in Plain Excel oder wenn Sie ein Fre­und von Tas­tenkom­bi­na­tio­nen sind, dann gelan­gen Sie ohne die eben aufge­führten Einzelschritte in den Piv­ot­Table- und Piv­otChart-Assis­ten­ten, wenn Sie AltNP Klick­en. Der Dia­log öffnet sich und Sie markieren hier den Ein­trag Mehrere Kon­so­li­dierungs­bere­iche:

Diese Markierun­gen sind kor­rekt und zielführend

Nach einem Klick auf Weit­er > sehen Sie dieses Fen­ster:

Diese Default-Werte belassen

Weit­er > und markieren Sie nun im näch­sten Fen­ster den Bere­ich der auszuw­er­tenden Dat­en, also A4:G16; die ganzen Berech­nun­gen der Ursprungsta­belle sollen nicht in die Piv­ot-Auswer­tung über­nom­men wer­den, das kann die Piv­ot-Funk­tion­al­ität viel bess­er 😎:

Der Bere­ich wurde über­nom­men, wieder Weit­er>

Nach einem Weit­er > leg­en Sie die Posi­tion der kün­fti­gen Piv­ot­Ta­belle fest, ich schlage hier statt eines neuen Tabel­len­blatts die Zelle K4 vor:

An dieser Posi­tion wird die Piv­ot­Table gle­ich einge­fügt

Nach einem Klick auf Fer­tig stellen sieht das Ganze fast wie die eben als Daten­quelle ver­wen­dete Kreuzta­belle aus, aber es ist eine Piv­ot­Ta­belle:

Diese Piv­ot­Table wurde automa­tisch erstellt

Okay, die teil­weise vie­len Nachkom­mas­tellen sind durch Berech­nun­gen (durch mich, wegen der Verteilung der Werte) in den Roh­dat­en ent­standen und kön­nen natür­lich durch Anzeige­for­mat Währung aus­geglichen wer­den. Aber den­noch gilt: Die Optik ist ja noch weit vom eigentlichen Ziel ent­fer­nt. 🙁 Jedoch: Ein Dop­pelk­lick in das berech­nete Gesamtergeb­nis (hier: Zelle R18) bringt in einem neuen Tabel­len­blatt annäh­ernd den gewün­scht­en Erolg:

Hier sind nur noch kleine Änderun­gen erforder­lich

Ja, nun noch die Spal­tenna­men anpassen, das Zahlen­for­mat ändern und Spalte D löschen. Das Ergeb­nis, die Liste ist eine „ganz nor­male” for­matierte („intel­li­gente”) Tabelle, welche Sie auch als Basis für eine Piv­ot­Ta­belle ver­wen­den kön­nen. Für eine Auswer­tung auf der Basis kalen­darisch­er Dat­en wäre eine Hil­f­ss­palte sehr hil­fre­ich, wo aus dem Text des Monat­sna­mens durch Hinzufü­gen eines Prä­fix 1. und eines Suf­fix  2014 ein echt­es Datum gener­iert wer­den kann.

▲ nach oben …

Teil 1 dieses Work­shops kön­nen Sie hier im Blog öff­nen.
Teil 2 dieses Work­shops kön­nen Sie hier im Blog öff­nen.

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)