Atrodiet trūkstošās vērtības programmā Excel

Anonim

Šajā rakstā mēs uzzināsim, kā tabulā meklēt trūkstošās vērtības, izmantojot Excel formulu.

Piemēram, mums ir vērtību saraksts, un mums ir nepieciešams apstiprinājums par vērtības atrašanās vietu sarakstā. Šim nolūkam mēs apsvērsim situāciju un ieteiksim dažas formulas, kā rīkoties tāpat. Mēs izmantosim dažādas funkcijas, atkarībā no table_array viegluma.

Tiks izmantota šāda funkcija. Tāpēc pirms lietošanas nedaudz iepazīstieties ar viņiem.

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

Mēs no tā izveidosim formulu. Pirmkārt, uzmeklēšanas vērtība tiek meklēta tabulas masīva konkrētajā slejā. Tad saskaņotās vērtības mums sniegs apstiprinājumu, izmantojot IF funkciju. Funkcija IF atgriež apstiprinājumu, izmantojot vērtības "Vai ir" un "Trūkst".

Pirmā metode: COUNTIF un IF funkcijas izmantošana

Izmantojiet vispārīgo formulu

= IF (COUNTIF (saraksts, šūnas_vērtība), "Vai ir", "Trūkst")

Paskaidrojums:

  • Funkcija COUNTIF saglabā šūnu_vērtību skaitu sarakstā un atgriež skaitli funkcijai IF.
  • Funkcija IF uzskata, ka 0 ir FALSE, bet jebkurš cits skaitlis, kas nav 0, ir TRUE.
  • Funkcija IF atgriež vērtību “Vai ir” kā vērtība, ja tā ir patiesa, un “trūkst” kā vērtība, ja tā ir nepatiesa.

Piemērs:

Apspriedīsim to, izmantojot piemēra formulu.

Šeit mums ir tabula, un mums no tās ir jāiegūst informācija.

Šeit mums ir nepieciešams apstiprinājums pēc ID saraksta. Tātad ID sarakstam mēs izmantojam nosaukto diapazonu. ID nosaukts diapazona lietojums C2: C14.

Tātad, lai iegūtu kopējo summu, mēs izmantosim formulu

= JA (COUNTIF (ID, G4), "Vai ir", "Trūkst")

Formulas skaidrojums:

  1. Funkcija COUNTIF saglabā ID 900 skaitu sarakstā un atgriež skaitli IF funkcijai.
  2. Funkcija IF uzskata, ka 0 ir FALSE, bet jebkurš cits skaitlis, kas nav 0, ir TRUE
  3. Funkcija IF atgriež vērtību “Vai ir” kā vērtība, ja tā ir patiesa, un “trūkst” kā vērtība, ja tā ir nepatiesa.

Šeit funkcijas argumentu masīvi ir norādīti kā šūnu atsauce.

Kā redzat, formula atgriež ID numura 807 un 953 vērtības. Bet atgriež ID numuru 900.

Otrā metode: Funkcijas ISNA & VLOOKUP izmantošana.

Formulas sintakse:

= IF (ISNA (VLOOKUP (šūnu_vērtība, saraksts, 1, 0)), "Trūkst", "Vai ir")

Formulas skaidrojums:

  1. Funkcija VLOOKUP meklē tabulas_masīva saraksta 1. slejas šūnu vērtību. Funkcija atgriež vērtību, ja tā tiek atrasta, vai arī atgriež kļūdu #N/A.
  2. Funkcija ISNA uztver kļūdu #N/A un atgriež TRUE, ja pastāv #N/A kļūda vai arī tiek atgriezta FALSE.
  3. Funkcija IF atgriež vērtību "Vai ir" kā vērtība, ja tā ir FALSE, un "trūkst" kā vērtību, ja tā ir TRUE.

Kā redzat no iepriekš minētā momentuzņēmuma. Formula atgriež atbilstības ID 807 un 953 "Vai ir", bet nesaskaņotajam ID 900 atgriež "Trūkst".

Trešā metode: Funkcijas ISNA & MATCH izmantošana.

Formulas sintakse:

= IF (ISNA (MATCH (šūnu_vērtība, saraksts, 0)), "Trūkst", "Vai ir")

Formulas skaidrojums:

  1. Funkcija MATCH tabulas masīva sarakstā meklē šūnas vērtību. Funkcija atgriež vērtību, ja tā tiek atrasta, vai arī atgriež kļūdu #N/A.
  2. Funkcija ISNA uztver kļūdu #N/A un atgriež TRUE, ja pastāv #N/A kļūda vai arī tiek atgriezta FALSE.
  3. Funkcija IF atgriež vērtību "Vai ir" kā vērtība, ja tā ir FALSE, un "trūkst" kā vērtību, ja tā ir TRUE.

Kā redzat no iepriekš minētā momentuzņēmuma. Formula atgriež atbilstības ID 807 un 953 "Vai ir", bet nesaskaņotajam ID 900 atgriež "Trūkst".

Iepriekš tika izskaidroti 3 piemēri, lai Excel sarakstā atrastu trūkstošās vērtības. Visas trīs formulas darbojas labi, taču ir daži punkti, kas jāmeklē.

Piezīmes:

  1. Funkcija VLOOKUP tabulas masīvā neskatās pa kreisi.
  2. Funkcija COUNTIF atbalsta aizstājējzīmes ( * , ? ), kas palīdz iegūt vērtības, kurās ir frāzes.
  3. Neciparu vērtības jānorāda pēdiņās ("vērtība") vai izmantojiet šūnu_referenci…
  4. Skatiet visu sarakstu, jo funkcija atgriež vērtības visur, kur tas atbilst.
  5. Funkcijas masīva argumentu var norādīt kā šūnu atsauci vai nosauktos diapazonus.
  6. Jūs varat pielāgot šīs formulas, ja nepieciešams, izmantojot citu Excel funkciju.
  7. Funkcija atgriež vērtību summu, kas atbilst visiem nosacījumiem.

Ceru, ka sapratāt, kā programmā Excel atrast trūkstošās vērtības. Izpētiet citus rakstus par Excel funkciju formulām šeit. Lūdzu, nekautrējieties izteikt savu jautājumu vai atsauksmes par iepriekš minēto rakstu. Mēs jums palīdzēsim.

INDEX-MATCH programmā Excel

VLOOKUP vairākas vērtības

VLOOKUP ar Dynamic Col Index

Daļēja atbilstība funkcijai VLOOKUP

Vlookup pēc datuma programmā Excel

17 lietas par Excel VLOOKUP

Populāri raksti

50 Excel saīsne produktivitātes paaugstināšanai

Rediģējiet nolaižamo sarakstu

Absolūta atsauce programmā Excel

Ja ar nosacītu formatējumu

Ja ar aizstājējzīmēm

Vlookup pēc datuma