Najděte chybějící hodnoty v Excelu

Anonim

V tomto článku se naučíme, jak vyhledat chybějící hodnoty z tabulky pomocí vzorce v Excelu.

Například máme seznam hodnot a potřebujeme potvrzení o tom, kde se hodnota v seznamu nachází. Z tohoto důvodu budeme zvažovat situaci a navrhneme nějaké vzorce, abychom udělali totéž. V závislosti na snadnosti parametru table_array budeme používat různé funkce.

Bude použita následující funkce. Před použitím se tedy o nich trochu seznamte.

  1. IF funkce
  2. Funkce COUNTIF
  3. Funkce ISNA
  4. Funkce VLOOKUP
  5. Funkce MATCH

Vytvoříme z toho vzorec. Za prvé, vyhledávací hodnota se prohledá v konkrétním sloupci pole tabulky. Poté nám odpovídající hodnoty poskytnou potvrzení pomocí funkce IF. Funkce IF vrací potvrzení pomocí hodnot „Is there“ & „Missing“.

První metoda: Použití funkce COUNTIF a IF

Použijte obecný vzorec

= IF (COUNTIF (seznam, hodnota_ buňky), „Je tam“, „Chybí“)

Vysvětlení:

  • Funkce COUNTIF uchovává počet cell_value v seznamu a vrací číslo do funkce IF.
  • Pokud funkce IF považuje 0 za FALSE a jakékoli jiné celé číslo jiné než 0 za PRAVDA.
  • Pokud funkce IF vrací hodnotu „Is there“ jako Value, je -li true, a „Missing“ jako hodnotu, pokud False.

Příklad:

Pojďme to zvládnout pomocí vzorce v příkladu.

Tady máme tabulku a potřebujeme z ní extrahovat informace.

Zde potřebujeme potvrzení seznamem ID. Pro seznam ID tedy používáme pojmenovaný rozsah. ID pojmenovaný rozsah použití pro C2: C14.

K získání celkové částky tedy použijeme vzorec

= IF (COUNTIF (ID, G4), "Is there", "Missing")

Vysvětlení pro vzorec:

  1. Funkce COUNTIF uchovává počet ID 900 v seznamu a vrací počet do funkce IF.
  2. Pokud funkce IF považuje 0 za FALSE a jakékoli jiné celé číslo jiné než 0 za PRAVDA
  3. Pokud funkce IF vrací hodnotu „Is there“ jako Value, je -li true, a „Missing“ jako hodnotu, pokud False.

Zde jsou pole argumentů pro funkci uvedena jako odkaz na buňku.

Jak vidíte, vzorec vrací hodnoty pro ID číslo 807 a 953. Ale pro ID číslo 900 vrací chybějící.

Druhá metoda: Použití funkce ISNA & VLOOKUP.

Syntaxe vzorce:

= IF (ISNA (VLOOKUP (hodnota_ buňky, seznam, 1, 0)), „chybí“, „je tam“)

Vysvětlení pro vzorec:

  1. Funkce VLOOKUP vyhledá hodnotu buňky v 1. sloupci seznamu table_array. Funkce vrátí hodnotu, pokud je nalezena, nebo vrátí chybu #N/A.
  2. Funkce ISNA zachytí chybu #N/A a vrátí hodnotu TRUE, pokud existuje chyba #N/A nebo vrátí FALSE.
  3. Funkce IF vrací hodnotu „Is there“ jako hodnotu, pokud je FALSE, a „Missing“ jako hodnotu, pokud je TRUE.

Jak vidíte z výše uvedeného snímku. Vzorec vrací "Is There" pro odpovídající ID 807 & 953. Ale vrací "Missing" pro nepřekonatelné ID 900.

Třetí metoda: Použití funkce ISNA & MATCH.

Syntaxe vzorce:

= IF (ISNA (MATCH (hodnota_ buňky, seznam, 0)), "chybí", "je tam")

Vysvětlení pro vzorec:

  1. Funkce MATCH vyhledá hodnotu buňky v seznamu table_array. Funkce vrátí hodnotu, pokud je nalezena, nebo vrátí chybu #N/A.
  2. Funkce ISNA zachytí chybu #N/A a vrátí hodnotu TRUE, pokud existuje chyba #N/A nebo vrátí FALSE.
  3. Funkce IF vrací hodnotu „Is there“ jako hodnotu, pokud je FALSE, a „Missing“ jako hodnotu, pokud je TRUE.

Jak vidíte z výše uvedeného snímku. Vzorec vrací "Is There" pro odpovídající ID 807 & 953. Ale vrací "Missing" pro nepřekonatelné ID 900.

Výše uvedené vysvětlil 3 příklady k nalezení chybějících hodnot v seznamu v aplikaci Excel. Všechny tři vzorce fungují dobře, ale je zde několik bodů, které je třeba hledat.

Poznámky:

  1. Funkce VLOOKUP nevypadá doleva v table_array.
  2. Funkce COUNTIF podporuje zástupné znaky ( * , ? ), což pomáhá při získávání hodnot s frázemi.
  3. Numerické hodnoty musí být uvedeny v uvozovkách ("hodnota") nebo použijte odkaz na buňku…
  4. Podívejte se na celý seznam, protože funkce vrací hodnoty všude tam, kde se shodují.
  5. Argument pole pro funkci lze zadat jako odkaz na buňku nebo pojmenované rozsahy.
  6. Tyto vzorce můžete podle potřeby přizpůsobit pomocí jiné funkce aplikace Excel.
  7. Funkce vrací součet hodnot splňujících všechny podmínky.

Doufám, že jste pochopili, jak najít chybějící hodnoty v Excelu. Zde najdete další články o vzorcích funkcí Excelu. Neváhejte uvést svůj dotaz nebo zpětnou vazbu k výše uvedenému článku. Pomůžeme vám.

INDEX-MATCH v Excelu

VLOOKUP Více hodnot

VLOOKUP s Dynamic Col Index

Částečná shoda s funkcí VLOOKUP

Vlookup podle data v Excelu

17 Věcí o VYHLEDÁVÁNÍ Excelu

Populární články

50 Zkratka aplikace Excel pro zvýšení produktivity

Upravte rozevírací seznam

Absolutní reference v Excelu

Pokud s podmíněným formátováním

Pokud se zástupnými znaky

Vlookup podle data