Kontingenčné tabuľky sú jednou z najvýkonnejších funkcií programu Microsoft Excel. Umožňujú analyzovať veľké množstvo údajov a zhrnúť ich len niekoľkými kliknutiami myši. V tomto článku skúmame kontingenčné tabuľky, pochopíme, aké sú, a naučia sa ich vytvárať a upravovať.
Poznámka: Tento článok je napísaný pomocou programu Excel 2010 (Beta). Koncepcia kontingenčnej tabuľky sa v priebehu rokov menila málo, ale spôsob vytvorenia jedného sa zmenil takmer v každej iterácii programu Excel. Ak používate verziu programu Excel, ktorá nie je 2010, očakávajte rôzne obrazovky od obrazoviek, ktoré vidíte v tomto článku.
Malá história
V prvých dňoch programov tabuľkových procesov Lotus 1-2-3 ovládal hrad. Jeho dominancia bola taká úplná, že ľudia si mysleli, že je to strata času, aby sa spoločnosť Microsoft obťažovala vývojom vlastného softvéru na tabuľkový procesor (Excel), ktorý by konkuroval Lotusu. Flash-forward do roku 2010 a dominancia programu Excel na trhu s tabuľkovými procesormi je väčšia, než kedy bol Lotus, zatiaľ čo počet používateľov, ktorí stále používajú Lotus 1-2-3, sa blíži k nule. Ako sa to stalo? Čo spôsobilo také dramatické zvrátenie bohatstva?
Priemyselní analytici položili to na dva faktory: Po prvé, Lotus sa rozhodol, že táto fantastická nová platforma GUI s názvom "Windows" bola prechodná výstava, ktorá by nikdy nezačala. Oni odmietli vytvoriť Windows verziu Lotus 1-2-3 (na niekoľko rokov, tak ako tak), predpovedať, že ich DOS verzia softvéru bol všetko, kto by niekedy potreboval. Microsoft prirodzene vyvinul Excel výhradne pre Windows. Po druhé, Microsoft vyvinul funkciu pre Excel, ktorú Lotus neposkytol v 1-2-3, konkrétne kontingenčnej tabuľky, Funkcia kontingenčných tabuliek, výhradne pre program Excel, bola považovaná za ohromne užitočnú, že ľudia boli ochotní naučiť sa úplne nový softvérový balík (Excel), než aby sa držali programu (1-2-3), ktorý ho nemal. Táto jediná funkcia, spolu s nesprávnym posúdením úspechu systému Windows, bolo pre Lotus 1-2-3 a začiatok úspechu programu Microsoft Excel.
Pochopenie kontingenčných tabuliek
Takže čo je to kontingenčná tabuľka presne?
Jednoducho povedané, kontingenčná tabuľka je súhrn niektorých údajov vytvorených tak, aby umožňovali jednoduchú analýzu uvedených údajov. Na rozdiel od ručne vytvoreného zhrnutia sú však kontingenčné tabuľky programu Excel interaktívne. Akonáhle ste vytvorili jeden, môžete ho ľahko zmeniť, ak neponúka presné údaje o vašich údajoch, ktoré ste dúfali. Pri niekoľkých kliknutiach sa súhrn môže "otočiť" - otočiť tak, že nadpisy stĺpcov sa stávajú riadkami riadkov a naopak. Môžeme urobiť oveľa viac. Skôr než sa pokúsite popísať všetky funkcie kontingenčných tabuliek, jednoducho ich preukážeme …
Údaje, ktoré analyzujete pomocou kontingenčnej tabuľky, nemôžu byť jednoduché akýkoľvek údaje - to musí byť surový údaje, ktoré boli predtým nespracované (nevypočítané) - zvyčajne zoznam nejakého druhu. Príkladom toho môže byť zoznam predajných transakcií v spoločnosti za posledných šesť mesiacov.
Preskúmajte údaje uvedené nižšie:
Všimnite si, že to je nie nespracované dáta. V skutočnosti je to už zhrnutie nejakého druhu. V bunke B3 vidíme 30 000 dolárov, čo zrejme predstavuje celkový objem predajov Jamesa Cooka za január. Takže kde sú surové údaje? Ako sme dosiahli výšku 30 000 USD? Kde je pôvodný zoznam predajných transakcií, z ktorých bol tento údaj vytvorený? Je jasné, že niekto musí niekto za posledných šesť mesiacov prejsť do problému zhromažďovania všetkých obchodných transakcií do súhrnu, ktorý vidíme vyššie. Ako dlho to predpokladáte? Hodina? Desať?
Pravdepodobne áno. Vidíte, hore uvedená tabuľka je skutočne nie kontingenčnej tabuľky. Bola vytvorená ručne z nespracovaných údajov uložených inde, a naozaj to trvalo niekoľko hodín na zostavenie. Napriek tomu je to presne ten druh zhrnutia mohol byť vytvorené pomocou kontingenčných tabuliek, v takom prípade by to trvalo len niekoľko sekúnd. Poďme zistiť, ako …
Ak by sme mali sledovať pôvodný zoznam predajných transakcií, mohlo by to vyzerať takto:
Ako vytvoriť kontingenčnú tabuľku
Najprv sa uistite, že máte nejaké nespracované údaje v pracovnom hárku v programe Excel. Zoznam finančných transakcií je typický, ale môže to byť zoznam takmer čokoľvek: údaje o kontakte s zamestnancom, vašej zbierke CD alebo čísla o spotrebe paliva pre vozový park vašej spoločnosti.
Takže začneme Excel … a načítavame takýto zoznam …
Kliknite na ľubovoľnú jednu bunku v zozname:
Potom, z insert kliknite na kartu kontingenčnej ikona:
Vytvoriť kontingenčnú tabuľku zobrazí sa dvoma otázkami: Aké údaje by mala byť vaša nová kontingenčná tabuľka založená a kde by mala byť vytvorená? Pretože sme už klikli na bunku v zozname (v kroku vyššie), celý zoznam okolo tejto bunky je pre nás už vybraný ($ A $ 1: $ G $ 88 na platby list v tomto príklade). Všimnite si, že môžeme vybrať zoznam v akejkoľvek inej oblasti akéhokoľvek iného pracovného hárka alebo dokonca nejakého externého zdroja údajov, ako napríklad databázovú tabuľku programu Access alebo dokonca databázovú tabuľku MS-SQL Server. Musíme tiež vybrať, či chceme, aby naša nová kontingenčná tabuľka bola vytvorená v a Nový pracovný hárok alebo na existujúce jedna. V tomto príklade vyberieme a Nový one:
Zobrazí sa tiež ďalšia políčka: Zoznam polí kontingenčnej tabuľky, Tento zoznam polí sa zobrazí vždy, keď klikneme na akúkoľvek bunku v kontingenčnom tabuľke (vyššie):
Zoznam polí v hornej časti poľa je vlastne zbierka hlavičiek stĺpcov z pracovného hárka pôvodných nespracovaných údajov. Štyri prázdne políčka v dolnej časti obrazovky nám umožňujú vybrať spôsob, akým by sme chceli, aby naša kontingenčná tabuľka zhrnula nespracované údaje. Doteraz v týchto poliach nie je nič, takže kontingenčná tabuľka je prázdna. Všetko, čo musíme urobiť, je ťahať polia dole zo zoznamu vyššie a položiť ich do dolných políčok. Potom sa automaticky vytvorí kontingenčná tabuľka, ktorá zodpovedá našim pokynom. Ak sa to stane zle, stačí len presunúť polia späť na miesto, odkiaľ pochádzajú a / alebo pretiahnuť Nový polia na ich nahradenie.
hodnoty box je pravdepodobne najdôležitejšia zo štyroch. Pole, ktoré sa vtiahne do tohto poľa, predstavuje údaje, ktoré je potrebné zhrnúť nejakým spôsobom (súčet, spriemerovanie, nájdenie maxima, minima atď.). Je to takmer vždy číselný dát. Perfektným kandidátom na toto pole v našich vzorových údajoch je pole / čiastka "Suma". Presuňme toto pole do okna hodnoty box:
Všimnite si, že (a) pole "Suma" v zozname polí je teraz zaškrtnuté a "Suma sumy" bola pridaná k hodnoty že stĺpec sumy bol zhrnutý.
Ak preskúmame samotnú kontingenčnú tabuľku, nájdeme sumu všetkých hodnôt "Suma" z pracovného hárka nespracovaných údajov:
S odkazom na naše vzorové dáta musíme identifikovať jednu alebo viac hlavičiek stĺpcov, ktoré by sme mohli použiť na rozdelenie tejto sumy. Napríklad sa môžeme rozhodnúť, že by sme chceli zhrnúť naše údaje tam, kde máme riadok riadku pre každého z rôznych predajcov v našej spoločnosti a celkom pre každého. Aby sme to dosiahli, stačí len presunúť pole "Predajca" do poľa Riadkové štítky box:
teraz, nakoniec, veci začínajú byť zaujímavé! Naša kontingenčná tabuľka sa začína formovať ….
Takže čo ešte môžeme urobiť? No, v určitom zmysle je naša kontingencia úplná. Vytvorili sme užitočný súhrn našich zdrojových údajov. Dôležité veci sa už naučili! Pre zvyšok článku budeme skúmať niektoré spôsoby, ako vytvoriť zložitejšie kontingenčné tabuľky a spôsoby prispôsobenia týchto kontingenčných tabuliek.
Po prvé, môžeme vytvoriť dva-rozmerná tabuľka. Urobme to tak, že použijeme ako spôsob stĺpca metódu platby. Jednoducho presuňte nadpis "Spôsob platby" na položku Štítky stĺpcov box:
Začína sa dostať veľmi cool!
Urobme to tri-rozmerná tabuľka. Čo môže takáto tabuľka vyzerať? Nuž, uvidíme …
Presuňte stĺpec / balík "Balík" do nadpisu Filter prehľadov box:
Umožní nám to filtrovať správu, na základe ktorej bol zakúpený "dovolenkový balík". Zistíme napríklad rozpis predajcu voči platobnej metóde všetko balíky alebo niekoľkými kliknutiami ju môžete zmeniť tak, aby zobrazoval rovnaký rozpis balíka "Sunseekers":
Ak sa ukáže, povedzme, že chceme len vidieť šekom a kreditnou kartou transakcie (to znamená žiadne hotovostné transakcie), potom môžeme zrušiť výber položky "Hotovosť" zo záhlavia stĺpcov. Kliknite na rozbaľovaciu šípku vedľa položky Štítky stĺpcov, a zrušiť "hotovosť":
formátovanie
Je to samozrejme veľmi silný systém, ale doterajšie výsledky vyzerajú veľmi jasne a nudné. Na začiatok čísla, ktoré sumarizujeme, nevyzerajú ako sumy v dolároch - stačí len staré čísla. Opravte to.
Pokusom môže byť, aby sme v takýchto situáciách vykonali to, čo sme zvyknutí robiť, a jednoducho vybrať celú tabuľku (alebo celý pracovný hárok) a pomocou formátovacích tlačidiel štandardného čísla na paneli s nástrojmi dokončiť formátovanie. Problém s týmto prístupom je, že ak v budúcnosti niekedy zmeníte štruktúru kontingenčnej tabuľky (čo je pravdepodobne 99%), potom sa tieto formáty čísel stratia. Potrebujeme spôsob, ktorý ich bude (pol-) trvalý.
Najprv nájdeme položku "Sum Sum" v položke hodnoty a kliknite naň. Zobrazí sa menu. Vyberáme Nastavenia polí hodnoty … z ponuky:
Nastavenia polí hodnoty Zobrazí sa políčko.
Kliknite na tlačidlo Formát čísel tlačidlo a štandard Pole Formát buniek Zobrazí sa:
Z kategórie zoznam, vyberte (povedzme) Účtovné, a znížte počet desatinných miest na 0. Kliknite na OK niekoľkokrát sa vrátite na kontingenčnú tabuľku …
Zatiaľ čo sme v oblasti formátovania, formátme celý kontingenčný tabuľku. Existuje niekoľko spôsobov, ako to urobiť. Použite jednoduchý …
Kliknite na tlačidlo Kontingenčné nástroje / návrh Záložka:
Potom spustite šípku v pravom dolnom rohu Styly kontingenčnej tabuľky zobraziť zoznam obrovských zbierok vstavaných štýlov:
Ďalšie možnosti
Môžeme pracovať aj s dátumami. Teraz zvyčajne existuje veľa, veľa dátumov v zozname transakcií, ako je ten, s ktorým sme začali. Ale program Excel poskytuje možnosť zoskupiť dátové položky spoločne podľa dňa, týždňa, mesiaca, roku atď. Pozrime sa, ako sa to deje.
Najskôr odstráňte stĺpec "Spôsob platby" z Štítky stĺpcov (jednoducho ho presuňte späť do zoznamu polí) a nahraďte ho stĺpcom "Dátum rezervácie":
Ak to chcete opraviť, kliknite pravým tlačidlom myši na akýkoľvek dátum a vyberte Skupina … z kontextového menu:
Zobrazí sa okno zoskupenia. Vyberáme mesiaca a kliknite na tlačidlo OK:
Voila! veľa užitočnejšia tabuľka:
Ďalšou zaujímavou vecou je vedieť, že môžete mať viac ako jednu sadu riadkov (alebo stĺpcov):
Udržujte veci jednoduché znova, pozrime sa, ako to vykresliť priemerne namiesto súčtových hodnôt.
Najskôr kliknite na položku "Suma sumy" a vyberte položku Nastavenia polí hodnoty … z kontextovej ponuky, ktorá sa zobrazí:
V Sumárna hodnota poľa podľa zoznam v zozname Nastavenia polí hodnoty začiarknite políčko priemerný:
Zatiaľ čo sme tu, poďme to zmeniť Vlastné meno, z "priemernej sumy" na niečo trochu stručnejšie. Zadajte niečo ako "Avg":
kliknite OKa uvidíte, ako to vyzerá. Všimnite si, že všetky hodnoty sa menia zo sumárnych súčtov na priemery a názov tabuľky (horná ľavá bunka) sa zmenil na "Avg":
Tu sú kroky na získanie niečoho podobného (od prázdnej kontingenčnej tabuľky):
- Presuňte "Predajcu" do priečinka Štítky stĺpcov
- Presuňte pole "Suma" do poľa hodnoty box trikrát
- V prvom poli "Suma" zmeňte jeho vlastné meno na "Celkom" a je to číslo Účtovné (0 desatinné miesta)
- V druhom poli "Suma" zmeňte jeho vlastné meno na "Priemer", jeho funkcia na priemerný a je to číselný formát Účtovné (0 desatinné miesta)
- Pre tretie pole "Suma" zmeňte jeho názov na "Počet" a jeho funkciu na počítať
-
Presuňte automaticky vytvorené
Image pole od Štítky stĺpcov na Riadkové štítky
Tu je koniec:
záver
Existuje veľa, mnoho ďalších funkcií a možností pre kontingenčné tabuľky vytvorené programom Microsoft Excel - príliš veľa na to, aby sa v takomto článku zobrazili. Ak chcete úplne pokryť potenciál kontingenčných tabuliek, bude potrebná malá kniha (alebo veľká webová stránka). Statečné a / alebo podivínske čitatelia môžu zozbierať kontingenčné tabuľky pomerne ľahko: Jednoducho kliknite pravým tlačidlom myši na všetko a pozrite sa, aké možnosti vám budú k dispozícii. K dispozícii sú tiež dve pásky s páskou: Nástroje / Možnosti kontingenčnej tabuľky a dizajn, Nezáleží na tom, či urobíte chybu - je jednoduché vymazať kontingenčnú tabuľku a začať znova - možnosť, že starí používatelia aplikácie Lotus 1-2-3 nikdy nemali.
Ak pracujete v balíku Office 2007, môžete si pozrieť náš článok o tom, ako vytvoriť kontingenčnú tabuľku v programe Excel 2007.
Zahrnuli sme pracovný zošit programu Excel, ktorý si môžete stiahnuť, aby ste mohli praktizovať svoje zručnosti v kontingenčnej tabuľke. Mal by pracovať so všetkými verziami programu Excel od roku 97.
Stiahnite si pracovný zošit Practice programu Excel
