Note: The other languages of the website are Google-translated. Back to English

Dewch o hyd i werthoedd coll

Mae yna achosion pan fydd angen i chi gymharu dwy restr i wirio a yw gwerth rhestr A yn bodoli yn rhestr B yn Excel. Er enghraifft, mae gennych restr o gynhyrchion, ac rydych chi am wirio a yw'r cynhyrchion yn eich rhestr yn bodoli yn y rhestr cynnyrch a ddarperir gan eich cyflenwr. I gyflawni'r dasg hon, yma gwnaethom restru tair ffordd isod, croeso i chi ddewis yr un rydych chi'n ei hoffi.

darganfyddwch werthoedd coll 1

Dewch o hyd i werthoedd coll gyda MATCH, ISNA ac IF
Dewch o hyd i werthoedd coll gyda VLOOKUP, ISNA ac IF
Dewch o hyd i werthoedd coll gyda COUNTIF ac IF


Dewch o hyd i werthoedd coll gyda MATCH, ISNA ac IF

I ffeindio mas os yw'r holl gynhyrchion ar eich rhestr yn bodoli ar restr eich cyflenwr fel y dangosir yn y screenshot uchod, gallwch yn gyntaf ddefnyddio'r swyddogaeth MATCH i adfer safle cynnyrch o'ch rhestr (gwerth rhestr A) yn rhestr y cyflenwr (rhestr B). Bydd MATCH yn dychwelyd y gwall # Amherthnasol pan na cheir hyd i gynnyrch. Yna gallwch chi fwydo'r canlyniad i ISNA i drosi'r gwallau # Amherthnasol i WIR, sy'n golygu bod y cynhyrchion hynny ar goll. Yna bydd y swyddogaeth IF yn dychwelyd y canlyniad rydych chi'n ei ddisgwyl.

Cystrawen generig

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

√ Nodyn: Gallwch chi newid y "Ar goll", "Wedi'i ddarganfod" i unrhyw werthoedd yn ôl yr angen.

  • Gwerth_edrych: Y gwerth MATCH a ddefnyddir i adfer ei safle os yw'n bodoli ynddo chwilio_ystod neu # Amherthnasol os na. Yma yn cyfeirio at y cynhyrchion yn eich rhestr.
  • ystod_chwilio: Yr ystod o gelloedd i'w cymharu â'r lookup_value. Yma yn cyfeirio at restr cynnyrch y cyflenwr.

I ffeindio mas os yw'r holl gynhyrchion ar eich rhestr yn bodoli ar restr eich cyflenwr, copïwch neu nodwch y fformiwla isod yn y gell H6, a gwasgwch Rhowch i gael y canlyniad:

= OS (ISNA (MATCH (30002,$ B $ 6: $ B $ 10, 0)), "Ar goll", "Wedi'i ddarganfod")

Neu, defnyddiwch gyfeirnod cell i wneud y fformiwla'n ddeinamig:

= OS (ISNA (MATCH (G6,$ B $ 6: $ B $ 10, 0)), "Ar goll", "Wedi'i ddarganfod")

√ Nodyn: Mae'r arwyddion doler ($) uchod yn nodi cyfeiriadau absoliwt, sy'n golygu'r chwilio_ystod ni fydd y fformiwla yn newid pan fyddwch chi'n symud neu'n copïo'r fformiwla i gelloedd eraill. Fodd bynnag, nid oes unrhyw arwyddion doler wedi'u hychwanegu at y lookup_value gan eich bod am iddo fod yn ddeinamig. Ar ôl mynd i mewn i'r fformiwla, llusgwch y handlen llenwi i lawr i gymhwyso'r fformiwla i'r celloedd isod.

darganfyddwch werthoedd coll 2

Esboniad o'r fformiwla

Yma rydym yn defnyddio'r fformiwla isod fel enghraifft:

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

  • MATCH (G8, $ B $ 6: $ B $ 10,0): Y match_type 0 yn gorfodi swyddogaeth MATCH i ddychwelyd gwerth rhifol sy'n nodi lleoliad y gêm gyntaf o 3004, y gwerth yn y gell G8, yn yr arae $ B $ 6: $ B $ 10. Fodd bynnag, yn yr achos hwn, ni allai MATCH ddod o hyd i'r gwerth yn yr ystod edrych, felly bydd yn dychwelyd y # N / A gwall.
  • ISNA (MATCH (G8, $ B $ 6: $ B $ 10,0)) = ISNA (# N / A): Mae ISNA yn gweithio i ddarganfod a yw gwerth yn wall “# Amherthnasol” ai peidio. Os oes, bydd y swyddogaeth yn dychwelyd TURE; Os yw'r gwerth yn unrhyw beth ond gwall “# Amherthnasol”, bydd yn dychwelyd yn GAU. Felly, bydd y fformiwla ISNA hon yn dychwelyd SUT.
  • OS (ISNA (MATCH (G8, $ B $ 6: $ B $ 10,0)), "Ar goll", "Wedi'i ddarganfod") = OS (TRUE, "Ar goll", "Wedi'i ddarganfod"): Bydd y swyddogaeth IF yn dychwelyd Ar goll os yw'r gymhariaeth a wneir gan ISNA a MATCH yn WIR, fel arall bydd yn dychwelyd Wedi'i ddarganfod. Felly, bydd y fformiwla'n dychwelyd Ar goll.

Dewch o hyd i werthoedd coll gyda VLOOKUP, ISNA ac IF

I ddarganfod a yw'r holl gynhyrchion yn eich rhestr yn bodoli yn rhestr eich cyflenwr, gallwch ddisodli'r swyddogaeth MATCH uchod gyda VLOOKUP, gan ei fod yn gweithio yr un fath â MATCH y bydd yn dychwelyd y gwall # Amherthnasol os nad yw'r gwerth yn bodoli yn rhestr arall, neu dywedwn ei bod ar goll.

Cystrawen generig

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

√ Nodyn: Gallwch chi newid y "Ar goll", "Wedi'i ddarganfod" i unrhyw werthoedd yn ôl yr angen.

  • Gwerth_edrych: Y gwerth VLOOKUP a ddefnyddir i adfer ei safle os yw'n bodoli ynddo chwilio_ystod neu # Amherthnasol os na. Yma yn cyfeirio at y cynhyrchion yn eich rhestr.
  • ystod_chwilio: Yr ystod o gelloedd i'w cymharu â'r lookup_value. Yma yn cyfeirio at restr cynnyrch y cyflenwr.

I ddarganfod a yw'r holl gynhyrchion yn eich rhestr yn bodoli yn rhestr eich cyflenwr, copïwch neu nodwch y fformiwla isod yn y gell H6, a gwasgwch Rhowch i gael y canlyniad:

= OS (ISNA (VLOOKUP (30002,$ B $ 6: $ B $ 10, 1, ANWIR)), "Ar goll", "Wedi'i ddarganfod")

Neu, defnyddiwch gyfeirnod cell i wneud y fformiwla'n ddeinamig:

= OS (ISNA (VLOOKUP (G6,$ B $ 6: $ B $ 10, 1, ANWIR)), "Ar goll", "Wedi'i ddarganfod")

√ Nodyn: Mae'r arwyddion doler ($) uchod yn nodi cyfeiriadau absoliwt, sy'n golygu'r chwilio_ystod ni fydd y fformiwla yn newid pan fyddwch chi'n symud neu'n copïo'r fformiwla i gelloedd eraill. Fodd bynnag, nid oes unrhyw arwyddion doler wedi'u hychwanegu at y lookup_value gan eich bod am iddo fod yn ddeinamig. Ar ôl mynd i mewn i'r fformiwla, llusgwch y handlen llenwi i lawr i gymhwyso'r fformiwla i'r celloedd isod.

darganfyddwch werthoedd coll 3

Esboniad o'r fformiwla

Yma rydym yn defnyddio'r fformiwla isod fel enghraifft:

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

  • VLOOKUP (G8, $ B $ 6: $ B $ 10,1, GAU): Yr ystod_lookup Anghywir yn gorfodi swyddogaeth VLOOKUP i edrych a dychwelyd y gwerth sy'n cyfateb yn union 3004, y gwerth yn y gell G8. Os yw'r lookup_value 3004 yn bodoli yn y 1colofn af yr arae $ B $ 6: $ B $ 10, bydd y VLOOKUP yn dychwelyd y gwerth hwnnw; Fel arall, bydd yn dychwelyd y gwerth gwall # N / A. Yma, nid yw 3004 yn bodoli yn yr arae, felly, y canlyniad fyddai # N / A.
  • ISNA (VLOOKUP (G8, $ B $ 6: $ B $ 10,1, GAU)) = ISNA (# N / A): Mae ISNA yn gweithio i ddarganfod a yw gwerth yn wall “# Amherthnasol” ai peidio. Os oes, bydd y swyddogaeth yn dychwelyd TURE; Os yw'r gwerth yn unrhyw beth ond gwall “# Amherthnasol”, bydd yn dychwelyd yn GAU. Felly, bydd y fformiwla ISNA hon yn dychwelyd SUT.
  • OS (ISNA (VLOOKUP (G8, $ B $ 6: $ B $ 10,1, GAU)), "Ar goll", "Wedi'i ddarganfod") = OS (TRUE, "Ar goll", "Wedi'i ddarganfod"): Bydd y swyddogaeth IF yn dychwelyd Ar goll os yw'r gymhariaeth a wnaed gan ISNA a VLOOKUP yn WIR, fel arall bydd yn dychwelyd Wedi'i ddarganfod. Felly, bydd y fformiwla'n dychwelyd Ar goll.

Dewch o hyd i werthoedd coll gyda COUNTIF ac IF

I ddarganfod a yw'r holl gynhyrchion yn eich rhestr yn bodoli yn rhestr eich cyflenwr, gallwch ddefnyddio fformiwla symlach gyda'r swyddogaethau COUNTIF ac IF. Mae'r fformiwla'n manteisio ar y ffaith y bydd Excel yn gwerthuso unrhyw rif ac eithrio sero (0) fel GWIR. Felly os oes gwerth yn bodoli mewn rhestr arall, bydd swyddogaeth COUNTIF yn dychwelyd cyfrif ei ddigwyddiadau yn y rhestr honno, yna bydd OS yn cymryd y rhif fel TURE; Os nad yw'r gwerth yn bodoli yn y rhestr, bydd swyddogaeth COUNTIF yn dychwelyd 0, a bydd IF yn ei gymryd fel GAU.

Cystrawen generig

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

√ Nodyn: Gallwch chi newid y "Wedi'i ddarganfod", "Ar goll" i unrhyw werthoedd yn ôl yr angen.

  • ystod_chwilio: Yr ystod o gelloedd i'w cymharu â'r lookup_value. Yma yn cyfeirio at restr cynnyrch y cyflenwr.
  • Gwerth_edrych: Y gwerth a ddefnyddiodd COUNTIF i ddychwelyd nifer ei ddigwyddiadau yn chwilio_ystod. Yma yn cyfeirio at y cynhyrchion yn eich rhestr.

I ddarganfod a yw'r holl gynhyrchion yn eich rhestr yn bodoli yn rhestr eich cyflenwr, copïwch neu nodwch y fformiwla isod yn y gell H6, a gwasgwch Rhowch i gael y canlyniad:

= OS (COUNTIF ($ B $ 6: $ B $ 10,30002), "Wedi dod o hyd", "Ar goll")

Neu, defnyddiwch gyfeirnod cell i wneud y fformiwla'n ddeinamig:

= OS (COUNTIF ($ B $ 6: $ B $ 10,G6), "Wedi dod o hyd", "Ar goll")

√ Nodyn: Mae'r arwyddion doler ($) uchod yn nodi cyfeiriadau absoliwt, sy'n golygu'r chwilio_ystod ni fydd y fformiwla yn newid pan fyddwch chi'n symud neu'n copïo'r fformiwla i gelloedd eraill. Fodd bynnag, nid oes unrhyw arwyddion doler wedi'u hychwanegu at y lookup_value gan eich bod am iddo fod yn ddeinamig. Ar ôl mynd i mewn i'r fformiwla, llusgwch y handlen llenwi i lawr i gymhwyso'r fformiwla i'r celloedd isod.

darganfyddwch werthoedd coll 4

Esboniad o'r fformiwla

Yma rydym yn defnyddio'r fformiwla isod fel enghraifft:

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

  • COUNTIF ($ B $ 6: $ B $ 10, G8): Mae swyddogaeth COUNTIF yn cyfrif sawl gwaith 3004, y gwerth yn y gell G8, yn ymddangos yn yr arae $ B $ 6: $ B $ 10. Yn ôl pob tebyg, nid yw 3004 yn bodoli yn yr arae, felly, y canlyniad fyddai 0.
  • OS (COUNTIF ($ B $ 6: $ B $ 10, G8), "Wedi dod o hyd", "Ar goll") = OS (0, "Wedi dod o hyd", "Ar goll"): Bydd y swyddogaeth IF yn gwerthuso 0 fel GAU. Felly, bydd y fformiwla'n dychwelyd Ar goll, y gwerth i'w ddychwelyd pan fydd yr ychwanegiad cyntaf yn gwerthuso i GAU.

Swyddogaethau cysylltiedig

Swyddogaeth Excel IF

Mae'r swyddogaeth IF yn un o'r swyddogaethau symlaf a mwyaf defnyddiol yn llyfr gwaith Excel. Mae'n perfformio prawf rhesymegol syml sy'n dibynnu ar ganlyniad y gymhariaeth, ac mae'n dychwelyd un gwerth os yw'r canlyniad yn WIR, neu werth arall os yw'r canlyniad yn GAU.

Swyddogaeth Excel MATCH

Mae swyddogaeth Excel MATCH yn chwilio am werth penodol mewn ystod o gelloedd, ac yn dychwelyd safle cymharol y gwerth.

Swyddogaeth Excel VLOOKUP

Mae swyddogaeth Excel VLOOKUP yn chwilio am werth trwy baru ar golofn gyntaf tabl ac yn dychwelyd y gwerth cyfatebol o golofn benodol yn yr un rhes.

Swyddogaeth Excel COUNTIF

Mae swyddogaeth COUNTIF yn swyddogaeth ystadegol yn Excel a ddefnyddir i gyfrif nifer y celloedd sy'n cwrdd â maen prawf. Mae'n cefnogi gweithredwyr rhesymegol (<>, =,>, a <), a'r cardiau gwyllt (? A *) ar gyfer paru rhannol.


Fformiwlâu Cysylltiedig

Chwiliwch am werth sy'n cynnwys testun penodol gyda chardiau gwyllt

I ddod o hyd i'r ornest gyntaf sy'n cynnwys llinyn testun penodol mewn ystod yn Excel, gallwch ddefnyddio fformiwla MYNEGAI a MATCH gyda nodau cerdyn gwyllt - y seren (*) a'r marc cwestiwn (?).

Gêm rannol gyda VLOOKUP

Mae yna adegau pan fydd angen Excel arnoch i adfer data yn seiliedig ar wybodaeth rannol. I ddatrys y broblem, gallwch ddefnyddio fformiwla VLOOKUP ynghyd â chymeriadau cardiau gwyllt - y seren (*) a'r marc cwestiwn (?).

Cydweddiad bras ag MYNEGAI a MATCH

Mae yna adegau pan fydd angen i ni ddod o hyd i gemau cyfatebol yn Excel i werthuso perfformiad gweithwyr, graddio sgoriau myfyrwyr, cyfrifo postio yn seiliedig ar bwysau, ac ati. Yn y tiwtorial hwn, byddwn yn siarad am sut i ddefnyddio'r swyddogaethau INDEX a MATCH i adfer y canlyniadau sydd eu hangen arnom.

Edrych ar y gwerth cyfatebol agosaf â meini prawf lluosog

Mewn rhai achosion, efallai y bydd angen i chi edrych ar y gwerth cyfatebol agosaf neu fras yn seiliedig ar fwy nag un maen prawf. Gyda'r cyfuniad o swyddogaethau MYNEGAI, MATCH ac IF, gallwch chi ei gyflawni'n gyflym yn Excel.


Yr Offer Cynhyrchedd Swyddfa Gorau

Kutools for Excel - Yn Eich Helpu i Sefyll Allan O Dyrfa

Hoffech chi gwblhau eich gwaith dyddiol yn gyflym ac yn berffaith? Mae Kutools ar gyfer Excel yn dod â 300 o nodweddion uwch pwerus (Cyfuno llyfrau gwaith, swm yn ôl lliw, cynnwys cell hollti, dyddiad trosi, ac yn y blaen ...) ac arbed amser 80% i chi.

  • Wedi'i gynllunio ar gyfer 1500 o senarios gwaith, yn eich helpu i ddatrys problemau Excel 80%.
  • Gostyngwch filoedd o gliciau bysellfwrdd a llygoden bob dydd, lleddfu'ch llygaid a'ch dwylo blinedig.
  • Dewch yn arbenigwr Excel mewn 3 munud. Nid oes angen cofio unrhyw fformiwlâu poenus a chodau VBA mwyach.
  • Treial am ddim diderfyn 30 diwrnod. Gwarant arian yn ôl 60 diwrnod. Uwchraddio a chefnogi am ddim am 2 flynedd.
Rhuban o Excel (gyda Kutools ar gyfer Excel wedi'i osod)

Tab Swyddfa - Galluogi Darllen a Golygu Tabbed yn Microsoft Office (cynnwys Excel)

  • Un eiliad i newid rhwng dwsinau o ddogfennau agored!
  • Gostyngwch gannoedd o gliciau llygoden i chi bob dydd, ffarweliwch â llaw llygoden.
  • Yn cynyddu eich cynhyrchiant 50% wrth wylio a golygu sawl dogfen.
  • Yn Dod â Thabiau Effeithlon i'r Swyddfa (gan gynnwys Excel), Yn union fel Chrome, Firefox, a New Internet Explorer.
Ergyd Sgrin o Excel (gyda Office Tab wedi'i osod)
sylwadau (0)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Nid oes unrhyw sylwadau wedi'u postio yma eto
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir