Elementi vrtilne tabele. Kaj bomo storili s prejetim gradivom. Prikažite in skrijete podrobne podatke

Glavni elementi vrtilnih tabel. Pred nadaljevanjem tega dela mislim, da ne bi bilo odveč povedati, iz katerih elementov so sestavljene vrtilne tabele, zato si oglejte primer na sliki 2. Polje strani je polje na prvotnem seznamu ali tabeli, ki je postavljeno v območje orientacije strani vrteča miza.

V tem primeru je obseg rob strani, ki ga lahko uporabimo za povzetek po regijah. Ko določite drug element polja strani, se vrtilna tabela preračuna, da prikaže vsote, povezane s tem elementom. Vrednost polja strani - elementi polja strani združujejo zapise ali vrednosti polja ali stolpca na prvotnem seznamu tabele. Z drugimi besedami, to je s preklopom tega polja, pravzaprav vsakič, ko pride do prehoda na nov vir podatkov za novo regijo.

Tako lahko na primer določite oblikovanje za več ravni podatkov, v vrtilni tabeli je več nivojev vmesnih seštevkov itd. Poleg tega lahko tako kot slogi tabel ustvarite lastne sloge, ki ustrezajo vašim posebnim potrebam, ne glede na to, ali gre za korporativne smernice ali posamezne nastavitve. Vrtilne tabele pa so bolj zapletene kot tabele, zato je na voljo več elementov tabel za določanje oblikovanja.

Določite lahko na primer oblikovanje za več ravni vmesnih seštevkov, v vrtilni tabeli lahko določite izmene na različnih ravneh. Izpeljane lastnosti - se lahko uporabljajo za dodajanje novih atributov prvotnemu naboru podatkov, ki izhaja iz obstoječih. Njegova vrednost je lahko ključ lastnosti ali ime ustvarjenega atributa. Če je nastavljeno na številko, bodo atributi prikazani navpično, če kombinirana dolžina imen atributov v znakih presega to številko.

  • Samo za nespremenljivo vrtilno tabelo.
  • Samo za vrtilno tabelo, ki jo je mogoče urejati.
  • Če je število vnosov preseženo, se prikaže ustrezno sporočilo.
Poročilo vrtilne tabele lahko uporabite za povzetek, analizo, raziskovanje in predstavitev povzetkov podatkov.

Polja vrstic so polja na prvotnem seznamu ali tabeli, ki so bila umeščena v območje vrstic vrtilne tabele. V tem primeru so Products in Vendor polja nizov. Notranja polja vrstic, na primer prodajalec, se natančno ujemajo s polji zunanjih vrstic podatkovnega območja, na primer notranja polja skupine izdelkov. Tako je tu izsledeno določeno pravilo, vsak element polja višje ravni, tako rekoč, izpopolni vsa polja nižje ravni, parameter izdelka omejuje vse podatke pod njim - prodajalci izdelkov, podatki o prodaji izdelkov teh prodajalcev. Polje stolpca je polje na izvornem seznamu ali tabeli, ki je postavljeno v območje stolpca.

Če imate drugačno različico, je lahko vaš videz nekoliko drugačen, če pa ni drugače navedeno, je funkcionalnost enaka. Vrtilna tabela je interaktiven način za hiter povzetek velikih količin podatkov. Z vrtilno tabelo lahko podrobno analizirate številčne podatke in odgovorite na nepričakovana vprašanja o svojih podatkih. Vrtilna tabela je posebej zasnovana za.

Podtipirajte in združujte numerične podatke, povzemajte podatke po kategorijah in podkategorijah ter ustvarjajte izračune in formule po meri. Razširite in strnite podatkovne plasti, da osredotočite svoje rezultate in podrobnosti iz povzetih podatkov razvrstite po zanimivih področjih. Premaknite vrstice v stolpce ali stolpce v vrstice, da si ogledate različne povzetke izvornih podatkov. Filtriranje, razvrščanje, razvrščanje v skupine in pogojno oblikovanje najbolj uporabna in zanimiva podskupina podatkov, ki vam omogoča, da se osredotočite samo na informacije, ki jih želite. Predstavljamo jedrnata, privlačna in opomnjena spletna poročila ali natisnjena poročila. Poizvedujte po velikih količinah podatkov na številne uporabniku prijazne načine. ... Tukaj je na primer preprost seznam gospodinjskih odhodkov na levi in \u200b\u200bvrtilna tabela, ki temelji na seznamu na desni.

V tem primeru so četrtine polja stolpcev, ki vključujejo dva elementa polja KB2 in KB3. Notranja polja stolpcev vsebujejo elemente, ustrezna podatkovna področja so zunanja polja stolpcev nad notranjimi polji. Primer prikazuje samo eno polje stolpca.

Podatkovno območje je del vrtilne tabele, ki vsebuje vsote. Celice podatkovnega območja prikazujejo vsote za postavke polj vrstic ali stolpcev. Vrednosti v vsaki celici podatkovnega območja ustrezajo prvotnim podatkom. V tem primeru celica C6 povzema vse zapise surovih podatkov, ki vsebujejo isto ime izdelka, distributerja in določeno četrtino Meat, Myastorg LLP in KB2. Elementi polj so podkategorije polja vrtilne tabele. V tem primeru so vrednosti Meso in morski sadeži postavke polja v polju Izdelki.

Načini dela s pivot tabelo. Ko ustvarite vrtilno tabelo, izberete njen vir podatkov, uredite polja na seznamu polj vrtilne tabele in izberete začetno postavitev, lahko med delom s poročilom vrtilne tabele izvedete naslednja opravila. Preglejte podatke, tako da sledite tem korakom.

Razširite in strnite podatke ter prikažite osnovne informacije, povezane z vrednostmi.

  • Razvrščanje, filtriranje in združevanje polj in elementov.
  • Spremenite funkcije povzetka in dodajte izračune in formule po meri.
Spremenite postavitev obrazca in postavitev polj, tako da sledite tem korakom.

Elementi polj predstavljajo zapise v polju ali stolpcu v izvornih podatkih. Elementi polj so prikazani kot naslovi vrstic ali stolpcev in na spustnem seznamu za polja strani. Podatkovno polje je polje na seznamu virov ali tabeli, ki vsebuje podatke. V tem primeru je polje Količina naročila podatkovno polje, ki v polju ali stolpcu povzema prvotne podatke. Podatkovno polje ponavadi povzema skupino števil, kot so statistični podatki ali prodajne količine, čeprav so trenutni podatki lahko tudi besedilni.

  • Dodajte, spremenite in odstranite polja.
  • Spremenite vrstni red polj ali elementov.
Po teh korakih spremenite razporeditev stolpcev, vrstic in vmesnih seštevkov. Vklopite ali izklopite naslove stolpcev ali vrstic ali prikažite ali skrijte prazne vrstice.
  • Pokaži vmesne seštevke nad njihovimi vrsticami ali pod njimi.
  • Pri osvežitvi prilagodite širino stolpcev.
  • Premaknite polje stolpca na območje vrstice ali polje vrstice na območje stolpca.
  • Kombinirajte ali razširite celice za zunanje vrstice in stolpce.
Z upoštevanjem teh korakov spremenite prikaz presledkov in napak.

Vrtilna tabela privzeto povzema besedilne podatke s pomočjo funkcije Povzetek skupnih vrednosti in številske podatke s pomočjo funkcije Povzetek vsote. Spreminjanje strukture vrtilne tabele Videz Vrtilno tabelo lahko uredite neposredno na listu tako, da povlečete imena poljskih gumbov ali elementov polja. Spremenite lahko tudi vrstni red elementov v polju, za katerega morate izbrati ime elementa, nato pa kazalec nastavite na obrobo celice.

  • Spremenite način prikaza napak in praznih celic.
  • Spremenite način prikaza elementov in nalepk brez podatkov.
Spremenite obliko, tako da sledite tem korakom.
  • Ročno in pogojno formatirajte celice in obsege.
  • Spremenite splošni slog oblike vrtilne tabele.
  • Spremenite obliko števil za polja.
Spremenite lahko tudi vrsto grafikona in druge parametre, kot so naslovi, umestitev legende, oznake podatkov, položaj grafikona in še več. Vendar obstajajo nekatere razlike. Vendar pa se vrstice trendov, oznake podatkov, vrstice napak in druge spremembe naborov podatkov ne shranijo.

Ko se kazalec spremeni v puščico, povlecite celico polja na novo mesto. Če želite odstraniti polje, povlecite gumb za polje zunaj območja s podrobnostmi. Če izbrišete polje, se v vrtilni tabeli skrijejo vse odvisne vrednosti, vendar ne bo vplivalo na prvotne podatke. Če morate uporabiti vsa predvidena orodja za strukturiranje vrtilne tabele ali če trenutna tabela prej ni vključevala vseh polj izvornih podatkov, uporabite čarovnika vrtilne tabele.

Standardni grafikoni tega oblikovanja po njegovi uporabi ne izgubijo. Vsaka celica naslednjih vrstic mora vsebovati podatke, ki ustrezajo glavi stolpca, in ne smete mešati podatkovnih vrst v istem stolpcu. V istem stolpcu na primer ne smete mešati vrednosti valut in datumov. Poleg tega znotraj obsega podatkov ne sme biti praznih vrstic ali stolpcev. Če se imenovani obseg razširi na več podatkov, bo posodobitev vrtilne tabele vključevala nove podatke. Če izvorni podatki vsebujejo samodejne vmesne seštevke in skupne vsote, ki ste jih ustvarili z ukazom Vsote v skupini Oris na zavihku Podatki, z istim ukazom izbrišite vmesne vsote in skupne vsote, preden ustvarite vrtilno tabelo.

Če vrtilna tabela vsebuje veliko skupino polj strani, jih je mogoče postaviti v vrstice ali stolpce. Poleg tega je treba opozoriti, da spreminjanje strukture vrtilne tabele ne vpliva na prvotne podatke.

Konec dela -

Ta tema spada v razdelek:

Obdelava tabelarnih informacij s pomočjo vrtilnih tabel s programom Microsoft Excel

Z zamenjavo vrstic in stolpcev lahko s prikazom ustvarite nove vsote prvotnih podatkov različnih straneh podatke lahko filtrirate in ... Z drugimi besedami, te tabele omogočajo kombiniranje podatkov iz različnih virov, ... Ustvarjanje vrtilne tabele Preden ustvarite vrtilno tabelo, morate najprej nastaviti podatke za to tabelo, .. .

Podatke lahko povlečete iz zunanjega vira podatkov, kot je baza podatkov, spletna analitična kocka ali besedilno datoteko... Lahko na primer shranite bazo podatkov o prodajnih evidencah, ki jih želite povzeti in analizirati. Glejte Pretvarjanje elementov vrtilne tabele v formule delovnih listov. Na primer podatki iz relacijskih baz podatkov ali besedilnih datotek.

Uporaba druge vrtilne tabele kot vira podatkov

Vsaka nova vrtilna tabela zahteva dodaten pomnilnik in prostor na disku. Ko pa obstoječo vrtilno tabelo uporabljate kot vir za novo v istem delovnem zvezku, oba uporabljata isti predpomnilnik. Ko znova uporabljate predpomnilnik, se velikost delovnega zvezka zmanjša in v pomnilnik se shrani manj podatkov. Lokacijske zahteve Če želite eno vrtilno tabelo uporabiti kot vir za drugo, morata biti obe v isti delovni zvezki. Če je izvirna vrtilna tabela v drugem delovnem zvezku, kopirajte vir na mesto delovnega zvezka, kjer želite, da se prikaže nova.

Če potrebujete dodatno gradivo o tej temi ali niste našli tistega, kar ste iskali, priporočamo, da uporabite iskanje v naši delovni bazi:

Kaj bomo storili s prejetim gradivom:

Če se je to gradivo izkazalo za koristno za vas, ga lahko shranite na svojo stran v družabnih omrežjih:

Vse teme v tem oddelku:

Ustvarjanje vrtilne tabele
Ustvarite vrtilno tabelo. Preden ustvarite vrtilno tabelo, morate najprej nastaviti podatke za to tabelo tako, da izberete del knjige, strani in če gre za bazo podatkov ali seznam, potem n

Spremembe vplivajo na vrtilne tabele. Ko člane združite ali razvejate ali ustvarite izračunana polja ali izračunane člane v enem, to vpliva na oba. Če želite vrtilno tabelo, ki je neodvisna od druge, lahko ustvarite novo na podlagi prvotnega vira podatkov, namesto da kopirate prvotno vrtilno tabelo.

Spreminjanje izvornih podatkov obstoječega poročila vrtilne tabele

Samo zavedajte se morebitnih težav s spominom, ki so s tem povezane prepogosto. Spremembe izvornih podatkov lahko povzročijo različne podatke, ki so na voljo za analizo. Na primer, lahko preprosto preklopite s testne baze na produkcijsko bazo podatkov.

Podrobnosti in dejanja vrtilne tabele
Podrobnosti vrtilne tabele in dejanja, izvedena na njej. Podrobni podatki so podkategorija vrtilne tabele. Ti elementi vrtilne tabele so unikatni elementi tabele ali z

Prikaz ali skrivanje podrobnih podatkov vrtilne tabele
Prikaz ali skrivanje podrobnih podatkov vrtilne tabele. Če želite prikazati podrobne podatke, ki so bili prej skriti, ali skriti obstoječe podatke, morate izbrati element polja, podrobni podatki

Prikaže nove podatke, ki jih je povzročila posodobitev. Osvežitev vrtilne tabele lahko spremeni tudi podatke, ki so na voljo za prikaz. Ogledate si lahko vsa nova polja na seznamu polj in jih dodate v poročilo. Težava je v tem, da informacije o začetku ne razlikujejo med dvema letoma, ampak preprosto obstaja polje z datumom. Rešili ga bomo na dva načina.

Najprej bomo uporabili matrične formule, drugič pa bomo to storili tako, da bomo ustvarili element, izračunan v vrtilni tabeli. Lahko preverite prispevek, o katerem smo se pogovarjali. Začnemo z majhno bazo podatkov z dvema stolpcema.

  • Skupaj 24 zapisov Znesek.
  • To je vrednost neke ekonomske vrednosti.
  • To je lahko na primer obračun podjetja.
Ločljivost z matričnimi formulami.

Pokažite ali skrijte elemente polj vrtilne tabele
Prikaži ali skrij elemente polj vrtilne tabele. Če morate element polja prikazati ali skriti, morate za to najprej izbrati polje, v katerem želite prikazati oz.

V polju vrtilne tabele prikaže največ ali najmanj elementov
Prikaže največ ali najmanj elementov v polju vrtilne tabele. Za prikaz največjega ali najmanjšega števila elementov morate izbrati polje, v katerem bodo elementi prikazani v prihodnosti.

V istem listu 1 bomo svoj primer rešili z uporabo matrične funkcije. Če želite izvedeti, kako delati s takimi funkcijami, si oglejte naslednjo objavo. Formula je v skodranih oklepajih, kar pomeni, da gre za matrično formulo. Kopira se in dobi odstotek variacije za vsak mesec.

Dodali smo stolpce za leto in mesec. Dodajanje novih polj v bazo podatkov, ki se izračunajo z informacijami, ki jih že vsebuje sama baza podatkov, ni zelo pravoverno. Te formule, ki veljajo za veljaven datum, nam natančno določajo mesec in leto tega datuma.

Onemogočite dostop do podrobnosti vrtilne tabele
Onemogočite dostop do podrobnosti vrtilne tabele. Če postavite kazalec nad celico v podatkovnem območju vrtilne tabele in dvokliknete, se prikaže zbirni seznam izvornih podatkov

Združevanje in razvrščanje podatkov v vrtilno tabelo
Združevanje in razvrščanje podatkov v vrtilno tabelo. V vrtilni tabeli lahko razvrstite datume, ure, številke in izbrane elemente. Na primer, lahko kombinirate mesece, da povzamete sq.

Zato smo potrebovali veljaven datum v stolpcu Datum in morali smo vzeti prvi dan vsakega od zadevnih mesecev. Veljavni datum bi moral navajati dan, mesec in leto, potem pa v obliki, ki jo damo, vprašamo samo mesec in leto. Veljajo, ker čeprav ne vidimo dneva, je to oblika, vendar je datum vpisan kot dan-mesec-leto.

Najprej ustvarimo vrtilno tabelo, kot je prikazano spodaj. Oznake meseca damo v oznake vrstic, leto v stolpce stolpcev in podatke vsote v vrednosti. Zdaj lahko vstavimo izračunano postavko. Pojavilo se bo okno, v katerem lahko zgradimo naš element oblikovanja.

Elemente združite in razvrstite v polje vrtilne tabele
Elemente združite in razvrstite v polje vrtilne tabele. Če želite elemente združiti v skupine in jih razstaviti, morate izbrati združene elemente. Nato na plošči kliknite gumb Skupina

Združi in razstavi skupine številk v vrtilni tabeli
Združi in razstavi skupine številk v vrtilni tabeli. Če želite razvrstiti in razvrščati številke, izberite številko v polju, kamor želite razvrstiti elemente. Nato pritisnite gumb

A formule ni treba pisati tako, da vtipkamo leta, temveč elemente izbiramo z miško. Novi stolpec je oblikovan v odstotkih in že smo ustvarili vrtilno tabelo z izračunano postavko. Ustvarimo lahko seznam z vsemi formulami, ustvarjenimi v vrtilni tabeli izračunanih postavk in izračunanih polj. Če želite to narediti, postavite kazalec v vrtilno tabelo in izberite Orodja vrtilne tabele, formule in Ustvari seznam formul.

Tako se ustvari nov list, v katerem bomo dobili zahtevani seznam. Segmentacija podatkov ponuja gumbe, ki jih lahko kliknete za filtriranje podatkov vrtilne tabele. Segmenti podatkov poleg hitrega filtriranja prikazujejo tudi trenutno stanje filtriranja, kar olajša razumevanje, kaj natančno je prikazano v filtriranem poročilu vrtilne tabele. Če je element izbran, je vključen v filter, podatki za ta element pa so prikazani v poročilu.

Družite datume ali ure v vrtilni tabeli in razvrščajte v skupine
Družite datume ali ure v vrtilni tabeli in razvrščajte v skupine. Če želite datum ali uro razvrstiti in razvrstiti v skupine, izberite datum ali čas v polju, v katerem želite razvrstiti element.

Elementi polj strani vrtilne tabele za skupine in razveljavitve skupine
Elemente polja vrtilne tabele razvrstite v skupine in jih razstavite. Če je vrtilna tabela ustvarjena iz zunanjega vira podatkov, preverite, ali polje strani pridobiva zunanje podatke


Osvežite ali spremenite vir podatkov vrtilne tabele. V procesu dela se včasih pojavi situacija, ko se pojavijo novi podatki, ki jih je treba vstaviti v ustvarjeni dokument, in

Osveževanje podatkov v vrtilni tabeli
Osvežite podatke v vrtilni tabeli. Če želite posodobiti podatke v vrtilni tabeli, morate narediti naslednje: v vrtilni tabeli je treba izbrati celico, katere vsebina je potrebna

Onemogočite osvežitev vrtilne tabele pri odpiranju datoteke delovnega zvezka
Onemogočite osveževanje vrtilne tabele pri odpiranju datoteke delovnega zvezka. Če želite onemogočiti osvežitev vrtilne tabele med odpiranjem datoteke, izberite celico v vrtilni tabeli, za katero ne bo prišlo do osveževanja

Razvrščanje podatkov vrtilne tabele
Razvrščanje podatkov vrtilne tabele. Elementi polj vrtilne tabele se samodejno razvrstijo v naraščajočem vrstnem redu glede na njihovo ime. Če razvrstite elemente po imenu, lahko obnovite izvirnik

Povzemanje in obdelava podatkov vrtilne tabele
Povzemanje in obdelava podatkov vrtilne tabele. Podatkovna polja vrtilne tabele lahko nastavite za izračune, ki niso privzeti v funkciji povzetek. Za izvedbo kompleksnih

Uporaba velikih in vmesnih seštevkov v vrtilni tabeli
Uporaba velikih in vmesnih seštevkov v vrtilni tabeli. Vrtilno tabelo je mogoče konfigurirati za izvajanje izračunov s pomočjo funkcije povzetka. Povzetek je vrsta izračuna, ki jo izvede

V vrtilno tabelo vstavite ali odstranite vmesne seštevke
V vrtilno tabelo vstavite ali odstranite vmesne seštevke. Če želite vstaviti ali izbrisati vmesne seštevke, morate kazalec nastaviti na gumb polja, v katerem želite

Ustvarite grafikon za vrtilno tabelo
Ustvarite grafikon za vrtilno tabelo. Če želite ustvariti grafikon za vrtilno tabelo, v orodni vrstici vrtilnih tabel v meniju vrtilne tabele izberite ukaz Izberi. Preveri to

Ta objava predstavlja zbirko preprostih in izvrstnih orodij vrtilne tabele v Excelu. Kaj se v angleščini imenuje nasveti in triki. Vzemite si čas in preberite nasvete tukaj. Kdo ve, morda boste končno našli odgovor na vprašanje, ki vas že dolgo muči?

Nasvet 1. Samodejno osveževanje vrtilnih tabel

Včasih želite, da se vrtilne tabele samodejno posodabljajo. Recimo, da ste ustvarili vrtilno tabelo za upravitelja. Malo verjetno je, da ga boste lahko redno posodabljali, razen če vas upravitelj pusti do svojega prenosnika. Lahko omogočite samodejna posodobitev vrtilna tabela, ki se zažene ob vsakem odpiranju delovnega zvezka:

  1. Z desno miškino tipko kliknite vrtilno tabelo in izberite Možnosti vrtilne tabele.
  2. V pogovornem oknu, ki se prikaže Možnosti vrtilne tabele izberite zavihek Podatki.
  3. Potrdite polje Osveži v datoteki odprto.

Slika: 1. Omogočite možnost Osveži v datoteki odprto

Potrditveno polje Osveži v datoteki odprto je treba namestiti za vsako vrtilno tabelo posebej.

Opombo prenesite v obliki ali primere v obliki (datoteka vsebuje kodo VBA).

Nasvet 2. Istočasna posodobitev vseh vrtilnih tabel knjige

Če delovni zvezek vsebuje več vrtilnih tabel, je lahko njihovo posodabljanje hkrati težavno. Te težave lahko premagamo na več načinov:

Metoda 1. Za vsako vrtilno tabelo, ki je del delovnega zvezka, lahko izberete nastavitev, ki se samodejno osveži, ko se delovni zvezek odpre (za več podrobnosti glejte 1. nasvet).

3. način: Uporabite kodo VBA za posodobitev vseh vrtilnih tabel v delovnem zvezku na zahtevo. Ta pristop uporablja metodo RefreshAll predmeta Delovni zvezek. Če želite uporabiti to tehniko, ustvarite nov modul in vnesite naslednjo kodo:

Sub Refresh_All ()

ThisWorkbook.RefreshAll

Nasvet 3. Razvrstite predmete v naključnem vrstnem redu

Na sl. Slika 2 prikazuje privzeti vrstni red prikaza regij v vrtilni tabeli. Regije so razvrščene po abecednem vrstnem redu: zahod, sever, srednji zahod in jug. Če vaša poslovna pravila zahtevajo, da najprej prikažete regijo Zahod in nato regijo Srednji zahod, Sever in Jug, naredite ročno razvrščanje. Samo vnesite Midwest v celico C4 in pritisnite tipko Enter... Vrstni red razvrščanja regij se bo spremenil.


Nasvet 4: Pretvorite vrtilno tabelo v trdno kodirane vrednosti

Namen ustvarjanja vrtilne tabele je povzeti in prikazati podatke v ustrezen format... Izvirni podatki za vrtilno tabelo so shranjeni ločeno, kar uvaja nekaj "režijskih stroškov". Pretvorba vrtilne tabele v vrednosti vam bo omogočila uporabo rezultatov, dobljenih v njej, ne da bi dostopali do izvirnih podatkov ali predpomnilnika vrtilne tabele. Način preoblikovanja vrtilne tabele je odvisen od tega, ali je prizadeta celotna tabela ali samo njen del.

Če želite pretvoriti del vrtilne tabele, sledite tem korakom:

  1. Izberite kopirane podatke vrtilne tabele, z desno miškino tipko kliknite in izberite Kopirati (ali na tipkovnici vnesite Ctrl + C).
  2. Z desno miškino tipko kliknite kjer koli na delovnem listu in izberite ukaz Prilepi (ali vnesite Ctrl + V).

Če morate pretvoriti celotno vrtilno tabelo, sledite tem korakom:

  1. Izberite celotno vrtilno tabelo, z desno miškino tipko kliknite in izberite Kopirati... Če vrtišče ne vsebuje območja FILTRI, lahko z bližnjico na tipkovnici Ctrl + Shift + * izberete območje vrtilne tabele.
  2. Z desno miškino tipko kliknite kjer koli na listu in v kontekstnem meniju izberite možnost Prilepite posebno.
  3. Izberite možnost Vrednosti in kliknite v redu.

Pred pretvorbo vrtilne tabele je dobro, da delne vsote izbrišete, ker v naboru podatkov brez povezave niso zelo uporabni. Če želite izbrisati vse vmesne seštevke, pojdite v meni Načrt -\u003e Vmesni seštevki -\u003e Ne prikazuj vmesnih seštevkov. Če želite izbrisati določene vmesne seštevke, z desno miškino tipko kliknite celico, v kateri se izračunajo vsote. Izberite element v priročnem meniju Parametri polja in v pogovornem oknu Parametri polja v oddelku Izid izberite izbirni gumb Ne... Po kliku na gumb v redu vmesni seštevki bodo izbrisani.

Nasvet 5. Izpolnjevanje praznih celic v polji LINE

Po pretvorbi vrtilne tabele na listu niso prikazane samo vrednosti, temveč celotna podatkovna struktura vrtilne tabele. Na primer, podatki, prikazani na sl. 3 so bili pridobljeni iz vrtilne tabele s tabelarno postavitvijo.


Slika: 3. Težko je uporabiti to pretvorjeno vrtilno tabelo brez polnjenja praznih celic na levi strani

Upoštevajte, da so polja Regija in Prodajni trg ohranja enako strukturo vrstic kot takrat, ko so ti podatki najdeni v območju ROWS vrtilne tabele. Excel 2013 ima hitra pot polnjenje celic v območju LINE z vrednostmi. Kliknite območje vrtilne tabele in pojdite skozi meni Konstruktor -> Postavitev poročila -> (slika 4). Nato lahko vrtilno tabelo pretvorite v vrednosti, kar bo povzročilo podatkovno tabelo brez presledkov.


Slika: 4. Po uporabi ukaza Ponovite vse oznake elementov vse prazne celice so zapolnjene

Nasvet 6: Razvrstitev številskih polj v vrtilni tabeli

V postopku razvrščanja in razvrščanja polj, ki vsebujejo veliko število podatkovnih postavk, ni vedno enostavno določiti številčnega ranga analiziranega podatkovnega elementa. Poleg tega, če se vrtilna tabela pretvori v vrednosti, bo dodelitev vsakemu podatkovnemu elementu številčnega ranga, prikazanega v celoštevilskem polju, močno olajšala analizo ustvarjenega nabora podatkov. Odprite vrtilno tabelo, kot je prikazana na sl. 5. Upoštevajte, da isti kazalnik - Znesek po polju Obseg prodaje - prikazano dvakrat. Z desno miškino tipko kliknite drugi primerek meritve in izberite Dodatni izračuni -> Razvrščanje od največjega do najmanjšega (slika 6.)

Po ustvarjanju uvrstitve lahko prilagodite napise in oblikovanje polj (slika 14.9). Rezultat bo lepo uvrščeno poročilo.



Nasvet 7: Zmanjšajte velikost poročila vrtilne tabele

Ko ustvarite poročilo vrtilne tabele, Excel posname posnetek podatkov in ga shrani v predpomnilnik vrtilne tabele. Predpomnilnik vrtilne tabele je posebno področje pomnilnika, ki shrani kopijo podatkovnega vira za pospešitev dostopa. Z drugimi besedami, Excel ustvari kopijo podatkov in jo nato shrani v predpomnilnik, povezan z delovnim zvezkom. Predpomnilnik vrtilne tabele omogoča optimizacijo poteka dela. Vse spremembe vrtilne tabele, na primer spreminjanje postavitve polj, dodajanje novih polj ali skrivanje elementov, so hitrejše, zahteve po sistemskih virih pa so veliko skromnejše. Glavna pomanjkljivost predpomnilnika vrtilne tabele je, da skoraj podvoji velikost datoteke delovnega zvezka vsakič, ko vrtilno tabelo ustvarite iz nič.

Izbrišite izvirne podatke.Če delovni zvezek vsebuje izvirni nabor podatkov in vrtilno tabelo, se njegova velikost datoteke podvoji. Zato lahko varno izbrišete izvirne podatke, kar pa sploh ne bo vplivalo na funkcionalnost vrtilne tabele. Po brisanju izvirnih podatkov ne pozabite shraniti stisnjene različice datoteke delovnega zvezka. Po brisanju prvotnih podatkov lahko vrtilno tabelo uporabljate kot običajno. Edina težava je nezmožnost posodobitve vrtilne tabele zaradi pomanjkanja prvotnih podatkov. Če potrebujete nekaj podatkov v ozadju, dvokliknite presečišče vrstice in stolpca na območju velikih seštevkov (na sliki 7 je to celica B18). Pri tem Excel vsebino predpomnilnika vrtilne tabele odvrže v nov delovni list.

Nasvet 8: Ustvarite samodejno razširljiv obseg podatkov

Zagotovo ste že večkrat naleteli na situacije, ko ste morali vsakodnevno posodabljati poročila vrtilne tabele. To je najpogosteje potrebno, kadar se v zapis podatkov nenehno dodajajo novi zapisi. V takih primerih boste morali na novo definirati prej uporabljeni obseg, preden bodo novi zapisi dodani v novo vrtilno tabelo. Ponovna opredelitev izvirnega obsega podatkov za vrtilno tabelo je enostavna, a ko to počnete pogosto, postane precej dolgočasno.

Rešitev težave je pretvorba prvotnega obsega podatkov v tabelo, še preden je ustvarjena vrtilna tabela. Zahvale gredo excelove preglednice lahko ustvarite imenovani obseg, ki se lahko samodejno razširi ali skrči, odvisno od količine podatkov v njem. Na obseg lahko povežete tudi katero koli komponento, grafikon, vrtilno tabelo ali formulo, tako da lahko sledite spremembam v naboru podatkov.

Za izvedbo opisane tehnike izberite izvorne podatke in nato kliknite ikono tabele, ki se nahaja na zavihku Vstavi (slika 8) ali pritisnite Ctrl + T (angleško T). Kliknite v redu v oknu, ki se odpre. Upoštevajte, da čeprav vam ni treba znova določiti obsega izvornih podatkov v vrtilni tabeli, morate kljub temu klikniti gumb, ko v obseg vrtilne tabele dodate izvorne podatke. Osveži.

Nasvet 9: Primerjajte običajne tabele z vrtilno tabelo

Če primerjate dve različni tabeli, je priročno uporabiti vrtilno tabelo, ki bo prihranila veliko časa. Recimo, da imate dve tabeli, ki prikazujeta podrobnosti o strankah za leti 2011 in 2012 (slika 9). Majhne dimenzije teh tabel so tukaj podane zgolj kot primeri. V praksi se uporabljajo veliko večje tabele.


Primerjava ustvari eno samo tabelo, iz katere je ustvarjena vrtilna tabela. Prepričajte se, da imate na voljo podatke, povezane s temi tabelami. V tem primeru uporablja stolpec Poslovno leto (slika 10). Po združitvi obeh tabel z nastalim kombiniranim naborom podatkov ustvarite novo vrtilno tabelo. Oblikujte vrtilno tabelo tako, da se območje stolpca vrtilne tabele uporablja kot oznaka tabele (identifikator, ki označuje izvor tabele). Kot je prikazano na sl. 11, leta so v območju stolpca, podatki o strankah pa v območju vrstice. Podatkovno področje vsebuje obseg prodaje za vsako stranko.


Nasvet 10: Samodejno filtriranje vrtilne tabele

Kot veste, samodejnih filtrov ni mogoče uporabiti za vrtilne tabele. Vendar obstaja trik, da omogočite samodejne filtre v vrtilni tabeli. Načelo te tehnike je, da postavite kazalec miške na desno od zadnje glave vrtilne tabele (celica D3 na sliki 12), nato pojdite na trak in izberite Podatki -> Filter... Od zdaj naprej se v vaši vrtilni tabeli prikaže samodejni filter! Izberete lahko na primer vse stranke z višjo stopnjo transakcije od povprečne. Samodejni filtri dodajo dodatno plast analitike v vrtilno tabelo.


Nasvet 11. Pretvorite nabore podatkov, prikazane v vrtilnih tabelah

Najboljša postavitev za prvotne podatke, pretvorjene v vrtilno tabelo, je tabelarična postavitev. Ta vrsta postavitve ima naslednje značilnosti: ni praznih vrstic ali stolpcev, vsak stolpec ima glavo, vsako polje ima vrednost v vsaki vrstici in stolpci ne vsebujejo ponavljajočih se skupin podatkov. V praksi pogosto obstajajo nabori podatkov, ki spominjajo na prikazano na sl. 13. Kot lahko vidite, se imena mesecev prikažejo v vrstici na vrhu tabele in izvajajo dvojno funkcijo oznak stolpcev in dejanskih podatkov. V vrtilni tabeli, ustvarjeni iz take tabele, bi nastalo 12 polj za upravljanje, od katerih vsako predstavlja ločen mesec.


Če želite odpraviti to težavo, lahko kot vmesni korak uporabite vrtilno tabelo z več konsolidiranimi obsegi (glejte podrobnosti). Če želite pretvoriti nabor podatkov, ki ima slog matrike, v nabor podatkov, ki je primernejši za ustvarjanje vrtilnih tabel, sledite tem korakom.

Korak 1. Združite vsa ne stolpna polja v en stolpec.Če želite ustvariti vrtilne tabele z več konsolidiranimi obsegi, ustvarite stolpec z eno dimenzijo. V tem primeru se vse, kar ni povezano s poljem mesec, šteje za dimenzijo. Zato polja Prodajni trg in Opis storitve je treba združiti v en stolpec. Če želite polja združiti v en stolpec, preprosto vnesite formulo, ki poveže dve polji z uporabo podpičja kot ločila. Dajte novemu stolpcu ime. Vnesena formula se prikaže v vrstici s formulami (slika 14).


Slika: 14. Rezultat združevanja stolpcev Prodajni trg in Opis storitve

Po izdelavi združenega stolpca pretvorite formule v vrednosti. Če želite to narediti, izberite novo ustvarjeni stolpec, pritisnite Ctrl + C in nato zaženite ukaz Prilepi -> Prilepite posebno -> Vrednosti... Zdaj je mogoče stolpce odstraniti Prodajni trg in Opis storitve (slika 15).


Slika: 15. Izbrisani stolpci Prodajni trg in Opis storitve

Korak 2. Ustvarite vrtilno tabelo z več obsegi konsolidacije.Zdaj morate poklicati čarovnika za vrtilne tabele in grafikone, ki ga mnogi uporabniki poznajo iz prejšnjih različic Excela. Pritisnite Alt + D + P, da zaženete tega čarovnika. Žal je ta bližnjica na tipkovnici za angleško različico programa Excel 2013. V ruski različici ustreza bližnjični tipki Alt + D + N. Ampak to ne deluje iz meni neznanih razlogov. Vendar pa lahko v orodno vrstico za hitri dostop pripeljete dobrega čarovnika vrtilne tabele, glejte. Po zagonu čarovnika izberite izbirni gumb V več obsegih konsolidacije... Kliknite Nadalje... Nastavite stikalo Ustvari polja strani in kliknite Nadalje... Določite delovno območje in kliknite Končano (glej podrobnosti). Ustvarili boste vrtilno tabelo (slika 16).


Korak 3. Dvokliknite presečišče vrstice in stolpca v vrstici splošnega seštevka.Na tej točki imate vrtilno tabelo (slika 16), ki vključuje več konsolidacijskih obsegov, kar je skoraj neuporabno. Izberite celico, ki je na presečišču vrstice, in stolpec skupnega seštevka, in dvokliknite nanjo (v našem primeru je to celica N88). Dobili boste nov list, katerega struktura je podobna strukturi, prikazani na sl. 17. Ta list je dejansko prenesena različica prvotnih podatkov.


Korak 4. Razdelitev stolpca Vrstica na ločena polja.Še vedno je treba razdeliti stolpec Vrstica v ločena polja (vrnitev na prvotno strukturo). Takoj za stolpcem dodajte en prazen stolpec Vrstica... Označite stolpec A in nato pojdite na zavihek traku Podatki in kliknite na gumb Besedilo stolpca... Na zaslonu se prikaže pogovorno okno Čarovnik za distribucijo besedila... V prvem koraku izberite izbirni gumb Razmejenoin kliknite gumb Naprej. V naslednjem koraku izberite izbirni gumb podpičje in kliknite Končano... Oblikujte besedilo, dodajte naslov in s pritiskom na Ctrl + T spremenite izvirne podatke v tabelo (slika 18).


Slika: 18. Ta nabor podatkov je idealen za ustvarjanje vrtilne tabele (primerjaj s sliko 13)

Nasvet 12: V vrtilno tabelo vključite dve obliki številk

Zdaj pa razmislite o situaciji, ko normalizirani nabor podatkov otežuje izdelavo vrtilne tabele, ki jo je enostavno analizirati. Primer je prikazan na sl. 19 je tabela, ki vključuje dva različna kazalnika za vsako prodajno področje. Obvestilo stolpec D, ki določa metriko.


Čeprav je tabela primer precej dobrega formatiranja, pa ni vse tako dobro. Upoštevajte, da morajo biti nekatere meritve prikazane v številčni obliki, druge pa v odstotkih. Toda v izvorni bazi podatkov polje Vrednost je tipa Double. Pri ustvarjanju vrtilne tabele iz nabora podatkov istemu polju ne morete dodeliti dveh različnih formatov števil Vrednost... Tu obstaja preprosto preprosto pravilo: eno polje ustreza enemu številskemu formatu. Poskus dodelitve oblike števila polju, ki mu je bil dodeljen format v odstotkih, bo povzročil, da se bodo vrednosti v odstotkih spremenile v običajne številke, ki se končajo z znakom za odstotek (slika 20).


Za rešitev te težave se uporablja format števil po meri, ki poljubno vrednost, večjo od 1,5, oblikuje kot število. Če je vrednost manjša od 1,5, je oblikovana v odstotkih. V pogovornem oknu Oblika celice izberite zavihek (vsi formati) in na terenu Tip vnesite naslednji niz za oblikovanje (slika 21): [\u003e \u003d 1,5] $ # ## 0; [<1,5]0,0%


Slika: 21. Uporabite format števil po meri, kjer so številke, manjše od 1,5, oblikovane v odstotkih

Rezultat je prikazan na sl. 22. Kot lahko vidite, je zdaj vsak indikator pravilno formatiran. Seveda recept v tem nasvetu ni univerzalen. Namesto tega kaže smer, v katero bi morali eksperimentirati.


Nasvet 13: Ustvarite frekvenčno porazdelitev za vrtilno tabelo

Če ste kdaj ustvarili porazdelitve frekvence s pomočjo funkcije Excel Pogostost, verjetno veste, da je to zelo težka naloga. Poleg tega se mora po spremembi obsega podatkov vse začeti znova. V tem poglavju boste izvedeli, kako ustvariti preproste frekvenčne porazdelitve z uporabo preproste vrtilne tabele. Najprej ustvarite vrtilno tabelo s podatki v območju vrstic. Bodite pozorni na sl. 23, kjer je polje v območju vrstice Glasnost prodaje.

Z desno miškino tipko kliknite katero koli vrednost v območju Vrstice in v kontekstnem meniju izberite možnost Skupina... V pogovornem oknu Združevanje v skupine (Slika 24) določajo vrednosti parametrov, ki določajo začetek, konec in korak frekvenčne porazdelitve. Kliknite V redu.

Slika: 24. V pogovornem oknu Združevanje v skupine prilagodite parametre frekvenčne porazdelitve

Če v vrtilno tabelo dodate polje Kupec (Slika 25), dobimo frekvenčno porazdelitev strankinih transakcij glede na velikost naročil (v dolarjih).

Slika: 25. Zdaj imate na voljo distribucijo transakcij strank glede na velikost naročil (v dolarjih)

Prednost te tehnike je, da lahko filter poročila vrtilne tabele uporabimo za interaktivno filtriranje podatkov na podlagi drugih stolpcev, na primer Regija in Prodajni trg... Uporabnik ima tudi možnost, da hitro prilagodi intervale porazdelitve frekvence z desnim klikom na katero koli številko v območju vrstice in nato izbiro možnosti Skupina... Zaradi jasnosti lahko dodamo vrtilni grafikon (slika 26).


Nasvet 14. Uporaba vrtilne tabele za distribucijo nabora podatkov po listih delovnih listov

Analitiki morajo pogosto ustvariti različna poročila vrtilne tabele za vsako regijo, prodajno območje, upravitelja itd. Ta naloga običajno vključuje dolgotrajen postopek kopiranja vrtilne tabele na nov list in nato spreminjanje polja filtra, da odraža ustrezno regijo in upravitelja. Ta postopek je ročen in se ponovi za vsako analizo. Toda pravzaprav lahko ustvarjanje posameznih vrtilnih tabel prenesete v Excel. Kot rezultat uporabe parametra ločena vrtilna tabela se samodejno ustvari za vsak element v območju filtrirnih polj. Če želite uporabiti to funkcijo, preprosto ustvarite vrtilno tabelo, ki vključuje polje filtra (slika 27). Kazalec postavite kamor koli v vrtilno tabelo in na zavihek Analiza v skupini Vrteča miza kliknite spustni meni Opcije (slika 28). Nato kliknite na gumb Pokaži strani s filtri poročila.


Slika: 28. Kliknite gumb Pokaži strani s filtri poročila

V pogovornem oknu, ki se prikaže (slika 29), lahko izberete polje filtra, za katerega bodo ustvarjene ločene vrtilne tabele. Izberite ustrezno polje filtra in kliknite v redu.

Slika: 29. Pogovorno okno Prikaz strani s filtri poročila

Za vsak element polja filtra bo ustvarjena vrtilna tabela, nameščena na ločenem listu (slika 30). Upoštevajte, da so zavihki listov poimenovani enako kot elementi v polju filtra. Upoštevajte, da je parameter Pokaži strani s filtri se lahko uporabljajo za filtriranje polj eno za drugim.


Nasvet 15: Uporaba vrtilne tabele za distribucijo nabora podatkov po posameznih delovnih zvezkih

Pri nasvetu 14 smo uporabili posebno možnost za razdelitev zbirne tabele po prodajnih trgih na različnih listih delovnega zvezka. Če se morate razdeliti začetni podatki za različne trge v ločenih knjigah lahko uporabite malo kode VBA. Najprej postavite polje, ki ga želite filtrirati, v področje filtrirnih polj. Postavite polje Obseg prodaje v obseg vrednosti (slika 31). Predlagana koda VBA po vrsti izbere vsak element FILTER in pokliče funkcijo Pokaži podrobnostiz ustvarjanjem novega podatkovnega lista. Ta list se nato shrani v nov delovni zvezek

Koda VBA.

Sub ExplodeTable ()

Zatemni PvtItem kot vrtilni element

Zatemni PvtTable kot vrtilno tabelo

Zatemni polje kot vrtišče

'Spremenite spremenljivke glede na skript

ConststrFieldName \u003d "Marketplace"<—Изменение имени поля

Const strTriggerRange \u003d "A4" '<—Изменение диапазона триггера

'Spremenite ime vrtilne tabele (če je potrebno)

SetPvtTable \u003d ActiveSheet.PivotTables ("vrtilna tabela1") '<—Изменение названия сводной

‘Prehodite vsak element izbranega polja

Za vsak element PvtItem v PvtTable.PivotFields (strFieldName) .PivotItems

PvtTable.PivotFields (strFieldName) .CurrentPage \u003d PvtItem.Name

Obseg (strTriggerRange) .ShowDetail \u003d True

'Dodelitev imena začasnemu listu

ActiveSheet.Name \u003d "TempSheet"

‘Kopiranje podatkov v nov delovni zvezek in brisanje začasnega lista

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Celice.EntireColumn.AutoFit

Application.DisplayAlerts \u003d False

ActiveWorkbook.SaveAs _

Ime datoteke: \u003d ThisWorkbook.Path & "\\" & PvtItem.Name & ".xlsx"

ActiveWorkbook.Zapri

Sheets ("Tempsheet"). Izbriši

Application.DisplayAlerts \u003d True

Vnesite to kodo v nov modul VBA. Preverite vrednosti naslednjih konstant in spremenljivk in jih po potrebi spremenite:

  • Const strFieldName. Ime polja, ki se uporablja za ločevanje podatkov. Z drugimi besedami, to je polje, ki se prilega območju filtra / strani vrtilne tabele.
  • Const strTriggerRange. Sprožilna celica, ki shrani eno številko iz podatkovnega območja vrtilne tabele. V našem primeru je sprožilna celica A4 (glej sliko 31).

Kot rezultat izvajanja kode VBA se bodo podatki za vsako prodajno območje shranili v ločeni delovni zvezek.

Opomba po Jelenovi knjigi Aleksander. ... 14. poglavje.



Priporočamo branje