Ako počítať jedinečné hodnoty v programe Excel

Ako počítať jedinečné hodnoty v programe Excel

Množiny údajov v programe Excel často obsahujú rovnakú hodnotu viackrát v stĺpci. Niekedy môže byť užitočné vedieť, koľko jedinečných hodnôt je v stĺpci. Ak napríklad prevádzkujete obchod a máte tabuľku všetkých svojich transakcií, namiesto počítania každej jednej transakcie môžete chcieť zistiť, koľko máte unikátnych zákazníkov.





Je možné to dosiahnuť tak, že v programe Excel spočítate jedinečné hodnoty pomocou metód, ktoré si s vami povieme nižšie.



Odstráňte duplicitné údaje zo stĺpca

Rýchly a špinavý spôsob, ako počítať jedinečné hodnoty v programe Excel, je odstrániť duplikáty a zistiť, koľko záznamov zostáva. Je to dobrá voľba, ak potrebujete rýchlu odpoveď a nepotrebujete sledovať výsledok.





Skopírujte údaje do nového hárka (aby ste omylom neodstránili všetky potrebné údaje). Vyberte hodnoty alebo stĺpce, z ktorých chcete odstrániť duplicitné hodnoty. V Dátové nástroje časť Údaje vyberte kartu Odstráňte duplikáty . Tým sa odstránia všetky duplicitné údaje a zostanú iba jedinečné hodnoty.

Rovnaký postup funguje, ak sú informácie rozdelené do dvoch stĺpcov. Rozdiel je v tom, že musíte vybrať oba stĺpce. V našom prípade máme stĺpec pre krstné meno a druhý pre priezvisko.



Ak chcete sledovať počet jedinečných hodnôt, je lepšie napísať vzorec. Nižšie vám ukážeme, ako to urobiť.

Súvisiace: Ako filtrovať v programe Excel a zobrazovať požadované údaje



Počítajte jedinečné hodnoty pomocou vzorca v Exceli

Aby sme mohli počítať iba jedinečné hodnoty, musíme skombinovať niekoľko funkcií Excelu. Najprv musíme skontrolovať, či je každá hodnota duplikát, a potom musíme spočítať zostávajúce položky. Musíme tiež použiť funkciu poľa.

Ak len hľadáte odpoveď, použite tento vzorec a každú inštanciu A2: A13 nahraďte bunkami, ktoré chcete použiť:



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

Ako sme sa tam dostali, je trochu komplikované. Ak teda chcete pochopiť, prečo tento vzorec funguje, nižšie ho rozdelíme po jednom.

Vysvetlenie funkcie poľa

Začnime najskôr vysvetlením, čo je pole. Pole je jedna premenná, ktorá obsahuje viacero hodnôt. Je to ako odkazovať na veľa buniek programu Excel naraz, a nie na každú bunku jednotlivo.

Z nášho pohľadu je to zvláštne rozlíšenie. Ak vzorcu povieme, aby sa pozeralo na bunky A2: A13 normálne alebo ako pole, údaje pre nás vyzerajú rovnako. Rozdiel je v tom, ako Excel zaobchádza s údajmi v zákulisí. Je to taký jemný rozdiel, že najnovšie verzie Excelu medzi nimi už ani nerozlišujú, aj keď staršie verzie áno.

Pre naše účely je dôležitejšie vedieť, ako môžeme používať polia. Ak máte najnovšiu verziu programu Excel, automaticky ukladá údaje ako pole, keď je to efektívnejšie. Ak máte staršiu verziu, po napísaní vzorca stlačte Ctrl + Shift + Enter . Akonáhle to urobíte, vzorec bude obklopený zloženými zátvorkami, aby sa ukázalo, že je v režime poľa.

Predstavujeme funkciu FREQUENCY

Funkcia FREQUENCY nám hovorí, koľkokrát sa číslo objaví v zozname. Je to skvelé, ak pracujete s číslami, ale náš zoznam je textový. Aby sme mohli používať túto funkciu, musíme najskôr nájsť spôsob, ako previesť náš text na čísla.

Ak sa pokúšate spočítať jedinečné hodnoty v zozname čísel, môžete nasledujúci krok preskočiť.

Použitie funkcie MATCH

Funkcia MATCH vracia pozíciu prvého výskytu hodnoty. Môžeme to použiť na konverziu nášho zoznamu mien na číselné hodnoty. Potrebuje vedieť tri informácie:

  • Akú hodnotu hľadáte?
  • Aký súbor údajov kontrolujete?
  • Hľadáte hodnoty vyššie, nižšie alebo rovnaké ako cieľová hodnota?

V našom príklade chceme vyhľadať každé meno našich zákazníkov v našej tabuľke Exel, aby sme zistili, či sa ich presné meno znova objaví inde.

ako zdieľať svoju polohu na iphone

V uvedenom príklade hľadáme v našom zozname (A2: A13) Tiah Gallagher (A2) a chceme presnú zhodu. 0 v poslednom poli určuje, že musí ísť o presnú zhodu. Náš výsledok nám hovorí, kde v zozname sa meno objavilo ako prvé. V tomto prípade to bolo krstné meno, takže výsledok je 1.

Problém je v tom, že nás zaujímajú všetci naši zákazníci, nielen Tiah. Ak sa však pokúsime hľadať A2: A13 namiesto iba A2, zobrazí sa chyba. Tu sú užitočné funkcie poľa. Prvý parameter môže obsahovať iba jednu premennú, inak vráti chybu. S poliami sa však zaobchádza ako s jedinou premennou.

Teraz naša funkcia hovorí Excelu, aby vyhľadal zhody pre celé naše pole. Ale počkajte, náš výsledok sa nezmenil! Stále sa tam píše 1. Čo sa tu deje?

Naša funkcia vracia pole. Prechádza každou položkou v našom poli a kontroluje zhody. Výsledky všetkých mien sa uložia do poľa, ktoré sa vráti ako výsledok. Pretože bunka zobrazuje iba jednu premennú súčasne, zobrazuje prvú hodnotu v poli.

Môžete si to overiť sami. Ak zmeníte prvý rozsah na A3: A13, výsledok sa zmení na 2. Dôvodom je, že meno Eiliyah je v zozname na druhom mieste a táto hodnota sa teraz uloží do poľa ako prvá. Ak zmeníte prvý rozsah na A7: A13, dostanete opäť 1, pretože meno Tiah sa prvýkrát objaví na prvej pozícii súboru údajov, ktorý kontrolujeme.

Súvisiace: Vzorce Excelu, ktoré vám pomôžu vyriešiť problémy v reálnom živote

Použitie funkcie FREQUENCY

Teraz, keď sme zmenili názvy na číselné hodnoty, môžeme používať funkciu FREQUENCY. Podobne ako v prípade MATCH vyžaduje cieľ, ktorý sa má hľadať, a súbor údajov na kontrolu. Rovnako ako v prípade MATCH, nechceme hľadať iba jednu hodnotu, chceme, aby funkcia kontrolovala každú položku v našom zozname.

Cieľ, ktorý chceme skontrolovať pomocou funkcie FREQUENCY, je každá položka v poli, ktorú vrátila naša funkcia MATCH. A chceme skontrolovať súbor údajov vrátený funkciou MATCH. Preto posielame funkciu MATCH, ktorú sme vytvorili vyššie, pre oba parametre.

Ak hľadáte jedinečné čísla a preskočili ste predchádzajúci krok, poslali by ste rozsah čísel ako oba parametre. Ak chcete vyhľadať všetky čísla v zozname, budete musieť použiť aj funkciu poľa, takže nezabudnite stlačiť Ctrl + Shift + Enter potom, čo ste zadali vzorec, ak používate staršiu verziu programu Excel.

Teraz je náš výsledok 2. Naša funkcia opäť vracia pole. Vracia pole, koľkokrát sa zobrazila každá jedinečná hodnota. Bunka zobrazuje prvú hodnotu v poli. V tomto prípade sa Tiahino meno objaví dvakrát, takže vrátená frekvencia je 2.

Použitie funkcie IF

Teraz má naše pole rovnaký počet hodnôt, ako máme jedinečné hodnoty. Ale nie sme ešte úplne hotoví. Potrebujeme spôsob, ako to zrátať. Ak skonvertujeme všetky hodnoty v poli na 1 a spočítame ich, budeme konečne vedieť, koľko jedinečných hodnôt máme.

Môžeme vytvoriť funkciu IF, ktorá zmení všetky hodnoty nad nulou na 1. Potom sa všetky hodnoty budú rovnať 1.

Aby sme to urobili, chceme, aby naša funkcia IF kontrolovala, či sú hodnoty v našom poli FREQUENCY väčšie ako nula. Ak je to pravda, mala by vrátiť hodnotu 1. Všimnete si, že teraz sa prvá hodnota v poli vráti ako jedna.

Použitie funkcie SUM

Sme v poslednom úseku! Posledným krokom je SUMA poľa.

Zabalte predchádzajúcu funkciu do funkcie SUM. Hotovo! Náš konečný vzorec je teda:

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

Počítanie jedinečných záznamov v programe Excel

Ide o pokročilú funkciu, ktorá vyžaduje veľa znalostí o programe Excel. Skúsiť to môže byť zastrašujúce. Akonáhle je však nastavený, môže byť veľmi nápomocný, takže by stálo za to prepracovať sa k nášmu vysvetleniu a uistiť sa, že mu rozumiete.

Ak nepotrebujete tak často počítať jedinečné položky, rýchly a špinavý tip na odstránenie duplicitných hodnôt bude fungovať veľmi rýchlo!

zdieľam zdieľam Tweet E -mail Ako kopírovať vzorce v programe Microsoft Excel

Naučiť sa všetky najlepšie metódy kopírovania a vkladania vzorcov do tabuľky programu Excel je úžasný spôsob, ako začať šetriť čas.

Čítajte ďalej Súvisiace témy
  • Produktivita
  • Tabuľka
  • Microsoft Excel
  • Analýza dát
O autorovi Jennifer Seaton(21 publikovaných článkov)

J. Seaton je vedecký spisovateľ, ktorý sa špecializuje na delenie zložitých tém. Má doktorát z University of Saskatchewan; jej výskum sa zameral na využitie učenia založeného na hrách na zvýšenie zapojenia študentov online. Keď nepracuje, nájdete ju pri jej čítaní, hraní videohier alebo záhradníctve.

Viac od Jennifer Seaton

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber