Găsiți valorile lipsă în Excel

Anonim

În acest articol, vom afla despre cum să căutați valorile lipsă din tabel folosind formula în excel.

De exemplu, avem o listă de valori și avem nevoie de confirmare cu privire la locul unde se află valoarea din listă. Pentru aceasta, vom analiza situația și vom sugera câteva formule pentru a face același lucru. Vom folosi diferite funcții în funcție de ușurința table_array.

Următoarea funcție va fi utilizată. Așadar, cunoașteți puțin despre ele înainte de ao utiliza.

  1. Funcția IF
  2. Funcția COUNTIF
  3. Funcția ISNA
  4. Funcția VLOOKUP
  5. Funcția MATCH

Vom construi o formulă din ea. În primul rând, valoarea căutării este căutată în coloana specială a matricei de tabele. Apoi valorile potrivite ne vor da confirmarea folosind funcția IF. Funcția IF returnează confirmarea folosind valorile „Există acolo” și „Lipsește”.

Prima metodă: Utilizarea funcției COUNTIF și IF

Utilizați formula generică

= IF (COUNTIF (listă, valoare_celulă), „Există”, „Lipsește”)

Explicaţie:

  • Funcția COUNTIF păstrează numărul de celule_valor din listă și returnează numărul la funcția IF.
  • Funcția IF consideră 0 ca FALS și orice alt număr, altul decât 0 ca fiind ADEVĂRAT.
  • Funcția IF returnează „Există acolo” ca valoare dacă este adevărat și „Lipsește” ca valoare dacă este falsă.

Exemplu:

Să reușim acest lucru folosind formula din exemplu.

Aici avem un tabel și trebuie să extragem informații din acesta.

Aici avem nevoie de confirmarea prin lista de identificare. Deci, folosim intervalul numit pentru lista de ID-uri. ID folosirea intervalului denumit pentru C2: C14.

Deci vom folosi formula pentru a obține suma totală

= IF (COUNTIF (ID, G4), „Există”, „Lipsește”)

Explicație pentru formulă:

  1. Funcția COUNTIF păstrează numărul de ID 900 în listă și returnează numărul la funcția IF.
  2. Funcția IF consideră 0 ca FALS și orice alt număr, altul decât 0 ca fiind ADEVĂRAT
  3. Funcția IF returnează „Există acolo” ca valoare dacă este adevărat și „Lipsește” ca valoare dacă este falsă.

Aici argumentul matricii funcției este dat ca referință de celulă.

După cum puteți vedea, formula returnează valorile pentru numărul de identificare 807 și 953. Dar returnează lipsă pentru numărul de identificare 900.

A doua metodă: Utilizarea funcției ISNA & VLOOKUP.

Sintaxa formulei:

= IF (ISNA (VLOOKUP (cell_value, list, 1, 0)), "Lipsește", "Există acolo")

Explicație pentru formulă:

  1. Funcția VLOOKUP caută valoarea celulei din prima coloană a listei table_array. Funcția returnează valoarea dacă este găsită sau altfel returnează o eroare # N / A.
  2. Funcția ISNA prinde eroarea # N / A și returnează TRUE dacă există o eroare # N / A sau altfel returnează FALSE.
  3. Funcția IF returnează „Există acolo” ca valoare dacă FALS și „Lipsește” ca valoare dacă este ADEVĂRAT.

După cum vedeți din instantaneul de mai sus. Formula returnează „Există” pentru ID-urile 807 și 953 potrivite, dar returnează „Lipsește” pentru ID-ul 900 neegalat.

A treia metodă: Utilizarea funcției ISNA & MATCH.

Sintaxa formulei:

= IF (ISNA (MATCH (valoare_celulă, listă, 0)), „Lipsește”, „Există”)

Explicație pentru formulă:

  1. Funcția MATCH caută valoarea celulei din lista table_array. Funcția returnează valoarea dacă este găsită sau altfel returnează o eroare # N / A.
  2. Funcția ISNA prinde eroarea # N / A și returnează TRUE dacă există o eroare # N / A sau altfel returnează FALSE.
  3. Funcția IF returnează „Există” ca valoare dacă FALS și „Lipsește” ca valoare dacă este ADEVĂRAT.

După cum vedeți din instantaneul de mai sus. Formula returnează „Există” pentru ID-urile 807 și 953 potrivite, dar returnează „Lipsește” pentru ID-ul 900 neegalat.

Cele de mai sus au explicat 3 exemple pentru a găsi valorile lipsă din listă în excel. Toate cele trei formule funcționează bine, dar există câteva puncte de căutat.

Note:

  1. Funcția VLOOKUP nu se uită spre stânga în table_array.
  2. Funcția COUNTIF acceptă metacaractere ( * , ? ) care ajută la extragerea valorilor care au fraze.
  3. Valorile nenumerice trebuie furnizate între ghilimele duble ("valoare") sau utilizați cell_reference …
  4. Vedeți întreaga listă pe măsură ce funcția returnează valori oriunde se potrivește.
  5. Argumentul matricei funcției poate fi dat ca referință de celulă sau intervale denumite.
  6. Puteți personaliza aceste formule după cum este necesar folosind altă funcție excel.
  7. Funcția returnează suma valorilor care îndeplinesc toate condițiile.

Sper că ați înțeles cum să găsiți valorile lipsă în Excel. Explorați aici mai multe articole despre formule funcționale Excel. Vă rugăm să nu ezitați să ne exprimați întrebarea sau feedbackul pentru articolul de mai sus. Vă vom ajuta.

INDEX-MATCH în Excel

VLOOKUP Valori multiple

VLOOKUP cu Index Dynamic Col

Potrivire parțială cu funcția VLOOKUP

Vlookup după dată în Excel

17 lucruri despre Excel VLOOKUP

Articole populare

50 Comandă rapidă Excel pentru a vă crește productivitatea

Editați o listă derulantă

Referință absolută în Excel

Dacă cu formatare condiționată

Dacă cu wildcards

Vizualizați după dată