Používanie kontingenčných tabuliek

Predtým som už hovoril o tom, že keď sa hovorí o bunke kontingenčná tabuľka namiesto obvyklého odkazu sa vráti funkcia GET.DATA.PUMP.TABLE (pozri). Ak máte záujem o ako na prekonanie tejto nepríjemnosti odporúčam odvolať sa na spomínanú poznámku. Ak sa pýtate, prečo takto sa to stáva a tiež aké sú kladné stránky funkcie PRIJÍMAŤ ÚDAJE PANEL.TABUĽKY, potom navrhujem fragment knihy Jelen, Alexander. (kapitola 15). Uvažovaná technika vám umožní vyrovnať sa s mnohými problémami, ktoré spôsobujú bolesti hlavy používateľom kontingenčných tabuliek, najmä:

Po obnovení kontingenčnej tabuľky zmizne predtým použité formátovanie. Formáty čísel sú stratené. Výsledky úpravy šírky stĺpca zmiznú.
Neexistuje ľahká cesta vytvorenie asymetrickej kontingenčnej tabuľky. Jedinou možnosťou je použiť pomenované množiny, ale táto metóda je k dispozícii iba pre tých, ktorí používajú kontingenčné tabuľky dátových modelov a nie bežné kontingenčné tabuľky.
Excel si šablóny nepamätá. Ak potrebujete kontingenčné tabuľky znovu a znovu vytvárať, budete sa musieť znova zoskupiť, 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é. Podobné techniky sa navyše používajú už od Excelu 2002. Moja komunikácia s používateľmi však ukazuje, že menej ako 1% ich pozná. Jedinou otázkou, ktorú majú používatelia, je, ako vypnúť zvláštnu funkciu ZÍSKAŤ ÚDAJE Z PANELU. Škoda ...

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

No, začnime pekne po poriadku.

Ako opustiť problematickú funkciu ZÍSKAŤ ÚDAJE. OSOBNÉ TABUĽKY

Funkcia GET PIVOTTABLE DATA bola pre mnohých používateľov už dlho bolesťou hlavy. Správanie kontingenčných tabuliek sa v programe Excel 2002 náhle zmenilo bez akéhokoľvek varovania. Len čo začnete vytvárať vzorce mimo kontingenčnej tabuľky, ktoré odkazujú na jej údaje, táto funkcia z ničoho nič vyjde.

Predpokladajme, že v kontingenčnej tabuľke zobrazenej na obr. 1, je potrebné porovnať údaje za roky 2015 a 2014.

Obrázok: 1. Originálny kontingenčný stôl

Pridajte nadpis "% výšky" do bunky D3.
Skopírujte formát z bunky C3 do bunky D3.
Do bunky D4 zadajte rovnaké znamienko.
Kliknite na bunku C4.
Zadajte znak / (lomka), ktorý predstavuje operáciu rozdelenia.
Kliknite na bunku B4.
Zadajte -1 a stlačte kombináciu klávesov zostať v tej istej cele. Výsledok naformátujte v percentách. Uvidíte, že západný región zaznamenal pokles príjmu 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 v pravom dolnom rohu bunky. Tento štvorec predstavuje rukoväť výplne, ktorú môžete použiť na kopírovanie vzorca na vyplnenie celého stĺpca v zostave.

Po dokončení kopírovania vzorca a pohľadom 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).

Obrázok: 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 by sa to stalo v skutočnom živote. Ktokoľvek vám povie, že po vykonaní vyššie uvedených krokov vytvorí Excel vzorec \u003d C4 / B4-1. Vráťte sa do bunky D4 a všimnite si riadok vzorcov (obrázok 4). Proste nejaký diabol! Jednoduchý vzorec \u003d C4 / B4-1 už neexistuje. Namiesto toho program nahrádza zložitú konštrukciu funkciou GET.DATA.PUMP.TABLE. Prečo tento vzorec poskytuje správne výsledky v bunke D4, ale po skopírovaní do buniek pod ním odmietne pracovať?

Prvá reakcia ktoréhokoľvek používateľa na to, čo sa stalo, bude nasledujúca: „Aká je táto podivná konštrukcia GET.DATA.PERSONAL.TABLE, ktorá zničila moju správu?“ Väčšina používateľov sa bude chcieť tejto funkcie ihneď zbaviť. Niektorí si položia otázku: „Prečo nám spoločnosť Microsoft túto funkciu sprístupnila?“

Nič také v tom nebolo Časy programu Excel 2000. Keď som sa začal pravidelne stretávať s funkciou GET.DATA.PERSONAL.TABLES, nenávidel som ju. Keď sa ma na jednom zo seminárov niekto pýtal, ako by sa to dalo využiť pre blaho veci, zostal som v nemom úžase. Nikdy som si takúto otázku nedával! Podľa môjho názoru a podľa názoru väčšiny používateľov programu Excel bola funkcia GET.DATA.PUMP.TABLE produktom zla, ktoré nemalo nič spoločné so silami dobra. Našťastie existujú dva spôsoby, ako túto funkciu deaktivovať.

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

Prejdite do bunky D4 a zadajte \u003d (znamienko rovnosti).
Zadajte C4.
Zadajte / (lomka na rozdelenie).
Zadajte B4.
Zadajte -1.
Kliknite na Zadajte.

Teraz ste vytvorili štamgast excel vzorec, ktoré je možné kopírovať do buniek pod stĺpcom a pomocou ktorých môžete získať 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 vykonajú opísané akcie sami.

Obrázok: 5. Stačí na klávesnici zadať \u003d C4 / B4-1 a vzorec bude fungovať tak, ako potrebujete

Niektorým používateľom bude nepríjemné obvyklé poradie zadávania vzorcov. Navrhovaná možnosť je navyše prácnejšia. Ak ste jedným z týchto používateľov, druhá cesta je pre vás ...

Zakážte funkciu GET PANEL.TABLE DATA.Funkciu GET PANEL.TABLE DATA môžete natrvalo deaktivovať. Kliknite na pás s ponukami Súbor → možnosti... V otvorenom okne možnostiExcel choď na príspevok Vzorce a zrušte začiarknutie políčka vedľa možnosti Použite funkciuGetPivotData pre odkazy na kontingenčné tabuľky... Kliknite na Ok.

Alternatívna možnosť. Kliknite na kontingenčnú tabuľku a na kontextovej karte, ktorá sa zobrazí Analýza kliknite na rozbaľovací zoznam vedľa tlačidla možnosti... Zrušte začiarknutie políčka vedľa Vytvorte GetPivotData (obr. 7). Zaškrtávacie políčko je predvolene zapnuté.

Prečo nám spoločnosť Microsoft ponúkla funkciu GET PANEL.TABLES.Ak je táto funkcia tak strašná, prečo ju spoločnosť Microsoft predvolene povolila? Prečo im záleží na udržaní podpory tejto funkcie v novších verziách Excelu? Sú si vedomí sentimentu používateľov? A prechádzame k zábavnej časti ...

Používanie ZÍSKAŤ PIVOTTABLE DATA na vylepšenie kontingenčných tabuliek

Kontingenčné tabuľky sú veľkým vynálezom ľudstva. Kontingenčná tabuľka je vytvorená pomocou niekoľkých kliknutí, čo eliminuje potrebu použitia pokročilého filtra, funkcií BDSUMM a dátových tabuliek. Pomocou kontingenčných tabuliek môžete vytvárať jednostránkové správy z veľkého množstva údajov. Tieto výhody zatieňujú niektoré nevýhody kontingenčných tabuliek, ako napríklad nevýrazné formátovanie a potreba prevádzať 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 takom prípade všetko začína počiatočnými údajmi. Vytvárame kontingenčnú tabuľku a pomocou všetkých možných techník ju prispôsobujeme a vylepšujeme. Niekedy kontingenčnú tabuľku prevedieme na hodnoty a urobíme nejaké konečné formátovanie.

Nová metodika kontingenčnej tabuľky, ktorú navrhol Rob Colley (vývojár spoločnosti Microsoft) a o ktorej sa bude diskutovať neskôr, je výsledkom vylepšenia vyššie opísaného procesu. V takom prípade sa najskôr vytvorí primitívna kontingenčná tabuľka. Túto tabuľku nemusíte formátovať. Potom nasleduje jednokrokový, pomerne časovo náročný proces, aby sa vytvoril pekne naformátovaný shell, do ktorého bude umiestnená záverečná správa. Potom sa použije funkcia GET DATA.PUMP.TABLE na rýchle naplnenie zostavy, ktorá je v shelli dátami. Po prijatí nových údajov ich môžete vložiť na hárok, aktualizovať primitívnu kontingenčnú tabuľku a vytlačiť správu, ktorá sa nachádza v škrupine (obrázok 9). Táto technika má množstvo nepopierateľných výhod. Nemusíte sa napríklad starať o naformátovanie prehľadu ihneď po jeho 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ý prehľad, ktorý zobrazuje skutočné údaje za posledné mesiace a ciele pre budúce mesiace.

Vytvorte primitívnu kontingenčnú tabuľku.Počiatočné údaje (obr. 10) sú prezentované vo forme transakcií obsahujúcich informácie o plánovaných a skutočných ukazovateľoch za každý región, v ktorom sa nachádzajú pobočky spoločnosti. Plánované údaje sú podrobne uvedené na úrovni mesiacov, zatiaľ čo skutočné údaje sú podrobne uvedené na úrovni jednotlivých dní. Plánované ukazovatele sa vytvárajú na nasledujúci rok a skutočné ukazovatele sa vytvárajú za posledné mesiace. Pretože sa prehľad bude aktualizovať každý mesiac, je tento proces výrazne zjednodušený, ak sa zdroj údajov kontingenčnej tabuľky zväčšuje, keď sa do dolnej časti pridávajú nové údaje. V zastaraných verziách Tvorba Excelu podobný zdroj dát bol implementovaný pomocou pomenovaného dynamického rozsahu pomocou funkcie OFFSET (pozri podrobnosti). Pri práci v programe Excel 2013 jednoducho vyberte jednu z dátových buniek a stlačte kombináciu klávesov Ctrl + T (vytvorte tabuľku). Takto sa vytvorí pomenovaná množina údajov, ktorá sa automaticky rozšíri pri pridávaní nových riadkov a stĺpcov.

Teraz vytvorme kontingenčnú tabuľku. Funkcia GET PIVOTTABLE DATA je dostatočne výkonná, ale môže vrátiť iba hodnoty, ktoré sa zobrazujú v aktuálnej kontingenčnej tabuľke. Táto funkcia nedokáže vykonať skenovanie medzipamäte na výpočet položiek, ktoré nie sú v kontingenčnej tabuľke.

Vytvorte kontingenčnú tabuľku:

Vyberte tím Vložte → Kontingenčná tabuľkaa potom v dialógovom okne Vytvorenie kontingenčnej tabuľkykliknite Ok.
V zozname polí v kontingenčnej tabuľke vyberte pole dátum... Zoznam dátumov sa objaví na ľavej strane kontingenčnej tabuľky (obr. 11).
Vyberte ľubovoľnú bunku s dátumom, napríklad A4. Na kontextovej karte Analýzanachádza sa v množine kontextových kariet Práca s kontingenčnými tabuľkami, kliknite na tlačidlo Zoskupenie podľa polí (pozrieť detaily). V dialógovom okne Zoskupenie vyberte možnosť Mesiace (obr. 12). Kliknite Ok... Názvy mesiacov sa objavia na ľavej strane kontingenčnej tabuľky (obr. 13).
Presuňte pole dátum do oblasti Stĺpce kontingenčnej tabuľky.
Presuňte pole Register do oblasti Stĺpce v zozname polí kontingenčnej tabuľky.
Vyberte pole Regiónsa majú zobraziť v ľavom stĺpci kontingenčnej tabuľky.
Vyberte pole Príjemktorá sa objaví v oblasti Hodnoty kontingenčnej tabuľky.

Obrázok: 11. Začnite zoskupením podľa polí dátum

V tomto okamihu vyzerá naša kontingenčná tabuľka dosť primitívne (obrázok 14). Naozaj nemám rada nápisy Názvy riadkov a Názvy stĺpcov... Je nepraktické zobrazovať súčty za Jan Plan a Jan Fact v stĺpci D atď. Ale nebojte sa vzhľad tejto kontingenčnej tabuľky, pretože okrem vás ju nikto iný neuvidí. Od tohto okamihu vytvoríme obálku zostavy, ktorej zdrojom údajov bude novovytvorená kontingenčná tabuľka.

Vytvorenie shellu správy.Vložte do svojho zošita prázdny list. Odložme na chvíľu nástroje kontingenčnej tabuľky a prejdime k bežným nástrojom Excel. Našou úlohou je pomocou vzorcov a formátovania vytvoriť krásny report, ktorý sa nehanbí ukázať manažérovi.

Postupujte nasledovne (obr. 15).

Do bunky A1 zadajte názov prehľadu - 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 \u003d EON MESIAC (TODAY (); 0). Táto funkcia vráti posledný deň aktuálneho mesiaca. Napríklad ak si tieto riadky prečí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). Vo výsledku bude vypočítaný dátum vyzerať ako text.
Do bunky A5 zadajte názov Región.
Do zvyšných buniek v stĺpci A zadajte nadpisy regiónov. Nadpisy regiónov sa musia zhodovať s názvami regiónov zobrazenými v kontingenčnej tabuľke.
V prípade potreby pridajte do stĺpca súčty podľa oddelenia.
V dolnej časti prehľadu pridajte riadok Spolu za spoločnosť.
Do bunky B4 zadajte vzorec \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1). Tento vzorec vráti dátumy 1. 1. 2014, 1. 2. 2104 atď., Prvé dni všetkých 12 mesiacov aktuálneho roka.
Vyberte bunku B4. Stlačením kombinácie klávesov Ctrl + 1 otvoríte okno Formát bunky... Na karte Číslo v sekcii Všetky formáty zadajte vlastný formát čísla Mmm... Tento formát zobrazuje názov trojpísmenového mesiaca. Zarovnajte text napravo od bunky.
Skopírujte obsah bunky B4 do rozsahu C4: M4. V hornej časti kontingenčnej tabuľky sa zobrazuje riadok s názvami mesiacov.
Do bunky B5 zadajte vzorec \u003d IF (MESIAC (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt, a pre súčasnú a budúcu - Plán.
Pridajte nadpis do bunky N5 Výsledok... Do bunky O4 - Výsledok, O5 - Plán, Р5 - % odchýlka.
Zadajte obvyklé vzorce programu Excel používané na výpočet súčtov oddelení, riadku s celkovým počtom spoločností, stĺpca s celkovým súčtom a stĺpca s rozptylom%:
1. v bunke B8 zadajte vzorec \u003d SUM (B6: B7) a skopírujte ho do ďalších buniek v riadku;
2. v bunke N6 zadajte vzorec \u003d SUM (B6: M6) a skopírujte ho do ďalších buniek v stĺpci;
3. do bunky P6 zadajte vzorec \u003d IFERROR ((N6 / O10) -1; 0) a skopírujte ho do ďalších buniek v stĺpci;
4. v bunke B13 zadajte vzorec \u003d SUM (B10: B12) a skopírujte ho do ďalších buniek v riadku;
5. v bunke B17 zadajte vzorec \u003d SUM (B15: B16) a skopírujte ho do ďalších buniek v riadku;
6. v bunke B19 zadajte vzorec \u003d SUM (B6: B18) / 2 a skopírujte ho do ďalších buniek v riadku.
Použite štýl Nadpis 4 na štítky v stĺpci A a na nadpisy v riadkoch 4 a 5.
Pre rozsah buniek B6: O19 vyberte formát čísla # ## 0.
Pre bunky v stĺpci P vyberte formát čísla 0,0%.

Takže sme dokončili vytvorenie shellu správy zobrazeného na obr. 15. Táto správa obsahuje všetky požadované formáty. Ďalšia časť ukazuje, ako sa na vyplnenie správy používa funkcia ZÍSKAŤ PIVOTOVATEĽNÉ ÚDAJE.

Obrázok: 15. Pred pridaním vzorcov nahláste obal GET.DATA.PERSONAL.TABLES

Používanie funkcie GET PIVOTTABLE DATA na vyplnenie shell zostavy údajmi.Odteraz budete môcť využívať všetky výhody plynúce z používania funkcie GET PANEL.TABLE DATA. Ak ste zrušili začiarknutie políčka umožňujúceho túto funkciu, vráťte sa k príslušnému nastaveniu a zrušte začiarknutie políčka (pozri popis na obr. 6 alebo 7).

Vyberte bunku B6 shellu správy. Táto bunka zodpovedá severovýchodnému regiónu a skutočným údajom za január.

Zadajte \u003d (znamienko rovnosti) a začnite 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 údajom za január - C12 (obr. 17).
Stlačte kláves Zadajtedokončíte zadávanie vzorca a vrátite sa do shellu správy. Výsledkom je, že program Excel pridá do bunky B6 funkciu ZÍSKAŤ PIVOTOVATEĽNÉ ÚDAJE. Bunka zobrazí hodnotu 277 435 dolárov.

Toto číslo si zapamätajte, pretože sa bude vyžadovať pri porovnaní s výsledkami vzorca, ktorý neskôr upravíte. Vzorec vygenerovaný programom má túto podobu: \u003d GET.DATA.PERSONAL.TABLE ("Príjem"; 'Obr. 11-14 ′! $ A $ 3; "Región"; "Severovýchod"; "Dátum") ; 1; „Ukazovateľ“; „Skutočnosť“). Ak ste doteraz ignorovali funkciu ZÍSKAŤ PIVOTTABILNÉ ÚDAJE, je čas sa na ňu bližšie pozrieť. Na obr. 18 sa tento vzorec zobrazuje v režime úprav spolu s nápovedou.

Argumenty funkcie:

Údajové pole. Pole z oblasti Hodnoty kontingenčnej tabuľky Poznámka: v takom prípade sa použije toto pole Príjem, ale nie Suma v poli Príjem.
Kontingenčná tabuľka. Pomocou tohto parametra sa vás spoločnosť Microsoft pýta: „Ktorú kontingenčnú tabuľku chcete použiť?“ Stačí určiť jednu z buniek kontingenčnej tabuľky. Záznam „Obr. 11 - 14 '! $ A $ 3 predstavuje prvú bunku v kontingenčnej tabuľke, do ktorej sa zadávajú údaje. Pretože v našom prípade môžete určiť ľubovoľnú bunku súvisiacu s kontingenčnou tabuľkou, nechajte argument nezmenený. Adresa bunky $ A $ 3 je vo všetkých ohľadoch vhodná.
Pole 1; prvok 1. V automaticky generovanom vzorci je vybratý názov poľa Región, a ako hodnota poľa - Severovýchod... Tu spočíva dôvod problémov, ktoré vznikajú pri práci s funkciou GET DATA.PUMP.TABLE. Automaticky vybrané hodnoty nemožno kopírovať, pretože sú napevno. Preto ak kopírujete vzorce v celej oblasti prehľadu, budete ich musieť zmeniť manuálne. Nahraďte severovýchod odkazom na bunku vo formáte $ A6. Umiestnením znaku dolára pred stĺpec A určíte, či je možné zmeniť riadkovú časť odkazu pri kopírovaní vzorca do buniek v stĺpci.
Pole 2; položka 2. Táto dvojica argumentov definuje pole dátum s hodnotou 1. Ak bola pôvodná kontingenčná tabuľka zoskupená podľa mesiacov, pole mesiac si zachová pôvodný názov poľa dátum... Číselná hodnota mesiaca je 1, čo zodpovedá januáru. Je ťažké odporučiť použiť takúto hodnotu pri vytváraní obrovských vzorcov, ktoré sú zostavené v desiatkach alebo dokonca stovkách buniek prehľadu. Lepšie je použiť vzorec, ktorý počíta hodnoty poľa dátum, ako je vzorec v bunke B4. Namiesto 1 v tomto prípade môžete použiť vzorec MESIAC (v $ 4). Znak dolára, ktorý predchádza číslu 4, naznačuje, že vzorec môže poľa priradiť hodnoty dátum na základe iných mesiacov, keď sa vzorec skopíruje do buniek v riadku.
Pole 3; prvok 3. V takom prípade sa názvu poľa priradí automaticky Register a poľná hodnota 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 nastavenú hodnotu poľa Fakt k odkazu B $ 5.
Pole 4; Element 4. Tieto argumenty sa nepoužívajú, pretože polia skončili.

Nový vzorec je znázornený na obr. 19. Za minútu bol namiesto naprogramovaného vzorca navrhnutého na prácu s jednou hodnotou vytvorený flexibilný vzorec, ktorý je možné skopírovať do všetkých buniek v množine údajov. Stlačte kláves Zadajtea získate rovnaký výsledok ako pred úpravou vzorca. Upravený vzorec má nasledujúcu formu: \u003d GET.PUMP.TABLE DATA ("Príjem"; 'Obr. 11-14 ′! $ A $ 3; "Región"; $ A6; "Dátum"; MESIAC (B $ 4) ; „Ukazovateľ“; B 5 USD)

Obrázok: 19. Po dokončení úprav je vzorec GET.DATA.PERSONAL.TABLE vhodný na kopírovanie do všetkých buniek rozsahu

Skopírujte vzorec do ľubovoľných prázdnych buniek v stĺpcoch B: M, kde sa počítajú výsledky. Teraz, keď prehľad obsahuje skutočné čísla, môžete vykonať konečné úpravy šírok stĺpcov.

V ďalšom kroku nastavíme vzorec GET.PUMP.TABLE na výpočet konečných cieľov. Ak iba skopírujete vzorec do bunky O6, zobrazí sa chybové hlásenie #REF! Dôvodom tejto chyby je slovo Výsledok v bunke O4 nie je názov mesiaca. Na zabezpečenie správneho fungovania funkcie ZÍSKAŤ PIVOTTABLE DATA musí byť požadovaná hodnota v kontingenčnej tabuľke. Ale keďže v pôvodnej kontingenčnej tabuľke je to pole Register je druhé pole v oblasti stĺpca, stĺpec údajov Zhrnutie plánu vlastne chýba. Posuňte pole Register tak, aby sa stal prvým v oblasti stĺpca (Obrázok 20).

Obrázok: 20. Upravte rozloženie polí v oblasti stĺpca tak, aby sa stĺpec zobrazil Obrysový plán

Porovnaj s obr. 14. Tam, v oblasti STĹPCA, bolo pole prvé dátum, čo viedlo k skutočnosti, že najskôr boli stĺpce zoskupené podľa dátumu a v každom mesiaci podľa plánu / skutočnosti. Prvým je pole Indikátor a v súhrne sú stĺpce prvé Plán, vnútri zoradené podľa mesiacov a potom všetkých stĺpcov Fakt.

Vráťte sa na hárok obálky správy, postavte sa do bunky O6, zadajte \u003d (znamienko rovnosti) a odkazujte na bunku N12 na hárku kontingenčnej tabuľky, ktorá zodpovedá plánovaným výsledkom regiónu Severovýchod. Kliknite na Zadajte... Získate vzorec \u003d GET.DATA.PERSONAL.TABLES ("Príjem"; 'Obr. 11-14'! $ A $ 3; "Región"; "Severovýchod"; "Ukazovateľ"; "Plán"). Upravte to: \u003d ZÍSKAJTE. DATA.SUMMARY.TABLES ("Income"; ‘Obr. 11-14 ′! $ A $ 3;„ Region “; $ A6;„ Indicator “; O $ 5). Skopírujte tento vzorec do ďalších buniek v stĺpci O (obrázok 21). Upozorňujeme, že aj keď presuniete rôzne oblasti zostavy kontingenčnej tabuľky, shell funguje správne. Samozrejme, ak deaktivujete niektoré kontingenčné polia, shell si s tým neporadí ...

Obrázok: 21. Záverečná správa, ktorá môže byť predložená manažérovi

Teraz máte dobre naformátovanú obálku správ, ktorá používa hodnoty z dynamickej kontingenčnej tabuľky. Aj keď prvotné vytvorenie správy trvalo pomerne dlho, aktualizácia trvala iba pár minút.

Aktualizujte správu.Ak chcete prehľad aktualizovať o údaje za budúce mesiace, postupujte podľa týchto pokynov.

Vložte skutočné údaje pod pôvodný súbor údajov. Pretože zdrojové údaje sú vo formáte tabuľky, formátovanie tabuľky sa automaticky rozšíri do nových riadkov údajov. Rozširuje tiež definíciu pôvodnej kontingenčnej tabuľky (v súbore Excel som už pridal skutočné údaje za celý rok).
Prejdite na kontingenčnú tabuľku. Kliknite pravým tlačidlom myši a vyberte možnosť Obnoviť... Kontingenčná tabuľka sa zmení, ale je to v poriadku.
Prejdite do shellu správy. V zásade už bolo urobené všetko pre aktualizáciu správy, ale nezaškodí otestovať výsledky. Zmeňte vzorec v bunke A2 napríklad na tento: \u003d EON MESIAC (DNES () +31 ; 0), a uvidíte, čo sa stane.

Pridávaním nových skutočných údajov o predaji každý mesiac sa nemusíte obávať opätovného vytvárania formátov, vzorcov atď. Popísaný proces aktualizácie správy je taký jednoduchý, že navždy zabudnete na problémy, ktoré sa vyskytli pri príprave mesačných správ. 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 zaistiť správne fungovanie vzorcov, skontrolujte, či sa celkový súčet v prehľade zhoduje s celkovým súčtom v kontingenčnej tabuľke. Keď sa objaví nová oblasť, stačí ju pridať na zabalený hárok a presunúť príslušné vzorce.

Nemyslel som si, že by som niekedy povedal nasledovné: „Funkcia GET.DATA.PERSONAL.TABLE je najväčším požehnaním. Ako sme bez nej existovali predtým? “

V origináli boli počiatočné údaje Jeleny usporiadané tak, aby ďalšie vzorce fungovali správne až v júli 2015. V súbore Excel pripojenom k \u200b\u200btejto poznámke som upravil pôvodné údaje, ako aj niektoré vzorce, aby všetko fungovalo bez ohľadu na dátum, kedy ste budete experimentovať s priloženým súborom Excel. Žiaľ, vzorce sa museli komplikovať.

Už som predtým hovoril o tom, že pri odkazovaní na bunku kontingenčnej tabuľky sa namiesto normálneho odkazu vráti funkcia GET.PIVOTTABLE DATA (pozri). Ak máte záujem o ako na prekonanie tejto nepríjemnosti odporúčam odvolať sa na spomínanú poznámku. Ak sa pýtate, prečo takto sa to stáva a tiež aké sú kladné stránky funkcie PRIJÍMAŤ ÚDAJE PANEL.TABUĽKY, potom navrhujem fragment knihy Jelen, Alexander. (kapitola 15). Uvažovaná technika vám umožní vyrovnať sa s mnohými problémami, ktoré spôsobujú bolesti hlavy používateľom kontingenčných tabuliek, najmä:

Po obnovení kontingenčnej tabuľky zmizne predtým použité formátovanie. Formáty čísel sú stratené. Výsledky úpravy šírky stĺpca zmiznú.
Neexistuje ľahký 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 k dispozícii iba pre tých, ktorí používajú kontingenčné tabuľky dátových modelov a nie bežné kontingenčné tabuľky.
Excel si šablóny nepamätá. Ak potrebujete kontingenčné tabuľky znovu a znovu vytvárať, budete sa musieť znova zoskupiť, použiť vypočítané polia a členov a vykonať množstvo ďalších podobných úloh.

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

No, začnime pekne po poriadku.

Ako opustiť problematickú funkciu ZÍSKAŤ ÚDAJE. OSOBNÉ TABUĽKY

Predpokladajme, že v kontingenčnej tabuľke zobrazenej na obr. 1, je potrebné porovnať údaje za roky 2015 a 2014.

Obrázok: 1. Originálny kontingenčný stôl

Pridajte nadpis "% výšky" do bunky D3.
Skopírujte formát z bunky C3 do bunky D3.
Do bunky D4 zadajte rovnaké znamienko.
Kliknite na bunku C4.
Zadajte znak / (lomka), ktorý predstavuje operáciu rozdelenia.
Kliknite na bunku B4.
Zadajte -1 a stlačte kombináciu klávesov zostať v tej istej cele. Výsledok naformátujte v percentách. Uvidíte, že západný región zaznamenal pokles príjmu 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 v pravom dolnom rohu bunky. Tento štvorec predstavuje rukoväť výplne, ktorú môžete použiť na kopírovanie vzorca na vyplnenie celého stĺpca v zostave.

Po dokončení kopírovania vzorca a pohľadom 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).

Obrázok: 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%

V časoch programu Excel 2000 sa nič také nestalo. Keď som sa začal pravidelne stretávať s funkciou GETDATA.PERSONAL.TABLES, nenávidel som ju. Keď sa ma na jednom zo seminárov niekto pýtal, ako by sa to dalo využiť pre blaho veci, zostal som v nemom úžase. Nikdy som si takúto otázku nedával! Podľa môjho názoru a podľa názoru väčšiny používateľov programu Excel bola funkcia GET.DATA.PUMP.TABLE produktom zla, ktoré nemalo nič spoločné so silami dobra. Našťastie existujú dva spôsoby, ako túto funkciu deaktivovať.

Prejdite do bunky D4 a zadajte \u003d (znamienko rovnosti).
Zadajte C4.
Zadajte / (lomka na rozdelenie).
Zadajte B4.
Zadajte -1.
Kliknite na Zadajte.

Teraz ste vytvorili bežný vzorec programu Excel, ktorý môžete skopírovať do buniek pod stĺpcom a pomocou ktorého získate 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 vykonajú opísané akcie sami.

Obrázok: 5. Stačí na klávesnici zadať \u003d C4 / B4-1 a vzorec bude fungovať tak, ako potrebujete

Niektorým používateľom bude nepríjemné obvyklé poradie zadávania vzorcov. Navrhovaná možnosť je navyše prácnejšia. Ak ste jedným z týchto používateľov, druhá cesta je pre vás ...

Používanie ZÍSKAŤ PIVOTTABLE DATA na vylepšenie kontingenčných tabuliek

Nasledujúce časti pojednávajú o tom, ako vytvoriť dynamický prehľad, ktorý zobrazuje skutočné údaje za posledné mesiace a ciele pre budúce mesiace.

Vytvorte primitívnu kontingenčnú tabuľku.Počiatočné údaje (obr. 10) sú prezentované vo forme transakcií obsahujúcich informácie o plánovaných a skutočných ukazovateľoch za každý región, v ktorom sa nachádzajú pobočky spoločnosti. Plánované údaje sú podrobne uvedené na úrovni mesiacov a skutočné údaje na úrovni jednotlivých dní. Plánované ukazovatele sa vytvárajú na nasledujúci rok a skutočné ukazovatele sa vytvárajú za posledné mesiace. Pretože sa prehľad bude aktualizovať každý mesiac, je tento proces výrazne zjednodušený, ak sa zdroj údajov kontingenčnej tabuľky zväčšuje, keď do dolnej časti pribúdajú nové údaje. V starších verziách Excelu sa takýto zdroj údajov vytvoril pomocou pomenovaného dynamického rozsahu pomocou funkcie OFFSET (pozri podrobnosti). Pri práci v programe Excel 2013 jednoducho vyberte jednu z údajových buniek a stlačte kombináciu klávesov Ctrl + T (vytvorte tabuľku). Takto sa vytvorí pomenovaná množina údajov, ktorá sa automaticky rozšíri pri pridávaní nových riadkov a stĺpcov.

Vytvorte kontingenčnú tabuľku:

Vyberte tím Vložte → Kontingenčná tabuľkaa potom v dialógovom okne Vytvorenie kontingenčnej tabuľkykliknite Ok.
V zozname polí v kontingenčnej tabuľke vyberte pole dátum... Zoznam dátumov sa objaví na ľavej strane kontingenčnej tabuľky (obr. 11).
Vyberte ľubovoľnú bunku s dátumom, napríklad A4. Na kontextovej karte Analýzanachádza sa v množine kontextových kariet Práca s kontingenčnými tabuľkami, kliknite na tlačidlo Zoskupenie podľa polí (pozrieť detaily). V dialógovom okne Zoskupenie vyberte možnosť Mesiace (obr. 12). Kliknite Ok... Názvy mesiacov sa objavia na ľavej strane kontingenčnej tabuľky (obr. 13).
Presuňte pole dátum do oblasti Stĺpce kontingenčnej tabuľky.
Presuňte pole Register do oblasti Stĺpce v zozname polí kontingenčnej tabuľky.
Vyberte pole Regiónsa majú zobraziť v ľavom stĺpci kontingenčnej tabuľky.
Vyberte pole Príjemktorá sa objaví v oblasti Hodnoty kontingenčnej tabuľky.

Obrázok: 11. Začnite zoskupením podľa polí dátum

V tomto okamihu vyzerá naša kontingenčná tabuľka dosť primitívne (obrázok 14). Naozaj nemám rada nápisy Názvy riadkov a Názvy stĺpcov... Je nepraktické zobrazovať súčty za Jan Plan a Jan Fact v stĺpci D atď. Ale nebojte sa vzhľadu tejto kontingenčnej tabuľky, pretože okrem vás ju nikto iný neuvidí. Od tohto okamihu vytvoríme obálku zostavy, ktorej zdrojom údajov bude novovytvorená kontingenčná tabuľka.

Postupujte nasledovne (obr. 15).

Do bunky A1 zadajte názov prehľadu - 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 \u003d EON MESIAC (TODAY (); 0). Táto funkcia vráti posledný deň aktuálneho mesiaca. Napríklad ak si tieto riadky prečí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). Vo výsledku bude vypočítaný dátum vyzerať ako text.
Do bunky A5 zadajte názov Región.
Do zvyšných buniek v stĺpci A zadajte nadpisy regiónov. Nadpisy regiónov sa musia zhodovať s názvami regiónov zobrazenými v kontingenčnej tabuľke.
V prípade potreby pridajte do stĺpca súčty podľa oddelenia.
V dolnej časti prehľadu pridajte riadok Spolu za spoločnosť.
Do bunky B4 zadajte vzorec \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1). Tento vzorec vráti dátumy 1. 1. 2014, 1. 2. 2104 atď., Prvé dni všetkých 12 mesiacov aktuálneho roka.
Vyberte bunku B4. Stlačením kombinácie klávesov Ctrl + 1 otvoríte okno Formát bunky... Na karte Číslo v sekcii Všetky formáty zadajte vlastný formát čísla Mmm... Tento formát zobrazuje názov trojpísmenového mesiaca. Zarovnajte text napravo od bunky.
Skopírujte obsah bunky B4 do rozsahu C4: M4. V hornej časti kontingenčnej tabuľky sa zobrazuje riadok s názvami mesiacov.
Do bunky B5 zadajte vzorec \u003d IF (MESIAC (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt, a pre súčasnú a budúcu - Plán.
Pridajte nadpis do bunky N5 Výsledok... Do bunky O4 - Výsledok, O5 - Plán, Р5 - % odchýlka.
Zadajte obvyklé vzorce programu Excel používané na výpočet súčtov oddelení, riadku s celkovým počtom spoločností, stĺpca s celkovým súčtom a stĺpca s rozptylom%:
1. v bunke B8 zadajte vzorec \u003d SUM (B6: B7) a skopírujte ho do ďalších buniek v riadku;
2. v bunke N6 zadajte vzorec \u003d SUM (B6: M6) a skopírujte ho do ďalších buniek v stĺpci;
3. do bunky P6 zadajte vzorec \u003d IFERROR ((N6 / O10) -1; 0) a skopírujte ho do ďalších buniek v stĺpci;
4. v bunke B13 zadajte vzorec \u003d SUM (B10: B12) a skopírujte ho do ďalších buniek v riadku;
5. v bunke B17 zadajte vzorec \u003d SUM (B15: B16) a skopírujte ho do ďalších buniek v riadku;
6. v bunke B19 zadajte vzorec \u003d SUM (B6: B18) / 2 a skopírujte ho do ďalších buniek v riadku.
Použite štýl Nadpis 4 na štítky v stĺpci A a na nadpisy v riadkoch 4 a 5.
Pre rozsah buniek B6: O19 vyberte formát čísla # ## 0.
Pre bunky v stĺpci P vyberte formát čísla 0,0%.

Obrázok: 15. Pred pridaním vzorcov nahláste obal GET.DATA.PERSONAL.TABLES

Vyberte bunku B6 shellu správy. Táto bunka zodpovedá severovýchodnému regiónu a skutočným údajom za január.

Zadajte \u003d (znamienko rovnosti) a začnite 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 údajom za január - C12 (obr. 17).
Stlačte kláves Zadajtedokončíte zadávanie vzorca a vrátite sa do shellu správy. Výsledkom je, že program Excel pridá do bunky B6 funkciu ZÍSKAŤ PIVOTOVATEĽNÉ ÚDAJE. Bunka zobrazí hodnotu 277 435 dolárov.

Argumenty funkcie:

Údajové pole. Pole z oblasti Hodnoty kontingenčnej tabuľky Poznámka: v takom prípade sa použije toto pole Príjem, ale nie Suma v poli Príjem.
Kontingenčná tabuľka. Pomocou tohto parametra sa vás spoločnosť Microsoft pýta: „Ktorú kontingenčnú tabuľku chcete použiť?“ Stačí určiť jednu z buniek kontingenčnej tabuľky. Záznam „Obr. 11 - 14 '! $ A $ 3 predstavuje prvú bunku v kontingenčnej tabuľke, do ktorej sa zadávajú údaje. Pretože v našom prípade môžete určiť ľubovoľnú bunku súvisiacu s kontingenčnou tabuľkou, nechajte argument nezmenený. Adresa bunky $ A $ 3 je vo všetkých ohľadoch vhodná.
Pole 1; prvok 1. V automaticky generovanom vzorci je vybratý názov poľa Región, a ako hodnota poľa - Severovýchod... Tu spočíva dôvod problémov, ktoré vznikajú pri práci s funkciou GET DATA.PUMP.TABLE. Automaticky vybrané hodnoty nemožno kopírovať, pretože sú napevno. Preto ak kopírujete vzorce v celej oblasti prehľadu, budete ich musieť zmeniť manuálne. Nahraďte severovýchod odkazom na bunku vo formáte $ A6. Umiestnením znaku dolára pred stĺpec A určíte, či je možné zmeniť riadkovú časť odkazu pri kopírovaní vzorca do buniek v stĺpci.
Pole 2; položka 2. Táto dvojica argumentov definuje pole dátum s hodnotou 1. Ak bola pôvodná kontingenčná tabuľka zoskupená podľa mesiacov, pole mesiac si zachová pôvodný názov poľa dátum... Číselná hodnota mesiaca je 1, čo zodpovedá januáru. Je ťažké odporučiť použiť takúto hodnotu pri vytváraní obrovských vzorcov, ktoré sú zostavené v desiatkach alebo dokonca stovkách buniek prehľadu. Lepšie je použiť vzorec, ktorý počíta hodnoty poľa dátum, ako je vzorec v bunke B4. Namiesto 1 v tomto prípade môžete použiť vzorec MESIAC (v $ 4). Znak dolára, ktorý predchádza číslu 4, naznačuje, že vzorec môže poľa priradiť hodnoty dátum na základe iných mesiacov, keď sa vzorec skopíruje do buniek v riadku.
Pole 3; prvok 3. V takom prípade sa názvu poľa priradí automaticky Register a poľná hodnota 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 nastavenú hodnotu poľa Fakt k odkazu B $ 5.
Pole 4; Element 4. Tieto argumenty sa nepoužívajú, pretože polia skončili.

Obrázok: 19. Po dokončení úprav je vzorec GET.DATA.PERSONAL.TABLE vhodný na kopírovanie do všetkých buniek rozsahu

Obrázok: 20. Upravte rozloženie polí v oblasti stĺpca tak, aby sa stĺpec zobrazil Obrysový plán

Obrázok: 21. Záverečná správa, ktorá môže byť predložená manažérovi

Aktualizujte správu.Ak chcete prehľad aktualizovať o údaje za budúce mesiace, postupujte podľa týchto pokynov.

Vložte skutočné údaje pod pôvodný súbor údajov. Pretože zdrojové údaje sú vo formáte tabuľky, formátovanie tabuľky sa automaticky rozšíri do nových riadkov údajov. Rozširuje tiež definíciu pôvodnej kontingenčnej tabuľky (v súbore Excel som už pridal skutočné údaje za celý rok).
Prejdite na kontingenčnú tabuľku. Kliknite pravým tlačidlom myši a vyberte možnosť Obnoviť... Kontingenčná tabuľka sa zmení, ale je to v poriadku.
Prejdite do shellu správy. V zásade už bolo urobené všetko pre aktualizáciu správy, ale nezaškodí otestovať výsledky. Zmeňte vzorec v bunke A2 napríklad na tento: \u003d EON MESIAC (DNES () +31 ; 0), a uvidíte, čo sa stane.

Nemyslel som si, že by som niekedy povedal nasledovné: „Funkcia GET.DATA.PERSONAL.TABLE je najväčším požehnaním. Ako sme bez nej existovali predtým? “

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

Ak chcete získať rýchly prístup k funkcii, musíte do bunky (\u003d) zadať znamienko rovnosti a v kontingenčnej tabuľke vybrať požadovanú bunku. Excel vygeneruje funkciu ZÍSKAŤ PIVOTOVATEĽNÉ ÚDAJE automaticky.

Zakážte vytváranie GetPivotData

Ak chcete vypnúť automatické generovanie funkcie GET PIVOTTABLE, vyberte ľubovoľnú bunku v kontingenčnej tabuľke, kliknite na kartu Práca s kontingenčnými tabuľkami -\u003e Možnostido skupiny Kontingenčná tabuľka.Kliknite na šípku nadol vedľa karty Možnosti.V rozbaľovacej ponuke zrušte začiarknutie políčka VytvorGetPivotData.

Použitie odkazov na bunky vo funkcii GET DATA.PUMP.TABLE

Namiesto zadávania názvov položiek alebo polí vo funkcii GET.DATA.PUMP.TABLE môžete odkazovať na bunky v hárku. V príklade nižšie obsahuje bunka E3 názov produktu a vzorec v bunke E4 sa na ňu vzťahuje. Vďaka tomu sa vráti celkový objem koláčov.

Používanie odkazov na kontingenčné tabuľky

Nie sú žiadne otázky o tom, ako fungujú odkazy na položky kontingenčnej tabuľky; problémy sa vyskytujú, ak sa chceme odvolať na údajové pole.

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

Ak by sme však zmenili prvý argument údajové polek odkazu na bunku E3 nám Excel vráti chybu #REF!

ZÍSKAJTE ÚDAJE O OSOBNÝCH TABUĽKÁCH (E3; $ A $ 3)

Problém sa vyrieši jednoduchým pridaním prázdneho reťazca („“) na začiatok alebo na koniec referencie bunky.

ZÍSKAJTE ÚDAJE TABUĽKY (E3 & ""; $ A $ 3)

Jednoduchá oprava vzorca vráti správnu hodnotu.

Použitie dátumov vo funkcii GET DATA.PUMP.TABLE

Ak používate dátumy vo funkcii ZÍSKAŤ PIVOTTABLE DATA, môžete mať problémy, aj keď je dátum zobrazený v kontingenčnej tabuľke. Napríklad nasledujúci vzorec 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.DATA.COMPLETE.TABLE ("Volume"; $ A $ 3; "Date"; "04/21/2013")

Ak sa chcete vyhnúť chybám súvisiacim s dátumami, 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 DÁTUM
Odkaz na 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 ZÍSKAŤ PIVOTTABILNÉ ÚDAJE sa ubezpečte, že formáty dátumu argumentu vzorec a kontingenčnej tabuľky sú rovnaké.

V bunke E4 používa vzorec formát dátumu „DD.MM.RRRR“ a vo výsledku boli vrátené správne informácie.

Pomocou funkcie DATEVALUE

Namiesto manuálneho zadania dátumu do vzorca môžete dátum vrátiť tak, že pridáte funkciu DATEVALUE.

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

RECEIVE.SUMMARY.TABLE.DATA ("Zväzok"; $ A $ 3; "Dátum"; DATEVALUE ("04.21.2013"))

Pomocou funkcie DATE

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

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Date"; DATE (2013; 4; 21))

Odkaz na bunku s dátumom

Namiesto manuálneho zadania dátumu do vzorca môžete odkázať na bunku, ktorá obsahuje dátum (v akomkoľvek formáte, ktorý Excel interpretuje údaje ako dátumy). V príklade v bunke E4 vzorec odkazuje na bunku E3 a Excel vráti správne údaje.

RECEIVE.SUMMARY.TABLE.DATA ("Zväzok"; $ A $ 3; "Dátum"; E3)