Primjeri dokumenata s excel tablicama

Izvedena je tablica možda najkorisniji alat u programu Excel. Uz njegovu pomoć pružaju se brojne mogućnosti za analizu velike količine podataka i brze izračune.

Što su zaokretne tablice u Excelu? Zaokretne tablice u Excelu za lutke

Izvedene tablice su Excel alat za sažimanje i analizu velikih količina podataka.

Recimo da imamo tablicu prodaje od 1000 linija po kupcima za godinu:

Tablica prikazuje:

  • Datumi narudžbi;
  • Regija u kojoj se nalazi klijent;
  • Tip klijenta;
  • Klijent;
  • Broj prodaja;
  • Prihod;
  • Dobit.

Sada, zamislimo da je naš upravitelj postavio zadatke za izračunavanje:

  • TOP pet klijenata po prihodu;

Da biste pronašli odgovor na ova pitanja, možete koristiti razne funkcije i formule. Ali što ako za te podatke ne postoje tri, već trideset zadataka? Svaki put morate promijeniti formule i funkcije i prilagoditi se za svaku vrstu izračuna.

Ovo je samo slučaj u kojem će vam alat PivotTable postati nezamjenjiv pomoćnik. Pomoću nje možete odgovoriti na bilo koje pitanje u nekoliko sekundi, prema podacima iz tablice.

Nadam se da ste u gornjem primjeru shvatili koliko je korisna i potrebna zaokretna tablica. Otkrijmo kako ih koristiti.

Kako napraviti pivot tablicu u Excelu

Da biste stvorili zaokretnu tablicu u Excelu, slijedite ove korake:

  • Odaberite bilo koju ćeliju u tablici s podacima na temelju kojih želite napraviti zaokretnu tablicu;
  • Kliknite karticu "Umetni" \u003d\u003e "Zaokretna tablica":

  • U skočnom dijaloškom okviru sustav će automatski odrediti granice podataka na temelju kojih možete stvoriti zaokretnu tablicu. Ova metoda u većini slučajeva funkcionira prema zadanim postavkama. Preporučujem da svaki put kada kreirate zaokretnu tablicu provjerite je li sustav ispravno definirao parametre za izradu:
    - Tablica ili raspon: sustav automatski otkriva granice podataka kako bi stvorio zaokretnu tablicu. Bit će točni pod uvjetom da u zaglavljima i redovima tablice nema razmaka. Raspon podataka možete prilagoditi prema potrebi.
  • Sustav prema zadanim postavkama stvara tablicu na novoj kartici datoteke Excel. Ako ga želite stvoriti na određenom mjestu na određenom listu, tada možete odrediti granice koje ćete stvoriti u stupcu "Na postojećem listu".



  • Kliknite "U redu".

Nakon što kliknete gumb "OK", stvorit će se zaokretna tablica.

Jednom kada se kreira stožerna tablica, na listu nećete vidjeti nikakve podatke. Sve što će biti dostupno je naziv zaokretne tablice i izbornik za odabir podataka za prikaz.



Prije nego počnemo analizirati podatke, predlažem da razumijemo što znači svako polje i područje zaokretne tablice.

Regije zaokretne tablice u Excelu

Da bi se pivot tablice učinkovito koristile, važno je temeljito znati kako one funkcioniraju i od čega se sastoje.

Saznajte više o područjima u nastavku:

  • Predmemorija pivot tablice
  • Područje vrijednosti
  • Područje gudača
  • Područje stupaca
  • Područje filtera

Što je predmemorija pivot tablice

Kada stvorite zaokretnu tablicu, Excel kreira predmemoriju (međuspremnik za brzi pristup srednjim podacima koji sadrži informacije za koje je najvjerojatnije da će se tražiti) podataka iz kojih će se graditi tablica.

Kada izvodite izračune na stvorenoj tablici, Excel se ne poziva svaki put na izvorne podatke, već koristi podatke iz predmemorije. Ova značajka značajno smanjuje količinu sistemskih resursa utrošenih na obradu i računanje podataka.

Važno je zapamtiti da predmemorija podataka povećava veličinu Excel datoteke.

Područje vrijednosti

Područje Vrijednosti sadrži sve vrijednosti tablice i može se prikazati kao glavna komponenta zaokretne tablice. Zamislite da želimo prikazati prodaju regija po mjesecima (iz primjera na početku članka) pomoću zaokretne tablice. Zasjenjene vrijednosti žuta boja na donjoj slici i vrijednosti su koje specificiramo u stožerna tablica u području Vrijednosti.



U gornjem primjeru stvorili smo stožernu tablicu koja prikazuje podatke o prodaji po regijama, podijeljene po mjesecima.

Područje gudača

Zaglavlja tablice s lijeve strane vrijednosti su redovi. U našem primjeru to su imena regija. Na snimci zaslona ispod crte su označene crtama:



Područje stupaca

Naslovi na vrhu vrijednosti tablice nazivaju se "Stupci".

U donjem primjeru polja "Stupci" označena su crvenom bojom, u našem slučaju to su vrijednosti mjeseci.



Područje filtera

Područje "Filteri" nije obavezno i \u200b\u200bomogućuje vam podešavanje razine detalja za podatke zaokretne tablice. Na primjer, podatke "Vrsta kupca" - "Trgovina mješovitom robom" možemo odrediti kao filtar, a Excel će u tablici prikazati podatke koji se odnose samo na trgovine s namirnicama.



Zaokretne tablice u Excelu. Primjeri

U primjerima u nastavku potražit ćemo kako pomoću zaokretnih tablica odgovoriti na tri pitanja s početka ovog članka:

  • Koliki je opseg prihoda za regiju Sjever u 2017. godini;
  • TOP pet klijenata po prihodu;
  • Kakvo je mjesto IP klijenta Ludnikov u regiji Vostok po prihodu?

Prije analize podataka važno je odlučiti kako podaci tablice trebaju izgledati (koje podatke označiti u stupce, retke, vrijednosti, filtre). Na primjer, ako trebamo prikazati podatke o prodaji kupaca po regijama, tada bismo trebali staviti imena regija u retke, mjesece u stupce, vrijednosti prodaje u polje "Vrijednosti". Jednom kad steknete ideju kako vidite konačnu zaokretnu tablicu, počnite je stvarati.

Prozor "Polja zaokretne tablice" sadrži područja i polja sa vrijednostima za smještaj u zaokretnu tablicu:

Polja se izrađuju na temelju izvornih podataka koji se koriste za zaokretnu tablicu. Odjeljak Područja je mjesto na kojem postavljate polja, a prema kojem će se području polje smjestiti, vaši se podaci ažuriraju u zaokretnoj tablici.

Premještanje polja iz područja u područje prikladno je sučelje u kojem se, dok se krećete, podaci u zaokretnoj tablici automatski ažuriraju.



Pokušajmo sada odgovoriti na pitanja upravitelja s početka ovog članka s primjerima u nastavku.

Primjer 1. Koliki prihod ima regija Sjever?

Kako bih izračunao obujam prodaje regije Sjever, predlažem da se podaci o prodaji svih regija stave u pivot tablicu. Za ovo nam je potrebno:

  • stvorite zaokretnu tablicu i premjestite polje "Regija" u područje "Redci";
  • stavite polje "Prihod" u područje "Vrijednosti"

Dobit ćemo odgovor: prodaja regije Sjever iznosi 1 233 006 966 ₽:



Primjer 2. TOP pet kupaca po prodaji

  • u pivot tablici pomaknite polje "Kupac" u područje "Redovi";
  • postavite polje "Prihod" u područje "Vrijednosti";
  • postavite format financijskog broja na zaokretne ćelije tablice s vrijednostima.

Dobivamo sljedeću zaokretnu tablicu:



Prema zadanim postavkama Excel sortira podatke u tablici abecednim redom. Slijedite ove korake kako biste sortirali podatke prema količini prodaje:

  • idite na izbornik “Poredaj” \u003d\u003e “Poredaj silazno”:



Kao rezultat, dobit ćemo sortirani popis kupaca prema prihodu.



Primjer 3. Kakvo je mjesto u smislu prihoda klijenta Ludnikov IE u regiji Vostok?

Da bih izračunao mjesto prema obujmu prihoda klijenta Ludnikov IP u regiji Vostok, predlažem stvaranje pivot tablice koja će prikazivati \u200b\u200bpodatke o prihodima po regijama i klijentima unutar ove regije.

Za ovo:

  • postavite polje zaokretne tablice "Regija" u područje "Redovi";
  • stavite polje "Kupac" u područje "Žice" ispod polja "Regija";
  • postavite format financijskog broja na ćelije s vrijednostima.

Čim polja "Regija" i "Kupac" postavite jedno ispod drugog u područje "Redovi", sustav će razumjeti kako želite prikazati podatke i predložiti odgovarajuću opciju.

  • polje "Prihod" bit će smješteno u područje "Vrijednosti".

Kao rezultat, dobili smo stožernu tablicu koja odražava podatke o prihodima kupaca u svakoj regiji.



Da biste sortirali podatke, slijedite ove korake:

  • desnom tipkom miša kliknite bilo koji redak podataka o prihodu na zaokretnoj tablici;
  • idite na izbornik “Poredaj” \u003d\u003e “Poredaj silazno”:



U dobivenoj tablici možemo utvrditi koje mjesto zauzima klijent Ludnikov IP među svim klijentima regije Vostok.



Postoji nekoliko mogućnosti za rješavanje ovog problema. Polje "Regija" možete premjestiti u područje "Filteri" i u redove smjestiti podatke o prodaji kupaca, odražavajući tako podatke o prihodu samo za kupce regije Istok.

Ako nikada prije niste koristili proračunsku tablicu za izradu dokumenata, preporučujemo vam čitanje našeg Excel vodiča za lutke.

Tada možete stvoriti svoju prvu proračunsku tablicu s tablicama, grafikonima, matematičkim formulama i oblikovanjem.

Detaljne informacije o osnovnim funkcijama i mogućnostima stolni procesor MS Excel. Opis glavnih elemenata dokumenta i upute za rad s njima u našem materijalu.



Rad sa stanicama. Padding i formatiranje

Prije nego što nastavite s određenim radnjama, morate razumjeti osnovni element bilo koji dokument u Excelu. Datoteka Excel sastoji se od jednog ili više listova, podijeljenih u male ćelije.

Ćelija je osnovna komponenta bilo kojeg Excel izvješća, proračunske tablice ili grafikona. Svaka ćelija sadrži jedan blok informacija. To može biti broj, datum, valuta, mjerna jedinica ili drugi format podataka.

Da biste popunili ćeliju, samo je kliknite pokazivačem i unesite potrebne podatke. Da biste uredili prethodno ispunjenu ćeliju, dvaput kliknite na nju.

Lik: 1 - primjer punjenja ćelija

Svaka ćelija na listu ima svoju jedinstvenu adresu. Dakle, s njim možete vršiti izračune ili druge operacije. Kada kliknete na ćeliju, na vrhu prozora pojavit će se polje s adresom, imenom i formulom (ako je ćelija uključena u bilo koji izračun).

Označimo ćeliju "Udio dionica". Njegova lokacija je A3. Te su informacije naznačene na ploči svojstava koja se otvara. Sadržaj također možemo vidjeti. Ova ćelija nema formule, pa nisu prikazane.

Više svojstava i funkcija ćelija koje se mogu upotrijebiti u vezi s njom dostupno je u kontekstnom izborniku. Kliknite desnu tipku manipulatora na ćeliju. Otvorit će se izbornik pomoću kojeg možete formatirati ćeliju, analizirati sadržaj, dodijeliti drugu vrijednost i druge radnje.

Lik: 2 - kontekstni izbornik ćelije i njegova glavna svojstva

Sortiranje podataka

Često se korisnici suočavaju sa zadatkom sortiranja podataka na listu u Excelu. Ova vam značajka pomaže u brzom odabiru i prikazu samo podataka koje želite iz cijele tablice.

Pred vama je već popunjena tablica (kako ćemo je stvoriti shvatit ćemo kasnije u članku). Zamislite da želite sortirati podatke za siječanj uzlaznim redoslijedom. Kako biste to učinili? Banalno pretipkavanje stola dodatni je posao, osim toga, ako je obiman, to nitko neće učiniti.

Excel ima posebnu funkciju za razvrstavanje. Korisnik je dužan samo:

  • Odaberite tablicu ili blok informacija;
  • Otvorite karticu "Podaci";
  • Kliknite ikonu "Poredaj";

Lik: 3 - kartica "Podaci"

  • U prozoru koji se otvori odaberite stupac tablice nad kojim ćemo izvršiti radnje (siječanj).
  • Dalje, vrsta sortiranja (grupiramo prema vrijednosti) i, konačno, redoslijed je uzlazan.
  • Potvrdite radnju klikom na "U redu".

Lik: 4 - postavljanje parametara sortiranja

Podaci će se automatski sortirati:

Lik: 5 - rezultat sortiranja znamenki u stupcu "Siječanj"

Slično tome, možete sortirati prema boji, fontu i drugim parametrima.

Matematički proračuni

Glavna prednost Excela je mogućnost automatskog izvršavanja izračuna u procesu popunjavanja tablice. Na primjer, imamo dvije ćelije s vrijednostima 2 i 17. Kako unijeti njihov rezultat u treću ćeliju, a da sami ne izvršimo izračune?

Da biste to učinili, morate kliknuti na treću ćeliju u koju će se unijeti konačni rezultat izračuna. Zatim kliknite ikonu funkcije f (x) kao što je prikazano na donjoj slici. U prozoru koji se otvori odaberite radnju koju želite primijeniti. SUM je zbroj, PROSJEČNO je prosjek itd. Potpuni popis funkcije i njihova imena u programu Excel editor mogu se naći na službenim web stranicama Microsofta.

Moramo pronaći zbroj dviju ćelija, pa kliknemo na "ZBIR".

Lik: 6 - odabir funkcije "SUM"

Prozor argumenata funkcije ima dva polja: "Broj 1" i "Broj 2". Odaberite prvo polje i kliknite na ćeliju s brojem "2". Njegova adresa bit će zapisana u niz argumenata. Kliknite "Broj 2" i kliknite ćeliju s brojem "17". Zatim potvrdite radnju i zatvorite prozor. Ako trebate izračunati matematiku s tri ili više okvira, samo nastavite unositi vrijednosti argumenata u okvire Broj 3, Broj 4 itd.

Ako se u budućnosti vrijednost sabranih stanica promijeni, njihov će se zbroj automatski ažurirati.

Lik: 7 - rezultat izračuna

Izrada tablica

Bilo koji podatak može se pohraniti u Excel tablice. Uz pomoć funkcije brzog postavljanja i formatiranja, u uređivaču je vrlo jednostavno organizirati osobni sustav kontrole proračuna, popis troškova, digitalne podatke za izvještavanje itd.

Tablice u Excelu imaju prednost nad sličnom opcijom u programu Word i ostalim uredskim programima. Ovdje imate priliku stvoriti tablicu bilo koje dimenzije. Podaci se lako popunjavaju. Postoji funkcijska ploča za uređivanje sadržaja. U Dodatku, spreman stol može se integrirati u docx datoteku pomoću uobičajene funkcije copy-paste.

Da biste stvorili tablicu, slijedite upute:

  • Kliknite karticu Umetanje. Na lijevoj strani okna s opcijama odaberite Tablica. Ako trebate sažeti bilo koje podatke, odaberite stavku "Zaokretna tablica";
  • Pomoću miša odaberite mjesto na listu koje će biti dodijeljeno za tablicu. Također možete unijeti mjesto podataka u prozor za stvaranje elementa;
  • Kliknite U redu da biste potvrdili radnju.

Lik: 8 - stvaranje standardne tablice

Za formatiranje izgled rezultirajuće ploče otvorite sadržaj konstruktora i u polju "Stil" kliknite predložak koji vam se sviđa. Po želji možete stvoriti vlastiti izgled s drugačijom shemom boja i odabirom stanica.

Lik: 9 - formatiranje tablice

Rezultat popunjavanja tablice podacima:

Lik: 10 - popunjena tablica

Za svaku ćeliju u tablici također možete prilagoditi vrstu podataka, oblikovanje i način prikaza podataka. Prozor dizajnera sadrži sve potrebne mogućnosti za daljnju konfiguraciju ploče, ovisno o vašim zahtjevima.

Dodavanje grafikona / grafikona

Za izradu grafikona ili grafikona potrebna je gotova pločica, jer će se grafički podaci temeljiti upravo na informacijama preuzetim iz pojedinih linija ili ćelija.

Da biste stvorili grafikon / grafikon, trebate:

  • Potpuno odaberite tablicu. Ako grafički element treba stvoriti samo za prikaz podataka određene stanice, odaberite samo njih;
  • Otvorite karticu za umetanje;
  • U okviru preporučenih grafikona odaberite ikonu za koju mislite da će najbolje vizualno opisati tablične podatke. U našem slučaju ovo je trodimenzionalni tortni grafikon. Pomaknite pokazivač na ikonu i odaberite izgled elementa;

    • Slično tome, možete stvoriti raspršene grafikone, linijske tablice i ovisnosti elemenata tablice. Svi primljeni grafički elementi također se mogu dodati u tekstualni dokumenti Riječ.

    U uređivaču proračunskih tablica Excel postoje mnoge druge funkcije, međutim, tehnike opisane u ovom članku bit će dovoljne za početni rad. U procesu stvaranja dokumenta mnogi korisnici samostalno ovladavaju naprednijim opcijama. To je zbog korisničkog i intuitivnog sučelja. najnovije verzije programa.

    Tematski videozapisi:

Izvedena tablica u Excelu moćan je alat za analizu podataka koji će vam pomoći brzo:

  • Priprema podataka za izvješća;
  • Izračunajte razne pokazatelje;
  • Podaci o grupi;
  • Filtrirajte i analizirajte metrike od interesa.

A također ćete uštedjeti tone vremena.

U ovom ćete članku naučiti:

  • Kako napraviti pivot tablicu;
  • Kako se koristi pivot tablica grupne vremenske serije i procjenjuju podatke u dinamici po godinama, tromjesečjima, mjesecima, danima ...
  • Kako izračunati prognozu pomoću zaokretne tablice i Forecast4AC PRO;

Prvo, naučimo kako napraviti pivot tablice.

Da bismo napravili zaokretnu tablicu, podatke moramo izgraditi u obliku jednostavne tablice. Svaki stupac mora imati 1 raščlanjeni parametar. Na primjer, imamo 3 stupca:

  • Proizvod
  • Prodaja u rubaljima

I u svaki redak 3. parametri su povezani, t.j. na primjer, 01. 02. 2010., Proizvod 1 prodan je za 422 656 rubalja.

Nakon što pripremite podatke za zaokretnu tablicu, postavite pokazivač u prvi stupac u prvu ćeliju jednostavne tablice, a zatim idite na izbornik "Umetni" i pritisnite gumb "Pivot table"

Pojavit će se dijaloški okvir u kojem:

  • možeš odmah pritisnite gumb "OK", a zaokretna tablica prikazat će se na zasebnom listu.
  • ili možete konfigurirati parametre izlaza podataka zaokretne tablice:
  1. Raspon s podacima koji će se prikazati u zaokretnoj tablici;
  2. Gdje prikazati zaokretnu tablicu (na novi list ili na postojeći (ako odaberete postojeći, morat ćete navesti ćeliju u koju želite smjestiti izravnu tablicu)).


Kliknite "U redu", zaokretna tablica je spremna i prikazana na novom listu. Nazovimo list Pivot.

  • Na desnoj strani lista vidjet ćete polja i područja s kojima možete raditi. Polja možete povući na područja i ona će biti prikazana u zaokretnoj tablici na listu.
  • Na lijevoj strani lista nalazi se zaokretna tablica.


Sada držite polje "Proizvod" lijevom tipkom miša - povucite ga do "Imena linija", "Prodaja u rubljama". - u "Vrijednosti" u zaokretnoj tablici. Tako smo primili ukupnu prodaju robe za cijelo razdoblje:


Grupiranje i filtriranje vremenskih serija u zaokretnoj tablici

Ako želimo analizirati i usporediti prodaju proizvoda po godinama, tromjesečjima, mjesecima, danima, onda to trebamo dodajte relevantna polja u zaokretnu tablicu.

Da biste to učinili, idite na list "Podaci" i nakon datuma umetnite 3 prazna stupca. Odaberite stupac "Proizvod" i kliknite "Umetni".

Važnotako da su novo dodani stupci unutar raspona postojeće tablice s podacima, tada nećemo trebati ponoviti zaokret da bismo dodali nova polja, bit će dovoljno da ga ažuriramo.

Umetnuti stupci nazivaju se "Godina", "Mjesec", "Godina-mjesec".

Sada u svaki od ovih stupaca dodajte odgovarajuću formulu da biste dobili vremenski parametar od interesa:

  • Dodajte formulu u stupac "Godina" \u003d GODINA (s obzirom na datum);
  • Dodajte formulu u stupac "Mjesec" \u003d MJESEC (s referencom na datum);
  • Dodajte formulu u stupac "Godina - mjesec" \u003d CONCATENATE (veza do godine; ""; veza do mjeseca).

Dobivamo 3 stupca s godinom, mjesecom i godinom i mjesecom:


Sada idite na list "Sažetak", postavite kursor na zaokretnu tablicu, desnom tipkom miša kliknite izbornik i pritisnite gumb "Ažuriraj"... Nakon ažuriranja na popisu polja imamo nova polja zaokretne tablice "Godina", "Mjesec", "Godina - mjesec", koje smo dodali jednostavan stol s podacima:


Sada analizirajmo prodaju po godinama.

Da bismo to učinili, povlačimo polje "Godina" u "nazive stupaca" zaokretne tablice. Dobivamo tablicu s prodajom po proizvodima po godinama:


Sada se želimo "spustiti" još dublje na razinu mjeseci i analizirati prodaju po godinama i mjesecima. Da biste to učinili, povucite polje mjeseca ispod godine u "imena stupaca":


Analizirati dinamiku mjeseci po godinama, možemo premjestiti mjesece u zaokretno područje "Imena redaka" i dobiti slijedeći prikaz zaokretne tablice:


U ovom prikazu zaokretne tablice vidimo:

  • prodaja za svaki proizvod u iznosu za cijelu godinu (redak s nazivom proizvoda);
  • detaljnije prodaja svakog proizvoda svakog mjeseca u dinamici od 4 godine.

Sljedeći izazov, mi želimo ukloniti iz analize prodaju na mjesec dana (na primjer, listopad 2012.), jer još nemamo podataka o prodaji punih mjesec dana.
Da biste to učinili, povucite "Godina - mjesec" u pivot područje "Filtar izvješća"

Kliknite gornji filtar sažetka i stavite kvačicu "Odaberi više elemenata"... Zatim, na popisu s godinama i brojevima mjeseci poništite 2012. 10 i kliknite U redu.


Dakle, možete dodati nove parametre za promjenu vremena i analizirati ona vremenska razdoblja koja su vam zanimljiva i u obliku u kojem vam treba. Zaokretna tablica izračunavat će pokazatelje za ona polja i filtre koje ste postavili i dodati im ih kao polje interesa.

Izračunavanje Pronoza pomoću zaokretne tablice i Forecast4AC PRO

Izgradimo prodaju pomoću zaokretne tablice po proizvodima, godini i mjesecu. Također ćemo isključiti sveukupne iznose tako da oni ne budu uključeni u izračun.

Da biste isključili ukupne vrijednosti u zaokretnoj tablici, postavite pokazivač na stupac "Ukupno" i kliknite gumb "Izbriši ukupno". Ukupno nestaje iz sažetka.



i pritisnite gumb "Forecast Model Chart" u izborniku Forecast4AC PRO

Dobivamo izračun prognoze za 12 mjeseci i prekrasan graf s analizom modela prognoze (trend, sezonalnost i model) u odnosu na stvarne podatke. Forecast4AC PRO može izračunati prognoze, sezonske stope, trend i druge pokazatelje za vas i graditi grafikone na temelju podataka prikazanih u pivot tablici.


Zaokretna tablica u Excelu moćan je alat za analizu podataka koji vam omogućuje brzo i jednostavno izračunavanje mjernih podataka i izgradnju podataka u obliku koji vas zanima.



Preporučujemo čitanje