Der gute alte Beatles-Titel war es nicht, der meinen Gastautor Gerhard Pundt zu diesem Beitrag veranlasst hat. Aber er passt trotzdem ganz gut zur Thematik.
Gerhard hat sich nämlich Gedanken über Sonnenauf- und -untergänge gemacht und sich gefragt, wie sich so etwas in Excel darstellen lässt.
Herausgekommen ist dabei ein schönes Excel-Modell und ein darauf aufbauendes, höchst kreatives Diagramm, welches den Verlauf der Sonne im Tagesablauf darstellt.
Wie das alles funktioniert, erfährst du – passend zur gestrigen Zeitumstellung – in diesem Artikel.
Die Excel-Datei mit dem fertigen Diagramm kann hier heruntergeladen werden.
Vorbereitung
Zunächst brauchen wir für jeden Tag die Sonnenaufgangs- und -untergangszeiten für unseren jeweiligen Wohn- oder Arbeitsort. Das ist gar nicht so schwierig, denn es gibt die Webseite www.sunrise-and-sunset.com/de/, auf der man Land und Ort auswählen kann. Ich habe mir Schwerin, die Landeshauptstadt von Mecklenburg-Vorpommern, ausgewählt. Jetzt ist noch der Monat auszusuchen, für den man die Daten sehen will.
Liegen nun die Daten des Ortes vor, werden daraus Datum, Sonnenaufgang und Sonnenuntergang in unsere Excel-Mappe in Tabellenblatt2 kopiert. Das Blatt benennen wir z.B. um in „Daten1“. Das Datum ist so wie kopiert nicht zu gebrauchen. Wir überschreiben es somit mit z.B. 01.09.2014, 02.09.2014 und ziehen mit dem Ausfüllkästchen bis zum 30.09.2014 nach unten. Die Beispieldatei enthält übrigens schon die Daten bis einschließlich Dezember 2014.
Datentabelle mit den Uhrzeiten
Der September beginnt mit Zelle B72, vorher sind schon der Juli und der August datenmäßig erfasst. Dieser Zelle geben wir den Namen „KnotSeptember2014“. Den Bereich B73:B102 markieren wir und geben ihm den Namen „September2014“. Mit allen weiteren Monaten wird analog verfahren.
Schritt 1: Die Wertetabelle
Tabellenblatt1 erhält z.B. den Namen „Focus1“. Es muss nun für die Auswertung vorbereitet werden. Zunächst wird eine Wertetabelle angelegt, die so aussieht, wie es die nachfolgende Abbildung zeigt:
Wertetabelle als Quelle für das Diagramm
Als lfd. Nr. werden die Zahlen von 1 bis 31 eingetragen. Das heißt letztlich, dass die Sonne im Diagramm 31 Stellungen einnimmt. Nichts spricht dagegen, z.B. 63 oder 95 Zahlen zu nehmen, nur muss es eine ungerade Anzahl sein, damit exakt der Höchststand (Zenit) auf der Mitte dieser Werte gezeigt wird. Alle weiteren Zellen in den Spalten B:E sind verformelt (siehe unten).
Für die Steuerung des Modells benötigen wir dann noch weitere fünf Zellen:
Steuerzellen
Die Zelle K5 benennen wir mit dem Namen „Auswahl“. Jetzt geht es an die Formeln:
B6 =G5+H5
B7 =B6+$J$5 (runter ziehen bis lfd. Nr. 30)
B36 =G5+I5
C6 =0
C7 =C6+2 (runter ziehen bis lfd. Nr. 16)
C22 =C21-2 (runter ziehen bis lfd. Nr. 31)
D6 =WERT(B6) (runter ziehen bis lfd. Nr. 31)
E6 =WENN(UND($H$8>=D6;$H$8<D7);C6;#NV) (runter ziehen bis lfd. Nr. 31)
G5 =HEUTE()
H5 =BEREICH.VERSCHIEBEN(INDIREKT("Knot"&K5);VERGLEICH(G5;INDIREKT(Auswahl);0);1)
I5 =BEREICH.VERSCHIEBEN(INDIREKT("Knot"&K5);VERGLEICH(G5;INDIREKT(Auswahl);0);2)
J5 =(B36-B6)/30
K5 =BEREICH.VERSCHIEBEN(KnotMon;VERGLEICH(MONAT(G5);Monat;0);1)&JAHR(G5)
H7 =JETZT()
H8 =WERT(H7)
Mit den Werten in Spalte C wird der auf- und absteigende Verlauf der Sonne gezeigt (Liniendiagramm). Mit den Werten in Spalte E werden die Sonne und ihr jeweiliger Stand gezeigt (Punktdiagramm). Damit nicht 31 Punkte (Sonnen) auf dem Diagramm zu sehen sind, es soll nur eine sein, bauen wir in die Formel in E6:E36 als Sonst_Wert die Anzeige „#NV“ ein. Das bewirkt, dass nur der aktuelle Sonnenstand im Diagramm erscheint.
Im dritten Arbeitsblatt, es soll „Listen1“ heißen, legen wir schließlich die Liste „Monat“ an. So soll sie aussehen:
Monatsliste
Die Zelle B2 erhält den Namen „KnotMon“, den Bereich B3:B14 soll „Monat“ heißen. Beide Namen werden in den vorhergehend beschriebenen Formeln verwendet.
Schritt 2: Das Liniendiagramm
Zuerst muss das Liniendiagramm erstellt werden. Dazu wird der Bereich C6:C36 markiert und im Register „Einfügen“ der Diagrammtyp „Linie“ gewählt:
Excel 2007: Liniendiagramm einfügen
Excel 2013: Liniendiagramm einfügen
Die Legende wird entfernt.
Dann wird die x-Achse markiert und in den Diagrammtools über die Schaltfläche „Daten auswählen“ die Option „Bearbeiten“ gewählt. Hier ziehen wir den Cursor über die lfd. Nr. 1 bis 31 und bestätigen das Ganze mit OK.
Nun markieren wir die y-Achse, wählen mit der rechten Maustaste „Achse formatieren“ und setzen das Minimum auf Fest 0 sowie das Maximum auf Fest 35 (oder höher, je nach Entscheidung in Schritt 1).
Jetzt wird die Diagrammlinie angeklickt, mit der rechten Maustaste „Diagrammreihen formatieren“ gewählt und als Linienart der gepunktete Strichtyp sowie eine geringe Breite ausgesucht. Als Linienfarbe wählen wir z.B. ein dunkleres Blau. Nun wird die Zeichnungsfläche angeklickt. Mit der rechten Maustaste wählen wir „Zeichnungsfläche formatieren“ und als Füllfarbe ein schönes Himmelsblau.
Über „Einfügen – ClipArt“ (bzw. „Einfügen – Onlinegrafiken“ in Excel 2013) suchen wir schließlich noch eine Schönwetterwolke und schieben sie über die Zeichnungsfläche. Die Wolke muss abschließend noch in eine passende Größe gebracht werden.
Das Liniendiagramm
Das Liniendiagramm ist fertig.
Schritt 3: Das Punktdiagramm
Wir markieren E6:E36 und wählen erstellen ein Punktdiagramm über das Menü „Einfügen – Punkt“:
Excel 2007: Punktdiagramm einfügen
Excel 2013: Punktdiagramm einfügen
Hier wählen wir das Diagramm nur mit Datenpunkten aus. Die Legende, die Achsenbeschriftungen, die Gitternetzlinien, die Füllung und die Linien der Zeichnungsfläche sowie die Füllung und die Linien des Diagrammbereichs werden entfernt. Nun ist nur noch der eine Punkt zu sehen. Wir klicken ihn an, wählen mit der rechten Maustaste „Datenreihen formatieren“ und öffnen den Bereich mit den Markierungsoptionen. Unter „Integriert“ wählen wir den Kreis und gebe ihm die Größe 30. Die Markierungsfüllung soll schließlich (Sonnen-)gelb sein.
Das „nackte“ Punktdiagramm
Das Punktdiagramm ist fertig.
Schritt 4: Die Vereinigung der Diagramme
Zuletzt muss nun noch das Punkt- über das Liniendiagramm geschoben werden. Wir platzieren zuerst das Liniendiagramm nach unseren Vorstellungen. Um nun das Punktdiagramm darüber zu schieben, halten wir die Strg-Taste und klicken auf die Sonne. Es erscheinen an den Ecken des Punktdiagramms vier kleine Kreise. Mit den Pfeiltasten schieben wir nun (ganz langsam) das Punkt- über das Liniendiagramm. Die richtige Position finden wir mit der lfd. Nr., bei der in Spalte E ein Wert steht (x-Achse) und mit dem Wert selbst (y-Achse).
Beide Diagramme übereinander legen
Was könnte noch gemacht werden?
Die auf- und absteigende Linie könnte durch eine Bogenlinie ersetzt werde. Dazu bieten sich eine auf dem Kopf stehende Parabel oder eine Halbellipse an.
Optimal wäre eine Lösung, die Sonnenauf- und –untergangszeiten direkt von der Webseite zu beziehen.
Bei der Konstruktion des Bogens könnte weiter berücksichtigt werden, dass die Sonne so um den 21.06. herum am höchsten und um den 22.12. herum am tiefsten steht.
Das JETZT() in Zelle H7 ändert sich bei Excel nur, wenn die Datei geöffnet, wenn der Cursor in H7 gesetzt und mit Enter abgeschlossen oder im Arbeitsblatt eine Berechnung vorgenommen wird.
Und es wäre natürlich eine gute Sache, wenn die Zeit sich fortlaufend verändern würde. Das aber kann sicherlich nur über VBA gelöst werden.
Vielleicht mag ein Leser sich dieser Aufgabenstellungen annehmen?
Über den Autor
Ich heiße Gerhard Pundt, bin 59 Jahre alt, verheiratet und habe Betriebswirtschaft studiert.
Seit ca. 15 Jahren arbeite ich in einem Unternehmen der Wasserver- und Abwasserentsorgung in Mecklenburg-Vorpommern als Controller.
Zu Excel kam ich 1993, bedingt durch die Arbeit. Mein heutiges Wissen in Excel und VBA habe ich mir autodidaktisch durch Lesen, Probieren und Üben angeeignet.
Heute kann ich sagen: Ich weiß mir zu helfen.
Was mache ich sonst noch gern? Ich lese Romane, inzwischen auch digital mit einem Ebook-Reader.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Guten Morgen Martin,
das oben beschriebene Diagramm ist wirklich klasse und ich habe es auch nachgearbeitet, hat Spaß gemacht. Nun aber meine Frage, ich soll einen Zeitstrahl erstellen um Termine zu verfolgen. Ich habe auch bei Excel online so eines gefunden, habe es mir heruntergeladen und damit gearbeitet komme aber einfach nicht hinter den eigentlichen Aufbau. Habe mir ein Diagramm erstellt, die Achsen ausgeblendet, die Mittelachse durch 0 Werte gerade bekommen und über + – Abweichungen die Beschriftungen übersichtlicher gestaltet, jedoch kann ich die Beschriftungen darüber nicht fixieren und es nimmt nicht immer die Änderungen an. Wollte auch kein Gantt Diagramm erstellen oder einen Projektmanager benutzen. Es sollte doch möglich sein mit einfachen Mittel so einen Zeitstrahl zu erstellen! Aber wie du ja schreibst „..finde ich die Lösung einfach nicht..“. Hast du eine Idee? Gruß Joe
Hallo Joe,
das ist hier leider nicht mit ein paar Worten erklärt. Grundsätzlich kann man ein Balkendiagramm verwenden, bei dem die Achsenbeschriftung die Zeitreihe (also z.B. die Monate oder Jahre) darstellt. Und für die Datenreihe, die die eigentliche Beschriftung enthalten soll, nimmst Du einen anderen Diagrammtyp (z.B. Liniendiagramm). Als Wert wäre dann immer der gleiche Datenwert zu verwenden, so dass die Entfernung zur Zeitreihe gleich bleibt. Aber wie gesagt, das sprengt hier eigentlich die Kommentarfunktion.
Grüße,
Martin
.P.S. Das Problem sitzt meistens vor dem Computer.
Guter Spruch….
Ich sage meist zu meinen Mitarbeitern, wenn sie mit einem PC „Problem“ kommen:
„Der Fehler sitzt zwischen Tastatur und Fußboden“…. und naja zu 90% bin ich ein wahrer Prophet;-)
Hallo Andreas,
ein ehemaliger Chef von mir hat in diesem Zusammenhang vom „45 cm-Problem“ gesprochen – das Problem sitzt 45 cm vor dem Bildschirm 🙂
Schöne Grüße,
Martin
Boah…!
Wieder eine irre, aber tolle Arbeit. Danke!
KOMPLIMENT und RESPEKT !