Box-Cox-Transformation in Excel (Schritt für Schritt)

Eine Box-Cox-Transformation ist eine häufig verwendete Methode zum Transformieren eines nicht normalverteilten Datensatzes in einen normalverteilten.

Die Grundidee besteht darin, mit der folgenden Formel einen Wert für λ zu finden, so dass die transformierten Daten so weit wie möglich normalverteilt sind:

• y(λ) = (y ^λ – 1) / λ wenn y ≠ 0
• y(λ) = log(y) falls y = 0

Das folgende Schritt-für-Schritt-Beispiel zeigt, wie Sie eine Box-Cox-Transformation für einen Datensatz in Excel durchführen.

Schritt 1: Geben Sie die Daten ein

Geben wir zunächst die Werte für ein Dataset ein:

Schritt 2: Sortieren Sie die Daten

Erstellen Sie als Nächstes eine Indexspalte und eine Spalte mit sortierten Daten:

(Die Formeln wurden mit einer englischsprachen Excel-Version erstellt. Für die deutschen Formeln siehe z.B. hier)

Schritt 3: Wählen Sie einen beliebigen Wert für Lambda

Als Nächstes wählen wir einen willkürlichen Wert von 1 für Lambda und wenden eine temporäre Box-Cox-Transformation auf die Daten an:

Schritt 4: Berechnen Sie die Z-Scores

Als Nächstes berechnen wir den Z-Score für jeden Wert im Index:

(Die Formeln wurden mit einer englischsprachen Excel-Version erstellt. Für die deutschen Formeln siehe z.B. hier)

Wir berechnen dann die Korrelation zwischen den Box-Cox-transformierten Werten und den Z-Werten:

Schritt 5: Finden Sie den optimalen Lambda-Wert

Als Nächstes verwenden wir Goal Seek, um den optimalen Lambda-Wert für die Box-Cox-Transformation zu finden.

Klicken Sie dazu im oberen Menüband auf die Registerkarte Daten. Klicken Sie dann in der Gruppe Prognose auf Was-wäre-wenn-Analyse.

Wählen Sie im Dropdown - Menü Zielwertsuche und tragen Sie die folgenden Werten ein:

Nachdem Sie auf OK geklickt haben, findet die Zielsuche automatisch den optimalen Lambda-Wert von -0,5225.

Schritt 6: Führen Sie die Box-Cox-Transformation durch

Schließlich wenden wir die Box-Cox-Transformation auf die Originaldaten an, wobei wir einen Lambda-Wert von -0,5225 verwenden:

Bonus: Wir können bestätigen, dass die transformierten Daten normal verteilt sind, indem wir einen Jarque-Bera-Test in Excel durchführen.

Zusätzliche Ressourcen

So transformieren Sie Daten in Excel (Log, Quadratwurzel, Kubikwurzel)
So berechnen Sie Z-Scores in Excel