Jak obliczyć współczynnik korelacji w Excelu (2 proste sposoby)

Excel to potężne narzędzie, które ma niesamowite funkcje i funkcjonalności podczas pracy ze statystykami.

Znalezienie korelacji między dwiema seriami danych jest jednym z najczęstszych obliczeń statystycznych podczas pracy z dużymi zbiorami danych,

Kilka lat temu pracowałem jako analityk finansowy i chociaż nie zajmowaliśmy się zbytnio danymi statystycznymi, znalezienie korelacji było czymś, co wciąż musieliśmy robić dość często.

W tym samouczku pokażę Ci dwa naprawdę proste sposoby obliczyć współczynnik korelacji w Excelu. Istnieje już wbudowana funkcja do tego, a można również skorzystać z pakietu Data Analysis Toolpak.

Więc zacznijmy!

Jaki jest współczynnik korelacji?

Ponieważ nie jest to klasa statystyczna, pozwólcie, że pokrótce wyjaśnię, czym jest współczynnik korelacji, a następnie przejdziemy do sekcji, w której obliczamy współczynnik korelacji w programie Excel.

Współczynnik korelacji to wartość, która mówi, jak blisko są ze sobą powiązane dwie serie danych.

Powszechnie używanym przykładem jest waga i wzrost 10 osób w grupie. Jeśli obliczymy współczynnik korelacji dla danych dotyczących wzrostu i wagi tych osób, otrzymamy wartość od -1 do 1.

Wartość mniejsza od zera wskazuje na ujemną korelację, co oznacza, że ​​jeśli wzrost wzrasta, to waga spada, lub jeśli waga wzrasta, wtedy wzrost maleje.

A wartość większa niż zero wskazuje na dodatnią korelację, co oznacza, że ​​jeśli wzrost wzrasta, to waga wzrasta, a jeśli wzrost maleje, to waga spada.

Im wartość jest bliższa 1, tym silniejsza jest korelacja dodatnia. Tak więc wartość 0,8 wskazywałaby, że dane dotyczące wzrostu i masy ciała są silnie skorelowane.

Uwaga: Istnieją różne typy współczynników korelacji i statystyk, ale w tym samouczku przyjrzymy się najpopularniejszemu, czyli współczynnikowi korelacji Pearsona

Zobaczmy teraz, jak obliczyć ten współczynnik korelacji w programie Excel.

Obliczanie współczynnika korelacji w Excelu

Jak wspomniałem, istnieje kilka sposobów obliczania współczynnika korelacji w programie Excel.

Korzystanie z formuły CORREL

CORREL to funkcja statystyczna wprowadzona w programie Excel 2007.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym chcesz obliczyć współczynnik korelacji między wzrostem a wagą 10 osób.

Poniżej znajduje się formuła, która by to zrobiła:

=WSPÓŁPRACA(B2:B12;C2:C12)

Powyższa funkcja CORREL przyjmuje dwa argumenty - serię z punktami danych wysokości i serię z punktami danych wagi.

I to wszystko!

Jak tylko naciśniesz Enter, Excel wykona wszystkie obliczenia na zapleczu, dając ci jeden numer współczynnika korelacji Pearsona.

W naszym przykładzie wartość ta wynosi nieco ponad 0,5, co wskazuje, że istnieje dość silna korelacja dodatnia.

Ta metoda jest najlepiej stosowana, jeśli masz dwie serie i wszystko, czego potrzebujesz, to współczynnik korelacji.

Ale jeśli masz wiele serii i chcesz poznać współczynnik korelacji wszystkich tych serii, możesz również rozważyć użycie pakietu narzędzi do analizy danych w programie Excel (omówione poniżej)

Korzystanie z pakietu narzędzi do analizy danych

Excel ma pakiet narzędzi do analizy danych, który można wykorzystać do szybkiego obliczania różnych wartości statystycznych (w tym uzyskiwania współczynnika korelacji).

Ale pakiet narzędzi do analizy danych jest domyślnie wyłączony w programie Excel. Tak więc pierwszym krokiem byłoby ponowne włączenie narzędzia do analizy danych, a następnie użycie go do obliczenia współczynnika korelacji Pearsona w programie Excel.

Włączanie pakietu narzędzi do analizy danych

Poniżej znajdują się kroki, aby włączyć pakiet narzędzi do analizy danych w programie Excel:

1. Kliknij kartę Plik
2. Kliknij Opcje
3. W oknie dialogowym Opcje programu Excel, które się otworzy, kliknij opcję Dodatki w panelu paska bocznego
4. W menu rozwijanym Zarządzaj wybierz Dodatki programu Excel
5. Kliknij Idź. Otworzy się okno dialogowe dodatków
6. Sprawdź opcję Analysis Toolpak
7. Kliknij OK

Powyższe kroki spowodowały dodanie nowej grupy na karcie Dane na wstążce programu Excel o nazwie Analiza. W tej grupie będziesz mieć opcję analizy danych

Obliczanie współczynnika korelacji za pomocą pakietu narzędzi do analizy danych

Teraz, gdy masz ponownie dostępne narzędzie do analizy na wstążce, zobaczmy, jak obliczyć za jego pomocą współczynnik korelacji.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz znaleźć korelację między trzema szeregami (wzrost i waga, wzrost i dochód oraz waga i dochód)

Poniżej znajdują się kroki, aby to zrobić:

1. Kliknij kartę Dane
2. W grupie Analiza kliknij opcję Analiza danych
3. W oknie dialogowym Analiza danych, które się otworzy, kliknij „Korelacja”
4. Kliknij OK. Otworzy się okno dialogowe Korelacja
5. Dla zakresu wejściowego wybierz trzy serie - w tym nagłówki
6. W przypadku opcji „Pogrupowane według” upewnij się, że wybrana jest opcja „Kolumny”
7. Wybierz opcję „Etykieta w pierwszym rzędzie”. Dzięki temu dane wynikowe będą miały te same nagłówki i będzie dużo łatwiej zrozumieć wyniki
8. W opcjach danych wyjściowych wybierz miejsce, w którym chcesz uzyskać tabelę wynikową. Przejdę do komórki G1 w tym samym arkuszu. Możesz także wybrać, czy chcesz uzyskać wyniki w nowym arkuszu lub nowym skoroszycie
9. Kliknij OK

Jak tylko to zrobisz, Excel obliczy współczynnik korelacji dla wszystkich serii i poda tabelę, jak pokazano poniżej:

Zauważ, że wynikowa tabela jest statyczna i nie zostanie zaktualizowana w przypadku zmiany któregokolwiek z punktów danych w tabeli. W przypadku jakiejkolwiek zmiany, będziesz musiał powtórzyć powyższe kroki ponownie, aby wygenerować nową tabelę współczynników korelacji.

Są to więc dwie szybkie i łatwe metody obliczania współczynnika korelacji w programie Excel.

Mam nadzieję, że ten samouczek okazał się przydatny!