Korelacijska analiza u Excelu. Primjer izvođenja korelacijske analize

Korelacijska analiza je uobičajena istraživačka metoda koja se koristi za određivanje razine ovisnosti 1. vrijednosti o 2. vrijednosti. Proračunska tablica ima poseban alat koji vam omogućuje provedbu ove vrste istraživanja.

Suština korelacijske analize

Potrebno je utvrditi odnos između dvije različite veličine. Drugim riječima, otkriva u kojem se smjeru (manje/veće) vrijednost mijenja ovisno o promjenama u sekundi.

Svrha korelacijske analize

Ovisnost se uspostavlja kada započne identifikacija koeficijenta korelacije. Ova se metoda razlikuje od regresijske analize jer postoji samo jedan pokazatelj izračunat pomoću korelacije. Interval se mijenja od +1 do -1. Ako je pozitivna, tada povećanje prve vrijednosti doprinosi povećanju druge. Ako je negativan, tada povećanje 2. vrijednosti pridonosi smanjenju 1. vrijednosti. Što je veći koeficijent, jedna vrijednost jače utječe na drugu.

Važno! Kod 0. koeficijenta ne postoji odnos između veličina.

Izračun koeficijenta korelacije

Analizirajmo izračun na nekoliko uzoraka. Na primjer, postoje tablični podaci, gdje su troškovi za promociju oglašavanja i obujam prodaje opisani po mjesecima u zasebnim stupcima. Na temelju tablice saznat ćemo razinu ovisnosti obujma prodaje o novcu utrošenom na promociju oglašavanja.

Metoda 1: Određivanje korelacije putem čarobnjaka za funkcije

CORREL – funkcija koja vam omogućuje implementaciju korelacijske analize. Opći obrazac – CORREL(masiv1;masiv2). Detaljne upute:

1. Potrebno je odabrati ćeliju u kojoj se planira prikazati rezultat izračuna. Kliknite "Umetni funkciju" koji se nalazi lijevo od tekstualnog polja za unos formule.

2. Otvara se čarobnjak za funkcije. Ovdje trebate pronaći CORREL, kliknite na njega, zatim na "OK".

3. Otvara se prozor s argumentima. U retku "Array1" morate unijeti koordinate intervala prve vrijednosti. U ovom primjeru, ovo je stupac Prodajna vrijednost. Samo trebate odabrati sve ćelije koje se nalaze u ovom stupcu. Slično tome, trebate dodati koordinate drugog stupca u redak "Array1". U našem primjeru, ovo je stupac Troškovi oglašavanja.

4. Nakon unosa svih raspona kliknite na gumb "OK".

Koeficijent je prikazan u ćeliji koja je naznačena na početku naših radnji. Dobiveni rezultat je 0,97. Ovaj pokazatelj odražava visoku ovisnost prve vrijednosti o drugoj.

Metoda 2: Izračunajte korelaciju pomoću paketa alata za analizu

Postoji još jedna metoda za određivanje korelacije. Ovdje se koristi jedna od funkcija koje se nalaze u paketu analize. Prije korištenja morate aktivirati alat. Detaljne upute:

1. Idite na odjeljak "Datoteka".

2. Otvorit će se novi prozor u kojem trebate kliknuti na odjeljak “Postavke”.
3. Kliknite na "Dodaci".
4. Nalazimo element "Upravljanje" na dnu. Ovdje trebate odabrati “Excel Add-ins” iz kontekstnog izbornika i kliknuti “OK”.

5. Otvorio se poseban prozor s dodacima. Stavite kvačicu pokraj elementa "Analysis Package". Kliknemo na "OK".
6. Aktivacija je uspjela. Sada idemo na Podaci. Pojavio se blok "Analiza" u kojem morate kliknuti "Analiza podataka".
7. U novom prozoru koji se pojavi odaberite element “Korelacija” i kliknite na “OK”.

8. Na zaslonu se pojavi prozor s postavkama analize. U retku “Interval unosa” potrebno je unijeti raspon apsolutno svih stupaca koji sudjeluju u analizi. U ovom primjeru to su stupci “Prodajna vrijednost” i “Troškovi oglašavanja”. Postavke prikaza izlaza početno su postavljene na Novi radni list, što znači da će rezultati biti prikazani na drugom listu. Po želji, možete promijeniti izlaznu lokaciju rezultata. Nakon što ste napravili sve postavke kliknite na “OK”.

Objavljeni su konačni rezultati. Rezultat je isti kao i kod prve metode – 0,97.

Definicija i izračun koeficijenta višestruke korelacije u MS Excelu

Za određivanje razine ovisnosti nekoliko veličina koristi se više koeficijenata. Ubuduće se rezultati sažimaju u zasebnoj tablici koja se naziva korelacijska matrica.

Detaljan vodič:

1. U odjeljku "Podaci" nalazimo već poznati blok "Analiza" i kliknite na "Analiza podataka".

2. U prozoru koji se pojavi kliknite na element "Korelacija" i kliknite na "U redu".
3. U retku "Interval unosa" upisujemo interval za tri ili više stupaca izvorne tablice. Raspon se može unijeti ručno ili ga jednostavno odabrati LMB-om i automatski će se pojaviti u željenom retku. U "Grupiranje" odaberite odgovarajuću metodu grupiranja. U "Izlaznom parametru" navodi se mjesto na kojem će biti prikazani rezultati korelacije. Kliknemo na "OK".

4. Spreman! Izgrađena je korelacijska matrica.

Koeficijent korelacije para u Excelu

Shvatimo kako ispravno nacrtati koeficijent korelacije para u proračunskoj tablici programa Excel.

Izračun parnog koeficijenta korelacije u Excelu

Na primjer, imate x i y vrijednosti.

X je zavisna varijabla, a y je nezavisna. Potrebno je pronaći smjer i snagu odnosa između ovih pokazatelja. Korak po korak upute:

1. Pronađimo prosječne vrijednosti pomoću funkcije SRCE.

2. Izračunajmo svaki х и xpros, у и Prosjek pomoću operatora «-».

3. Izračunate razlike množimo.

4. Izračunavamo zbroj pokazatelja u ovom stupcu. Brojnik je pronađeni rezultat.

5. Izračunajte nazivnike razlike х и x-prosjek, y и y-medij. Da bismo to učinili, izvršit ćemo kvadriranje.

6. Korištenje funkcije AUTOSUMMA, pronađite indikatore u dobivenim stupcima. Radimo množenje. Korištenje funkcije KORIJEN kvadrirajte rezultat.

7. Kvocijent izračunavamo pomoću vrijednosti nazivnika i brojnika.

8. CORREL je integrirana funkcija koja vam omogućuje da spriječite složene izračune. Idemo na "Čarobnjak za funkcije", odabiremo CORREL i odredimo nizove indikatora х и у. Gradimo graf koji prikazuje dobivene vrijednosti.

Matrica parnih korelacijskih koeficijenata u Excelu

Analizirajmo kako izračunati koeficijente uparenih matrica. Na primjer, postoji matrica od četiri varijable.

Korak po korak upute:

1. Idemo na "Analiza podataka", koja se nalazi u bloku "Analiza" na kartici "Podaci". Odaberite Korelacija s popisa koji se pojavi.
2. Postavili smo sve potrebne postavke. “Interval unosa” – interval sva četiri stupca. “Interval izlaza” – mjesto gdje želimo prikazati ukupne iznose. Kliknemo na gumb "OK".
3. Na odabranom mjestu izgrađena je korelacijska matrica. Svako sjecište retka i stupca je koeficijent korelacije. Broj 1 prikazuje se kada se koordinate podudaraju.

Funkcija CORREL za određivanje odnosa i korelacije u Excelu

CORREL – funkcija koja se koristi za izračunavanje koeficijenta korelacije između 2 polja. Pogledajmo četiri primjera svih mogućnosti ove funkcije.

Primjeri korištenja funkcije CORREL u Excelu

Prvi primjer. Postoji ploča s podacima o prosječnim plaćama zaposlenika tvrtke tijekom jedanaest godina i tečaju dolara. Potrebno je utvrditi odnos između ove dvije veličine. Tablica izgleda ovako:

Algoritam izračuna izgleda ovako:

Prikazani rezultat je blizu 1. Rezultat:

Određivanje koeficijenta korelacije utjecaja djelovanja na rezultat

Drugi primjer. Dva su se ponuditelja obratila dvjema različitim agencijama za pomoć oko petnaestodnevne promocije. Svakodnevno se provodila socijalna anketa koja je određivala stupanj potpore za svakog podnositelja zahtjeva. Svaki ispitanik mogao je izabrati jednog od dva kandidata ili se usprotiviti svima. Potrebno je utvrditi koliko je svaka reklamna promocija utjecala na stupanj podrške prijaviteljima, koja je tvrtka učinkovitija.

Pomoću donjih formula izračunavamo korelacijski koeficijent:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

Rezultati:

Iz dobivenih rezultata vidljivo je da se stupanj podrške 1. prijavitelju povećavao sa svakim danom promocije oglašavanja, pa se koeficijent korelacije približava 1. Kada je oglašavanje pokrenuto, drugi prijavitelj je imao veliki broj povjerenja, a za 5 dana je bio pozitivan trend. Zatim se stupanj povjerenja smanjio i do petnaestog dana pao ispod početnih pokazatelja. Niski rezultati sugeriraju da je promaknuće negativno utjecalo na podršku. Ne zaboravite da drugi popratni čimbenici koji se ne razmatraju u tabličnom obliku također mogu utjecati na pokazatelje.

Analiza popularnosti sadržaja korelacijom pregleda videa i repostova

Treći primjer. Osoba za promoviranje vlastitih videozapisa na YouTube video hostingu koristi društvene mreže za oglašavanje kanala. Primjećuje da postoji neka veza između broja repostova na društvenim mrežama i broja pregleda na kanalu. Je li moguće predvidjeti buduću izvedbu pomoću alata za proračunske tablice? Potrebno je utvrditi razumnost primjene jednadžbe linearne regresije za predviđanje broja pregleda videa ovisno o broju repostova. Tablica s vrijednostima:

Sada je potrebno utvrditi prisutnost odnosa između 2 pokazatelja prema donjoj formuli:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Jaka direktna veza”;”Jaka inverzna veza”);”Slaba ili nikakva veza”)' class='formula'>

Ako je dobiveni koeficijent veći od 0,7, tada je prikladnije koristiti funkciju linearne regresije. U ovom primjeru radimo:

Sada gradimo grafikon:

Primjenjujemo ovu jednadžbu za određivanje broja pregleda pri 200, 500 i 1000 dijeljenja: =9,2937*D4-206,12. Dobivamo sljedeće rezultate:

funkcija PROGNOZA omogućuje vam da odredite broj pregleda u ovom trenutku, ako je bilo, na primjer, dvjesto pedeset repostova. Primjenjujemo: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Vrijednosti nisu povezane”)' class='formula'>. Dobivamo sljedeće rezultate:

Značajke korištenja funkcije CORREL u Excelu

Ova funkcija ima sljedeće značajke:

1. Prazne ćelije se ne uzimaju u obzir.
2. Ćelije koje sadrže Booleovu i tekstualnu informaciju ne uzimaju se u obzir.
3. Dvostruka negacija "-" koristi se za obračun logičkih vrijednosti u obliku brojeva.
4. Broj ćelija u proučavanim nizovima mora odgovarati, inače će se prikazati poruka #N/A.

Procjena statističke značajnosti koeficijenta korelacije

Kod testiranja značajnosti koeficijenta korelacije nulta hipoteza je da indikator ima vrijednost 0, dok alternativa nema. Za provjeru se koristi sljedeća formula:

Zaključak

Analiza korelacije u proračunskoj tablici jednostavan je i automatiziran proces. Da biste to izveli, trebate samo znati gdje se nalaze potrebni alati i kako ih aktivirati kroz postavke programa.