Primeri dokumentov s tabelami excel

Vrtilna tabela je morda najbolj uporabno orodje v Excelu. Z njegovo pomočjo je na voljo veliko možnosti za analizo velike količine podatkov in hitre izračune.

Kaj so vrtilne tabele v Excelu? Vrtilne tabele v Excelu za lutke

Vrtilne tabele so Excelovo orodje za povzemanje in analizo velikih količin podatkov.

Recimo, da imamo 1000-vrsticno tabelo prodaje po kupcih za to leto:

Tabela prikazuje:

Datumi naročil;
Regija, kjer je stranka;
Vrsta odjemalca;
Naročnik;
Število prodaj;
Prihodki;
Dobiček.

Zdaj pa si predstavljajmo, da je naš upravitelj določil naloge za izračun:

TOP pet strank po prihodkih;

Odgovor na ta vprašanja lahko uporabite z različnimi funkcijami in formulami. Kaj pa, če za te podatke ne obstajajo tri, ampak trideset nalog? Vsakič morate spremeniti formule in funkcije ter se prilagoditi za vsako vrsto izračuna.

To je samo v primeru, da bo orodje vrtilne tabele postalo nepogrešljiv asistent za vas. Z njegovo pomočjo lahko v skladu s podatki iz tabele v nekaj sekundah odgovorite na katero koli vprašanje.

Upam, da ste v zgornjem primeru ugotovili, kako koristna in potrebna je vrtilna tabela. Ugotovimo, kako jih uporabiti.

Kako narediti vrtilno tabelo v Excelu

Če želite v Excelu ustvariti vrtilno tabelo, sledite tem korakom:

Izberite katero koli celico v tabeli s podatki, na podlagi katerih želite izdelati vrtilno tabelo;
Kliknite zavihek "Vstavi" \u003d\u003e "Vrtilna tabela":

V pojavnem pogovornem oknu bo sistem samodejno določil meje podatkov, na podlagi katerih lahko ustvarite vrtilno tabelo. Ta metoda v večini primerov deluje privzeto. Priporočam, da vsakič, ko ustvarite vrtilno tabelo, preverite, ali je sistem pravilno določil parametre za njeno izdelavo:
- Tabela ali obseg: sistem samodejno zazna meje podatkov, da ustvari vrtilno tabelo. Pravilni bodo, če v glavah in vrsticah v tabeli ni presledkov. Področje podatkov lahko prilagodite po potrebi.
Sistem privzeto ustvari tabelo v novem zavihku datoteke Excel. Če ga želite ustvariti na določenem mestu na določenem listu, lahko v stolpcu »Na obstoječem listu« določite meje, ki jih želite ustvariti.

Kliknite »V redu«.

Ko kliknete gumb »V redu«, se ustvari vrtilna tabela.

Ko ustvarite vrtilno tabelo, na listu ne boste videli nobenih podatkov. Na voljo bo le ime vrtilne tabele in meni za izbiro podatkov za prikaz.

Preden začnemo analizirati podatke, predlagam, da razumemo, kaj pomeni vsako polje in območje vrtilne tabele.

Regije vrtilne tabele v Excelu

Za učinkovito uporabo vrtilnih tabel je pomembno temeljito vedeti, kako delujejo in iz česa so sestavljene.

Izvedite več o spodnjih področjih:

Predpomnilnik vrtilne tabele
Območje vrednot
Območje strun
Območje stolpcev
Območje filtrov

Kaj je predpomnilnik vrtilne tabele

Ko ustvarite vrtilno tabelo, Excel ustvari predpomnilnik (vmesni podatkovni vmesnik za hitri dostop, ki vsebuje informacije, za katere je najverjetneje, da bodo zahtevane) podatkov, iz katerih bo zgrajena tabela.

Ko izvajate izračune na ustvarjeni tabeli, se Excel ne sklicuje vsakič na izvirne podatke, temveč uporablja podatke iz predpomnilnika. Ta funkcija bistveno zmanjša količino sistemskih virov, porabljenih za obdelavo in računalništvo podatkov.

Pomembno je vedeti, da podatkovni predpomnilnik poveča velikost datoteke Excel.

Območje vrednot

Območje Vrednosti vsebuje vse vrednosti tabele in ga je mogoče prikazati kot glavno komponento vrtilne tabele. Predstavljajte si, da želimo prikazati prodajo regij po mesecih (iz primera na začetku članka) s pomočjo vrtilne tabele. Osenčene vrednosti rumena na spodnji sliki in so vrednosti, ki jih določimo v vrteča miza na področju Vrednote.

V zgornjem primeru smo ustvarili vrtilno tabelo, ki prikazuje podatke o prodaji po regijah, razčlenjene po mesecih.

Območje strun

Glave tabel levo od vrednosti so vrstice. V našem primeru so to imena regij. Na spodnjem posnetku zaslona so vrstice označene z rdečo:

Območje stolpcev

Naslovi na vrhu vrednosti tabele se imenujejo "Stolpci".

V spodnjem primeru so polja »Stolpci« označena z rdečo, v našem primeru so to vrednosti mesecev.

Območje filtrov

Območje "Filtri" ni obvezno in omogoča nastavitev stopnje podrobnosti podatkov vrtilne tabele. Kot filter lahko na primer določimo podatke »Vrsta kupca« - »Trgovina z živili« in Excel bo v tabeli prikazal podatke, ki zadevajo samo trgovine z živili.

Vrtilne tabele v Excelu. Primeri

V spodnjih primerih bomo preučili, kako z vrtilnimi tabelami odgovoriti na tri vprašanja z začetka tega članka:

Kolikšen je obseg prihodkov za regijo Sever v letu 2017?
TOP pet strank po prihodkih;
Kakšno je mesto odjemalca Ludnikov IP v regiji Vostok glede na prihodek?

Pred analizo podatkov se je pomembno odločiti, kako naj bodo videti podatki tabele (katere podatke označiti v stolpce, vrstice, vrednosti, filtre). Če moramo na primer prikazati podatke o prodaji strank po regijah, moramo imena regij vstaviti v vrstice, mesece v stolpce, prodajne vrednosti v polje »Vrednosti«. Ko imate idejo, kako vidite končno vrtilno tabelo, jo začnite ustvarjati.

Okno "Polja vrtilne tabele" vsebuje področja in polja z vrednostmi za umestitev v vrtilno tabelo:

Polja so ustvarjena na podlagi izvirnih podatkov, uporabljenih za vrtilno tabelo. V razdelku Območja boste postavili polja in glede na to, na katerem območju bo polje postavljeno, se vaši podatki posodobijo v vrtilni tabeli.

Premikanje polj iz območja v območje je priročen vmesnik, v katerem se med premikanjem podatki v vrtilni tabeli samodejno posodabljajo.

Zdaj pa poskusimo odgovoriti na vprašanja upravitelja z začetka tega članka s spodnjimi primeri.

Primer 1. Koliko prihodkov ima severna regija?

Za izračun obsega prodaje severne regije predlagam, da podatke o prodaji vseh regij vstavite v vrtilno tabelo. Za to potrebujemo:

ustvarite vrtilno tabelo in premaknite polje "Regija" v območje "Vrstice";
postavite polje »Prihodki« v polje »Vrednote«

Dobili bomo odgovor: prodaja regije Sever je 1 233 006 966 ₽:

Primer 2. TOP pet prodajnih kupcev

v vrtilni tabeli premaknite polje »Stranka« v območje »Vrstice«;
postavite polje »Prihodek« v območje »Vrednosti«;
nastavite obliko finančnega števila na vrtilne celice tabel z vrednostmi.

Imeli bomo naslednjo vrtilno tabelo:

Excel privzeto razvrsti podatke v tabeli po abecednem vrstnem redu. Če želite podatke razvrstiti po obsegu prodaje, sledite tem korakom:

pojdite v meni “Razvrsti” \u003d\u003e “Razvrsti padajoče”:

Kot rezultat bomo dobili razvrščen seznam kupcev po prihodkih.

Primer 3. Kakšno mesto glede na prihodek zaseda odjemalec Ludnikov IP v regiji Vostok?

Za izračun mesta glede na obseg dohodka odjemalca Ludnikov IP v regiji Vostok predlagam izdelavo vrtilne tabele, ki bo prikazala podatke o prihodkih po regijah in odjemalcih v tej regiji.

Za to:

postavite polje vrtilne tabele “Regija” v območje “Vrstice”;
postavite polje »Stranka« v polje »Strune« pod polje »Regija«;
nastavite obliko finančnega števila na celice z vrednostmi.

Takoj, ko v področje Vrstice postavite polji »Regija« in »Stranka«, bo sistem razumel, kako želite prikazati podatke, in predlagal ustrezno možnost.

polje »Prihodki« bo postavljeno v polje »Vrednosti«.

Kot rezultat smo dobili vrtilno tabelo, ki odraža podatke o prihodkih strank v posamezni regiji.

Če želite podatke razvrstiti, sledite tem korakom:

z desno tipko miške kliknite katero koli vrstico podatkov o prihodkih v vrtilni tabeli;
pojdite v meni “Razvrsti” \u003d\u003e “Razvrsti padajoče”:

V dobljeni tabeli lahko ugotovimo, kakšno mesto zaseda stranka Ludnikov IP med vsemi strankami regije Vostok.

Obstaja več možnosti za rešitev tega problema. Polje »Regija« lahko premaknete v območje »Filtri« in v vrstice postavite podatke o prodaji strank, s čimer odražajo podatke o prihodkih samo za stranke vzhodne regije.

Če še nikoli niste uporabljali preglednice za ustvarjanje dokumentov, priporočamo, da preberete naš Excel-ov vodnik za lutke.

Nato lahko ustvarite svojo prvo preglednico s tabelami, grafi, matematičnimi formulami in oblikovanjem.

Podrobne informacije o osnovnih funkcijah in zmožnostih namizni procesor MS Excel. Opis glavnih elementov dokumenta in navodila za delo z njimi v našem gradivu.

Delo s celicami. Podloga in oblikovanje

Preden nadaljujete s konkretnimi dejanji, morate razumeti osnovni element kateri koli dokument v Excelu. Datoteka Excel je sestavljena iz enega ali več listov, razdeljenih na majhne celice.

Celica je osnovni sestavni del katerega koli Excelovega poročila, preglednice ali grafa. Vsaka celica vsebuje en blok informacij. Lahko je številka, datum, valuta, merska enota ali druga oblika zapisa podatkov.

Če želite izpolniti celico, jo preprosto kliknite s kazalcem in vnesite zahtevane podatke. Če želite urediti prej napolnjeno celico, jo dvokliknite.

Slika: 1 - primer polnjenja celic

Vsaka celica na listu ima svoj edinstven naslov. Tako lahko z njim izvajate izračune ali druge operacije. Ko kliknete celico, se na vrhu okna prikaže polje z naslovom, imenom in formulo (če je celica vključena v kakršne koli izračune).

Izberimo celico "Delež delnic". Njegov lokacijski naslov je A3. Te informacije so prikazane v oknu z lastnostmi, ki se odpre. Vsebino lahko tudi vidimo. Ta celica nima formul, zato niso prikazane.

V priročnem meniju je na voljo več lastnosti celic in funkcij, ki jih je mogoče uporabiti v zvezi z njo. Kliknite celico z desno tipko manipulatorja. Odprl se bo meni, s katerim lahko formatirate celico, analizirate vsebino, dodelite drugačno vrednost in druga dejanja.

Slika: 2 - kontekstni meni celice in njene glavne lastnosti

Razvrščanje podatkov

Uporabniki se pogosto soočajo z nalogo razvrščanja podatkov na listu v Excelu. Ta funkcija vam pomaga hitro izbrati in si ogledati samo želene podatke iz celotne tabele.

Pred vami je že izpolnjena tabela (o tem, kako jo ustvarimo, bomo ugotovili kasneje v članku). Predstavljajte si, da želite podatke za januar razvrstiti po naraščajočem vrstnem redu. Kako bi to naredil? Banalno pretipkanje mize je dodatno delo, poleg tega pa je nihče ne bo storil, če je obsežna.

Excel ima posebno funkcijo za razvrščanje. Uporabnik mora le:

Izberite tabelo ali blok informacij;
Odprite zavihek "Podatki";
Kliknite ikono »Razvrsti«;

Slika: 3 - zavihek "Podatki"

V oknu, ki se odpre, izberite stolpec tabele, nad katerim bomo izvajali akcije (januar).
Nato vrsta razvrščanja (razvrščamo po vrednosti) in nazadnje vrstni red narašča.
Ukrep potrdite s klikom na »V redu«.

Slika: 4 - nastavitev parametrov razvrščanja

Podatki bodo samodejno razvrščeni:

Slika: 5 - rezultat razvrščanja številk v stolpcu "Januar"

Podobno lahko razvrščate po barvi, pisavi in \u200b\u200bdrugih parametrih.

Matematični izračuni

Glavna prednost Excela je zmožnost samodejnega izvajanja izračunov v procesu izpolnjevanja tabele. Na primer, imamo dve celici z vrednostma 2 in 17. Kako lahko vnesemo njihov rezultat v tretjo celico, ne da bi sami izvajali izračune?

Če želite to narediti, morate klikniti tretjo celico, v katero bo vnesen končni rezultat izračunov. Nato kliknite ikono funkcije f (x), kot je prikazano na spodnji sliki. V oknu, ki se odpre, izberite dejanje, ki ga želite uporabiti. SUM je vsota, AVERAGE je povprečje itd. Celoten seznam funkcije in njihova imena v urejevalniku Excel najdete na uradni spletni strani Microsofta.

Poiskati moramo vsoto dveh celic, zato kliknemo na "SUM".

Slika: 6 - izbira funkcije "SUM"

Okno argumentov funkcije ima dve polji: "Številka 1" in "Številka 2". Izberite prvo polje in kliknite celico s številko "2". Njegov naslov bo zapisan v argumentni niz. Kliknite "Številka 2" in kliknite celico s številko "17". Nato potrdite dejanje in zaprite okno. Če morate matematiko izvesti s tremi ali več polji, preprosto vnesite vrednosti argumentov v številko 3, številko 4 itd.

Če se v prihodnosti vrednost strnjenih celic spremeni, se njihova vsota samodejno posodobi.

Slika: 7 - rezultat izračunov

Ustvarjanje tabel

Vse podatke lahko shranite v Excelove tabele. S pomočjo funkcije hitre nastavitve in oblikovanja je v urejevalniku zelo enostavno organizirati osebni sistem nadzora proračuna, seznam stroškov, digitalne podatke za poročanje itd.

Tabele v Excelu imajo prednost pred podobno možnostjo v Wordu in drugih pisarniških programih. Tu imate priložnost ustvariti tabelo katere koli dimenzije. Podatki se enostavno izpolnijo. Obstaja funkcijska plošča za urejanje vsebine. Poleg tega pripravljena miza je mogoče integrirati v datoteko docx z običajno funkcijo copy-paste.

Če želite ustvariti tabelo, sledite navodilom:

Kliknite zavihek Vstavi. Na levi strani podokna z možnostmi izberite Tabela. Če morate povzeti katere koli podatke, izberite postavko "Vrtilna tabela";
Z miško izberite mesto na listu, ki bo dodeljeno za tabelo. Prav tako lahko vnesete lokacijo podatkov v okno za ustvarjanje elementov;
Kliknite V redu, da potrdite dejanje.

Slika: 8 - izdelava standardne tabele

Za formatiranje videz nastale plošče odprite vsebino konstruktorja in v polju "Slog" kliknite predlogo, ki vam je všeč. Po želji lahko ustvarite svoj videz z drugačno barvno shemo in izbiro celic.

Slika: 9 - oblikovanje tabele

Rezultat izpolnjevanja tabele s podatki:

Slika: 10 - izpolnjena tabela

Za vsako celico v tabeli lahko prilagodite tudi vrsto podatkov, oblikovanje in način prikaza informacij. Okno oblikovalca vsebuje vse potrebne možnosti za nadaljnjo konfiguracijo plošče glede na vaše zahteve.

Dodajanje grafov / grafikonov

Za izdelavo diagrama ali grafa je potrebna že pripravljena plošča, ker bodo grafični podatki temeljili natančno na podatkih, ki so bili vzeti iz posameznih vrstic ali celic.

Če želite ustvariti grafikon / graf, potrebujete:

V celoti izberite tabelo. Če morate ustvariti grafični element samo za prikaz podatkov določene celice, izberite samo njih;
Odprite zavihek za vstavljanje;
V polju s priporočenimi grafikoni izberite ikono, za katero menite, da bo najbolje prikazala tabelarne informacije. V našem primeru je to tridimenzionalni tortni grafikon. Premaknite kazalec na ikono in izberite videz elementa;

Podobno lahko ustvarite razpršene ploskve, vrstne diagrame in odvisnosti elementov tabele. Vse prejete grafične elemente lahko tudi dodate besedilni dokumenti Beseda.

V urejevalniku preglednic Excel je še veliko drugih funkcij, vendar bodo tehnike, opisane v tem članku, zadostovale za začetno delo. V procesu ustvarjanja dokumenta mnogi uporabniki samostojno obvladajo naprednejše možnosti. To je posledica uporabniku prijaznega in intuitivnega vmesnika. najnovejše različice programov.

Tematski videoposnetki:

Vrtilna tabela v Excelu je zmogljivo orodje za analizo podatkov, ki vam bo hitro pomagalo:

Pripravite podatke za poročila;
Izračunajte različne kazalnike;
Podatki o skupinah;
Filtrirajte in analizirajte meritve, ki vas zanimajo.

In tudi prihranite na tone časa.

V tem članku boste izvedeli:

Kako narediti vrtilno tabelo;
Kako uporabljati vrtilno tabelo skupinske časovne vrste in ocenite podatke v dinamiki po letih, četrtletjih, mesecih, dneh ...
Kako izračunati napoved s pomočjo vrtilne tabele in Forecast4AC PRO;

Najprej se naučimo, kako narediti vrtilne tabele.

Za izdelavo vrtilne tabele moramo podatke zgraditi v obliki preproste tabele. Vsak stolpec mora imeti 1 razčlenjen parameter. Na primer, imamo 3 stolpce:

Izdelka
Prodaja v rubljih

In v vsako vrstico 3. parametri so povezani, tj. na primer 02.01.2010, izdelek 1 je bil prodan za 422 656 rubljev.

Ko pripravite podatke za vrtilno tabelo, nastavite kazalko v prvi stolpec v prvo celico preproste tabele, nato pojdite v meni "Vstavi" in pritisnite gumb "Pivot table"

Pojavilo se bo pogovorno okno, v katerem:

ti lahko takoj pritisnite gumb "OK", vrtilna tabela pa bo prikazana na ločenem listu.
ali pa lahko konfigurirate parametre izpisa podatkov vrtilne tabele:

Obseg s podatki, ki bodo prikazani v vrtilni tabeli;
Kje prikazati vrtilno tabelo (na nov list ali na obstoječega (če izberete obstoječega, boste morali določiti celico, v katero želite postaviti vrtilno tabelo)).

Kliknite »V redu«, vrtilna tabela je pripravljena in prikazana na novem listu. Pokličimo list Pivot.

Na desni strani lista boste videli polja in področja, s katerimi lahko delate. Polja lahko povlečete na območja in bodo prikazana v vrtilni tabeli na listu.
Na levi strani lista je vrtilna tabela.

Zdaj pridržite polje "Izdelek" z levim gumbom miške - povlecite ga v polje "Imena linij", "Prodaja v rubljih". - v "Vrednosti" v vrtilni tabeli. Tako smo prejeli skupno prodajo blaga po celotnem obdobju:

Združevanje in filtriranje časovnih vrst v vrtilni tabeli

Če želimo zdaj analizirati in primerjati prodajo izdelkov po letih, četrtletjih, mesecih, dneh, potem to potrebujemo dodajte ustrezna polja v vrtilno tabelo.

Če želite to narediti, pojdite na list "Podatki" in za datumom vstavite 3 prazne stolpce. Izberite stolpec »Izdelek« in kliknite »Vstavi«.

Pomembnotako da so na novo dodani stolpci znotraj obsega obstoječe tabele s podatki, nam za dodajanje novih polj ne bo treba ponoviti vrtišča, dovolj bo, da ga posodobimo.

Vstavljeni stolpci se imenujejo "Leto", "Mesec", "Leto-mesec".

Zdaj v vsakem od teh stolpcev dodajte ustrezno formulo, da dobite časovni parameter, ki nas zanima:

Formulo dodajte v stolpec "Leto" \u003d LETO (s sklicevanjem na datum);
Formulo dodajte v stolpec "Mesec" \u003d MESEC (s sklicevanjem na datum);
Formulo dodajte v stolpec "Leto - mesec" \u003d CONCATENATE (povezava do leta; ""; povezava do meseca).

Dobimo 3 stolpce z letom, mesecem in letom ter mesecem:

Zdaj pojdite na list »Povzetek«, postavite kazalec na vrtilno tabelo, z desno miškino tipko kliknite meni in pritisnite gumb "Posodobi"... Po posodobitvi na seznamu polj smo nova polja vrtilne tabele "Leto", "Mesec", "Leto - mesec", ki smo jih dodali preprosta miza s podatki:

Zdaj pa analizirajmo prodajo po letih.

Če želite to narediti, povlecite polje »Leto« v »imena stolpcev« vrtilne tabele. Dobimo tabelo s prodajo po izdelkih po letih:

Zdaj se želimo "spustiti" še globlje na raven mesecev in analizirati prodajo po letih in mesecih. Če želite to narediti, povlecite polje »mesec« pod letom v »imena stolpcev«:

Analizirati dinamiko mesecev po letih, mesece lahko premaknemo v vrtišče "Imena vrstic" in dobimo naslednji pogled vrtilne tabele:

V tem pogledu vrtilne tabele vidimo:

prodaja za vsak izdelek v znesku za celo leto (vrstica z imenom izdelka);
podrobneje prodaja za vsak izdelek v vsakem mesecu v dinamiki za 4 leta.

Naslednji izziv smo iz analize želimo odstraniti prodajo za en mesec (na primer oktober 2012), ker zaenkrat še nimamo podatkov o prodaji.
Če želite to narediti, povlecite "Leto - mesec" v vrtišče "Filter poročila"

Kliknite filter povzetka, ki se prikaže zgoraj in potrdite polje "Izberi več elementov"... Nato na seznamu z leti in števili mesecev počistite polje 2012 10 in kliknite V redu.

Tako lahko dodate nove parametre za spreminjanje časa in analizirate tiste časovne intervale, ki so vam zanimivi in \u200b\u200bv obliki, v kateri jo potrebujete. Vrtilna tabela bo izračunala kazalnike za tista polja in filtre, ki ste jih nastavili, in jih dodala kot zanimivo polje.

Izračun Pronoza z uporabo vrtilne tabele in napovedi 4AC PRO

Gradimo prodajo z uporabo vrtilne tabele glede na izdelek, po letu in mesecu. Izklopili bomo tudi skupne vsote, tako da ne bodo vključene v izračun.

Če želite izklopiti vsote v vrtilni tabeli, postavite kazalko na stolpec "Skupna vsota" in kliknite gumb "Izbriši skupno vsoto". Skupno število izgine iz povzetka.

in pritisnite gumb "Forecast Model Chart" v meniju Forecast4AC PRO

Dobimo izračun napovedi za 12 mesecev in čudovit graf z analizo modela napovedi (trend, sezonskost in model) glede na dejanske podatke. Forecast4AC PRO lahko za vas izračuna napovedi, sezonske stopnje, trend in druge kazalnike ter gradi grafe na podlagi podatkov, prikazanih v vrtilni tabeli.

Vrtilna tabela v Excelu je zmogljivo orodje za analizo podatkov, ki vam omogoča hitro izračunavanje meritev in hitro in enostavno ustvarjanje podatkov v obliki, ki vas zanima.