Poiščite manjkajoče vrednosti v Excelu

Anonim

V tem članku bomo izvedeli, kako poiskati manjkajoče vrednosti iz tabele s formulo v Excelu.

Na primer, imamo seznam vrednosti in potrebujemo potrditev glede tega, kje se nahaja vrednost na seznamu. V ta namen bomo preučili situacijo in predlagali nekaj formul za enako ravnanje. Uporabljali bomo različne funkcije delovanja, odvisno od enostavnosti matrike table_array.

V uporabi bo naslednja funkcija. Zato se pred uporabo malo seznanite z njimi.

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

Iz tega bomo zgradili formulo. Najprej se iskalna vrednost poišče v določenem stolpcu matrike tabel. Nato nam bodo ujemajoče se vrednosti potrdile s funkcijo IF. Funkcija IF vrne potrditev z vrednostmi "Ali obstaja" in "Manjka".

Prva metoda: Uporaba funkcije COUNTIF in IF

Uporabite splošno formulo

= IF (COUNTIF (seznam, vrednost_celice), "Ali obstaja", "Manjka")

Pojasnilo:

  • Funkcija COUNTIF vodi štetje cell_value na seznamu in vrne številko funkciji IF.
  • Če funkcija IF šteje 0 kot FALSE in katero koli drugo celo število razen 0 kot TRUE.
  • IF funkcija vrne "Ali obstaja" kot vrednost, če je res, in "manjka" kot vrednost, če je napačna.

Primer:

To rešimo s pomočjo formule v primeru.

Tukaj imamo tabelo in iz nje moramo izvleči podatke.

Tu potrebujemo potrditev s seznama osebnih dokumentov. Zato za seznam ID uporabljamo imenovano območje. ID imenovani obseg uporabe za C2: C14.

Zato bomo uporabili formulo, da dobimo skupni znesek

= IF (COUNTIF (ID, G4), "Ali obstaja", "Manjka")

Pojasnilo za formulo:

  1. Funkcija COUNTIF hrani število 900 na seznamu in vrne število funkciji IF.
  2. Če funkcija IF šteje 0 kot FALSE in katero koli drugo celo število razen 0 kot TRUE
  3. IF funkcija vrne "Ali obstaja" kot vrednost, če je res, in "manjka" kot vrednost, če je napačna.

Tu so nizi argumentov funkcije podani kot referenca celice.

Kot lahko vidite, formula vrne vrednosti za ID številko 807 in 953. Toda vrne Manjka za ID številko 900.

Druga metoda: Uporaba funkcije ISNA & VLOOKUP.

Sintaksa formule:

= IF (ISNA (VLOOKUP (cell_value, list, 1, 0)), "Manjka", "Ali obstaja")

Pojasnilo za formulo:

  1. Funkcija VLOOKUP poišče vrednost celice v prvem stolpcu seznama table_array. Funkcija vrne vrednost, če je najdena, ali pa vrne napako #N/A.
  2. Funkcija ISNA ujame napako #N/A in vrne TRUE, če obstaja napaka #N/A, ali pa vrne FALSE.
  3. IF funkcija vrne "Ali obstaja" kot vrednost, če je FALSE, in "manjka" kot vrednost, če je TRUE.

Kot vidite iz zgornjega posnetka. Formula vrne "Ali obstaja" za ujemajoči se ID 807 in 953. Toda vrne "Manjka" za neprimerljiv ID 900.

Tretja metoda: Uporaba funkcije ISNA & MATCH.

Sintaksa formule:

= IF (ISNA (MATCH (cell_value, list, 0)), "Manjka", "Ali obstaja")

Pojasnilo za formulo:

  1. Funkcija MATCH poišče vrednost celice na seznamu table_array. Funkcija vrne vrednost, če je najdena, ali pa vrne napako #N/A.
  2. Funkcija ISNA ujame napako #N/A in vrne TRUE, če obstaja napaka #N/A, ali pa vrne FALSE.
  3. IF funkcija vrne "Ali obstaja" kot vrednost, če je FALSE, in "manjka" kot vrednost, če je TRUE.

Kot vidite iz zgornjega posnetka. Formula vrne "Ali obstaja" za ujemajoči se ID 807 in 953. Toda vrne "Manjka" za neprimerljiv ID 900.

Zgoraj so bili razloženi 3 primeri za iskanje manjkajočih vrednosti na seznamu v Excelu. Vse tri formule delujejo dobro, vendar je treba iskati nekaj točk.

Opombe:

  1. Funkcija VLOOKUP ne gleda v levo v matriki_tabel.
  2. Funkcija COUNTIF podpira nadomestne znake ( * , ? ), ki pomaga pri pridobivanju vrednosti z besednimi zvezami.
  3. Neštevilske vrednosti morajo biti navedene v dvojnih narekovajih ("vrednost") ali uporabite cell_reference …
  4. Oglejte si celoten seznam, saj funkcija vrača vrednosti, kjer koli se ujema.
  5. Argument niza funkcije je lahko podan kot referenca celice ali poimenovani obsegi.
  6. Te formule lahko po potrebi prilagodite z drugo funkcijo Excel.
  7. Funkcija vrne vsoto vrednosti, ki izpolnjujejo vse pogoje.

Upam, da ste razumeli, kako poiskati manjkajoče vrednosti v Excelu. Raziščite več člankov o formulah funkcij Excel tukaj. Prosimo, da navedete svoje vprašanje ali povratne informacije za zgornji članek. Mi vam bomo pomagali.

INDEX-MATCH v Excelu

VLOOKUP Več vrednosti

VLOOKUP z indeksom dinamičnega kolona

Delno ujemanje s funkcijo VLOOKUP

Vlookup po datumu v Excelu

17 stvari o Excelu VLOOKUP

Priljubljeni članki

50 Bližnjica v Excelu za večjo produktivnost

Uredite spustni seznam

Absolutna referenca v Excelu

Če s pogojnim oblikovanjem

Če z nadomestnimi znaki

Vpogled po datumu