Uporaba vrtilnih tabel

Prej sem že govoril o tem, da pri sklicevanju na celico vrteča miza namesto običajne povezave se vrne funkcija GET.DATA.PUMP.TABLE (glej). Če vas zanima kot za premagovanje te nevšečnosti priporočam sklicevanje na omenjeno opombo. Če se sprašujete, zakaj tako se to zgodi in tudi kakšni so pozitivni vidiki funkcije GET DATA OF PANEL.TABLE, nato pa ponudim fragment knjige Jelen, Alexander. (poglavje 15). Z obravnavano tehniko se boste lahko spopadli s številnimi težavami, ki povzročajo preglavice uporabnikom vrtilnih tabel, zlasti:

Ko se vrtilna tabela osveži, predhodno uporabljeno oblikovanje izgine. Številčni formati so izgubljeni. Rezultati prilagajanja širine stolpca izginejo.
Ne obstaja enostaven način ustvarjanje asimetrične vrtilne tabele. Edina možnost je uporaba imenovanih naborov, vendar je ta metoda na voljo samo tistim, ki uporabljajo vrtilne tabele podatkovnega modela in ne običajnih vrtilnih tabel.
Excel se ne spomni predlog. Če morate znova in znova znova ustvariti vrtilne tabele, se boste morali znova združiti, uporabiti izračunana polja in člane ter izvesti številna druga podobna opravila.

Vse, kar je tukaj opisano, pravzaprav ni novo. Poleg tega se podobne tehnike uporabljajo že od Excela 2002. Vendar pa moja komunikacija z uporabniki kaže, da jih pozna manj kot 1%. Edino vprašanje, ki ga imajo uporabniki, je, kako izklopiti čudno funkcijo GET DATA OF PANEL.TABLE. Škoda…

Prenesite opombo v obliki zapisa ali primere v obliki zapisa

No, začnimo po vrsti.

Kako opustiti problematično funkcijo PRIDOBITE PODATKE. OSEBNE MIZE

Funkcija GET PIVOTTABLE DATA je že dolgo glavobol za mnoge uporabnike. V Excelu 2002 se je naenkrat brez opozorila spremenilo vedenje vrtilnih tabel. Takoj, ko začnete ustvarjati formule zunaj vrtilne tabele, ki se sklicujejo na njene podatke, ta funkcija prihaja od nikoder.

Denimo, da je v vrtilni tabeli, prikazani na sl. 1, morate primerjati podatke za leti 2015 in 2014.

Slika: 1. Izvirna vrtilna tabela

V celico D3 dodajte naslov "% višine".
Kopirajte obliko iz celice C3 v celico D3.
V celico D4 vnesite enačbo.
Kliknite celico C4.
Vnesite znak / (poševnica naprej), ki predstavlja operacijo delitve.
Kliknite celico B4.
Vnesite -1 in pritisnite kombinacijo tipk ostati v isti celici. Rezultat oblikujte v odstotkih. Videli boste, da je Zahod doživel 43,8-odstotni padec dohodka (slika 2). Ne zelo dobri rezultati.
Ko končate z vnosom prve formule, izberite celico D4.
Dvokliknite majhen kvadrat v spodnjem desnem kotu celice. Ta kvadrat predstavlja ročico za izpolnjevanje, s katero lahko kopirate formulo za zapolnitev celotnega stolpca v poročilu.

Ko končate s kopiranjem formule in s pogledom na zaslon, boste ugotovili, da je nekaj narobe - vsaka regija je v letu pokazala upad za 43,8% (slika 3).

Slika: 3. Ko končate s kopiranjem formule v vse celice v stolpcu, boste videli, da je vsaka regija pokazala 43,8-odstotni padec

V resničnem življenju se to verjetno ne bo zgodilo. Vsakdo vam bo povedal, da bo Excel po zgornjih korakih ustvaril formulo \u003d C4 / B4-1. Vrnite se v celico D4 in opazite vrstico s formulo (slika 4). Samo nekaj hudiča! Preprosta formula \u003d C4 / B4-1 ne obstaja več. Namesto tega program zapleteno konstrukcijo nadomesti s funkcijo GET.DATA.PUMP.TABLE. Zakaj ta formula daje pravilne rezultate v celici D4, vendar po kopiranju v spodnje celice noče delovati?

Prvi odziv katerega koli uporabnika na to, kar se je zgodilo, bo naslednji: "Kaj je ta nenavadna konstrukcija GET.DATA.PERSONAL.TABLE, ki je uničila moje poročilo?" Večina uporabnikov se bo te funkcije želelo takoj znebiti. Nekateri bodo zastavili vprašanje: "Zakaj nam je Microsoft to funkcijo dodelil?"

V tem ni bilo nič takega excel-krat 2000. Ko sem se začel redno srečevati s funkcijo GET.DATA.PERSONAL.TABLES, sem jo sovražil. Ko me je na enem od seminarjev nekdo vprašal, kako bi to lahko uporabili v dobrobit, sem obnemela. Še nikoli si nisem postavil takega vprašanja! Po mojem mnenju in po mnenju večine uporabnikov Excela je bila funkcija GET.DATA.PUMP.TABLE plod zla, ki ni imel nobene zveze z močmi dobrega. Na srečo lahko to funkcijo onemogočite na dva načina.

Zaklepanje funkcije GET PIVOTTABLE DATA z vnosom formule.Obstaja preprost način, da preprečite pojav funkcije GET PIVOTTABLE. Če želite to narediti, morate ustvariti formulo brez uporabe miške ali smernih tipk. Samo sledite tem korakom.

Pojdite v celico D4 in vnesite \u003d (znak enakosti).
Vnesite C4.
Vnesite / (poševnica naprej za delitev).
Vnesite B4.
Vnesite -1.
Kliknite na Enter.

Zdaj ste ustvarili običajno excel formula, ki jih lahko kopirate v celice pod stolpcem in s katerimi dobite pravilne rezultate (slika 5). Kot lahko vidite, lahko na območjih zunaj vrtilne tabele ustvarite formule, ki se nanašajo na podatke v vrtilni tabeli. In tisti, ki ne verjamejo, da je to mogoče, naj opisana dejanja izvedejo sami.

Slika: 5. Preprosto vnesite \u003d C4 / B4-1 s tipkovnice in formula bo delovala, kot potrebujete

Nekateri uporabniki se bodo počutili nelagodno ob običajnem vrstnem redu vnosa formul. Poleg tega je predlagana možnost bolj zahtevna. Če ste eden izmed teh uporabnikov, je drugi način za vas ...

Onemogočite funkcijo GET PANEL.TABLE DATA.Funkcijo GET PANEL.TABLE DATA lahko trajno onemogočite. Kliknite trak menija mapa → Parametri... V odprtem oknu ParametriExcel pojdi na prispevek Formule in počistite polje poleg možnosti Uporabite funkcijoGetPivotData za povezave vrtilne tabele... Kliknite na V redu.

Alternativna možnost. Kliknite vrtilno tabelo in kontekstni zavihek, ki se prikaže Analiza kliknite spustni seznam poleg gumba Parametri... Počistite polje zraven Ustvari GetPivotData (slika 7). Potrditveno polje je privzeto vklopljeno.

Zakaj nam je Microsoft ponudil funkcijo GET PANEL.TABLES.Če je ta funkcija tako grozna, zakaj jo je Microsoft privzeto omogočil? Zakaj jim je mar za ohranjanje podpore za to funkcijo v novejših različicah Excela? Se zavedajo razpoloženja uporabnikov? In preidemo na zabavni del ...

Uporaba GET PIVOTTABLE DATA za izboljšanje vrtilnih tabel

Pivot tabele so velik izum človeštva. Vrtilna tabela je ustvarjena z nekaj kliki, tako da ni treba uporabljati naprednega filtra, funkcije BDSUMM in podatkovnih tabel. Z vrtilnimi tabelami lahko iz ogromnih količin podatkov ustvarite poročila na eni strani. Te prednosti zasenčijo nekatere slabosti vrtilnih tabel, na primer nejasno oblikovanje in potrebo po pretvorbi vrtilnih tabel v vrednosti za dodatno prilagoditev. Na sl. Slika 8 prikazuje tipičen postopek za izdelavo vrtilne tabele. V tem primeru se vse začne z začetnimi podatki. Ustvarimo vrtilno tabelo in uporabljamo vse možne tehnike za njeno prilagoditev in izboljšanje. Včasih pretvorimo vrtilno tabelo v vrednosti in naredimo nekaj končnega oblikovanja.

Nova metodologija vrtilne tabele, ki jo je predlagal Rob Colley (razvijalec pri Microsoftu) in o kateri bomo razpravljali pozneje, je rezultat izboljšav zgoraj opisanega postopka. V tem primeru se najprej ustvari primitivna vrtilna tabela. Te tabele vam ni treba formatirati. Nato se izvede en korak, sorazmerno dolgotrajen postopek, da se ustvari lepo oblikovana lupina, v kateri bo končno poročilo. Po tem se s funkcijo GET.DATA.PUMP.TABLE hitro napolni poročilo, ki je v lupini, s podatki. Po prejemu novih podatkov jih lahko položite na list, posodobite primitivno vrtilno tabelo in natisnete poročilo, ki je v lupini (slika 9). Ta tehnika ima številne nesporne prednosti. Na primer, vam ni treba skrbeti za oblikovanje poročila takoj, ko ga ustvarite. Postopek ustvarjanja vrtilnih tabel postane skoraj popolnoma avtomatiziran.

Naslednji razdelki razpravljajo o tem, kako ustvariti dinamično poročilo, ki prikazuje dejanske številke za pretekle mesece in cilje za prihodnje mesece.

Ustvarite primitivno vrtilno tabelo.Začetni podatki (slika 10) so predstavljeni v obliki transakcij, ki vsebujejo informacije o načrtovanih in dejanskih kazalnikih za vsako regijo, v kateri so podružnice podjetja. Načrtovane številke so podrobno določene na mesečni ravni, dejanske pa na ravni posameznih dni. Načrtovani kazalniki so ustvarjeni za prihodnje leto, dejanski pa za pretekle mesece. Ker se bo poročilo posodabljalo vsak mesec, je ta postopek močno poenostavljen, če se vir podatkov vrtilne tabele poveča, ko se na dno dodajo novi podatki. V zastarelih različicah Ustvarjanje Excela podoben vir podatkov je bil izveden z imenovanim dinamičnim obsegom s funkcijo OFFSET (glej podrobnosti). Ko delate v Excelu 2013, preprosto izberite eno od podatkovnih celic in pritisnite kombinacijo tipk Ctrl + T (ustvarite tabelo). To bo ustvarilo imenovani nabor podatkov, ki se samodejno razširi z dodajanjem novih vrstic in stolpcev.

Zdaj pa ustvarimo vrtilno tabelo. Funkcija GET PIVOTTABLE DATA je dovolj zmogljiva, vendar lahko vrne samo vrednosti, ki so prikazane v dejanski vrtilni tabeli. Ta funkcija ne more izvesti pregleda predpomnilnika za izračun elementov, ki niso prisotni v vrtilni tabeli.

Ustvari vrtilno tabelo:

Izberite Team Vstavi → Vrteča mizain nato v pogovornem oknu Ustvarjanje vrtilne tabelekliknite v redu.
Na seznamu polj vrtilne tabele izberite polje datum... Na levi strani vrtilne tabele se prikaže seznam datumov (slika 11).
Izberite katero koli datumsko celico, na primer A4. Na kontekstnem zavihku Analizaki se nahaja v nizu kontekstualnih zavihkov Delo z vrtilnimi tabelami, kliknite gumb Razvrščanje po področjih (glej podrobnosti). V pogovornem oknu Združevanje v skupine izberite možnost Meseci (slika 12). Kliknite v redu... Imena mesecev bodo prikazana na levi strani vrtilne tabele (slika 13).
Povlecite polje datum na območje stolpcev vrtilne tabele.
Povlecite polje Kazalo na območje Stolpci seznama polj vrtilne tabele.
Izberite polje Regijaki se prikaže v levem stolpcu vrtilne tabele.
Izberite polje dohodek, ki se prikaže v območju vrednosti vrtilne tabele.

Slika: 11. Začnite z razvrščanjem po polju datum

V tem trenutku je naša vrtilna tabela videti precej primitivno (slika 14). Resnično ne maram črk Imena vrstic in Imena stolpcev... Nepraktično je prikazati vsote za Jan načrt in Jan Dejstvo v stolpcu D itd. A brez skrbi videz to vrtilno tabelo, ker je poleg vas ne bo videl nihče drug. Od tega trenutka bomo ustvarili ovoj za poročila, katerega vir podatkov bo na novo ustvarjena vrtilna tabela.

Ustvarjanje lupine poročila.V svoj delovni zvezek vstavite prazen list. Za nekaj časa odstavimo orodja vrtilne tabele in nadaljujmo z običajnimi orodji Excel. Naša naloga je s pomočjo formul in oblikovanja ustvariti čudovito poročilo, ki ga ni sram pokazati upravitelju.

Nadaljujte, kot sledi (slika 15).

V celico A1 vnesite ime poročila - Načrtovani in dejanski kazalniki po regijah.
Pojdite na zavihek domov, kliknite na gumb Slogi celic izberite obliko Naslov 1.
V celico A2 vnesite formulo \u003d EONTHS (DANES (); 0). Ta funkcija vrne zadnji dan tekočega meseca. Če na primer te vrstice preberete 14. avgusta 2014, bo celica A2 prikazala datum 31. avgust 2014.
Izberite celico A2. Pritisnite kombinacijo tipk Ctrl + 1, da se prikaže pogovorno okno Oblika celice... V zavihku Številka kliknite na element Vsi formati... Vnesite obliko števil po meri kot "Od meseca" MMMM "načrtovani kazalniki" (slika 16). Posledično bo izračunani datum videti kot besedilo.
V celico A5 vnesite naslov Regija.
Naslove regij vnesite v preostale celice v stolpcu A. Naslovi regij se morajo ujemati z imeni regij, prikazanimi v vrtilni tabeli.
Če je potrebno, v stolpec dodajte oznake za vsote po oddelkih.
Na dnu poročila dodajte vrstico Skupaj za podjetje.
V celico B4 vnesite formulo \u003d DATUM (LETO ($ A $ 2); STOLPEC (A1); 1). Ta formula vrne datume 01.01.2014, 01.02.2104 itd., Prve dni vseh 12 mesecev tekočega leta.
Izberite celico B4. Pritisnite kombinacijo tipk Ctrl + 1, da odprete okno Oblika celice... V zavihku Številka V poglavju Vsi formati vnesite obliko številke po meri Mmm... Ta oblika prikazuje tričrkovno ime meseca. Poravnajte besedilo na desni strani celice.
Kopirajte vsebino celice B4 v obseg C4: M4. Na vrhu vrtilne tabele se prikaže vrstica z imeni mesecev.
V celico B5 vnesite formulo \u003d IF (MESEC (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Dejstvoin za sedanjost in prihodnost - Načrtujte.
V celico N5 dodajte naslov Izid... V celico O4 - Izid, O5 - Načrtujte, P5 - % odstopanja.
Vnesite običajne Excelove formule, ki se uporabljajo za izračun skupnih oddelkov, skupne vrstice podjetja, stolpca skupnega seštevka in stolpca% variance:
1. v celico B8 vnesite formulo \u003d SUM (B6: B7) in jo kopirajte v druge celice v vrstici;
2. v celico N6 vnesite formulo \u003d SUM (B6: M6) in jo kopirajte v druge celice v stolpcu;
3. v celico P6 vnesite formulo \u003d IFERROR ((N6 / O10) -1; 0) in jo kopirajte v druge celice v stolpcu;
4. v celico B13 vnesite formulo \u003d SUM (B10: B12) in jo kopirajte v druge celice v vrstici;
5. v celico B17 vnesite formulo \u003d SUM (B15: B16) in jo kopirajte v druge celice v vrstici;
6. v celico B19 vnesite formulo \u003d SUM (B6: B18) / 2 in jo kopirajte v druge celice v vrstici.
Slog naslova 4 uporabite na nalepkah v stolpcu A in na naslovih v vrsticah 4 in 5.
Za obseg celic B6: O19 izberite format številke # ## 0.
Za celice v stolpcu P izberite obliko števila 0,0%.

Torej smo zaključili ustvarjanje lupine poročila, prikazane na sl. 15. To poročilo vključuje vse zahtevane oblike. V naslednjem razdelku je prikazano, kako uporabiti funkcijo GET PIVOTTABLE DATA (DOLOČITI PODATKOVNE PODATKE) za dokončanje poročila.

Slika: 15. Poročilo zavijte pred dodajanjem formul GET.DATA.PERSONAL.TABLES

Uporaba funkcije GET PIVOTTABLE DATA za zapolnitev lupine poročila s podatki.Odslej boste lahko izkusili vse prednosti uporabe funkcije GET PANEL.TABLE DATA. Če ste počistili potrditveno polje, ki omogoča to funkcijo, se vrnite na ustrezno nastavitev in vrnite potrditveno polje (glejte opis na sliki 6 ali 7).

Izberite celico B6 lupine poročila. Ta celica ustreza severovzhodni regiji in dejanskim podatkom za januar.

Enter \u003d (enak znak), da začnete vnašati formulo.
Pojdite na list iz vrtilne tabele in kliknite celico, ki ustreza severovzhodni regiji in dejanske številke za januar - C12 (slika 17).
Pritisnite tipko Enterza dokončanje vnosa formule in vrnitev v lupino poročila. Kot rezultat bo Excel v celico B6 dodal funkcijo GET PIVOTTABLE DATA. V celici bo prikazana vrednost 277.435 USD.

Zapomnite si to številko, saj bo potrebna pri primerjavi z rezultati formule, ki jo boste pozneje uredili. Formula, ki jo ustvari program, ima naslednjo obliko: \u003d GET.DATA.PUMP.TABLE ("Dohodek; 'Slika 11-14 ′! $ A $ 3;" Regija ";" Severovzhod ";" Datum " ; 1; "Kazalnik"; "Dejstvo"). Če ste doslej ignorirali funkcijo GET PIVOTTABLE DATA, je čas, da si jo podrobneje ogledate. Na sl. 18 ta formula je prikazana v načinu urejanja skupaj z namigom.

Argumenti funkcije:

Podatkovno_polje. Polje iz območja vrednosti vrtilne tabele. Prosimo, upoštevajte: v tem primeru se uporablja polje dohodek, vendar ne Znesek v polju Dohodek.
Vrteča miza. S tem parametrom vas Microsoft vpraša: "Katero vrtilno tabelo želite uporabiti?" Dovolj je določiti eno od celic vrtilne tabele. Vnos „Sl. 11-14 '! $ A $ 3 se nanaša na prvo celico v vrtilni tabeli, kamor se vnašajo podatki. Ker lahko v našem primeru določite katero koli celico, povezano z vrtilno tabelo, pustite argument nespremenjen. Naslov celice $ A $ 3 je primeren v vseh pogledih.
Polje 1; element 1. V samodejno ustvarjeni formuli je izbrano ime polja Regija, in kot vrednost polja - Severovzhod... Tu je razlog za težave, ki se pojavijo pri delu s funkcijo GET DATA.PUMP.TABLE. Samodejno izbranih vrednosti ni mogoče kopirati, ker so trdo kodirane. Če boste torej formule kopirali v celotno območje poročila, jih boste morali ročno spremeniti. Zamenjajte Severovzhod s referenco celice v obliki $ A6. Če postavite znak za dolar pred stolpec A, ugotovite, ali je mogoče sklic v vrstici spremeniti, ko formulo kopirate v celice v stolpcu.
Polje 2; točka 2. Ta par argumentov definira polje datum z vrednostjo 1. Če je bila prvotna vrtilna tabela razvrščena po mesecih, polje za mesec ohrani prvotno ime polja datum... Številčna vrednost meseca je 1, kar ustreza januarju. Težko je priporočljivo uporabiti takšno vrednost pri ustvarjanju ogromnih formul, ki so nastavljene v več deset ali celo na stotine poročevalnih celic. Bolje uporabiti formulo, ki izračuna vrednosti polj datum, kot je formula v celici B4. Namesto 1 v tem primeru lahko uporabite formulo MESEC (v 4 USD). Znak za dolar pred 4 označuje, da lahko formula polju dodeli vrednosti datum glede na druge mesece, ko se formula kopira v celice v vrstici.
Polje 3; element 3. V tem primeru se ime polja samodejno dodeli Kazalo in vrednost polja Dejstvo... Te vrednosti so pravilne za januar, za naslednje mesece pa bo treba vrednost polja spremeniti v načrt. Spremenite trdno kodirano vrednost polja Dejstvo do povezave B $ 5.
Polje 4; Element 4. Ti argumenti se ne uporabljajo, ker polj je konec.

Nova formula je prikazana na sl. 19. V minuti je bila namesto trdo kodirane formule, zasnovane za delo z eno samo vrednostjo, ustvarjena prilagodljiva formula, ki jo je mogoče kopirati v vse celice nabora podatkov. Pritisnite tipko Enterin dobili boste enak rezultat kot pred urejanjem formule. Urejena formula ima naslednjo obliko: \u003d GET.PUMP.TABLE DATA ("Prihodki"; 'Slika 11-14 ′! $ A $ 3; "Region"; $ A6; "Date"; MONTH (B $ 4) ; "Indikator"; 5 USD)

Slika: 19. Po zaključku urejanja je formula GET.DATA.PERSONAL.TABLE primerna za kopiranje v vse celice obsega

Kopirajte formulo v poljubne prazne celice v stolpcih B: M, kjer se izračunajo rezultati. Zdaj, ko poročilo vsebuje realne številke, lahko končno prilagodite širine stolpcev.

V naslednjem koraku bomo nastavili formulo GET.PUMP.TABLE za izračun končnih ciljev. Če kopirate formulo v celico O6, se prikaže sporočilo o napaki #REF! Razlog za to napako je, da je beseda Izid v celici O4 ni ime meseca. Če želite zagotoviti pravilno delovanje funkcije GET PIVOTTABLE DATA, mora biti zahtevana vrednost v vrtilni tabeli. Ker pa je v prvotni vrtilni tabeli polje Kazalo je drugo polje v območju stolpca, stolpec s podatki Povzetek načrta dejansko odsoten. Premakni polje Kazalo tako da postane prvi v območju stolpca (slika 20).

Slika: 20. Prilagodite postavitev polj v območju stolpca, tako da se stolpec prikaže Okvirni načrt

Primerjaj s sl. 14. Tam, na območju STOLPCA, je bilo prvo polje datum, kar je privedlo do dejstva, da so bile stolpce sprva razvrščene po datumu, v mesecu pa po načrtu / dejstvu. Zdaj je prvo polje Indicator, v povzetku pa prvi stolpci Načrtujte, znotraj razvrščeno po mesecih in nato v vseh stolpcih Dejstvo.

Če se vrnemo na list ovojnice poročila, stojimo v celici O6, vtipkamo \u003d (enak znak) in se sklicujemo na celico N12 na listu vrtilne tabele, ki ustreza načrtovanim rezultatom severovzhodne regije. Kliknite na Enter... Rezultat je formula \u003d GET.DATA.PERSONAL.TABLES ("Dohodek"; "Slika 11-14"! $ 3 $; "Regija"; "Severovzhod"; "Indikator"; "Načrt"). Uredite: \u003d GET. DATA.SUMMARY.TABLEES ("Dohodek"; 'Slika 11-14 ′! $ A $ 3; "Region"; $ A6; "Indicator"; O $ 5). Kopirajte to formulo v druge celice v stolpcu O (slika 21). Upoštevajte, da tudi če premaknete različna področja poročila vrtilne tabele, ovoj deluje pravilno. Če neka vrtilna polja onemogočite, se lupina s tem ne bo spoprijela ...

Slika: 21. Končno poročilo, ki ga je mogoče predstaviti upravitelju

Zdaj imate dobro oblikovan ovitek poročil, ki uporablja vrednosti iz dinamične vrtilne tabele. Čeprav je začetno ustvarjanje poročila trajalo precej dolgo, je posodabljanje trajalo le nekaj minut.

Posodobite poročilo.Če želite posodobiti poročilo s podatki za prihodnje mesece, sledite tem korakom.

Vstavite dejanske številke pod prvotni nabor podatkov. Ker so izvorni podatki v obliki tabele, se oblikovanje tabele samodejno razširi v nove podatkovne vrstice. Prav tako razširja definicijo prvotne vrtilne tabele (v datoteki Excel sem že dodal dejanske številke za celo leto).
Pojdite na vrtilno tabelo. Z desno miškino tipko izberite in izberite Osveži... Videz vrtilne tabele se bo spremenil, vendar je v redu.
Pojdite na lupino poročila. Načeloma je bilo za posodobitev poročila že storjeno vse, vendar testiranje rezultatov ne škodi. Spremenite na primer formulo v celici A2 na to: \u003d EON MESECI (DANES () +31 ; 0) in poglejte, kaj se bo zgodilo.

Če vsak mesec dodajate nove podatke o dejanski prodaji, vam ni treba skrbeti za ponovno ustvarjanje formatov, formul itd. Opisani postopek posodabljanja poročila je tako preprost, da boste za vedno pozabili na težave, ki so se pojavile med pripravo mesečnih poročil. Edina težava lahko nastane, če se podjetje reorganizira, zaradi česar se lahko v vrtilni tabeli pojavijo nova območja. Če želite zagotoviti, da formule delujejo pravilno, preverite, ali se skupno število v poročilu ujema s skupnim seštevkom v vrtilni tabeli. Ko se prikaže novo območje, ga preprosto dodajte na ovit list in povlecite ustrezne formule.

Nisem si mislil, da bom kdaj rekel naslednje: »Funkcija GET.DATA.PUMP.TABLE je največji blagoslov. Kako smo že prej obstajali brez nje? "

Prvotni Jelenini podatki so bili v izvirniku razporejeni tako, da so nadaljnje formule pravilno delovale šele julija 2015. V Excelovi datoteki, priloženi tej opombi, sem spremenil prvotne podatke in nekatere formule, tako da je vse delovalo, ne glede na datum, ko poskusili boste s priloženo datoteko Excel. Na žalost so morale biti formule zapletene.

Prej sem že govoril o tem, da se pri sklicevanju na celico vrtilne tabele namesto običajne povezave vrne funkcija GET.PIVOTTABLE DATA (glej). Če vas zanima kot za premagovanje te nevšečnosti priporočam sklicevanje na omenjeno opombo. Če se sprašujete, zakaj tako se to zgodi in tudi kakšni so pozitivni vidiki funkcije GET DATA OF PANEL.TABLE, nato pa ponudim fragment knjige Jelen, Alexander. (poglavje 15). Z obravnavano tehniko se boste lahko spopadli s številnimi težavami, ki povzročajo preglavice uporabnikom vrtilnih tabel, zlasti:

Ko se vrtilna tabela osveži, predhodno uporabljeno oblikovanje izgine. Številčni formati so izgubljeni. Rezultati prilagajanja širine stolpca izginejo.
Na enostaven način ni mogoče ustvariti asimetrične vrtilne tabele. Edina možnost je uporaba imenovanih naborov, vendar je ta metoda na voljo samo tistim, ki uporabljajo vrtilne tabele podatkovnega modela in ne običajnih vrtilnih tabel.
Excel se ne spomni predlog. Če morate znova in znova znova ustvariti vrtilne tabele, se boste morali znova združiti, uporabiti izračunana polja in člane ter izvesti številna druga podobna opravila.

Prenesite opombo v obliki zapisa ali primere v obliki zapisa

No, začnimo po vrsti.

Kako opustiti problematično funkcijo PRIDOBITE PODATKE. OSEBNE MIZE

Denimo, da je v vrtilni tabeli, prikazani na sl. 1, morate primerjati podatke za leti 2015 in 2014.

Slika: 1. Izvirna vrtilna tabela

V celico D3 dodajte naslov "% višine".
Kopirajte obliko iz celice C3 v celico D3.
V celico D4 vnesite enačbo.
Kliknite celico C4.
Vnesite znak / (poševnica naprej), ki predstavlja operacijo delitve.
Kliknite celico B4.
Vnesite -1 in pritisnite kombinacijo tipk ostati v isti celici. Rezultat oblikujte v odstotkih. Videli boste, da je Zahod doživel 43,8-odstotni padec dohodka (slika 2). Ne zelo dobri rezultati.
Ko končate z vnosom prve formule, izberite celico D4.
Dvokliknite majhen kvadrat v spodnjem desnem kotu celice. Ta kvadrat predstavlja ročico za izpolnjevanje, s katero lahko kopirate formulo za zapolnitev celotnega stolpca v poročilu.

Ko končate s kopiranjem formule in s pogledom na zaslon, boste ugotovili, da je nekaj narobe - vsaka regija je v letu pokazala upad za 43,8% (slika 3).

Slika: 3. Ko končate s kopiranjem formule v vse celice v stolpcu, boste videli, da je vsaka regija pokazala 43,8-odstotni padec

Nič takega se ni zgodilo v dneh Excela 2000. Ko sem se začel redno srečevati s funkcijo GETDATA.PERSONAL.TABLES, sem jo sovražil. Ko me je na enem od seminarjev nekdo vprašal, kako bi ga lahko uporabili v dobrobit, sem obnemela. Še nikoli nisem postavil takšnega vprašanja! Po mojem mnenju in po mnenju večine uporabnikov Excela je bila funkcija GET.DATA.PUMP.TABLE plod zla, ki ni imel nobene zveze z močmi dobrega. Na srečo lahko to funkcijo onemogočite na dva načina.

Pojdite v celico D4 in vnesite \u003d (znak enakosti).
Vnesite C4.
Vnesite / (poševnica naprej za delitev).
Vnesite B4.
Vnesite -1.
Kliknite na Enter.

Zdaj ste ustvarili navadno Excelovo formulo, ki jo lahko kopirate v spodnje celice stolpca, da dobite pravilne rezultate (slika 5). Kot lahko vidite, lahko na območjih zunaj vrtilne tabele ustvarite formule, ki se nanašajo na podatke v vrtilni tabeli. In tisti, ki ne verjamejo, da je to mogoče, naj opisana dejanja izvedejo sami.

Slika: 5. Preprosto vnesite \u003d C4 / B4-1 s tipkovnice in formula bo delovala, kot potrebujete

Uporaba GET PIVOTTABLE DATA za izboljšanje vrtilnih tabel

Naslednji razdelki razpravljajo o tem, kako ustvariti dinamično poročilo, ki prikazuje dejanske številke za pretekle mesece in cilje za prihodnje mesece.

Ustvarite primitivno vrtilno tabelo.Začetni podatki (slika 10) so predstavljeni v obliki transakcij, ki vsebujejo informacije o načrtovanih in dejanskih kazalnikih za vsako regijo, v kateri so podružnice podjetja. Načrtovane številke so podrobno določene na mesečni ravni, dejanske pa na ravni posameznih dni. Načrtovani kazalniki so ustvarjeni za prihodnje leto, dejanski pa za pretekle mesece. Ker se bo poročilo posodabljalo vsak mesec, je ta postopek močno poenostavljen, če se vir podatkov vrtilne tabele poveča, ko se na dno dodajo novi podatki. V starejših različicah Excela je bil tak vir podatkov ustvarjen z imenovanim dinamičnim obsegom s funkcijo OFFSET (glej podrobnosti). Pri delu v Excelu 2013 samo izberite eno od podatkovnih celic in pritisnite kombinacijo tipk Ctrl + T (ustvarite tabelo). To bo ustvarilo imenovani nabor podatkov, ki se samodejno razširi z dodajanjem novih vrstic in stolpcev.

Ustvari vrtilno tabelo:

Izberite Team Vstavi → Vrteča mizain nato v pogovornem oknu Ustvarjanje vrtilne tabelekliknite v redu.
Na seznamu polj vrtilne tabele izberite polje datum... Na levi strani vrtilne tabele se prikaže seznam datumov (slika 11).
Izberite katero koli datumsko celico, na primer A4. Na kontekstnem zavihku Analizaki se nahaja v nizu kontekstualnih zavihkov Delo z vrtilnimi tabelami, kliknite gumb Razvrščanje po področjih (glej podrobnosti). V pogovornem oknu Združevanje v skupine izberite možnost Meseci (slika 12). Kliknite v redu... Imena mesecev bodo prikazana na levi strani vrtilne tabele (slika 13).
Povlecite polje datum na območje stolpcev vrtilne tabele.
Povlecite polje Kazalo na območje Stolpci seznama polj vrtilne tabele.
Izberite polje Regijaki se prikaže v levem stolpcu vrtilne tabele.
Izberite polje dohodek, ki se prikaže v območju vrednosti vrtilne tabele.

Slika: 11. Začnite z razvrščanjem po polju datum

V tem trenutku je naša vrtilna tabela videti precej primitivno (slika 14). Resnično ne maram črk Imena vrstic in Imena stolpcev... Nepraktično je prikazati vsote za Jan načrt in Jan Dejstvo v stolpcu D itd. Toda ne skrbite za videz te vrtilne tabele, ker je razen vas ne bo videl nihče drug. Od tega trenutka bomo ustvarili ovitek poročila, katerega vir podatkov bo na novo ustvarjena vrtilna tabela.

Nadaljujte, kot sledi (slika 15).

V celico A1 vnesite ime poročila - Načrtovani in dejanski kazalniki po regijah.
Pojdite na zavihek domov, kliknite na gumb Slogi celic izberite obliko Naslov 1.
V celico A2 vnesite formulo \u003d EONTHS (DANES (); 0). Ta funkcija vrne zadnji dan tekočega meseca. Če na primer te vrstice preberete 14. avgusta 2014, bo celica A2 prikazala datum 31. avgust 2014.
Izberite celico A2. Pritisnite kombinacijo tipk Ctrl + 1, da se prikaže pogovorno okno Oblika celice... V zavihku Številka kliknite na element Vsi formati... Vnesite obliko števil po meri kot "Od meseca" MMMM "načrtovani kazalniki" (slika 16). Posledično bo izračunani datum videti kot besedilo.
V celico A5 vnesite naslov Regija.
Naslove regij vnesite v preostale celice v stolpcu A. Naslovi regij se morajo ujemati z imeni regij, prikazanimi v vrtilni tabeli.
Če je potrebno, v stolpec dodajte oznake za vsote po oddelkih.
Na dnu poročila dodajte vrstico Skupaj za podjetje.
V celico B4 vnesite formulo \u003d DATUM (LETO ($ A $ 2); STOLPEC (A1); 1). Ta formula vrne datume 01.01.2014, 01.02.2104 itd., Prve dni vseh 12 mesecev tekočega leta.
Izberite celico B4. Pritisnite kombinacijo tipk Ctrl + 1, da odprete okno Oblika celice... V zavihku Številka V poglavju Vsi formati vnesite obliko številke po meri Mmm... Ta oblika prikazuje tričrkovno ime meseca. Poravnajte besedilo na desni strani celice.
Kopirajte vsebino celice B4 v obseg C4: M4. Na vrhu vrtilne tabele se prikaže vrstica z imeni mesecev.
V celico B5 vnesite formulo \u003d IF (MESEC (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Dejstvoin za sedanjost in prihodnost - Načrtujte.
V celico N5 dodajte naslov Izid... V celico O4 - Izid, O5 - Načrtujte, P5 - % odstopanja.
Vnesite običajne Excelove formule, ki se uporabljajo za izračun skupnih oddelkov, skupne vrstice podjetja, stolpca skupnega seštevka in stolpca% variance:
1. v celico B8 vnesite formulo \u003d SUM (B6: B7) in jo kopirajte v druge celice v vrstici;
2. v celico N6 vnesite formulo \u003d SUM (B6: M6) in jo kopirajte v druge celice v stolpcu;
3. v celico P6 vnesite formulo \u003d IFERROR ((N6 / O10) -1; 0) in jo kopirajte v druge celice v stolpcu;
4. v celico B13 vnesite formulo \u003d SUM (B10: B12) in jo kopirajte v druge celice v vrstici;
5. v celico B17 vnesite formulo \u003d SUM (B15: B16) in jo kopirajte v druge celice v vrstici;
6. v celico B19 vnesite formulo \u003d SUM (B6: B18) / 2 in jo kopirajte v druge celice v vrstici.
Slog naslova 4 uporabite na nalepkah v stolpcu A in na naslovih v vrsticah 4 in 5.
Za obseg celic B6: O19 izberite format številke # ## 0.
Za celice v stolpcu P izberite obliko števila 0,0%.

Slika: 15. Poročilo zavijte pred dodajanjem formul GET.DATA.PERSONAL.TABLES

Izberite celico B6 lupine poročila. Ta celica ustreza severovzhodni regiji in dejanskim podatkom za januar.

Enter \u003d (enak znak), da začnete vnašati formulo.
Pojdite na list iz vrtilne tabele in kliknite celico, ki ustreza severovzhodni regiji in dejanske številke za januar - C12 (slika 17).
Pritisnite tipko Enterza dokončanje vnosa formule in vrnitev v lupino poročila. Kot rezultat bo Excel v celico B6 dodal funkcijo GET PIVOTTABLE DATA. V celici bo prikazana vrednost 277.435 USD.

Argumenti funkcije:

Podatkovno_polje. Polje iz območja vrednosti vrtilne tabele. Prosimo, upoštevajte: v tem primeru se uporablja polje dohodek, vendar ne Znesek v polju Dohodek.
Vrteča miza. S tem parametrom vas Microsoft vpraša: "Katero vrtilno tabelo želite uporabiti?" Dovolj je določiti eno od celic vrtilne tabele. Vnos „Sl. 11-14 '! $ A $ 3 se nanaša na prvo celico v vrtilni tabeli, kamor se vnašajo podatki. Ker lahko v našem primeru določite katero koli celico, povezano z vrtilno tabelo, pustite argument nespremenjen. Naslov celice $ A $ 3 je primeren v vseh pogledih.
Polje 1; element 1. V samodejno ustvarjeni formuli je izbrano ime polja Regija, in kot vrednost polja - Severovzhod... Tu je razlog za težave, ki se pojavijo pri delu s funkcijo GET DATA.PUMP.TABLE. Samodejno izbranih vrednosti ni mogoče kopirati, ker so trdo kodirane. Če boste torej formule kopirali v celotno območje poročila, jih boste morali ročno spremeniti. Zamenjajte Severovzhod s referenco celice v obliki $ A6. Če postavite znak za dolar pred stolpec A, ugotovite, ali je mogoče sklic v vrstici spremeniti, ko formulo kopirate v celice v stolpcu.
Polje 2; točka 2. Ta par argumentov definira polje datum z vrednostjo 1. Če je bila prvotna vrtilna tabela razvrščena po mesecih, polje za mesec ohrani prvotno ime polja datum... Številčna vrednost meseca je 1, kar ustreza januarju. Težko je priporočljivo uporabiti takšno vrednost pri ustvarjanju ogromnih formul, ki so nastavljene v več deset ali celo na stotine poročevalnih celic. Bolje uporabiti formulo, ki izračuna vrednosti polj datum, kot je formula v celici B4. Namesto 1 v tem primeru lahko uporabite formulo MESEC (v 4 USD). Znak za dolar pred 4 označuje, da lahko formula polju dodeli vrednosti datum glede na druge mesece, ko se formula kopira v celice v vrstici.
Polje 3; element 3. V tem primeru se ime polja samodejno dodeli Kazalo in vrednost polja Dejstvo... Te vrednosti so pravilne za januar, za naslednje mesece pa bo treba vrednost polja spremeniti v načrt. Spremenite trdno kodirano vrednost polja Dejstvo do povezave B $ 5.
Polje 4; Element 4. Ti argumenti se ne uporabljajo, ker polj je konec.

Slika: 19. Po zaključku urejanja je formula GET.DATA.PERSONAL.TABLE primerna za kopiranje v vse celice obsega

Kopirajte formulo v poljubne prazne celice v stolpcih B: M, kjer se izračunajo rezultati. Zdaj, ko poročilo vsebuje realne številke, lahko končno prilagodite širine stolpcev.

Slika: 20. Prilagodite postavitev polj v območju stolpca, tako da se stolpec prikaže Okvirni načrt

Slika: 21. Končno poročilo, ki ga je mogoče predstaviti upravitelju

Posodobite poročilo.Če želite posodobiti poročilo s podatki za prihodnje mesece, sledite tem korakom.

Vstavite dejanske številke pod prvotni nabor podatkov. Ker so izvorni podatki v obliki tabele, se oblikovanje tabele samodejno razširi v nove podatkovne vrstice. Prav tako razširja definicijo prvotne vrtilne tabele (v datoteki Excel sem že dodal dejanske številke za celo leto).
Pojdite na vrtilno tabelo. Z desno miškino tipko izberite in izberite Osveži... Videz vrtilne tabele se bo spremenil, vendar je v redu.
Pojdite na lupino poročila. Načeloma je bilo za posodobitev poročila že storjeno vse, vendar testiranje rezultatov ne škodi. Spremenite na primer formulo v celici A2 na to: \u003d EON MESECI (DANES () +31 ; 0) in poglejte, kaj se bo zgodilo.

Nisem si mislil, da bom kdaj rekel naslednje: »Funkcija GET.DATA.PUMP.TABLE je največji blagoslov. Kako smo že prej obstajali brez nje? "

Za vrtilne tabele je funkcija GET.DATA.PIVOTTABLE funkcija, ki vrne podatke, shranjene v poročilu vrtilne tabele.

Če želite hiter dostop do funkcije, morate v celico vnesti enak znak (\u003d) in v vrtilni tabeli izbrati želeno celico. Excel bo samodejno ustvaril funkcijo GET PIVOTTABLE DATA.

Onemogoči ustvarjanje GetPivotData

Če želite izklopiti samodejno generiranje funkcije GET PIVOTTABLE, izberite katero koli celico v vrtilni tabeli, kliknite zavihek Delo z vrtilnimi tabelami -\u003e Možnostiv skupino Vrteča miza.Kliknite puščico navzdol poleg zavihka Parametri.V spustnem meniju počistite polje UstvaritiGetPivotData.

Uporaba referenc celic v funkciji GET DATA.PUMP.TABLE

Namesto da navedete imena elementov ali polj v funkciji GET.DATA.PUMP.TABLE, se lahko sklicujete na celice na delovnem listu. V spodnjem primeru celica E3 vsebuje ime izdelka, formula v celici E4 pa se nanaša nanjo. Posledično bo vrnjena celotna količina peciva.

Uporaba sklicev na polja vrtilne tabele

Ni vprašanj o tem, kako delujejo povezave do elementov vrtilne tabele, težave se pojavijo, če se želimo sklicevati na podatkovno polje.

V primeru celica E3 vsebuje ime podatkovnega polja "Količina" in lepo bi bilo, če bi se sklicevali na to celico v funkciji, namesto da bi ime polja imeli v formuli GET.DATA.PUMP.TABLE.

Če pa spremenimo prvi argument podatkovno poljena sklic na celico E3 nam bo Excel vrnil napako #REF!

PRIDOBITE OSEBNE PODATKE ZA MIZO (E3; $ A $ 3)

Težavo rešite preprosto z dodajanjem praznega niza (“”) na začetek ali konec sklica na celico.

PRIDOBITE PODATKE O OSEBNI TABELI (E3 & ""; $ A $ 3)

Preprost popravek formule bo vrnil pravilno vrednost.

Uporaba datumov v funkciji GET.DATA.PUMP.TABLE

Če uporabljate datume v funkciji GET PIVOTTABLE DATA, lahko imate težave, tudi če je datum prikazan v vrtilni tabeli. Spodnja formula je na primer datum »21.4.2013«, vrtilna tabela pa vsebuje polje z datumi prodaje. Vendar formula v celici E4 vrne napako.

GET.DATA.PERSONAL.TABLE ("Obseg"; $ A $ 3; "Datum"; "21.4.2013")

Da bi se izognili napakam, povezanim z datumi, lahko uporabite enega od naslednjih načinov:

Primerjajte formate datumov v formulah in vrtilni tabeli
Uporabite funkcijo DATEVALUE
Uporabite funkcijo DATE
Glejte celico s pravilnim datumom

Primerjajte formate datumov v formuli in vrtilni tabeli.

Če želite dobiti pravilen rezultat, se pri uporabi funkcije GET PIVOTTABLE DATA prepričajte, da so datumski formati argumenta formule in vrtilne tabele enaki.

V celici E4 formula uporablja datumsko obliko »DD.MM.LLLL«, zato so bile vrnjene pravilne informacije.

Uporaba funkcije DATEVALUE

Namesto da ročno vnesete datum v formulo, lahko za vrnitev datuma dodate funkcijo DATEVALUE.

V celico E4 se datum vnese s funkcijo DATEVALUE in Excel vrne zahtevane podatke.

RECEIVE.SUMMARY.TABLE.DATA ("Obseg"; $ A $ 3; "Datum"; DATOV. ("21.4.2013"))

Uporaba funkcije DATE

Namesto ročnega vnosa datuma v formulo lahko uporabite funkcijo DATUM, ki bo pravilno vrnila potrebne podatke.

RECEIVE.SUMMARY.TABLE.DATA ("Obseg"; $ A $ 3; "Datum"; DATUM (2013; 4; 21))

Referenca celice datuma

Namesto da ročno vnesete datum v formulo, se lahko sklicujete na celico, ki vsebuje datum (v kateri koli obliki, v kateri Excel podatke razlaga kot datume). V primeru v celici E4 se formula nanaša na celico E3 in Excel vrne pravilne podatke.

RECEIVE.SUMMARY.TABLE.DATA ("Obseg"; $ A $ 3; "Datum"; E3)