Kategorie: Diagramme
Finanzierungsziel visualisieren
Mit Hilfe eines Liniendiagramms soll ein Finanzierungsziel visualisiert werden. Ob man einen Urlaub buchen oder etwas anderes finanzieren möchte… man benötigt Geldmittel. Die Entwicklung darüber, wie nah man wann seinem Ziel ist, kann man vielleicht optisch besser verfolgen.
Nachfolgend geht es um die Finanzierung einer Studienfahrt nach Auschwitz-Birkenau für Schülerinnen und Schüler der Erich Kästner-Gesamtschule in Bochum (siehe Blogbeitrag vom 02.12.2017).
2000 Euro werden benötigt, das Ziel soll vom 02.12.2017 bis zum 18.12.2017 erreicht werden. Folgenden Geldeingang kann man schon verzeichnen:
Ein Diagramm kann aber verständlicher zeigen, wie nah man seinem Ziel bislang gekommen ist. Darum habe ich eine neue Tabelle erstellt.
In dieser Tabelle werden die Tagessummen aus der oberen grünen Tabelle gebildet und der Fehlbetrag errechnet.
Formel B2 =WENN(SUMMEWENN(tab_Eingang[Datum];Auswertung!A2;tab_Eingang[Betrag])=0;#NV;SUMMEWENN(tab_Eingang[Datum];Auswertung!A2;tab_Eingang[Betrag]))
Formel C2 =SUMME([@Lücke]/ANZAHL([Lücke]);C1)
Formel D2 =SUMME(WENN(ISTZAHL(B2);B2;0);D1)
Formel F2 =MAX(0;[@Lücke]-[@[Kumulierte Summe]])
Formel für Diagrammzeile =“Restliches Finanzierungsziel zu „&TEXT(MAX(Tabelle2[Kumulierte Summe])/Tabelle2[@Lücke];“0,0 %“)&“ erreicht!“
Aus der Tabelle heraus wird ein Liniendiagramm erzeugt. Die Linie für die Tageswerte wird zu einem Säulendiagramm gewandelt.
(Finanzierungsstand des Förderprojekts, Stand: 16.12.2017)
Hier ist die Datei zum Nacharbeiten: E839 Liniendiagramm für Finanzierungsziel
Hier geht es zum YouTube-Video:
Videolink: https://youtu.be/wgiWcy-fPbo
Ich wünsche euch einen schönen Nikolaustag
Beste Grüße
Andreas
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

Äpfel, Zirkelbezug, Iteration & Co.
Anbei eine kleine Bildstrecke zum Video Excel – Äpfel, Zirkelbezug, Iteration & Co. Unten ist auch die Datei als Downloadlink verfügbar.
Man beachte den Zirkelbezug in Zelle O2! Die Formel ist unten im Bild aufgeführt.
Die kleine Formel aus D3 zeigt, dass nur Zufallswerte gezogen werden, wenn die Zelle J14 auf 0 steht.
Das ist die Formel in den Körben, stellvertretend für Zelle N14 mit der entsprechenden Formatierung für grüne Äpfel.
Damit der Zirkelbezug vernünftig berechnet werden kann, habe ich unter Datei – Optionen – Formeln die iterativen Berechnungen aktiviert und die Iterationszahl auf 1 gesenkt.
Hier gibt es die Excel-Datei zum Download:
Excel 723 Apfelernte
Hier geht es zum Video:
Videolink: https://youtu.be/PK_1E3aX6iM
Excel – Terminplan in Gantt-Ansicht – Kostenauswertung mit Pivot – Teil 8
In Excel-Gantt-Tutorial # 7 habe ich Kosten für mein Projekt ermittelt.
Die Auswertung mit Pivot bietet sich an.
Hier geht es zur Ausgangsdatei:
Excel626 Terminplan Start
Hier geht es zum Video:
Videolink: http://youtu.be/biXgmFsGyQE
Excel – Terminplan in Gantt-Ansicht – Meilenstein – Teil 4
Text folgt noch… für Eilige gibt es aber schon einmal die Übungsdatei:
Excel622 Terminplan
Hier geht es zum Video:
Videolink: http://youtu.be/8iRsIuj2sQM
Excel – Terminplan in Gantt-Ansicht erzeugen
Heute habe ich eine neue Serie bei YouTube gestartet: Die Erzeugung eines Terminplans in Excel mit Darstellung eines Gantt-Balkenplans über die bedingte Formatierung.
Die Tabelle auf der linken Seite steuert die Darstellung der Balken auf der rechten Seite. Zudem werden Meilensteine als Raute dargestellt.
Bei der Darstellung des Projektfortschritts können die Prozentwerte visuell dargestellt werden. Feiertage werden ignoriert.
Hier kann man der Playlist folgen:
Die Übungsdateien werden schrittweise zum Nachvollziehen pro Videoeinheit angeboten.
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.
Aus den folgenden Daten werde ich über das Register EINFÜGEN ein gestapeltes 3D-Balkendiagramm erzeugen:
Das sieht erst einmal nach wenig aus.
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.
Zu guter Letzt noch ein grafischer Farbverlauf für den Hintergrund, der die 3D-Optik unterstützt.
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 – Summe oberhalb gestapelten Säulen im Pivot-Chart
Die Darstellung von Summen oberhalb von gestapelten Säulen ist ein wenig trickreich. Leider gibt es dazu keinen einfachen Optionsschalter bei der Erstellung von Diagrammen.
Bei Diagrammen aus Pivot-Daten gibt es aber noch einen einfachen zusätzlichen Trick.
Generieren Sie eine zweite Tabelle und erstellen Sie daraus ein Säulendiagramm. Bei diesem Säulendiagramm kann man die Summen oberhalb von Säulen darstellen lassen. Anschließend werden die Diagrammfarben auf „ohne Füllung“ gesetzt.
Anschließend wird das eigentliche gestapelte Säulendiagramm auf Basis der ursprünglichen Pivot-Daten erstellt. Hier muss die Hintergrundfarbe der Diagramm- und Zeichnungsflächen unsichtbar geschaltet werden.
Nun sind beide Diagramme so übereinander zu platzieren, dass die Summen der unteren Säulen oberhalb der gestapelten Säulen erscheinen. Dazu muss eventuell noch der Bereich der Zeichnungsfläche angepasst werden.
Mehr dazu erfahren Sie im folgenden Video.
Link zum Video:
Videolink: http://youtu.be/3858JHaspRE

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
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.