Regresiona analiza u Excel-u. Detaljno ilustrovano uputstvo

Važno je shvatiti da Excel nije samo program za kreiranje baza podataka, već i profesionalni statistički alat. A u statistici postoji mnogo načina za rukovanje numeričkim vrijednostima. Jedna od njih je regresiona analiza. To je usko povezano sa korelacijama. Prije nego što shvatite kako to implementirati u praksi u Excelu, prvo morate razumjeti šta je regresijska analiza i po čemu se razlikuje od korelacione analize.

Pojam "korelacija" poznat je mnogima, čak i onima koji nisu posebno upućeni u statistiku. Već je postao toliko popularan da se često može čuti u svakodnevnom životu. A to znači vrlo jednostavan fenomen – odnos između dvije varijable, kada promjena jedne uzrokuje promjenu u drugoj.

Važno je shvatiti da sama korelacija uspostavlja obrazac, ali ne ukazuje na prirodu ovog obrasca. Odnosno, jedna varijabla može utjecati na drugu, ili možda imaju neku treću varijablu, promjenu u kojoj se mijenjaju obje odjednom. Odnosno, korelacija omogućava uspostavljanje odnosa između pojava, ali ne i uticaja jedne na drugu.

Linearna regresija vam omogućava da jednostavno ustanovite tip ovog odnosa, tako da postaje moguće predvidjeti zavisnu varijablu ovisno o tome kako se nezavisna varijabla mijenja. Sada pogledajmo bliže kako se možete osjećati kao vizionar bez zatvaranja Excel dokumenta.

Kako povezati paket analize u Excelu

Regresiona analiza nije dostupna korisniku odmah nakon otvaranja. Prvo se mora omogućiti. Samo u tom slučaju korisnik će moći koristiti ove alate. Da biste aktivirali funkciju regresijske analize, morate izvršiti sljedeće korake:

1. Otvorite meni „Datoteka“. Da biste to učinili, kliknite na istoimeno dugme lijevo od kartice "Početna".
2. Zatim ćemo otvoriti meni postavki datoteke. Zanima nas kartica „Parametri“.
3. Sada imamo priliku da prilagodimo postavke programa Excel. Zatim idite na meni dodataka, postavite Excel dodatke na listu ispod i kliknite na „Idi“.
4. Nakon toga, pojavljuje se prozor u kojem možete upravljati postojećim dodacima. Zainteresovani smo za opciju “Paket analize”. Morate označiti polje pored njega i kliknuti na “OK”.

Sada imamo novi blok alata na kartici „Podaci“, u kojoj se pojavilo dugme „Analiza podataka“.

A sada ćemo detaljnije opisati koje su vrste regresione analize i kako je implementirati u Excelu.

Koje su vrste regresione analize

Postoji nekoliko vrsta regresije:

1. Parabolic.
2. Power.
3. Logaritamski.
4. Eksponencijalno.
5. Demonstrativno.
6. Hyperbolic.
7. Linearna regresija.

Pogledajmo pobliže posljednju sortu u programu za proračunske tablice Excel.

Linearna regresija u Excelu

Uzmimo mali primjer. Recimo da imamo datoteku sa rasponom podataka koji sadrži podatke o prosječnoj temperaturi zraka izvan prozora u određenom vremenskom periodu i koliko je kupaca bilo istog dana. Da biste to učinili, trebate koristiti regresijsku analizu, nakon što ste točno shvatili kako klimatski uvjeti (tj. temperatura zraka) utječu na to kako se posjećuje ova trgovačka ustanova. Da bismo to uradili, potrebno je da napišemo jednačinu regresije koja izgleda ovako: Y u0d a1 + a1xXNUMX + … + akhk. Hajde da malo raščlanimo ove podatke.

1. Y. Označava varijablu koja zavisi od određenih faktora. To je ono što treba da analiziramo. U našem primjeru, ova varijabla je broj kupaca.
2. x je skup faktora koji mogu promijeniti vrijednost varijable. U ovom slučaju to je temperatura zraka. Ali mogu biti uključene i druge vrijednosti koje se mogu matematički izmjeriti.
3. a je koeficijent regresije. Neophodno je da formula može odrediti ne samo prisustvo samog faktora, već i stepen njegovog uticaja na varijablu Y.
4. k je ukupan broj svih faktora koji su trenutno dostupni.

Da biste izvršili analizu linearne regresije, morate slijediti sljedeće korake:

1. Kliknite na dugme „Analiza podataka“ koje se pojavljuje nakon dodavanja odgovarajućeg dodatka. Nalazi se na kartici „Podaci“ u grupi „Analiza“.
2. Nakon toga, pojavit će se mali dijaloški okvir. Ali uprkos tome, sadrži dovoljnu količinu informacija o tome koji se alati za analizu mogu koristiti. Zanima nas i regresija. Odgovarajuća stavka mora biti odabrana. Kada je označeno, možete kliknuti na dugme OK.
3. Nakon toga, moramo postaviti regresiju. U odgovarajućem dijaloškom okviru obavezno je popuniti X i Y ulazne intervale. Ako ostavite prazno, zadane postavke će se primijeniti na preostale parametre. U polje sa ulaznim intervalom Y upisujemo opseg u kojem se nalaze varijable, za koji pokušavamo da ustanovimo uticaj dostupnih faktora. Jednostavnim riječima, ukupan broj kupaca. Postoji nekoliko načina da unesete adresu: sa tastature ili direktno izaberite mišem. Naravno, prva opcija je u većini slučajeva jednostavnija, ali ako osoba poznaje metodu slijepog ispisa i točno pamti adresu raspona, onda će mu ručno i dalje biti lakše.

Zatim unosimo faktore (tačnije, ćelije koje sadrže informacije o njima) u polje "Input interval X". Kao što je ranije pomenuto, pred nama je zadatak da shvatimo kako temperatura vazduha utiče na broj kupaca. Da biste to učinili, morate zapisati adrese ćelija uključenih u kolonu "Temperatura". Kako uraditi? To je potpuno isto kao i kod prethodnog polja: unesite ručno ili mišem odaberite odgovarajući raspon.

Što se tiče ostalih postavki, one omogućavaju postavljanje oznaka, nivoa pouzdanosti indikatora, konstantne nule, kao i postavljanje niza drugih parametara. Ali u velikoj većini situacija, nema potrebe za prilagođavanjem ovih postavki. Jedina stvar koju treba učiniti je postaviti ispravan radio gumb za izlaznu opciju. Prema standardu, zbrojevi se prikazuju na drugom listu, ali korisnik može, ako želi, ispisati na isti list kao i tabela sa originalnim podacima. Također je moguće ispisati rezultate u posebnu knjigu. Konačno, nakon dovršetka podešavanja, potrebno je da kliknete na dugme "OK", nakon čega će program sam izvršiti sve preostale radnje.

Kako interpretirati rezultate analize

Sa rezultatima regresione analize možete se upoznati na mjestu koje je navedeno u parametrima. To otprilike izgleda ovako.

Najvažnija vrijednost na koju ćemo se fokusirati je R-kvadrat. On bilježi kvalitet korištenog modela. Što je veći, to je veći. Ako je manji od 0,5, tada se ovisnost smatra lošom, ako je veća, onda je već bolja. Što bliže 1 to bolje. Prema tome, maksimalni koeficijent je 1.

Takođe morate obratiti pažnju na još jedan važan pokazatelj. Može se naći u ćeliji koja se nalazi na spoju reda Y-presjeka i kolone „Koeficijenti“. Ovdje možete vidjeti vrijednost Y, koja će pod određenim uvjetima biti nula. Također možete razumjeti koliko naša zavisna varijabla ovisi o faktorima. Da biste to učinili, morate vidjeti koja se figura nalazi na sjecištu stupca Varijabla X1 "i" Koeficijenti ". Što je veći omjer, to bolje.

Vidimo da program Microsoft Excel otvara široke mogućnosti za regresionu analizu. Ali, naravno, potrebna je dodatna obuka za čitanje ovih rezultata. Ali ako već razumete statistiku, biće mnogo lakše. Hajde sada da damo nekoliko jednostavnih primera da bi bilo jasnije kako linearna regresija funkcioniše u praksi.

Primjer regresijske analize #1

A sada je vrijeme da analiziramo praktične slučajeve kako se linearna regresija može koristiti. Recimo da imamo skup podataka o troškovima TV reklamiranja, Internet promocije i koliko je bilo moguće prodati robu u nacionalnoj valuti. Svi ovi podaci su spakovani u tabelu. Naš zadatak je da odredimo koeficijente regresije za nezavisne varijable (to jest, u našem slučaju to su troškovi oglašavanja na TV-u i na Internetu, jer obje vrijednosti utječu na količinu prodane robe). Redoslijed radnji je sljedeći:

1. Otvorite radni list i unesite podatke.
2. Aktivirajte alat za regresiju kao što je gore opisano.
3. U dijaloškom okviru koji se pojavi potrebno je postaviti interval unosa X, Y, postaviti oznake
4. Također, ne zaboravite unijeti izlazni interval. Da biste dovršili ovaj zadatak, također morate navesti opcije kao što su dijagram normalne vjerovatnoće i dijagram ostatka.

Vidimo da za ovaj slučaj ne moramo suštinski odstupiti od gore opisane šeme. Linearna regresija u ovom slučaju vam omogućava da smanjite troškove oglašavanja i povećate povrat na njega. Odnosno, marketinški rečeno, povećati ROMI – omjer povrata ulaganja u marketing.

Primjer regresijske analize #2

Drugi slučaj u kojem se može izvršiti regresijska analiza je potreba da se pronađe maksimalni model za raspodjelu troškova različitih vrsta oglašavanja kako bi se ostvario najveći profit. A takav marketinški zadatak mogao bi se riješiti običnim Excelom, tko bi pomislio?

Pretpostavimo da je maksimalni budžet za oglašavanje koji organizacija može potrošiti 170000 XNUMX rubalja. Ovo ograničenje se ne može obezbijediti gore opisanim standardnim sredstvima. Ovdje trebate koristiti potpuno drugačiji dodatak pod nazivom “Traži rješenje”. Postoji prilika da ga pronađete u istom odjeljku kao i onaj koji smo mi opisali. I slično paketu za analizu, trebamo omogućiti ovaj dodatak u istom meniju.

Šta je alat “Traži rješenje”? Ovo je dodatak koji vam omogućava da pronađete najbolji način za rješavanje određenog problema. Ima dva glavna parametra: ciljnu funkciju i ograničenja. Na taj način korisnik može pronaći optimalan iznos troškova za oglašavanje u određenim uslovima. Ovo je jedna od glavnih prednosti ovog alata.

Kao iu slučaju paketa za analizu, alat za pretraživanje rješenja zahtijeva matematički model. To je ciljna funkcija. U našem slučaju to je kako slijedi: Y = 2102438,6 + 6,4004 X1 – 54,068 X2 > max. Sljedeći izraz se koristi kao ograničenja: X1 + X2 <= 170000, X1>= 0, X2 >=0.

Nakon korištenja alata Search for a Solution, pokazalo se da je, s obzirom na zadane parametre i ograničenja, optimalno trošiti novac na oglašavanje na televiziji, jer se tako može ostvariti maksimalni profit. Kako koristiti ovaj alat u praksi? Da biste to učinili, slijedite ove jednostavne korake.

1. Da biste započeli, kliknite na "Opcije Excel", a zatim idite na kategoriju "Dodaci".
2. Nakon toga, u polju “Upravljanje” pronađite “Excel dodatke” i kliknite na “Idi”.
3. Nakon toga na listi dodataka aktivirajte “Traži rješenje”.

Nakon pritiska na OK, dodatak se uspješno aktivira. Zatim jednostavno kliknite na odgovarajuće dugme na kartici „Podaci“ u istoj grupi kao i paket za analizu i postavite odgovarajuće parametre. Nakon toga, program će sve učiniti sam. Stoga je korištenje regresije u Excelu vrlo jednostavna stvar. Mnogo lakše nego što se čini na prvi pogled, jer većinu radnji izvodi program. Dovoljno je samo voziti u ispravnim postavkama i onda se možete opustiti. I da, još uvijek morate ispravno protumačiti rezultate. Ali to nije problem. Sretno.