Bivariate Normalverteilung in Excel Berechnen
Schritt-für-Schritt-Anleitung
Um die Verteilungsfunktion einer bivariaten Normalverteilung in Excel zu berechnen, kannst Du folgende Schritte befolgen:
-
VBA-Editor Öffnen: Drücke [ALT][F11], um den VBA-Editor zu öffnen.
-
Modul Einfügen: Gehe zu Einfügen -> Modul, um ein neues Modul zu erstellen.
-
Code Einfügen: Füge den folgenden Code ein, um eine benutzerdefinierte Funktion zur Berechnung der Dichtefunktion zu erstellen:
Function BIVARIATE_NV(x1 As Range, my1 As Range, sigma1 As Range, _
x2 As Range, my2 As Range, sigma2 As Range, _
rho As Range) As Double
Dim A As Double, a0 As Double, S1 As Double, S2 As Double, S12 As Double
Dim pi As Double
pi = 4 * Atn(1)
A = 1 / (2 * pi * sigma1 * sigma2 * Sqr(1 - rho ^ 2))
a0 = -1 / (2 * (1 - rho ^ 2))
S1 = (x1 - my1) / sigma1: S1 = S1 * S1
S2 = (x2 - my2) / sigma2: S2 = S2 * S2
S12 = -2 * rho * (x1 - my1) * (x2 - my2) / (sigma1 * sigma2)
BIVARIATE_NV = A * Exp(a0 * (S1 + S2 + S12))
End Function
-
Zurück zu Excel: Drücke erneut [ALT][F11], um zu Excel zurückzukehren.
-
Variablen Einsetzen: Setze die entsprechenden Zellbezüge für x1, my1, sigma1, x2, my2, sigma2 und rho.
-
Formel Nutzen: Verwende die Funktion in einer Zelle, z.B. =BIVARIATE_NV(A1, B1, C1, A2, B2, C2, D1).
Um die Verteilungsfunktion zu berechnen, kannst Du die numerische Integration durch die Trapezmethode oder andere Methoden in Excel durchführen.
Häufige Fehler und Lösungen
- Fehler in der Funktion: Wenn Du einen Fehler bei der Verwendung der Funktion erhältst, überprüfe die Zellbezüge und stelle sicher, dass alle Werte korrekt eingegeben sind.
- Falsche Ergebnisse: Achte darauf, dass die Varianzen und der Korrelationskoeffizient richtig sind. Ein falscher Wert kann die Ergebnisse der Dichtefunktion erheblich beeinflussen.
- VBA nicht aktiviert: Stelle sicher, dass Makros in Excel aktiviert sind, um die benutzerdefinierte Funktion nutzen zu können.
Alternative Methoden
Wenn Du kein VBA verwenden möchtest, kannst Du die Dichtefunktion direkt mit Excel-Formeln berechnen. Die Formel zur Berechnung der Dichtefunktion einer bivariaten Normalverteilung lautet:
=1/(2*PI()*sigma1*sigma2*WURZEL(1-rho^2))*EXP(-0,5/(1-rho^2)*((($E6-my1)/sigma1)^2-2*rho*(($E6-my1)/sigma1)*((F$5-my2)/sigma2)+((F$5-my2)/sigma2)^2))
Hierbei ersetzt Du sigma1, sigma2, rho, my1, my2 durch die entsprechenden Zellbezüge.
Praktische Beispiele
Ein einfaches Beispiel zur Veranschaulichung der bivariaten Normalverteilung:
-
Gegebene Werte:
- Erwartungswert X (my1) = 0
- Erwartungswert Y (my2) = 0
- Standardabweichung X (sigma1) = 1
- Standardabweichung Y (sigma2) = 1
- Korrelationskoeffizient (rho) = 0.5
-
Berechnung:
Setze die obigen Werte in die Dichtefunktion ein und berechne BIVARIATE_NV(0.5, 0.5, 1, 0.5, 1, 0.5).
Tipps für Profis
- Z-Standardisierung: Verwende die Z-Standardisierung, um die Normalverteilung zu prüfen. Dies kann Dir helfen, die Variablen in die Standardnormalverteilung zu transformieren.
- Visualisierung: Nutze die Diagrammfunktion in Excel, um die Verteilung visuell darzustellen. Ein Oberflächendiagramm eignet sich besonders gut für bivariate Verteilungen.
- Numerische Integration: Bei komplexeren Funktionen zur Berechnung der Verteilungsfunktion ist die Verwendung von numerischen Integrationsmethoden (z.B. Trapezmethode) empfehlenswert.
FAQ: Häufige Fragen
1. Wie kann ich die Dichtefunktion für eine bivariate Normalverteilung in Excel berechnen?
Du kannst die Dichtefunktion entweder durch VBA oder direkt mit Excel-Formeln berechnen. Das Tutorial oben zeigt beide Methoden.
2. Ist die Verwendung von VBA notwendig?
Nein, VBA ist nicht zwingend erforderlich. Du kannst auch Excel-Formeln für die Berechnung verwenden, jedoch kann VBA die Berechnung erleichtern und flexibler gestalten.
3. Wo finde ich weitere Ressourcen zur bivariaten Normalverteilung?
Du kannst Online-Tools und Rechner verwenden, um die Werte der bivariaten Normalverteilung zu überprüfen. Websites wie die von John Hull oder andere Statistikseiten bieten hilfreiche Ressourcen.