Tutorials

Excel-Tutorial: Zufallszahlen mit einer festen Gesamtsumme generieren

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel Zufallszahlen erstellst, die in der Summe genau einen bestimmten Wert (z. B. 100) ergeben.

Es gibt in Excel keine direkte Funktion wie ZUFALLSSUMME(100), aber wir können dies mit zwei bewährten Methoden lösen: der Skalierungs-Methode (schnell und einfach) und dem Solver (für exakte Ganzzahlen).

Methode 1: Die Skalierungs-Methode (Schnell & Flexibel)

Diese Methode eignet sich am besten, wenn Dezimalzahlen erlaubt sind. Die Idee: Wir generieren beliebige Zufallszahlen und rechnen sie proportional so um, dass sie die Zielsumme ergeben.

Schritt-für-Schritt-Anleitung:

  1. Zufallszahlen generieren: Angenommen, du möchtest 5 Zahlen generieren, die zusammen 100 ergeben. Markiere die Zellen A1 bis A5 und gib folgende Formel ein: =ZUFALLSZAHL() (Hinweis: Diese Funktion erzeugt eine Zahl zwischen 0 und 1).

  2. Die Summe berechnen: Schreibe in Zelle A6: =SUMME(A1:A5)

  3. Die Zahlen skalieren: Jetzt rechnen wir die Werte in Spalte B so um, dass sie in der Summe 100 ergeben. Gib in Zelle B1 folgende Formel ein: =A1/$A$6*100 (Erklärung: Wir teilen die einzelne Zahl durch die Gesamtsumme und multiplizieren sie mit dem Zielwert 100).

  4. Formel nach unten ziehen: Ziehe die Formel von B1 bis B5 herunter.

  5. Ergebnis prüfen: Wenn du nun die Summe von B1 bis B5 bildest (=SUMME(B1:B5)), wird das Ergebnis exakt 100 sein.

Methode 2: Der Solver (Für Ganzzahlen ohne Dezimalstellen)

Wenn du ganze Zahlen (Integer) benötigst, die genau 100 ergeben, ist die obige Methode schwierig, da Rundungen die Summe verfälschen (z.B. kommt 99 oder 101 raus). Hier hilft der Excel-Solver.

Schritt-für-Schritt-Anleitung:

  1. Vorbereitung:

    • Schreibe in A1 bis A5 einfach die Zahl 1 (als Startwert).
    • Schreibe in A6 die Summenformel: =SUMME(A1:A5).

  2. Solver aktivieren (falls noch nicht geschehen):

    • Gehe auf Datei > Optionen > Add-Ins.
    • Wähle unten bei "Verwalten" Excel-Add-Ins und klicke auf Los....
    • Setze ein Häkchen bei Solver und bestätige mit OK.

  3. Solver konfigurieren:

    • Gehe zum Reiter Daten und klicke ganz rechts auf Solver.
    • Ziel festlegen: $A$6.
    • Zielwert: Wähle Wert: und gib 100 ein.
    • Durch Ändern von Variablenzellen: Markiere $A$1:$A$5.

  4. Nebenbedingungen hinzufügen: Klicke auf Hinzufügen, um Regeln festzulegen:

    • $A$1:$A$5 = ganzzahlig (Damit keine Kommazahlen entstehen).
    • $A$1:$A$5 >= 1 (Damit keine Null oder negativen Zahlen entstehen).
    • Optional: $A$1:$A$5 <= 50 (Falls keine einzelne Zahl zu groß werden soll).

  5. Lösen:

    • Wähle als Lösungsmethode Evolutionär (funktioniert am besten bei Zufallswerten).
    • Klicke auf Lösen.

Excel wird nun die Zellen A1 bis A5 so anpassen, dass sie in der Summe exakt 100 ergeben.

Methode 3: Der "Quick & Dirty"-Trick (Für einfache Listen)

Wenn du schnell eine Liste brauchst und keine exakte statistische Gleichverteilung benötigst:

  1. Fülle A1 bis A9 mit =ZUFALLSBEREICH(1;20).
  2. Schreibe in A10: =100-SUMME(A1:A9).

Nachteil: Die letzte Zahl (A10) ist nicht wirklich "zufällig" im Vergleich zu den anderen und könnte sogar negativ werden, wenn die Summe der ersten neun Zahlen bereits über 100 liegt. Diese Methode ist nur für kleine Wertebereiche zu empfehlen.

Profi-Tipp: Werte fixieren

Da sich Zufallszahlen in Excel bei jeder Eingabe neu berechnen, solltest du die Ergebnisse am Ende fixieren:

  1. Markiere die fertigen Zahlen.
  2. Drücke Strg + C (Kopieren).
  3. Rechtsklick > Werte einfügen (das Symbol mit der "123").

Nun sind die Zahlen statisch und verändern sich nicht mehr.

0