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.
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.
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.
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.
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
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.
Mae swyddogaeth Excel MATCH yn chwilio am werth penodol mewn ystod o gelloedd, ac yn dychwelyd safle cymharol y gwerth.
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.
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 (?).
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
Kutools ar gyfer Excel Mae ganddo Dros 300 o Nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...
Tab Office - Galluogi Darllen a Golygu Tabiau yn Microsoft Office (gan gynnwys 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), Just Like Chrome, Edge a Firefox.
