Kategorie: Charts

Risikomatrix in Excel

Im Zuge meiner Ausbildung zum Projektfachmann bei der GPM und während meiner Arbeit im Projektcontrolling einer großen deutschen Landesbank habe ich ordentlich mit Risiken im Rahmen des Projektmanagements zu tun gehabt. Risiken müssen nicht nur erfasst und benannt werden. Es gilt, sie permanent zu kontrollieren, zu aktualisieren und entsprechende Ersatzpläne im Schrank zu haben.

Eine einfache Liste von Risiken kann dann mit Hilfe eines Ampelsystems visualisiert werden. In der Regel werden die Risiken dann auch in einer Matrix optisch verortet.

Die Risiken werden in einer Liste geführt. Jedes Risiko hat eine ID, ist einer Kategorie und einer Datei zugeordnet. In den Spalten Probability und Impact werden die Wahrscheinlichkeit und die Auswirkung des Einzelrisikos in Bezug auf das Projekt mit Zahlwerten von null bis vier verortet.

Grafisch können die Risiken dann in einer Matrix verortet werden. Dazu ist es aber notwendig, dass Risiken mit identischen Werten für Probability und Impact nicht direkt aufeinander liegen, sondern geordnet im selben Bereich untereinander liegen. Dafür ist eine Formel zu entwickeln, welche die Risiken aufteilt.

Möchte man ein Risiko im Zeitverlauf beobachten, kann auch die Entwicklung des Risikos im Diagramm dargestellt werden.

Nun lässt sich der zeitliche Weg der Entwicklung des Risikos nachverfolgen.

Der Aufbau der Risikomatrix und die damit verbundene Dynamik hat mich schon etwas stärker beschäftigt. Die Aufgabe selbst habe ich auch einer Gruppe von angehenden “Excel Experts” gestellt, einer Fortbildungsreihe für meine gewerblichen Kunden über 5-7 Termine über ein halbes Jahre hinweg, bei dem wir uns alle 4 Wochen zu Excel-Schulungen und Besprechung von Excel-Themen treffen. Immer die gleiche Gruppe, die so schrittweise Stück für Stück Excel vertieft und zwischenzeitlich die Gelegenheit hat das Wissen in der Praxis auszuprobieren und mit Fragen in die nächste Runde kommt.

Das Wissen und die Dateien hier im Blog und bei YouTube kostenlos bereitzustellen funktioniert nur dank der regelmäßigen Unterstützung einiger Zuschauer – vielen Dank an euch! – und darüber, dass zumindest die Kosten für Technik und Lizenzen über die YouTube-Werbeeinnahmen gedeckt werden können.

Hier nun die ganzen Videos, in denen der Aufbau Schritt für Schritt erläutert wird. Ich nutze selbst Excel aus Office 365. Die Techniken sollten aber auch mit Excel 2013 funktionieren. Ganz unten gibt es noch einen Hinweis für Nutzer von Excel 2010, da dort die Beschriftung der Punktepaare nicht so einfach umgesetzt werden kann.

Die Aufgabenstellung

Videolink: https://youtu.be/6seitkSJxNE

Die folgenden Dateien können genutzt werden, um die Videos nachzuarbeiten.

Mit folgender Datei wird in Lösung 1 gestartet: E943 Start Risikomatrix

Dies ist die resultierende Datei: E943 Ergebnis Risikomatrix

Die Matrix aufbereiten

Videolink: https://youtu.be/xvGZzgi0MFE

In folgender Datei für Lösungsvideo 2 werden die Risiken ordentlich aufgeteilt: E944 Risikomatrix

Texte in einem Feld verteilen

Videolink: https://youtu.be/8d0UxvKjL5E

Soll auch gefiltert werden können, müssen die überflüssigen Werte neutralisiert werden: E945 Risikomatrix

Risikogruppen filtern

Videolink: https://youtu.be/xoHUz2vdgNw

Im Zuge der Risikoüberwachung sollen die Einzelrisiken im Zeitverlauf beobachtet werden. Von Meeting zu Meeting werden die Risiken in den Punkten Eintrittswahrscheinlichkeit und Höhe des Einflusses auf das Projekt neu bewertet. Diese Darstellung ermöglicht nun auch eine Beobachtung, wie das Projekt bewertet wurde. Ausgangsdatei: E946 Start Risikomatrix Endstand passend zum Video: E946 Ergebnis Risikomatrix

Risiken im Zeitverlauf überwachen:

Videolink: https://youtu.be/7AYV058QkVg

Die Datenbeschriftung von Punkten im Diagramm wurde mit Excel 2013 massiv verbessert. Für vorherige Versionen kann das kostenlose Add-In XY-Chart Labeler genutzt werden. Ich beschreibe es im folgenden Video:

Videolink: https://youtu.be/rNKzwWTkN-c

Viel Erfolg in euren Projekten
Andreas

Dashboard Design – Buchempfehlung

Ich habe schon so viele Dashboards gesehen, erstellt und modifiziert, doch eine wirklich gute systematische Einführung über Dashboard-Design und die begleitenden Überlegungen hatte ich bislang noch nicht gelesen.

Vielleicht kennt ihr meine Videos zum Tachometer, zur Statusanzeige und den Dashboards… schöne visuelle Spielereien mit Excel. Ich möchte aber wirklich sinnvolle und aussagekräftige Dashboards zur aktuellen Situationen des Unternehmens und daraus abzuleitende Maßnahmen entwerfen. Hierzu helfen mir die Überlegungen aus Stephen Fews Buch “Informationen Dashboard Design” weiter.

Hier geht es überhaupt nicht um Excel. Hier geht es um den Sinn von Dashboards, Vorüberlegungen, Ergründung und viele negative und positive Beispiele. Wozu dienen Dashboards und wie unterscheiden sich diese von einer Datenanalyse, einem Report, einer Scorecard und einem Portal.

Empfohlen wurde mir das Buch auf dem diesjährigen Amsterdam Excel Summit von David Hoppe –> Hartelijk dank! David hatte uns sehr informative grafische Darstellungen und Bullet Graphs demonstriert. Letztere möchte ich euch auch nicht vorenthalten.

Wer also keine gebundenen englischsprachigen Wälzer im Großformat mit knapp 245 Seiten scheut, könnte hier für ein paar Euro ein wirklich sehr gelungenes Werk erwerben. Die systematische Aufstellung der Fragen und zu beachtenden Aspekte wird mich zukünftig noch mehr bei der Erstellung zielgerichteter Dashboards unterstützen.

Und natürlich wird es mir Spaß machen, die dort gezeigten Diagramme mit Excel nachzubauen und in meinen Seminaren und Videos zu erläutern.

Ich wünsche einen perfekten Sommer.
Gruß aus Berlin
Andreas

P.S.: Seit dem Jahr 2014 finden in Amsterdam viele Excel MVPs, Controller, Trainer und Berater zusammen, um voneinander zu profitieren, Informationen auszutauschen und mindestens ein bis zwei lekker Tage in Amsterdam zu verbringen. Organisiert wird das Trainingsevent von Jan Karel Pieterse (MVP) und Tony de Jonker (MVP).
2017 geht’s weiter: http://topexcelclass.com/

Differenzen im Diagramm ausweisen

In einer kleinen dreiteiligen Reihe habe ich ein gestapeltes Säulendiagramm dafür genutzt, die Differenzen im Diagramm zwischen vorletzter und letzter Säule anzuzeigen.

Dabei sind die Werte entweder positiv angestiegen und werden auch mit einem Pluszeichen ausgewiesen, oder die negative Abweichung wird mit einem Minuszeichen angezeigt. Da die Säulenhöhe meines gelben Hilfsbereichs positiv ist, ist die negative Ausweisung auch nicht selbstverständlich.

(Hinweis: Aktuelle Ankündigen nun wieder über http://twitter.com/athehos )

Die gelben horizontalen Hilfsstriche wurden mit Hilfe von Säulensegmenten gelöst – beschrieben mit einer manuellen Lösung in Video 1., die Berechnung der Segmente per Formel in Video 2 und in Video 3 wurden die Labels ausgerechnet.

Zudem wird in Video 3 eine Lösung gezeigt, wie man die Differenz eleganter ausweisen kann. Ab Excel 2013 gibt es die Möglichkeit, gezielt eine weitere Datenreihe für Beschriftungen zu nutzen, für vorherige Excel-Versionen gibt es auch das kostenlose Excel-Add-In XY-Chart-Labeler.

Hier ist die Übungsdatei: Excel 722 Diagramm letzte Änderung

Video 1 (Excel # 722)

Link zum Video: https://youtu.be/qvsqcYvtqXc

Video 2 (Excel # 724)

Link zum Video: https://youtu.be/yGkVi_uQ6kA

Video 3 (Excel # 726)

Link zum Video: https://youtu.be/MqSMIw-39Hw

Und als Ergänzung der XY-Chart-Labeler

Link zum Video: https://youtu.be/rNKzwWTkN-c

Excel – Balkendiagramm in 3D-Optik

Gestapelte 3D-Balkendiagramme von der Stange sehen erst einmal recht nüchtern aus. Irgendwie wie zusammengesteckte Bausteine (vier Buchstaben).
Mit ein wenig Anpassung kann die Optik aber modifiziert werden.

601_A

Aus den folgenden Daten werde ich über das Register EINFÜGEN ein gestapeltes 3D-Balkendiagramm erzeugen:

601_B

Das sieht erst einmal nach wenig aus.

601BB

Also werden Gitternetze, Hilfslinien, Achsen etc. entfernt. Eventuell erübrigt sich auch die Legende. Dazu kommen dann noch die Datenbeschriftungen für den linken Teil des Balkens.

Im 3D-Format wird eine Drehung eingestellt. Vorher habe ich noch die Farben der Datenreihen verändert. Die Tiefe habe ich auf 0 gesetzt (anschließend waren es wieder 20). Allerdings verbleibt noch ein kleiner dunkler Rand unter dem oberen Balken.

601_C

Zu guter Letzt noch ein grafischer Farbverlauf für den Hintergrund, der die 3D-Optik unterstützt.

601_D

Hier gibt es die Datei mit der Grafik:
Excel 601 Balkendiagramm 3D

Hier geht es zum Video:
Videolink: http://youtu.be/zTrMGa3De30

Excel – Diagrammfarben per VBA zuweisen

Den Datenreihen meines Diagramms möchte ich feste Farbwerte zuweisen. Die gleiche Stadt soll im Diagramm immer die gleiche Farbe zugewiesen bekommen.

Wie das Makro funktioniert, sehen Sie hier:

Videolink: http://youtu.be/_1ue9imc4yc

VBA-Code:
Sub Farben_Diagramm()

Dim chtDiagramm As Chart
Dim i As Integer, j As Integer, intColor As Integer, intSeries As Integer
Dim strName As String, strChart As String, strBlatt As String
On Error GoTo ErrorHandler
    strBlatt = “Versuch”
    strChart = “chartPersonal”

    Set chtDiagramm = Sheets(strBlatt).ChartObjects(strChart).Chart
    intSeries = chtDiagramm.SeriesCollection.Count

    chtDiagramm.SetElement (msoElementDataLabelNone)
    chtDiagramm.SetElement (msoElementDataLabelCenter)

    For i = 1 To intSeries
        strName = chtDiagramm.SeriesCollection(i).Name
        For j = 2 To Range(“rng_Orte”).Value + 1
            If Sheets(“Versuch”).Cells(j, 9).Value = strName Then
                intColor = Sheets(“Versuch”).Cells(j, 14).Value
                With chtDiagramm.SeriesCollection(strName)
                    .Format.Fill.Visible = msoTrue
                    .Format.Fill.ForeColor.RGB = RGB(Sheets(“Versuch”).Cells(j, 11).Value, _
                        Sheets(“Versuch”).Cells(j, 12).Value, Sheets(“Versuch”).Cells(j, 13).Value)
                       
                    With .DataLabels.Format.TextFrame2.TextRange.Font.Fill
                        .ForeColor.RGB = RGB(intColor, intColor, intColor)
                        .Solid
                    End With
                    .DataLabels.Format.TextFrame2.TextRange.Font.Bold = msoTrue
                End With
               
            End If
        Next j
    Next i
   
    Exit Sub
   
ErrorHandler:
    MsgBox “Ein Fehler ist aufgetreten”, vbInformation, “Fehler ” & Err.Number
End Sub

Wie kommt man auf so einen Code? Ich habe mir die Makroaufzeichnung zu Nutze gemacht:

Videolink: http://youtu.be/iHae8Su6GGo

Hier wird der Code von oben erläutert:
Videolink: http://youtu.be/Yrl7-PZiMjg

Excel – PivotChart und VBA – Summe oberhalb eines gestapelten Säulendiagramms

Bei einem normalen gestapelten Säulendiagramm kann die Summe schnell oberhalb der Säulen platziert werden. Doch wie sieht es bei einem Pivot-Diagramm aus? Die Spalte Gesamtergebnis lässt sich leider nicht in das Diagramm integrieren, oder?

Ich behelfe mir mit einem berechneten Element, dieses bildet die Summe der sichtbaren also über den Filter definierten Elemente. Anschließend verstellt sich das Layout des Diagramms, auch dies muss korrigiert werden.

Zunächst die Videos – und steht die VBA-Lösung.

Video 1 – Aufbau des Diagramms und des berechneten Felds
Videolink: http://youtu.be/wAI2afucNcI

Video 2 – Intermezzo 1 – Die Makroaufzeichnung berechnetes Element
Videolink: http://youtu.be/j7kwBgZVzZc

Video 3 – Intermezzo 2 – Die Makroaufzeichnung Anpassung Diagramm
Videolink: http://youtu.be/JP3TZPv2cfo

Video 4 – Beschreibung des VBA-Codes
Videolink: http://youtu.be/CKxazIaH6qc

VBA-Code:

Sub Summe_Säulendiagramm()
Dim strFormel As String
Dim strElement As String
Dim i As Integer
Dim intItem As Integer
Dim intAnzahlItems As Integer

On Error GoTo ErrorHandler

strFormel = “=”
intItem = 0
Sheets(“Auswertung”).Range(“B4”).Select
intAnzahlItems = ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”).PivotItems.Count

For i = 2 To intAnzahlItems
If ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”).PivotItems(i).Visible = True Then
intItem = intItem + 1
strElement = ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”).PivotItems(i).Name
If intItem > 1 Then strFormel = strFormel & “+”
strFormel = strFormel & strElement
End If
Next i

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”) _
.PivotItems(“Summe”).Formula = strFormel

ActiveSheet.ChartObjects(“Diagramm 1”).Activate
With ActiveChart
.FullSeriesCollection(“Summe”).ChartType = xlLine ‘In Excel 2010 SeriesCollection
.FullSeriesCollection(“Summe”).DataLabels.Select ‘In Excel 2010 SeriesCollection
.SetElement (msoElementDataLabelTop)
.PlotArea.Select
.FullSeriesCollection(“Summe”).Format.Line.Visible = msoFalse ‘In Excel 2010 SeriesCollection
End With
Exit Sub

ErrorHandler:
MsgBox “Ein Fehler ist aufgetreten. Wird Summe angezeigt? Haben Sie auch Daten ausgewählt?”, _
vbCritical, “Aktion abgebrochen”
End Sub

Excel – PowerPivot – Berechnete Felder – Measures

In PivotTables können berechnete Felder die Berechnungen in Pivot-Tabellen ergänzen. Diese reguläre Option ist allerdings in mit PowerPivot erstellten PivotTables inaktiv. Statt dessen werden berechnete Felder wahlweise im Berechnungsbereich von PowerPivot oder im Register PowerPivot unter Berechnete Felder erstellt.

Diese Measures stehen dann in Tabellen, Diagrammen und in Power View (Excel 2013) zur Verfügung. Ebenso sollten die Daten für Power Maps genutzt werden können (noch nicht getestet).

Hier ist schon mal die Übungsdatei
Excel_439_PowerPivot_4

Und hier geht es zum Video
Videolink: http://youtu.be/TxXV-F-otOQ