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í.
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.
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.
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.
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 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 vyhledá konkrétní hodnotu v rozsahu buněk a vrátí relativní polohu hodnoty.
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 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 (?).
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
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.
