Excel

Prečo kontingenčné tabuľky?

Why Pivot Tables

Praktický pracovný list je súčasťou online video školenie .

Ahoj, Dave, tu z Exceljet





Dnes sa teda budeme zaoberať otázkou Prečo kontingenčné tabuľky?

A táto otázka sa vynára často, keď ľudia prvýkrát narazia na kontingenčné tabuľky, pretože sa pýtajú ... prečo by ich to malo zaujímať? A pokúšate sa vysvetliť, že kontingenčné tabuľky sú naozaj rýchle, že údaje môžete interaktívne sumarizovať rôznymi spôsobmi ... bla, bla bla





A môžete tak trochu vidieť, ako sa im oči len tak lesknú, pretože kým sami kontingenčný stolík skutočne nepoužívate, je skutočne ťažké pochopiť, prečo by vám boli užitočné.

Myslím si, že jedným z najlepších spôsobov, ako porozumieť tomu, ako môžu kontingenčné tabuľky pomôcť, je pozrieť sa na niekoľko príkladov vytvárania niektorých zostáv vedľa seba, kde jedna skupina zostáv zostavujeme ručne pomocou bežných funkcií a vzorcov programu Excel a druhú množinu, staviame na kontingenčných tabuľkách.



A presne to sa v dnešnom videu chystalo.

A chcem tu byť s vami skutočne vpredu. Milujem kontingenčné tabuľky.

Odkedy som v 90. rokoch dostal svoju prvú kontingenčnú tabuľku na správnu obnovu, uvedomil som si, že sú to skutočne užitočné nástroje.

Myslím si teda, že ak používate Excel s veľkým počtom nespracovaných údajov a potrebujete tieto údaje zhrnúť rôznymi spôsobmi a ak potrebujete vytvárať dobre vyzerajúce zostavy, potom si kontingenčné tabuľky určite zaslúžia váš čas.

Dúfam teda, že do konca videa so mnou budete súhlasiť.

Poďme sa pozrieť na niekoľko príkladov.

****

Tu sme v Exceli a vidíte, že máme súbor nespracovaných údajov.

Tieto údaje predstavujú predaj čokolády niekoľkým zákazníkom v rôznych lokalitách, na veľkoobchodnej úrovni.

Môžete vidieť, že máme dátum a zákazníka. Mesto, štát a región, produkt a kategória produktu a nakoniec množstvo, výnosy, náklady na predaný tovar a zisk.

Teraz si predstavte, že vám váš manažér pošle tieto údaje a požiada vás o jednostránkové zhrnutie, ktoré ukazuje rozdelenie tržieb podľa produktu.

Za predpokladu, že neviete, ako používať kontingenčné tabuľky, ale ovládate vzorce Excelu, najskôr vytvoríme manuálny prehľad.

Najľahšie to urobíte pomocou funkcie SUMIFS. SUMIFS sa ľahko používa, ale môže vytvoriť niekoľko škaredých vzorcov, takže aby som vám uľahčil čítanie a vyhol sa mnohým absolútnym odkazom, pokračujem v názve stĺpcov.

Teraz máme názov pre každý stĺpec v údajoch.

Keď som tu, pomenujem aj celú sadu údajov, aby som sa k nim mohol ľahko odvolať, keď neskôr vytvorím kontingenčnú tabuľku.

Teraz vytvoríme manuálnu správu.

Najprv si vezmem kópiu všetkých produktov a potom odstránim duplikáty a zoraďujem.

Teraz pridám niekoľko štítkov a formátovanie.

Takže kosti správy máme teraz k dispozícii, ale žiadne údaje. A je čas napísať prvý vzorec SUMIFS. V skutočnosti by som mohol použiť SUMIF. Ale pretože v priebehu minúty pridám ďalšie kritériá, bude jednoduchšie, ak začneme s SUMIF.

Pretože som už stĺpce pomenoval, môžem názvy bez problémov písať bez toho, aby som sa vracal k výberu údajov.

Teraz musím skopírovať vzorec a pridať súčet a čísla naformátujem ako menu.

Nie, máme základnú správu, ktorú chce váš manažér ... jednostranovú správu o predaji s rozdelením podľa produktov.

Manuálny prístup teda nie je taký hrozný, ale samozrejme, že som už vedel, ako na to, a na urýchlenie som použil množstvo tipov a skratiek.

Ak nie ste takí spokojní so vzorcami, bude to oveľa ťažšie, s množstvom chýb a učenia sa za pochodu.

Teraz vytvoríme rovnakú zostavu pomocou kontingenčnej tabuľky.

Keďže som údaje už pomenoval, môžem tu použiť tento názov.

Budeme potrebovať produkt ako štítok riadka a výnosy ako hodnotu.

A potom na výnosy použite číselný formát.

A to je všetko. Teraz máme kontingenčnú tabuľku s rovnakými výsledkami. A všimnite si, že som vôbec nepoužil žiadne vzorce.

Samozrejme, žiadny manažér sa nikdy neuspokojí s ich pôvodnými pokynmi. Všetci budú požadovať zmeny.

Pozrime sa teda na to, ako oba tieto prehľady zvládajú niekoľko žiadostí o zmeny.

Najprv povedzme, že sa váš manažér vráti a požiada o členenie podľa produktu a regiónu. Chce región cez vrchol.

Pokiaľ ide o manuálny prehľad, začnem tým, že si zaobstarám kópiu všetkých regiónov, a budem postupovať rovnako ako predtým pri výrobkoch.

Dnes musím transponovať regióny cez vrchol.

Potom musím trochu poupratovať.

vytvoriť rozbaľovací zoznam v programe Excel 2010

Ďalej musím rozšíriť funkciu SUMIFS tak, aby zvládala produkt aj región.

Budem musieť zamknúť referenciu na stĺpec pre produkt ... pretože to skopírujem o chvíľu ... potom pridajte nový rozsah a kritériá pre región so zamknutým riadkom.

Teraz môžem skopírovať vzorce do tabuľky a pridať súčty.

Opäť to nie je také hrozné, ale veľa kliknutí a veľa vzorcov.

Zmena zostavy kontingenčnej tabuľky je o niečo jednoduchšia. Všetko, čo musím urobiť, je pridať región ako štítok stĺpca a máme hotovo.

Opäť nie je potrebné aktualizovať žiadne vzorce.

Tiež zmením rozloženie kontingenčnej tabuľky na „obrys“ a znova ho zarovnám s manuálnym prehľadom.

Odovzdáte to teda svojej vedúcej a o hodinu neskôr povie, že je to skvelé. Ale môže tiež vidieť rozdelenie podľa roku namiesto regiónu.

Hmmm. Bude to náročnejšie.

V tomto prípade viem, že údaje zaberajú iba 3 roky .... 2011, 2012 a 2013, takže začnem tam a pridám ich ručne ako hlavičky a zbavím sa stĺpca navyše.

Teraz stačí aktualizovať vzorce.

Toto je skutočne dobrý prípad použitia pre funkciu SUMPRODUCT, ale to by si vyžadovalo oveľa viac vysvetlenia, takže zatiaľ zostaneme pri SUMIFS.

Teraz, aby sme aktualizovali funkciu SUMIFS tak, aby zodpovedala dátumom podľa roku, budeme musieť použiť dve kritériá spolu s funkciou DATE a určitým zreťazením.

Prvé kritérium sa zhoduje s dátumom, ktorý je väčší alebo rovný prvému roku.

Druhé kritérium sa zhoduje s dátumom, ktorý je menší alebo rovný poslednému dňu v roku.

V oboch prípadoch odkazujeme na rok priamo a musíme uzamknúť riadok.

Keď skopírujem vzorec dole a dole, máme správu.

Vidíte, že táto zmena bola trochu komplikovanejšia a vyžadovala si dobré znalosti vzorcov a funkcií Excelu a konceptov, ako je zreťazenie.

Čo kontingenčná tabuľka?

Na aktualizáciu kontingenčnej tabuľky sa musím najskôr zbaviť oblasti.

Potom musím pridať pole s dátumom a zoskupiť podľa roku.

Rovnako ako predtým neexistujú žiadne vzorce na aktualizáciu.

Takže chápete. Tieto správy je možné úplne vytvoriť ručne a v niektorých prípadoch je žiaduce to urobiť týmto spôsobom. Použitie kontingenčnej tabuľky je však oveľa rýchlejšie a jednoduchšie.

Kontingenčná tabuľka môže v konečnom dôsledku vytvárať oveľa komplexnejšie správy, než by ste kedy chceli riešiť ručne.

Dobre, dúfal som, že sa vám páči to porovnanie ručného vytvárania niektorých zostáv a rovnakých zostáv v kontingenčných tabuľkách. Ako vidíte, nie je to ani zďaleka, kontingenčné tabuľky sú oveľa rýchlejšie.

Myslím si, že pri akejkoľvek analýze údajov v programe Excel by ste mali premýšľať o použití kontingenčných tabuliek.

Ale okrem jednoduchého pohodlia vám kontingenčné tabuľky poskytujú skutočnú konkurenčnú výhodu.

Takže či už vnútorne súťažíte so svojimi rovesníkmi o zvýšenie alebo povýšenie, alebo len o uznanie.

Alebo ak externe súťažíte s inými spoločnosťami na vašom trhu o identifikáciu trendov alebo príležitostí, kontingenčné tabuľky sú nástrojom, ktorý vám môže pomôcť vyniknúť a lepšie vykonávať svoju prácu.



^