Všeobecný vzorec
=COUNTIF(list,A1)=0
Zhrnutie
Ak chcete porovnať zoznamy a zvýrazniť hodnoty, ktoré existujú v jednom, ale nie v druhom, môžete použiť podmienené formátovanie pomocou vzorca založeného na funkcii COUNTIF. Napríklad na zvýraznenie hodnôt A1: A10, ktoré neexistujú C1: C10, vyberte A1: A10 a vytvorte pravidlo podmieneného formátovania založené na tomto vzorci:
=COUNTIF($C$1:$C$10,A1)=0
Poznámka: Pri podmienenom formátovaní je dôležité pri výbere zadať vzorec vzhľadom na „aktívnu bunku“, ktorý sa v tomto prípade považuje za A1.
Vysvetlenie
Tento vzorec je vyhodnotený pre každú z 10 buniek v A1: D10. A1 sa zmení na adresu vyhodnocovanej bunky, zatiaľ čo C1: C10 sa zadá ako absolútna adresa, takže sa vôbec nezmení.
Kľúčom k tomuto vzorcu je = 0 na konci, čo „prevráti“ logiku vzorca. Pre každú hodnotu v A1: A10 vráti funkcia COUNTIF počet zobrazení hodnoty v C1: C10. Pokiaľ sa hodnota v C1: C10 objaví aspoň raz, COUNTIF vráti nenulové číslo a vzorec vráti FALSE.
Ak ale nenájdete hodnotu v C1: C10, funkcia COUNTIF vráti nulu a keďže 0 = 0, vzorec vráti hodnotu TRUE a použije sa podmienené formátovanie.
Pomenované rozsahy pre jednoduchú syntax
Ak pomenujete prehľadávaný zoznam (v tomto prípade C1: C10) pomenovaným rozsahom, vzorec je ľahšie čitateľný a zrozumiteľný:
=COUNTIF(list,A1)=0
Funguje to, pretože pomenované rozsahy sú automaticky absolútne.
Verzia rozlišujúca veľké a malé písmená
Ak potrebujete počítať veľké a malé písmená, môžete použiť nasledujúci vzorec:
=SUMPRODUCT((--EXACT(A1,list)))=0
Funkcia EXACT vykoná vyhodnotenie malých a veľkých písmen a SUMPRODUCT porovná výsledok. Rovnako ako v prípade nástroja COUNTIF, aj tento vzorec sa vráti, keď bude výsledok nulový. Pretože sa v teste rozlišujú veľké a malé písmená, slovo „apple“ bude chýbať, aj keď sa v druhom zozname zobrazí „Apple“ alebo „APPLE“. Na tejto stránke nájdete podrobnejšie vysvetlenie.
