S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenč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.

dve_tabulky_zacatek

  • 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)

zadani_vzorce_podminene_formatovani

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.

 

vysledek

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í.

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.