Anleitung Excel-Diagramme

Für Interessierte an dieser Stelle eine kleine Anleitung in die Technik der kombinierten Diagrammerstellung mit Excel. Als Beispiel dient eine kleine Auswahl der in der Lottozahlen-Infografik verwendeten Diagramme. Im unteren Bereich des Dokuments erfolgt eine Beschreibung des Lottozahlengenerators, der kostenlos zum Download verfügbar ist. Es sollte zunächst erwähnt werden, dass ein gutes Diagramm nicht nur eine technische Angelegenheit ist, sondern zum guten Teil von gestalterischen Fähigkeiten abhängt. Hier wären vor allem Farbauswahl, Farbkombination und Größe der Diagrammelemente zu nennen. Ebensowichtig ist zu wissen was im Diagramm unnötig ist und weggelassen werden sollte. Dass diese Fähigkeiten nicht selbstverständlich sind, zeigen die vielen Gruseldiagramme aus der Vergangenheit als Geschäftsdiagramme so bunt wie ein Weihnachtsbaum waren und aggressive Farben gemischt bzw. unpassende 3-D Elemente verwendet wurden. Hier schien der Grundsatz: "weniger ist mehr" oder "der Meister zeigt sich in der Beschränkung" noch unbekannt zu sein. Mittlerweile ist das aber besser geworden, weil für die Design-Neandertaler ein breites Nachhilfeangebot im Design Thinking zur Verfügung steht.

Als Grundfarbe wird hier grau bevorzugt, weil diese Farbe dezent einsetzbar ist und sich mit vielen anderen Farben kombinieren läßt ohne dass es schnell bunt wird. Ein Beispiel wie grau in verschiedenen Tönen und in Kombination mit anderen Farben verwendet werden kann, zeigt folgende Abbildung:

Lottozahlen 2016

Dargestellt sind die Lottozahlen aus den ersten beiden Monaten des Jahres 2016. Nachfolgend eine Beschreibung einzelner Diagramme aus obiger Abbildung.


Das Diagramm zur Darstellung der großen und kleinen Lottozahlen hat mit zwei Zeilen und drei Spalten eine einfache Datenbasis. Der Diagrammtyp kann ohne Anpassungen der Struktur aus den Excel-Vorlagen übernommen werden. Lediglich überflüssige Elemente wie vertikale Achsenbeschriftungen müssen entfernt werden, da die Häufigkeiten bei einer kleinen Anzahl von Säulen besser direkt in den Säulen der Diagramme angezeigt werden:

Diagramm Säule


Das Diagramm zur Darstellung der Häufigkeiten der einzelnen Lottozahlen 1-49 ist etwas komplexer, da hier verschiedene Diagrammtypen in einem Diagramm integriert werden müssen. Neben dem Säulendiagramm (gestapelte Version) zur Darstellung der Samstags- und Mittwochszahlen ist ein Bereichsdiagramm

Bereichdiagramm


in der gestapelten Version nötig um die Standardabweichung (graue Fläche) zu integrieren. Zusätzlich sind zwei Linien-Diagramme nötig um die Durchschnitte (gestrichelte Linie) für kleine und große Zahlen getrennt darzustellen:

Lotto-Diagramm

Hier sind in der Datenbasis 6 Spalten nötig um die Aufgabe zu bewältigen. Die ersten beiden Spalten mit den Samstag - und Mittwochzahlen sind für das gestapelte Säulen-Diagramm, die 3. Spalte wird für die leere Fläche benötigt, das festgelegt werden muss wo die Standardabweichung beginnt. Diese leere Fläche wird unsichtbar gemacht indem sie mit "keine Farbe" oder der Hintergrundfarbe des Diagramms formatiert wird. Die 4. Spalte enthält die Standardabweichung, die zusammen mit der leeren Fläche den oben erwähnten Excel-Diagrammtyp "Bereich" bekommt.

Die beiden letzten Spalten sind für die zwei gestrichelten Linien (sieht aus wie eine, sind aber zwei) für die jeweiligen Mittelwerte für kleine (1-24) und große (25-49) Lottozahlen. Um häßliche Abfälle der Linie am Beginn/Ende zu vermeiden muss zu einem Trick gegriffen werden, indem jede Zeile in der keine Linie und auch kein Abfall der Linie angezeigt werden soll ein #NV eingefügt wird. Folgerichtig steht bei den großen Zahlen in den Zeilen 1-24 ein #NV, da der Mittelwert für große Zahlen bei den kleinen Zahlen nichts zu suchen hat. Umgekehrt steht bei den kleinen Zahlen ab Zeile 25 ein #NV (oben nicht zu sehen), damit der Kleine-Zahlen-Mittelwert hier nicht angezeigt wird.


Das folgende Diagramm zur Darstellung der sieben Reihenhäufigkeiten scheint mit dem Vordiagramm bis auf die umgekippte Säule (Excel nennt es Balken) vieles gemeinsamt zu haben, ist aber technisch doch anders erstellt, weil Excel bei Balkendiagrammen keinen Diagrammtyp Bereich anbietet:

Balkendiagramm


Damit hier auch die graue Fläche für die Standardabweichung angeboten werden kann, muss anders verfahren werden. Nachdem ein Balken-Diagramm (gestapelte Version) erstellt wurde liegen zunächst alle Spalten gestapelt nebeneinander vor, und zwar so:

Excel-Diagramm

Damit die leere Fläche nicht wie oben erst nach den Samstagszahlen beginnt, sondern in der vertikalen Achse bei null anfängt, müssen die Samstagszahlen auf die Sekundärachse verschoben werden. Also Samstag-Balken selektieren und Sekundärachse bei Reihenoptionen einstellen:

Exceldiagramm

Damit beginnen die auf der Primärachse verbliebenen Spalten leer und Standardabweichung bei null wie es hier benötigt wird. Es ist noch darauf zu achten, dass die Balken für die Standardabweichung bei Abstandsbreite 0% haben (kein Abstand). Dadurch berühren sich die Balken der sieben Reihen und es entsteht der Effekt, dass es sich um eine zusammenhängende Fläche handelt. In Wirklichkeit sind es aber sieben Balken:

Balkendiagramm





Wie funktioniert der Excel-Lottozahlengenerator


Excel-Lottozahlengenerator basiert auf Excelformeln zur Erzeugung von Zufallszahlen 1 bis 49 und enthält keine Makros. Ohne Makros müssen die Zahlen allerdings manuell in die Tipptabelle übertragen werden. Weiter unten wird daher eine Anleitung gegeben wie man den Excel-Lottogenerator um eine Schaltfläche erweitert, welche die Zahlen automatisch in die Tabelle überträgt.

Zunächst wird in Hilfsspalte T eine Zufallszahl mit der Excelfunktion Zufallszahl() erzeugt. Diese liefert zunächst eine Zahl zwischen 0 und 1,00 z.B. 0,46 und muss noch mit 49 multipliziert werden um eine Zahl über 1 zu bekommen. Mit der Funktion Ganzzahl() muss noch dafür gesorgt werden, dass keine Nachkommastellen ausgegeben werden. Die komplette Formel sieht so aus: =GANZZAHL(49*ZUFALLSZAHL()+1)

Zufallszahl


Die Formel =KKLEINSTE(T$3:T$8;ZEILE()-2) in Spalte U sorgt dafür, dass die Zufallszahlen in aufsteigender Folge vorliegen. Einfache Formeln in Zellen C3 bis H3 holen die Zufallszahlen aus Spalte U ab und zeigen diese über dem Excel-Lottoschein an. Zuletzt müssen die Zufallszahlen noch im Lottoschein markiert werden, damit sie auf einen Blick sichtbar sind. Hier kann die Bedingte Formatierung von Excel benutzt werden, die folgendermaßen eingerichtet wird:

Start-Tabreiter -> Button Bedingte Formatierung -> Regeln verwalten

Format

Zellbereich C5:I11 für den Lottoschein mit Dollarzeichen für absolute Bezüge eintragen. Button Regel bearbeiten ... führt zur Formel =ZÄHLENWENN($C$3:$H$3;C5), welche die Bedingung definiert wann Formatierung greift. Hier wird also nur formatiert, wenn die Lottozahl aus dem Lottoschein mit der Zufallszahl übereinstimmt. Zuletzt muss noch die Farbe für die bedingte Formatierung (Schaltfläche: Formatieren ...) bestimmt werden:

Farbe 




Schaltfläche für Datenübertrag erstellen


Eine nützliche Funktion ist eine Schaltfläche, welche die Zufallszahlen in die Tipp-Tabelle überträgt, wo diese dauerhaft gesammelt werden können. Diese Schaltfläche ist einfach zu erstellen und braucht nur wenige Zeilen VBA-Code.

Button im Bereich ActiveX-Steuerelemente auswählen:

Schaltflaeche


mit dem Mouse-Pointer den Button/Schaltfläche auf gewünschte Größe ziehen -> Eigenschaften Fenster anzeigen um den Button umzubennenen Tabreiter Entwicklertools -> Eigenschaften (Entwurfsmodus muss aktiviert sein):

Button anlegen

Bei Caption den Button-Text ändern: hier auf =>

Button umbennen



Die Schaltfläche mit der Mouse ein wenig verkleinern:

Entwurfsmodus


Damit die Schaltfläche auch was tut muss dieser VBA-Code hinzugefügt werden. Ein Doppelklick auf die Schaltfläche (im Entwurfsmodus) führt in die Entwicklungsumgebung wo das Coding eingefügt werden kann:

VBA Code


Erste und letzte Zeile werden automatisch von Excel eingefügt, so dass nur 3 Zeilen VBA-Code eingefügt werden müssen, damit die Zufallszahlen über dem Lottoschein in die Tipp-Tabelle kopiert bzw. übertragen werden:

Private Sub CommandButton1_Click()

Dim iii As Integer

iii = Cells(Rows.Count, 12).End(xlUp).Row + 1

Cells(iii, 12).Resize(1, 6).Value = Range("C3:H3").Value

End Sub


Damit die Schaltfläche den Code ausführen kann muss vorher der Entwurfsmodus durch Klicken auf den entsprechenden Button in der Excel-Symbolleiste beendet werden.