Lognormalverteilung in Excel: LOGNORM.INV, Parameter und Einsatz
Kurz gesagt: eine Größe, die meist mäßig ausfällt, aber hin und wieder ein Vielfaches erreicht, etwa die Kosten einer Reparatur. Sie fällt nie unter null.
Eine Lognormalverteilung beschreibt eine Größe, die nicht unter null fallen kann und ein langes oberes Ende hat, etwa Kosten, einen Verlust oder eine Dauer; ihr Logarithmus ist normalverteilt. In Excel zieht =LOGNORM.INV(RAND(), LN(median), s) daraus (RAND heißt im deutschen Excel ZUFALLSZAHL). Die Reparaturkosten eines Schadensfalls mit dem Median 2.000 (USD) und s = 0,8 haben den Mittelwert 2.754, also über dem Median, und ein P90 von 5.576, das 2,8-Fache des Medians.
Was eine Lognormalverteilung ist
Wo eine Normalverteilung kleine, unabhängige Effekte addiert, multipliziert eine Lognormalverteilung sie: Eine Reparatur erweist sich als 20 % aufwendiger, braucht dann ein um 30 % teureres Teil und dauert 10 % länger. Ihre Werte sind immer positiv, die meisten ballen sich am unteren Ende, und ein dünnes Ende reicht weit nach rechts: Einige Schadensfälle kosten ein Vielfaches des typischen.
So lesen Sie das Diagramm: Jeder Balken zählt, wie viele der 10.000 simulierten Werte in diesen Abschnitt fielen; die Linie ist die theoretische Kurve. Je höher der Balken, desto öfter landen Werte dort. Der lange, niedrige Abschnitt rechts sind die seltenen, teuren Schadensfälle.
Zwei Zahlen legen sie fest. Der Median ist der typische Wert: Die Hälfte der Ergebnisse liegt darunter. Die Streuung s bestimmt, wie weit das Ende reicht: Bei etwa 0,25 bleiben die Werte ziemlich nah am Median, bei etwa 1 sind einige um ein Vielfaches größer. (Genau genommen ist s die Standardabweichung des Logarithmus der Werte.) Mittelwert, Gipfel und Perzentile ergeben sich alle aus diesen beiden Zahlen.
Formeln: Mittelwert, Standardabweichung, Gipfel und Perzentile
| Größe | Formel und Beispiel |
|---|---|
| Mittelwert | Median × es²/2 = 2.000 × e0,32 = 2.754 |
| Standardabweichung | Mittelwert × √(es² − 1) = 2.608 |
| Gipfel (Modus) | Median × e−s² = 1.055 |
| P10 und P90 | Median × e∓1,2816 s = 717 und 5.576 |
Die Reihenfolge Gipfel < Median < Mittelwert ist typisch für eine rechtsschiefe Größe. Das Ende zieht den Mittelwert nach oben: Nur 34,5 % der simulierten Schadensfälle kosten mehr als der durchschnittliche. Ein Budget, das auf dem Median oder auf den wahrscheinlichsten Kosten beruht, reicht für den Durchschnitt nicht aus.
Die Lognormalverteilung in Excel
LOGNORM.INV(probability, mean, standard_dev) lässt sich leicht falsch lesen: Mittelwert und Standardabweichung sind die des Logarithmus der Werte, nicht die der Werte. Für den Schadensfall oben sind es LN(2.000) = 7,60 und 0,8. Mittelwert und Standardabweichung der Kosten selbst einzusetzen ist der häufigste Fehler bei dieser Funktion und liefert sinnlose Ergebnisse. Die Arbeitsmappe hält deshalb den Median in einer Zelle und übergibt LN(median); in einer einzigen Zelle lautet die Ziehung =LOGNORM.INV(RAND(),LN(D6),F6).
| Zelle | Formel | Wert |
|---|---|---|
| Zufallszahl u (I6) | =RAND() | |
| Ziehung (J6) | =LOGNORM.INV(I6, | |
| Ziehung in einer Zelle | =LOGNORM.INV(RAND(), | |
| Mittelwert (K6) | =D6*EXP(F6^2/2) | 2.754 |
| P10 (L6) | =LOGNORM.INV(0.1, | 717 |
| P90 (M6) | =LOGNORM.INV(0.9, | 5.576 |
Zahlen prüfen: exakte Werte im Vergleich mit 10.000 Ziehungen aus Excel und aus xellstorm
| Exakt | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mittelwert | 2.754 | 2.797 | 2.754 |
| Standardabweichung | 2.608 | 2.659 | 2.603 |
| P10 | 717 | 734 | 717 |
| P50 (Median) | 2.000 | 2.020 | 2.000 |
| P90 | 5.576 | 5.642 | 5.575 |
Excel zieht mit RAND() völlig zufällig; xellstorm verteilt seine Ziehungen gleichmäßig über den Bereich (Latin-Hypercube-Sampling), daher liegen seine Kennzahlen näher an den exakten Werten.
Kennen Sie stattdessen Mittelwert und Standardabweichung der Werte, rechnen Sie diese zuerst um: s = √(ln(1 + (sd / mean)²)) und median = mean / √(1 + (sd / mean)²). Ein Mittelwert von 3.000 und eine Standardabweichung von 1.500 ergeben s = 0,4724 und einen Median von 2.683. In Excel (SQRT heißt dort WURZEL): =SQRT(LN(1+(sd/mean)^2)) und =mean/SQRT(1+(sd/mean)^2). LOGNORM.DIST(x, LN(median), s, TRUE) (im deutschen Excel LOGNORM.VERT) liefert die Chance auf einen Wert unter x, und das ältere LOGINV arbeitet wie LOGNORM.INV.
Wann eine Lognormalverteilung passt
- Kosten, Verluste und Schadenshöhen, die nicht negativ sein können und gelegentlich ein Vielfaches ihres typischen Werts erreichen.
- Dauern und Lieferzeiten: Eine Arbeit ist selten viel schneller fertig als üblich, kann aber weit länger dauern, wie im Beispiel Lieferzeiten, in dem eine Lognormalverteilung an frühere Lieferungen angepasst wird.
- Preise und Wachstumsfaktoren, die sich um Prozentsätze ändern: Ein Preis, der über viele Zeiträume um zufällige Prozentsätze steigt oder fällt, ist am Ende lognormalverteilt.
Im Vergleich zu den Alternativen: Eine Normalverteilung ist symmetrisch und kann negativ werden; eine PERT-Verteilung oder Dreiecksverteilung hat ein hartes Maximum, das zu einer Expertenspanne passt, aber nicht zu einer Größe, die um ein Vielfaches überschritten werden kann; eine Gamma-Verteilung hat eine ähnliche Form mit dünnerem Ende. Liegen Daten vor, passen Sie Lognormal- und Gamma-Verteilung beide an und vergleichen Sie.
Eine Lognormalverteilung in xellstorm eingeben
- Machen Sie die Zelle zur Eingangsgröße und wählen Sie im Schritt „Verteilungen“ die Verteilung „Lognormal“.
- Die Felder folgen scipy: s ist die Streuung des Logarithmus, scale der Median und loc eine Verschiebung, 0, solange die Werte nicht oberhalb von null beginnen. Für den Schadensfall: s 0,8, scale 2000, loc 0. Die Spalten „Mittelwert“ und „P10 – P90“ bestätigen, was Sie eingegeben haben.
- Denken Sie in Perzentilen, markieren Sie die Eingangsgröße und verwenden Sie in der Seitenleiste „Aus Schätzungen“: Geben Sie ein P10, P50 und P90 ein und wählen Sie „Lognormal“; s und den Median ermittelt die App für Sie. Mit früheren Werten passt „Aus Daten“ eine Lognormalverteilung mit loc 0 an und vergleicht sie mit anderen Formen.
„Aus Arbeitsmappe importieren“ wandelt Zellen mit =LOGNORM.INV(RAND(), mu, sigma) in lognormalverteilte Eingangsgrößen um, ebenso Add-In-Funktionen wie RiskLognorm (Mittelwert und Standardabweichung der Werte), RiskLognorm2 (ihres Logarithmus), VoseLognormal und PsiLogNormal.
Häufige Fragen
Wie erzeuge ich lognormalverteilte Zufallszahlen in Excel?
Verwenden Sie =LOGNORM.INV(RAND(), LN(median), s); der Median ist der typische Wert und s die Standardabweichung seines Logarithmus. Für den Median 2.000 und s = 0,8 ergibt das =LOGNORM.INV(RAND(), LN(2000), 0.8). Jede Neuberechnung zieht einen neuen Wert.
Was sind Mittelwert und Standardabweichung in LOGNORM.INV?
Es sind Mittelwert und Standardabweichung von ln(x), oft μ und σ geschrieben, nicht die der Werte selbst. μ ist der Logarithmus des Medians: LN(2.000) = 7,60. Wenn Sie von Mittelwert und Standardabweichung der Werte ausgehen, rechnen Sie sie um: σ = √(ln(1 + (sd / mean)²)) und μ = ln(mean) − σ² / 2.
Warum liegt der Mittelwert einer Lognormalverteilung über dem Median?
Weil ihr Ende weit nach rechts reicht, die Werte aber nicht unter null fallen können: Der gelegentliche sehr große Wert hebt den Durchschnitt. Der Mittelwert ist der Median × es²/2, die Lücke wächst also mit der Streuung: Bei s = 0,8 ergibt ein Median von 2.000 den Mittelwert 2.754.
Lognormal- oder Normalverteilung: Welche nehme ich?
Nehmen Sie die Normalverteilung für Größen, die symmetrisch um einen Sollwert streuen und weit von null entfernt bleiben. Nehmen Sie die Lognormalverteilung für positive Größen, deren Streuung nach oben größer ist als nach unten: Kosten, Verluste, Dauern, Preise. Ist die Standardabweichung klein im Vergleich zum Mittelwert (unter etwa einem Zehntel davon), sind beide fast identisch.
Verwandte Seiten
- Leitfaden
Welche Wahrscheinlichkeitsverteilung soll ich nehmen? Leitfaden für Monte-Carlo-Modelle
Die passende Wahrscheinlichkeitsverteilung in zwei Fragen finden: PERT-, Dreiecks-, Normal-, Lognormal-, Weibull-, Poisson-, Binomial-, Beta- und weitere Verteilungen, jeweils mit Einsatzzweck und Excel-Formel. - Leitfaden
Gamma-Verteilung in Excel: Wartezeiten, Summen und GAMMA.INV
Die Gamma-Verteilung beschreibt positive, rechtsschiefe Größen wie die Gesamtzeit mehrerer Schritte. Formparameter und Skalenparameter, GAMMA.INV und GAMMA.DIST in Excel sowie Gamma- im Vergleich zur Lognormalverteilung. - Lieferzeiten
Wie wahrscheinlich ist eine pünktliche Lieferung?
Passen Sie Verteilungen an wenige synthetische Lieferdaten an, vergleichen Sie die Wahrscheinlichkeiten pünktlicher Lieferung und erfahren Sie, warum mehr Simulationsiterationen eine kleine Stichprobe nicht ausgleichen. Mit Excel-Arbeitsmappe.
xellstorm simuliert Excel-Modelle nach der Monte-Carlo-Methode direkt im Browser: ohne Add-In, und Ihre Arbeitsmappe verlässt Ihren Rechner nie.