Jak počítat jedinečné hodnoty v aplikaci Excel

Jak počítat jedinečné hodnoty v aplikaci Excel

Datové sady v Excelu často obsahují stejnou hodnotu vícekrát ve sloupci. Někdy může být užitečné vědět, kolik jedinečných hodnot je ve sloupci. Pokud například provozujete obchod a máte tabulku se všemi vašimi transakcemi, můžete místo počítání každé transakce chtít zjistit, kolik máte unikátních zákazníků.





Je možné to provést tak, že v aplikaci Excel spočítáte jedinečné hodnoty pomocí metod, které vám probereme níže.



Odebrat duplicitní data ze sloupce

Rychlý a špinavý způsob, jak počítat jedinečné hodnoty v aplikaci Excel, je odebrat duplikáty a zjistit, kolik položek zbývá. Je to dobrá volba, pokud potřebujete odpověď rychle a nepotřebujete sledovat výsledek.





Zkopírujte data do nového listu (abyste omylem neodstranili všechna potřebná data). Vyberte hodnoty nebo sloupec, ze kterého chcete odebrat duplicitní hodnoty. V Datové nástroje sekce Data vyberte záložku Odebrat duplikáty . Tím se odstraní všechna duplicitní data a ponechají se pouze jedinečné hodnoty.

Stejný postup funguje, pokud jsou informace rozděleny mezi dva sloupce. Rozdíl je v tom, že musíte vybrat oba sloupce. V našem příkladu máme sloupec pro křestní jméno a druhý pro příjmení.



Pokud chcete sledovat počet jedinečných hodnot, je lepší místo toho napsat vzorec. Níže vám ukážeme, jak to udělat.

Související: Jak filtrovat v aplikaci Excel a zobrazit požadovaná data



Počítejte jedinečné hodnoty se vzorcem aplikace Excel

Abychom mohli počítat pouze jedinečné hodnoty, musíme zkombinovat několik funkcí Excelu. Nejprve musíme zkontrolovat, zda je každá hodnota duplikát, a poté musíme spočítat zbývající položky. Musíme také použít funkci pole.

Pokud hledáte odpověď, použijte tento vzorec a každou instanci A2: A13 nahraďte buňkami, které chcete použít:



{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

Jak jsme se tam dostali, je trochu komplikované. Pokud tedy chcete pochopit, proč tento vzorec funguje, rozebereme jej níže po jednom.

Vysvětlení funkce pole

Začněme nejprve vysvětlením, co je pole. Pole je jedna proměnná, která obsahuje více hodnot. Je to jako odkazovat na hromadu buněk aplikace Excel místo odkazování na každou buňku jednotlivě.

To je z našeho pohledu zvláštní rozdíl. Řekneme -li vzorci, aby se na buňky A2: A13 díval normálně nebo jako pole, data pro nás vypadají stejně. Rozdíl je v tom, jak Excel zachází s daty v zákulisí. Je to tak jemný rozdíl, že nejnovější verze Excelu už mezi nimi ani nerozlišují, ačkoli starší verze ano.

Pro naše účely je důležitější vědět, jak můžeme pole použít. Pokud máte nejnovější verzi Excelu, automaticky ukládá data jako pole, když je to efektivnější. Pokud máte starší verzi, po dokončení psaní vzorce stiskněte Ctrl + Shift + Enter . Jakmile to uděláte, bude vzorec obklopen složenými závorkami, což ukazuje, že je v režimu pole.

Představujeme funkci FREQUENCY

Funkce FREQUENCY nám říká, kolikrát se číslo objeví v seznamu. To je skvělé, pokud pracujete s čísly, ale náš seznam je textový. Abychom mohli tuto funkci používat, musíme nejprve najít způsob, jak převést náš text na čísla.

Pokud se pokoušíte spočítat jedinečné hodnoty v seznamu čísel, můžete další krok přeskočit.

Použití funkce MATCH

Funkce MATCH vrací pozici prvního výskytu hodnoty. Můžeme to použít k převodu našeho seznamu jmen na číselné hodnoty. Potřebuje znát tři informace:

  • Jakou hodnotu hledáte?
  • Jaký soubor dat kontrolujete?
  • Hledáte hodnoty vyšší, nižší nebo rovné cílové hodnotě?

V našem příkladu chceme vyhledat každé jméno našich zákazníků v naší tabulce Exel, abychom zjistili, zda se jejich přesné jméno znovu objeví jinde.

vytiskněte seznam souborů ve složce a podsložkách

Ve výše uvedeném příkladu prohledáváme náš seznam (A2: A13) pro Tiah Gallagher (A2) a chceme přesnou shodu. 0 v posledním poli určuje, že se musí jednat o přesnou shodu. Náš výsledek nám říká, kde se v seznamu název objevil jako první. V tomto případě to bylo křestní jméno, takže výsledek je 1.

Problém je v tom, že nás zajímají všichni naši zákazníci, nejen Tiah. Pokud se ale pokusíme hledat A2: A13 místo pouze A2, zobrazí se chyba. Zde jsou užitečné funkce pole. První parametr může mít pouze jednu proměnnou, jinak vrátí chybu. S poli se však zachází jako s jedinou proměnnou.

Naše funkce nyní říká Excelu, aby zkontroloval shody pro celé naše pole. Ale počkejte, náš výsledek se nezměnil! Pořád se tam píše 1. Co se to tu děje?

Naše funkce vrací pole. Prochází každou položkou v našem poli a kontroluje shody. Výsledky všech jmen jsou uloženy v poli, které je vráceno jako výsledek. Protože buňka zobrazuje pouze jednu proměnnou najednou, zobrazuje první hodnotu v poli.

Můžete si to ověřit sami. Pokud změníte první rozsah na A3: A13, výsledek se změní na 2. Důvodem je, že jméno Eiliyah je v seznamu druhé a tato hodnota je nyní uložena jako první do pole. Pokud změníte první rozsah na A7: A13, dostanete opět 1, protože jméno Tiah se poprvé objeví na první pozici datové sady, kterou kontrolujeme.

Příbuzný: Vzorce aplikace Excel, které vám pomohou vyřešit skutečné problémy

Použití funkce FREQUENCY

Nyní, když jsme změnili názvy na číselné hodnoty, můžeme použít funkci FREQUENCY. Podobně jako u MATCH vyžaduje cíl, který má hledat, a sadu dat ke kontrole. Také podobně jako MATCH nechceme hledat pouze jednu hodnotu, chceme, aby funkce zkontrolovala každou položku v našem seznamu.

Cíl, který chceme zkontrolovat funkcí FREQUENCY, je každá položka v poli, kterou naše funkce MATCH vrátila. A chceme zkontrolovat datovou sadu vrácenou funkcí MATCH. Proto posíláme funkci MATCH, kterou jsme vytvořili výše, pro oba parametry.

Pokud hledáte jedinečná čísla a přeskočili jste předchozí krok, poslali byste rozsah čísel jako oba parametry. Chcete -li vyhledat všechna čísla ve vašem seznamu, budete muset použít také funkci pole, takže nezapomeňte stisknout Ctrl + Shift + Enter poté, co jste zadali vzorec, pokud používáte starší verzi Excelu.

Nyní je náš výsledek 2. Naše funkce opět vrací pole. Vrací pole, kolikrát se objevila každá jedinečná hodnota. Buňka ukazuje první hodnotu v poli. V tomto případě se jméno Tiah objeví dvakrát, takže vrácená frekvence je 2.

Použití funkce IF

Nyní má naše pole stejný počet hodnot, jako máme jedinečné hodnoty. Ale nejsme úplně hotovi. Potřebujeme způsob, jak to sečíst. Pokud převedeme všechny hodnoty v poli na 1 a sečteme je, pak konečně budeme vědět, kolik unikátních hodnot máme.

Můžeme vytvořit funkci IF, která změní všechny hodnoty nad nulou na 1. Potom se všechny hodnoty budou rovnat 1.

Chcete -li to provést, chceme, aby naše funkce IF zkontrolovala, zda jsou hodnoty v našem poli FREQUENCY větší než nula. Pokud je to pravda, měla by vrátit hodnotu 1. Všimnete si, že nyní se první hodnota v poli vrací jako jedna.

Použití funkce SUMA

Jsme v posledním úseku! Posledním krokem je SUMAT pole.

Zabalte předchozí funkci do funkce SUMA. Hotovo! Náš konečný vzorec je tedy:

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

Počítání unikátních záznamů v Excelu

Toto je pokročilá funkce, která vyžaduje spoustu znalostí o Excelu. Zkoušet to může být zastrašující. Jakmile je ale nastaven, může být velmi užitečný, takže by možná stálo za to si naše vysvětlení propracovat, abyste se ujistili, že mu rozumíte.

Pokud nepotřebujete tak často počítat jedinečné položky, rychlý a špinavý tip na odstranění duplicitních hodnot bude fungovat v malém množství!

Podíl Podíl tweet E-mailem Jak kopírovat vzorce v aplikaci Microsoft Excel

Naučit se všechny nejlepší metody kopírování a vkládání vzorců do tabulky aplikace Excel je skvělý způsob, jak začít šetřit čas.

Číst dále Související témata
  • Produktivita
  • Tabulkový procesor
  • Microsoft Excel
  • Analýza dat
O autorovi Jennifer Seaton(21 článků zveřejněno)

J. Seaton je vědecký spisovatel, který se specializuje na bourání složitých témat. Má doktorát z University of Saskatchewan; její výzkum se zaměřil na využití učení založeného na hrách ke zvýšení zapojení studentů online. Když nepracuje, najdete ji u čtení, hraní videoher nebo zahradničení.

Více od Jennifer Seaton

Přihlaste se k odběru našeho zpravodaje

Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!

Kliknutím sem se přihlásíte k odběru