Den Zufall in Excel kreativ nutzen 12

Die ZUFALLSBEREICH-Funktion mal etwas anders eingesetzt.
 

Dass Excel ganz gut rechnen kann, ist kein großes Geheimnis.

Manchmal braucht man jedoch kein exakt vorher bestimmbares Ergebnis, sondern ein eher zufälliges. Zum Beispiel, wenn man für irgendwelche Zwecke nur ein paar Testdaten erzeugen möchte. Auch das ist kein Problem mit den eingebauten Zufallsfunktionen.

Dass man damit aber noch ganz andere Dinge anstellen kann, ist auf den ersten Blick vielleicht nicht ganz so offensichtlich.

Lass dich also überraschen!

ZUFALLSZAHL

Mit der Funktion ZUFALLSZAHL lässt sich eine (quasi-)zufällige Zahl zwischen 0 und 1 erzeugen. Die Funktion ist sehr einfach gestrickt und hat keinerlei Parameter:

=ZUFALLSZAHL()

Zufallszahl zwischen 0 und 1

Zufallszahl zwischen 0 und 1

Multipliziert man das Ganz z.B. mit 100 und kombiniert die Funktion noch mit der RUNDEN-Funktion, erhält man Zufallszahlen zwischen 0 und 100:

Zufallszahlen zwischen 1 und 100

Zufallszahlen zwischen 1 und 100

ZUFALLSBEREICH

Etwas einfacher geht es mit der verwandten Funktion ZUFALLSBEREICH. Hier kann man mit zwei Parametern bestimmen, zwischen welchen beiden Grenzen die Zufallszahl liegen soll:

=ZUFALLSBEREICH(Untergrenze; Obergrenze)

So erzeugt dieses Beispiel eine Zufallszahl zwischen 10 und 100:

Zufallszahl innerhalb eines Bereichs

Zufallszahl innerhalb eines Bereichs

Die beiden Funktionen ZUFALLSZAHL und ZUFALLSBEREICH sind sogenannte volatile Funktionen, das heißt, sie werden automatisch neu berechnet, sobald man irgendwo in seiner Excel-Datei einen Wert eingibt oder eine manuelle Neuberechnung über die F9-Taste auslöst.

Soweit also ganz praktisch, aber wahrscheinlich nicht viel Neues für dich.

Zufällige Texte

Dass man über die Funktion ZUFALLSBEREICH aber auch mehr oder weniger zufällige Buchstaben erzeugen kann, ist dir vielleicht bisher noch nicht bekannt gewesen, oder? Dazu erstelle ich zuerst eine Liste aller benötigten Buchstaben und kombiniere dann die beiden Funktionen INDEX und ZUFALLSBEREICH:

Zufällige Buchstaben

Zufällige Buchstaben

Zur Erinnerung:
Mit der INDEX-Funktion gibt man einen bestimmten Eintrag aus einer definierten Liste wieder. Welcher Eintrag das ist, bestimmt man über den zweiten Parameter. In ihrer kürzesten Form sieht die Funktion so aus:

=INDEX(Liste; Zeile)

Wir geben nun anstelle einer fixen Zeilenangabe die ZUFALLSBEREICH-Funktion an, und zwar für einen Wert zwischen 1 und 26 (da unsere Liste 26 Buchstaben umfasst). Und damit erhalten wir unseren zufälligen Buchstaben.

Dies lässt sich natürlich auch mit anderen Beispielen umsetzen, wie hier mit Bundesländern:

Zufälliges Bundesland

Zufälliges Bundesland

Und jedesmal, wenn du die F9-Taste drückst, wird ein neues Bundesland ausgespuckt. Vielleicht brauchst du ja auch für Demo-Zwecke eine Umsatzübersicht mit zufälligen Vertriebsregionen. Dann verwendest du anstelle der Bundesländer eben die benötigten Regionen.

Wichtig ist nur, dass der obere Wertebereich in der ZUFALLSBEREICH-Funktion immer genau der Anzahl der Elemente in deiner Liste entspricht.

Etwas buntere Effekte lassen sich mit dieser Formel auch erzeugen. Man muss lediglich noch Regeln für eine bedingte Formatierung definieren:

Zufälliges Farbenspiel

Zufälliges Farbenspiel

Zufällige Sätze

Und wem das noch nicht reicht, der kann ganze (mehr oder weniger sinnvolle) Sätze erzeugen:

Zufällige Sätze

Zufällige Sätze

Hier verknüpfen wir einfach drei INDEX-Funktionen, die jeweils auf eine andere Liste zugreifen. Und jedesmal, wenn man die F9-Taste drückt, kann man sich über den neu generierten Unsinn erfreuen 🙂

Wer sagt denn, dass mit Excel immer etwas Sinnvolles herauskommen muss?

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

12 Gedanken zu “Den Zufall in Excel kreativ nutzen

  • Andi

    Für reine Buchstabenreihen würde ich die =Zeichen() Funktion in Kombination mit =Zufallsbereich() empfehlen, das sieht dann so aus und man braucht weder Index noch Hilfszellen/Zellbezüge:
    =ZEICHEN(ZUFALLSBEREICH(65;90))

    PS:
    Zeichen 65 entspricht “A”, Zeichen 90 entspricht “Z”
    Zeichen 97 entspricht “a”, Zeichen 122 entspricht “z”
    PPS:
    Das Umkehrung für =Zeichen() ist =Code()

    • Martin Weiß Autor des Beitrags

      Hallo Andi,

      das mit der ZEICHEN-Funktion ist in der Tat eine sehr schöne und clevere Lösung. Vielen Dank für den Hinweis!

      Schöne Grüße,
      Martin

  • Wolfgang

    Es gibt Formeln/Funktionen, die ich nie benötige…….bis ich sie gesehen habe. 😉 😉 😉

    Danke für Deine Denkanstöße, Martin.

    Gruß

    Wolfgang

    • Martin Weiß Autor des Beitrags

      Hallo Wolfgang,

      genau das sollen meine Artikel sein: Denkanstöße. Danke für das Feedback 🙂

      Schöne Grüße,
      Martin

  • Ditmar

    Hallo Martin,

    ich möchte folgendes machen…
    Es sollen X Namen, z.B. 23, aus einer Namensliste mit Y Namen, z.B. 66, per Zufallsgenerator ausgelost werden. dabei soll aber kein Name doppelt vergeben werden. Wenn allerdings in der Namensliste ein Name zweimal auftaucht, also z.B. 2 mal Müller, sollte es auch möglich sein Müller 2 mal im Ergebnis zu haben. Namen zufällig auswählen klappt schon mal. Problem ist jetzt aber noch die Sache die Auswahl ohne Wiederholungen hinzubekommen. Hast du dafür eventuell eine Lösung?

    • Martin Weiß Autor des Beitrags

      Hallo Ditmar,

      eine mögliche Lösung ist folgende:
      In Zelle A1: =ZUFALLSZAHL()
      In Zelle B1: =RANG(A1;$A$1:$A$66)

      Beide Formeln bis in Zeile 66 kopieren (oder soweit, wie Du Namen in Deiner Namensliste hast). Mit den errechneten Werten in Spalte B kannst Du dann per INDEX-Funktion den Namen aus der Liste auswählen. Es kann keine Nummer mehrfach vorkommen und Namen nur dann, wenn sie mehrfach in der Liste enthalten sind.

      Schöne Grüße,
      Martin

  • Kalida

    Hallo Martin,
    ich würde gerne die Funktion mit Namen nutzen, allerdings den Zufall dahingehend einschränken, dass zwei Namen, die direkt neben der Zelle stehen ausgeschlossen sind. Mit NICHT funktioniert es bisher leider nicht. Kennst du dafür eine Lösung?

    • Martin Weiß Autor des Beitrags

      Hallo Kalida,

      tut mir leid, aber dazu fällt mir spontan auch keine Lösung ein. Vielleicht hat ja ein anderer Leser eine Idee.

      Schöne Grüße,
      Martin

    • Hudi

      Das ist einfach. Mache eine Liste mit allen Namen [NAMEN] von “Aaron bis xxx, die in Frage kommen.
      Weise in der Spalte daneben jedem Namen eine Zahl von 1 – x [Zahl_Namen] zu.
      Mit der Formel Zufallsbereich [den Bereich mit den Zahlen auswählen] –> mit jedem mal neu berechnen kriegst du eine andere Zahl aus deinem gewählten Bereich zurück [Zufallszahl]. Jetzt schaust, welchem Namen die entsprechend generierte Zufallszahl zu geordnet wird und du hast deinen Zufallsnamen. Die Zwei Namen, die die ausschliessen wilst, stehen natürlich nicht in deiner Liste, aus der du Zufällig auswählst.

      Wenn du das weiter automatisieren willst, so prüfe in einer weiteren Spalte hinter den Zugeordnenten Zahlen folgendes:
      Wenn(Zufallszahl=Zahl_Namen;NAMEN;0)
      Nach jedem neuberechnen wird dir nun in dieser neuen Spalte nur der ausgewählte Namen zurückgeliefert. Beim Rest steht Null.

  • Hudi

    Weiss jemand, wie “zufällig” diese Excel Funktion wirklich ist?
    Wie wird sie berechnet? Kann sie prognostiziert werden?
    Ist sie wirklich rein zufällig oder quasi nur “pseudo zufällig”?

    Naja, lustige Frage…..aber ich vermute, dass sie nicht wirklich rein zufällig sein kann.

  • Daniel

    Hallo Martin,
    ich bin schon so lange aus Excel raus, dass ich gerade echt nicht weiterkomme. Ich habe eine Liste mit möglichen Fragen für einen Leistungsnachweis. Jetzt möchte ich, dass einige Fragen in jedem Leistungsnachweis fest drin sind und einige aus der Liste per Zufall dazu kommen. Das ganze auch noch gemischt damit “nie” an Frage 1 immer ein und dieselbe Frage steht.
    Hier nun die Frage, ob da auch etwas möglich ist?
    Vielen Dank für die Hilfe

    • Martin Weiß Autor des Beitrags

      Hallo Daniel,

      das ist nicht ganz trivial. Eine, zugegeben etwas unsaubere Lösung, wäre folgende:

      Der Fragenkatalog aller Fragen steht in Spalte A.
      In Spalte B wird mit der Funktion ZUFALLSBEREICH für jede Frage ein Wert 1 oder 0 bestimmt.
      Bei den Fragen, die immer drankommen sollen, schreibst Du fix den Wert 1 in Spalte B.
      In Spalte E kommt dann die etwas kniffelige Array-Formel (mit Strg+Umschalt+Enter abschließen!):
      {=INDEX($A$1:$A$20;KGRÖSSTE(($B$1:$B$20=1)*(ZEILE($A$1:$A$20));ZÄHLENWENN($B$1:$B$20;1)-ZEILE(A1)+1))}

      Diese Formel ist in ähnlicher Form im Artikel Besser als SVERWEIS beschrieben. Vereinfacht ausgedrückt werden damit alle Fragen aus Spalte A geholt, bei denen in Spalte B der Wert 1 steht:
      Zufallsfragen

      Jedesmal, wenn Du die F9-Taste drückst, wird ein neues Ergebnis berechnet. Nicht perfekt, aber zumindest mal ein Ansatz.

      Schöne Grüße,
      Martin