Excel je moćan alat koji ima nevjerojatne funkcije i funkcionalnosti pri radu sa statistikom.
Pronalaženje korelacije između dva niza podataka jedan je od najčešćih statističkih proračuna pri radu s velikim skupovima podataka,
Prije nekoliko godina radio sam kao financijski analitičar, i iako nismo bili previše uključeni u statističke podatke, pronalaženje korelacije bilo je nešto što smo još uvijek morali često raditi.
U ovom vodiču pokazat ću vam dva zaista jednostavna načina izračunati koeficijent korelacije u Excelu. Za to već postoji ugrađena funkcija, a možete koristiti i paket alata za analizu podataka.
Pa krenimo!
Što je koeficijent korelacije?
Budući da ovo nije klasa statistike, dopustite mi da ukratko objasnim što je koeficijent korelacije, a zatim ćemo prijeći na odjeljak gdje izračunavamo koeficijent korelacije u Excelu.
Koeficijent korelacije vrijednost je koja vam govori koliko su bliske dvije serije podataka.
Uobičajeni primjer je težina i visina 10 ljudi u grupi. Ako za te osobe izračunamo koeficijent korelacije za podatke o visini i težini, dobit ćemo vrijednost između -1 i 1.
Vrijednost manja od nule ukazuje na negativnu korelaciju, što znači da ako se visina povećava tada se težina smanjuje ili ako se težina povećava pri tome tada se visina smanjuje.
A vrijednost veća od nule ukazuje na pozitivnu korelaciju, što znači da ako se visina povećava tada se težina povećava, a ako se visina smanjuje tada se težina smanjuje.
Što je vrijednost bliža 1, pozitivna je korelacija jača. Dakle, vrijednost 0,8 ukazuje na to da su podaci o visini i težini jako povezani.
Napomena: Postoje različite vrste koeficijenata korelacije i statistika, ali u ovom ćemo vodiču pogledati najčešći koji je Pearsonov koeficijent korelacijePogledajmo sada kako izračunati ovaj koeficijent korelacije u Excelu.
Izračunavanje koeficijenta korelacije u Excelu
Kao što sam spomenuo, postoji nekoliko načina na koje možete izračunati koeficijent korelacije u Excelu.
Koristeći formulu CORREL
CORREL je statistička funkcija koja je uvedena u programu Excel 2007.
Pretpostavimo da imate skup podataka kako je prikazano u nastavku gdje želite izračunati koeficijent korelacije između visine i težine 10 ljudi.
Ispod je formula koja bi to učinila:
= CORREL (B2: B12, C2: C12)
Gornja funkcija CORREL uzima dva argumenta - niz s točkama podataka o visini i niz s točkama podataka o težini.
I to je to!
Čim pritisnete enter, Excel izvrši sve izračune u pozadini i daje vam jedan broj Pearsonovog koeficijenta korelacije.
U našem primjeru ta je vrijednost nešto veća od 0,5, što ukazuje na to da postoji prilično jaka pozitivna korelacija.
Ova metoda se najbolje koristi ako imate dvije serije i sve što želite je koeficijent korelacije.
No, ako imate više serija i želite saznati koeficijent korelacije svih ovih serija, tada također možete razmisliti o korištenju paketa alata za analizu podataka u Excelu (dalje u tekstu)
Korištenje paketa alata za analizu podataka
Excel ima paket alata za analizu podataka koji se može koristiti za brzo izračunavanje različitih statističkih vrijednosti (uključujući dobivanje koeficijenta korelacije).
No, paket za analizu podataka prema zadanim je postavkama onemogućen u Excelu. Dakle, prvi bi korak bio omogućiti alat za analizu podataka, a zatim ga upotrijebiti za izračun Pearsonovog koeficijenta korelacije u Excelu.
Omogućavanje paketa alata za analizu podataka
U nastavku su navedeni koraci za omogućavanje paketa alata za analizu podataka u Excelu:
- Kliknite karticu Datoteka
- Pritisnite Opcije
- U dijaloškom okviru Opcije programa Excel koji se otvori kliknite opciju Dodaci u oknu bočne trake
- Na padajućem izborniku Upravljanje odaberite programske dodatke programa Excel
- Kliknite na Idi. Ovo će otvoriti dijaloški okvir dodataka
- Provjerite opciju Alat za analizu
- Pritisnite U redu
Gore navedeni koraci dodali bi novu grupu na kartici Podaci na Excelovoj vrpci pod nazivom Analiza. Unutar ove grupe imali biste opciju Analiza podataka
Izračunavanje koeficijenta korelacije pomoću alata za analizu podataka
Sada kada imate alat za analizu na raspolaganju na vrpci, pogledajmo kako pomoću njega izračunati koeficijent korelacije.
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite saznati povezanost između tri serije (visina i težina, visina i prihod, težina i prihod)
U nastavku su navedeni koraci za to:
- Kliknite karticu Podaci
- U grupi Analiza kliknite opciju Analiza podataka
- U dijaloškom okviru Analiza podataka koji se otvori kliknite "Korelacija"
- Pritisnite U redu. Ovo će otvoriti dijaloški okvir Korelacija
- Za raspon unosa odaberite tri serije - uključujući zaglavlja
- Za "Grupirano po" provjerite je li odabrano "Stupci"
- Odaberite opciju - "Oznaka u prvom redu". To će osigurati da će rezultirajući podaci imati ista zaglavlja i da će biti puno lakše razumjeti rezultate
- U opcijama Output odaberite gdje želite rezultirajuću tablicu. Idem s ćelijom G1 na istom radnom listu. Također možete odabrati da svoje rezultate unesete u novi radni list ili novu radnu knjigu
- Pritisnite U redu
Čim to učinite, Excel će izračunati koeficijent korelacije za sve serije i dati vam tablicu kao što je prikazano u nastavku:
Imajte na umu da je rezultirajuća tablica statična i da se neće ažurirati u slučaju da se promijeni bilo koja točka podataka u vašoj tablici. U slučaju bilo kakve promjene, morat ćete ponovno ponoviti gore navedene korake kako biste generirali novu tablicu koeficijenata korelacije.
Dakle, ovo su dvije brze i jednostavne metode za izračunavanje koeficijenta korelacije u Excelu.
Nadam se da vam je ovaj vodič bio koristan!
