Nájdite chýbajúce hodnoty v programe Excel

Anonim

V tomto článku sa dozvieme, ako vyhľadať v tabuľke chýbajúce hodnoty pomocou vzorca v programe Excel.

Máme napríklad zoznam hodnôt a potrebujeme potvrdenie o tom, kde sa hodnota v zozname nachádza. Preto zvážime situáciu a navrhneme niekoľko vzorcov, aby sme urobili to isté. V závislosti od jednoduchosti parametra table_array budeme používať rôzne funkcie.

Bude použitá nasledujúca funkcia. Pred použitím sa teda o nich trochu zoznámte.

  1. IF funkcia
  2. Funkcia COUNTIF
  3. Funkcia ISNA
  4. Funkcia VLOOKUP
  5. Funkcia MATCH

Zostrojíme z toho vzorec. Po prvé, hodnota vyhľadávania sa vyhľadá v konkrétnom stĺpci poľa tabuľky. Potom nám zodpovedajúce hodnoty poskytnú potvrdenie pomocou funkcie IF. Funkcia IF vráti potvrdenie pomocou hodnôt „Je tam“ a „Chýba“.

Prvá metóda: Použitie funkcií COUNTIF a IF

Použite generický vzorec

= IF (COUNTIF (zoznam, hodnota_ bunky), „Je tam“, „Chýba“)

Vysvetlenie:

  • Funkcia COUNTIF udrží počet hodnôt cell_value v zozname a vráti číslo do funkcie IF.
  • Ak funkcia IF považuje 0 za NEPRAVDU a akékoľvek iné celé číslo iné ako 0 za PRAVDU.
  • Ak funkcia IF vráti hodnotu „Is there“ ako hodnotu, ak je true, a „Missing“ ako hodnotu, ak je hodnota False.

Príklad:

Poďme to zvládnuť pomocou vzorca v príklade.

Tu máme tabuľku a musíme z nej získať informácie.

Tu potrebujeme potvrdenie zoznamom ID. Pre zoznam ID teda používame pomenovaný rozsah. ID pomenované použitie rozsahu pre C2: C14.

Na získanie celkovej sumy použijeme vzorec

= IF (COUNTIF (ID, G4), "Je tam", "Chýba")

Vysvetlenie pre vzorec:

  1. Funkcia COUNTIF uchováva počet ID 900 v zozname a vracia ich do funkcie IF.
  2. Ak funkcia IF považuje 0 za NEPRAVDU a akékoľvek iné celé číslo iné ako 0 za PRAVDU
  3. Ak funkcia IF vráti hodnotu „Is there“ ako hodnotu, ak je true, a „Missing“ ako hodnotu, ak je hodnota False.

Tu sú polia argumentov pre funkciu uvedené ako odkaz na bunku.

Ako vidíte, vzorec vracia hodnoty pre identifikačné číslo 807 a 953. Pri identifikačnom čísle 900 však chýba.

Druhá metóda: Použitie funkcie ISNA & VLOOKUP.

Syntax vzorca:

= IF (ISNA (VLOOKUP (hodnota_ bunky, zoznam, 1, 0)), „chýba“, „je tam“)

Vysvetlenie pre vzorec:

  1. Funkcia VLOOKUP vyhľadá hodnotu bunky v 1. stĺpci zoznamu table_array. Funkcia vráti hodnotu, ak je nájdená, alebo vráti chybu #N/A.
  2. Funkcia ISNA zachytí chybu #N/A a vráti hodnotu TRUE, ak existuje chyba #N/A alebo vráti hodnotu FALSE.
  3. Ak funkcia IF vráti hodnotu „Is there“ ako hodnotu, ak je FALSE, a „Missing“ ako hodnotu, ak je TRUE.

Ako vidíte z vyššie uvedenej snímky. Vzorec vráti „Is There“ pre zodpovedajúce ID 807 & 953. Ale vráti „Missing“ pre bezkonkurenčné ID 900.

Tretia metóda: Použitie funkcie ISNA & MATCH.

Syntax vzorca:

= IF (ISNA (MATCH (hodnota_ bunky, zoznam, 0)), „chýba“, „je tam“)

Vysvetlenie pre vzorec:

  1. Funkcia MATCH vyhľadá hodnotu bunky v zozname table_array. Funkcia vráti hodnotu, ak je nájdená, alebo vráti chybu #N/A.
  2. Funkcia ISNA zachytí chybu #N/A a vráti hodnotu TRUE, ak existuje chyba #N/A alebo vráti hodnotu FALSE.
  3. Ak funkcia IF vráti hodnotu „Is there“ ako hodnotu, ak je FALSE, a „Missing“ ako hodnotu, ak je TRUE.

Ako vidíte z vyššie uvedenej snímky. Vzorec vráti „Is There“ pre zodpovedajúce ID 807 & 953. Ale vráti „Missing“ pre bezkonkurenčné ID 900.

Vyššie uvedené vysvetľuje 3 príklady na nájdenie chýbajúcich hodnôt v zozname v programe Excel. Všetky tri vzorce fungujú dobre, ale je potrebné hľadať body.

Poznámky:

  1. Funkcia VLOOKUP sa v poli table_array nepozerá doľava.
  2. Funkcia COUNTIF podporuje zástupné znaky ( * , ? ), čo pomáha pri získavaní hodnôt s frázami.
  3. Numerické hodnoty musia byť uvedené v dvojitých úvodzovkách ("hodnota") alebo použite odkaz na bunku …
  4. Pozrite sa na celý zoznam, pretože funkcia vracia hodnoty kdekoľvek sa zhoduje.
  5. Argument poľa pre funkciu môže byť uvedený ako odkaz na bunku alebo pomenované rozsahy.
  6. Tieto vzorce môžete podľa potreby prispôsobiť pomocou inej funkcie programu Excel.
  7. Funkcia vráti súčet hodnôt, ktoré spĺňajú všetky podmienky.

Dúfam, že ste pochopili, ako nájsť chýbajúce hodnoty v programe Excel. Tu nájdete ďalšie články o vzorcoch funkcií programu Excel. Neváhajte uviesť svoj dotaz alebo spätnú väzbu k vyššie uvedenému článku. Pomôžeme vám.

INDEX-MATCH v programe Excel

VLOOKUP Viacnásobné hodnoty

VLOOKUP s indexom Dynamic Col

Čiastočná zhoda s funkciou VLOOKUP

Vlookup podľa dátumu v Exceli

17 vecí o VLOOKUPE v Exceli

Populárne články

50 Skratka pre Excel, ktorá zvýši vašu produktivitu

Upravte rozbaľovací zoznam

Absolútna referencia v Exceli

Ak s podmieneným formátovaním

Ak so zástupnými znakmi

Vlookup podľa dátumu