30 Excel inoshanda mumazuva makumi matatu: MATCH

Nezuro mumarathon 30 Excel inoshanda mumazuva makumi matatu takawana tambo dzemavara tichishandisa basa SEARCH (TSVAGA) uye zvakare yakashandiswa IFERROR (IFERROR) uye ISNUMBER (ISNUMBER) mumamiriro ezvinhu apo basa rinokanda chikanganiso.

Pazuva rechi19 remarathon edu, tichadzidza basa racho MATCH (TSVAGA). Inotarisa kukosha muhurongwa uye, kana kukosha kwawanikwa, inodzorera nzvimbo yayo.

Saka, ngatitendeukei kune ruzivo rwereferenzi pane basa MATCH (MATCH) uye tarisa mienzaniso mishoma. Kana iwe uine yako mienzaniso kana nzira dzekushanda neiri basa, ndokumbira ugovane mumhinduro.

Basa 19: MATCH

basa MATCH (MATCH) inodzosa chinzvimbo cheukoshi muhurongwa, kana kukanganisa #AT (#N/A) kana isina kuwanikwa. Array inogona kurongeka kana kusarongedzwa. Function MATCH (MATCH) haisi nyaya inonzwisisika.

Ungashandisa sei iyo MATCH basa?

basa MATCH (MATCH) inodzosa chinzvimbo chechinhu muhurongwa, uye mhedzisiro iyi inogona kushandiswa nemamwe mabasa akadai se. INDEX (INDEX) kana VLOOKUP (VPR). Semuyenzaniso:

  • Tsvaga nzvimbo yechinhu murunyorwa rusina kurongwa.
  • Shandisa ne SARUDZA (SARUDZA) kushandura kuita kwemudzidzi kune mavara mamakisi.
  • Shandisa ne VLOOKUP (VLOOKUP) yekusarudzika koramu sarudzo.
  • Shandisa ne INDEX (INDEX) kuti uwane kukosha kwepedyo.

Syntax MATCH

basa MATCH (MATCH) ine syntax inotevera:

MATCH(lookup_value,lookup_array,[match_type])

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

  • lookup_value (lookup_value) - Inogona kuve mameseji, nhamba, kana boolean.
  • lookup_array (lookup_array) - array kana array referensi (maseru ari padyo mukoramu imwechete kana mutsetse wakafanana).
  • match_type (match_type) inogona kutora matatu maitiro: -1, 0 or 1. Kana nharo ikasiiwa, yakaenzana ne 1.

Traps MATCH (MATCH)

basa MATCH (MATCH) inodzosa chinzvimbo chechinhu chakawanikwa, asi kwete kukosha kwayo. Kana iwe uchida kudzorera kukosha, shandisa MATCH (MATCH) pamwe chete nebasa INDEX (INDEX).

Muenzaniso 1: Kutsvaga chinhu mune isina kurongwa runyorwa

Kune runyorwa rusina kurongwa, unogona kushandisa 0 senharo kukosha match_type (match_type) kutsvaga chaiyo inofanana. Kana iwe uchida kutsvaga chaiyo match yemavara tambo, unogona kushandisa wildcard mavara mukukosha kwekutsvaga.

Mumuenzaniso unotevera, kuti tiwane nzvimbo yemwedzi murunyoro, tinogona kunyora zita remwedzi, pamwe chete kana kuti chikamu, tichishandisa wildcards.

=MATCH(D2,B3:B7,0)

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

30 Excel inoshanda mumazuva makumi matatu: MATCH

Senharo lookup_array (lookup_array) unogona kushandisa ruzhinji rwemaconstants. Mumuenzaniso unotevera, mwedzi unodiwa unopinzwa muchitokisi D5, uye mazita emwedzi anotsiviwa senharo yechipiri kuchiitiko. MATCH (MATCH) sehurongwa hwezvisingachinji. Kana iwe ukapinda mwedzi unotevera muchitokisi D5, semuenzaniso, Oct (Gumiguru), ipapo mugumisiro webasa uchava #AT (#N/A).

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

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

30 Excel inoshanda mumazuva makumi matatu: MATCH

Muenzaniso 2: Shandura magiredhi evadzidzi kubva muzana kuenda kune mavara

Unogona kushandura mamakisi emudzidzi kuita tsamba system uchishandisa basa racho MATCH (MATCH) sezvawakaita newe VLOOKUP (VPR). Mumuenzaniso uyu, basa rinoshandiswa pamwe chete ne SARUDZA (Sarudzo), iyo inodzorera fungidziro yatinoda. Nharo match_type (match_type) yakaiswa kuenzana ne -1, nekuti zvibodzwa zviri patafura zvinorongwa zvichidzika.

Apo nharo match_type (match_type) ndizvo -1, mhedzisiro ndiyo diki kukosha iyo yakakura kupfuura kana kuenzana nemutengo unodiwa. Mumuenzaniso wedu, kukosha kwaidiwa ndeye 54. Sezvo pasina kukosha kwakadaro mumutsara wezvibodzwa, chinhu chinoenderana nehuwandu hwe60 chinodzorerwa. Sezvo 60 iri munzvimbo yechina mune rondedzero, mhedzisiro yebasa SARUDZA (Sarudzo) ichave kukosha kuri pachinzvimbo chechina, kureva sero C4, ine zvibodzwa D.

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

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

30 Excel inoshanda mumazuva makumi matatu: MATCH

Muenzaniso 3: Gadzira sarudzo inochinjika yekoramu yeVLOOKUP (VLOOKUP)

Kupa kuwedzera kushanduka kune basa VLOOKUP (VLOOKUP) Unogona kushandisa MATCH (MATCH) kutsvaga nhamba yekoramu, pane kuomarara-kodha kukosha kwayo mukuita. Mumuenzaniso unotevera, vashandisi vanogona kusarudza dunhu muchitokisi H1, uku ndiko kukosha kwavari kutsvaga VLOOKUP (VPR). Tevere, vanogona kusarudza mwedzi muchitokisi H2, uye basa MATCH (MATCH) ichadzosa nhamba yekoramu inoenderana nemwedzi iwoyo.

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

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

30 Excel inoshanda mumazuva makumi matatu: MATCH

Muenzaniso 4: Kutsvaga kukosha kwepedyo uchishandisa INDEX (INDEX)

basa MATCH (MATCH) inoshanda zvikuru pamwe chete nebasa INDEX (INDEX), iyo yatichatarisa zvakanyanya gare gare mune ino marathon. Mumuenzaniso uyu, basa MATCH (MATCH) inoshandiswa kutsvaga iri padyo nenhamba chaiyo kubva kunhamba dzakati wandei.

  1. basa ABS inodzosa modulus yemusiyano pakati penhamba imwe neimwe yakafembera uye chaiyo.
  2. basa Min (MIN) inowana musiyano mudiki.
  3. basa MATCH (MATCH) inowana kero yemusiyano mudiki mune runyorwa rwemisiyano. Kana paine akawanda anoenderana kukosha mune iyo rondedzero, yekutanga inodzoserwa.
  4. basa INDEX (INDEX) inodzosa zita rinoenderana nenzvimbo iyi kubva pamazita.

=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 inoshanda mumazuva makumi matatu: MATCH

Leave a Reply