Ako vytvoriť kontingenčnú tabuľku v Exceli na rozdelenie a rozdelenie údajov

Microsoft Excel 2016, ktorý sa nachádza v balíku Office, je najnovšia a najlepšia tabuľková aplikácia z Redmondu a existuje dobrý dôvod, prečo ju tak veľa používateľov čísel používa na všetky svoje potreby. Zatiaľ čo Excel je vhodný na jednoduché tabuľky na sledovanie výdavkov alebo zostavovanie kalendárov, príde na svoje, keď potrebujete krájať na kocky a potom prezentovať komplexné údaje. Tu vám ukážeme, ako vytvoriť kontingenčnú tabuľku v Exceli, aby ste mohli využiť jeden z najvýkonnejších nástrojov aplikácie.

Skôr ako začneme, na čo presne sú kontingenčné tabuľky dobré? Jednoducho povedané, kontingenčné tabuľky vám umožňujú pozerať sa na tie isté údaje rôznymi spôsobmi a z rôznych uhlov, aby sa uľahčilo vykonávanie hĺbkovej analýzy a zisťovanie dôležitých trendov. Keď napríklad hodnotíte výsledky predaja, možno sa budete chcieť pozrieť na konkrétnu osobu, konkrétny produkt alebo konkrétny časový rámec. Pomocou kontingenčnej tabuľky môžete vytvoriť jeden fond informácií a potom jednoducho zmeniť zameranie z jednej veci na druhú – analýzu, ktorú by bolo únavné vykonávať manuálne.

Všimnite si, že budete potrebovať plná verzia Excelu 2016 používať kontingenčné tabuľky. Univerzálna platforma Windows (UWP) Windows 10 verzia Excelu nezahŕňa to a mnohé ďalšie pokročilé funkcie Excelu. Nakoniec, ak chcete vyskúšať nové funkcie Excelu, pripojte sa Program Office Insider a možno budete prvý, kto zažije novú funkciu kontingenčnej tabuľky.

Krok 1: Pripravte si údaje

Snáď najdôležitejším krokom pri používaní kontingenčných tabuliek programu Excel je starostlivé usporiadanie údajov. Najjednoduchší spôsob, ako to urobiť, je použiť Excelové tabuľky, ktorá vám umožní pridať riadky, ktoré budú zahrnuté do vašej kontingenčnej tabuľky vždy, keď stlačíte obnovenie. Prinajmenšom však chcete, aby boli vaše údaje vo forme tabuľky s informatívnymi hlavičkami stĺpcov a konzistentnými údajmi v stĺpcoch.

Povedzme napríklad, že chcete analyzovať informácie o predaji vašej spoločnosti. Máte šesť predajcov, ktorí predávajú tri produkty vo viacerých regiónoch. Vaše produkty sú prispôsobené pre každého zákazníka, a preto sa ceny líšia. Tu je vzorová tabuľka s fiktívnymi informáciami, ktorá vám poskytne predstavu o tom, ako možno usporiadať údaje s ohľadom na kontingenčnú tabuľku.

Tabuľka bola vytvorená jednoducho tak, že najprv zadali údaje, potom vybrali celý rozsah a potom prešli na Vložiť > Tabuľka. Tento krok opäť nemusíte vykonať, ale odporúča sa, ak chcete neskôr pridať ďalšie riadky údajov a zjednodušiť aktualizáciu kontingenčnej tabuľky.

Označte Coppock/Digitálne trendy

Krok 2: Vyskúšajte odporúčanie

Excel je plný šikovných trikov na uľahčenie práce s údajmi a vždy, keď je to možné, sa pokúsi uhádnuť, čo chcete dosiahnuť, a potom automaticky vykoná niekoľko krokov. Túto užitočnú povahu tu demonštruje nástroj Odporúčané kontingenčné tabuľky, ktorý sa pozrie na vaše údaje a ponúka niekoľko logických možností, ako veci analyzovať a prezentovať.

Ak chcete použiť odporúčanú kontingenčnú tabuľku, jednoducho prejdite na Vložiť > Odporúčané kontingenčné tabuľky. Excel vám predstaví niekoľko možností, ktoré môžete zvážiť. V našom príklade Excel ponúka vytvorenie 10 rôznych kontingenčných tabuliek, ktoré zohľadňujú množstvo rôznych uhlov pohľadu na naše údaje o predaji. Všimnite si, že na tom, ako označíte stĺpce, záleží; Excel číta tieto hlavičky a ponúka odporúčania, ktoré dávajú najväčší zmysel. Ak sa napríklad chcete pozrieť na predajné ceny, nepoužívajte výraz „náklady“, pretože Excel podľa toho založí svoje odporúčanie.

Jedným z odporúčaní je „Súčet celkového predaja podľa zákazníka“. Ak zvolíme túto možnosť, Excel pristúpi k vytvoreniu kontingenčnej tabuľky.

Označte Coppock/Digitálne trendy

Všimnite si, že kontingenčná tabuľka zobrazuje iba údaje, ktoré sa týkajú našej súčasnej analýzy. Na pravej strane v dialógovom okne Polia kontingenčnej tabuľky nájdete kritériá, ktoré Excel použil na jeho vytvorenie. Čo znamená každé z týchto polí, sa budeme zaoberať v ďalšej časti o prispôsobení.

Krok 3: Prispôsobte si kontingenčnú tabuľku

Každá z položiek v tomto dialógovom okne je dôležitá na určenie toho, ako bude vaša kontingenčná tabuľka fungovať. Kliknutím na ozubené koliesko konfigurácie zmeníte vzhľad tohto dialógového okna tak, aby vám vyhovoval.

Výber polí

Tu si vyberáte, ktoré stĺpce chcete zahrnúť do kontingenčnej tabuľky. Ako sa tieto údaje naplnia v kontingenčnej tabuľke, je určené typom údajov, ktoré predstavujú – Excel zistí, či pridať stĺpec do kontingenčnej tabuľky alebo pridať údaje poľa do tabuľky. Ak napríklad vyberiete položku Položky, Excel predpokladá, že chcete pridať počet položiek pre každého zákazníka.

Označte Coppock/Digitálne trendy

Na druhej strane, ak vyberiete „Dátum“, Excel umiestni údaje do tabuľky a usporiada predaj podľa toho, kedy k nemu došlo.

Označte Coppock/Digitálne trendy

Pri práci na vlastných kontingenčných tabuľkách môžete experimentovať a zistiť, ako pridávanie a odstraňovanie polí ovplyvňuje zobrazené údaje. Zistíte, že Excel robí skvelú prácu pri vytváraní výberov, ktoré dávajú zmysel, ale môžete tiež zmeniť veci, ak voľby Excelu nedávajú zmysel.

Filtre

Ak chcete obmedziť zobrazované údaje, presuňte položky zo zoznamu na výber polí do sekcie „Filtre“. Ak napríklad pretiahnete položku „Zákazník“ do sekcie „Filtre“, môžete jednoducho zobraziť iba údaje od jedného alebo vybraných zákazníkov.

Označte Coppock/Digitálne trendy

Stĺpce

Presunutím polí do sekcie Stĺpce môžete rozšíriť spôsob vytvárania prehľadov údajov. Opäť platí, že keď pretiahnete pole do tejto sekcie, Excel sa pokúsi zistiť, ako chcete zobraziť údaje. Ak napríklad pretiahnete „Dátum“ do sekcie „Stĺpce“, Excel zobrazí zhrnuté tržby za najlogickejší časový rámec, ktorým je v tomto prípade mesiac. To by bolo užitočné, ak by vašou hlavnou starosťou bolo, koľko sa predalo mesačne, s ohľadom na nákupné vzorce zákazníkov.

Označte Coppock/Digitálne trendy

Riadky

Podobne môžete presunúť polia do sekcie „Riadky“ a vložiť tak rôzne údaje do riadkov kontingenčnej tabuľky. Opäť, ak pretiahneme „Dátum“ do sekcie „Riadky“, Excel rozdelí tržby podľa zákazníkov za mesiac, ale údaje budú zhrnuté podľa zákazníka a nie podľa mesiacov ako v predchádzajúcom príklade. V tomto prípade sa väčšinou obávate o to, koľko ste každému zákazníkovi predali, ale tiež chcete zistiť akékoľvek trendy založené na čase.

Označte Coppock/Digitálne trendy

hodnoty

Nakoniec sekcia „Hodnoty“ určuje, ako analyzujete svoje údaje. Vo všetkých našich doterajších príkladoch sme sledovali celkový predaj. Ak v tejto časti kliknete na kláves so šípkou nadol, môžete nakonfigurovať nastavenia poľa hodnôt tak, aby ste videli iný numerický výpočet.

Označte Coppock/Digitálne trendy

Povedzme napríklad, že sa namiesto celkového predaja chcete pozrieť na priemery predaja. V dialógovom okne nastavení poľa hodnôt by ste jednoducho vybrali „Priemerné“. Môžete tiež nastaviť formát čísel, aby výsledky dávali čo najväčší zmysel.

Označte Coppock/Digitálne trendy

Teraz namiesto zvažovania celkového predaja podľa zákazníka a následného výpočtu celkového súčtu sa pozeráme na priemerný predaj podľa spoločnosti a potom na priemerný predaj v celej spoločnosti. Pomohlo by to pri hodnotení, ktorí zákazníci sú napríklad v predaji nadpriemerní alebo podpriemerní, a teda ktorí si zaslúžia najväčšiu (alebo najmenšiu) pozornosť. V našom príklade si možno spoločnosti Acme Engineering a Jones Roofing nezaslúžia takú pozornosť predaja ako ostatné.

Je zrejmé, že kontingenčné tabuľky ponúkajú množstvo možností, ktoré vám uľahčia krájanie a delenie vašich údajov na kocky. Trik, ako efektívne používať kontingenčné tabuľky, je rozhodnúť sa presne, čo chcete vidieť, skôr ako začnete používať možnosti. Tiež sa chcete uistiť, že vaše údaje zodpovedajú tomu, ako ste si rozložili tabuľku a ako ste pomenovali hlavičky. Čím opatrnejší budete pri nastavovaní vecí, tým efektívnejšie budú vaše kontingenčné tabuľky.

Krok 4: Vytvorte si vlastné kontingenčné tabuľky od začiatku

Keď ste sa pohrali s funkciou kontingenčnej tabuľky a trochu porozumeli tomu, ako rôzne možnosti ovplyvňujú vaše údaje, môžete začať vytvárať kontingenčnú tabuľku od začiatku. Proces je podobný ako pri použití odporúčania, iba ak prejdete na Vložiť > Kontingenčná tabuľka a potom manuálne vyberiete svoje údaje ako prvý krok.

V našom prípade to znamená vybrať tabuľku1, ale mohli by sme vybrať aj rozsah údajov alebo stiahnuť z externého zdroja údajov. Môžeme sa tiež rozhodnúť, či chceme vytvoriť nový pracovný hárok alebo umiestniť kontingenčnú tabuľku vedľa našich údajov na určité miesto v existujúcom hárku.

Označte Coppock/Digitálne trendy

Po vykonaní výberu sa nám zobrazí prázdna kontingenčná tabuľka a dialógové okno Polia kontingenčnej tabuľky.

Označte Coppock/Digitálne trendy

Vytvorenie vlastnej kontingenčnej tabuľky je potom už len jednoduchou záležitosťou výberu polí a určením spôsobu výpočtu a zobrazenia údajov. Povedzme, že chceme vidieť, ako sa predajcom darilo za mesiac, s celkovým počtom predajov za rok. Vybrali by sme polia „Predajca“, „Dátum“ a „Celkový predaj“, presuňte pole „Predajca“ do sekcie „Filtre“ a nakonfigurovali by sme hodnoty tak, aby sa zobrazovali ako mena. Excel automaticky pridá príslušné dátumové položky do sekcie „Riadky“ a predpokladá, že chceme vidieť súčty.

Označte Coppock/Digitálne trendy

V predvolenom nastavení Excel zobrazí všetky údaje pre všetkých predajcov. Môžeme tiež vybrať jedného predajcu, aby sme videli iba jeho údaje. V tomto prípade vidíme, že Judy French mala tržby len za tri mesiace, hoci jej celkové tržby boli značné. To by mohlo znamenať, že jej predajný proces bol dlhší, pretože namiesto rýb išla po veľrybách – hodnotný záver, ak je presný. Možno by bol dobrý nápad investovať do asistenta, ktorý by pomohol Judy rýchlejšie ukončiť predaj.

Označte Coppock/Digitálne trendy

Krok 5: Investujte do učenia

Ak chcete získať naozaj ak máte dobré skúsenosti s používaním kontingenčných tabuliek Excelu, investujte nejaký čas do toho, aby ste sa dozvedeli viac o tom, ako používa rôzne typy údajov. Microsoft ponúka svoje vlastné školiace zdroje a existuje množstvo trénerov tretích strán zvážiť.

Vo všeobecnosti to však znamená kopať do Excelu spôsobom, ktorý presahuje rámec tejto príručky. Dúfajme však, že teraz vidíte, ako môžu byť kontingenčné tabuľky mocným nástrojom pri analýze vašich údajov a je relatívne ľahké začať, ak máte svoje údaje nakonfigurované na správny typ tabuľky. A nemôžeme dostatočne zdôrazniť, aké dôležité je vedieť, čo chcete pomocou kontingenčnej tabuľky dosiahnuť, skôr ako začnete.

Odporúčania redaktorov

• Používať Office? Váš počítač môže byť v dôsledku tejto zmeny od spoločnosti Microsoft ohrozený
• Ako chrániť heslom súbor programu Excel