Generieke formule
=COUNTIF(list,A1)=0
Samenvatting
Als u lijsten wilt vergelijken en waarden wilt markeren die in de ene maar niet in de andere voorkomen, kunt u voorwaardelijke opmaak toepassen met een formule op basis van de AANTAL.ALS-functie. Om bijvoorbeeld waarden A1: A10 te markeren die niet C1: C10 bestaan, selecteert u A1: A10 en maakt u een voorwaardelijke opmaakregel op basis van deze formule:
=COUNTIF($C$1:$C$10,A1)=0
Opmerking: bij voorwaardelijke opmaak is het belangrijk om de formule in te voeren ten opzichte van de "actieve cel" in de selectie, waarvan in dit geval wordt aangenomen dat deze A1 is.
Uitleg
Deze formule wordt geëvalueerd voor elk van de 10 cellen in A1: D10. A1 verandert in het adres van de cel die wordt geëvalueerd, terwijl C1: C10 wordt ingevoerd als een absoluut adres, dus het verandert helemaal niet.
De sleutel tot deze formule is de = 0 aan het einde, die de logica van de formule "omdraait". Voor elke waarde in A1: A10 retourneert AANTAL.ALS het aantal keren dat de waarde voorkomt in C1: C10. Zolang de waarde minstens één keer voorkomt in C1: C10, retourneert AANTAL.ALS een getal dat niet gelijk is aan nul en retourneert de formule ONWAAR.
Maar als er geen waarde wordt gevonden in C1: C10, retourneert de AANTAL.ALS nul en, aangezien 0 = 0, retourneert de formule WAAR en wordt de voorwaardelijke opmaak toegepast.
Benoemde bereiken voor eenvoudige syntaxis
Als u de lijst die u zoekt (in dit geval C1: C10) een naam geeft met een benoemd bereik, is de formule eenvoudiger te lezen en te begrijpen:
=COUNTIF(list,A1)=0
Dit werkt omdat benoemde bereiken automatisch absoluut zijn.
Hoofdlettergevoelige versie
Als u een hoofdlettergevoelige telling nodig heeft, kunt u een formule als deze gebruiken:
=SUMPRODUCT((--EXACT(A1,list)))=0
De functie EXACT voert een hoofdlettergevoelige evaluatie uit en SUMPRODUCT geeft het resultaat weer. Net als bij de AANTAL.ALS, wordt deze formule geretourneerd wanneer het resultaat nul is. Omdat de test hoofdlettergevoelig is, wordt "apple" als ontbrekend weergegeven, zelfs als "Apple" of "APPLE" in de tweede lijst verschijnt. Zie deze pagina voor een meer gedetailleerde uitleg.
