Obecný vzorec
=COUNTIF(list,A1)=0
souhrn
Chcete-li porovnat seznamy a zvýraznit hodnoty, které existují v jednom, ale ne v druhém, můžete použít podmíněné formátování pomocí vzorce založeného na funkci COUNTIF. Například pro zvýraznění hodnot A1: A10, které neexistují C1: C10, vyberte A1: A10 a vytvořte pravidlo podmíněného formátování založené na tomto vzorci:
=COUNTIF($C$1:$C$10,A1)=0
Poznámka: U podmíněného formátování je důležité do výběru zadat vzorec relativní k „aktivní buňce“, která je v tomto případě považována za A1.
Vysvětlení
Tento vzorec je vyhodnocen pro každou z 10 buněk v A1: D10. A1 se změní na adresu vyhodnocované buňky, zatímco C1: C10 se zadá jako absolutní adresa, takže se vůbec nezmění.
Klíčem k tomuto vzorci je = 0 na konci, což „převrátí“ logiku vzorce. Pro každou hodnotu v A1: A10 vrátí funkce COUNTIF počet zobrazení hodnoty v C1: C10. Dokud se hodnota v C1: C10 objeví alespoň jednou, COUNTIF vrátí nenulové číslo a vzorec vrátí FALSE.
Pokud ale v C1: C10 hodnotu nenajdete, vrátí funkce COUNTIF nulu a protože 0 = 0, vzorec vrátí hodnotu TRUE a použije se podmíněné formátování.
Pojmenované rozsahy pro jednoduchou syntaxi
Pokud pojmenujete prohledávaný seznam (v tomto případě C1: C10) pojmenovaným rozsahem, je vzorec jednodušší pro čtení a porozumění:
=COUNTIF(list,A1)=0
To funguje, protože pojmenované rozsahy jsou automaticky absolutní.
Verze rozlišující velká a malá písmena
Pokud potřebujete počítat velká a malá písmena, můžete použít vzorec takto:
=SUMPRODUCT((--EXACT(A1,list)))=0
Funkce EXACT provádí vyhodnocení malých a velkých písmen a SUMPRODUCT hodnotí výsledek. Stejně jako u nástroje COUNTIF se tento vzorec vrátí, když je výsledek nulový. Protože se v testu rozlišují velká a malá písmena, bude se slovo „apple“ zobrazovat jako chybějící, i když se ve druhém seznamu objeví „Apple“ nebo „APPLE“. Na této stránce naleznete podrobnější vysvětlení.
