V tomto článku sa naučíme Ako získať medzisúčet zoskupený podľa dátumu pomocou funkcie GETPIVOTDATA v programe Excel.
Scenár:
Spravidla pracujeme s kontingenčnými tabuľkami, musíme nájsť konkrétne súčtové hodnoty. Tieto súhrnné hodnoty s kritériami pre niektoré hodnoty dátumu je možné extrahovať v programe Excel. Ak napríklad potrebujete nájsť súčet všetkých polí kategorizovaných podľa mesiacov alebo rokov s hodnotami dátumu.
Ako problém vyriešiť?
Tu máme problém, súčet záznamov s kritériami ako hodnotou dátumu. Použitie funkcií a nástroja nám pomôže problém prekonať.
- Získať kontingenčnú tabuľku
- Funkcia GETPIVOTDATA
Najprv sa vytvorí kontingenčná tabuľka, ktorá bude kategorizovaná podľa hodnôt dátumu. Potom sa na nájdenie súčtu so zoskupenými kritériami v kontingenčnej tabuľke použije nasledujúci vzorec.
Všeobecný vzorec:
| = GETPIVOTDATA ("Suma poľa", table_ref, ["field_1", "krit_1 "], [" field_2 ", "krit_1"], …) |
Poznámka :- Použite hlavičku poľa so správnym kľúčovým slovom
Súčet poľa: pole, kde sa požaduje súčet
table_ref: prvé referenčné pole
field_1: kritériá poľa 1
kritika_1: kritériá 1
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie. Najprv vytvoríme pre údaje kontingenčnú tabuľku.
Konvertujte dané údaje na kontingenčnú tabuľku pomocou možnosti kontingenčnej tabuľky. Tu nájdete ďalšie informácie o získaní kontingenčnej tabuľky údajov. Po získaní kontingenčnej tabuľky boli údaje kategorizované podľa mesačného indexu.
Ako vidíte, kontingenčná tabuľka je tu vľavo a nastavenia poľa vpravo. Problém získania súčtu medzisúčtov je však stále tu.
Ako používať funkciu GETPIVOTDATA s kontingenčnou tabuľkou.
Tu máme kontingenčnú tabuľku a musíme nájsť súčet množstva v marcovom mesiaci. Ak namiesto kontingenčnej tabuľky robíme rovnakú úlohu s údajovými hodnotami, bude to náročná úloha alebo na vyriešenie tohto problému bude potrebná viac ako jedna funkcia. Teraz, aby ste našli súčet množstva v marcovom mesiaci, použite nižšie uvedený vzorec.
Použite vzorec
| = GETPIVOTDATA („Suma množstva“, A3, „Mesiace“, „Mar“) |
Vysvetlenie:
- Argument súčet množstva na nájdenie poľa súčet množstva.
- A3: referenčná bunka poľa tabuľky
- Pole tabuľky „Mesiace“ vybrané na získanie hodnôt súčtu podľa mesiaca.
- „Marec“ Marcový mesiac vybratý tam, kde je požadovaná suma.
- Funkcia GETPIVOTDATA extrahuje súčet množstiev kategorizovaný ako uvedený argument, ako je vysvetlené vyššie.
Výsledok získate stlačením klávesu Enter.
Ako vidíte, vzorec vráti 302 ako súčet množstva v marcovom mesiaci. Tu môžete povedať, že tento vzorec je veľmi užitočný pri práci s kontingenčnou tabuľkou. Teraz, aby sme z kontingenčnej tabuľky získali súčet ceny v mesiaci január, použijeme rovnaký vzorec.
Použite vzorec:
| = GETPIVOTDATA („Suma ceny“, B3, „mesiace“, „január“) |
Suma ceny: súčet hodnôt ceny
B3: odkaz na zodpovedajúcu tabuľku
„Mesiace“: kritériá poľa
"Jan": kritériá
Ako vidíte, vzorec vráti súčet ceny 1705,82, ako je v tabuľke označené zelenou šípkou. Teraz rozumiete, ako získať kontingenčnú tabuľku z údajov, a potom zistiť medzisúčet zoskupený podľa dátumu ako kritérií. Kritériá môžu byť uvedené ako argument vo vzorci s úvodzovkami.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Kontingenčná tabuľka je nástroj na vytváranie kontingenčných tabuliek, ale funkcia GETPIVOTDATA extrahuje hodnotu z kontingenčnej tabuľky.
- Kontingenčná tabuľka funguje iba s hodnotou čísel.
- Pomocou kontingenčnej tabuľky môžete získať počet, súčet, priemer, rozptyl alebo štandardnú odchýlku údajov.
- Vo vzorci je možné zadať viac kritérií oddelených čiarkami pomocou úvodzoviek ("").
- Sledujte argument kľúčového slova vzorec, pretože vzorec vracia #REF! Chyba, ak nie je použitá správne.
Dúfam, že tento článok o tom, ako získať medzisúčty zoskupené podľa dátumu pomocou funkcie GETPIVOTDATA v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o funkciách kontingenčného grafu a tabuľky. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Kontingenčná tabuľka : Crunch your data numbers in a go using the PIVOT table tool in Excel.
Dynamická kontingenčná tabuľka : Crunch your nově added data with old data numbers using the dynamic PIVOT table tool in Excel.
Zobraziť hlavičku skrytého poľa v kontingenčnej tabuľke : Upraviť (zobraziť / skryť) hlavičku poľa nástroja tabuľky PIVOT v programe Excel.
Ako obnoviť kontingenčné grafy : Obnovte svoje grafy PIVOT v programe Excel, aby ste bez problémov získali aktualizovaný výsledok.
Populárne články:
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.