30 Excel imagwira ntchito m'masiku 30: MATCH

Dzulo pa marathon 30 Excel imagwira ntchito m'masiku 30 tinapeza zingwe zolembera pogwiritsa ntchito ntchitoyi FUFUZANI (SEARCH) komanso amagwiritsidwa ntchito IFERROR (IFERROR) ndi ISNUMBER (ISNUMBER) nthawi yomwe ntchitoyi ipanga cholakwika.

Pa tsiku la 19 la mpikisano wathu wothamanga, tidzaphunzira za ntchitoyi GANIZANI (FUFUZANI). Imayang'ana mtengo wotsatizana ndipo, ngati mtengo wapezeka, umabwezanso malo ake.

Chifukwa chake, tiyeni titembenuzire ku zambiri zofotokozera za ntchitoyi GANIZANI (MATCH) ndipo yang'anani zitsanzo zingapo. Ngati muli ndi zitsanzo kapena njira zogwirira ntchito ndi ntchitoyi, chonde gawanani nawo mu ndemanga.

Ntchito 19: MATCH

ntchito GANIZANI (MATCH) imabweretsanso malo amtengo mugulu, kapena cholakwika #AT (#N/A) ngati sichinapezeke. Mndandanda ukhoza kusankhidwa kapena kusankhidwa. Ntchito GANIZANI (MATCH) sizovuta.

Kodi mungagwiritse ntchito bwanji MATCH?

ntchito GANIZANI (MATCH) imabweretsanso malo a chinthu mugulu, ndipo zotsatirazi zitha kugwiritsidwa ntchito ndi ntchito zina monga INDEX (INDEX) kapena VLOOKUP (VPR). Mwachitsanzo:

  • Pezani malo a chinthu pamndandanda wosasankhidwa.
  • Gwiritsani ntchito ndi SANKHA (SAKHANI) kuti mutembenuzire ntchito ya ophunzira kukhala magiredi a zilembo.
  • Gwiritsani ntchito ndi VLOOKUP (VLOOKUP) posankha magawo osinthika.
  • Gwiritsani ntchito ndi INDEX (INDEX) kuti mupeze mtengo wapafupi.

Syntax MATCH

ntchito GANIZANI (MATCH) ali ndi mawu awa:

MATCH(lookup_value,lookup_array,[match_type])

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

  • kumachika (lookup_value) - Itha kukhala mawu, nambala, kapena boolean.
  • lookup_array (lookup_array) - mndandanda kapena mndandanda (maselo oyandikana nawo pamzere womwewo kapena mzere womwewo).
  • match_mtundu (match_type) ikhoza kutenga zinthu zitatu: -1, 0 or 1. Ngati mkangano wasiyidwa, ndi wofanana ndi 1.

Traps MATCH (MATCH)

ntchito GANIZANI (MATCH) imabweretsanso malo omwe apezeka, koma osati mtengo wake. Ngati mukufuna kubweza mtengo, gwiritsani ntchito GANIZANI (MATCH) pamodzi ndi ntchito INDEX (INDEX).

Chitsanzo 1: Kupeza chinthu pamndandanda wosasankhidwa

Kwa mndandanda wosasankhidwa, mutha kugwiritsa ntchito 0 monga mtengo wotsutsana match_mtundu (match_type) kuti mufufuze zofananira ndendende. Ngati mukufuna kupeza chofanana ndendende ndi zingwe, mutha kugwiritsa ntchito zilembo zakutchire mumtengo wofufuzira.

Muchitsanzo chotsatirachi, kuti tipeze malo a mwezi pamndandanda, tingalembe dzina la mweziwo, wathunthu kapena mbali yake, pogwiritsa ntchito makadi olusa.

=MATCH(D2,B3:B7,0)

=ПОИСКПОЗ(D2;B3:B7;0)

30 Excel imagwira ntchito m'masiku 30: MATCH

Monga mkangano lookup_array (lookup_array) mutha kugwiritsa ntchito zosinthika zingapo. Muchitsanzo chotsatirachi, mwezi womwe ukufunidwa umalowetsedwa mu cell D5, ndipo mayina a miyezi amalowetsedwa ngati mtsutso wachiwiri pa ntchitoyi. GANIZANI (MATCH) ngati mndandanda wazosintha. Ngati mutalowa mwezi wotsatira mu selo D5, mwachitsanzo, Oct (October), ndiye zotsatira za ntchitoyi zidzakhala #AT (#N / A).

=MATCH(D5,{"Jan","Feb","Mar"},0)

=ПОИСКПОЗ(D5;{"Jan";"Feb";"Mar"};0)

30 Excel imagwira ntchito m'masiku 30: MATCH

Chitsanzo 2: Sinthani magiredi a ophunzira kuchoka paperesenti kupita ku zilembo

Mutha kusintha magiredi a ophunzira kukhala kalembedwe ka zilembo pogwiritsa ntchito ntchitoyi GANIZANI (MATCH) monga momwe mudachitira ndi VLOOKUP (VPR). Mu chitsanzo ichi, ntchitoyo imagwiritsidwa ntchito molumikizana ndi SANKHA (KUSANKHA), komwe kumabweza kuyerekeza komwe tikufuna. Kukangana match_mtundu (match_type) yakhazikitsidwa yofanana ndi -1, chifukwa zigoli zomwe zili patebulo zimasanjidwa motsika.

Pamene mkangano match_mtundu (match_type) ndi -1, chotsatira chake ndi mtengo wochepa kwambiri womwe uli wokulirapo kapena wofanana ndi mtengo womwe ukufunidwa. Mu chitsanzo chathu, mtengo wofunidwa ndi 54. Popeza palibe mtengo wotere pamndandanda wa ziwerengero, chinthu chofanana ndi mtengo wa 60 chimabwezeretsedwa. Popeza 60 ali pamalo achinayi pamndandanda, zotsatira za ntchitoyo SANKHA (SANKHAni) kukhala mtengo womwe uli pamalo a 4, mwachitsanzo, selo C6, yomwe ili ndi mphambu D.

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)

=ВЫБОР(ПОИСКПОЗ(B9;B3:B7;-1);C3;C4;C5;C6;C7)

30 Excel imagwira ntchito m'masiku 30: MATCH

Chitsanzo 3: Pangani magawo osinthika a VLOOKUP (VLOOKUP)

Kuti apereke kusinthasintha kwambiri pa ntchitoyi VLOOKUP (VLOOKUP) Mutha kugwiritsa ntchito GANIZANI (MATCH) kuti mupeze nambala yagawo, m'malo molemba molimba mtengo wake mu ntchitoyi. Muchitsanzo chotsatirachi, ogwiritsa ntchito amatha kusankha dera mu cell H1, uwu ndi mtengo womwe akufuna VLOOKUP (VPR). Kenako, akhoza kusankha mwezi mu selo H2, ndi ntchito GANIZANI (MATCH) ibweza nambala yagawo yomwe ikugwirizana ndi mweziwo.

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)

=ВПР(H1;$B$2:$E$5;ПОИСКПОЗ(H2;B1:E1;0);ЛОЖЬ)

30 Excel imagwira ntchito m'masiku 30: MATCH

Chitsanzo 4: Kupeza mtengo wapafupi pogwiritsa ntchito INDEX (INDEX)

ntchito GANIZANI (MATCH) imagwira ntchito bwino kuphatikiza ndi ntchitoyi INDEX (INDEX), yomwe tiyang'ana kwambiri mtsogolomu mu mpikisanowu. Mu chitsanzo ichi, ntchito GANIZANI (MATCH) amagwiritsidwa ntchito kupeza nambala yapafupi ndi yolondola kuchokera pamanambala angapo omwe amaganiziridwa.

  1. ntchito ABS imabweretsanso modulus ya kusiyana pakati pa nambala iliyonse yoganiziridwa ndi yolondola.
  2. ntchito MIN (MIN) imapeza kusiyana kochepa kwambiri.
  3. ntchito GANIZANI (MATCH) amapeza adiresi ya kusiyana kochepa kwambiri pamndandanda wa zosiyana. Ngati pali zikhalidwe zingapo zofananira pamndandanda, woyamba adzabwezedwa.
  4. ntchito INDEX (INDEX) imabweretsanso dzina lolingana ndi malowa kuchokera pamndandanda wa mayina.

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))

=ИНДЕКС(B2:B5;ПОИСКПОЗ(МИН(ABS(C2:C5-F1));ABS(C2:C5-F1);0))

30 Excel imagwira ntchito m'masiku 30: MATCH

Siyani Mumakonda