Korelacja w Excelu - WSP.KORELACJI, macierz korelacji, istotność i Spearman

Korelacja w Excelu to przede wszystkim funkcja WSP.KORELACJI(tablica1;tablica2), która zwraca współczynnik korelacji Pearsona r. Funkcja PEARSON daje identyczny wynik. Excel nie podaje jednak istotności korelacji, więc wartość p trzeba dopisać dwiema formułami: statystyką t = r · √(n - 2) / √(1 - r²) i funkcją ROZKŁ.T.DS. Macierz korelacji dla wielu zmiennych naraz zrobisz narzędziem Korelacja z dodatku Analiza danych, a korelację Spearmana, zamieniając wyniki na rangi funkcją POZYCJA.ŚR. Poniżej wszystkie kroki na jednym przykładzie.

Ten wpis pokazuje technikę liczenia w Excelu. Kiedy wybrać współczynnik Pearsona, a kiedy Spearmana, wyjaśniamy w tekście korelacja Pearsona czy Spearmana, a czym korelacja różni się od regresji, w tekście korelacja a regresja.

Współczynnik korelacji w Excelu: funkcja WSP.KORELACJI

=WSP.KORELACJI(tablica1;tablica2)

Oba zakresy muszą mieć tyle samo komórek i być ułożone tak, żeby w tym samym wierszu stały wyniki tej samej osoby. Jeśli zakresy mają różną długość, Excel zwraca błąd. Błąd pojawi się także wtedy, gdy któraś zmienna ma zerowe odchylenie standardowe, na przykład wszyscy zaznaczyli tę samą odpowiedź. Funkcja pomija komórki z tekstem i puste komórki, ale liczy zera. Przy brakach danych bezpieczniej najpierw usunąć niepełne wiersze, żeby wiedzieć, na ilu osobach liczysz korelację, i żeby zgadzało się to z opisem próby.

Kwadrat współczynnika, czyli r², policzysz funkcją R.KWADRAT albo po prostu podnosząc wynik do kwadratu. Mówi on, jaka część zmienności jednej zmiennej wiąże się liniowo ze zmiennością drugiej.

Korelacja a kowariancja w Excelu

W menu funkcji i w dodatku Analiza danych obok korelacji znajdziesz kowariancję (KOWARIANCJA.PRÓBKI i narzędzie Kowariancja). Kowariancja też mówi o kierunku związku, ale jej wartość zależy od jednostek: dla godzin nauki i wyniku w procentach wyjdzie inna liczba niż dla minut nauki i punktów. Korelacja to kowariancja podzielona przez iloczyn odchyleń standardowych obu zmiennych, dlatego zawsze mieści się między -1 a 1 i da się ją porównywać między badaniami. W pracy dyplomowej raportujesz korelację, a kowariancja jest potrzebna najwyżej jako etap pośredni obliczeń. Jeśli w wyniku z dodatku widzisz liczby większe niż 1, prawie na pewno wybrałeś narzędzie Kowariancja zamiast Korelacja.

Korelacja w Excelu krok po kroku

1. Ułóż obie zmienne w sąsiednich kolumnach, jedna osoba w jednym wierszu, bez pustych wierszy w środku.
2. Zrób wykres punktowy: zaznacz obie kolumny i wybierz z karty Wstawianie wykres punktowy (XY). Sprawdź, czy punkty układają się wzdłuż linii, czy w łuk, i czy nie ma pojedynczych punktów daleko od reszty.
3. W wolnej komórce wpisz =WSP.KORELACJI(A2:A16;B2:B16).
4. Obok policz liczebność =ILE.LICZB(A2:A16), statystykę t i wartość p (formuły w sekcji o istotności).
5. Jeśli wykres pokazał zależność nieliniową albo wartości odstające, policz dodatkowo korelację Spearmana na rangach.

Wykres nie jest dodatkiem dla ozdoby. Jedna nietypowa osoba potrafi zmienić r z 0,2 na 0,6, a sam współczynnik tego nie zdradzi. Jak rozpoznać takie obserwacje, piszemy w tekście wartości odstające: jak je wykryć. Na wykresie punktowym możesz też kliknąć serię prawym przyciskiem, dodać linię trendu i włączyć wyświetlanie wartości R-kwadrat.

Przykład: korelacja w Excelu na danych z ankiety

Przykładowe dane od 15 studentów: liczba godzin nauki tygodniowo (kolumna A), wynik egzaminu w procentach (kolumna B) i samoocena stresu na skali 1-10 (kolumna C).

Formuła =WSP.KORELACJI(A2:A16;B2:B16) zwraca r = 0,69, a r² = 0,48. Oznacza to dodatnią, silną zależność: im więcej godzin nauki, tym wyższy wynik egzaminu, a liniowy związek z godzinami nauki wiąże się z 48% zmienności wyników. Średnia liczba godzin wynosi 5,67 (SD = 3,09), a średni wynik egzaminu 65,8% (SD = 9,78); te statystyki policzysz według instrukcji statystyki opisowe w Excelu.

Istotność korelacji w Excelu: jak policzyć p

Ani funkcja WSP.KORELACJI, ani narzędzie z dodatku nie podają wartości p. Istotność współczynnika Pearsona sprawdza się testem t z n - 2 stopniami swobody. Jeśli r jest w komórce E2, a liczebność w E3, wpisz:

MODUŁ.LICZBY jest potrzebny, bo ROZKŁ.T.DS nie przyjmuje liczb ujemnych, a przy korelacji ujemnej t też jest ujemne. Wynik p = 0,004 jest mniejszy niż 0,05, więc korelacja jest istotna statystycznie. Ten sam wynik dałby SPSS w kolumnie istotności dwustronnej.

Drugi sposób to wartość krytyczna. Dla n = 15 i poziomu 0,05 (test dwustronny) współczynnik jest istotny, gdy |r| przekracza 0,514. Wartość krytyczną policzysz formułą =ROZKŁ.T.ODWR.DS(0,05;13)/PIERWIASTEK(13+ROZKŁ.T.ODWR.DS(0,05;13)^2). Przy małych próbach nawet wyraźne korelacje bywają nieistotne, a przy kilkuset osobach istotne staje się już r = 0,15. Dlatego obok p zawsze interpretuj samą wartość r.

Przedział ufności dla współczynnika korelacji

Recenzenci coraz częściej oczekują przedziału ufności obok r. Excel policzy go przez transformację Fishera. Jeśli r jest w E2, a n w E3, dolna granica 95% to =ROZKŁAD.FISHER.ODW(ROZKŁAD.FISHER(E2)-1,96/PIERWIASTEK(E3-3)), a górna to ta sama formuła z plusem zamiast minusa. W przykładzie przedział wynosi od 0,28 do 0,89. Jest szeroki, bo 15 osób to mała próba: dane są zgodne zarówno z umiarkowanym, jak i z bardzo silnym związkiem w populacji. Taki przedział uczciwiej pokazuje niepewność wyniku niż samo p = 0,004.

Macierz korelacji w Excelu (Analiza danych)

Gdy masz kilka zmiennych, nie musisz wpisywać formuły dla każdej pary. Narzędzie Korelacja z dodatku Analiza danych liczy wszystkie współczynniki Pearsona naraz.

1. Włącz dodatek Analysis ToolPak w Plik > Opcje > Dodatki, jeśli jeszcze go nie masz.
2. Kliknij Dane > Analiza danych > Korelacja.
3. W zakresie wejściowym zaznacz wszystkie kolumny z nagłówkami (tu A1:C16), wybierz grupowanie według kolumn i zaznacz etykiety w pierwszym wierszu.
4. Wskaż miejsce wyniku i kliknij OK.

Wynik to tabela, w której wypełniona jest tylko część pod przekątną, a na przekątnej stoją jedynki. Dla przykładu:

Macierz z dodatku nie zawiera wartości p ani liczebności, a jej wartości są statyczne. Wartości p dopisujesz formułami z poprzedniej sekcji: dla pary godziny nauki i stres t = -2,47, p = 0,028, a dla pary wynik egzaminu i stres t = -6,85, p < 0,001. Narzędzie liczy też korelacje wyłącznie Pearsona; Spearmana w dodatku nie ma.

Korelacja Spearmana w Excelu przez rangi

Współczynnik Spearmana to współczynnik Pearsona policzony na rangach zamiast na surowych wynikach. Excel nie ma dla niego osobnej funkcji, ale wystarczą dwie dodatkowe kolumny.

1. W kolumnie D wpisz rangi godzin nauki: =POZYCJA.ŚR(A2;$A$2:$A$16;1) i skopiuj w dół. Ostatni argument 1 oznacza rangowanie rosnąco, od najmniejszej wartości.
2. W kolumnie E zrób to samo dla wyniku egzaminu: =POZYCJA.ŚR(B2;$B$2:$B$16;1).
3. Policz =WSP.KORELACJI(D2:D16;E2:E16). To jest rho Spearmana.
4. Istotność policz tymi samymi formułami co dla Pearsona, wstawiając rho zamiast r.

Funkcja POZYCJA.ŚR przy remisach nadaje rangę średnią. W przykładzie dwie osoby uczyły się po 5 godzin i zajmują pozycje 7 i 8, więc obie dostają rangę 7,5. Funkcja POZYCJA.NAJW dałaby obu rangę 7, co zniekształca współczynnik, dlatego do Spearmana jej nie używaj. Kierunek rangowania nie ma znaczenia, byle był taki sam w obu kolumnach.

Dla przykładu rho = 0,66, t = 3,15, p = 0,008. Wynik jest zgodny z SPSS, który przy współczynniku Spearmana liczy istotność tym samym przybliżeniem rozkładem t. Rho jest tu nieco niższe niż r Pearsona (0,69). Przy danych bez wyraźnych odstępstw od liniowości oba współczynniki zwykle są zbliżone, a duża różnica między nimi to sygnał, żeby jeszcze raz obejrzeć wykres.

Co Excel liczy przy korelacji, a czego nie

Dla jednej czy dwóch par zmiennych Excel w zupełności wystarczy. Gdy zmiennych porządkowych z wieloma remisami jest dużo, rozważ współczynnik tau Kendalla, który opisujemy w tekście korelacja tau Kendalla; w Excelu nie ma go wcale. Przy danych z ankiety pamiętaj też, że pojedyncze pytanie na skali Likerta to zmienna porządkowa z kilkoma wartościami. Zwykle najpierw sumujesz lub uśredniasz pozycje w wynik skali, a dopiero potem liczysz korelację; jak to zrobić, pokazujemy w tekście skale w kwestionariuszu: jak liczyć wskaźnik.

Korelacja w Excelu: interpretacja wyniku

• Znak mówi o kierunku. Dodatni: obie zmienne rosną razem. Ujemny: gdy jedna rośnie, druga maleje, jak stres i wynik egzaminu (r = -0,88).
• Wartość bezwzględna mówi o sile. Umowne progi, na przykład 0,1, 0,3 i 0,5 dla efektu małego, średniego i dużego, różnią się między dziedzinami, więc podaj, na kim się opierasz.
• p mówi, czy przy takiej liczebności wynik da się odróżnić od braku związku, a nie, czy związek jest silny.
• Korelacja nie oznacza przyczynowości. Z tych danych nie wynika, że nauka obniża stres; obie zmienne mogą zależeć od trzeciej, na przykład od organizacji czasu.
• r² czytaj ostrożnie. Wartość 0,48 nie znaczy, że godziny nauki „wyjaśniają” połowę wyniku egzaminu w sensie przyczynowym, tylko że tyle zmienności obu zmiennych się pokrywa w tej próbie.

Jak zapisać korelację w pracy (APA 7)

Raport korelacji to współczynnik, stopnie swobody (n - 2) w nawiasie i p. W tekście nie piszesz „wg Excela”; program wymieniasz raz w opisie metod.

Liczba godzin nauki korelowała dodatnio z wynikiem egzaminu, r(13) = 0,69; p = 0,004. Związek był silny. Analiza współczynnikiem Spearmana dała zbliżony wynik, r_s(13) = 0,66; p = 0,008.

Przy wielu zmiennych zamiast serii zdań przygotuj tabelę korelacji z gwiazdkami lub wartościami p, a w notce opisz oznaczenia i liczebność. Ogólne zasady zapisu wyników znajdziesz w poradniku jak zaraportować wynik testu w APA 7.

Typowe błędy przy korelacji w Excelu

• Raportowanie samego r bez wartości p i liczebności, bo Excel ich nie pokazał.
• Brak wykresu punktowego, przez co umyka zależność w kształcie łuku albo jedna obserwacja odstająca.
• Rangowanie funkcją POZYCJA.NAJW zamiast POZYCJA.ŚR przy liczeniu Spearmana.
• Posortowanie jednej kolumny bez drugiej, co rozrywa pary wyników i niszczy korelację.
• Kody braków danych (np. 99) pozostawione w zakresie.
• Korelacja Pearsona dla zmiennych porządkowych z kilkoma kategoriami, gdzie lepszy jest Spearman.
• Wniosek przyczynowy z korelacji.

Gdy zmiennych jest kilkanaście, ręczne dopisywanie p do każdej pary szybko staje się źródłem pomyłek. W takiej sytuacji wygodniej przejść do programu statystycznego albo zamówić policzenie korelacji Pearsona lub Spearmana w kreatorze wyceny; raport zawiera wyniki z wartościami p w tabelach w standardzie APA.

Najczęstsze pytania

Jak policzyć korelację w Excelu?

Wpisz =WSP.KORELACJI(zakres1;zakres2), gdzie oba zakresy mają tyle samo komórek, a w tym samym wierszu są wyniki tej samej osoby. Funkcja zwraca współczynnik korelacji Pearsona od -1 do 1. Dla wielu zmiennych użyj narzędzia Korelacja z dodatku Analiza danych.

Jak sprawdzić istotność korelacji w Excelu?

Policz statystykę t = r·√(n - 2)/√(1 - r²), a potem p formułą =ROZKŁ.T.DS(MODUŁ.LICZBY(t);n-2). Jeśli p jest mniejsze niż 0,05, korelacja jest istotna statystycznie. Wynik jest zgodny z istotnością dwustronną w SPSS.

Czy w Excelu można policzyć korelację Spearmana?

Tak, choć nie ma osobnej funkcji. Zamień obie zmienne na rangi funkcją POZYCJA.ŚR, która przy remisach nadaje rangi średnie, i policz WSP.KORELACJI na kolumnach z rangami. Istotność liczysz tak samo jak dla Pearsona.

Czym różni się WSP.KORELACJI od PEARSON w Excelu?

Niczym w wyniku. Obie funkcje zwracają ten sam współczynnik korelacji liniowej Pearsona. Funkcja PEARSON istnieje głównie ze względu na zgodność ze starszymi arkuszami.

Dlaczego macierz korelacji w Excelu jest wypełniona tylko do połowy?

Macierz korelacji jest symetryczna, więc korelacja zmiennej A z B jest taka sama jak B z A. Narzędzie z dodatku Analiza danych pokazuje tylko wartości pod przekątną, a na przekątnej wpisuje jedynki, bo każda zmienna koreluje sama ze sobą idealnie.