140. Excel: Zufallsdatum oder Zufallszeit - eine reine Ansichtssache
Zufallsdatum
Für das Berechnen von Zufallsdaten (Zufallsdatum) fand der
Sternenhimmelstuermer eine Menge Ansätze im Internet, die
eins gemeinsam haben (zumindest bei Erstellung des Datums,
nämlich die Formel Zufallsbereich
(untere_Zahl;
obere_Zahl) .
Bis hierher ist es einfach, da die Formel eine typische Excelformel ist,
die man in Verbindung mit Jahr(),
Tag(), Monat() zum
Beispiel zur Berechnung von Zufallsdaten verwenden kann. Da
fängt das Problem aber schon an, da es bekanntlich Monate mit 28, 29,
30 und 31 Tagen gibt und Sie einfach bei normaler Berechnung auf diese
Tage verzichten müssen oder das mit komplizierten Formeln berechnen
müssen...
Das ist natürlich alles Quatsch und viel zu kompliziert, wenn Sie ein
valides Datum per Zufall ermitteln wollen, dass über dem 1.1.1900
liegt, dann ist dieses ohne komplizierte Formel möglich, wenn Sie ein
wenig wie Excel denken:
Eine 1 im Datumsformat ist für
Excel im Ausdruck der 01.01.1900, eine 2
der 02.01.1900 und eine drei.... (Einstellbar in Excel der Versionen ab
2007 in der Registerkarte start - Dropdownmenü für Zellenformatierung
standart, Zahl, Datum kurz oder lang werden hier angezeigt).
Für die Formel des Zufallsbereiches bedeutet z. B. für ein Zufallsdatum
zwischen dem 15.09.2011 bis 15.09.2013 das die Formel z. B. in der
Zelle A1:
=Zufallsbereich (40801;41532)
in der Ansicht von A1 muss das Ergebnis als
Datum kurz angezeigt werden, weil Sie ansonsten ein ziemlich
guter Kopfrechner sein müssten, um das zufällig generierte Datum zu
erkennen.
Die Formel von A1 können Sie mit dem
kleinen Anfasser unten rechts wieder beliebig oft kopieren, so dass Sie
beliebig viele Zufallsdatumsangaben generieren können.
Die Daten, die dabei herauskommen, sind immer valide und es gibt zu
einer Zahl immer ein entsprechendes Datum.
Wie kriege ich die langen Zahlen raus: einfach in Excel die regulären Daten in schreibweise tt.mm.jj eingeben, Ansicht Standart oder Zahl eingeben und das war es.
Also z. B.
A1 15.09.2011 eingeben und die Ansicht auf Zahl einstellen
B1 15.09.2013 eingeben und wieder die Ansicht wechseln
In C1 steht dann die z. B. die Formel: =Zufallsbereich (A1;B1). Dor dann wieder Ansicht als Datum für die Zelle einstellen.
Vergessen Sie das Einstellen der beiden Zellen A1 und A2,
dann gibt es eine Warnmeldung mit dem Vorschlag die Formel zu
korregieren und Sie kommen an den korrekten Zahlenwert - mit dem
kleinen Bonus, dass die Ansicht der beiden Zellen (A1 und B2)
nicht ändert, was durchaus nützlich sein kann, da Sie ja die
Ursprungsdaten vielleicht noch einmal betrachten wollen..., also
einfach O. K. drücken und sich über das Ergebnis freuen..
Anmerkung: Wenn Sie mehrere Zufallszahlen in diesem Bereich mit dem
zuletzt genannten Rechenweg generieren wollen, dann sollten Sie mit
absoluten Bezügen arbeiten:
C1 =ZUFALLSBEREICH($A$2;$B$2)
der Inhalt der Zellen A1 und B1 ist wie im obigen Beispiel...
Das generiert in jeder neuen Zeile mit dem kleinen Anfasser unten rechts von c1 herabgezogen
in jeder Zeile ein neues Zufallsdatum. Die Dollarzeichen vor der Zelle
und der Zahl sagen lediglich, dass die Bezugszellen beim Kopieren
gleich bleiben.
Der Sternenhimmelstuermer staunt nicht schlecht, aber absolute Bezüge sind gerade für Anfänger scheinbar schwer erlernbar...
Ja, das funktioniert natürlich auch mit =ZUFALLSBEREICH(A$2;B$2) , aber dieses sollte keine Abhandlung über die Verwendung von absoluten Bezügen werden...
----
Zufallszeit
Dasselbe gilt analog zur Uhrzeit. Der Sternenhimmelstuermer sah hier
interessante Lösungsansätze, die mathematisch valide sind, aber
den pragmatisch faulen Durchschnittsbürger doch eher überfordern,
da es relativ einfach geht:
Machen wir das ganze ein wenig universaler mit drei Zellen
In A1 steht die untere
Zahl für den Zufallsbereich, z. B. 04:48:00
In B1 steht die obere
Zahl für den Zufallsbereich, z. B.
05:05:18
und in C1 steht wieder
die Formel für den Zufallsbereich oder in valider Formel ausgedrückt:
=ZUFALLSZAHL()*(B1-A1)+A1
und natürlich die Ansicht des Ergebnisses als
Zeit.
Warum nun mit Zufallszahl?
Weil der Zufallsbereich eine natürliche Zahlen verlangt. Bei der
Uhrzeit kommen gebrochene Zahlen bis zur 1 heraus: 0,5 ist 12:00:00 Uhr
mittags noch irgendwelche Fragen? Schauen Sie es sich selbst an, Stunden, Minuten und Sekunden werden in einem Bruchteil von 1
ausgedrückt...Die Formel oben beruht auf der offiziellen Formel zur
Beschränkung von Zufallszahlen - das geht natürlich auch mit
Dezimalzahlen, da Excel sonst in der Tat ein schwacher Rechenkünstler
wäre...
Das Ganze nochmal mit absoluten Bezügen:
=ZUFALLSZAHL()*($B$1-$A$1)+$A$1
----
Zufallszeit mit Zufallsdatum ab dem 01.01.1900
Wenn Sie diese beiden Schritte nachvollzogen haben, dann wissen Sie nun
auch, wie man einen zufälligen Tag mit Uhrzeit kreuzt
- richtig, mit einer einfachen Addition im einfachsten Fall:
Wir addieren also das Ergebnis des Zufallsdatums mit dem Ergebnis der
Zufallszeit: Heraus kommt dann eine Ganze Zahl mit Stellen
nach dem Komma.
Diese Zahl können wir dann wieder in der Ansicht Datum betrachten und siehe da,
Sie haben einen Zufallstag mit Angabe der Zufallszeit.
Einfacher geht es, wenn Sie die Zufallszahlenformel entsprechend
anpassen - viele Wege führen zum Ziel - manche länger und andere
kürzer...
Sie beleidigen die Intelligenz des Autors, wenn Sie dafür noch eine
Erklärung oder ein Beispiel in Formelform brauchen -:). Ansonsten lesen
Sie sich die letzten zwei Kapitel durch und verstehen es...
----
Fazit: Wenn Sie ein wenig wie Excel denken, dann haben Sie nicht nur im
Zufallsdatenbereich, sondern in allen Rechnungen mit Datum ab dem
01.01.1900 kein Problem mehr. Es ging dem Autor nicht primär um
Zufallszahlen, sondern dass der Leser sich ein wenig mit der
Rechenweise im Hintergrund beschäftigt.
An Zufallstagen und Zufallszeit kann man nach Einschätzung des Autors
einen Leser didaktisch gut vermitteln, wie Excel im Hintergrund arbeitet - dieser Tipp hätte auch ganz
anders aufgebaut sein können-:)
Der Autor ist kein Rationalist, sondern Empiriker, was bei den
Alltagsproblemchen mit Excel manchmal von Vorteil ist, wenn man einfach
die Ansicht wechselt und nicht mit komplizierten Formeln weniger
zielführend, aber mathematisch gesehen überlegen hantiert ...