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.

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

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář
TOPlist