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

Vyhledávání

Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
Nezařazené

Nyní otevřený kurz

Jednodenní Excel v praxi

  • 14.3.2017
  • V Praze
Další info / přihláška

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ář

FB CZ

TOPlist