Analiza podataka kroz Pivot & Analysis Toolpak

Pivot Table je definitivno najmoćniji alat u Excelu. Ako ga često koristite, znate sigurno da je to fantastičan program za obradu podataka.

Takođe Analysis ToolPak predstavlja opcioni dodatak u Excel-u koji pruža dodatne mogućnosti za analizu podataka. Po instalaciji, ovaj add-in nije odmah dostupan kroz TAB tako da je neophodno da se aktivira kako bi mogao da se koristi. Ovaj dodatak je sastavni deo Excel-a a postoje i drugi dodaci koji mogu da se skinu iz Download Centra i oni moraju da se skinu i instaliraju.

Takođe ste sigurno upoznati da dolazi sa alatkom koja je specijalno dizajnirana da analizira velike količine i setove podataka i jednostavno nepostoji bolji i brži način da se dođe do zaključaka i vizualizacije podataka.

Pivot je vrlo svestran, brz, fleksibilan i izuzetno precizan alat koji može da se koristi sa različitim vrstama podataka. Jednom kada naučite kako radi, zauvek će promeniti način na koji gledate na analizu podataka.

Samim tim, učenje i usavršavanje Excel-a i Pivot-a je odličan potez u karijeri, a u nastavku ću za početak objasniti kako da ga kreirate.

Mnogi brinu da je pravljenje same tabele poprilično dug i težak posao ali ako imate struktuiranu bazu podataka, što je veoma bitno, možete kreirati Pivot vrlo brzo.

Na slikama su primeri kako se pravi.

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

kako-da-napravim-pivot-u-excelu-analiza-2

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.

kako-da-napravim-pivot-u-excelu-analiza-3

Selektovanje kategorija za analizu u kreiranoj Pivot tabeli

Prateći uputstva do sada, kreirali ste praznu Pivot tabelu u novom radnom dokumentu na osnovu podataka koje ste označili da želite da analizirate. Da bi ste prikazali podatke, sa desne strane su prikazana polja (ako ne vidite polja, kliknite desni klik bilo gde na tabeli i selektujete opciju Show list). Označite i kliknite na polja kao na slici i dobićete prodaju po proizvodu po danu. Ovo naravno možete po potrebi menjati, a sve zavisi šta vas interesuje tj. koju povratnu informaciju želite da dobijete. Ako deselektujete polje Datum, dobićete ukupnu prodaju za sve datume po proizvodu itd.

Proizvod -> Rows

Datum -> Rows

Prodaja -> Values

kako-da-napravim-pivot-u-excelu-analiza-4

kako da napravim pivot u excelu analiza podataka

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. excel analysis toolpak
  3. Iz menija koji se otvorio, selektujte Analysis ToolPak i kliknite OK. excel analysis toolpak
  4. U tabu DATA (koji se nalazi posle File, Insert itd..) pogledajte skroz desno i videćete sekciju Analysis. Kliknite na Data Analysis.excel analysis toolpak

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

excel analysis toolpak

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).excel toolpak histogram
  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 tekstuexcel analysis toolpak histogram
  3. Selektujte iz liste Histogram i kliknite OK. excel analysis toolpak
  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). excel toolpak histogram
  8. Kliknite OK. excel toolpak histogram
  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:

excel toolpak histogram

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:A20excel toolpak histogram
  2. Kliknite na Insert > Insert Statistic Chart > Histogram excel 2016 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 2016 histogram

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).

excel toolpak analysis pozitivna korelacija

  • 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

excel toolpak analysis negativna korelacija

  • 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. excel analysis toolpak histogram 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. excel toolpak analysis menu
  3. Na primer, selektujte opseg od A1:C6 excel toolpak analysis korelacija
  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 excel toolpak analysis korelacija

 

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.

O autoru

Dušan Milošević

Dodaj komentar

This site uses Akismet to reduce spam. Learn how your comment data is processed.