Dynamische Formatierung in Excel und Cube-Formeln

International eingesetzte Reports müssen einige Punkte berücksichtigen. Einer davon ist die stark variierenden Größenordnung unterschiedlicher Währungen. So entspricht beispielsweise ein Euro in etwa 14.000 indischen Rupien – eine Betrag in IDR hat also mindestens um vier Nullen mehr als der Eurobetrag.

Um die Berichte dennoch leserlich zu halten empfiehlt sich die Darstellung in Tausend, Millionen oder Milliarden, etc. Somit muss der Report auf die Änderung der Währung dynamisch reagieren.

Zu diesem Zwecke bestimme ich zunächst über eine CUBEVALUE-Formel den Gesamtwert der gewählten Perioden unter Berücksichtigung der diversen Slicer. Alternativ kann auch ein Durchschnittswert je Periode errechnet werden.

Erwähnenswert ist, dass man einen Time-Slicer (eine Timeline) leider nicht einfach wie ein Set verwenden kann. Es muss deshalb zuerst explizit ein Set gebildet werden:

C1: =CUBERANKEDMEMBER(„Connection“,Timeline,1)

D1:=CUBERANKEDMEMBER(„Connection“,Timeline,CUBESETCOUNT(Timeline))

E1:=CUBESET(„Connection“,“{[DIM DATE].[YQMD].&[„&C1&“]:[DIM DATE].[YQMD].&[„&D1&“]}“,“SelectedMonths“)

Anschließend kann auf dieses Set wie folgt referenziert werden:

F1:=CUBEVALUE(„Connection“,“[Measures].[Net Revenue]“,Slicer1,Slicer2,…,E1)

Unter Verwendung des Zehnerlogarithmus wird nun dynamisch das Format bestimmt, wobei je nach Anzahl der gewünschten darzustellenden Stellen der unten errechneten Wert noch um eins zu reduzieren ist:

G1:=ROUNDDOWN(LOG10(F1)/3,0)

H1:=“#,##0.00″&REPT(„,“,G1) &““““ & VLOOKUP(G1,Matrix,2,FALSE) &““““

Matrix:=Lookup

Erläuterung: Jedes Komma, welches an den Formatstring (#,##0.00) angehängt wird, führt in der Darstellung zu einer Division durch Tausend. Also ergibt 123.456 in der Darstellung bei Verwendung von #,##0.00, den Wert 123.46.

Nun gibt es mehrere Möglichkeiten das Format anzuwenden:

  • Verwendung von TEXT(Zahl,H1)
  • Verwendung von Conditional Formatting ganzer Bereiche, wobei für jedes Format eine separate Regel angelegt werden muss. ACHTUNG: diese Art der Formatierung funktioniert nicht für Grafiken, diese verwenden immer das auf der Zelle hinterlegte Format.
  • Direkte Division der Werte durch POWER(10,G1). Diese Variante eignet sich besonders bei „halbdynamischen“ Pivots, welche die Zellwerte mittel CUBEVALUE auslesen.

 

Beispiel: Darstellung im Rahmen eines KPI Dashboards, links in EUR und rechts dieselbe KPI in IDR:

 

 

 

 

 

 

 

 

 

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s