Počítání odlišných hodnot v kontingenční tabulce aplikace Excel (snadný průvodce krok za krokem)

Kontingenční tabulky aplikace Excel jsou úžasné (vím, že to zmiňuji pokaždé, když píšu o kontingenčních tabulkách, ale je to pravda).

Se základním porozuměním a malým přetažením můžete zvládnout spoustu práce za pár sekund.

Zatímco v kontingenčních tabulkách lze hodně udělat několika kliknutími, existují věci, které by vyžadovaly několik kroků navíc nebo trochu práce kolem.

A jedna taková věc je počítat odlišné hodnoty v kontingenční tabulce.

V tomto tutoriálu vám ukážu, jak počítat odlišné hodnoty a také jedinečné hodnoty v kontingenční tabulce aplikace Excel.

Než se ale vrhnu na to, jak počítat odlišné hodnoty, je důležité porozumět rozdílu mezi „odlišným počtem“ a „jedinečným počtem“

Rozdílný počet vs. jedinečný počet

I když to může vypadat jako totéž, to není.

Níže je uveden příklad, kde existuje datová sada jmen a já jsem vyjmenoval jedinečná a odlišná jména samostatně.

Jedinečné hodnoty/názvy jsou ty, které se vyskytují pouze jednou. To znamená, že všechna jména, která se opakují a mají duplikáty, nejsou jedinečná. Jedinečné názvy jsou uvedeny ve sloupci C ve výše uvedené datové sadě

Zřetelné hodnoty/názvy jsou ty, které se vyskytují alespoň jednou v datové sadě. Pokud se tedy jméno objeví třikrát, stále se počítá jako jedno odlišné jméno. Toho lze dosáhnout odstraněním duplicitních hodnot/jmen a zachováním všech odlišných. Zřetelné názvy jsou uvedeny ve sloupci B ve výše uvedené sadě dat.

Na základě toho, co jsem viděl, většina případů, kdy lidé říkají, že chtějí získat jedinečný počet v kontingenční tabulce, ve skutečnosti znamenají odlišný počet, což je to, co v tomto tutoriálu pokrývám.

Počítejte odlišné hodnoty v kontingenční tabulce aplikace Excel

Předpokládejme, že máte údaje o prodeji, jak je uvedeno níže:

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

S výše uvedenou datovou sadou řekněme, že chcete najít odpověď na následující otázky:

1. Kolik prodejních zástupců je v každé oblasti (což není nic jiného než odlišný počet prodejních zástupců v každé oblasti)?
2. Kolik obchodních zástupců prodalo tiskárnu v letech 2021-2022?

Zatímco kontingenční tabulky mohou okamžitě shrnout data několika kliknutími, abyste získali počet odlišných hodnot, budete muset udělat několik dalších kroků.

Pokud používáte Excel 2013 nebo jeho verze„V kontingenční tabulce je integrovaná funkce, která vám rychle poskytne zřetelný počet. A pokud používáte Excel 2010 nebo verze před tím, budete muset upravit zdrojová data přidáním pomocného sloupce.

V tomto kurzu jsou zahrnuty následující dvě metody:

• Přidání pomocného sloupce do původní sady dat pro počítání jedinečných hodnot (funguje ve všech verzích).
• Přidání dat do datového modelu a použití možnosti Rozdílný počet (k dispozici v aplikaci Excel 2013 a verzích po ní).

Existuje třetí metoda, kterou Roger ukazuje v tomto článku (kterou nazývá metoda Pivot the Pivot Table).

Začněme!

Přidání pomocného sloupce do datové sady

Poznámka: Pokud používáte Excel 2013 a vyšší verze, tuto metodu přeskočte a přejděte na další (protože používá vestavěnou funkci kontingenční tabulky - Zřetelný hrabě).

Toto je snadný způsob, jak počítat odlišné hodnoty v kontingenční tabulce, protože ke zdrojovým datům stačí přidat pomocný sloupec. Jakmile přidáte pomocný sloupec, můžete tuto novou sadu dat použít k výpočtu odlišného počtu.

I když se jedná o snadné řešení, existují určité nevýhody této metody (popsané dále v tomto kurzu).

Nejprve vám ukážu, jak přidat pomocný sloupec a získat zřetelný počet.

Předpokládejme, že mám sadu dat, jak je uvedeno níže:

Přidejte následující vzorec do sloupce F a použijte jej pro všechny buňky, které mají data v sousedních sloupcích.

= IF (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

Výše uvedený vzorec používá funkci COUNTIFS k počítání počtu zobrazení názvu v dané oblasti. Všimněte si také, že rozsah kritérií je $ C $ 2: C2 a $ B $ 2: B2. To znamená, že se stále rozšiřuje, když jdete dolů po sloupci.

Například v buňce E2 jsou rozsahy kritérií $ C $ 2: C2 a $ B $ 2: B2 a v buňce E3 se tyto rozsahy rozšíří na $ C $ 2: C3 a $ B $ 2: B3.

Tím je zajištěno, že funkce COUNTIFS počítá první instanci jména jako 1, druhou instanci jména jako 2 atd.

Protože chceme získat pouze odlišná jména, používá se funkce IF, která vrací 1, když se název pro oblast objeví poprvé, a vrací 0, když se objeví znovu. Tím je zajištěno, že se počítají pouze odlišná jména, nikoli opakování.

Níže uvádíme, jak by vaše datová sada vypadala, když jste přidali pomocný sloupec.

Nyní, když jsme upravili zdrojová data, můžeme toto použít k vytvoření kontingenční tabulky a pomocí pomocného sloupce získat odlišný počet obchodních zástupců v každé oblasti.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte libovolnou buňku v datové sadě.
2. Klikněte na kartu Vložit.
3. Klikněte na kontingenční tabulku (nebo použijte klávesovou zkratku - ALT + N + V)
4. V dialogovém okně Vytvořit kontingenční tabulku zkontrolujte, zda je tabulka/rozsah správná (a obsahuje pomocný sloupec) a zda je vybrána možnost „Nový pracovní list“.
5. Klikněte na OK.

Výše uvedené kroky by vložily nový list, který má kontingenční tabulku.

Přetáhněte pole „Region“ v oblasti Řádky a pole „D Počet“ v oblasti Hodnoty.

Získáte kontingenční tabulku, jak je uvedeno níže:

Nyní můžete změnit záhlaví sloupce z „Součet počtu D“ na „Prodejce“.

Nevýhody používání pomocného sloupce:

I když je tato metoda docela přímočará, musím zdůraznit několik nedostatků, které přicházejí s úpravou zdrojových dat v kontingenční tabulce:

• Zdroj dat se pomocným sloupcem není tak dynamický jako kontingenční tabulka. S kontingenční tabulkou můžete data libovolně rozřezávat a krájet, ale když použijete pomocný sloupec, ztratíte část této schopnosti. Řekněme, že přidáte pomocný sloupec, abyste získali počet odlišných prodejních zástupců v každé oblasti. Co teď, když chcete také získat značný počet prodejců, kteří prodávají tiskárny? Budete se muset vrátit ke zdrojovým datům a upravit vzorec pomocného sloupce (nebo přidat nový pomocný sloupec).
• Vzhledem k tomu, že do zdroje kontingenční tabulky přidáváte další data (která se také přidávají do kontingenční mezipaměti), může to vést k vyšší velikosti souboru aplikace Excel.
• Protože používáme vzorec aplikace Excel, může váš sešit aplikace Excel zpomalit v případě, že máte tisíce řádků dat.

Přidejte data do datového modelu a shrňte je pomocí odlišného počtu

Kontingenční tabulka přidala do Excelu 2013 nové funkce, které vám umožňují získat odlišný počet při sumarizaci datové sady.

V případě, že používáte předchozí verzi, tuto metodu nebudete moci použít (jako byste měli zkusit přidat pomocný sloupec, jak je uvedeno v metodě nad touto).

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete získat počet jedinečných obchodních zástupců v každé oblasti.

Níže jsou uvedeny kroky k získání odlišné hodnoty počtu v kontingenční tabulce:

1. Vyberte libovolnou buňku v datové sadě.
2. Klikněte na kartu Vložit.
3. Klikněte na kontingenční tabulku (nebo použijte klávesovou zkratku - ALT + N + V)
4. V dialogovém okně Vytvořit kontingenční tabulku zkontrolujte, zda je tabulka/rozsah správná a v seznamu Vybraný nový list.
5. Zaškrtněte políčko „Přidat tato data do datového modelu“
6. Klikněte na OK.

Výše uvedené kroky by vložily nový list, který má novou kontingenční tabulku.

Přetáhněte oblast v oblasti Řádky a obchodní zástupce v oblasti Hodnoty. Získáte kontingenční tabulku, jak je uvedeno níže:

Výše uvedená kontingenční tabulka uvádí celkový počet prodejních zástupců v každé oblasti (a nikoli odlišný počet).

Chcete -li v kontingenční tabulce získat odlišný počet, postupujte takto:

1. Klikněte pravým tlačítkem na libovolnou buňku ve sloupci „Počet zástupců prodeje“.
2. Klikněte na Nastavení hodnotového pole
3. V dialogovém okně Nastavení hodnotového pole vyberte jako typ výpočtu „Rozlišený počet“ (možná ho budete muset posunout dolů v seznamu).
4. Klikněte na OK.

Všimněte si, že název sloupce se změní z „Počet prodejních zástupců“ na „Rozlišený počet obchodních zástupců“. Můžete to změnit na cokoli chcete.

Některé věci, které znáte při přidávání dat do datového modelu:

• Pokud svá data uložíte do datového modelu a poté otevřete ve starší verzi Excelu, zobrazí se vám varování - „Některé funkce kontingenční tabulky nebudou uloženy“. Při otevření ve starší verzi, která to nepodporuje, se nemusí zobrazit odlišný počet (a datový model).
• Když přidáte data do datového modelu a vytvoříte kontingenční tabulku, nezobrazí možnosti přidání vypočítaných polí a vypočítaných sloupců.

Kliknutím sem stáhnete ukázkový soubor

Co když chcete počítat jedinečné hodnoty (a ne odlišné hodnoty)?

Pokud chcete počítat jedinečné hodnoty, nemáte v kontingenční tabulce žádné vestavěné funkce a budete se muset spolehnout pouze na pomocné sloupce.

Pamatujte - jedinečné hodnoty a odlišné hodnoty nejsou stejné. Kliknutím sem poznáte rozdíl.

Jedním příkladem může být situace, kdy máte níže uvedenou sadu dat a chcete zjistit, kolik obchodních zástupců je pro každou oblast jedinečných. To znamená, že působí pouze v jednom konkrétním regionu, nikoli v ostatních.

V takových případech musíte vytvořit jeden z více než jednoho pomocného sloupce.

V tomto případě platí následující vzorec:

= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

Výše uvedený vzorec kontroluje, zda se název obchodního zástupce vyskytuje pouze v jedné oblasti nebo ve více než jedné oblasti. Činí to tak, že spočítá počet výskytů jména v oblasti a vydělí jej celkovým počtem výskytů názvu. Pokud je hodnota menší než 1, znamená to, že se název vyskytuje ve dvou nebo více než dvou oblastech.

V případě, že se název vyskytuje ve více než jedné oblasti, vrátí 0, jinak vrátí jedničku.

Vzorec také kontroluje, zda se název opakuje ve stejné oblasti nebo ne. Pokud se název opakuje, pouze první instance názvu vrátí hodnotu 1 a všechny ostatní instance vrátí 0.

Může se to zdát trochu složité, ale opět záleží na tom, čeho se snažíte dosáhnout.

Pokud tedy chcete v kontingenční tabulce počítat jedinečné hodnoty, použijte pomocné sloupce a pokud chcete počítat odlišné hodnoty, můžete použít vestavěnou funkci (v aplikaci Excel 2013 a vyšší) nebo můžete použít pomocný sloupec.

Kliknutím sem stáhnete ukázkový soubor

Také by se vám mohly líbit následující výukové programy pro kontingenční tabulky:

• Jak filtrovat data v kontingenční tabulce v aplikaci Excel
• Jak seskupit data v kontingenčních tabulkách v aplikaci Excel
• Jak seskupit čísla v kontingenční tabulce v aplikaci Excel
• Jak použít podmíněné formátování v kontingenční tabulce v aplikaci Excel
• Kráječe v kontingenční tabulce aplikace Excel
• Jak aktualizovat kontingenční tabulku v aplikaci Excel
• Odstranit kontingenční tabulku v aplikaci Excel