Keresse meg a hiányzó értékeket
Vannak esetek, amikor össze kell hasonlítania két listát, hogy ellenőrizze, létezik-e az A lista értéke az Excel B listájában. Például van egy terméklistája, és szeretné ellenőrizni, hogy a listán szereplő termékek szerepelnek-e a szállítója által biztosított terméklistában. Ennek a feladatnak az elvégzéséhez az alábbiakban három módszert soroltunk fel, bátran válassza ki a kívántat.
Keresse meg a hiányzó értékeket a MATCH, ISNA és IF segítségével
Keresse meg a hiányzó értékeket a VLOOKUP, ISNA és IF segítségével
Keresse meg a hiányzó értékeket a COUNTIF és IF segítségével
Keresse meg a hiányzó értékeket a MATCH, ISNA és IF segítségével
Utána járni ha a listán szereplő összes termék szerepel a beszállítói listáján amint a fenti képernyőképen látható, először a MATCH funkcióval lekérheti a listán szereplő termék pozícióját (az A lista értéke) a beszállítói listában (B lista). A MATCH a #N/A hibát adja vissza, ha a termék nem található. Ezután betáplálhatja az eredményt az ISNA-ba, hogy az #N/A hibákat IGAZ értékké konvertálja, ami azt jelenti, hogy ezek a termékek hiányoznak. Az IF függvény ezután a várt eredményt adja vissza.
Általános szintaxis
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")
√ Megjegyzés: A „Hiányzó”, „Talált” értékeket tetszőleges értékre módosíthatja.
- lookup_value: A pozíció lekéréséhez használt MATCH érték, ha létezik keresési_tartomány vagy #N/A hiba, ha nem. Itt a listán szereplő termékekre vonatkozik.
- lookup_range: A cellákkal összehasonlítható tartomány keresési_érték. Itt a szállító terméklistájára hivatkozunk.
Utána járni ha a listán szereplő összes termék szerepel a beszállítói listáján, másolja vagy írja be az alábbi képletet a H6 cellába, majd nyomja meg a gombot belép az eredmény eléréséhez:
=IF(ISNA(MATCH(30002,$ B $ 6: $ B $ 10,0)),"Hiányzó","Talált")
Vagy használjon cellahivatkozást a képlet dinamikussá tételéhez:
=IF(ISNA(MATCH(G6,$ B $ 6: $ B $ 10,0)),"Hiányzó","Talált")
√ Megjegyzés: A fenti dollárjelek ($) abszolút hivatkozásokat jeleznek, ami azt jelenti, hogy a keresési_tartomány a képlet nem változik, amikor áthelyezi vagy más cellákba másolja a képletet. Azonban nincsenek dollárjelek hozzáadva a keresési_érték mivel azt szeretné, hogy dinamikus legyen. A képlet beírása után húzza le a kitöltő fogantyút, hogy a képletet az alábbi cellákra alkalmazza.
A képlet magyarázata
Itt példaként az alábbi képletet használjuk:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")
- EGYEZÉS(G8,$B$6:$B$10,0): A match_type 0 arra kényszeríti a MATCH függvényt, hogy olyan számértéket adjon vissza, amely az első egyezés pozícióját jelzi 3004, az érték a G8 cellában, a tömbben $ B $ 6: $ B $ 10. Ebben az esetben azonban a MATCH nem találta az értéket a keresési tömbben, ezért visszaadja a # N / A hiba.
- ISNA(EGYEZÉS(G8,$B$6:$B$10,0)) = ISNA(# N / A): Az ISNA azon dolgozik, hogy kiderítse, hogy egy érték „#N/A” hiba-e vagy sem. Ha igen, a függvény a TURE; Ha az érték nem „#N/A” hiba, akkor FALSE-t ad vissza. Tehát ez az ISNA képlet visszatér TURE.
- HA(ISNA(EGYEZÉS(G8,$B$6:$B$10,0))"Hiányzó", "Talált") = IF(TRUE"Hiányzó", "Talált"): Az IF függvény a Missing értéket adja vissza, ha az ISNA és a MATCH összehasonlítása IGAZ, ellenkező esetben a Found értéket adja vissza. Tehát a képlet visszatér Hiányzó.
Keresse meg a hiányzó értékeket a VLOOKUP, ISNA és IF segítségével
Ha meg szeretné tudni, hogy a listán szereplő összes termék megtalálható-e a beszállítói listáján, lecserélheti a fenti MATCH függvényt a VLOOKUP-ra, mivel az ugyanúgy működik, mint a MATCH, és a #N/A hibát adja vissza, ha az érték nem létezik egy másik lista, vagy azt mondjuk, hogy hiányzik.
Általános szintaxis
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")
√ Megjegyzés: A „Hiányzó”, „Talált” értékeket tetszőleges értékre módosíthatja.
- lookup_value: A VLOOKUP érték, amelyet a pozíció lekéréséhez használt, ha létezik keresési_tartomány vagy #N/A hiba, ha nem. Itt a listán szereplő termékekre vonatkozik.
- lookup_range: A cellákkal összehasonlítható tartomány keresési_érték. Itt a szállító terméklistájára hivatkozunk.
Ha meg szeretné tudni, hogy a listán szereplő összes termék megtalálható-e a beszállítói listáján, kérjük, másolja ki vagy írja be az alábbi képletet a H6 cellába, majd nyomja meg a belép az eredmény eléréséhez:
=IF(ISNA(VLOOKUP(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Hiányzó","Talált")
Vagy használjon cellahivatkozást a képlet dinamikussá tételéhez:
=IF(ISNA(VLOOKUP(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Hiányzó","Talált")
√ Megjegyzés: A fenti dollárjelek ($) abszolút hivatkozásokat jeleznek, ami azt jelenti, hogy a keresési_tartomány a képlet nem változik, amikor áthelyezi vagy más cellákba másolja a képletet. Azonban nincsenek dollárjelek hozzáadva a keresési_érték mivel azt szeretné, hogy dinamikus legyen. A képlet beírása után húzza le a kitöltő fogantyút, hogy a képletet az alábbi cellákra alkalmazza.
A képlet magyarázata
Itt példaként az alábbi képletet használjuk:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- KERESÉS(G8,$B$6:$B$10,1,HAMIS): A range_lookup HAMIS arra kényszeríti a VLOOKUP függvényt, hogy keresse meg és adja vissza a pontosan egyező értéket 3004, a G8 cellában lévő érték. Ha a keresési_érték 3004 létezik a 1a tömb st oszlopa $ B $ 6: $ B $ 10, a VLOOKUP ezt az értéket adja vissza; Ellenkező esetben a #N/A hibaértéket adja vissza. Itt a 3004 nem létezik a tömbben, így az eredmény az lenne # N / A.
- ISNA(KERESÉS(G8,$B$6:$B$10,1,HAMIS)) = ISNA(# N / A): Az ISNA azon dolgozik, hogy kiderítse, hogy egy érték „#N/A” hiba-e vagy sem. Ha igen, a függvény a TURE; Ha az érték nem „#N/A” hiba, akkor FALSE-t ad vissza. Tehát ez az ISNA képlet visszatér TURE.
- HA(ISNA(KERESÉS(G8,$B$6:$B$10,1,HAMIS))"Hiányzó", "Talált") = IF(TRUE"Hiányzó", "Talált"): Az IF függvény hiányzó értéket ad vissza, ha az ISNA és a VLOOKUP összehasonlítása IGAZ, ellenkező esetben a Found értéket adja vissza. Tehát a képlet visszatér Hiányzó.
Keresse meg a hiányzó értékeket a COUNTIF és IF segítségével
Ha meg szeretné tudni, hogy a listán szereplő összes termék megtalálható-e a beszállítói listáján, használhat egy egyszerűbb képletet a COUNTIF és IF függvényekkel. A képlet kihasználja azt a tényt, hogy az Excel a nulla (0) kivételével bármely számot IGAZként értékel. Tehát ha egy érték létezik egy másik listában, a COUNTIF függvény visszaadja az előfordulások számát abban a listában, majd az IF a számot TURE-nek veszi; Ha az érték nem létezik a listában, a COUNTIF függvény 0-t ad vissza, az IF pedig HAMIS értéknek veszi.
Általános szintaxis
=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")
√ Megjegyzés: A „Talált”, „Hiányzó” értékeket igény szerint bármilyen értékre módosíthatja.
- lookup_range: A cellákkal összehasonlítható tartomány keresési_érték. Itt a szállító terméklistájára hivatkozunk.
- lookup_value: A COUNTIF érték, amely az előfordulások számát adja vissza keresési_tartomány. Itt a listán szereplő termékekre vonatkozik.
Ha meg szeretné tudni, hogy a listán szereplő összes termék megtalálható-e a beszállítói listáján, kérjük, másolja ki vagy írja be az alábbi képletet a H6 cellába, majd nyomja meg a belép az eredmény eléréséhez:
=HA(COUNTIF($ B $ 6: $ B $ 10,30002),"Talált","Hiányzó")
Vagy használjon cellahivatkozást a képlet dinamikussá tételéhez:
=HA(COUNTIF($ B $ 6: $ B $ 10,G6),"Talált","Hiányzó")
√ Megjegyzés: A fenti dollárjelek ($) abszolút hivatkozásokat jeleznek, ami azt jelenti, hogy a keresési_tartomány a képlet nem változik, amikor áthelyezi vagy más cellákba másolja a képletet. Azonban nincsenek dollárjelek hozzáadva a keresési_érték mivel azt szeretné, hogy dinamikus legyen. A képlet beírása után húzza le a kitöltő fogantyút, hogy a képletet az alábbi cellákra alkalmazza.
A képlet magyarázata
Itt példaként az alábbi képletet használjuk:
=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")
- COUNTIF($B$6:$B$10,G8): A COUNTIF függvény megszámolja, hogy hányszor 3004, a G8 cellában lévő érték jelenik meg a tömbben $ B $ 6: $ B $ 10. Úgy tűnik, a 3004 nem létezik a tömbben, így az eredmény az lenne 0.
- HA(COUNTIF($B$6:$B$10,G8)"Talált", "Hiányzó") = IF(0"Found", "Missing"): Az IF függvény a 0-t FALSE-ként értékeli. Tehát a képlet visszatér Hiányzó, az az érték, amelyet akkor kell visszaadni, amikor az első kiegészítés HAMIS értéket kap.
Kapcsolódó funkciók
Az IF függvény az Excel munkafüzet egyik legegyszerűbb és leghasznosabb funkciója. Egy egyszerű logikai tesztet hajt végre, amely az összehasonlítási eredménytől függ, és egy értéket ad vissza, ha az eredmény IGAZ, vagy egy másik értéket, ha az eredmény HAMIS.
Az Excel MATCH függvény egy adott értéket keres egy cellatartományban, és visszaadja az érték relatív pozícióját.
Az Excel VLOOKUP függvény keres egy értéket a táblázat első oszlopának illesztésével, és visszaadja a megfelelő értéket ugyanabban a sorban egy bizonyos oszlopból.
A COUNTIF függvény egy statisztikai függvény az Excelben, amely a feltételnek megfelelő cellák számának megszámlálására szolgál. Támogatja a logikai operátorokat (<>, =, > és <), valamint a helyettesítő karaktereket (? és *) a részleges egyeztetéshez.
Kapcsolódó képletek
Keressen egy adott szöveget tartalmazó értéket helyettesítő karakterekkel
Az első egyezés megtalálásához, amely egy bizonyos szöveges karakterláncot tartalmaz egy tartományban az Excelben, használhat egy INDEX és MATCH képletet helyettesítő karakterekkel – csillaggal (*) és kérdőjelekkel (?).
Részleges egyezés a VLOOKUP-val
Vannak esetek, amikor szükség van az Excelre a részinformációk alapján történő adatok lekéréséhez. A probléma megoldásához használhat egy VLOOKUP képletet helyettesítő karakterekkel - a csillaggal (*) és a kérdőjelekkel (?).
Hozzávetőleges egyezés az INDEX és a MATCH kifejezésekkel
Vannak idők, amikor hozzávetőleges egyezéseket kell találnunk az Excelben, hogy értékelhessük az alkalmazottak teljesítményét, osztályozzuk a tanulók pontszámait, kiszámítsuk a postaköltséget súly alapján stb. szükséges eredményeket.
Keresse meg a legközelebbi egyezési értéket több kritériummal
Bizonyos esetekben előfordulhat, hogy több feltétel alapján kell keresnie a legközelebbi vagy közelítő egyezési értéket. Az INDEX, MATCH és IF funkciók kombinációjával gyorsan elvégezheti az Excelben.
A legjobb irodai termelékenységi eszközök
Kutools for Excel - segít kitűnni a tömegből
A Kutools for Excel több mint 300 funkcióval büszkélkedhet, Győződjön meg arról, hogy amire szüksége van, csak egy kattintásnyira van...
Office lap – A lapos olvasás és szerkesztés engedélyezése a Microsoft Office programban (beleértve az Excelt is)
- Egy másodperc a tucatnyi nyitott dokumentum közötti váltáshoz!
- Csökkentse az egérkattintások százait az Ön számára minden nap, búcsút mondjon az egér kezének.
- 50% -kal növeli a termelékenységet több dokumentum megtekintésekor és szerkesztésekor.
- Hatékony lapokat hoz az Office-ba (beleértve az Excelt is), akárcsak a Chrome, az Edge és a Firefox.
