Spočítajte odlišné hodnoty v kontingenčnej tabuľke programu Excel (jednoduchý sprievodca krok za krokom)

Kontingenčné tabuľky programu Excel sú úžasné (viem, že to spomínam vždy, keď píšem o kontingenčných tabuľkách, ale je to pravda).

So základným porozumením a trochou ťahania a pustenia môžete zvládnuť kus práce za niekoľko sekúnd.

Aj keď sa v kontingenčných tabuľkách dá veľa urobiť niekoľkými kliknutiami, existujú veci, ktoré by vyžadovali niekoľko ďalších krokov alebo trochu práce.

A jedna taká vec je spočítať odlišné hodnoty v kontingenčnej tabuľke.

V tomto tutoriále vám ukážem, ako počítať odlišné hodnoty, ako aj jedinečné hodnoty v kontingenčnej tabuľke programu Excel.

Predtým, ako sa pustím do počítania odlišných hodnôt, je dôležité porozumieť rozdielu medzi „odlišným počtom“ a „jedinečným počtom“.

Rozdielny počet vs. jedinečný počet

Aj keď to môže vyzerať ako to isté, nie je.

Nasleduje príklad, kde existuje množina údajov a ja som uviedol jedinečné a odlišné mená oddelene.

Jedinečné hodnoty/názvy sú tie, ktoré sa vyskytujú iba raz. To znamená, že všetky názvy, ktoré sa opakujú a majú duplikáty, nie sú jedinečné. Jedinečné názvy sú uvedené v stĺpci C vo vyššie uvedenej množine údajov

Rozlišujúce hodnoty/názvy sú tie, ktoré sa vyskytujú najmenej raz v súbore údajov. Ak sa teda meno objaví trikrát, stále sa počíta ako jedno odlišné meno. To sa dá dosiahnuť odstránením duplicitných hodnôt/názvov a zachovaním všetkých odlišných. Rozlišujúce názvy sú uvedené v stĺpci B vo vyššie uvedenom súbore údajov.

Na základe toho, čo som videl, väčšinou vtedy, keď ľudia hovoria, že chcú získať jedinečný počet v kontingenčnej tabuľke, v skutočnosti znamenajú odlišný počet, čomu sa v tomto návode venujem.

Spočítajte odlišné hodnoty v kontingenčnej tabuľke programu Excel

Predpokladajme, že máte údaje o predaji, ako je uvedené nižšie:

Kliknutím sem stiahnete ukážkový súbor a budete pokračovať

V rámci vyššie uvedeného súboru údajov povedzme, že chcete nájsť odpoveď na nasledujúce otázky:

1. Koľko predajných zástupcov je v každom regióne (čo nie je nič iné ako rozdielny počet obchodných zástupcov v každom regióne)?
2. Koľko predajných zástupcov predalo tlačiareň v rokoch 2021-2022?

Aj keď kontingenčné tabuľky môžu okamžite zhrnúť údaje niekoľkými kliknutiami, na získanie počtu odlišných hodnôt budete musieť urobiť niekoľko ďalších krokov.

Ak používate Excel 2013 alebo jeho verzieV kontingenčnej tabuľke je vstavaná funkcia, ktorá vám rýchlo poskytne zreteľný počet. A ak používate Excel 2010 alebo verzie pred tým, budete musieť upraviť zdrojové údaje pridaním pomocného stĺpca.

V tomto návode sú zahrnuté nasledujúce dve metódy:

• Pridanie pomocného stĺpca do pôvodnej množiny údajov na počítanie jedinečných hodnôt (funguje vo všetkých verziách).
• Pridanie údajov do dátového modelu a použitie možnosti Odlišný počet (k dispozícii v programe Excel 2013 a ďalších verziách).

Existuje tretia metóda, ktorú Roger ukazuje v tomto článku (ktorú nazýva metóda Pivot a Pivot Table).

Začnime!

Pridanie pomocného stĺpca do množiny údajov

Poznámka: Ak používate Excel 2013 a vyššie verzie, túto metódu preskočte a prejdite na ďalšiu (pretože používa vstavanú funkciu kontingenčnej tabuľky - Výrazný gróf).

Je to jednoduchý spôsob, ako počítať odlišné hodnoty v kontingenčnej tabuľke, pretože do zdrojových údajov stačí pridať iba pomocný stĺpec. Keď pridáte pomocný stĺpec, potom môžete tento nový súbor údajov použiť na výpočet odlišného počtu.

Aj keď je to jednoduché riešenie, má táto metóda určité nevýhody (popísané ďalej v tomto návode).

Najprv vám ukážem, ako pridať pomocný stĺpec a získať zreteľný počet.

Predpokladajme, že mám súbor údajov, ako je uvedené nižšie:

Do stĺpca F pridajte nasledujúci vzorec a použite ho pre všetky bunky, ktoré majú údaje v susedných stĺpcoch.

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

Vyššie uvedený vzorec používa funkciu COUNTIFS na spočítanie počtu zobrazení názvu v danej oblasti. Všimnite si tiež, že rozsah kritérií je $ C $ 2: C2 a $ B $ 2: B2. To znamená, že sa neustále rozširuje, keď idete po stĺpci.

V bunke E2 sú napríklad rozsahy kritérií $ C $ 2: C2 a $ B $ 2: B2 a v bunke E3 sa tieto rozsahy rozšíria na $ C $ 2: C3 a $ B $ 2: B3.

To zaisťuje, že funkcia COUNTIFS počíta prvú inštanciu mena ako 1, druhú inštanciu názvu ako 2 atď.

Pretože chceme získať iba odlišné názvy, používa sa funkcia IF, ktorá vráti 1, keď sa názov pre oblasť objaví prvýkrát, a vráti 0, keď sa objaví znova. Tým sa zaistí, že sa budú počítať iba odlišné názvy, a nie opakovania.

Nasleduje postup, ako by váš súbor údajov vyzeral, keď ste pridali pomocný stĺpec.

Teraz, keď sme upravili zdrojové údaje, môžeme to použiť na vytvorenie kontingenčnej tabuľky a pomocou pomocného stĺpca získať zreteľný počet zástupcov predaja v každom regióne.

Nasledujú kroky, ako to urobiť:

1. Vyberte ľubovoľnú bunku v množine údajov.
2. Kliknite na kartu Vložiť.
3. Kliknite na kontingenčnú tabuľku (alebo použite klávesovú skratku - ALT + N + V)
4. V dialógovom okne Vytvoriť kontingenčnú tabuľku skontrolujte, či je tabuľka/rozsah správna (a obsahuje pomocný stĺpec) a či je vybratý „Nový pracovný hárok“.
5. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by vložili nový hárok s kontingenčnou tabuľkou.

Potiahnite pole „Región“ v oblasti Riadky a pole „D Počet“ v oblasti Hodnoty.

Dostanete kontingenčnú tabuľku, ako je uvedené nižšie:

Teraz môžete zmeniť hlavičku stĺpca z „Súčtu počtu D“ na „Predajcu“.

Nevýhody použitia pomocného stĺpca:

Aj keď je táto metóda celkom jednoduchá, musím zdôrazniť niekoľko nevýhod, ktoré so sebou prináša úprava zdrojových údajov v kontingenčnej tabuľke:

• Zdroj údajov so stĺpcom pomocníka nie je taký dynamický ako kontingenčná tabuľka. Aj keď pomocou kontingenčnej tabuľky môžete údaje ľubovoľne rozporcovať a nakrájať na kocky, pri použití pomocného stĺpca prídete o časť tejto schopnosti. Povedzme, že pridáte pomocný stĺpec, aby ste získali počet výrazných predajcov v každom regióne. Teraz, čo keď chcete tiež získať značný počet tlačiarní predávajúcich predajcov. Budete sa musieť vrátiť k zdrojovým údajom a upraviť vzorec stĺpca pomocníka (alebo pridať nový stĺpec pomocníka).
• Pretože do zdroja kontingenčnej tabuľky pridávate viac údajov (ktoré sa tiež pridávajú do vyrovnávacej pamäte), môže to viesť k vyššej veľkosti súboru programu Excel.
• Pretože používame vzorec programu Excel, váš zošit programu Excel môže byť pomalý v prípade, že máte tisíce riadkov údajov.

Pridajte údaje do dátového modelu a sumarizujte ich pomocou odlišného počtu

Kontingenčná tabuľka pridala novú funkciu v programe Excel 2013, ktorá vám umožňuje získať odlišný počet pri sumarizácii množiny údajov.

V prípade, že používate predchádzajúcu verziu, nebudete môcť túto metódu použiť (ako by ste sa mali pokúsiť pridať pomocný stĺpec, ako je uvedené v metóde nad touto).

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete získať počet jedinečných zástupcov predaja v každom regióne.

Nasledujú kroky na získanie odlišnej hodnoty počítania v kontingenčnej tabuľke:

1. Vyberte ľubovoľnú bunku v množine údajov.
2. Kliknite na kartu Vložiť.
3. Kliknite na kontingenčnú tabuľku (alebo použite klávesovú skratku - ALT + N + V)
4. V dialógovom okne Vytvoriť kontingenčnú tabuľku skontrolujte, či je tabuľka/rozsah správna a či je vybratý nový pracovný hárok.
5. Začiarknite políčko „Pridať tieto údaje do dátového modelu“
6. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by vložili nový hárok, ktorý má novú kontingenčnú tabuľku.

Presuňte oblasť do oblasti Riadky a Obchodný zástupca do oblasti Hodnoty. Získate kontingenčnú tabuľku, ako je uvedené nižšie:

Vyššie uvedená kontingenčná tabuľka uvádza celkový počet predajných zástupcov v každom regióne (a nie konkrétny počet).

Ak chcete v kontingenčnej tabuľke získať odlišný počet, postupujte podľa nasledujúcich krokov:

1. Kliknite pravým tlačidlom myši na ľubovoľnú bunku v stĺpci „Počet obchodných zástupcov“.
2. Kliknite na položku Nastavenia poľa hodnoty
3. V dialógovom okne Nastavenia poľa hodnoty vyberte ako typ výpočtu „Rozlišovať počet“ (možno ho budete musieť posúvať nadol v zozname).
4. Kliknite na tlačidlo OK.

Všimnete si, že názov stĺpca sa zmení z „Počet predajných zástupcov“ na „Odlišný počet obchodných zástupcov“. Môžete to zmeniť na čokoľvek chcete.

Niektoré veci, ktoré poznáte pri pridávaní údajov do dátového modelu:

• Ak údaje uložíte do dátového modelu a potom otvoríte v staršej verzii Excelu, zobrazí sa upozornenie - „Niektoré funkcie kontingenčnej tabuľky sa neuložia“. Pri otvorení v staršej verzii, ktorá ho nepodporuje, sa nemusí líšiť počet (a dátový model).
• Keď pridáte údaje do dátového modelu a vytvoríte kontingenčnú tabuľku, nezobrazí možnosti pridania vypočítaných polí a vypočítaných stĺpcov.

Kliknutím sem stiahnete ukážkový súbor

Čo keď chcete počítať jedinečné hodnoty (a nie odlišné hodnoty)?

Ak chcete počítať jedinečné hodnoty, v kontingenčnej tabuľke nemáte žiadnu vstavanú funkciu a budete sa musieť spoliehať iba na pomocné stĺpce.

Nezabudnite - jedinečné hodnoty a odlišné hodnoty nie sú rovnaké. Kliknutím sem zistíte rozdiel.

Príkladom môže byť situácia, keď máte nižšie uvedenú množinu údajov a chcete zistiť, koľko predajcov je pre každý región unikátnych. To znamená, že pôsobia iba v jednom konkrétnom regióne a nie v ostatných.

V takýchto prípadoch musíte vytvoriť jeden alebo viac pomocných stĺpcov.

V tomto prípade trik robí nasledujúci vzorec:

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

Vyššie uvedený vzorec kontroluje, či sa názov obchodného zástupcu vyskytuje iba v jednej oblasti alebo vo viac ako jednej oblasti. Dosiahne sa to tak, že sa spočíta počet výskytov mena v regióne a vydelí sa celkovým počtom výskytov mena. Ak je hodnota menšia ako 1, znamená to, že názov sa vyskytuje v dvoch alebo viac ako dvoch oblastiach.

V prípade, že sa názov vyskytuje vo viac ako jednej oblasti, vráti hodnotu 0, v opačnom prípade vráti hodnotu jedna.

Vzorec tiež kontroluje, či sa názov opakuje v tej istej oblasti alebo nie. Ak sa názov opakuje, iba prvá inštancia názvu vráti hodnotu 1 a všetky ostatné inštancie vrátia hodnotu 0.

Môže sa to zdať trochu zložité, ale opäť to závisí od toho, čo sa snažíte dosiahnuť.

Ak teda chcete v kontingenčnej tabuľke počítať jedinečné hodnoty, použite pomocné stĺpce a ak chcete počítať odlišné hodnoty, môžete použiť vstavanú funkciu (v programe Excel 2013 a novšej verzii) alebo môžete použiť pomocný stĺpček.

Kliknutím sem stiahnete ukážkový súbor

Tiež by sa vám mohli páčiť nasledujúce návody na kontingenčné tabuľky:

• Ako filtrovať údaje v kontingenčnej tabuľke v programe Excel
• Ako zoskupiť dátumy v kontingenčných tabuľkách v programe Excel
• Ako zoskupiť čísla v kontingenčnej tabuľke v programe Excel
• Ako použiť podmienené formátovanie v kontingenčnej tabuľke v programe Excel
• Krájače v kontingenčnej tabuľke programu Excel
• Ako obnoviť kontingenčnú tabuľku v programe Excel
• Odstráňte kontingenčnú tabuľku v programe Excel