Obecný vzorec
=IF(COUNTIF(list,value),"OK","Missing")
souhrn
Chcete-li identifikovat hodnoty v jednom seznamu, které chybí v jiném seznamu, můžete použít jednoduchý vzorec založený na funkci COUNTIF s funkcí IF. V zobrazeném příkladu je vzorec v G6:
=IF(COUNTIF(list,F6),"OK","Missing")
kde „list“ je pojmenovaný rozsah B6: B11.
Vysvětlení
Funkce COUNTIF počítá buňky, které splňují kritéria, a vrací počet nalezených výskytů. Pokud žádná buňka nesplňuje kritéria, vrátí funkce COUNTIF nulu. Chování můžete použít přímo uvnitř příkazu IF k označení hodnot, které mají nulový počet (tj. Chybějící hodnoty). V zobrazeném příkladu je vzorec v G6:
=IF(COUNTIF(list,F6),"OK","Missing")
kde "seznam" je pojmenovaný rozsah, který odpovídá rozsahu B6: B11.
Funkce IF vyžaduje logický test pro návrat PRAVDA nebo NEPRAVDA. V tomto případě provede funkce COUNTIF logický test. Pokud je hodnota nalezena v seznamu , funkce COUNTIF vrátí číslo přímo do funkce IF. Tímto výsledkem může být jakékoli číslo … 1, 2, 3 atd.
Funkce IF vyhodnotí libovolné číslo jako PRAVDA, což způsobí, že IF vrátí „OK“. Pokud hodnotu v seznamu nenajdete , vrátí funkce COUNTIF nulu (0), která se vyhodnotí jako FALSE, a funkce IF vrátí hodnotu „Missing“.
Alternativa k MATCH
Můžete také otestovat chybějící hodnoty pomocí funkce MATCH. MATCH najde pozici položky v seznamu a vrátí chybu # N / A, pokud není nalezena hodnota. Toto chování můžete použít k vytvoření vzorce, který vrací "Chybějící" nebo "OK" testováním výsledku MATCH s funkcí ISNA. ISNA vrátí TRUE pouze v případě, že obdrží chybu # N / A.
Chcete-li použít MATCH, jak je ukázáno ve výše uvedeném příkladu, vzorec je:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
Pamatujte, že MATCH musí být nakonfigurován pro přesnou shodu. Chcete-li to provést, ujistěte se, že třetí argument je nula nebo FALSE.
Alternativa k VLOOKUP
Vzhledem k tomu, že VLOOKUP také vrátí chybu # N / A, když hodnota není zaokrouhlená, můžete vytvořit vzorec s VLOOKUP, který funguje stejně jako možnost MATCH. Stejně jako u MATCH musíte nakonfigurovat VLOOKUP tak, aby používal přesnou shodu, a poté otestovat výsledek pomocí ISNA. Všimněte si také, že VLOOKUPu dáme pro pole tabulky pouze jeden sloupec (sloupec B).
