Microsoft Excel: Pivot Tables
Zbirne tabele v Excelu uporabnikom omogočajo, da na enem mestu združijo precejšnjo količino informacij, ki jih vsebujejo okorne tabele, in zbirajo zapletena poročila. V tem primeru se vrednosti zbirnih tabel samodejno posodobijo, ko se spremeni vrednost katere koli povezane tabele. Poglejmo, kako ustvariti pivot tabelo v Microsoft Excelu.
Vsebina
Ustvari pivot tabelo na običajen način
Čeprav bomo preučili proces ustvarjanja vrtilne tabele z uporabo primera Microsoft Excel 2010, vendar ta algoritem velja za druge sodobne različice te aplikacije.
Za osnovo bomo vzeli mizo plačil delavcem v podjetju. Navede imena zaposlenih, spol, kategorijo, datum plačila in znesek plačila. To pomeni, da vsaka epizoda plačila posameznemu zaposlenemu ustreza ločeni vrstici tabele. V tej tabeli moramo združiti kaotično locirane podatke v eno zbirno tabelo. V tem primeru se bodo podatki uporabljali šele v tretjem četrtletju leta 2016. Poglejmo, kako to narediti na konkretnem primeru.
Najprej pretvorimo prvotno tabelo v dinamično tabelo. To je potrebno, da če dodate vrstice in druge podatke, se samodejno vnesejo v vrtilno tabelo. Če želite to narediti, postanemo kazalec na kateri koli celici tabele. Nato v bloku "Slogi" na traku kliknite gumb "Oblika kot miza". Izberite kateri koli slog, ki vam je všeč.
Nato se odpre pogovorno okno, ki nas pozove, da določimo koordinate lokacije tabel. Vendar pa privzeto koordinate, ki jih program ponuja in tako pokriva celotno tabelo. Zato se lahko strinjamo in kliknemo na gumb »V redu«. Toda uporabniki bi morali vedeti, da lahko, če hočejo, spremenijo parametre pokrivanja območja tabele.
Po tem se tabela pretvori v dinamično in avtoranging. Prav tako dobi ime, ki ga lahko po želji uporabnik spremeni v vse, kar je primerno za njega. Ime tabele si lahko ogledate ali spremenite na zavihku »Dizajner«.
Če želite neposredno ustvariti vrtilno tabelo, pojdite na zavihek "Vstavi". Kliknite na prvi gumb v traku, ki se imenuje "Pivot Table". Po tem se odpre meni, v katerem bi morali izbrati, kaj bomo ustvarili, tabelo ali grafikon. Kliknite gumb »Zbirna tabela«.
Odpre se okno, v katerem ponovno izberemo obseg ali ime tabele. Kot lahko vidite, je program že zaostril ime naše mize, zato ni nič drugega. Na dnu pogovornega okna lahko izberete, kje želite ustvariti tabelo s povzetkom: na novem listu (privzeto) ali na istem listu. Seveda je v večini primerov veliko bolj priročno uporabiti zbirno tabelo na posebnem listu. Toda to je posamezna zadeva za vsakega uporabnika, kar je odvisno od njegovih nastavitev in nalog. Samo kliknite na gumb »V redu«.
Po tem se na novem listu odpre oblika oblikovanja vrtilne tabele.
Kot lahko vidite, v desnem delu okna je seznam polj tabel in pod štirimi področji:
- Imena vrstic;
- Imena stolpcev;
- Vrednosti;
- Poročilo o filtrih.
Preprosto povlečemo z miško polja tabele, ki jih potrebujemo, na področjih, ki ustrezajo našim potrebam. Ne obstaja jasno pravilo o tem, za katera polja se premika, ker je vse odvisno od izvorne tabele in posebnih nalog, ki se lahko spremenijo.
Torej smo v tem primeru premaknili polja »Spol« in »Datum« na področje »Filter za poročila«, polje »Kadrovska kategorija« v polju »Imena stolpcev«, polje »Ime« v polju »Vrste vrstic«, polje »Količina« plače "na območje" Vrednosti ". Treba je opozoriti, da so vsi aritmetični izračuni podatkov, pridobljenih iz druge tabele, možni samo na zadnjem področju. Kot lahko vidite, smo v času, ko smo izvedli te manipulacije s prenosom polj v območju, tudi tabela v levem delu okna prav tako spremenila.
To je zbirna tabela. Nad tabelo so prikazani filtri po spolu in datumu.
Konfiguriranje vrtilne tabele
Toda, kot se spominjamo, bi morali v tabeli ostati podatki le za tretje četrtletje. Medtem se prikažejo podatki za celotno obdobje. Če želite tabelo dati na želeno obliko, kliknite gumb ob filteru »Datum«. V pojavnem oknu potrdite polje na sredini napisa "Izberi več elementov". Nato odstranite klop iz vseh datumov, ki se ne ujemajo v tretjem četrtletju. V našem primeru je to samo en datum. Kliknite gumb "V redu".
Na enak način lahko uporabimo filter na tleh in na poročilu izberemo le nekatere moške.
Po tem je zbirna tabela pridobila ta obrazec.
Če želite dokazati, da lahko podatke upravljate v tabeli, kot želite, ponovno odpremo obliko seznama polj. Če želite to narediti, pojdite na kartico »Parametri« in kliknite gumb »Seznam polj«. Potem premaknite polje »Datum« iz območja »Filtri poročila« na področje »Ime vrstice« in med področjema »Osebje« in »Spol«, izmenjujemo območja. Vse operacije se izvajajo tako, da preprosto povlečete elemente.
Zdaj ima miza povsem drugačen videz. Stolpci so razdeljeni po spolu, vrstice so bile razčlenjene po mesecih, zdaj pa lahko tabelo filtrira kategorija zaposlenih.
Če v seznamu polj premaknete ime vrstic in določite datum, ki je višji od imena, bodo datumi plačila razdeljeni na imena zaposlenih.
Tudi številske vrednosti tabele lahko prikažete kot histogram. Če želite to narediti, izberite celico s številčno vrednostjo v tabeli, pojdite na zavihek "Domov", kliknite gumb "Pogojno oblikovanje", pojdite na postavko "Histogram" in izberite vrsto histograma, ki vam je všeč.
Kot vidite, se histogram pojavlja samo v eni celici. Da bi pravilo histograma uporabili za vse celice v tabeli, kliknite na gumb, ki se je pojavil ob histogramu, in v odprtem oknu preklopimo stikalo na položaj »Za vse celice«.
Zdaj je naša zbirna tabela pridobila predstavljiv videz.
Ustvarite pivot tabelo s čarovnikom za vrtilne tabele
PivotTable lahko ustvarite s čarovnikom za vrtilne tabele. Toda za to morate samo prinesti orodje v orodno vrstico za hiter dostop. Pojdite na postavko "File" in kliknite gumb "Možnosti".
V oknu odprtih parametrov pojdite na »Orodna vrstica za hiter dostop«. Izberite ukaze iz ukazov na traku. V seznamu predmetov poiščite »Čarovnik za vrtilne tabele in čarovnika za pike«. Izberite to, kliknite gumb "Dodaj" in nato kliknite gumb "V redu" v spodnjem desnem kotu okna.
Kot lahko vidite, smo po naših dejstvih na orodni vrstici za hitri dostop prikazali novo ikono. Kliknemo nanj.
Po tem se odpre čarovnik za vrtilno tabelo. Kot lahko vidite, imamo štiri različice vira podatkov, iz katerih bo oblikovana zbirna tabela:
- na seznamu ali v zbirki podatkov Microsoft Excel;
- v zunanjem viru podatkov (druga datoteka);
- v več razponih konsolidacije;
- v drugi zbirni tabeli ali v povzetku.
Spodaj izberite, kaj bomo ustvarili, tabelo s povzetki ali grafikon. Naredite izbiro in kliknite gumb "Naprej".
Po tem se pojavi okno z obsegom tabele s podatki, ki jih lahko spremenite, če želite, vendar tega ne potrebujemo. Samo kliknite na gumb "Naprej".
Nato vam čarovnik za vrtilne tabele izbere kraj, kjer bo nova tabela postavljena na isti list ali na novo. Naredite izbiro in kliknite na gumb »Done«.
Po tem se odpre nova lista s popolnoma enakim oblikom kot običajen način ustvarjanja vrtilne mize. Zato ni nobenega smisla ustaviti na njem ločeno.
Vsi nadaljnji ukrepi se izvajajo v skladu z istim algoritmom, kot je opisano zgoraj.
Kot lahko vidite, lahko v Microsoft Excelu ustvarite pivotno tabelo na dva načina: na običajen način s pomočjo gumba na traku in s čarovnikom za vrtilno tabelo. Druga metoda zagotavlja več dodatnih možnosti, vendar v večini primerov funkcionalnost prve možnosti zadošča za izpolnitev nalog. Zbirne tabele lahko v poročilih ustvarijo podatke o skoraj vseh merilih, ki jih bo uporabnik določil v nastavitvah.