Excel - Zufällige Listen erstellen

Hallo zusammen,

heute geht es darum, für Beispiele, Tests etc. zufällig generierte Listen zu erstellen. Hier müssen wir unterscheiden zwischen Zahlenwerten - auch Datumswerte zählen dazu - und Namen, Adressen etc.

Zahlenwerte

Zur Ermittlung zufälliger Zahlenwerte zieht ihr am besten die Tabellenfunktion ZUFALLSBEREICH heran. Die bekommt einen Zahlenbereich übergeben und liefert euch eine zufällige Ganzzahl in diesem Bereich zurück. Damit kann man ne ganze Menge anstellen.

Achtung: Bei allem, was ihr im Tabellenblatt macht, wird die Zufallszahl neu ermittelt. Das kann je nach Datenmenge ne Weile dauern und unter Umständen auch etwas nervig sein. Deshalb: Immer die Werte festschreiben, wenn sie ermittelt sind. Das macht ihr z.B., indem Ihr die Spalte markiert, STRG+C drückt, dann  in die gleiche Spalte rechtsklickt und bei Einfügeoptionen "Werte einfügen" auswählt.

Kontostand

Ein Kontostand kann im Plus oder im Minus sein und ist selten ganzzahlig. Da müssen wir also etwas tricksen. Wir erzeugen erstmal eine Ganzzahl - sagen wir zwischen -1.500 € und 50.000 €. Damit haben wir die Vorkommastellen.

Formel: =ZUFALLSBEREICH(-1500;50000)

Jetzt kommen noch die Cent dazu. dazu brauchen wir die Anzahl der Cent - das können zwischen 0 und 99 sein - und teilen die durch 100 und addieren das ganz einfach dazu.

Formel: =ZUFALLSBEREICH(-1500;50000)+ZUFALLSBEREICH(0;99)/100

Die Formel nach unten fortschreiben, Spalte noch als Währung formatieren und schon haben wir eine beliebige Anzahl an Kontoständen.

Kontonummer

Bei meiner Bank hat eine Kontonummer immer 10 Stellen. Das lässt dich mit folgender Konstellation bewerkstelligen.

Formel: =ZUFALLSBEREICH(1000000000;9999999999)

Geburtsdatum

Bei einem Geburtsdatum wird es ein ganz klein wenig kniffelig. Ein Datum ist für Excel auch nur eine Zahl. Es beginnt standardmäßig am 01.01.1900 zu zählen - theoretisch sogar schon am 0.1.1900, aber dieses Datum ist unsinnig. Der 1.1.1900 um 0:00 Uhr morgens entspricht 1. Danach wird für jeden Tag 1 hochgezählt. Eine Stunde entspricht danach 1/24 von einem Tag, also 0,416667. Damit können wir also Datumswerte auch per Zufallsbereich ermitteln. Als erstes Argument übergeben wir das früheste und als zweites das späteste Datum als Zahlenwert. Den Zahlenwert kriegen wir entweder vorhern raus oder ermitteln ihn mithilfe der Formel DATWERT. Und so sieht das Konstrukt dann aus, wenn ich ein Geburtsdatum zwischen dem 01.01.1925 und dem 31.12.2010 haben will:

Formel: =ZUFALLSBEREICH(DATWERT("01.01.1925");DATWERT("31.12.2010"))

Die Spalte solltet ihr anschließend noch als Datum formatieren.

Personendaten

Zufällige Personendaten können wir nicht berechnen lassen. Bei einigen wenigen Datensätzen könnt ihr noch eurer Kreativität freien Lauf lassen, wenn es über eine Handvoll hinausgeht, wird das aber schon ganz schön mühsam.

Dafür gibt es aber sogenannte Fakename-Generatoren. Unter der Seite www.fakenamegenerator.com könnt ihr komplette Identitäten zufällig erstellen lassen. Dabei könnt ihr auch Nationalität und Geschlecht vorgeben. Bei Nationalität sind auch ganz witzige Varianten - z.B. Hobbits oder Ninjas - möglich.

Hier bekommt ihr nicht nur Namen und Adressen, es gibt auch Blutgruppe, Beruf (englisch) oder Gewicht der Person.

Wer auf VBA zurückgreifen will, der findet in unseren Xing-Forum eine Funktion, die Name, Adresse und Mail auch im Tabellenblatt zur Verfügung stellt.

Dafür müsst ihr die Funktion am besten in eure Personal.xls kopieren, damit sie auch immer verfügbar ist.

Anleitung:

• geht in die Entwicklungsumgebung (Tastenkombination ALT + F11)
• Dort solltet ihr auf der linken Seite im Projekt-Explorer das "VBAProject (PERSONAL.XLSB)" sehen (in älteren Versionen Personal.xls)

• habt ihr diesen Eintrag NICHT, dann müsst ihr die Arbeitsmappe erst erzeugen lassen. Das geht ganz einfach, indem ihr den Makrorekorder startet (Register Entwicklertools oben links) und im erscheinenden Fenster bei "Makro speichern in" die persönliche Arbeitsmappe auswählt. Dann mit OK bestätigen. Der Makro-Aufzeichnen-Button hat sein Aussehen verändert und heißt jetzt "Aufzeichnung beenden". Klickt ihn an. Nun hab ihr die persönliche Arbeitsmappe mit einem leeren Makro drin. Jetzt bitte mit ALT + F11 in die Entwicklungsumgebung zurück.

• Markiert den Eintrag mit der Personal.xlsb im Projekt-Explorer und klickt in der Menüleiste auf "Einfügen" und dann auf "Modul"
• Das neue Codemodul wird in der Mitte des Bildschirms geöffnet - ein großes leeres Fenster. Da kopiert ihr den Code aus dem Xing-Forum rein (das ist alles von "Function GetFake() As String" bis "End Function")
• Folgendermaßen sieht das jetzt aus:

• Die Entwicklungsumgebung könnt ihr schließen (Tastenkombination ALT + Q, oder über das Kreuzchen oben rechts oder das Menü "Datei")
• In der Tabelle zurück gebt ihr nun in der ersten Zelle folgendes ein: =PERSONAL.XLSB!GetFake()
• Wenn ihr mit einer älteren Version von Excel arbeitet, lasst das B nach XLS weg.
• Drückt die Enter-Taste. Es wird ein wenig dauern, bis die Funktion berechnet wurde. Das liegt daran, dass im Hintergrund der Internet-Explorer aufgerufen wird und der ist ja bekanntlich leider nicht der schnellste.
• Wenn ihr einen Fehlerwert zurückbekommt (#WERT!), dann war der InternetExplorer zu langsam. Da hilft eine Neuberechnung - Doppelklick auf die Zelle und dann Enter-Taste. Manchmal muss man das auch 3 oder 4 mal machen, aber irgendwann schafft es IE dann doch.
• Ihr könnt diese Formel wie jede andere Auch automatisch nach unten fortschreiben lassen - aber Achtung: das dauert.
• Wenn alle Fehlerwerte neu berechnet sind, schreibt die Werte bitte fest (Kopieren und Werte einfügen). Vorher funktioniert das Trennen im nächsten Schritt nicht.
• Nun habt ihr folgendes Bild:

• Die Formel bringt alle benötigten Daten hintereinander mit Semikolon getrennt. Die lassen wir jetzt von Excel noch auseinander nehmen. Dazu markiert ihr die komplette Spalte (bei mir Spalte D) und klickt im Register "Daten" auf "Text in Spalten"

• Der Textkonvertierungs-Assistent öffnet sich. Im ersten Schritt stellt ihr die Option "Getrennt" ein und klickt auf "Weiter". Bei Schritt 2 muss als Trennzeichen das Semikolon eingestellt werden. Dann seht ihr in der Vorschau schon, wie der Text auf Spalten aufgeteilt wird. Achtung: bitte ausschließlich das Semikolon als Trennzeichen anhaken. Schritt 3 könnt ihr überspringen, indem ihr sofort auf "Fertig stellen" klickt.

• Nun habt ihr eine schöne Liste mit Namen, Adresse und Mailadresse. Überschriften drüber, die Postleitzahl ggf. noch als solche formatieren und ihr habt folgendes Bild:

Ich hoffe, der kleine Workshop heute hat euch Spaß gemacht. Bei Fragen: nur keine Scheu, ich helfe gern weiter :)