Získajte príklad údajov kontingenčnej tabuľky programu Excel

Zanechať komentár 6,950

Predtým som už hovoril o tom, ako keď hovoríte o bunke kontingenčnej tabuľky namiesto bežného odkazu sa vráti funkcia GET.PIVORTABLE.DATA (pozri). Ak máš záujem Ako Na prekonanie tejto nepríjemnosti vám odporúčam odkázať na spomínanú poznámku. Ak máš záujem, Prečo? toto sa stane a tiež čo pozitívne aspekty funkcia má GET.DATA.PIVOT.TABLE, potom navrhujem fragment knihy od Jelen, Alexander. (15. kapitola). Uvažovaná technika nám umožní vyrovnať sa s mnohými problémami, ktoré spôsobujú bolesti hlavy používateľom kontingenčných tabuliek, najmä:

Keď obnovíte kontingenčnú tabuľku, predtým použité formátovanie zmizne. Formáty čísel sa stratia. Výsledky úpravy šírky stĺpcov zmiznú.
Neexistuje jednoduchý spôsob vytvorenie asymetrickej kontingenčnej tabuľky. Jedinou možnosťou je použiť pomenované množiny, ale táto metóda je dostupná len pre tých, ktorí používajú kontingenčné tabuľky údajového modelu namiesto bežných kontingenčných tabuliek.
Excel si nepamätá šablóny. Ak potrebujete vytvárať kontingenčné tabuľky znova a znova, budete musieť prerobiť zoskupovanie, použiť vypočítané polia a členov a vykonať množstvo ďalších podobných úloh.

V skutočnosti všetko, čo je tu opísané, nie je nové. Navyše, podobné techniky sa používajú od Excelu 2002. Moja komunikácia s používateľmi však ukazuje, že menej ako 1 % ich pozná. Jedinou otázkou, ktorú používatelia majú, je, ako vypnúť podivnú funkciu GET.PIRTTABLE.DATA. Je to škoda…

Stiahnite si poznámku vo formáte alebo formáte, príklady vo formáte

No začnime pekne po poriadku.

Ako opustiť problematickú funkciu GET.PIVOT.TABLE.DATA

Funkcia GET.PIVO.TABLE.DATA už dlho trápila mnohých používateľov. Úplne náhle, bez akéhokoľvek varovania, sa správanie kontingenčných tabuliek v Exceli 2002 zmenilo. Keď začnete vytvárať vzorce mimo kontingenčnej tabuľky, ktoré odkazujú na jej údaje, táto funkcia sa objaví z ničoho nič.

Predpokladajme, že v kontingenčnej tabuľke znázornenej na obr. 1, musíte porovnať údaje za rok 2015 a 2014.

Ryža. 1. Pôvodná kontingenčná tabuľka

Pridajte nadpis „% rastu“ do bunky D3.
Skopírujte formát z bunky C3 do bunky D3.
Do bunky D4 zadajte znamienko rovnosti.
Kliknite na bunku C4.
Ak chcete reprezentovať operáciu delenia, zadajte / (lomka).
Kliknite na bunku B4.
Napíšte -1 a stlačte kombináciu klávesov zostať v tej istej cele. Formátujte výsledok ako percento. Uvidíte, že región Západ zaznamenal pokles príjmov o 43,8 % (obrázok 2). Nie veľmi dobré výsledky.
Po dokončení zadávania prvého vzorca vyberte bunku D4.
Dvakrát kliknite na malý štvorec umiestnený v pravom dolnom rohu bunky. Toto pole predstavuje rukoväť výplne, ktorá vám umožňuje skopírovať vzorec tak, aby vyplnil celý stĺpec zostavy.

Po dokončení skopírovania vzorca, pri pohľade na obrazovku si uvedomíte, že niečo nie je v poriadku – každý región vykázal za rok pokles o 43,8 % (obr. 3).

Ryža. 3. Po dokončení kopírovania vzorca do všetkých buniek v stĺpci uvidíte, že každá oblasť zaznamenala pokles o 43,8 %

Je nepravdepodobné, že sa to stane v skutočný život. Každý používateľ vám povie, že po vykonaní krokov uvedených vyššie Excel vytvorí vzorec = C4/B4–1. Vráťte sa do bunky D4 a venujte pozornosť riadku vzorcov (obrázok 4). Len nejaký druh diabla! Jednoduchý vzorec =C4/B4–1 už neexistuje. Namiesto toho program nahradí komplexnú konštrukciu funkciou GET.PIVORTABLE.DATA. Prečo tento vzorec poskytuje správne výsledky v bunke D4, ale po skopírovaní do buniek pod ním odmieta fungovať?

Prvá reakcia každého používateľa na to, čo sa stalo, bude nasledujúca: „Čo je to za zvláštnu konštrukciu GET.PIVOT.TABLE.DATA, ktorá zničila moju správu?“ Väčšina používateľov sa bude chcieť tejto funkcie okamžite zbaviť. Niektorí si položia otázku: „Prečo nám spoločnosť Microsoft poskytla túto funkciu?

Nič také tam nebolo Excel časy 2000. Akonáhle som sa začal pravidelne stretávať s funkciou GET.PIVOT.TABLE.DATA, jednoducho som ju nenávidel. Keď sa ma na jednom zo seminárov niekto opýtal, ako by sa to dalo využiť v prospech veci, zostal som v nemom úžase. Nikdy som si takúto otázku nepoložil! Podľa môjho názoru a podľa názoru väčšiny používateľov Excelu bola funkcia GET.DATA.PICTTABLE produktom zla, ktorý nemal nič spoločné so silami dobra. Našťastie existujú dva spôsoby, ako túto funkciu vypnúť.

Blokovanie funkcie GET.PIVOT.TABLE.DATA zadaním vzorca. Existuje jednoduchý spôsob, ako zabrániť zobrazeniu funkcie GET.PIVOTABLE.DATA. Aby ste to dosiahli, musíte vytvoriť vzorec bez použitia myši alebo kurzorových kláves. Postupujte podľa týchto krokov.

Prejdite do bunky D4 a zadajte = (rovná sa).
Zadajte C4.
Zadajte / (lomka označujúca operáciu delenia).
Zadajte B4.
Zadajte -1.
Kliknite Zadajte.

Teraz ste vytvorili regulár Vzorec programu Excel, ktorý je možné skopírovať do buniek pod stĺpcom a pomocou ktorého získate správne výsledky (obr. 5). Ako vidíte, v oblastiach mimo kontingenčnej tabuľky môžete vytvárať vzorce, ktoré odkazujú na údaje v kontingenčnej tabuľke. A tí, ktorí neveria, že je to možné, nech urobia opísané kroky sami.

Ryža. 5. Stačí zadať =С4/В4–1 z klávesnice a vzorec bude fungovať tak, ako má

Niektorí používatelia sa budú cítiť nepríjemne, pretože je narušené obvyklé poradie zadávania vzorcov. Okrem toho je navrhovaná možnosť náročnejšia na prácu. Ak ste jedným z týchto používateľov, druhý spôsob je pre vás...

Vypnutie funkcie GET.PIVOT.TABLE.DATA. Funkciu GET.PIVOTABLE.DATA môžete natrvalo zakázať. Kliknite na pás s ponukami Súbor → možnosti. V okne, ktoré sa otvorí možnostiExcel prejdite na vklad Vzorce a zrušte začiarknutie políčka vedľa možnosti Použiť funkciuGetPivotData pre odkazy v kontingenčnej tabuľke. Kliknite Dobre.

Alternatívna možnosť. Kliknite na kontingenčnú tabuľku a na kontextovú kartu, ktorá sa zobrazí Analýza kliknite na rozbaľovací zoznam vedľa tlačidla možnosti. Zrušte začiarknutie políčka vedľa položky Vytvorte GetPivotData(obr. 7). V predvolenom nastavení je začiarkavacie políčko povolené.

Prečo nám Microsoft ponúkol funkciu GET.PICTTABLE.DATA. Ak je táto funkcia taká hrozná, prečo ju spoločnosť Microsoft predvolene povolila? Prečo sa starajú o zachovanie podpory pre túto funkciu v nových verziách Excelu? Sú si vedomí nálady používateľov? A prejdeme k tomu najzaujímavejšiemu...

Použitie funkcie GET.PIVORTABLE.DATA na zlepšenie kontingenčných tabuliek

Kontingenčné stoly sú skvelým vynálezom ľudstva. Kontingenčná tabuľka sa vytvorí len niekoľkými kliknutiami, čím sa eliminuje potreba pokročilých filtrov, BDSUMM a údajových tabuliek. Kontingenčné tabuľky vám umožňujú vytvárať jednostránkové zostavy založené na obrovských množstvách údajov. Tieto výhody zatieňujú niektoré nevýhody kontingenčných tabuliek, ako je nevýrazné formátovanie a potreba previesť kontingenčné tabuľky na hodnoty pre ďalšie prispôsobenie. Na obr. Obrázok 8 zobrazuje typický proces vytvárania kontingenčnej tabuľky. V tomto prípade všetko začína počiatočnými údajmi. Vytvárame kontingenčnú tabuľku a používame všetky možné techniky na jej prispôsobenie a zlepšenie. Niekedy prevádzame kontingenčnú tabuľku na hodnoty a robíme konečné formátovanie.

Nová technika vytvárania kontingenčných tabuliek, ktorú navrhol Rob Colley (vývojár spoločnosti Microsoft) a o ktorej sa bude hovoriť ďalej, je vylepšením vyššie opísaného procesu. V tomto prípade sa najskôr vytvorí primitívna kontingenčná tabuľka. Túto tabuľku nie je potrebné formátovať. Potom prejde jednokrokovým, relatívne náročným procesom, aby sa vytvoril pekne naformátovaný shell, v ktorom bude umiestnená záverečná správa. Potom sa funkcia GET.PIVOT.TABLE.DATA používa na rýchle naplnenie zostavy umiestnenej v shelli údajmi. Po prijatí nových údajov ich môžete umiestniť na hárok, aktualizovať primitívnu kontingenčnú tabuľku a vytlačiť zostavu umiestnenú v shell (obr. 9). Táto technika má množstvo nepopierateľných výhod. Nemusíte sa napríklad starať o formátovanie správy hneď po jej vytvorení. Proces vytvárania kontingenčných tabuliek sa stáva takmer úplne automatizovaným.

Nasledujúce časti pojednávajú o tom, ako vytvoriť dynamickú zostavu, ktorá zobrazuje skutočné hodnoty za minulé mesiace a ciele pre budúce mesiace.

Vytvorenie primitívnej kontingenčnej tabuľky. Zdrojové údaje (obr. 10) sú prezentované vo forme transakcií obsahujúcich informácie o plánovaných a skutočných ukazovateľoch pre každý región, v ktorom má spoločnosť pobočky. Plánované ukazovatele sú rozpísané na úrovni mesiaca a skutočné ukazovatele na úrovni jednotlivých dní. Plánované ukazovatele sa vytvárajú na rok dopredu a skutočné ukazovatele sa vytvárajú za minulé mesiace. Keďže zostava sa bude aktualizovať každý mesiac, tento proces sa výrazne zjednoduší, ak sa veľkosť zdroja údajov kontingenčnej tabuľky zväčší, keď sa na spodok pridajú nové údaje. V starších verziách Excelu sa takýto zdroj údajov vytvoril pomocou pomenovaného dynamického rozsahu pomocou funkcie OFFSET (viac informácií nájdete v časti). Pri práci v Exceli 2013 jednoducho vyberte jednu z dátových buniek a stlačte kombináciu klávesov Ctrl+T (vytvorte tabuľku). Tým sa vytvorí pomenovaná množina údajov, ktorá sa automaticky rozšíri po pridaní nových riadkov a stĺpcov.

Teraz vytvoríme kontingenčnú tabuľku. Funkcia GET.PIVORTABLE.DATA je pomerne výkonná, ale môže vrátiť iba hodnoty, ktoré sa zobrazujú v skutočnej kontingenčnej tabuľke. Táto funkcia nedokáže prezerať vyrovnávaciu pamäť a vypočítať položky, ktoré nie sú v kontingenčnej tabuľke.

Vytvorte kontingenčnú tabuľku:

Vyberte tím Vložiť → Kontingenčná tabuľka a potom v dialógovom okne Vytvorenie kontingenčnej tabuľky kliknite OK.
V zozname polí kontingenčnej tabuľky vyberte pole dátum. Na ľavej strane kontingenčnej tabuľky sa zobrazí zoznam dátumov (obr. 11).
Vyberte ľubovoľnú bunku dátumu, napríklad A4. Na kontextovej karte Analýza umiestnené v skupine kontextových kariet Práca s kontingenčnými tabuľkami, kliknite na tlačidlo Zoskupiť podľa poľa(pozri detaily). V dialógovom okne Zoskupovanie vyberte možnosť Mesiace(obr. 12). Kliknite OK. Názvy mesiacov sa zobrazia na ľavej strane kontingenčnej tabuľky (obr. 13).
Potiahnite pole dátum do oblasti stĺpcov kontingenčnej tabuľky.
Potiahnite pole Index do oblasti stĺpcov zoznamu polí kontingenčnej tabuľky.
Vyberte pole región, ktorý sa zobrazí v ľavom stĺpci kontingenčnej tabuľky.
Vyberte pole Príjem ktorý sa zobrazí v oblasti hodnôt kontingenčnej tabuľky.

Ryža. 11. Začnite zoskupovaním podľa poľa dátum

V tejto fáze naša kontingenčná tabuľka vyzerá dosť primitívne (obr. 14). Naozaj sa mi nepáčia nápisy Názvy liniek A Názvy stĺpcov. Nie je praktické zobrazovať súčty pre Ján Plán A Ján Fakt v stĺpci D atď. Ale nebojte sa vzhľad túto súhrnnú tabuľku, pretože ju okrem vás nikto iný neuvidí. Odteraz vytvoríme shell zostavy, ktorého zdrojom údajov bude novovytvorená kontingenčná tabuľka.

Vytvorenie shellu správy. Vložte prázdny hárok do zošita. Odložme na chvíľu nástroje na prácu s kontingenčnými tabuľkami a prejdime k bežným excelovským nástrojom. Našou úlohou je pomocou vzorcov a formátovania vytvoriť krásnu správu, ktorú nie je trápne ukázať manažérovi.

Postupujte podľa týchto krokov (obr. 15).

Do bunky A1 zadajte názov zostavy - Plánované a skutočné ukazovatele podľa regiónov.
Prejdite na kartu Domov, kliknite na tlačidlo Štýly buniek vyberte formát Nadpis 1.
Do bunky A2 zadajte vzorec =MESIAC(DNES();0). Táto funkcia vráti posledný deň aktuálneho mesiaca. Ak to napríklad čítate 14. augusta 2014, v bunke A2 sa zobrazí dátum 31. augusta 2014.
Vyberte bunku A2. Stlačením kombinácie klávesov Ctrl+1 zobrazíte dialógové okno Formát bunky. Na karte číslo kliknite na položku Všetky formáty. Zadajte vlastný formát čísla ako "Od mesiaca "MMMM" plánované ukazovatele"(obr. 16). V dôsledku toho sa vypočítaný dátum zobrazí ako text.
Do bunky A5 zadajte názov región.
Zadajte názvy oblastí do zostávajúcich buniek v stĺpci A. Názvy oblastí sa musia zhodovať s názvami oblastí uvedenými v kontingenčnej tabuľke.
V prípade potreby pridajte štítky do stĺpca pre súčty oddelení.
V spodnej časti prehľadu pridajte riadok Celkom za spoločnosť.
Do bunky B4 zadajte vzorec =DÁTUM(ROK($A$2),STĹPEC (A1),1). Tento vzorec vráti dátumy 01/01/2014, 02/01/2104 atď., prvé dni všetkých 12 mesiacov aktuálneho roka.
Vyberte bunku B4. Okno otvoríte stlačením kombinácie kláves Ctrl+1 Formát bunky. Na karte číslo V kapitole Všetky formáty zadajte vlastný formát čísla MMM. Tento formát zobrazuje názov mesiaca s tromi písmenami. Zarovnajte text k pravému okraju bunky.
Skopírujte obsah bunky B4 do rozsahu C4:M4. V hornej časti kontingenčnej tabuľky sa zobrazí riadok s názvami mesiacov.
Do bunky B5 zadajte vzorec = AK(MESIAC(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt a pre súčasnosť a budúcnosť - Plán.
Pridajte nadpis do bunky N5 Spodná čiara. Do bunky O4 - Spodná čiara, O5 - Plán, P5 - % odchýlka.
Zadajte zvyčajné vzorce programu Excel, ktoré sa používajú na výpočet súčtu oddelení, riadkov súčtu spoločnosti, stĺpcov celkového súčtu a stĺpcov % rozptylu:
1. do bunky B8 zadajte vzorec =SUM(B6:B7) a skopírujte ho do iných buniek v riadku;
2. do bunky N6 zadajte vzorec =SUM(B6:M6) a skopírujte ho do iných buniek v stĺpci;
3. do bunky P6 zadajte vzorec =IFERROR((N6/O10)-1;0) a skopírujte ho do ostatných buniek v stĺpci;
4. do bunky B13 zadajte vzorec =SUM(B10:B12) a skopírujte ho do iných buniek v riadku;
5. do bunky B17 zadajte vzorec =SUM(B15:B16) a skopírujte ho do iných buniek v riadku;
6. Do bunky B19 zadajte vzorec =SUM(B6:B18)/2 a skopírujte ho do iných buniek v riadku.
Použite štýl Nadpis 4 na titulky v stĺpci A a nadpisy v riadkoch 4 a 5.
Pre rozsah buniek B6:O19 vyberte formát čísla # ##0.
Pre bunky stĺpca P vyberte formát čísla 0,0 %.

Dokončili sme teda vytváranie shellu správy znázorneného na obr. 15. Táto správa obsahuje všetko požadované formátovanie. Nasledujúca časť ukazuje, ako použiť funkciu GET.PIVORTABLE.DATA na dokončenie zostavy.

Ryža. 15. Pred pridaním vzorcov nahláste shell GET.PIVOT.TABLE.DATA

Použitie funkcie GET.PIVORTABLE.DATA na naplnenie prostredia zostavy údajmi. Odteraz budete môcť využívať všetky výhody používania funkcie GET.PIVOT.TABLE.DATA. Ak ste zrušili začiarknutie políčka, ktoré povolilo túto funkciu, vráťte sa späť na príslušné nastavenie a vráťte začiarkavacie políčko (pozrite si popis na obrázku 6 alebo 7).

Vyberte bunku B6 prostredia zostavy. Táto bunka zodpovedá severovýchodnej oblasti a skutočným údajom za január.

Zadajte = (rovná sa), aby ste začali zadávať vzorec.
Prejdite na hárok z kontingenčnej tabuľky a kliknite na bunku, ktorá zodpovedá severovýchodnej oblasti a skutočným ukazovateľom za január - C12 (obr. 17).
Stlačte kláves Zadajte na dokončenie zadávania vzorca a návrat do prostredia zostavy. Výsledkom je, že Excel pridá funkciu GET.PIOTTABLE.DATA do bunky B6. V bunke sa zobrazí hodnota 277 435 USD.

Toto číslo si zapamätajte, pretože ho budete potrebovať pri porovnávaní s výsledkami vzorca, ktorý budete neskôr upravovať. Vzorec vygenerovaný programom je nasledujúci: =GET.DATA.PIVORTABLE(" Príjem " ;'Obr. 11-14′!$A$3; "Región" ; "Severovýchod" ; "Dátum" ;1; " Indikátor"; "Fakt"). Ak ste doteraz funkciu GET.PIOTTABLE.DATA ignorovali, je načase sa na ňu pozrieť bližšie. Na obr. 18 tento vzorec sa zobrazí v režime úprav spolu s nápovedou.

Argumenty funkcie:

Dátové_pole. Pole z oblasti hodnôt kontingenčnej tabuľky. Poznámka: v tomto prípade sa používa pole Príjem, ale nie Suma podľa poľa Príjem.
Kontingenčná_tabuľka. Pri tejto možnosti sa vás Microsoft opýta: „Ktorú kontingenčnú tabuľku chcete použiť?“ Stačí zadať jednu z buniek kontingenčnej tabuľky. Záznam „Obr. 11-14’!$A$3 označuje prvú bunku kontingenčnej tabuľky, do ktorej sa zadávajú údaje. Keďže v našom prípade môžeme zadať akúkoľvek bunku súvisiacu s kontingenčnou tabuľkou, ponechajte argument nezmenený. Adresa bunky $A$3 je vhodná vo všetkých ohľadoch.
Pole 1; prvok 1. V automaticky vygenerovanom vzorci sa ako názov poľa vyberie názov región a ako hodnota poľa - Severovýchod. Práve tu spočívajú problémy, ktoré vznikajú pri práci s funkciou GET.PIVOT.TABLE.DATA. Automaticky vybrané hodnoty nemožno skopírovať, pretože sú pevne zakódované. Preto, ak sa vzorce skopírujú v celej oblasti zostavy, budete ich musieť zmeniť manuálne. Namiesto hodnoty Severovýchod nahraďte odkaz na bunku v tvare $ A6. Zadaním znaku dolára pred názvom stĺpca A určíte, že riadkovú časť odkazu možno zmeniť pri kopírovaní vzorca do buniek stĺpca.
Pole 2; prvok 2. Tento pár argumentov určuje pole dátum s hodnotou 1. Ak bola pôvodná kontingenčná tabuľka zoskupená podľa mesiaca, pole mesiaca si zachová pôvodný názov poľa dátum. Číselná hodnota mesiaca je 1, čo zodpovedá januáru. Pri vytváraní obrovských vzorcov, ktoré sú špecifikované v desiatkach alebo dokonca stovkách buniek zostavy, je sotva vhodné použiť takúto hodnotu. Je lepšie použiť vzorec, ktorý vypočíta hodnoty polí dátum, podobne ako vzorec v bunke B4. Namiesto 1 v tomto prípade môžete použiť vzorec MONTH(B$4). Znak dolára pred 4 znamená, že vzorec môže priradiť hodnoty do poľa dátum na základe iných mesiacov, keď sa vzorec skopíruje do buniek riadku.
Pole 3; prvok 3. V tomto prípade sa názov poľa priradí automaticky Index a hodnotu poľa Fakt. Tieto hodnoty sú správne pre január, ale pre nasledujúce mesiace bude potrebné zmeniť hodnotu poľa na Plán. Zmeňte pevne zakódovanú hodnotu poľa Fakt za odkaz 5 B$.
Pole 4; prvok 4. Tieto argumenty sa nepoužívajú, pretože polia skončili.

Nový vzorec je znázornený na obr. 19. Za minútu sa namiesto pevne zakódovaného vzorca určeného na prácu s jednou hodnotou vytvoril flexibilný vzorec, ktorý možno skopírovať do všetkých buniek množiny údajov. Stlačte kláves Zadajte a získate rovnaký výsledok ako pred úpravou vzorca. Upravený vzorec má nasledujúci tvar: =GET.DATA.PIVORTABLE("Výnosy" ;'Obr. 11-14′!$A$3; "Región" ;$A6; "Dátum" ;MESIAC(B$4); "Ukazovateľ " ; 5 $)

Ryža. 19. Po dokončení úprav je vzorec GET.PIOTTABLE.DATA vhodný na kopírovanie do všetkých buniek v rozsahu

Skopírujte vzorec do všetkých prázdnych buniek v stĺpcoch B:M, kde sa vypočítavajú výsledky. Teraz, keď zostava obsahuje skutočné číselné hodnoty, môžete vykonať posledné úpravy šírky stĺpcov.

V ďalšom kroku nakonfigurujeme vzorec GET.DATA.PICTTABLE na výpočet konečných plánovaných ukazovateľov. Ak jednoducho skopírujete vzorec do bunky O6, zobrazí sa chybové hlásenie #LINK! Dôvod, prečo sa táto chyba zobrazuje, je slovo Spodná čiara v bunke O4 nie je názov mesiaca. Na zabezpečenie správneho fungovania funkcie GET.PIVORTABLE.DATA musí byť požadovaná hodnota v kontingenčnej tabuľke. Ale keďže v pôvodnej kontingenčnej tabuľke pole Index je druhé pole v oblasti stĺpcov, stĺpec údajov Výsledok plánu vlastne neprítomný. Presuňte pole Index tak, aby sa stal prvým v oblasti stĺpca (obr. 20).

Ryža. 20. Upravte rozloženie polí v oblasti stĺpcov tak, aby sa zobrazil stĺpec Plánovať výsledok

Porovnajte s obr. 14. Tam, v oblasti STĹPCA, bolo prvé pole dátum, čo viedlo k tomu, že najprv boli stĺpce zoskupené podľa dátumu a v rámci každého mesiaca podľa plánu/skutočnosti. Teraz je pole Indikátor umiestnené ako prvé a v súhrne sú na prvom mieste stĺpce Plán, vnútri zoradené podľa mesiaca a potom idú všetky stĺpce Fakt.

Vráťte sa do hárku správy, prejdite do bunky O6, zadajte = (rovná sa) a pozrite sa na bunku N12 na hárku kontingenčnej tabuľky zodpovedajúcej plánovaným výsledkom pre oblasť Severovýchod. Kliknite Zadajte. Výsledný vzorec =GET.DATA.PIVORTABLE("Príjem";'Obr. 11-14′!$A$3;"Región";"Severovýchod";"Ukazovateľ";"Plán"). Upravte to: = GET. SUMMARY.TABLE DATA(“Príjmy”;’Obr. 11-14′!$A$3;”Región”;$A6;”Indikátor”;O$5). Skopírujte tento vzorec do ďalších buniek v stĺpci O (obr. 21). Všimnite si, že aj keď presúvate rôzne oblasti zostavy kontingenčnej tabuľky, shell funguje správne. Samozrejme, ak deaktivujete niektoré súhrnné polia, shell to nezvládne...

Ryža. 21. Záverečná správa, ktorú možno predložiť manažérovi

Teraz máte dobre naformátovaný obal zostavy, ktorý používa hodnoty z dynamickej kontingenčnej tabuľky. Prvotné vytvorenie správy síce trvalo pomerne dlho, jej aktualizácia je však otázkou niekoľkých minút.

Aktualizujte prehľad. Ak chcete prehľad aktualizovať o údaje pre budúce mesiace, postupujte takto:

Vložte skutočné metriky pod pôvodnú množinu údajov. Pretože zdrojové údaje sú vo formáte tabuľky, formátovanie tabuľky sa automaticky prenesie do nových riadkov údajov. Rozšírená je aj definícia pôvodnej kontingenčnej tabuľky (v excelovom súbore som už doplnil skutočné ukazovatele za celý rok).
Prejdite do kontingenčnej tabuľky. Kliknite pravým tlačidlom myši a vyberte Aktualizovať. Vzhľad kontingenčnej tabuľky sa zmení, ale to je v poriadku.
Prejdite do prostredia prehľadu. V zásade sa už urobilo všetko pre aktualizáciu správy, ale nezaškodí otestovať výsledky. Zmeňte vzorec v bunke A2 napríklad na tento: =MONTH(DNES() +31 ;0) a uvidíte, čo sa stane.

Pridaním nových údajov o skutočnom predaji každý mesiac sa nebudete musieť starať o opätovné vytváranie formátov, vzorcov atď. Opísaný proces aktualizácie reportov je tak jednoduchý, že navždy zabudnete na problémy, ktoré vznikli pri príprave mesačných reportov. Jediný problém môže nastať, ak dôjde k reorganizácii spoločnosti, v dôsledku čoho sa v kontingenčnej tabuľke môžu objaviť nové regióny. Ak chcete zabezpečiť, aby vaše vzorce fungovali správne, uistite sa, že súčty vo vašej zostave sa zhodujú so súčtami v kontingenčnej tabuľke. Keď sa objaví nová oblasť, jednoducho ju pridajte na hárok so škrupinou a „pretiahnite“ zodpovedajúce vzorce.

Nemyslel som si, že niekedy poviem toto: „Funkcia GET.DATA.PICTTABLE je najväčším požehnaním. Ako sme predtým bez nej existovali?

V origináli Jelen boli zdrojové údaje usporiadané tak, že ďalšie vzorce fungovali správne až v júli 2015. V excelovskom súbore priloženom k tejto poznámke som upravil zdrojové údaje, ako aj niektoré vzorce tak, aby všetko fungovalo bez ohľadu na dátum, kedy budete experimentovať s priloženým súborom Excel. Bohužiaľ, vzorce museli byť komplikované.

Pre kontingenčné tabuľky existuje funkcia GET.PIVORTABLE.DATA, ktorá vracia údaje uložené v zostave kontingenčnej tabuľky.

Ak chcete rýchlo získať prístup k funkcii, musíte do bunky zadať znamienko rovnosti (=) a zvýrazniť požadovanú bunku v kontingenčnej tabuľke. Excel vygeneruje funkciu GET.PIRTTABLE.DATA automaticky.

Zakázanie vytvárania GetPivotData

Ak chcete zakázať automatické generovanie funkcie GET.PIOTTABLE.DATA, vyberte ľubovoľnú bunku v kontingenčnej tabuľke, prejdite na kartu Práca s kontingenčnými tabuľkami -> Možnosti do skupiny Kontingenčná tabuľka. Kliknite na šípku nadol vedľa karty Možnosti. V rozbaľovacej ponuke zrušte začiarknutie položky VytvorteGetPivotData.

Použitie odkazov na bunky vo funkcii GET.PIVOTABLE.DATA

Namiesto zadávania názvov položiek alebo polí vo funkcii GET.PIOTTABLE.DATA môžete odkazovať na bunky umiestnené na hárku. V nižšie uvedenom príklade bunka E3 obsahuje názov produktu a vzorec v bunke E4 naň odkazuje. V dôsledku toho sa vráti celkový objem koláčov.

Používanie odkazov na pole kontingenčnej tabuľky

Neexistujú žiadne otázky o tom, ako fungujú odkazy na položky kontingenčnej tabuľky, ak chceme odkazovať na dátové pole.

V príklade bunka E3 obsahuje názov dátového poľa „Množstvo“ a bolo by pekné odkázať na túto bunku vo funkcii namiesto názvu poľa vo vzorci GET.PIVOT.TABLE.DATA.

Ak však zmeníme prvý argument dátové_pole na odkaz na bunku E3, Excel vráti chybu #REF!

GET.PIVORTABLE.DATA(E3,$A$3)

Problém vyriešite jednoduchým pridaním prázdneho reťazca (“”) na začiatok alebo koniec odkazu na bunku.

GET.PIVORTABLE.DATA(E3&"";$A$3)

Jednoduchá úprava vzorca vráti správnu hodnotu.

Použitie dátumov vo funkcii GET.PIVOTABLE.DATA

Ak použijete dátumy vo funkcii GET.PIVORTABLE.DATA, môžu sa vyskytnúť problémy, aj keď sa dátum zobrazí v kontingenčnej tabuľke. Napríklad argument nižšie uvedeného vzorca je dátum „04/21/2013“ a kontingenčná tabuľka obsahuje pole s dátumami predaja. Vzorec v bunke E4 však vráti chybu.

GET.PIVORTABLE.DATA("Objem";$A$3;"Dátum";"21.04.2013″)

Ak chcete zabrániť chybám dátumu, môžete použiť jednu z nasledujúcich metód:

Porovnajte formáty dátumu vo vzorci a kontingenčnej tabuľke
Použite funkciu DATEVALUE
Použite funkciu DATE
Pozrite si bunku so správnym dátumom

Porovnajte formáty dátumu vo vzorci a kontingenčnej tabuľke.

Ak chcete získať správny výsledok, pri používaní funkcie GET.PIVORTABLE.DATA sa uistite, že formáty dátumu argumentu vzorca a kontingenčnej tabuľky sú rovnaké.

V bunke E4 vzorec použil formát dátumu „DD.MM.RRRR“ a vrátil správne informácie.

Pomocou funkcie DATEVALUE

Namiesto manuálneho zadávania dátumu do vzorca môžete pridať funkciu DATEVALUE na vrátenie dátumu.

Do bunky E4 sa zadá dátum pomocou funkcie DATEVALUE a Excel vráti požadované informácie.

GET.PIVORTABLE.DATA("Volume";$A$3;"Dátum";DATEVALUE("04/21/2013"))

Pomocou funkcie DATE

Namiesto manuálneho zadávania dátumu do vzorca môžete použiť funkciu DÁTUM, ktorá vám umožní správne vrátiť potrebné informácie.

GET.PIVORTABLE.DATA("Objem";$A$3;"Dátum";DÁTUM(21. 4. 2013))

Odkaz na bunku dátumu

Namiesto manuálneho zadávania dátumu do vzorca môžete odkazovať na bunku obsahujúcu dátum (v akomkoľvek formáte, v ktorom Excel akceptuje údaje ako dátumy). V príklade v bunke E4 vzorec odkazuje na bunku E3 a Excel vráti správne údaje.

GET.PIVORTABLE.DATA("Objem";$A$3;"Dátum";E3)

Predtým som už hovoril o tom, ako keď odkazujete na bunku kontingenčnej tabuľky, namiesto bežného odkazu sa vráti funkcia GET.PIVORTABLE.DATA (pozri). Ak máš záujem Ako Na prekonanie tejto nepríjemnosti vám odporúčam odkázať na spomínanú poznámku. Ak máš záujem, Prečo? toto sa stane a tiež, aké pozitívne aspekty má funkcia GET.DATA.PICTTABLE, potom navrhujem fragment knihy od Jelen, Alexander. (15. kapitola). Uvažovaná technika nám umožní vyrovnať sa s mnohými problémami, ktoré spôsobujú bolesti hlavy používateľom kontingenčných tabuliek, najmä:

Keď obnovíte kontingenčnú tabuľku, predtým použité formátovanie zmizne. Formáty čísel sa stratia. Výsledky úpravy šírky stĺpcov zmiznú.
Neexistuje jednoduchý spôsob, ako vytvoriť asymetrickú kontingenčnú tabuľku. Jedinou možnosťou je použiť pomenované množiny, ale táto metóda je dostupná len pre tých, ktorí používajú kontingenčné tabuľky údajového modelu namiesto bežných kontingenčných tabuliek.
Excel si nepamätá šablóny. Ak potrebujete vytvárať kontingenčné tabuľky znova a znova, budete musieť prerobiť zoskupovanie, použiť vypočítané polia a členov a vykonať množstvo ďalších podobných úloh.

Stiahnite si poznámku vo formáte alebo formáte, príklady vo formáte

No začnime pekne po poriadku.

Ako opustiť problematickú funkciu GET.PIVOT.TABLE.DATA

Predpokladajme, že v kontingenčnej tabuľke znázornenej na obr. 1, musíte porovnať údaje za rok 2015 a 2014.

Ryža. 1. Pôvodná kontingenčná tabuľka

Pridajte nadpis „% rastu“ do bunky D3.
Skopírujte formát z bunky C3 do bunky D3.
Do bunky D4 zadajte znamienko rovnosti.
Kliknite na bunku C4.
Ak chcete reprezentovať operáciu delenia, zadajte / (lomka).
Kliknite na bunku B4.
Napíšte -1 a stlačte kombináciu klávesov zostať v tej istej cele. Formátujte výsledok ako percento. Uvidíte, že región Západ zaznamenal pokles príjmov o 43,8 % (obrázok 2). Nie veľmi dobré výsledky.
Po dokončení zadávania prvého vzorca vyberte bunku D4.
Dvakrát kliknite na malý štvorec umiestnený v pravom dolnom rohu bunky. Toto pole predstavuje rukoväť výplne, ktorá vám umožňuje skopírovať vzorec tak, aby vyplnil celý stĺpec zostavy.

Po dokončení skopírovania vzorca, pri pohľade na obrazovku si uvedomíte, že niečo nie je v poriadku – každý región vykázal za rok pokles o 43,8 % (obr. 3).

Ryža. 3. Po dokončení kopírovania vzorca do všetkých buniek v stĺpci uvidíte, že každá oblasť zaznamenala pokles o 43,8 %

Je nepravdepodobné, že sa to stane v reálnom živote. Každý používateľ vám povie, že po vykonaní krokov uvedených vyššie Excel vytvorí vzorec = C4/B4–1. Vráťte sa do bunky D4 a venujte pozornosť riadku vzorcov (obrázok 4). Len nejaký druh diabla! Jednoduchý vzorec =C4/B4–1 už neexistuje. Namiesto toho program nahradí komplexnú konštrukciu funkciou GET.PIVORTABLE.DATA. Prečo tento vzorec poskytuje správne výsledky v bunke D4, ale po skopírovaní do buniek pod ním odmieta fungovať?

Nič také nebolo v časoch Excelu 2000. Keď som sa začal pravidelne stretávať s funkciou GET.PIVOTABLE.DATA, jednoducho som ju nenávidel. Keď sa ma na jednom zo seminárov niekto opýtal, ako by sa to dalo využiť v prospech veci, zostal som v nemom úžase. Nikdy som si takúto otázku nepoložil! Podľa môjho názoru a podľa názoru väčšiny používateľov Excelu bola funkcia GET.DATA.PICTTABLE produktom zla, ktorý nemal nič spoločné so silami dobra. Našťastie existujú dva spôsoby, ako túto funkciu vypnúť.

Prejdite do bunky D4 a zadajte = (rovná sa).
Zadajte C4.
Zadajte / (lomka označujúca operáciu delenia).
Zadajte B4.
Zadajte -1.
Kliknite Zadajte.

Teraz ste vytvorili bežný vzorec Excel, ktorý môžete skopírovať do buniek pod stĺpcom a získať správne výsledky (obrázok 5). Ako vidíte, v oblastiach mimo kontingenčnej tabuľky môžete vytvárať vzorce, ktoré odkazujú na údaje v kontingenčnej tabuľke. A tí, ktorí neveria, že je to možné, nech urobia opísané kroky sami.

Ryža. 5. Stačí zadať =С4/В4–1 z klávesnice a vzorec bude fungovať tak, ako má

Použitie funkcie GET.PIVORTABLE.DATA na zlepšenie kontingenčných tabuliek

Nasledujúce časti pojednávajú o tom, ako vytvoriť dynamickú zostavu, ktorá zobrazuje skutočné hodnoty za minulé mesiace a ciele pre budúce mesiace.

Vytvorte kontingenčnú tabuľku:

Vyberte tím Vložiť → Kontingenčná tabuľka a potom v dialógovom okne Vytvorenie kontingenčnej tabuľky kliknite OK.
V zozname polí kontingenčnej tabuľky vyberte pole dátum. Na ľavej strane kontingenčnej tabuľky sa zobrazí zoznam dátumov (obr. 11).
Vyberte ľubovoľnú bunku dátumu, napríklad A4. Na kontextovej karte Analýza umiestnené v skupine kontextových kariet Práca s kontingenčnými tabuľkami, kliknite na tlačidlo Zoskupiť podľa poľa(pozri detaily). V dialógovom okne Zoskupovanie vyberte možnosť Mesiace(obr. 12). Kliknite OK. Názvy mesiacov sa zobrazia na ľavej strane kontingenčnej tabuľky (obr. 13).
Potiahnite pole dátum do oblasti stĺpcov kontingenčnej tabuľky.
Potiahnite pole Index do oblasti stĺpcov zoznamu polí kontingenčnej tabuľky.
Vyberte pole región, ktorý sa zobrazí v ľavom stĺpci kontingenčnej tabuľky.
Vyberte pole Príjem ktorý sa zobrazí v oblasti hodnôt kontingenčnej tabuľky.

Ryža. 11. Začnite zoskupovaním podľa poľa dátum

V tejto fáze naša kontingenčná tabuľka vyzerá dosť primitívne (obr. 14). Naozaj sa mi nepáčia nápisy Názvy liniek A Názvy stĺpcov. Nie je praktické zobrazovať súčty pre Ján Plán A Ján Fakt v stĺpci D atď. Nerobte si však starosti so vzhľadom tejto kontingenčnej tabuľky, pretože ju okrem vás nikto iný neuvidí. Odteraz vytvoríme shell zostavy, ktorého zdrojom údajov bude novovytvorená kontingenčná tabuľka.

Postupujte podľa týchto krokov (obr. 15).

Do bunky A1 zadajte názov zostavy - Plánované a skutočné ukazovatele podľa regiónov.
Prejdite na kartu Domov, kliknite na tlačidlo Štýly buniek vyberte formát Nadpis 1.
Do bunky A2 zadajte vzorec =MESIAC(DNES();0). Táto funkcia vráti posledný deň aktuálneho mesiaca. Ak to napríklad čítate 14. augusta 2014, v bunke A2 sa zobrazí dátum 31. augusta 2014.
Vyberte bunku A2. Stlačením kombinácie klávesov Ctrl+1 zobrazíte dialógové okno Formát bunky. Na karte číslo kliknite na položku Všetky formáty. Zadajte vlastný formát čísla ako "Od mesiaca "MMMM" plánované ukazovatele"(obr. 16). V dôsledku toho sa vypočítaný dátum zobrazí ako text.
Do bunky A5 zadajte názov región.
Zadajte názvy oblastí do zostávajúcich buniek v stĺpci A. Názvy oblastí sa musia zhodovať s názvami oblastí uvedenými v kontingenčnej tabuľke.
V prípade potreby pridajte štítky do stĺpca pre súčty oddelení.
V spodnej časti prehľadu pridajte riadok Celkom za spoločnosť.
Do bunky B4 zadajte vzorec =DÁTUM(ROK($A$2),STĹPEC (A1),1). Tento vzorec vráti dátumy 01/01/2014, 02/01/2104 atď., prvé dni všetkých 12 mesiacov aktuálneho roka.
Vyberte bunku B4. Okno otvoríte stlačením kombinácie kláves Ctrl+1 Formát bunky. Na karte číslo V kapitole Všetky formáty zadajte vlastný formát čísla MMM. Tento formát zobrazuje názov mesiaca s tromi písmenami. Zarovnajte text k pravému okraju bunky.
Skopírujte obsah bunky B4 do rozsahu C4:M4. V hornej časti kontingenčnej tabuľky sa zobrazí riadok s názvami mesiacov.
Do bunky B5 zadajte vzorec = AK(MESIAC(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt a pre súčasnosť a budúcnosť - Plán.
Pridajte nadpis do bunky N5 Spodná čiara. Do bunky O4 - Spodná čiara, O5 - Plán, P5 - % odchýlka.
Zadajte zvyčajné vzorce programu Excel, ktoré sa používajú na výpočet súčtu oddelení, riadkov súčtu spoločnosti, stĺpcov celkového súčtu a stĺpcov % rozptylu:
1. do bunky B8 zadajte vzorec =SUM(B6:B7) a skopírujte ho do iných buniek v riadku;
2. do bunky N6 zadajte vzorec =SUM(B6:M6) a skopírujte ho do iných buniek v stĺpci;
3. do bunky P6 zadajte vzorec =IFERROR((N6/O10)-1;0) a skopírujte ho do ostatných buniek v stĺpci;
4. do bunky B13 zadajte vzorec =SUM(B10:B12) a skopírujte ho do iných buniek v riadku;
5. do bunky B17 zadajte vzorec =SUM(B15:B16) a skopírujte ho do iných buniek v riadku;
6. Do bunky B19 zadajte vzorec =SUM(B6:B18)/2 a skopírujte ho do iných buniek v riadku.
Použite štýl Nadpis 4 na titulky v stĺpci A a nadpisy v riadkoch 4 a 5.
Pre rozsah buniek B6:O19 vyberte formát čísla # ##0.
Pre bunky stĺpca P vyberte formát čísla 0,0 %.

Ryža. 15. Pred pridaním vzorcov nahláste shell GET.PIVOT.TABLE.DATA

Vyberte bunku B6 prostredia zostavy. Táto bunka zodpovedá severovýchodnej oblasti a skutočným údajom za január.

Zadajte = (rovná sa), aby ste začali zadávať vzorec.
Prejdite na hárok z kontingenčnej tabuľky a kliknite na bunku, ktorá zodpovedá severovýchodnej oblasti a skutočným ukazovateľom za január - C12 (obr. 17).
Stlačte kláves Zadajte na dokončenie zadávania vzorca a návrat do prostredia zostavy. Výsledkom je, že Excel pridá funkciu GET.PIOTTABLE.DATA do bunky B6. V bunke sa zobrazí hodnota 277 435 USD.

Argumenty funkcie:

Dátové_pole. Pole z oblasti hodnôt kontingenčnej tabuľky. Poznámka: v tomto prípade sa používa pole Príjem, ale nie Suma podľa poľa Príjem.
Kontingenčná_tabuľka. Pri tejto možnosti sa vás Microsoft opýta: „Ktorú kontingenčnú tabuľku chcete použiť?“ Stačí zadať jednu z buniek kontingenčnej tabuľky. Záznam „Obr. 11-14’!$A$3 označuje prvú bunku kontingenčnej tabuľky, do ktorej sa zadávajú údaje. Keďže v našom prípade môžeme zadať akúkoľvek bunku súvisiacu s kontingenčnou tabuľkou, ponechajte argument nezmenený. Adresa bunky $A$3 je vhodná vo všetkých ohľadoch.
Pole 1; prvok 1. V automaticky vygenerovanom vzorci sa ako názov poľa vyberie názov región a ako hodnota poľa - Severovýchod. Práve tu spočívajú problémy, ktoré vznikajú pri práci s funkciou GET.PIVOT.TABLE.DATA. Automaticky vybrané hodnoty nemožno skopírovať, pretože sú pevne zakódované. Preto, ak sa vzorce skopírujú v celej oblasti zostavy, budete ich musieť zmeniť manuálne. Namiesto hodnoty Severovýchod nahraďte odkaz na bunku v tvare $ A6. Zadaním znaku dolára pred názvom stĺpca A určíte, že riadkovú časť odkazu možno zmeniť pri kopírovaní vzorca do buniek stĺpca.
Pole 2; prvok 2. Tento pár argumentov určuje pole dátum s hodnotou 1. Ak bola pôvodná kontingenčná tabuľka zoskupená podľa mesiaca, pole mesiaca si zachová pôvodný názov poľa dátum. Číselná hodnota mesiaca je 1, čo zodpovedá januáru. Pri vytváraní obrovských vzorcov, ktoré sú špecifikované v desiatkach alebo dokonca stovkách buniek zostavy, je sotva vhodné použiť takúto hodnotu. Je lepšie použiť vzorec, ktorý vypočíta hodnoty polí dátum, podobne ako vzorec v bunke B4. Namiesto 1 v tomto prípade môžete použiť vzorec MONTH(B$4). Znak dolára pred 4 znamená, že vzorec môže priradiť hodnoty do poľa dátum na základe iných mesiacov, keď sa vzorec skopíruje do buniek riadku.
Pole 3; prvok 3. V tomto prípade sa názov poľa priradí automaticky Index a hodnotu poľa Fakt. Tieto hodnoty sú správne pre január, ale pre nasledujúce mesiace bude potrebné zmeniť hodnotu poľa na Plán. Zmeňte pevne zakódovanú hodnotu poľa Fakt za odkaz 5 B$.
Pole 4; prvok 4. Tieto argumenty sa nepoužívajú, pretože polia skončili.

Ryža. 19. Po dokončení úprav je vzorec GET.PIOTTABLE.DATA vhodný na kopírovanie do všetkých buniek v rozsahu

Ryža. 20. Upravte rozloženie polí v oblasti stĺpcov tak, aby sa zobrazil stĺpec Plánovať výsledok

Ryža. 21. Záverečná správa, ktorú možno predložiť manažérovi

Aktualizujte prehľad. Ak chcete prehľad aktualizovať o údaje pre budúce mesiace, postupujte takto:

Vložte skutočné metriky pod pôvodnú množinu údajov. Pretože zdrojové údaje sú vo formáte tabuľky, formátovanie tabuľky sa automaticky prenesie do nových riadkov údajov. Rozšírená je aj definícia pôvodnej kontingenčnej tabuľky (v excelovom súbore som už doplnil skutočné ukazovatele za celý rok).
Prejdite do kontingenčnej tabuľky. Kliknite pravým tlačidlom myši a vyberte Aktualizovať. Vzhľad kontingenčnej tabuľky sa zmení, ale to je v poriadku.
Prejdite do prostredia prehľadu. V zásade sa už urobilo všetko pre aktualizáciu správy, ale nezaškodí otestovať výsledky. Zmeňte vzorec v bunke A2 napríklad na tento: =MONTH(DNES() +31 ;0) a uvidíte, čo sa stane.

Nemyslel som si, že niekedy poviem toto: „Funkcia GET.DATA.PICTTABLE je najväčším požehnaním. Ako sme predtým bez nej existovali?