Korelacja w Excelu, macierz oraz mapa cieplna

Korelacja w Excelu

Korelacja jest miarą określającą związek między zmiennymi.

Współczynnik korelacji r zawiera się pomiędzy <-1,1>, gdzie wartości -1 oraz 1 wskazują doskonałą korelacje a 0 jej brak.

Siła korelacji dla wartości bezwzględnej |r| (poziom zależności)

< 0,2 – brak
0,2 – 0,4 – słaba
0,4 – 0,7 – umiarkowana
0,7 – 0,9 – dość silna
> 0,9 – bardzo silna
korelacja w Excelu

Korelacja dodatnia zachodzi wtedy, kiedy zależność między zmiennymi jest wprost proporcjonalna, czyli np. wraz ze wzrostem jednej zmiennej rośnie druga. Korelacja ujemna zachodzi wtedy, kiedy jest odwrotnie proporcjonalna, czyli wraz ze wzrostem jednej druga maleje.

Obliczanie wskaźnika korelacji za pomocą formuły w Excel-u

Formuła, służąca obliczeniu korelacji w Excelu metodą Pearsona to:

=CORREL(array1,array2) – angielska wersja
=WSP.KORELACJI(tablica1;tablica2) – polska wersja

gdzie argumentami funkcji są tablice, które chcemy zbadać pod kątem korelacji.

Należy zachować szczególną ostrożność przy zaznaczaniu tablic, które chcemy ze sobą porównać aby jej zakresy były odpowiadające.

Macierz korelacji w Excel-u

  • Aby korzystać z macierzy korelacji w Excel-u należy dodać Analysis ToolPak

> File > Options > Add-In > Go > Analysis ToolPak

  • Data > Data Analysis > Correlation
  • W okienku Input Range należy zaznaczyć zakres tablic, które chcemy zbadać

Należy zachować szczególna ostrożność przy zaznaczaniu tablic, które chcemy ze sobą porównać. Jeśli są obok siebie to łatwo zaznaczyć taki zakres, jeśli natomiast nie sąsiadują ze sobą należy zaznaczyć pierwszą następnie z pomocą klawisza CTRL zaznaczamy drugą tablicę, ale dokładnie odpowiadającą pierwszej.

      • Jeśli tablice zawierają nagłówki należy odznaczyć Label in the first row
      • Ponadto możemy wskazać w sekcji Output options, gdzie umiejscowić macierz korelacji i akceptujemy OK.

Formatowanie warunkowe wskaźnika korelacji w Excelu.

Aby stworzyć tzw. mapę cieplną w Excelu dla korelacji, można zastosować formatowanie warunkowe.  Jeśli chcemy użyć tylko jednej reguły należy zaznaczyć zakres komórek, które chcemy objąć formatowanie warunkowym, następnie wybrać > Home > Condition Formatting > New Rule

Format Style wybierz 3-color Scale

Minimum wybierz Number wstaw -1 Colour (wybierz np. niebieski)
Midpoint wybierz Number wstaw 0 Colour (wybierz np. czerwony)
Maximum wybierz Number wstaw 1 Colour (wybierz np. niebieski)
Jednakże, aby stworzyć bardziej czytelną mapę cieplną dla korelacji (grafika na górze tego postu) z zastosowaniem koloru żółtego jako pośredniego dla umiarkowanej korelacji, należy stworzyć wiele reguł dla każdego przedziału  z osobna.

Podobne zagadnienia i sztuczki w Excel-u można nauczyć się u mnie na kompleksowym Szkoleniu w Londynie.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *