Analiza podataka kroz Pivot & Analysis Toolpak

Priprema podataka za Pivot

U većini slučajeva ako export-ujete podatke iz bilo kog programa sa kojim svakodnevno radite, dobićete strukturu kao na slici koja je već pripremljena za Pivot analizu. Ako to nije slučaj, neophodno je pre svega da pripremiti podatake uz obavezno poštovanje strukture. Svaka kolona mora da bude definisana u zaglavlju sa opisom.

Ne sme da postoje prazna polja u redovima i kolonama koje želite da analizirate.

Selektovanje podataka za Pivot

Kada ste pripremili ili ste već dobili struktuirane podatke, potrebno je da selektujete bilo koju ćeliju u okviru vaše baze tj. tabele izvora podataka i da kliknete

Insert tab – >PivotTable

Selektovanje opsega podataka za analiziranje u Pivot tabeli

Kada ste selektovali PivotTable opciju, otvoriće vam se novi prozor pod nazivom Create PivotTable. Potrebno je da selektujete sve podatke koje želite da analizirate i kliknite OK.

Selektovanje kategorija za analizu u kreiranoj Pivot tabeli

Excel Analysis ToolPak

Korisnik dostavlja podatke ili se pretpostavlja da ih već ima u svom radnom okruženju u Excelu i kroz dodatak se unose parametri za svaku analizu. Alat koristi određene statističke i makro funkcije da uradi kalkulaciju i prikaže rezultate u posebnoj tabeli ili u grafikonu (u zavisnosti od željene analize). Može se koristiti samo u jednom radnom sheet-u istovremeno.

Dodatak sadrži:

• ANOVA: Single Factor
• ANOVA: Two-Factor With Replication
• ANOVA: Two-Factor Without Replication
• Correlation
• Covariance
• Descriptive Statistics
• Exponential Smoothing
• F-Test Two-Sample for Variances
• Fourier Analysis
• Histogram
• Moving Average
• Random Number Generator
• Rank and Percentile
• Regression
• Sampling
• t-Test: Paired Two Sample for Means
• t-Test: Two-Sample Assuming Equal Variances
• t-Test: Two-Sample Assuming Unequal Variances
• z-Test: Two Sample for Means

Da pokrenete Analysis ToolPak , pratite sledeće korake:

1. Kliknite na FILE (u gornjem levom uglu), nakon toga kliknite na OPTIONS
2. Sa leve strane nakon što kliknete na Add-ins, selektujte Analysis ToolPak i kliknite na GO dugme. 
3. Iz menija koji se otvorio, selektujte Analysis ToolPak i kliknite OK. 
4. U tabu DATA (koji se nalazi posle File, Insert itd..) pogledajte skroz desno i videćete sekciju Analysis. Kliknite na Data Analysis.

Otvoriće vam se sledeći prozor. Na primer selektujte ako želite Histogram i kliknite OK da bi ga kreirali.

Ako ste uradili sve kao što je napisano po koracima, onda ste uspešno aktivirali Analysis ToolPak i možete slobodno da ga koristite.

Excel Analysis ToolPak – Histogram

Histogram predstavlja grafički prikaz distribucije podataka koji je prvi predstavio Karl Pearson.

Ovaj primer će vam objasniti kako da kreirate histogram u Excel-u ako imate verziju koja nije 2016 kroz Analysis ToolPak a takođe tu je i objašnjenja ako imate Excel 2016.

1. Pre svega potrebno je da napravite opseg vrednosti (bin), podelite na primer ceo opseg vrednosti iz vašeg seta podataka u serije intervala. U primeru sa slike izabrao sam da to bude kolona D3:D7 (možete ručno menjati i prilagođavati).
2. Kliknite na DATA tab i u sekciji Analysis, kliknite na Data Analysis. Napomena: Ako slučajno ne možete da nađete Data Analysis polje onda je potrebno da aktivirate Analysis ToolPak dodatak koji je sastavni deo svakog Excel-a. Detaljne korake kako se aktivira objasnio sam u ovom tekstu. 
3. Selektujte iz liste Histogram i kliknite OK. 
4. Selektujte podatke koje analizirate, u ovom slučaju oni su u opsegu A2:A20 (bez header-a tj. opisa kolone).
5. Kliknite u polje Bin Range i selektujte opseg serije intervala koje ste prethodno definisali u prvom koraku.
6. Nakon toga kliknite na Output Range i selektujte F3. Ovde definišete gde će se pojaviti nova tabela rezultata.
7. Selektujte Chart Output polje (pojaviće se i grafički prikaz). 
8. Kliknite OK. 
9. Kliknite na legendu sa desne strane i pritisnite DELETE.
10. Preimenujte Bin i stavite tačan naziv u ovom slučaju iz primera tabele to je Broj automobila (ime header-a vašeg seta podataka za analizu).
11. Ako želite da izbrišete razmak između stubova, kliknite desnim klikom na njih, kliknite Format Data Series i promenite Gap width u 0%.
12. Ako želite da dodate ivice, kliknite desnim klikom na ceo grafikon i kliknite na Format Data Series pa onda kliknite na Fill & Line ikonicu i selektujte Border i izaberite boju.

Rezultat:

Histogram u Excelu 2016

Ako imate novu verziju, Excel 2016, onda Histogram možete da kreirate na brži način. Naravno podrazumeva se da imate podatke koje želite da analizirate i serije intervala (bin) kojima želite da izmerite frekvenciju (mada ovo može da se definiše i kasnije kroz podešavanja kod grafikona).

1. Selektujte podatke, u slučaju sa slike to je A2:A20
2. Kliknite na Insert > Insert Statistic Chart > Histogram 
3. Kada vam se pojavi Histogram, kliknite na horizontalnu osu kako bi ga konfigurisali. Kliknite Format Axis pa zatim Axis Options.
4. Iskoristite opcije koje se nalaze u Axis Options i podesite ih.

• By Category – Izaberite da li želite da na horizontalnoj osi kategorije budu teks umesto brojeva
• Automatic – Ovo predstalja default podešavanje kada je u pitanju Histogram. Veličina opsega interval se izračunava korišćenjem Scott’s normal reference rule.
• Bin width – Decimalni broj između svakog opsega intervala Histograma. 
• Number of bins – Broj intervala
• Overflow bin – Kreiranje intervala za sve vrednosti koje su iznad broju u polju koji upišete.
• Underflow bin – Kreiranje intervala za sve vrednosti koje su ispod ili jednake broju u polju koji upišete.

Excel Analysis ToolPak – Koeficijent korelacije

Koeficijent korelacije (vrednost između -1 i +1) nam govori koliko su jako dve varijable povezane međusobno.

U statistici se mogu naći nekoliko različitih korelacija ali ako nije drugačije naglašeno najčešće se misli na Pearson correlation (The product moment correlation coefficient ili PMCC). Mnogi spreadsheet editori kao što su MS Excel, GoogleDocs i OpenOffice mogu da izračunaju korelaciju za vas.

U nastavku ću na praktičnim primerima objasniti dva načina kako možete da izračunate koeficijente, konkretno kroz alat Excel, korišćenjem funkcije CORREL ili preko Analysis Toolpak dodatka gde se takođe može naći koeficijent korelacije između dve varijable.

CORREL funkcija

Selektujte ćeliju gde želite da dobijete koeficijent (na primeru je to B8), upišite sledeću formulu =CORREL u ćeliji i selektujte dva seta podataka (array 1 i array 2) za koji želite da izračunate koeficijent. Prvi set podataka će na primer biti od A2:A6,  a drugi od B2:B6.

• Koeficijent korelacije +1  pokazuje savršenu pozitivnu korelaciju.

Kada se varijabla A poveća, varijabla B se poveća takođe. Kada se varijabla A smanji, varijabla B se smanji takođe (primer na slici ispod).

• Koeficijent korelacije -1 pokazuje savršenu negativnu korelaciju.

Kada se varijabla A poveća, varijabla B se smanji, a kada se A varijabla smanji, varijabla B se poveća

• Kada je koeficijent korelacije blizu 0 onda  to znači da nema korelacije (međusobne povezanosti).

Ako želite da koristite Analysis Toolpak dodatak za Excel kako bi brzo i jednostavno dobili korelacione koeficijente između varijabli, pratite sledeće korake.

1. U tabu Data, u Analysis grupi, kliknite na Data Analysis.  Napomena: Ako kojim slučajem ne možete da nađete Data Analysis onda treba da aktivirate dodatak (objašnjenje i koraci kako da ga aktivirate sam pisao u tekstu koji se nalazi ovde.)
2. Selektujte Correlation i kliknite OK. 
3. Na primer, selektujte opseg od A1:C6 
4. Selektujte Labels in first rows.
5. Selektujte ćeliju gde želite da se pojavi tabela sa korelacijama (na sledećoj slici će to biti D19 na primer).
6. Kliknite OK. 
7. Rezultat 

Varijable A i B imaju pozitivnu korelaciju (0.9). Varijable A i C nisu u korelaciju (-0.20). Varijable B i C takođe nisu u korelaciji (-0.28). Ove tvrdnje mogu da se potvrde tako što će se napraviti grafikon i vizuelno prikazati brojevi. To možete uraditi tako što ćete kliknite u tabu na Insert, i u okviru Chart, selektujte 2D line.