Všeobecný vzorec
=IF(COUNTIF(list,value),"OK","Missing")
Zhrnutie
Na identifikáciu hodnôt v jednom zozname, ktoré chýbajú v inom zozname, môžete použiť jednoduchý vzorec založený na funkcii COUNTIF s funkciou IF. V zobrazenom príklade je vzorec v G6:
=IF(COUNTIF(list,F6),"OK","Missing")
kde „zoznam“ je pomenovaný rozsah B6: B11.
Vysvetlenie
Funkcia COUNTIF počíta bunky, ktoré vyhovujú kritériám, a vracia počet nájdených výskytov. Ak žiadne bunky nespĺňajú kritériá, funkcia COUNTIF vráti nulu. Môžete použiť správanie priamo vo vnútri príkazu IF na označenie hodnôt, ktoré majú nulový počet (tj chýbajúce hodnoty). V zobrazenom príklade je vzorec v G6:
=IF(COUNTIF(list,F6),"OK","Missing")
kde „zoznam“ je pomenovaný rozsah, ktorý zodpovedá rozsahu B6: B11.
Funkcia IF vyžaduje logický test na vrátenie hodnoty TRUE alebo FALSE. V takom prípade vykoná funkcia COUNTIF logický test. Ak sa hodnota nachádza v zozname , funkcia COUNTIF vráti číslo priamo do funkcie IF. Týmto výsledkom môže byť akékoľvek číslo … 1, 2, 3 atď.
Funkcia IF vyhodnotí akékoľvek číslo ako PRAVDA, čo spôsobí, že IF sa vráti „OK“. Ak sa hodnota v zozname nenachádza , funkcia COUNTIF vráti nulu (0), ktorá sa vyhodnotí ako FALSE, a funkcia IF vráti hodnotu „Missing“.
Alternatíva so ZÁPASOM
Chýbajúce hodnoty môžete tiež otestovať pomocou funkcie MATCH. MATCH nájde pozíciu položky v zozname a vráti chybu # N / A, ak sa nenájde hodnota. Toto správanie môžete použiť na zostavenie vzorca, ktorý vráti hodnotu „Chýba“ alebo „OK“, a to testovaním výsledku ZHODY s funkciou ISNA. ISNA vráti TRUE iba vtedy, keď prijme chybu # N / A.
Ak chcete použiť MATCH, ako je uvedené v príklade vyššie, vzorec je:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
MATCH musí byť nakonfigurovaný na presnú zhodu. Za týmto účelom skontrolujte, či je tretí argument nula alebo FALSE.
Alternatíva k VLOOKUP
Pretože VLOOKUP vráti chybu # N / A, keď hodnota nie je zaokrúhlená, môžete vytvoriť vzorec s VLOOKUP, ktorý funguje rovnako ako možnosť MATCH. Rovnako ako v prípade MATCH, musíte nakonfigurovať VLOOKUP tak, aby používal presnú zhodu, a potom otestovať výsledok pomocou ISNA. Všimnite si tiež, že VLOOKUP-u dávame pre pole tabuľky iba jeden stĺpec (stĺpec B).
