Note: The other languages of the website are Google-translated. Back to English

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 1

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.

keresse meg a hiányzó értékeket 2

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.

keresse meg a hiányzó értékeket 3

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.

keresse meg a hiányzó értékeket 4

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

Excel IF függvény

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.

Excel MATCH függvény

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.

Excel VLOOKUP funkció

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.

Excel COUNTIF függvény

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

Szeretnéd gyorsan és tökéletesen elvégezni mindennapi munkádat? A Kutools for Excel 300 hatékony speciális funkciót kínál (munkafüzetek összevonása, színenkénti összegzés, cellatartalom felosztása, dátum konvertálása stb.), és 80%-os időt takaríthat meg Önnek.

  • 1500 munkaforgatókönyvhöz tervezve, segít megoldani az Excel problémák 80%-át.
  • Csökkentse a napi billentyűzet és egér kattintások ezreit, enyhítse fáradt szemét és kezét.
  • Legyen Excel szakértő 3 perc alatt. Nem kell többé emlékezni semmilyen fájdalmas képletre és VBA kódra.
  • 30 napos korlátlan ingyenes próbaidőszak. 60 napos pénzvisszafizetési garancia. Ingyenes frissítés és támogatás 2 évre.
Az Excel szalagja (telepítve a Kutools for Excel programmal)

Office fül - Füles olvasás és szerkesztés engedélyezése a Microsoft Office alkalmazásban (beleértve az Excel programot 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 Excel-t is), akárcsak a Chrome-ot, a Firefox-ot és az új Internet Explorert.
Az Excel képernyőképe (az Office Tab telepítve)
A megjegyzések rendezése szerint
Hozzászólások (0)
Még nincs értékelés. Legyen Ön az első, aki értékel!
Még senki sem írt megjegyzést
Hagyja meg észrevételeit
Feladás vendégként
×
Értékeld ezt a bejegyzést:
0   Karakterek
Javasolt helyek

Kövess minket

Copyright © 2009 - www.extendoffice.com. | Minden jog fenntartva. Powered by ExtendOffice. | Oldaltérkép
A Microsoft és az Office logó a Microsoft Corporation védjegyei vagy bejegyzett védjegyei az Egyesült Államokban és / vagy más országokban.
Sectigo SSL védi