Transformacja boxa-coxa w excelu (krok po kroku)

Transformacja Boxa-Coxa jest powszechnie stosowaną metodą przekształcania zbioru danych o rozkładzie normalnym w zbiór o bardziej normalnym rozkładzie.

Podstawową ideą jest znalezienie takiej wartości λ, aby przekształcone dane były jak najbardziej zbliżone do rozkładu normalnego, korzystając ze wzoru:

• y(λ) = (y ^λ – 1) / λ jeśli y ≠ 0
• y(λ) = log(y), jeśli y = 0

Poniższy przykład krok po kroku pokazuje, jak wykonać transformację Box-Cox na zestawie danych w programie Excel.

Krok 1: Wprowadź dane

Zacznijmy od wpisania wartości ze zbioru danych:

Krok 2: Posortuj dane

Następnie utwórz kolumnę indeksu i kolumnę posortowanych danych:

Krok 3: Wybierz dowolną wartość Lambdy

Następnie wybierzemy dowolną wartość 1 dla lambda i zastosujemy tymczasową transformację Box-Cox do danych:

Krok 4: Oblicz wyniki Z

Następnie obliczymy wynik Z dla każdej wartości indeksu:

Następnie obliczymy korelację między wartościami przekształconymi metodą Box-Cox a wynikami Z:

Krok 5: Znajdź optymalną wartość lambda

Następnie użyjemy funkcji Goal Seek, aby znaleźć optymalną wartość lambda do wykorzystania w transformacji box-cox.

Aby to zrobić, kliknij kartę Dane na górnej wstążce. Następnie kliknij Analiza typu „co by było, gdyby” w grupie Prognozy .

W menu rozwijanym kliknij opcję Szukaj celu i wpisz następujące wartości:

Po kliknięciu OK funkcja Goal Seek automatycznie znajdzie optymalną wartość lambda, która wynosi -0,5225 .

Krok 6: Wykonaj transformację Boxa-Coxa

Na koniec zastosujemy transformację box-cox do oryginalnych danych, używając wartości lambda wynoszącej -0,5225:

Bonus: Możemy potwierdzić, że przekształcone dane mają rozkład normalny, wykonując test Jarque-Bera w Excelu .

Dodatkowe zasoby

Jak przekształcać dane w programie Excel (log, pierwiastek kwadratowy, pierwiastek sześcienny)
Jak obliczyć wyniki Z w programie Excel