Přejít k hlavnímu obsahu

Najděte chybějící hodnoty

Existují případy, kdy potřebujete porovnat dva seznamy, abyste zjistili, zda hodnota seznamu A existuje v seznamu B v aplikaci Excel. Máte například seznam produktů a chcete zkontrolovat, zda produkty ve vašem seznamu existují v seznamu produktů poskytnutém vaším dodavatelem. Chcete-li tento úkol splnit, uvádíme níže tři způsoby, můžete si vybrat ten, který se vám líbí.

najít chybějící hodnoty 1

Najděte chybějící hodnoty pomocí MATCH, ISNA a IF
Najděte chybějící hodnoty pomocí funkcí VLOOKUP, ISNA a IF
Najděte chybějící hodnoty pomocí COUNTIF a IF


Najděte chybějící hodnoty pomocí MATCH, ISNA a IF

Zjistit pokud všechny produkty ve vašem seznamu existují v seznamu vašeho dodavatele jak je znázorněno na obrázku výše, můžete nejprve použít funkci MATCH k načtení pozice produktu z vašeho seznamu (hodnota seznamu A) v seznamu dodavatele (seznam B). MATCH vrátí chybu #N/A, když nebude produkt nalezen. Potom můžete výsledek odeslat do ISNA, aby se chyby #N/A převedly na TRUEs, což znamená, že tyto produkty chybí. Funkce IF pak vrátí výsledek, který očekáváte.

Obecná syntaxe

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Poznámka: Hodnoty „Chybí“, „Nalezeno“ můžete změnit na libovolné hodnoty, jak potřebujete.

  • vyhledávací_hodnota: Hodnota MATCH použitá k načtení její pozice, pokud existuje rozsah vyhledávání nebo #N/A chyba, pokud ne. Zde se odkazuje na produkty ve vašem seznamu.
  • rozsah vyhledávání: Rozsah buněk, které se mají porovnat s lookup_value. Zde se odkazuje na seznam produktů dodavatele.

Zjistit pokud všechny produkty ve vašem seznamu existují v seznamu vašeho dodavatele, zkopírujte nebo zadejte vzorec níže do buňky H6 a stiskněte vstoupit získat výsledek:

=IF(ISNA(Shoda(30002,$ B $ 6: $ B $ 10,0)),"Chybí","Nalezeno")

Nebo použijte odkaz na buňku, aby byl vzorec dynamický:

=IF(ISNA(Shoda(G6,$ B $ 6: $ B $ 10,0)),"Chybí","Nalezeno")

√ Poznámka: Znaky dolaru ($) výše označují absolutní odkazy, což znamená rozsah vyhledávání ve vzorci se nezmění, když vzorec přesunete nebo zkopírujete do jiných buněk. Nejsou zde však přidány žádné znaky dolaru lookup_value protože chcete, aby to bylo dynamické. Po zadání vzorce přetáhněte úchyt výplně dolů, abyste vzorec použili na níže uvedené buňky.

najít chybějící hodnoty 2

Vysvětlení vzorce

Zde jako příklad používáme níže uvedený vzorec:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

  • MATCH(G8;$B$6:$B$10,0): Typ shody 0 vynutí funkci MATCH, aby vrátila číselnou hodnotu, která označuje pozici první shody 3004, hodnota v buňce G8, v poli $ B $ 6: $ B $ 10. V tomto případě však MATCH nemohl najít hodnotu ve vyhledávacím poli, takže vrátí hodnotu # N / A chyba.
  • ISNA(MATCH(G8;$B$6:$B$10,0)) = ISNA(# N / A): ISNA pracuje na zjištění, zda je hodnota chybou „#N/A“ či nikoli. Pokud ano, funkce vrátí TURE; Pokud je hodnota jiná než chyba „#N/A“, vrátí hodnotu FALSE. Takže tento vzorec ISNA se vrátí TURU.
  • LI(ISNA(MATCH(G8;$B$6:$B$10,0))"Chybí","Nalezeno") = IF(TRUE"Chybí","Nalezeno"): Funkce IF vrátí Chybějící, pokud je porovnání provedené ISNA a MATCH PRAVDA, jinak vrátí nalezeno. Vzorec se tedy vrátí Chybějící.

Najděte chybějící hodnoty pomocí funkcí VLOOKUP, ISNA a IF

Chcete-li zjistit, zda všechny produkty ve vašem seznamu existují v seznamu vašeho dodavatele, můžete výše uvedenou funkci MATCH nahradit funkcí VLOOKUP, protože funguje stejně jako MATCH, že vrátí chybu #N/A, pokud hodnota neexistuje v jiný seznam, nebo říkáme, že chybí.

Obecná syntaxe

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Poznámka: Hodnoty „Chybí“, „Nalezeno“ můžete změnit na libovolné hodnoty, jak potřebujete.

  • vyhledávací_hodnota: Hodnota VLOOKUP použitá k načtení její pozice, pokud existuje rozsah vyhledávání nebo #N/A chyba, pokud ne. Zde se odkazuje na produkty ve vašem seznamu.
  • rozsah vyhledávání: Rozsah buněk, které se mají porovnat s lookup_value. Zde se odkazuje na seznam produktů dodavatele.

Chcete-li zjistit, zda všechny produkty ve vašem seznamu existují v seznamu vašeho dodavatele, zkopírujte nebo zadejte níže uvedený vzorec do buňky H6 a stiskněte vstoupit získat výsledek:

=IF(ISNA(VYHLEDAT(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Chybí","Nalezeno")

Nebo použijte odkaz na buňku, aby byl vzorec dynamický:

=IF(ISNA(VYHLEDAT(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Chybí","Nalezeno")

√ Poznámka: Znaky dolaru ($) výše označují absolutní odkazy, což znamená rozsah vyhledávání ve vzorci se nezmění, když vzorec přesunete nebo zkopírujete do jiných buněk. Nejsou zde však přidány žádné znaky dolaru lookup_value protože chcete, aby to bylo dynamické. Po zadání vzorce přetáhněte úchyt výplně dolů, abyste vzorec použili na níže uvedené buňky.

najít chybějící hodnoty 3

Vysvětlení vzorce

Zde jako příklad používáme níže uvedený vzorec:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

  • SVYHLEDAT(G8,$B$6:$B$10,1;NEPRAVDA): Range_lookup NEPRAVDIVÉ vynutí funkci VLOOKUP vyhledat a vrátit hodnotu, která přesně odpovídá 3004, hodnota v buňce G8. Pokud je hodnota lookup_value 3004 existuje v 1st sloupec pole $ B $ 6: $ B $ 10, SVYHLEDAT tuto hodnotu vrátí; V opačném případě vrátí chybovou hodnotu #N/A. Zde 3004 v poli neexistuje, takže výsledek by byl # N / A.
  • ISNA(SVYHLEDAT(G8,$B$6:$B$10,1;NEPRAVDA)) = ISNA(# N / A): ISNA pracuje na zjištění, zda je hodnota chybou „#N/A“ či nikoli. Pokud ano, funkce vrátí TURE; Pokud je hodnota jiná než chyba „#N/A“, vrátí hodnotu FALSE. Takže tento vzorec ISNA se vrátí TURU.
  • LI(ISNA(SVYHLEDAT(G8,$B$6:$B$10,1;NEPRAVDA))"Chybí","Nalezeno") = IF(TRUE"Chybí","Nalezeno"): Funkce IF vrátí Chybějící, pokud je porovnání provedené ISNA a SVYHLEDAT PRAVDA, jinak vrátí nalezeno. Vzorec se tedy vrátí Chybějící.

Najděte chybějící hodnoty pomocí COUNTIF a IF

Chcete-li zjistit, zda všechny produkty ve vašem seznamu existují v seznamu vašeho dodavatele, můžete použít jednodušší vzorec s funkcemi COUNTIF a IF. Vzorec využívá toho, že Excel vyhodnotí jakékoli číslo kromě nuly (0) jako PRAVDIVÉ. Pokud tedy hodnota existuje v jiném seznamu, funkce COUNTIF vrátí počet jejích výskytů v tomto seznamu, pak IF vezme číslo jako TURE; Pokud hodnota v seznamu neexistuje, funkce COUNTIF vrátí 0 a IF ji vezme jako FALSE.

Obecná syntaxe

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Poznámka: Hodnoty „Nalezeno“ a „Chybí“ můžete podle potřeby změnit na libovolné hodnoty.

  • rozsah vyhledávání: Rozsah buněk, které se mají porovnat s lookup_value. Zde se odkazuje na seznam produktů dodavatele.
  • vyhledávací_hodnota: Hodnota COUNTIF použitá k vrácení počtu jejích výskytů v rozsah vyhledávání. Zde se odkazuje na produkty ve vašem seznamu.

Chcete-li zjistit, zda všechny produkty ve vašem seznamu existují v seznamu vašeho dodavatele, zkopírujte nebo zadejte níže uvedený vzorec do buňky H6 a stiskněte vstoupit získat výsledek:

=IF(COUNTIF($ B $ 6: $ B $ 10,30002),"Nalezeno" "Chybí")

Nebo použijte odkaz na buňku, aby byl vzorec dynamický:

=IF(COUNTIF($ B $ 6: $ B $ 10,G6),"Nalezeno" "Chybí")

√ Poznámka: Znaky dolaru ($) výše označují absolutní odkazy, což znamená rozsah vyhledávání ve vzorci se nezmění, když vzorec přesunete nebo zkopírujete do jiných buněk. Nejsou zde však přidány žádné znaky dolaru lookup_value protože chcete, aby to bylo dynamické. Po zadání vzorce přetáhněte úchyt výplně dolů, abyste vzorec použili na níže uvedené buňky.

najít chybějící hodnoty 4

Vysvětlení vzorce

Zde jako příklad používáme níže uvedený vzorec:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

  • COUNTIF($B$6:$B$10,G8): Funkce COUNTIF počítá, kolikrát 3004, hodnota v buňce G8, se objeví v poli $ B $ 6: $ B $ 10. Zdá se, že 3004 v poli neexistuje, takže výsledek by byl 0.
  • LI(COUNTIF($B$6:$B$10,G8)"Nalezeno","Chybí") = IF(0"Nalezeno" "Chybí"): Funkce IF vyhodnotí 0 jako FALSE. Vzorec se tedy vrátí Chybějící, hodnota, která se vrátí, když se první rozšíření vyhodnotí jako FALSE.

Související funkce

Funkce Excel IF

Funkce IF je jednou z nejjednodušších a nejužitečnějších funkcí v sešitu aplikace Excel. Provádí jednoduchý logický test, který v závislosti na výsledku porovnání, a vrací jednu hodnotu, pokud je výsledek PRAVDA, nebo jinou hodnotu, pokud je výsledek FALSE.

Funkce Excel MATCH

Funkce Excel MATCH vyhledá konkrétní hodnotu v rozsahu buněk a vrátí relativní polohu hodnoty.

Funkce VLOOKUP aplikace Excel

Funkce Excel VLOOKUP vyhledá hodnotu podle shody v prvním sloupci tabulky a vrátí odpovídající hodnotu z určitého sloupce ve stejném řádku.

Funkce Excel COUNTIF

Funkce COUNTIF je statistická funkce v Excelu, která se používá k počítání počtu buněk, které splňují kritérium. Podporuje logické operátory (<>, =, > a <) a zástupné znaky (? a *) pro částečnou shodu.


Související vzorce

Vyhledejte hodnotu obsahující konkrétní text se zástupnými znaky

Chcete-li najít první shodu, která obsahuje určitý textový řetězec v rozsahu v aplikaci Excel, můžete použít vzorec INDEX a MATCH se zástupnými znaky – hvězdičkou (*) a otazníkem (?).

Částečná shoda s VLOOKUP

Jsou chvíle, kdy potřebujete Excel k načtení dat na základě dílčích informací. K vyřešení problému můžete použít vzorec SVYHLEDAT spolu se zástupnými znaky – hvězdičkou (*) a otazníkem (?).

Přibližná shoda s INDEX a MATCH

Jsou chvíle, kdy potřebujeme najít přibližné shody v Excelu k vyhodnocení výkonu zaměstnanců, hodnocení studentů, vypočítat poštovné na základě hmotnosti atd. V tomto tutoriálu si povíme, jak pomocí funkcí INDEX a MATCH načíst výsledky, které potřebujeme.

Vyhledejte nejbližší hodnotu shody s více kritérii

V některých případech možná budete muset vyhledat nejbližší nebo přibližnou hodnotu shody na základě více než jednoho kritéria. Díky kombinaci funkcí INDEX, MATCH a IF to v Excelu rychle zvládnete.


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations