Excel je močno orodje, ki ima pri delu s statistiko nekaj neverjetnih funkcij in funkcionalnosti.
Iskanje korelacije med dvema nizoma podatkov je eden najpogostejših statističnih izračunov pri delu z velikimi nizi podatkov,
Pred nekaj leti sem delal kot finančni analitik in čeprav nismo bili močno vključeni v statistične podatke, je bilo iskanje korelacije nekaj, kar smo morali še vedno početi.
V tej vadnici vam bom pokazal dva zelo preprosta načina izračunajte korelacijski koeficient v Excelu. Za to že obstaja vgrajena funkcija, uporabite pa lahko tudi orodje za analizo podatkov.
Pa začnimo!
Kaj je korelacijski koeficient?
Ker to ni razred statistike, naj na kratko razložim, kaj je korelacijski koeficient, nato pa preidemo na razdelek, kjer izračunamo korelacijski koeficient v Excelu.
Korelacijski koeficient je vrednost, ki pove, kako tesno sta povezani dve podatkovni vrsti.
Pogosto uporabljen primer je teža in višina 10 ljudi v skupini. Če za te ljudi izračunamo korelacijski koeficient za podatke o višini in teži, bomo dobili vrednost med -1 in 1.
Vrednost, manjša od nič, označuje negativno korelacijo, kar pomeni, da če se višina poveča, se teža zmanjša ali če se teža poveča, potem se višina zmanjša.
Vrednost več kot nič kaže na pozitivno korelacijo, kar pomeni, da če se višina poveča, se teža poveča, in če se višina zmanjša, se teža zmanjša.
Bližje kot je vrednost 1, močnejša je pozitivna korelacija. Torej bi vrednost 0,8 pomenila, da so podatki o višini in teži močno povezani.
Opomba: Obstajajo različne vrste korelacijskih koeficientov in statističnih podatkov, toda v tej vadnici bomo pogledali najpogostejšega, ki je Pearsonov korelacijski koeficientZdaj pa poglejmo, kako izračunati ta korelacijski koeficient v Excelu.
Izračun korelacijskega koeficienta v Excelu
Kot sem omenil, obstaja nekaj načinov za izračun korelacijskega koeficienta v Excelu.
Uporaba formule CORREL
CORREL je statistična funkcija, ki je bila uvedena v Excelu 2007.
Recimo, da imate niz podatkov, kot je prikazano spodaj, kjer želite izračunati korelacijski koeficient med višino in težo 10 ljudi.
Spodaj je formula, ki bi to naredila:
= CORREL (B2: B12, C2: C12)
Zgornja funkcija CORREL ima dva argumenta - niz z točkami podatkov o višini in niz s točkami podatkov o teži.
In to je to!
Takoj, ko pritisnete enter, Excel opravi vse izračune v zaledju in vam dodeli eno samo številko korelacijskega koeficienta Pearson.
V našem primeru je ta vrednost nekoliko večja od 0,5, kar kaže, da obstaja precej močna pozitivna korelacija.
To metodo je najbolje uporabiti, če imate dve seriji in želite le korelacijski koeficient.
Če pa imate več serij in želite izvedeti korelacijski koeficient vseh teh serij, lahko razmislite tudi o uporabi paketa orodij za analizo podatkov v Excelu (obravnavano v nadaljevanju)
Z uporabo orodja za analizo podatkov
Excel ima paket orodij za analizo podatkov, s katerim lahko hitro izračunate različne statistične vrednosti (vključno s pridobivanjem korelacijskega koeficienta).
Toda paket orodij za analizo podatkov je privzeto onemogočen v Excelu. Torej bi bil prvi korak, da omogočimo orodje za analizo podatkov in ga nato uporabimo za izračun korelacijskega koeficienta Pearson v Excelu.
Omogočanje paketa orodij za analizo podatkov
Spodaj so navedeni koraki za omogočanje orodja za analizo podatkov v Excelu:
- Kliknite zavihek Datoteka
- Kliknite Možnosti
- V pogovornem oknu Excel Options, ki se odpre, kliknite možnost Add-ins v podoknu stranske vrstice
- V spustnem meniju Upravljanje izberite dodatke Excel
- Kliknite Pojdi. S tem se odpre pogovorno okno za dodatke
- Preverite možnost Analiza orodja
- Kliknite V redu
Zgornji koraki bi dodali novo skupino na zavihku Podatki v Excelovem traku, imenovano Analiza. V tej skupini bi imeli možnost Analiza podatkov
Izračun korelacijskega koeficienta z uporabo orodja za analizo podatkov
Zdaj, ko imate orodje za analizo na voljo na traku, poglejmo, kako z njim izračunati korelacijski koeficient.
Recimo, da imate niz podatkov, kot je prikazano spodaj, in želite ugotoviti povezavo med tremi vrstami (višina in teža, višina in dohodek ter teža in dohodek)
Spodaj so navedeni koraki za to:
- Kliknite zavihek Podatki
- V skupini Analiza kliknite možnost Analiza podatkov
- V pogovornem oknu Analiza podatkov, ki se odpre, kliknite »Korelacija«
- Kliknite V redu. S tem se odpre pogovorno okno Korelacija
- Za vnosni obseg izberite tri serije - vključno z glavami
- Za »Združeno po« preverite, ali je izbrano »Stolpci«
- Izberite možnost - »Oznaka v prvi vrstici«. To bo zagotovilo, da bodo imeli nastali podatki enake glave in bi bilo veliko lažje razumeti rezultate
- V možnostih Output izberite, kje želite nastalo tabelo. Na istem delovnem listu bom šel s celico G1. Izberete lahko tudi, da bodo rezultati prikazani na novem delovnem listu ali v novem delovnem zvezku
- Kliknite V redu
Takoj, ko to storite, bo Excel izračunal korelacijski koeficient za vse serije in vam dal tabelo, kot je prikazano spodaj:
Upoštevajte, da je nastala tabela statična in se ne bo posodobila, če se spremeni katera koli podatkovna točka v tabeli. V primeru kakršne koli spremembe boste morali ponoviti zgornje korake, da ustvarite novo tabelo korelacijskih koeficientov.
To sta torej dve hitri in enostavni metodi za izračun korelacijskega koeficienta v Excelu.
Upam, da vam je bila ta vadnica koristna!
