Porovnání seznamů pomocí podmíněného formátování

Tento návod je trochu složitější, ale užitečný. Jedná se o porovnání dvou tabulek pomocí podmíněného formátování. Porovnání dvou tabulek už je řešené zde, nicméně existuje ještě jedna cesta jak porovnávat, která je někdy vhodnější.

V tomto případě chci v jednom seznamu barevně vyznačit položky, které v jiném seznamu chybí – tedy označit to, co je jakoby navíc. Představte si, že máte ve firmě několik aut – jejich výpis je v prvním sloupci. Některá jsou už pojištěná a jiná nejsou. Seznam pojištěných aut je ve druhém sloupci. Vy teď potřebujete v prvním sloupci označit ta data, která ve druhém sloupci chybí. Tedy označit nepojištěná auta, která je třeba pojistit.

• Označte auta, kterých se rozlišení týká – tedy hodnoty v levém sloupci.
• Jděte na Domů / Podmíněné formátování / Nové pravidlo / Určit buňky k formátování pomocí vzorce.
• Zadejte tento vzorec: =JE.CHYBHODN(SVYHLEDAT(A2;$D:$D;1;0))
• Nastavte formát buňky (u mě je to zelená barva)

Proč zrovna takovýto vzorec?

• Excel nejprve použije funkci SVYHLEDAT/VLOOKUP (podobně by bylo možné použít např. POZVYHLEDAT nebo jinou prohledávací funkci). Tato funkce se podívá po hodnotě z A2 (a s postupně i po dalších hodnotách) do druhého sloupce. Výstupem bude buď nějaká hodnota (číslo nalezeného auta) nebo chyba.
• To, jestli je to chyba nebo hodnota, určí funkce JE.CHYBHODN. Ta vezme výsledek funkce SVYHLEDAT a podle něj vrátí PRAVDA (když se jedná o chybu) nebo NEPRAVDA (když se nejedná o chybu).
• A do podmíněného formátování pak spadne PRAVDA nebo NEPRAVDA. Při PRAVDĚ se aplikuje podmíněné formátování.
• Jinými slovy – podle toho, jestli SVYHLEDAT najde ve druhém seznamu odpovídající hodnotu, se probarví buňka.

 

Poznámky

• Šipky jsou pouze pro znázornění – jsou “dokreslené” ručně
• Pokud byste chtěli v našem příkladu označit místo nepojištěných aut naopak auta pojištěná, zabalte vše ještě do funkce NE, která obrací logickou hodnotu výrazu:
  =NE(JE.CHYBHODN(SVYHLEDAT(A2;$D:$D;1;0)))
• V článku je uvedená pouze jedna z několika možností jak tento úkol řešit. Vzorec by šel zapsat např. i takto:
  =COUNTIFS(A2;$D$D)>0
  nebo i jen:
  =COUNTIFS(A2;$D$D)
• Funkce COUNTIFS, SVYHLEDAT atd. jsou velmi náročné na výpočetní výkon. Takže i podmíněné formátování na nich založené může zpomalovat práci s tabulkou, pokud se takto pracuje s hodně buňkami. Pokud se ale pohybujete v řádech do stovek nebo tisíců, nejspíš žádné reálné zpomalení nehrozí.