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

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

Rádi byste svou každodenní práci dokončili rychle a dokonale? Kutools pro Excel přináší 300 výkonných pokročilých funkcí (kombinace sešitů, součet podle barvy, rozdělení obsahu buněk, převod data a tak dále...) a ušetří vám 80 % času.

  • Navrženo pro 1500 pracovních scénářů, pomůže vám vyřešit 80 % problémů s Excelem.
  • Omezte každý den tisíce kliknutí na klávesnici a myš a ulehčete svým unaveným očím a rukám.
  • Staňte se odborníkem na Excel za 3 minuty. Už si nemusíte pamatovat žádné bolestivé vzorce a kódy VBA.
  • 30denní neomezená bezplatná zkušební verze. 60denní záruka vrácení peněz. Bezplatný upgrade a podpora po dobu 2 let.
Pás karet aplikace Excel (s nainstalovaným programem Kutools pro Excel)

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, Firefox a Nový Internet Explorer.
Snímek obrazovky aplikace Excel (s nainstalovanou kartou Office)
Komentáře (0)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL