Prenesite vrtilno tabelo v Excelu

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

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 vrteča miza ;
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 vsebovati 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 prilagodite izhodne parametre podatkov vrtilne tabele:

Obseg s podatki, ki bodo prikazani v vrtilni tabeli;
Kje prikazati vrtilno tabelo (na nov list ali na obstoječega (če se odločite za 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 območja, s katerimi lahko delate. Polja lahko povlečete v 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 do polja "Imena linij", polja "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 kazalko na vrtilno tabelo, z desno miškino tipko kliknite meni in pritisnite gumb "Posodobi"... Po posodobitvi na seznamu polj imamo 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« do »imen 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 postavite kljukico "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 naredite analizo tistih časovnih intervalov, 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 "Vsota" in kliknite gumb "Izbriši vsoto". Skupno število izgine iz povzetka.

in v meniju Forecast4AC PRO kliknite gumb "Chart Model Chart"

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 in enostavno izračunavanje meritev in risanje podatkov v obliki, ki vas zanima.

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

Po tem 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 osnovna komponenta katerega koli Excelovega poročila, tabele ali grafa. Vsaka celica vsebuje en blok informacij. Lahko je številka, datum, znesek valute, 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.

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

Sl. 2 - kontekstni meni celice in njene glavne lastnosti

Razvrščanje podatkov

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

Pred vami je 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 namensko funkcijo za razvrščanje. Uporabnik mora le:

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

Sl. 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«.

Sl. 4 - nastavitev parametrov razvrščanja

Podatki bodo samodejno razvrščeni:

Sl. 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 vnesti njihov rezultat v tretjo celico, ne da bi sami opravili izračune?

Če želite to narediti, morate klikniti tretjo celico, v katero bo vpisan 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".

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

Sl. 7 - rezultat izračunov

Ustvarjanje tabel

Vse podatke lahko shranite v Excelove tabele. S pomočjo funkcije hitre konfiguracije 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 podobnimi možnostmi 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 plošče z možnostmi izberite Tabela. Če morate povzeti katere koli podatke, izberite element "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.

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

Sl. 9 - formatiranje tabele

Rezultat izpolnjevanja tabele s podatki:

Sl. 10 - izpolnjena tabela

Za vsako celico v tabeli lahko prilagodite tudi vrsto podatkov, oblikovanje in način prikaza informacij. Okno konstruktorja 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 je treba grafični element ustvariti 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 nastale grafične elemente lahko tudi dodate besedilni dokumenti Beseda.

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

Tematski videoposnetki:

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 na podlagi teh podatkov ne obstajajo tri, ampak trideset nalog? Vsakič morate spremeniti formule in funkcije ter se prilagoditi za vsako vrsto izračuna.

Ravno v tem primeru bo orodje vrtilne tabele postalo nepogrešljiv asistent za vas. Z njegovo pomočjo boste v nekaj sekundah lahko odgovorili na katero koli vprašanje, glede na podatke iz tabele.

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 datoteka 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 vmesni pomnilnik s hitrim dostopom, 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 je lahko prikazano kot glavni sestavni del vrtilne tabele. Predstavljajmo 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 vrtilni tabeli v območju "Vrednosti".

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" se uporablja neobvezno 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 na začetku 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, ki jih je treba postaviti 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 uvrstimo 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 kupcev po prodaji

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 je mesto glede prihodkov stranke Ludnikov IE v regiji East?

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“ eno pod drugo, bo sistem razumel, kako želite prikazati podatke, in predlagal ustrezno možnost.

postavite polje »Prihodek« v območje »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.