Korelaciona analiza u Excel-u. Primjer izvođenja korelacijske analize

Korelaciona analiza je uobičajena istraživačka metoda koja se koristi za određivanje stepena zavisnosti 1. vrednosti od 2. vrednosti. Tabela ima poseban alat koji vam omogućava da implementirate ovu vrstu istraživanja.

Suština korelacione analize

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

Svrha korelacione analize

Zavisnost se uspostavlja kada počne identifikacija koeficijenta korelacije. Ova metoda se razlikuje od regresione analize, jer postoji samo jedan indikator izračunat korištenjem korelacije. Interval se mijenja od +1 do -1. Ako je pozitivan, onda povećanje prve vrijednosti doprinosi povećanju druge. Ako je negativan, onda povećanje 2. vrijednosti doprinosi smanjenju druge. Što je koeficijent veći, to jača jedna vrijednost utiče na drugu.

Važno! Na koeficijentu 0 nema veze između veličina.

Proračun koeficijenta korelacije

Analizirajmo proračun na nekoliko uzoraka. Na primjer, postoje tabelarni podaci, gdje su troškovi za reklamnu promociju i obim prodaje opisani po mjesecima u posebnim kolonama. Na osnovu tabele saznaćemo nivo zavisnosti obima prodaje od novca utrošenog na reklamnu promociju.

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

CORREL – funkcija koja vam omogućava da implementirate analizu korelacije. Opšti oblik – CORREL(masiv1;masiv2). Detaljne upute:

1. Potrebno je odabrati ćeliju u kojoj se planira prikazati rezultat proračuna. Kliknite na "Insert Function" koji se nalazi lijevo od polja za tekst da biste unijeli formulu.

2. Otvara se čarobnjak za funkcije. Ovdje morate pronaći CORREL, kliknite na njega, a zatim na “OK”.

3. Otvara se prozor sa argumentima. U redak “Niz1” morate unijeti koordinate intervala 1. vrijednosti. U ovom primjeru, ovo je stupac Vrijednost prodaje. Samo trebate odabrati sve ćelije koje se nalaze u ovoj koloni. Slično, potrebno je dodati koordinate druge kolone u red “Niz2”. U našem primjeru, ovo je kolona Troškovi oglašavanja.

4. Nakon što unesete sve opsege, kliknite na dugme “OK”.

Koeficijent je prikazan u ćeliji koja je bila naznačena na početku naših akcija. Dobijeni rezultat je 0,97. Ovaj indikator odražava visoku zavisnost prve vrijednosti od druge.

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 koja se nalazi u paketu za analizu. Prije upotrebe, potrebno je aktivirati alat. Detaljna uputstva:

1. Idite na odjeljak "Datoteka".

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

5. Otvoren je poseban prozor dodataka. Postavite kvačicu pored elementa „Paket analize“. Kliknemo na “OK”.
6. Aktivacija je uspjela. Sada idemo na Data. Pojavio se blok "Analiza" u kojem trebate kliknuti na "Analiza podataka".
7. U novom prozoru koji se pojavi odaberite element “Korelacija” i kliknite na “OK”.

8. Na ekranu se pojavio prozor postavki analize. U red „Input interval“ potrebno je uneti opseg apsolutno svih kolona koje učestvuju u analizi. U ovom primjeru, to su kolone “Vrijednost prodaje” i “Troškovi oglašavanja”. Postavke prikaza izlaza su inicijalno postavljene na Novi radni list, što znači da će rezultati biti prikazani na drugom listu. Opciono, možete promijeniti izlaznu lokaciju rezultata. Nakon što izvršite sva podešavanja, kliknite na “OK”.

Izašli su konačni rezultati. Rezultat je isti kao kod prve metode – 0,97.

Definisanje i izračunavanje koeficijenta višestruke korelacije u MS Excel-u

Za identifikaciju nivoa zavisnosti nekoliko veličina koriste se višestruki koeficijenti. U budućnosti, rezultati se sumiraju u posebnu tabelu, koja se naziva korelacionom matricom.

Detaljan vodič:

1. U odjeljku “Podaci” nalazimo već poznati blok “Analiza” i kliknemo “Analiza podataka”.

2. U prozoru koji se pojavi kliknite na element “Korelacija” i kliknite na “OK”.
3. U redu „Input interval“ ubacujemo interval za tri ili više kolona izvorne tabele. Opseg se može unijeti ručno ili jednostavno odabrati pomoću LMB-a i automatski će se pojaviti u željenom redu. U “Grupiranju” izaberite odgovarajući način grupisanja. U “Output Parameter” specificira lokaciju na kojoj će biti prikazani rezultati korelacije. Kliknemo na “OK”.

4. Spremni! Izgrađena je korelaciona matrica.

Koeficijent korelacije para u Excelu

Hajde da shvatimo kako ispravno nacrtati koeficijent korelacije para u Excel tabeli.

Izračunavanje koeficijenta korelacije parova u Excelu

Na primjer, imate x i y vrijednosti.

X je zavisna varijabla, a y je nezavisna. Potrebno je pronaći pravac i jačinu odnosa između ovih indikatora. Korak po korak uputstvo:

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

2. Izračunajmo svaki х и xavg, у и pros koristeći operator «-».

3. Izračunate razlike množimo.

4. Izračunavamo zbir indikatora u ovoj koloni. Brojač je pronađeni rezultat.

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

6. Korištenje funkcije AUTOSUMMA, pronađite indikatore u rezultirajućim kolonama. Radimo množenje. Korištenje funkcije ROOT kvadrirajte rezultat.

7. Izračunavamo količnik koristeći vrijednosti nazivnika i brojnika.

8. CORREL je integrirana funkcija koja vam omogućava da spriječite složene proračune. Idemo u "Čarobnjak za funkcije", odaberite CORREL i odredite nizove indikatora х и у. Gradimo graf koji prikazuje dobijene vrijednosti.

Matrica parnih koeficijenata korelacije u Excelu

Hajde da analiziramo 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“. Izaberite Korelacija sa liste koja se pojavi.
2. Postavljamo sva potrebna podešavanja. “Input interval” – interval sve četiri kolone. “Izlazni interval” – mjesto gdje želimo prikazati ukupne vrijednosti. Kliknemo na dugme “OK”.
3. Na odabranom mjestu je izgrađena korelaciona matrica. Svaki presek reda i kolone je koeficijent korelacije. Broj 1 se prikazuje kada se koordinate podudaraju.

CORREL funkcija za određivanje odnosa i korelacije u Excelu

CORREL – funkcija koja se koristi za izračunavanje koeficijenta korelacije između 2 niza. Pogledajmo četiri primjera svih sposobnosti ove funkcije.

Primjeri korištenja funkcije CORREL u Excelu

Prvi primjer. Postoji tabla sa podacima o prosečnim platama zaposlenih u kompaniji tokom jedanaest godina i kursu dolara. Potrebno je utvrditi odnos između ove dvije veličine. Tabela izgleda ovako:

Algoritam proračuna izgleda ovako:

Prikazani rezultat je blizu 1. Rezultat:

Određivanje koeficijenta korelacije uticaja akcija na rezultat

Drugi primjer. Dva ponuđača obratila su se dvije različite agencije za pomoć oko petnaestodnevne promocije. Svakodnevno je vršena socijalna anketa koja je određivala stepen podrške za svakog kandidata. Svaki ispitanik je mogao izabrati jednog od dva kandidata ili se suprotstaviti svima. Potrebno je utvrditi koliko je svaka reklamna promocija uticala na stepen podrške aplikantima, koja kompanija je efikasnija.

Koristeći formule u nastavku, izračunavamo koeficijent korelacije:

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

Rezultati:

Iz dobijenih rezultata postaje jasno da se stepen podrške prvom aplikantu povećavao sa svakim danom reklamne promocije, pa se koeficijent korelacije približava 1. Kada je oglašavanje pokrenuto, drugi aplikant je imao veliki broj povjerenja, a za 1 dana je bio pozitivan trend. Zatim se stepen povjerenja smanjio i do petnaestog dana pao ispod početnih pokazatelja. Niski rezultati ukazuju na to da je promocija negativno uticala na podršku. Ne zaboravite da drugi prateći faktori koji nisu uzeti u obzir u tabelarnom obliku takođe mogu uticati na indikatore.

Analiza popularnosti sadržaja prema korelaciji pregleda videa i repostova

Treći primjer. Osoba za promociju vlastitih videa na YouTube video hostingu koristi društvene mreže za reklamiranje kanala. Primjećuje da postoji određena veza između broja repostova na društvenim mrežama i broja pregleda na kanalu. Da li je moguće predvidjeti buduće performanse pomoću alata za proračunske tablice? Neophodno je identificirati razumnost primjene jednačine linearne regresije za predviđanje broja pregleda videa u zavisnosti od broja repostova. Tabela sa vrijednostima:

Sada je potrebno utvrditi postojanje odnosa između 2 indikatora prema donjoj formuli:

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

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

Sada gradimo grafikon:

Primjenjujemo ovu jednačinu da odredimo broj pregleda na 200, 500 i 1000 dijeljenja: =9,2937*D4-206,12. Dobijamo sljedeće rezultate:

funkcija PROGNOZA omogućava 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'>. Dobijamo sljedeće rezultate:

Značajke korištenja CORREL funkcije u Excelu

Ova funkcija ima sljedeće karakteristike:

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

Procjena statističke značajnosti koeficijenta korelacije

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

zaključak

Korelaciona analiza u tabeli je jednostavan i automatizovan proces. Da biste to izvršili, potrebno je samo znati gdje se nalaze potrebni alati i kako ih aktivirati kroz postavke programa.