Pronađite nedostajuće vrijednosti u Excelu

Anonim

U ovom ćemo članku naučiti o tome kako potražiti nedostajuće vrijednosti iz tablice pomoću formule u Excelu.

Na primjer, imamo popis vrijednosti i potrebna nam je potvrda o tome gdje se nalazi vrijednost na popisu. Za to ćemo razmotriti situaciju i predložiti neke formule da učinimo isto. Koristit ćemo različite funkcije rada ovisno o jednostavnosti matrice table_array.

Sljedeća funkcija će se koristiti. Zato se upoznajte s njima prije nego što ga upotrijebite.

  1. IF funkcija
  2. COUNTIF funkcija
  3. ISNA funkcija
  4. VLOOKUP funkcija
  5. MATCH funkcija

Iz toga ćemo izgraditi formulu. Prvo, tražena vrijednost se traži u određenom stupcu polja tablice. Tada će nam podudarne vrijednosti dati potvrdu pomoću IF funkcije. IF funkcija vraća potvrdu koristeći vrijednosti "Je li tu" i "Nedostaje".

Prva metoda: Korištenje funkcije COUNTIF i IF

Koristite opću formulu

= IF (COUNTIF (popis, vrijednost_ ćelije), "Postoji li", "Nedostaje")

Obrazloženje:

  • COUNTIF funkcija održava broj cell_value na popisu i vraća broj IF funkciji.
  • IF funkcija smatra 0 kao FALSE, a bilo koji drugi cijeli broj osim 0 kao TRUE.
  • IF funkcija vraća "Postoji li" kao vrijednost ako je istina, a "nedostaje" kao vrijednost ako je netočna.

Primjer:

Dovršimo ovo pomoću ovoga koristeći formulu u primjeru.

Ovdje imamo tablicu i iz toga moramo izvući podatke.

Ovdje nam je potrebna potvrda s ID liste. Stoga koristimo imenovani raspon za popis ID -ova. iskaznica naziv raspona za C2: C14.

Stoga ćemo koristiti formulu za dobivanje ukupnog iznosa

= AKO (COUNTIF (ID, G4), "Postoji li", "Nedostaje")

Objašnjenje formule:

  1. COUNTIF funkcija održava broj ID 900 na popisu i vraća broj IF funkciji.
  2. IF funkcija smatra 0 kao FALSE, a bilo koji drugi cijeli broj osim 0 kao TRUE
  3. IF funkcija vraća "Postoji li" kao vrijednost ako je istina, a "nedostaje" kao vrijednost ako je netočna.

Ovdje se nizovi argumenata za funkciju navode kao referenca ćelije.

Kao što vidite, formula vraća vrijednosti za ID broj 807 i 953. No, vraća ID nedostaje za ID broj 900.

Druga metoda: Korištenje funkcije ISNA & VLOOKUP.

Sintaksa formule:

= IF (ISNA (VLOOKUP (cell_value, list, 1, 0)), "Nedostaje", "Postoji li")

Objašnjenje formule:

  1. Funkcija VLOOKUP traži vrijednost ćelije u prvom stupcu popisa table_array. Funkcija vraća vrijednost ako je pronađena ili vraća pogrešku #N/A.
  2. ISNA funkcija hvata grešku #N/A i vraća TRUE ako greška #N/A postoji ili inače vraća FALSE.
  3. IF funkcija vraća "Postoji li" kao vrijednost ako je FALSE i "nedostaje" kao vrijednost ako je TRUE.

Kao što vidite iz gornje snimke. Formula vraća "Postoji li" za podudarni ID 807 i 953. No, vraća "Nedostaje" za neusklađeni ID 900.

Treća metoda: Korištenje funkcije ISNA & MATCH.

Sintaksa formule:

= IF (ISNA (MATCH (cell_value, list, 0)), "Nedostaje", "Postoji li")

Objašnjenje formule:

  1. MATCH funkcija traži vrijednost ćelije na popisu table_array. Funkcija vraća vrijednost ako je pronađena ili vraća pogrešku #N/A.
  2. ISNA funkcija hvata grešku #N/A i vraća TRUE ako greška #N/A postoji ili inače vraća FALSE.
  3. IF funkcija vraća "Postoji li" kao vrijednost ako je FALSE, a "nedostaje" kao vrijednost ako je TRUE.

Kao što vidite iz gornje snimke. Formula vraća "Postoji li" za podudarni ID 807 i 953. No, vraća "Nedostaje" za neusklađeni ID 900.

Gore objašnjena 3 primjera za pronalaženje nedostajućih vrijednosti na popisu u Excelu. Sve tri formule rade dobro, ali postoje neke točke na koje treba obratiti pažnju.

Bilješke:

  1. Funkcija VLOOKUP ne gleda lijevo u matrici_tablice.
  2. Funkcija COUNTIF podržava zamjenske znakove ( * , ? ) koji pomaže u izdvajanju vrijednosti koje sadrže izraze.
  3. Numeričke vrijednosti moraju biti navedene u dvostrukim navodnicima ("vrijednost") ili upotrijebite cell_reference …
  4. Pogledajte cijeli popis jer funkcija vraća vrijednosti gdje god se podudara.
  5. Argument niza funkcije može se dati kao referenca ćelije ili imenovani rasponi.
  6. Ove formule možete prilagoditi prema potrebi pomoću druge Excel funkcije.
  7. Funkcija vraća zbroj vrijednosti koje zadovoljavaju sve uvjete.

Nadam se da ste razumjeli kako pronaći nedostajuće vrijednosti u Excelu. Ovdje istražite više članaka o formulama funkcija programa Excel. Slobodno navedite svoj upit ili povratnu informaciju za gornji članak. Mi ćemo vam pomoći.

INDEX-MATCH u Excelu

VLOOKUP Više vrijednosti

VLOOKUP s Dynamic Col Index

Djelomično podudaranje s funkcijom VLOOKUP

Pogled prema datumu u Excelu

17 stvari o Excelu VLOOKUP

Popularni članci

50 Excel prečac za povećanje vaše produktivnosti

Uredite padajući popis

Apsolutna referenca u Excelu

Ako s uvjetnim oblikovanjem

Ako sa zamjenskim znakovima

Pogled prema datumu