DOTAZ: Mám výpis cca deseti telefonních čísel a ke každému seznam volaných čísel v období jednoho měsíce. Potřebuji nalézt číslo nebo čísla, která se vyskytují ve všech seznamech.
V tomto příspěvku si ukážeme řešení pomocí nástojů listu Excelu, v dalším pokračování řešení pomocí návrháře PowerQuery.
POSTUP 1
Nejrychlejší způsob zobrazení společných hodnot – duplicit – ve více seznamech nabízí nástroj Podmíněné formátování.
Označíme požadované oblasti dat a na kartě DOMŮ klepneme na příkaz PODMÍNĚNÉ FORMÁTOVÁNÍ.
V nabídce zvolíme položku PRAVIDLA ZVÝRAZNĚNÍ BUNĚK a v dalším seznamu aktivujeme položku DUPLICITNÍ HODNOTY.
Excel zvýrazní veškeré nalezené duplicity – z jeho pohledu je lhostejné, zda se jedná o duplicity v rámci jednho seznamu, nebo duplicity kombinace několika seznamů.
Lze říci, že nepodbarví pouze ty hodnoty, které se vyskytují pouze jednou.
POSTUP 2
Pro zjištění počtu výskytů hodnoty je v Excelu připravena funkce COUNTIF, která spočítá, kolikrát se daná hodnota vyskatuje v prohledáváané oblasti.
Nejdříve zkopírujeme oblasti do jednoho dlouhého seznamu:
Funkci COUNTIF zapíšeme v „roletovém“ tvaru, kdy oblast dat v prvním argumentu funguje tak, že při kopírování vzorce směrem dolů se zvětšuje prohledávaná oblast o jednu buňku.
Excel tedy nehledá hodnotu zadanou ve druhém argumentu v celé oblasti, ale jen v oblasti od začátku seznamu k aktuálnímu řádku.
Čísla tedy znamenají počet nalezení hodnoty v předchozí oblasti – můžeme tedy rychle zjistit 1, výskyt hodnoty, 2. výskyt hodnoty atd.
Pro vyznačení hodnot s nyjvyšším počtem opakování můžeme opět použít podmíněné formátování, kde necháme Excel zobrazi pouze ty řádky, ve kterých se nachází maximální hodnota vypočtená funkcí COUNTIF.
POSTUP 3
Pokud používáme funkci FILTER, můžeme vytvořit velmi rychlé a elegantní řešení pro nalezení hodnot, které se vyskytují současně ve všech seznamech.
Nejdříve pro zjednodušení zápisu vzorce vytvoříme pojmenované oblasti dat – můžeme využít příkaz VYTVOŘIT Z VÝBĚRU z karty VZORCE.
Nesmíme zapomenout označit nejdříve oblast dat a zvolit pro výběr názvu variantu HORNÍ ŘÁDEK.
Pro nalezení hodnot, které se vyskytují současně ve všech třech seznamech, zapíšeme do prázdné buňky vzorec
=FILTER(Seznam_1; COUNTIF(Seznam_2;Seznam_1)*COUNTIF(Seznam_3;Seznam_1)=1)
DŮLEŽITÉ je zapsat pořadí oblastí ve funkcích COUNTIF tak, jak je zobrazeno.
Excel zobrazil trojici hodnot, které se vyskytují ve všech prohledávaných oblastech Seznam 1, Seznam 2 a Seznam 3.
Pro rychlé seznámení se zobrazenými postupy si můžete stáhnout excelovský sešit
