Preskoči na glavno vsebino

Poiščite manjkajoče vrednosti

Obstajajo primeri, ko morate primerjati dva seznama, da preverite, ali vrednost seznama A obstaja na seznamu B v Excelu. Na primer, imate seznam izdelkov in želite preveriti, ali izdelki na vašem seznamu obstajajo na seznamu izdelkov, ki ga je zagotovil vaš dobavitelj. Za izpolnitev te naloge smo spodaj našteli tri načine, izberite tistega, ki vam je všeč.

poišči manjkajoče vrednosti 1

Poiščite manjkajoče vrednosti z MATCH, ISNA in IF
Poiščite manjkajoče vrednosti z VLOOKUP, ISNA in IF
Poiščite manjkajoče vrednosti s COUNTIF in IF


Poiščite manjkajoče vrednosti z MATCH, ISNA in IF

Izvedeti če so vsi izdelki na vašem seznamu na seznamu vašega dobavitelja kot je prikazano na zgornjem posnetku zaslona, ​​lahko najprej uporabite funkcijo MATCH, da pridobite položaj izdelka na vašem seznamu (vrednost seznama A) na seznamu dobaviteljev (seznam B). MATCH bo vrnil napako #N/A, če izdelka ni mogoče najti. Nato lahko rezultat pošljete ISNA, da pretvori napake #N/A v TRUE, kar pomeni, da ti izdelki manjkajo. Funkcija IF bo nato vrnila rezultat, ki ga pričakujete.

Splošna skladnja

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Opomba: "Manjkaj", "Najdeno" lahko spremenite v poljubne vrednosti.

  • iskalna_vrednost: Vrednost MATCH, ki se uporablja za pridobivanje njenega položaja, če obstaja v iskalni_obseg ali #N/A napaka, če ne. Tukaj se nanaša na izdelke na vašem seznamu.
  • iskalni_obseg: Razpon celic za primerjavo z lookup_value. Tukaj se nanaša na seznam izdelkov dobavitelja.

Izvedeti če so vsi izdelki na vašem seznamu na seznamu vašega dobavitelja, kopirajte ali vnesite spodnjo formulo v celico H6 in pritisnite Vnesite da dobite rezultat:

=ČE(ISNA(UJEMA(30002,$ 6 $: $ 10 $,0)),"Manjka","Najdeno")

Ali pa uporabite sklic na celico, da naredite formulo dinamično:

=ČE(ISNA(UJEMA(G6,$ 6 $: $ 10 $,0)),"Manjka","Najdeno")

√ Opomba: Zgornji znaki za dolar ($) označujejo absolutne reference, kar pomeni iskalni_obseg v formuli se ne bo spremenilo, ko jo premaknete ali kopirate v druge celice. Vendar pa ni dodanih znakov dolarja lookup_value ker želite, da je dinamičen. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah.

poišči manjkajoče vrednosti 2

Pojasnilo formule

Tukaj za primer uporabimo spodnjo formulo:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

  • MACH(G8,$B$6:$B$10,0): Vrsta ujemanja 0 prisili funkcijo MATCH, da vrne številsko vrednost, ki označuje položaj prvega ujemanja 3004, vrednost v celici G8 v matriki $ 6 $: $ 10 $. Vendar v tem primeru MATCH ni mogel najti vrednosti v iskalnem nizu, zato bo vrnil # N / A napaka.
  • ISNA(MACH(G8,$B$6:$B$10,0)) = ISNA(# N / A): ISNA deluje tako, da ugotovi, ali je vrednost napaka »#N/A« ali ne. Če je odgovor pritrdilen, bo funkcija vrnila TURE; Če je vrednost kaj drugega kot napaka »#N/A«, bo vrnila FALSE. Torej se bo ta formula ISNA vrnila TURE.
  • ČE (ISNA(MACH(G8,$B$6:$B$10,0)),"Manjka","Najdeno") = IF(TRUE,"Manjka","Najdeno"): Funkcija IF bo vrnila manjkajoče, če je primerjava, ki sta jo opravila ISNA in MATCH, TRUE, v nasprotnem primeru bo vrnila Najdeno. Torej, formula se bo vrnila Manjka.

Poiščite manjkajoče vrednosti z VLOOKUP, ISNA in IF

Če želite izvedeti, ali vsi izdelki na vašem seznamu obstajajo na seznamu vašega dobavitelja, lahko zgornjo funkcijo MATCH zamenjate z VLOOKUP, saj deluje enako kot MATCH in vrne napako #N/A, če vrednost ne obstaja v drug seznam ali pa rečemo, da manjka.

Splošna skladnja

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Opomba: "Manjkaj", "Najdeno" lahko spremenite v poljubne vrednosti.

  • iskalna_vrednost: Vrednost VLOOKUP, uporabljena za pridobivanje njenega položaja, če obstaja v iskalni_obseg ali #N/A napaka, če ne. Tukaj se nanaša na izdelke na vašem seznamu.
  • iskalni_obseg: Razpon celic za primerjavo z lookup_value. Tukaj se nanaša na seznam izdelkov dobavitelja.

Če želite izvedeti, ali so vsi izdelki na vašem seznamu na seznamu vašega dobavitelja, kopirajte ali vnesite spodnjo formulo v celico H6 in pritisnite Vnesite da dobite rezultat:

=ČE(ISNA(VLOOKUP(30002,$ 6 $: $ 10 $,1,FALSE)),"Manjka","Najdeno")

Ali pa uporabite sklic na celico, da naredite formulo dinamično:

=ČE(ISNA(VLOOKUP(G6,$ 6 $: $ 10 $,1,FALSE)),"Manjka","Najdeno")

√ Opomba: Zgornji znaki za dolar ($) označujejo absolutne reference, kar pomeni iskalni_obseg v formuli se ne bo spremenilo, ko jo premaknete ali kopirate v druge celice. Vendar pa ni dodanih znakov dolarja lookup_value ker želite, da je dinamičen. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah.

poišči manjkajoče vrednosti 3

Pojasnilo formule

Tukaj za primer uporabimo spodnjo formulo:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

  • VLOOKUP(G8,$B$6:$B$10,1,FALSE): Range_lookup FALSE prisili funkcijo VLOOKUP, da poišče in vrne vrednost, ki se natančno ujema 3004, vrednost v celici G8. Če je iskalna vrednost 3004 obstaja v 1st stolpec matrike $ 6 $: $ 10 $, bo VLOOKUP vrnil to vrednost; V nasprotnem primeru bo vrnil vrednost napake #N/A. Tukaj 3004 ne obstaja v matriki, zato bi bil rezultat # N / A.
  • ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)) = ISNA(# N / A): ISNA deluje tako, da ugotovi, ali je vrednost napaka »#N/A« ali ne. Če je odgovor pritrdilen, bo funkcija vrnila TURE; Če je vrednost kaj drugega kot napaka »#N/A«, bo vrnila FALSE. Torej se bo ta formula ISNA vrnila TURE.
  • ČE (ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Manjka","Najdeno") = IF(TRUE,"Manjka","Najdeno"): Funkcija IF bo vrnila Missing, če je primerjava, ki sta jo opravila ISNA in VLOOKUP, TRUE, v nasprotnem primeru bo vrnila Najdeno. Torej, formula se bo vrnila Manjka.

Poiščite manjkajoče vrednosti s COUNTIF in IF

Če želite izvedeti, ali so vsi izdelki na vašem seznamu na seznamu vašega dobavitelja, lahko uporabite preprostejšo formulo s funkcijama COUNTIF in IF. Formula izkorišča dejstvo, da bo Excel katero koli število razen nič (0) ocenil kot TRUE. Torej, če vrednost obstaja na drugem seznamu, bo funkcija COUNTIF vrnila število njenih pojavov na tem seznamu, potem bo IF število vzelo kot TURE; Če vrednost ne obstaja na seznamu, bo funkcija COUNTIF vrnila 0, IF pa jo bo vzela kot FALSE.

Splošna skladnja

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Opomba: "Najdeno", "Manjka" lahko spremenite v poljubne vrednosti.

  • iskalni_obseg: Razpon celic za primerjavo z lookup_value. Tukaj se nanaša na seznam izdelkov dobavitelja.
  • iskalna_vrednost: Vrednost COUNTIF, uporabljena za vrnitev števila njegovih pojavov iskalni_obseg. Tukaj se nanaša na izdelke na vašem seznamu.

Če želite izvedeti, ali so vsi izdelki na vašem seznamu na seznamu vašega dobavitelja, kopirajte ali vnesite spodnjo formulo v celico H6 in pritisnite Vnesite da dobite rezultat:

=ČE(ŠTEVIČ($ 6 $: $ 10 $,30002),"Najdeno","Pogrešano")

Ali pa uporabite sklic na celico, da naredite formulo dinamično:

=ČE(ŠTEVIČ($ 6 $: $ 10 $,G6),"Najdeno","Pogrešano")

√ Opomba: Zgornji znaki za dolar ($) označujejo absolutne reference, kar pomeni iskalni_obseg v formuli se ne bo spremenilo, ko jo premaknete ali kopirate v druge celice. Vendar pa ni dodanih znakov dolarja lookup_value ker želite, da je dinamičen. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah.

poišči manjkajoče vrednosti 4

Pojasnilo formule

Tukaj za primer uporabimo spodnjo formulo:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

  • ŠTEVI ($B$6:$B$10,G8): Funkcija COUNTIF šteje, kolikokrat 3004, vrednost v celici G8, se prikaže v matriki $ 6 $: $ 10 $. Očitno 3004 ne obstaja v matriki, zato bi bil rezultat 0.
  • ČE (ŠTEVI ($B$6:$B$10,G8),"Najdeno","Manjka") = IF(0,"Najdeno","Manjka"): Funkcija IF bo 0 ocenila kot FALSE. Torej, formula se bo vrnila Manjka, vrednost, ki jo je treba vrniti, ko je prvo povečanje ocenjeno na FALSE.

Sorodne funkcije

Excel IF funkcija

Funkcija IF je ena najpreprostejših in najbolj uporabnih funkcij v Excelovem delovnem zvezku. Izvede preprost logični test, ki glede na rezultat primerjave vrne eno vrednost, če je rezultat TRUE, ali drugo vrednost, če je rezultat FALSE.

Excel MATCH funkcija

Funkcija Excel MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj vrednosti.

Excel VLOOKUP funkcija

Funkcija Excel VLOOKUP išče vrednost z ujemanjem v prvem stolpcu tabele in vrne ustrezno vrednost iz določenega stolpca v isti vrstici.

Funkcija Excel COUNTIF

Funkcija COUNTIF je statistična funkcija v Excelu, ki se uporablja za štetje števila celic, ki izpolnjujejo merilo. Podpira logične operatorje (<>, =, > in <) ter nadomestne znake (? in *) za delno ujemanje.


Sorodne formule

Poiščite vrednost, ki vsebuje določeno besedilo z nadomestnimi znaki

Če želite poiskati prvo ujemanje, ki vsebuje določen besedilni niz v obsegu v Excelu, lahko uporabite formulo INDEX in MATCH z nadomestnimi znaki – zvezdico (*) in vprašajem (?).

Delno ujemanje z VLOOKUP

Včasih potrebujete Excel za pridobivanje podatkov na podlagi delnih informacij. Za rešitev težave lahko uporabite formulo VLOOKUP skupaj z nadomestnimi znaki - zvezdico (*) in vprašajem (?).

Približno ujemanje z INDEX in MATCH

Včasih moramo v Excelu najti približna ujemanja, da ocenimo uspešnost zaposlenih, ocenimo ocene učencev, izračunamo poštnino glede na težo itd. V tej vadnici se bomo pogovarjali o tem, kako s funkcijami INDEX in MATCH pridobiti rezultate, ki jih potrebujemo.

Poiščite vrednost najbližjega ujemanja z več merili

V nekaterih primerih boste morda morali poiskati najbližjo ali približno vrednost ujemanja na podlagi več meril. S kombinacijo funkcij INDEX, MATCH in IF lahko to hitro opravite v Excelu.


Najboljša orodja za pisarniško produktivnost

Kutools za Excel - vam pomaga izstopati iz množice

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike  |  Izbriši prazne vrstice  |  Združite stolpce ali celice brez izgube podatkov  |  Krog brez formule ...
Super VLookup: Več meril  |  Več vrednosti  |  Na več listih  |  Nejasno iskanje...
Adv. Spustni seznam: Preprost spustni seznam  |  Odvisni spustni seznam  |  Večkrat izberite spustni seznam...
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  Primerjaj stolpce z Izberite Enake in različne celice ...
Predstavljene funkcije: Mrežni fokus  |  Pogled oblikovanja  |  Velika vrstica formule  |  Upravitelj delovnih zvezkov in listov | Knjižnica virov (Samodejno besedilo)  |  Izbirnik datuma  |  Združite delovne liste  |  Šifriranje/dešifriranje celic  |  Pošljite e-pošto po seznamu  |  Super filter  |  Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake ...)  |  50 + Graf Vrste (Gantt Chart ...)  |  40+ Praktično Formule (Izračunajte starost glede na rojstni dan ...)  |  19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti ...)  |  12 Pretvorba Orodja (Številke v besede, Pretvorba valut ...)  |  7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeli Excelove celice ...)  |  ... in več

Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...

Opis


Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)

  • Eno sekundo za preklop med desetinami odprtih dokumentov!
  • Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
  • Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
  • Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations