Sie wollen eine Abweichung mit den verfügbaren „Boardmitteln“ Ihres Arbeitsplatz-PC’s in bester Form visualisieren? Darüber hinaus ist auch die Übersichtlichkeit Ihrer komplexen Auswertungen optimierungsbedürftig! Jetzt heißt es, mit Microsoft Excel zu zaubern, denn das „Hausmittel“ kann mehr als Sie glauben! Entweder Sie nutzen die bedingten Formatierungen von Excel oder aber Sie greifen zu einem echten Kunstgriff, der eine maximale und eindrucksvolle Wirkung erzielt; neue – bisher nicht gekannte – Darstellungsformen werden jetzt möglich…durch einen einfachen und wirkungsvollen Trick: Benutzen Sie einfach mal die WIEDERHOLEN-Funktion…und Sie werden Ihr „Darstellungswunder“ erleben.

Abbildung 1 zeigt einen Visualisierungsvorschlag, der einfach möglich ist; ohne sich Gedanken über die genutzte Excel-Version, deren Möglichkeiten oder gar über Programmieroptionen Gedanken machen zu müssen. Microsoft mit den Versionen ab Excel 2003 bietet mehr, als Sie bisher zu glauben ahnten. Ich möchte Ihnen aber auch nicht vorenthalten welche Möglichkeiten diese Versionen sonst so bietet.

Entscheiden Sie selbst: Welche Variante ist für Sie die sinnvollste?

Abbildung 1: Ein Beispiel für eine in Microsoft Excel erstellte Tabelle nach „Corporate Information Design“ (CID)

Wer seine Excel-Tabellen signifikant les- und nachvollziehbarer gestalten möchte, der kommt an einer einheitlichen Notation nicht vorbei, die als überzeugender Standard im gesamten Unternehmen konsequente Anwendung findet und auch vor der Visualisierung von Firmenkennzahlen durch Diagramme oder gar Dashboards nicht halt macht; definieren Sie für sich ein Corporate Information Design, das überzeugt und ein Bekenntnis zur Klarheit ist! Wählen Sie eine einheitliche „Visualisierungssprache“ und lösen Sie sich von bunten, überladenen Darstellungen, die im Zweifelsfall lediglich durch Ihre undurchsichtige Farbvielfalt glänzen, nicht aber durch die Förderung der sekundenschnellen Wahrnehmung der wesentlichen, Controlling relevanten Inhalte. Spannende, weitergehende Informationen finden Sie auf der Website von Prof. Hichert+Partner unter SUCCESS (Der Meister der Notation!) oder in der gleichnamigen Gruppe bei XING.

Nach dem Kurzexkurs in die nur scheinbar theoretische Welt der Notation, komme ich jetzt wieder zur Sache: Abweichungen perfekt visualisieren! Welche Potentiale bieten die unterschiedlichen Excelversionen?

Microsoft Excel 2003

Das folgende Beispiel zeigt, wie Sie die Dastellung von Abweichungen der Spalten J, K, M und N durch Bedingte Formatierung… aus dem Menü Format und dem Einsatz der WENN-Funktion (z.B. J7=WENN(H7<0;H7;"") ) erreichen.

Abbildung 2: Abweichungen mit Microsoft Excel 2003

Microsoft Excel 2007

Mircosoft war auf dem Weg zur neueren Version nicht untätig; jetzt haben Sie als Anwender durch die bedingten Formatierungen erheblich mehr Möglichkeiten. Abbildung 3 zeigt zwei unterschiedliche Variationen.

Die Anwendung der WENN-Funktionen ist genauso, wie in der Vorgängerversion – Excel 2003.

Abbildung 3: Abweichungen mit Microsoft Excel 2007

Bei der ersten Abweichungsdarstellung ist alles wie zuvor; erst die zweite Abweichungsdarstellung zeigt die Verbesserung deutlich. Es ist schade, dass Datenbalken immer nur von links nach rechts wachsen können; dieser Umstand führt dazu, dass der Verlauf der roten Balken schlechter lesbar ist.

Um die Abweichung als Datenbalken darzustellen, gehen Sie folgendermaßen vor:

  • Wählen Sie die Zellen aus die eine gleichartige Formatierung erhalten sollen, z.B. N7:N11.
  • Klicken Sie auf Bedingte Formatierung aus der Multifunktionsleiste Start, dann Datenbalken und wählen eine Farbe.
  • Über den gleichen Weg und mit Klick auf Regeln verwalten, können sie anschließend die Formatierung nach Ihren Wünschen anpassen.

Microsoft Excel 2010

Einige der „Ungereimtheiten“ aus Excel 2007 wurden mit der 2010er Version korrigiert; so können jetzt – sehr zu meiner Freude – Datenbalken endlich auch von rechts nach links wachsen. Außerdem hat Microsoft wieder gelernt und die nächste sinnvolle Optimierung platziert: jetzt gibt es noch mehr Varianten der bedingten Formatierungen, die sichtbaren Nutzen stiften. In Abbildung 4 sind drei dieser neune Datenbalken zu sehen.

Die Anwendung der WENN-Funktionen bleibt, wie beim Beispiel mit Excel 2003, weiterhin bestehen.

Abbildung 4: Abweichungen mit Microsoft Excel 2010

Nun zu den Abweichungen:

  • Bei der ersten Darstellung (Spalten K und L) ist ein Datenbalken mit Farbverlauf zu sehen. Microsoft verwendet die Breite der gewählten Spalte als Referenz für die Längen der Balken. So stehen die Werte der längsten Balken immer in den Balken.
  • Durch die zweiten Darstelllung (Spalte N) wird deutlich, dass eine Abweichung jetzt auch direkt in einer Zelle angezeigt werden kann und Sie deren Mitte als Achse verwenden können. Will man allerdings die korrespondierenden Zahlen auch anzeigen, so werden diese bei negativen Werten nicht – wie sonst üblich – an den linken Rand der Zelle oder besser noch an den Balken gesetzt, sondern bleiben einheitlich formatiert. Die nächste wünschenswerte Ausbaustufe wird dann sicherlich mit der nächsten Version folgen – denn wir wissen: Microsoft wird immer besser!
  • Darstellung drei (Spalten P und Q) zeigt die gleichen Datenbalken wie die erste Darstelllung. Hier jedoch ohne Farbverlauf und mit Beschriftungen, die außerhalb der Balken liegen. Um dieser Darstellung zu realisieren, habe ich Hilfsrechnungen (graue Bereiche) hinzugefügt, um jeweils einen längeren Balken pro Richtung (Zellen P7 und Q7) zu erzeugen, der endlich angemessen Platz für die Beschriftungen schafft.

Gehen Sie folgendermaßen vor, um die Abweichung der Datenbalken für die dritte Abweichung darzustellen:

  • Als Vorbereitung: Sie brauchen nur eine Zeile oberhalb der Abweichungen und eine Spalte für die Hilfsrechnungen. Erzeugen Sie den absoluten Wert pro Zeile und ermitteln Sie dann den maximalen Wert. Jetzt addieren Sie noch ein Delta dazu, das für den Platz der Beschriftungen steht.
  • Wählen Sie dann die Zellen aus, die eine gleichartige Formatierung erhalten sollen, z.B. Q7:Q12.
  • Klicken Sie auf Bedingte Formatierung aus der Multifunktionsleiste Start, dann Datenbalken und wählen eine Farbe.
  • Über den gleichen Weg und mit einem Klick auf Regeln verwalten können Sie anschließend die Formatierungen der vorhandenen Regeln bedarfsgerecht anpassen. In diesem Fall können Sie auch die Balkenrichtung, oder wie mit negativen Werten und der Achse umgegangen werden soll, definieren.
Soweit der Überblick über die zahlreichen Möglichkeiten, die uns Microsoft Excel für die Darstellung von Abweichungsbalken bietet. Excel 2010 ist sicher bedeutender Schritt nach vorn; auch nach den Maßstäben der „Notationsbewussten“. Doch von klarer, eindeutiger Lesbarkeit von Kennzahlen kann immer noch nicht gesprochen werden. Die Beschriftungen sind immer noch nicht da, wo sie im besten Fall hingehören und differierende Abweichungen immer wieder mit denselben Datenbalken zu visualisieren, ist nicht genug. So bleibt vorerst unklar, ob es sich um einen absoluten oder prozentuellen Vergleich handelt – und das ist sicher bedeutungsschwer im Hinblick auf unternehmerische Entscheidungen.

 

Wenn Sie einen weiteren Schritt in die richtige Richtung gehen wollen, dann schauen Sie sich den Vorschlag in Abbildung 5 einfach an. Jetzt können Sie sofort und auf einen Blick erkennen, wie sich die aktuellen Produktmengen im Vergleich zum Vorjahr im absoluten und im prozentualen Vergleich entwickelt haben. Klar? Sicher!

Abbildung 5: Abweichungen mit Microsoft Excel und der WIEDERHOLEN-Funktion

Hier kommt nun endlich der angekündigte Trick – dramaturgisch spät, aber dann doch – mit der WIEDERHOLEN-Funktion ins Spiel:
WIEDERHOLEN(<Zeichen>;<Zeichenkette>)
Folgendes muss für diese Art von Abweichungsdarstellungen getan werden:

  • Für die Spalten L und O sowie M und P sind jeweils die gleichen Zellformate einzustellen.
  • In den Spalten I und J (grauer Bereich) werden Hilfsrechnungen eingefügt, welche später ausgeblendet werden. Die Spalte I bildet dabei z.B. den prozentualen Wert I8=(F8/D8-1)*100. In der Spalte J wird berechnet wie häufig ein Zeichen mit der WIEDERHOLEN-Funktion dargestellt werden soll, also z.B. J8=WENN(ABS(I8/$J$7)<=(100/$J$7);ABS(I8/$J$7);100/$J$7). Hier muss auch berücksichtigt werden, dass es eine Begrenzung der Zeichenwiederholungen passend zur Spaltenbreite geben muss. In diesem Beispiel soll ein Zeichen alle 20%-Schritte gezeichnet werden bei einer Begrenzung auf 5.
  • Ich möchte beispielhaft auf die positive, absolute Abweichung (Spalte M) eingehen, für die folgende Formel verwendet wird: M8=WENN(H8>0;WIEDERHOLEN("█";J8) & " " & TEXT(H8;"+0;-0");""). Das █-Zeichen kann über den Symbol-Befehl aus dem normalen Schriftzeichensatz und der Untergruppe Blockgrafikzeichen einkopiert. Es wird so häufig – wie in Schritt 2 berechnet – wiederholt. Zusätzlich wird mittels der TEXT-Funktion die Zahl so formatiert, dass immer ein Vorzeichen dargestellt wird, denn Abweichungen haben immer einen positiven bzw. negativen Charakter.>

Ich hoffe sehr, einige – besser noch viele – Denkanstöße geliefert zu haben, um Ihre Tabellen nachhaltig zu optimieren. Der Aufwand lohnt sich ganz sicher, wenn Daten für ein „wiederkehrendes“ Reporting Verwendung finden.
Abschließend sei dem Exceltüftler Holger Gerths mein besonderer Dank ausgesprochen, der immer wieder Möglichkeiten und Potentiale ausgräbt, die selbst dem Hersteller zumeist verborgen blieben.

Auf bald,

Ihr Jörg Knuth

Category: Blog
Schlagwörter: