Kako napraviti stožernu tabelu u Excelu. Kako raditi sa podacima

MS Excel Pivot Table je moćan alat za analizu podataka. Ovaj alat vam omogućava da izvučete informacije iz velikog niza podataka, u bilo kojem kontekstu, sa samo nekoliko klikova mišem. U ovom članku pokušaću jednostavnim i razumljivim jezikom, korak po korak, objasniti šta i kako učiniti kako bi vam ovaj izuzetno zgodan MS Excel alat bio na raspolaganju.

Zahtjevi za izvorne podatke.

Dakle, osnova svake pivot tabele je ispravno konstruisan niz podataka – „ispravna tabela“. Na slici ispod možete vidjeti primjer ispravno formiranog niza podataka:

Pogledajmo šta je, zapravo, “ispravnost” ove tabele? Ispravnost je da:

• Svaka kolona sadrži samo istu vrstu podataka, kolonu A samo Datumi, u koloni IN samo Dokumenti, u koloni WITH samo kupci, novac u novcu, dobavljači u dobavljačima, kategorije proizvoda u kategorijama i tako dalje... U takvoj tabeli možemo vrlo lako koristiti Filter;
• Kolone sa istim tipom podataka se ne ponavljaju;
• U tabeli nema ukupnih redova, samo “čisti podaci”;
• U tekstualnim podacima u tabeli nema praznih ćelija; svaki red sadrži ime klijenta, proizvoda, dobavljača, menadžera itd...

Inače, u nizu uopšte nije važno u kom se redosledu nalaze kolone i po kom polju su podaci sortirani. Ovo ni na koji način ne utiče na konstrukciju Pivot tabele.

A evo i primjera “pogrešne tablice” iz koje, čak i ako krenete, Pivot Table neće biti izgrađena, a čak i ako bude izgrađena, biće potpuno nemoguće raditi s podacima... Naš “ omiljeni“ računovodstveni sistemi obično nas „obrade“ ovakvim tabelama, koje nam ih daju u obliku izveštaja koji su potpuno neprikladni za dalju analizu:

Evo još jednog primjera "Neispravnog niza":

Ima tu gomila "nepravilnosti":

• Prvo, kolone “Dobavljač” i “Kategorija” imaju prazne ćelije, tako da ne možemo koristiti filter;
• Drugo, u skoro svakoj koloni postoje redovi „Ukupno...“, potpuno su nepotrebni za pravljenje Pivot tabele, štaviše, samo će vam smetati;
• Treće, kolona "Grand Total" takođe nije potrebna;
• Četvrto, isti tip podataka, odnosno “Novac”, nalazi se u čak tri kolone: ​​“Jan”, “Feb” i “Mar”, što će značajno otežati konstrukciju Pivot tabele, što znači da ćemo morati uradi nešto sa tim...

Ali ovo je sve za poseban razgovor, ako želite naučiti kako brzo pretvoriti takve "krive tablice" u "ispravne nizove", pročitajte članke: "Kako brzo napraviti pivot tablicu iz 1C ili SAP izvještaja?" i "Kako brzo pretvoriti tabelu u niz za pivot tabele?"

Zapravo, izgradnja pivot tabele:

Uzmite "ispravan niz", postavite kursor u bilo koju ćeliju niza, odaberite karticu "Insert" u glavnom meniju, u lijevom uglu, u odjeljku "Tabele", kliknite na dugme "Pivot Table":

U dijaloškom okviru "Kreiraj zaokretnu tablicu" koji se otvori, kliknite na "OK":

MS Excel će kreirati novi list na kojem će naznačiti mjesto na koje će se umetnuti Pivot Table, a sa desne strane će prikazati prozor za podešavanje polja Pivot Table u kojem ćete vidjeti sve nazive kolona vaš niz:

Počnimo sa postavljanjem zaokretne tablice. Kakav kroj želimo? Kategorije proizvoda po menadžeru - molim. Pomerite miša preko polja „Kategorija“, kliknite na njega levim tasterom miša i prevucite ga u polje „REDOVI“. Polje “Manager” se prevlači u “COLUMNS”, a “Iznos” se prevlači u “VRIJEDNOSTI”:

Pivot tabele su jedan od najefikasnijih alata u MS Excel-u. Uz njihovu pomoć možete pretvoriti milion redova podataka u sažeti izvještaj u sekundi. Osim brzog sumiranja rezultata, zaokretne tabele vam omogućavaju da doslovno promijenite način na koji analizirate u hodu prevlačenjem polja iz jednog područja izvještaja u drugo.

Pivot tabele su takođe jedan od najpodcenjenijih Excel alata. Većina korisnika nije svjesna mogućnosti koje su u njihovim rukama. Zamislimo da pivot tablice još nisu izmišljene. Radite za kompaniju koja svoje proizvode prodaje raznim kupcima. Radi jednostavnosti, u asortimanu su samo 4 artikla. Proizvode redovno kupuje nekoliko desetina klijenata koji se nalaze u različitim regionima. Svaka transakcija se unosi u bazu podataka i predstavlja poseban red.

Vaš direktor vas nalaže da napravite kratak izvještaj o prodaji sve robe po regijama (regionima). Problem se može riješiti na sljedeći način.

Prvo, napravimo izgled tablice, odnosno zaglavlje koje se sastoji od jedinstvenih vrijednosti za proizvode i regije. Napravimo kopiju stupca proizvoda i uklonimo duplikate. Zatim, koristeći specijalni insert, transponiramo stupac u red. Isto radimo i sa regionima, samo bez transponovanja. Dobit ćemo zaglavlje izvještaja.

Ova tablica mora biti popunjena, tj. sumirati prihod za relevantne proizvode i regije. Ovo je lako učiniti koristeći SUMIFS funkciju. Dodajmo i rezultate. Dobit ćete zbirni izvještaj o prodaji po regijama i proizvodima.

Izvršili ste zadatak i pokažite izvještaj direktoru. Gledajući u sto, generiše nekoliko sjajnih ideja odjednom.

— Da li je moguće napraviti izvještaj ne o prihodima, već o dobiti?

— Da li je moguće prikazati proizvode po redovima i regione po kolonama?

— Da li je moguće kreirati takve tabele za svakog menadžera posebno?

Čak i ako ste iskusan korisnik Excela, kreiranje novih izvještaja će oduzeti dosta vremena. Ovo da ne spominjemo moguće greške. Međutim, ako znate zaokretne tabele, onda je odgovor: da, treba mi 5 minuta, možda i manje.

Evo kako se to radi. Otvorite izvorne podatke. Zaokretna tabela se može napraviti koristeći regularni opseg, ali bi bilo ispravnije da se transformiše. Ovo će odmah riješiti problem automatskog hvatanja novih podataka. Odaberite bilo koju ćeliju i idite na karticu Insert. Na lijevoj strani trake nalaze se dva dugmeta: i Preporučene zaokretne tabele.

Ako ne znate kako organizirati postojeće podatke, možete koristiti naredbu Preporučene zaokretne tabele. Excel će prikazati sličice mogućih izgleda na osnovu vaših podataka.

Kliknite na odgovarajuću opciju i stožerna tabela je spremna. Ostaje samo da se podsetite, jer... Malo je vjerovatno da će se standardna priprema potpuno poklopiti s vašim željama. Ako trebate da napravite zaokretnu tabelu od nule, ili imate staru verziju programa, kliknite na dugme. Pojavit će se prozor u kojem trebate odrediti izvorni raspon (ako aktivirate bilo koju ćeliju Excel tablice, ona će se sama odrediti) i lokaciju buduće pivot tablice (novi list će biti odabran prema zadanim postavkama).

Vrlo često ovdje nema potrebe ništa mijenjati. Nakon što kliknete na OK, kreirat će se novi Excel list s praznim izgledom zaokretne tablice.

Pre nego što pređemo na podešavanja, upoznajmo se sa interfejsom i osnovnim konceptima. Izgled tabele je prilagodljiv na panelu Polja zaokretne tabele, koji se nalazi na desnoj strani lista.

Na vrhu panela nalazi se lista svih dostupnih polja, odnosno kolona u izvornim podacima. Ako trebate dodati novo polje u izgled, možete označiti okvir pored njega - Excel će sam odrediti gdje ovo polje treba postaviti. Međutim, to nije uvijek ispravno, pa je bolje prevući miša na željeno mjesto na rasporedu. Polja se također mogu izbrisati tako što ćete ih poništiti ili povući nazad.

Stožerna tabela se sastoji od 4 oblasti, koje se nalaze na dnu panela: vrednosti, redovi, kolone, filteri. Pogledajmo pobliže njihovu svrhu.

Raspon vrijednosti– ovo je središnji dio pivot tablice sa vrijednostima koje se dobijaju agregiranjem izvornih podataka odabranom metodom.
U većini slučajeva, do agregacije dolazi do Sumiranje. Ako su svi podaci u odabranom polju u numeričkom formatu, Excel će dodijeliti zadanu sumu. Ako izvorni podaci sadrže barem jedan tekst ili praznu ćeliju, tada će se umjesto zbroja izračunati Količinaćelije. U našem primjeru, svaka ćelija je zbir svih odgovarajućih proizvoda u odgovarajućoj regiji.

Možete koristiti druge metode izračunavanja u ćelijama zaokretne tabele. Postoji oko 20 tipova (prosjek, minimalna vrijednost, proporcija, itd.). Postoji nekoliko načina za promjenu metode izračuna. Najjednostavnije je da kliknete desnim tasterom miša na bilo koju ćeliju željenog polja u samoj pivot tabeli i izaberete drugu metodu agregacije.

Područje linije– nazive linija koje se nalaze u krajnjoj lijevoj koloni. Ovo su sve jedinstvene vrijednosti odabranog polja (kolona). U području reda može biti nekoliko polja, tada tabela postaje višeslojna. Ovdje se obično postavljaju kvalitativne varijable kao što su nazivi proizvoda, mjeseci, regije itd.

Područje kolona– slično redovima, prikazuje jedinstvene vrijednosti odabranog polja, samo u kolonama. Naslovi kolona su također obično kvalitativni pokazatelj. Na primjer, godine i mjeseci, grupe proizvoda.

Područje filtera– koristi se, kao što ime kaže, za filtriranje. Na primjer, sam izvještaj prikazuje proizvode po regijama. Morate ograničiti svoju pivot tablicu na određenu industriju, period ili menadžera. Zatim se polje filtera postavlja u područje filtera i željena vrijednost se bira sa padajuće liste.

Dodavanjem i uklanjanjem polja u određena područja, možete prilagoditi bilo koji dio vaših podataka koji želite za nekoliko sekundi.

Pogledajmo kako ovo funkcionira na djelu. Za sada, napravimo istu tabelu koja je već kreirana pomoću funkcije SUMIFS. Da biste to učinili, povucite u područje Vrijednosti polje "Prihodi", u oblasti Strings prevucite polje “Region” (regija prodaje) na Kolone- "Proizvod".

Kao rezultat, dobijamo pravu stožernu tabelu.

Bilo je potrebno 5-10 sekundi da se napravi. Izmjena postojeće pivot tablice je također jednostavna. Pogledajmo kako se želje režisera lako pretvaraju u stvarnost.

Zamijenimo prihod profitom.

Proizvodi i područja se također mogu zamijeniti povlačenjem miša.

Postoji nekoliko dostupnih alata za filtriranje pivot tablica. U ovom slučaju, jednostavno ćemo postaviti polje „Upravitelj“ u područje filtera.

Sve je trajalo nekoliko sekundi. Evo kako je zaista lako raditi sa zaokretnim tabelama. Naravno, nisu svi zadaci tako trivijalni. Postoje i slučajevi kada je potrebno koristiti sofisticiraniju metodu agregacije, dodavanje izračunatih polja, uslovno oblikovanje itd. Ali ovo je naprednija upotreba zaokretnih tabela.

Početni podaci

Za uspješan rad sa zaokretnim tabelama, izvorni podaci moraju ispuniti niz zahtjeva. Preduslov je prisustvo imena iznad svakog polja (kolone) po kojima će ova polja biti identifikovana. Sada nekoliko korisnih savjeta.

1. Najbolji format za podatke je Excel tabela. Dobro je jer svako polje ima ime i kada se dodaju novi redovi, oni se automatski uključuju u pivot tabelu.

2. Izbjegavajte ponavljanje grupa u kolonama. Na primjer, svi datumi trebaju biti u jednom polju, a ne podijeljeni, na primjer, po mjesecima u odvojenim kolonama.

3. Uklonite praznine i prazne ćelije, inače ovaj red može ispasti iz analize.

4. Primijenite odgovarajuće formatiranje na polja. Brojevi moraju biti u numeričkom formatu, datumi moraju biti datumi. U suprotnom će nastati problemi prilikom grupisanja i matematičke obrade. Ali Excel će vam ovdje pomoći, jer... Prilično dobro određuje format podataka.

Općenito, postoji nekoliko zahtjeva, ali ih trebate znati.

Ako izvršite promjene u izvoru (na primjer, dodate nove redove), zaokretna tabela se neće promijeniti dok je ne osvježite desnom tipkom miša

ili
preko komande u kartici Podaci – Osvježi sve.

Ovo je urađeno namjerno jer pivot tablica zauzima puno prostora u RAM-u. Zbog toga se radi ekonomičnijeg korišćenja računarskih resursa ne radi direktno sa izvorom, već sa keš memorijom, gde se nalazi snimak izvornih podataka.

Koristeći pivot tabele čak i na ovom osnovnom nivou, možete značajno povećati brzinu i kvalitet obrade velikih količina podataka. Ako nešto ostane nejasno, pišite u komentarima.

Pivot table je jedan od najmoćnijih Excel alata. Oni vam omogućavaju da analizirate i sumirate različite rezultate za velike količine podataka sa samo nekoliko klikova. U ovom članku ćemo se upoznati sa pivot tablicama, razumjeti šta su, naučiti kako ih kreirati i konfigurirati.

Prilikom pisanja ovog članka koristio sam Excel 2010. Koncept zaokretnih tabela ostao je uglavnom nepromijenjen dugi niz godina, ali način na koji se kreiraju malo se razlikuje u svakoj novoj verziji Excela. Ako imate verziju Excel-a koja nije 2010, budite spremni da će se snimci ekrana u ovom članku razlikovati od onoga što vidite na ekranu.

Malo istorije

U zoru razvoja programa za kreiranje tabela, pravila Lotusa su bila 1-2-3. Njegova dominacija bila je toliko potpuna da su se napori Microsofta da razvije sopstveni softver (Excel) kao alternativu Lotusu činili gubljenjem vremena. Sada brzo naprijed u 2010! Excel dominira industrijom proračunskih tablica više nego što je Lotus ikada učinio u svojoj istoriji, a broj ljudi koji još uvijek koriste Lotus približava se nuli. Kako se ovo moglo dogoditi? Šta je bio razlog tako dramatičnog preokreta?

Analitičari identifikuju dva glavna faktora:

• Prvo, Lotus je odlučio da je ova novonastala GUI platforma pod nazivom Windows samo prolazna moda koja neće dugo trajati. Odbili su da naprave Windows verziju Lotusa 1-2-3 (ali samo na nekoliko godina), predviđajući da je DOS verzija njihovog softvera sve što će potrošačima ikada trebati. Microsoft je, naravno, razvio Excel posebno za Windows.
• Drugo, Microsoft je razvio alat u Excel-u pod nazivom pivot table, koji nije bio dostupan u Lotusu 1-2-3. Zaokretne tabele, ekskluziva za Excel, bile su toliko nevjerovatno korisne da su ljudi bili skloni da nauče novi Excel softver umjesto da se drže Lotusa 1-2-3, koji ih nije imao.

Pivot tabele, zajedno sa potcenjivanjem uspeha Windows-a uopšte, odigrale su marš smrti za Lotus 1-2-3 i označile početak uspeha Microsoft Excel-a.

Šta su zaokretne tabele?

Dakle, koji je najbolji način da se opiše šta su pivot tabele?

Jednostavno rečeno, zaokretne tabele su rezimei nekih podataka, kreirani da bi se olakšala analiza ovih podataka. Za razliku od ručno kreiranih ukupnih iznosa, Excel zaokretne tabele su interaktivne. Jednom kreirane, možete ih lako promijeniti ako ne daju sliku koju ste se nadali. Sa samo nekoliko klikova, zbrojevi se mogu preokrenuti tako da naslovi kolona postanu naslovi redova i obrnuto. Postoji mnogo različitih stvari koje možete učiniti sa zaokretnim tablicama. Umjesto da pokušavate riječima opisati sve mogućnosti pivot tablica, lakše je to pokazati u praksi...

Podaci koje analizirate pomoću zaokretnih tabela ne mogu biti bilo kakva vrsta podataka. Ovo bi trebali biti sirovi podaci, kao neka vrsta liste. Na primjer, ovo može biti lista završenih prodaja u kompaniji u posljednjih šest mjeseci.

Pogledajte podatke prikazane na donjoj slici:

Imajte na umu da ovo nisu neobrađeni podaci jer su već sažeti. U ćeliji B3 vidimo 30.000 dolara, što je vjerovatno ukupan iznos koji je James Cook zaradio u januaru. Gdje su onda izvorni podaci? Odakle cifra od 30.000 dolara? Gdje je originalna lista prodaje iz koje je izveden ovaj mjesečni iznos? Jasno je da se neko potrudio da organizuje i sortira sve podatke o prodaji za poslednjih šest meseci i pretvori ih u zbirnu tabelu koju vidimo. Šta mislite koliko je trajalo? Sat? Deset sati?

Činjenica je da gornja tabela nije sažeta tabela. Napravljen je ručno od sirovih podataka pohranjenih na drugom mjestu i za obradu je trebalo najmanje nekoliko sati. Ovo je upravo ona vrsta sažete tabele koja se može kreirati pomoću pivot tabela, trošeći samo nekoliko sekundi na nju. Hajde da shvatimo kako...

Ako se vratimo na originalnu prodajnu listu, to bi izgledalo otprilike ovako:

Možda ćete biti iznenađeni da iz ove liste trgovačkih transakcija, koristeći pivot tablice i za samo nekoliko sekundi, možemo kreirati mjesečni izvještaj o prodaji u Excelu, o čemu smo gore govorili. Da, možemo ovo i još mnogo više!

Kako napraviti pivot tablicu?

Prvo, uvjerite se da imate neke izvorne podatke na Excel listu. Lista finansijskih transakcija je najtipičnija stvar koja se nalazi. Zapravo, to može biti lista bilo čega: kontakt informacije zaposlenika, zbirka CD-ova ili podaci o potrošnji goriva vaše kompanije.

Dakle, pokrenimo Excel... i učitajmo ovu listu...

Kada otvorimo ovu listu u Excel-u, možemo početi kreirati zaokretnu tabelu.

Odaberite bilo koju ćeliju sa ove liste:

Zatim na kartici Insert(Insert) odaberite komandu Zaokretna tabela(Tabela sažetka):

Pojavit će se okvir za dijalog Kreirajte zaokretnu tabelu(Kreiranje zaokretne tabele) sa dva pitanja za vas:

• Koje podatke trebam koristiti za kreiranje nove pivot tablice?
• Gdje da postavim stožer?

Pošto smo u prethodnom koraku već odabrali jednu od ćelija liste, cijela lista će biti automatski odabrana za kreiranje pivot tablice. Imajte na umu da možemo odabrati drugačiji raspon, drugu tabelu ili čak neki eksterni izvor podataka, kao što je Access ili MS-SQL tabela baze podataka. Osim toga, moramo odabrati gdje ćemo postaviti novu stožernu tabelu: na novi list ili na jedan od postojećih. U ovom primjeru ćemo izabrati opciju – Novi radni list(na novom listu):

Excel će kreirati novi radni list i na njega postaviti praznu zaokretnu tabelu:

Čim kliknemo na bilo koju ćeliju u zaokretnoj tabeli, pojavit će se drugi dijaloški okvir: Lista polja zaokretne tabele(Polja zaokretne tabele).

Lista polja na vrhu dijaloškog okvira je lista svih naslova u originalnoj listi. Četiri prazna područja na dnu ekrana vam omogućavaju da zaokretnoj tabeli kažete kako želite da sumirate podatke. Iako su ove oblasti prazne, ni u tabeli nema ničega. Sve što treba da uradimo je da prevučemo naslove iz gornjeg dela u prazna područja ispod. U ovom slučaju, pivot tablica se automatski generira u skladu s našim uputama. Ako smo pogriješili, možemo ukloniti naslove iz donjeg područja ili povući druge da ih zamijenimo.

Region Vrijednosti(Značenja) je vjerovatno najvažnije od četiri. Koji naslov se nalazi u ovoj oblasti određuje koji će podaci biti sumirani (zbir, prosek, maksimum, minimum, itd.). To su skoro uvek numeričke vrednosti. Odličan kandidat za poziciju u ovoj oblasti - podaci pod naslovom Iznos(Cijena) našeg originalnog stola. Hajde da povučemo ovaj naslov na područje Vrijednosti(Vrijednosti):

Imajte na umu da je naslov Iznos je sada označena kvačicom iu tom području Vrijednosti Pojavio se unos (Vrijednosti). Zbroj iznosa(polje Zbroj po iznosu), što označava da je kolona Iznos sažeo

Ako pogledamo samu stožernu tabelu, videćemo zbir svih vrednosti iz kolone Iznos originalni sto.

Dakle, naša prva pivot tablica je kreirana! Zgodno, ali ne posebno impresivno. Vjerovatno želimo više informacija o našim podacima nego što ih trenutno imamo.

Pogledajmo izvorne podatke i pokušamo identificirati jednu ili više kolona koje se mogu koristiti za razbijanje ovog zbroja. Na primjer, možemo kreirati našu pivot tablicu na način da se ukupan iznos prodaje izračunava za svakog prodavca posebno. One. Redovi će biti dodati u našu stožernu tabelu sa imenom svakog prodavca kompanije i njegovim ukupnim iznosom prodaje. Da biste postigli ovaj rezultat, samo prevucite naslov Prodavac Oznake redova(Linije):

Sve je zanimljivije! Naš stožer počinje da se oblikuje...

Vidite li prednosti? U nekoliko klikova kreirali smo tabelu za koju bi bilo potrebno mnogo vremena da se napravi ručno.

Šta drugo možemo učiniti? Pa, na neki način, naša stožerna tabela je već spremna. Napravili smo koristan sažetak neobrađenih podataka. Važne informacije su već primljene! U ostatku ovog članka, pogledat ćemo neke načine za kreiranje složenijih zaokretnih tablica i naučiti kako ih prilagoditi.

Postavljanje pivot table

Prvo, možemo kreirati dvodimenzionalnu stožernu tabelu. Uradimo to koristeći zaglavlje kolone Način plaćanja(Način plaćanja). Samo prevucite naslov Način plaćanja regionu Column Labels(kolone):

Dobijamo rezultat:

Izgleda veoma cool!

Sada napravimo trodimenzionalnu tabelu. Kako bi mogao izgledati takav sto? Hajde da pogledamo…

Prevucite naslov Paket(kompleks) na područje Filter izvještaja(Filteri):

Obratite pažnju gde je završio...

Ovo nam daje priliku da filtriramo izvještaj prema atributu „Koji je kompleks za odmor plaćen“. Na primjer, možemo vidjeti raščlambu po prodavačima i načinima plaćanja za sve komplekse, ili u par klikova možemo promijeniti prikaz zbirne tabele i prikazati istu raščlambu samo za one koji su naručili kompleks Sunseekers.

Dakle, ako ovo dobro razumijete, onda se naša stožerna tablica može nazvati trodimenzionalnom. Nastavimo sa konfigurisanjem...

Ako se odjednom ispostavi da u zaokretnoj tabeli treba da budu prikazana samo plaćanja čekom i kreditnom karticom (tj. bankovni transfer), tada možemo onemogućiti prikaz zaglavlja Cash(Gotovina). Da biste to uradili pored Column Labels kliknite na strelicu nadole i u padajućem meniju poništite izbor stavke Cash:

Hajde da vidimo kako sada izgleda naša stožerna tabela. Kao što vidite, kolona Cash nestao iz nje.

Formatiranje zaokretnih tabela u Excel-u

Očigledno, zaokretne tabele su veoma moćan alat, ali za sada rezultati izgledaju pomalo obični i dosadni. Na primjer, brojevi koje zbrajamo ne izgledaju kao iznosi u dolarima – oni su samo brojevi. Hajde da popravimo ovo.

Primamljivo je poduzeti uobičajene radnje u takvoj situaciji i jednostavno odabrati cijelu tabelu (ili cijeli list) i koristiti standardne tipke za formatiranje brojeva na alatnoj traci da biste postavili željeni format. Problem s ovim pristupom je da ako ikada promijenite strukturu zaokretne tablice u budućnosti (a postoji 99% šanse da se to dogodi), formatiranje će biti izgubljeno. Potreban nam je način da to učinimo (skoro) trajnim.

Prvo, pronađimo unos Zbroj iznosa u oblasti Vrijednosti(Vrijednosti) i kliknite na njega. U meniju koji se pojavi izaberite stavku Postavke polja vrijednosti(Opcije polja vrijednosti):

Pojavit će se okvir za dijalog Postavke polja vrijednosti(Parametri polja vrijednosti).

Kliknite na dugme Format broja(Format broja), pojavljuje se dijaloški okvir Format Cells(Format ćelije):

Sa liste Kategorija(Formati brojeva) odaberite Računovodstvo(Finansijski) i postavite broj decimalnih mjesta na nulu. Sada pritisnite nekoliko puta uredu da se vratimo na našu stožernu tabelu.

Kao što vidite, brojevi su na kraju bili formatirani kao iznosi u dolarima.

Sada kada smo završili s formatiranjem, postavimo format za cijelu zaokretnu tablicu. Postoji nekoliko načina da to učinite. Hajde da koristimo jednostavniji...

Otvorite karticu Alati za zaokretnu tablicu: Dizajn(Rad sa zaokretnim tabelama: Dizajner):

Odaberite bilo koji odgovarajući stil i pogledajte rezultat u svojoj pivot tablici:

Ostale postavke za zaokretne tabele u Excel-u

Ponekad morate filtrirati podatke po datumima. Na primjer, naša lista trgovačkih transakcija sadrži mnogo, mnogo datuma. Excel pruža alat za grupisanje podataka po danu, mjesecu, godini itd. Da vidimo kako se to radi.

Prvo uklonite unos. Način plaćanja iz regiona Column Labels(Kolone). Da biste to uradili, prevucite ga nazad na listu naslova i na njegovo mesto pomerite naslov Date Booked(Datum rezervacije):

Kao što vidite, ovo je privremeno učinilo našu stožernu tabelu beskorisnom. Excel je kreirao zasebnu kolonu za svaki datum na koji je izvršena trgovina. Kao rezultat, dobili smo veoma širok sto!

Da biste to popravili, kliknite desnom tipkom miša na bilo koji datum i odaberite iz kontekstnog izbornika Grupa(Grupa):

Pojavljuje se dijalog za grupisanje. Mi biramo Mjeseci(Mjeseci) i pritisnite uredu:

Voila! Ova tabela je mnogo korisnija:

Inače, ova tabela je skoro identična onoj prikazanoj na početku članka, gdje su rezultati prodaje ručno sastavljeni.

Postoji još jedna vrlo važna stvar koju morate znati! Možete kreirati ne jedan, već nekoliko nivoa naslova redova (ili kolona):

...i to će izgledati ovako...

Ista stvar se može uraditi sa zaglavljima kolona (ili čak filterima).

Vratimo se na originalni prikaz tabele i vidimo kako prikazati prosječne vrijednosti umjesto zbroja.

Za početak kliknite na Zbroj iznosa i iz menija koji se pojavi izaberite Postavke polja vrijednosti(Opcije polja vrijednosti):

Na listi Sažeti polje vrijednosti po(Operacija) u dijaloškom okviru Postavke polja vrijednosti(Opcije polja vrijednosti) odaberite Prosjek(prosjek):

U isto vrijeme, dok smo ovdje, promijenimo se Prilagođeno ime(Prilagođeni naziv) sa Prosjek iznosa(polje Količina po količini) na nešto kraće. Unesite nešto ovako u ovo polje Avg:

Kliknite uredu i vidi šta se desilo. Primijetite da su se sve vrijednosti promijenile iz ukupnih u prosjeke, a naslov tabele (u gornjoj lijevoj ćeliji) se promijenio u Avg:

Ako želite, možete odmah dobiti sumu, prosek i količinu (prodaju) smeštene u jednu stožernu tabelu.

Evo korak-po-korak vodiča kako to učiniti, počevši s praznom zaokretnom tablicom:

1. Prevucite naslov Prodavac(Predstavnik prodaje) u regiji Column Labels(Kolone).
2. Povucite naslov tri puta Iznos(Cijena) na područje Vrijednosti(Vrijednosti).
3. Za prvo polje Iznos promijenite ime u Ukupno(Iznos), a format broja u ovom polju je Računovodstvo(finansijski). Broj decimalnih mjesta je nula.
4. Drugo polje Iznos ime Prosjek e, podesite operaciju za to Prosjek(Prosjek) i promijenite format broja u ovom polju u Računovodstvo(Finansijski) sa brojem decimalnih mjesta jednakim nuli.
5. Za treće polje Iznos set title Count i operacija za njega - Count(Količina)
6. U području Column Labels(Kolone) polje automatski kreirano ΣValues(Σ vrijednosti) – prevucite ga na područje Oznake redova(žice)

Evo šta dobijamo na kraju:

Ukupan, prosek i broj prodaja - sve u jednoj pivot tabeli!

Zaključak

Zaokretne tabele u Microsoft Excel-u sadrže mnogo, mnogo funkcija i postavki. Ovako kratak članak ne može ni blizu da ih sve pokrije. Za potpuni opis svih mogućnosti pivot tablica potrebna je mala knjiga ili velika web stranica. Hrabri i radoznali čitaoci mogu nastaviti da istražuju pivot tabele. Da biste to učinili, samo kliknite desnom tipkom miša na gotovo bilo koji element zaokretne tablice i pogledajte koje se funkcije i postavke otvaraju. Na traci ćete pronaći dvije kartice: Alati za zaokretnu tabelu: Opcije(Analiza) i Dizajn(Konstruktor). Nemojte se plašiti da napravite grešku; uvek možete da izbrišete stožernu tabelu i počnete ispočetka. Imate priliku koju dugogodišnji korisnici DOS-a i Lotusa 1-2-3 nikada nisu imali.

Zaokretne tabele u Excel-u vam omogućavaju da analizirate podatke koji se nalaze u jednom velikom opsegu. Štaviše, izvorna tabela koja će se koristiti za analizu može se kreirati ili u Excel-u ili u drugoj bazi podataka ili drugom dokumentu.

Sve što trebate učiniti je odabrati potrebna zaglavlja za redove i stupce od postojećih. Filter također možete primijeniti na cijelu zaokretnu tablicu u cjelini ili samo na vrijednosti koje su naznačene u nazivima redova i stupaca.

Sada o zahtjevima koji se moraju poštovati prilikom kreiranja u Excelu. Izvorni podaci moraju biti predstavljeni u obliku tabele, koja mora imati naslov za kolone, odnosno zaglavlje. Za ove svrhe, kreiranje pametne tablice u Excelu je savršeno. Takođe ne bi trebalo da sadrži prazne redove, kolone ili ćelije. Ne bi trebalo biti skrivenih redova/kolona ili spojenih ćelija.

Kako kreirati

Pogledajmo sada primjer kreiranja pivot tablice. Pretpostavimo da imamo podatke o prodaji trgovine odjećom: koji prodavač, koji broj, koje artikle je prodao i za koji iznos.

Da biste napravili stožernu tabelu, izaberite bilo koju ćeliju iz originalne, zatim idite na karticu „Umetanje“ i kliknite na dugme "Pivot table".

Pojavljuje se sljedeći dijaloški okvir. U njemu je potrebno naznačiti opseg ili naziv tabele sa izvornim podacima, a ovde ga možete izabrati iz drugog izvora. Zatim označite markerom gdje ćete postaviti izvještaj koji će biti kreiran. Uradimo to na novom listu. Kliknite OK.

U otvorenoj Excel radnoj svesci kreiraće se novi list na koji će se postaviti još uvek prazna zaokretna tabela.

Lista polja i oblasti će se pojaviti na desnoj strani. Polja su sva zaglavlja kolona koja su bila u originalnom rasponu. Koristeći miš, povući ćemo ih u jedno od četiri područja ispod. Tako se formira stožerna tabela.

Dodata polja će biti označena kvačicom. U oblastima tablica možete promijeniti njihova mjesta kako biste postigli izgled koji je najprikladniji za analizu vaših izvornih podataka.

Kako urediti polja

Sada ćemo odlučiti po kom principu ćemo analizirati podatke. Na primjer, trebate saznati koji prodavac je prodao koji proizvod svakog mjeseca i za koji iznos.

Podatke ćemo filtrirati prema odabranom asortimanu od strane prodavača. Odnosno, biramo prodavca, a u tabeli će biti prikazana roba koju je on prodao. Kliknite levim tasterom miša na polje „Prodavac“ i prevucite ga u oblast „Filter izveštaja“. Tabela se promijenila i dodano polje je sada označeno kvačicom.

Za linije odaberite “Proizvodi”. Slično, prevucite željeno polje u područje "Naslovi redova".

Imajte na umu da možete umetnuti više polja u područje. Na primjer, u redovima ćemo odabrati proizvode i navesti cijenu. Proizvodi su postali padajuće liste koje prikazuju cijenu. Ako prvo navedete cijenu, a zatim proizvode, padajuća lista će postati cijena. Ovdje je bitan redoslijed polja.

Kada bismo imali kolonu “Jedinice” u originalnoj tabeli. Tada bi ova zbirna tabela pokazala koji prodavac, u kom mjesecu, koliko jedinica robe je prodao po određenoj cijeni.

U regiju "Naslovi kolona" Prevucite polje „Datum“. Da biste prikazali prodaju ne za svaki dan, već, na primjer, po mjesecu, kliknite desnim tasterom miša na bilo koji datum i izaberite „Grupa“ iz menija.

Tabela će imati sljedeći oblik.

Sada prevucite polje "Iznos" u oblast "Vrijednosti".

Kao što vidite, prikazani su samo brojevi, iako je u originalnom opsegu postavljen numerički format ćelija za ovu kolonu, može biti i novčani ili finansijski. U koloni „Datum“ format ćelije je takođe bio odgovarajući – datum.

Da biste to popravili, odaberite željeni raspon ćelija u sažetku i pritisnite desnu tipku miša. zatim izaberite iz menija "Format broja".

U sljedećem prozoru odaberite "Numerički", možete označiti okvir "Razdjelnik grupe bitova" i kliknite na "OK".

Kako raditi sa podacima

Nakon što smo uzeli u obzir sva potrebna polja, možemo početi raditi sa pivot tablicom u Excelu. Odabir prodavača: možete odabrati jednog, nekoliko ili sve odjednom tako što ćete označiti kvadratić "Odaberi više stavki".

Također možete koristiti filter za redove i stupce. U primjeru, to su proizvodi i mjeseci. Na primjer, označavanjem polja za odijelo i hlače možete saznati za koliko su ih prodali svi prodavači ili određeni prodavač.

U području Vrijednosti možete konfigurirati postavke za polje. Primjer prikazuje zbir vrijednosti: Romi su u februaru prodali košulje u iznosu od 1.800,00. da vidimo koliko je komada. Kliknite lijevom tipkom miša na liniju "Zbroj po polju..." i izaberite iz menija "Opcije polja vrijednosti".

U sljedećem prozoru sa liste odaberite "Količina", odaberite ono što je pogodno za vaš slučaj i kliknite "OK".

Sada, gledajući vrijednosti, možemo shvatiti da je Roma u februaru prodala dvije majice.

Sada napravimo opšti filter za tabelu po mesecima. Mijenjamo područja: u "Filter izvještaja" povlačimo polje "Datum" u "Naslovi kolona"- "Prodavac".

Takva zbirna tabela će pokazati koliko je i koju vrstu robe svaki prodavac prodao za cijeli period ili za određeni mjesec.

Obratite pažnju i na traku. Kada odaberete bilo koju ćeliju iz zaokretne tablice, na njoj se pojavljuje kartica "Rad sa zaokretnim tabelama" sa dvije podkartice “Parametri” i “Dizajner”.

Problem sa kojim se svi suočavamo nije nedostatak podataka; naprotiv - to su ogromne količine podataka! Zato se zalažem za korištenje sjajne Excel funkcije - Pivot table da sumirate i analizirate vaše podatke.

Uvid u snagu zaokretnih tabela je jednostavan kao upoređivanje originalnih podataka sa verzijom zaokretne tabele; Sa samo nekoliko klikova mogao sam vidjeti prosječnu cijenu artikla po državi.

Kao profesionalac za finansije, genetski sam sklon da volim tabele. Ali također sam otkrio da koristim tabele za organizaciju svog kreativnog i slobodnog rada. Bez obzira za šta koristite tabele, zaokretna tabela vam može pomoći da dobijete više smisla iz svojih podataka.

Ovu lekciju ćemo izgraditi prema našoj, radi boljeg rada sa podacima. Pokazat ću vam pet mojih omiljenih najboljih praksi za pivot tablicu.

U ovom vodiču koristiću uzorke podataka koje je Microsoft pružio na ovoj stranici. Koristite ove podatke za ponovno kreiranje primjera ili testiranje funkcija koje demonstriram.

Kako koristiti napredne tehnike zaokretne tablice u Excelu (kratki video)

Volim da podučavam koristeći screencaste, koji vam daju priliku da gledate kako koristim funkcije korak po korak. Pogledajte kratki video ispod koji pokriva pet mojih omiljenih naprednih funkcija Excel zaokretne tabele:

5 naprednih trikova zaokretne tabele u Excelu

Nastavite čitati kako biste naučili kako koristiti svaku od ovih pet funkcija u lekciji u nastavku, uključujući: Slicere, vremensku liniju, prikaz tabele, izračunata polja i istaknute zaokretne tabele. Hajde da počnemo.

1. Sekcije

Slices- Pokaži i klikni alat za preciziranje podataka uključenih u Excel zaokretnu tabelu. Umetnite rezač i lako možete promijeniti podatke uključene u stožernu tabelu.

U ovom primjeru sam umetnuo isječak za tip Stavka. Nakon što kliknem na Ruksak, zaokretna tabela prikazuje samo ovaj parametar u tabeli.

Mnogo puta razvijam izvještaje na kontrolnoj tabli koje će drugi koristiti. Dodavanje rezača može pomoći krajnjem korisniku da prilagodi izvještaj po svom ukusu.

Da biste dodali rezač, kliknite na stožernu tabelu i pronađite karticu Analiza na Excel traci.

Označite više polja da biste uključili ove kolone, od kojih će svaki biti isječak.

Čekaj Ctrl na tastaturi da biste odabrali više stavki za rezanje koje će uključivati ​​višestruke odabire iz stupca kao podatke zaokretne tablice.

2. Vremenska linija

Vremenski okviri Ovo je poseban tip rezač koji se koristi za podešavanje datuma uključenih kao dio podataka zaokretne tablice. Ako vaši podaci sadrže datume, zaista morate isprobati Timeline kao način odabira podataka iz određenih vremenskih perioda.

Idi na odjeljak Analiza > Ubaci vremensku liniju da biste dodali vremensku liniju, poseban tip rezača koji upravlja podacima uključenim u naprednu Excel zaokretnu tabelu na osnovu datuma.

Savjet: Ako vam ova funkcija ne radi, provjerite da li izvorni podaci imaju datum formatiran u proračunskoj tabeli kao datum.

Da biste dodali vremensku liniju, obavezno odaberite zaokretnu tabelu (kliknite unutar nje), a zatim kliknite na dugme Analiza > Ubaci vremensku liniju na Excel traci. U iskačućem prozoru odaberite kolonu datuma (ili više kolona) i kliknite uredu da kreirate vremensku liniju.

Kliknite i prevucite u prozoru Vremenska linija da odaberete određeni vremenski raspon za zaokretnu tabelu.

Kada je vremenska linija umetnuta, možete kliknuti i pomicati ručke na vremenskoj liniji da biste promijenili ono što je uključeno u stožernu tabelu.

Možete promijeniti način rada vremenske linije klikom na padajuću listu u donjem desnom uglu. Možete promijeniti vremensku liniju da prikažete kvartalne ili godišnje podatke umjesto određenih datuma, na primjer.

3. Tabelarni prikaz

Standardni prikaz zaokretnih tabela u Excel-u izgleda kao vodopad; Kako prevlačite više polja u redove, Excel stvara više „slojeva“ u podacima.

Problem je što je teško uklopiti formule u zaokretne tabele u standardnom pogledu. Ako imate podatke u zaokretnoj tabeli, ali želite da ih vidite kao običnu tabelu, morate koristiti prikaz tabele za zaokretnu tabelu.

Odaberite Dizajner > Izgled izvještaja > Prikaži u tabličnom obliku za rad sa vašom zaokretnom tablicom kao običnom stolom.

Zašto biste trebali koristiti tabelarni prikaz? Dovođenje vaših podataka zaokretne tablice u klasičan tabelarni stil omogućit će vam da lakše unesete formule podataka ili ih umetnete u poseban izvještaj.

Koristite prikaz tabele da bi vaša zaokretna tabela više ličila na standardne redove i kolone.

Većinu vremena radije koristim Excelov prikaz tabele. Više liči na standardnu ​​tabelu i lakše je pisati formule i raditi s podacima u njoj. Mogao bih isto tako uzeti ovaj prikaz i zalijepiti ga u novu karticu.

4. Izračunata polja

Izračunata polja su način da dodate kolonu u zaokretnu tabelu koja nije u izvornim podacima. Možete koristiti standardne matematičke operacije da kreirate potpuno nova polja za rad. Uzmite dvije postojeće kolone i koristite matematiku da kreirate potpuno nove.

Recimo da imamo podatke o prodaji u tabeli. Imamo broj prodatih artikala i prodajnu cijenu za svaki artikl. Ovo je idealno vrijeme za korištenje izračunatog polja za izračunavanje ukupne narudžbe.

Da biste započeli rad s izračunatim poljima, počnite tako što ćete kliknuti unutar zaokretne tablice, a zatim pronađite na traci Analiza. Kliknite na meni Polja, elementi i skupovi a zatim odaberite Izračunato polje.

Koristi Analiza > Polja, članovi i skupovi > Izračunato polje da umetnete izračunato polje u Vašu zaokretnu tabelu.

U novom iskačućem prozoru počnite davanjem naziva izračunatom polju. u mom slučaju ja ću to nazvati Total Order. Ukupna vrijednost narudžbe je količina pomnožena cijenom svake jedinice. Zatim sam dvaput kliknuo na prvo naslovno polje (količina) na listi polja u ovom prozoru.

Da bih izračunao ukupnu cijenu narudžbe, pomnožio sam postojeće polje jedinične cijene ( Cijena po jedinici) na polju količine ( Količina).

Nakon dodavanja imena ovog polja, dodaću znak množenja * , a zatim dvaput kliknite na ukupnu količinu (količinu). Idemo dalje i kliknimo uredu.

Sada je Excel ažurirao moju proširenu zaokretnu tabelu i dodao novo izračunato polje. Također ćete vidjeti listu zaokretnih tabela na listi polja tako da ih možete prevući bilo gdje u izvještaj kad god vam zatreba.

Ako ne želite da koristite aritmetiku sa dve kolone, takođe možete da unesete sopstvene aritmetičke vrednosti u izračunato polje. Na primjer, ako želim jednostavno dodati 5% poreza na promet za svaku narudžbu, mogao bih napisati sljedeće u izračunato polje:

Pomnožio sam ukupnu cijenu narudžbe sa 1,05 da bih izračunao trošak uključujući porez na promet; možete koristiti numeričke vrijednosti zajedno sa postojećim poljima.

U principu, izračunata polja mogu sadržavati bilo koje standardne matematičke operatore kao što su sabiranje, oduzimanje, množenje i dijeljenje. Koristite ova izračunata polja ako ne želite da ažurirate originalne podatke.

Idi Umetni > da isprobate ovu funkciju.

Ova funkcija je toliko jednostavna za korištenje da se podrazumijeva. Možete ga koristiti za brzo kreiranje naprednih zaokretnih tabela u Excelu. Samo označite svoje podatke, idite na karticu Insert na Excel traci i odaberite Preporučene zaokretne tabele.

Iskačući prozor sadrži mnogo opcija za kreiranje pivot tabele iz izvornih podataka. Kliknite na sličice na lijevoj strani ovog prozora da vidite preporučene opcije zaokretne tablice koje pruža Excel.

Funkcija preporuka zaokretne tabele nudi mnoge opcije za analizu vaših podataka jednim klikom.

Iako je ovo napredna funkcija za koju je malo korisnika svjesno, ona je također odličan alat za pokretanje pivot tablica. Ništa vas ne sprečava da izmenite zaokretnu tabelu tako što ćete sami zameniti polja, ali to je dobra polazna tačka.

Također, sviđa mi se ova funkcija kao alat za istraživanje podataka. Ako ne znam šta tražim kada počnem da istražujem podatke, preporučene Excel zaokretne tabele su često pronicljivije od mene!

Ponavljajte i nastavite učiti (sa još više lekcija iz Excela)

Ova napredna lekcija iz Excela pomogla vam je da dublje zaronite u zaokretne tabele, jednu od mojih omiljenih funkcija za analizu i pregled Excel tabele. Koristim zaokretne tablice da pronađem značenje u velikim skupovima podataka kako bih mogao donijeti bolje odluke i poduzeti akciju.

Ove lekcije će vaše vještine sa Excelom i Pivot tabelama podići na viši nivo. Provjerite ih:

• ExcelZoo ima sjajan pregled tehnika zaokretne tabele u svom članku, 10 lekcija za savladavanje zaokretnih tabela (na engleskom).
• Mi u Envato Tuts+ pokrili smo pivot tablice sa vodičem za početnike.
• Za lakši uvod u Microsoft Excel, pogledajte našu seriju tutorijala.

Šta još želite znati o pivot tablicama? Javite mi svoje ideje ili pitanja u komentarima ispod ovog vodiča.