Általános képlet
=IF(COUNTIF(list,value),"OK","Missing")
Összegzés
Az egyik lista azon értékeinek azonosításához, amelyek hiányoznak egy másik listából, használhat egy egyszerű képletet, amely a COUNTIF függvényen alapul, és az IF függvénnyel. A bemutatott példában a G6 képlete a következő:
=IF(COUNTIF(list,F6),"OK","Missing")
ahol a "lista" a B6: B11 megnevezett tartomány.
Magyarázat
A COUNTIF függvény megszámolja a kritériumoknak megfelelő cellákat, visszaadva a talált előfordulások számát. Ha egyetlen cella sem felel meg a feltételeknek, a COUNTIF nullát ad vissza. A viselkedést közvetlenül az IF utasításban használhatja olyan értékek megjelölésére, amelyeknek nullszáma van (azaz hiányzó értékek vannak). A bemutatott példában a G6 képlete a következő:
=IF(COUNTIF(list,F6),"OK","Missing")
ahol a "lista" egy megnevezett tartomány, amely megfelel a B6: B11 tartománynak.
Az IF függvényhez logikai tesztre van szükség az IGAZ vagy HAMIS visszatéréséhez. Ebben az esetben a COUNTIF függvény végzi a logikai tesztet. Ha az érték megtalálható a listában , akkor a COUNTIF visszaad egy számot közvetlenül az IF függvénynek. Ez az eredmény tetszőleges szám lehet: 1, 2, 3 stb.
Az IF függvény bármely számot IGAZként értékel, aminek következtében az IF "OK" értéket ad vissza. Ha az érték nem található a listán , akkor a COUNTIF nullát (0) ad vissza, ami FALSE értéknek számít , az IF pedig "Hiányzó" értéket ad vissza.
A MATCH alternatívája
A hiányzó értékeket a MATCH funkcióval is tesztelheti. A MATCH megtalálja az elem pozícióját a listában, és ha nem található érték, akkor a # N / A hibát adja vissza. Ezzel a viselkedéssel olyan képletet hozhat létre, amely a „Hiányzó” vagy „OK” értéket adja vissza, a MATCH eredményének ISNA függvénnyel történő tesztelésével. Az ISNA csak akkor adja vissza az IGAZ értéket, ha # N / A hibát kap.
A MATCH használatához a fenti példában látható képlet:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
Ne feledje, hogy a MATCH-ot konfigurálni kell a pontos egyezéshez. Ehhez ellenőrizze, hogy a harmadik argumentum nulla vagy HAMIS.
A VLOOKUP alternatívája
Mivel a VLOOKUP # N / A hibát is ad vissza, ha az érték nem kerek, a VLOOKUP segítségével létrehozhat egy képletet, amely ugyanúgy működik, mint a MATCH opció. A MATCH-hoz hasonlóan a VLOOKUP-ot is be kell állítania a pontos egyezés használatához, majd az eredményt ISNA-val kell tesztelni. Vegye figyelembe azt is, hogy a VLOOKUP-nak csak egyetlen oszlopot (B oszlopot) adunk a táblázat tömbhöz.
