Obsah
Kontingenčné tabuľky je jedným z najvýkonnejších nástrojov v Exceli. Umožňujú vám analyzovať a zhrnúť rôzne súhrny veľkého množstva údajov pomocou niekoľkých kliknutí myšou. V tomto článku sa zoznámime s kontingenčnými tabuľkami, pochopíme, čo to je, naučíme sa ich vytvárať a prispôsobovať.
Tento článok bol napísaný pomocou Excelu 2010. Koncepcia kontingenčných tabuliek sa v priebehu rokov príliš nezmenila, no spôsob, akým ich vytvárate, sa v každej novej verzii Excelu mierne líši. Ak máte verziu Excelu nie 2010, pripravte sa, že snímky obrazovky v tomto článku sa budú líšiť od toho, čo vidíte na obrazovke.
Trochu histórie
V začiatkoch tabuľkového softvéru vládla lopta Lotus 1-2-3. Jeho dominancia bola taká úplná, že snahy Microsoftu vyvinúť vlastný softvér (Excel) ako alternatívu k Lotusu sa zdali stratou času. Teraz rýchlo vpred do roku 2010! Excel dominuje tabuľkám viac ako kód Lotusu vo svojej histórii a počet ľudí, ktorí stále používajú Lotus, sa blíži k nule. Ako sa to mohlo stať? Čo bolo dôvodom takého dramatického zvratu udalostí?
Analytici identifikujú dva hlavné faktory:
- Po prvé, Lotus sa rozhodol, že táto nová platforma GUI s názvom Windows je len prechodný výstrelok, ktorý nebude trvať dlho. Odmietli vytvoriť verziu Lotus 1-2-3 pre Windows (ale len na niekoľko rokov), pričom predpovedali, že verzia ich softvéru pre DOS bude všetko, čo spotrebitelia kedy budú potrebovať. Microsoft prirodzene vyvinul Excel špeciálne pre Windows.
- Po druhé, Microsoft predstavil nástroj v Exceli s názvom PivotTables, ktorý nebol dostupný v Lotus 1-2-3. Kontingenčné tabuľky, exkluzívne pre Excel, sa ukázali ako veľmi užitočné, že ľudia mali tendenciu zostať pri novom softvérovom balíku Excel, než pokračovať v Lotus 1-2-3, ktorý ich nemal.
Kontingenčné tabuľky spolu s podceňovaním úspechu systému Windows vo všeobecnosti hrali pochod smrti pre Lotus 1-2-3 a predznamenali úspech programu Microsoft Excel.
Čo sú to kontingenčné tabuľky?
Aký je teda najlepší spôsob, ako charakterizovať, čo sú kontingenčné tabuľky?
Jednoducho povedané, kontingenčné tabuľky sú súhrny niektorých údajov, vytvorené na uľahčenie analýzy týchto údajov. Na rozdiel od manuálne vytvorených súčtov sú kontingenčné tabuľky Excel interaktívne. Po vytvorení ich môžete ľahko upraviť, ak nedávajú obrázok, v ktorý ste dúfali. Len niekoľkými kliknutiami myšou je možné súčty prevrátiť tak, že nadpisy stĺpcov sa stanú nadpismi riadkov a naopak. Pomocou kontingenčných tabuliek môžete robiť veľa vecí. Namiesto toho, aby ste sa pokúšali opísať slovami všetky funkcie kontingenčných tabuliek, je jednoduchšie ich demonštrovať v praxi...
Údaje, ktoré analyzujete pomocou kontingenčných tabuliek, nemôžu byť náhodné. Mali by to byť nespracované nespracované údaje, napríklad zoznam nejakého druhu. Môže to byť napríklad zoznam predajov uskutočnených spoločnosťou za posledných šesť mesiacov.
Pozrite sa na údaje zobrazené na obrázku nižšie:
Upozorňujeme, že nejde o nespracované nespracované údaje, pretože už boli zhrnuté. V bunke B3 vidíme 30000 30000 dolárov, čo je pravdepodobne celkový výsledok, ktorý James Cook dosiahol v januári. Kde sú teda pôvodné údaje? Odkiaľ pochádza XNUMX XNUMX dolárov? Kde je pôvodný zoznam predajov, z ktorých bol odvodený tento mesačný súčet? Je jasné, že niekto odviedol skvelú prácu, keď zorganizoval a zoradil všetky údaje o predaji za posledných šesť mesiacov a premenil ich na tabuľku súčtov, ktorú vidíme. Ako dlho to podľa vás trvalo? hodinu? Desať?
Faktom je, že vyššie uvedená tabuľka nie je kontingenčnou tabuľkou. Bol ručne vyrobený z nespracovaných údajov uložených inde a jeho spracovanie trvalo aspoň pár hodín. Práve takúto súhrnnú tabuľku je možné vytvoriť pomocou kontingenčných tabuliek v priebehu niekoľkých sekúnd. Poďme zistiť, ako…
Ak by sme sa vrátili k pôvodnému predajnému zoznamu, vyzeral by asi takto:
Možno vás prekvapí, že z tohto zoznamu obchodov pomocou kontingenčných tabuliek a v priebehu niekoľkých sekúnd vytvoríme mesačný prehľad predaja v Exceli, ktorý sme rozobrali vyššie. Áno, dokážeme to a ešte viac!
Ako vytvoriť kontingenčnú tabuľku?
Najprv sa uistite, že máte nejaké zdrojové údaje v hárku programu Excel. Zoznam finančných transakcií je najtypickejší. V skutočnosti to môže byť zoznam čohokoľvek: kontaktné údaje zamestnancov, zbierka CD alebo údaje o spotrebe paliva vašej spoločnosti.
Takže spustíme Excel ... a načítame takýto zoznam ...
Po otvorení tohto zoznamu v Exceli môžeme začať vytvárať kontingenčnú tabuľku.
Vyberte ľubovoľnú bunku z tohto zoznamu:
Potom na karte vloženie (Vložiť) vyberte príkaz Kontingenčná tabuľka (kontingenčná tabuľka):
Zobrazí sa dialógové okno Vytvorte kontingenčnú tabuľku (Vytvorenie kontingenčnej tabuľky) s dvoma otázkami:
- Aké údaje použiť na vytvorenie novej kontingenčnej tabuľky?
- Kam umiestniť kontingenčnú tabuľku?
Keďže v predchádzajúcom kroku sme už vybrali jednu z buniek zoznamu, na vytvorenie kontingenčnej tabuľky sa automaticky vyberie celý zoznam. Všimnite si, že môžeme vybrať iný rozsah, inú tabuľku a dokonca aj nejaký externý zdroj údajov, ako napríklad databázovú tabuľku Accessu alebo MS-SQL. Okrem toho si musíme vybrať, kam umiestniť novú kontingenčnú tabuľku: na nový hárok alebo na jeden z existujúcich. V tomto príklade vyberieme možnosť – Nový pracovný list (na nový list):
Excel vytvorí nový hárok a umiestni naň prázdnu kontingenčnú tabuľku:
Hneď ako klikneme na ľubovoľnú bunku v kontingenčnej tabuľke, zobrazí sa ďalšie dialógové okno: Zoznam polí kontingenčnej tabuľky (Polia kontingenčnej tabuľky).
Zoznam polí v hornej časti dialógového okna je zoznamom všetkých titulov z pôvodného zoznamu. Štyri prázdne oblasti v spodnej časti obrazovky vám umožňujú povedať kontingenčnej tabuľke, ako chcete zhrnúť údaje. Pokiaľ sú tieto oblasti prázdne, v tabuľke nie je nič. Jediné, čo musíme urobiť, je pretiahnuť nadpisy z hornej oblasti do prázdnych oblastí nižšie. Zároveň sa automaticky vygeneruje kontingenčná tabuľka v súlade s našimi pokynmi. Ak sa pomýlime, môžeme odstrániť nadpisy zo spodnej oblasti alebo presunúť iné a nahradiť ich.
Plocha hodnoty (Významy) je pravdepodobne najdôležitejší zo štyroch. Ktorý nadpis sa v tejto oblasti umiestni, určuje, aké údaje sa budú sumarizovať (súčet, priemer, maximum, minimum atď.) Takmer vždy ide o číselné hodnoty. Výborným kandidátom na miesto v tejto oblasti sú údaje pod hlavičkou čiastka (Cena) nášho pôvodného stola. Presuňte tento názov do oblasti hodnoty (Hodnoty):
Upozorňujeme, že názov čiastka je teraz označené začiarknutím a v oblasti hodnoty (Hodnoty) objavila sa položka Suma sumy (pole Čiastka Čiastka), čo znamená, že stĺpec čiastka zhrnul.
Ak sa pozrieme na samotnú kontingenčnú tabuľku, uvidíme súčet všetkých hodnôt zo stĺpca čiastka originálny stôl.
Naša prvá kontingenčná tabuľka je teda vytvorená! Pohodlné, ale nie mimoriadne pôsobivé. Pravdepodobne chceme získať viac informácií o našich údajoch, ako momentálne máme.
Vráťme sa k pôvodným údajom a skúsme identifikovať jeden alebo viac stĺpcov, pomocou ktorých je možné túto sumu rozdeliť. Napríklad našu kontingenčnú tabuľku môžeme zostaviť tak, že celková suma predaja sa vypočíta pre každého predajcu individuálne. Tie. do našej kontingenčnej tabuľky sa pridajú riadky s menom každého predajcu v spoločnosti a ich celkovou sumou predaja. Ak chcete dosiahnuť tento výsledok, jednoducho presuňte názov predajca (obchodný zástupca) do regiónu Štítky riadkov (Struny):
Stáva sa to zaujímavejšie! Naša kontingenčná tabuľka sa začína formovať...
Vidíte výhody? Pár kliknutiami sme vytvorili tabuľku, ktorej manuálne vytvorenie by trvalo veľmi dlho.
Čo ešte môžeme urobiť? No v istom zmysle je naša kontingenčná tabuľka pripravená. Vytvorili sme užitočný súhrn pôvodných údajov. Dôležité informácie už boli doručené! Vo zvyšku tohto článku sa pozrieme na niektoré spôsoby vytvárania zložitejších kontingenčných tabuliek a tiež sa naučíme, ako ich prispôsobiť.
Nastavenie kontingenčnej tabuľky
Najprv môžeme vytvoriť dvojrozmernú kontingenčnú tabuľku. Urobme to pomocou nadpisu stĺpca Spôsob platby (Spôsob platby). Stačí pretiahnuť názov Spôsob platby do oblasti Štítky stĺpcov (Stĺpce):
Dostaneme výsledok:
Vyzerá veľmi cool!
Teraz urobme trojrozmernú tabuľku. Ako by taký stôl vyzeral? Pozrime sa…
Presuňte hlavičku Balíček (Komplex) do oblasti filtre prehľadov (Filtre):
Všimnite si, kde je…
To nám dáva možnosť filtrovať prehľad na základe „Ktorý dovolenkový komplex bol zaplatený“. Môžeme napríklad vidieť rozdelenie podľa predajcov a spôsobov platby pre všetky komplexy alebo niekoľkými kliknutiami myšou zmeniť pohľad na kontingenčnú tabuľku a zobraziť rovnaké rozdelenie iba pre tých, ktorí si objednali komplex Slniečkári.
Takže, ak tomu správne rozumiete, potom sa naša kontingenčná tabuľka môže nazývať trojrozmerná. Pokračujme v nastavovaní…
Ak sa zrazu ukáže, že v kontingenčnej tabuľke by sa mala zobrazovať iba platba šekom a kreditnou kartou (teda bezhotovostná platba), tak môžeme vypnúť zobrazovanie nadpisu peňažný (Hotovosť). Za to vedľa Štítky stĺpcov kliknite na šípku nadol a zrušte začiarknutie políčka v rozbaľovacej ponuke peňažný:
Pozrime sa, ako teraz vyzerá naša kontingenčná tabuľka. Ako vidíte, stĺpec peňažný zmizla z nej.
Formátovanie kontingenčných tabuliek v Exceli
Kontingenčné tabuľky sú samozrejme veľmi výkonným nástrojom, ale zatiaľ výsledky vyzerajú trochu jednoducho a nudne. Napríklad čísla, ktoré spočítame, nevyzerajú ako sumy v dolároch – sú to len čísla. Poďme to napraviť.
Je lákavé urobiť v takejto situácii to, na čo ste zvyknutí a jednoducho vybrať celú tabuľku (alebo celý hárok) a pomocou štandardných tlačidiel formátovania čísel na paneli nástrojov nastaviť požadovaný formát. Problém s týmto prístupom je, že ak niekedy v budúcnosti zmeníte štruktúru kontingenčnej tabuľky (čo sa stane s 99% pravdepodobnosťou), formátovanie sa stratí. Potrebujeme spôsob, ako to urobiť (takmer) trvalým.
Najprv nájdime záznam Suma sumy in hodnoty (Hodnoty) a kliknite naň. V zobrazenej ponuke vyberte položku Nastavenia poľa hodnôt (Možnosti poľa hodnôt):
Zobrazí sa dialógové okno Nastavenia poľa hodnôt (Možnosti poľa hodnôt).
lis formát čísel (Formát čísla), otvorí sa dialógové okno. formátovanie buniek (formát bunky):
Zo zoznamu kategórie (Číselné formáty) vyberte účtovné (Finančné) a nastavte počet desatinných miest na nulu. Teraz stlačte niekoľkokrát OKaby sme sa vrátili do našej kontingenčnej tabuľky.
Ako vidíte, čísla sú formátované ako sumy v dolároch.
Keď už sme pri formátovaní, poďme nastaviť formát pre celú kontingenčnú tabuľku. Existuje niekoľko spôsobov, ako to urobiť. Používame ten, ktorý je jednoduchší...
Kliknite na tlačidlo Nástroje kontingenčnej tabuľky: Dizajn (Práca s kontingenčnými tabuľkami: Konštruktor):
Ďalej rozbaľte ponuku kliknutím na šípku v pravom dolnom rohu sekcie Štýly kontingenčnej tabuľky (Štýly kontingenčnej tabuľky) a pozrite si rozsiahlu kolekciu vložených štýlov:
Vyberte si akýkoľvek vhodný štýl a pozrite sa na výsledok v kontingenčnej tabuľke:
Ďalšie nastavenia kontingenčnej tabuľky v Exceli
Niekedy je potrebné filtrovať údaje podľa dátumov. Napríklad v našom zozname obchodov je veľa, veľa dátumov. Excel poskytuje nástroj na zoskupenie údajov podľa dňa, mesiaca, roku atď. Pozrime sa, ako sa to robí.
Najprv odstráňte záznam. Spôsob platby z regiónu Štítky stĺpcov (Stĺpce). Ak to chcete urobiť, presuňte ho späť do zoznamu titulov a na jeho miesto presuňte názov Dátum rezervácie (dátum rezervácie):
Ako vidíte, naša kontingenčná tabuľka je dočasne zbytočná. Excel vytvoril samostatný stĺpec pre každý dátum, kedy sa obchod uskutočnil. V dôsledku toho sme dostali veľmi široký stôl!
Ak to chcete opraviť, kliknite pravým tlačidlom myši na ľubovoľný dátum a vyberte z kontextovej ponuky GROUP (Skupina):
Zobrazí sa dialógové okno zoskupenia. Vyberáme Mesiacov (Mesiace) a kliknite OK:
Voila! Táto tabuľka je oveľa užitočnejšia:
Mimochodom, táto tabuľka je takmer totožná s tou uvedenou na začiatku článku, kde boli súčty predajov zostavené ručne.
Existuje ďalší veľmi dôležitý bod, ktorý musíte vedieť! Môžete vytvoriť nie jednu, ale niekoľko úrovní nadpisov riadkov (alebo stĺpcov):
...a bude to vyzerať takto...
To isté možno urobiť s nadpismi stĺpcov (alebo dokonca filtrami).
Vráťme sa k pôvodnej podobe tabuľky a pozrime sa, ako namiesto súčtov zobraziť priemery.
Ak chcete začať, kliknite na Suma sumy a zo zobrazenej ponuky vyberte Nastavenia poľa hodnôt (Možnosti poľa hodnôt):
Zoznam Zhrnúť pole hodnoty podľa (Operácia) v dialógovom okne Nastavenia poľa hodnôt (Možnosti poľa hodnoty) vyberte priemerný (Priemer):
Zároveň, kým sme tu, zmeňme sa CustomName (Vlastný názov) s Priemer sumy (pole Čiastka Čiastka) na niečo kratšie. Do tohto poľa zadajte niečo ako avg:
lis OK a uvidíte, čo sa stane. Všimnite si, že všetky hodnoty sa zmenili zo súčtu na priemery a hlavička tabuľky (v ľavej hornej bunke) sa zmenila na avg:
Ak chcete, môžete okamžite získať množstvo, priemer a počet (tržby) umiestnené v jednej kontingenčnej tabuľke.
Tu je podrobný návod, ako to urobiť, počnúc prázdnou kontingenčnou tabuľkou:
- Presuňte hlavičku predajca (obchodný zástupca) do regiónu Štítky stĺpcov (Stĺpce).
- Potiahnite názov trikrát čiastka (Náklady) na oblasť hodnoty (Hodnoty).
- Pre prvé pole čiastka zmeniť názov na Spolu (Suma) a formát čísla v tomto poli je účtovné (Finančné). Počet desatinných miest je nula.
- Druhé pole čiastka názov Priemere, nastavte preň operáciu priemerný (Priemer) a formát čísla v tomto poli sa tiež zmenia na účtovné (Finančné) s nula desatinnými miestami.
- Pre tretie pole čiastka nastaviť názov Spočítať a operácia pre neho - Spočítať (množstvo)
- V Štítky stĺpcov Pole (Stĺpce) sa vytvorí automaticky Hodnoty Σ (Σ Hodnoty) – presuňte ho do oblasti Štítky riadkov (čiary)
Tu je to, čo skončíme:
Celková suma, priemerná hodnota a počet predajov – všetko v jednej kontingenčnej tabuľke!
záver
Kontingenčné tabuľky v programe Microsoft Excel obsahujú množstvo funkcií a nastavení. V takom malom článku ani zďaleka nepokrývajú všetky. Na úplný popis všetkých možností kontingenčných tabuliek by bola potrebná malá kniha alebo veľká webstránka. Odvážni a zvedaví čitatelia môžu pokračovať v skúmaní kontingenčných tabuliek. Ak to chcete urobiť, stačí kliknúť pravým tlačidlom myši na takmer ľubovoľný prvok kontingenčnej tabuľky a zistiť, aké funkcie a nastavenia sa otvárajú. Na páse s nástrojmi nájdete dve karty: Nástroje kontingenčnej tabuľky: Možnosti (analýza) a dizajn (Konštruktér). Nebojte sa urobiť chybu, kontingenčnú tabuľku môžete kedykoľvek vymazať a začať odznova. Máte príležitosť, ktorú dlhoroční používatelia DOS a Lotus 1-2-3 nikdy nemali.