Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: MATCH

Izolo kumarathon 30 Excel isebenza ngezinsuku ezingama-30 sithole izintambo zombhalo sisebenzisa umsebenzi SEARCH (SESHA) futhi isetshenzisiwe IFERROR (IFERROR) kanye ISNUMBER (ISNUMBER) ezimeni lapho umsebenzi uphonsa iphutha.

Ngosuku lwe-19 lwemarathon yethu, sizofunda umsebenzi MATCH (SESHA). Ibheka inani ohlwini futhi, uma inani litholwa, libuyisela indawo yalo.

Ngakho-ke, ake siphendukele kulwazi lwereferensi lomsebenzi MATCH (FANISA) bese ubheka izibonelo ezimbalwa. Uma unezibonelo zakho noma izindlela zokusebenza ngalo msebenzi, sicela wabelane ngazo kumazwana.

Umsebenzi 19: UMDLALO

Umsebenzi MATCH (MATCH) ibuyisela indawo yenani kumalungu afanayo, noma iphutha #AT (#N/A) uma ingatholakali. Amalungu afanayo angase ahlungwe noma ahlungwe. Umsebenzi MATCH (MATCH) ayizwela kakhulu.

Ungawusebenzisa kanjani umsebenzi we-MATCH?

Umsebenzi MATCH (MATCH) ibuyisela indawo yento kulungu elifanayo, futhi lo mphumela ungasetshenziswa eminye imisebenzi efana nale. INDEX (INDEX) noma I-VLOOKUP (VPR). Ngokwesibonelo:

• Thola indawo ye-elementi ohlwini olungahlungiwe.
• Sebenzisa nge KHETHA (KHETHA) ukuguqula ukusebenza komfundi kube amamaki ezinhlamvu.
• Sebenzisa nge I-VLOOKUP (I-VLOOKUP) yokukhetha ikholomu evumelana nezimo.
• Sebenzisa nge INDEX (INDEX) ukuze uthole inani eliseduze.

I-Syntax MATCH

Umsebenzi MATCH (MATCH) ine-syntax elandelayo:

MATCH(lookup_value,lookup_array,[match_type])

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

• ukubheka (lokup_value) - Kungaba umbhalo, inombolo, noma i-boolean.
• uhlu_lokubheka (lookup_array) – ireferensi yamalungu afanayo noma amalungu afanayo (amaseli aseduze kukholamu efanayo noma umugqa ofanayo).
• fanisa_uhlobo (match_type) ingathatha amanani amathathu: -1, 0 or 1. Uma impikiswano ishiywa, ilingana ne 1.

I-Traps MATCH (MATCH)

Umsebenzi MATCH (MATCH) ibuyisela indawo yento etholiwe, kodwa hhayi inani layo. Uma ufuna ukubuyisela inani, sebenzisa MATCH (FANISA) kanye nomsebenzi INDEX (INDEX).

Isibonelo 1: Ukuthola i-elementi kuhlu olungahlungiwe

Ukuze uthole uhlu olungahlungiwe, ungasebenzisa 0 njengenani lempikiswano fanisa_uhlobo (match_type) ukuze useshe okufana ncamashi. Uma ufuna ukuthola ukufana nse kochungechunge lombhalo, ungasebenzisa izinhlamvu ze-wildcard kunani lokusesha.

Esibonelweni esilandelayo, ukuthola indawo yenyanga ohlwini, singabhala igama lenyanga, liphelele noma liyingxenye, sisebenzisa amakhadi asendle.

=MATCH(D2,B3:B7,0)

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

Njengengxabano uhlu_lokubheka (lokup_array) ungasebenzisa uxhaxha lwama-constants. Esibonelweni esilandelayo, inyanga oyifunayo ifakwa kuseli D5, futhi amagama ezinyanga athathelwa indawo njengempikiswano yesibili yomsebenzi. MATCH (MATCH) njengohlu lwama-constants. Uma ufaka inyanga yakamuva kuseli D5, isibonelo, Oct (October), khona-ke umphumela womsebenzi uzoba #AT (#N/A).

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

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

Isibonelo sesi-2: Shintsha amamaki abafundi ukusuka kumaphesenti ukuya ezinhlamvini

Ungakwazi ukuguqula amamaki abafundi abe uhlelo lwezinhlamvu usebenzisa umsebenzi MATCH (FANISA) njengoba wenze nge I-VLOOKUP (VPR). Kulesi sibonelo, umsebenzi usetshenziswa ngokuhlanganyela KHETHA (UKUKHETHA), okubuyisela isilinganiso esisidingayo. Ukuphikisana fanisa_uhlobo (match_type) isethwe yalingana ne -1, ngoba amaphuzu akuthebula ahlelwa ngokulandelana okwehlayo.

Lapho ingxabano fanisa_uhlobo (uhlobo_lokufanisa) ngu -1, umphumela uyinani elincane kakhulu elikhulu noma elilingana nenani elifunekayo. Esibonelweni sethu, inani elifunekayo lingu-54. Njengoba lingekho inani elinjalo ohlwini lwamaphuzu, isici esihambisana nenani elingu-60 siyabuyiselwa. Njengoba u-60 esendaweni yesine ohlwini, umphumela womsebenzi KHETHA (KHETHA) kuzoba inani elisendaweni yesi-4, okungukuthi iseli C6, eliqukethe amaphuzu D.

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

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

Isibonelo sesi-3: Dala ukukhetha kwekholomu eguquguqukayo ye-VLOOKUP (VLOOKUP)

Ukunikeza ukuguquguquka okwengeziwe emsebenzini I-VLOOKUP (VLOOKUP) Ungasebenzisa MATCH (FANISA) ukuze uthole inombolo yekholomu, kunokubhala kanzima inani layo emsebenzini. Esibonelweni esilandelayo, abasebenzisi bangakhetha isifunda kuseli H1, leli inani abalifunayo I-VLOOKUP (VPR). Okulandelayo, bangakhetha inyanga kuseli H2, kanye nomsebenzi MATCH (MATCH) izobuyisela inombolo yekholomu ehambisana naleyo nyanga.

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

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

Isibonelo sesi-4: Ukuthola inani eliseduze usebenzisa I-INDEX (INDEX)

Umsebenzi MATCH (MATCH) isebenza kahle ngokuhambisana nomsebenzi INDEX (INDEX), esizoyibhekisisa kakhudlwana ngokuhamba kwesikhathi kulo mjaho. Kulesi sibonelo, umsebenzi MATCH (MATCH) isetshenziselwa ukuthola eseduze nenombolo efanele ezinambeni ezimbalwa eziqageliwe.

1. Umsebenzi ABS ibuyisela imodyuli yomehluko phakathi kwenombolo ngayinye eqageliwe nelungile.
2. Umsebenzi -MIN (MIN) ithola umehluko omncane kakhulu.
3. Umsebenzi MATCH (MATCH) ithola ikheli lomehluko omncane ohlwini lomehluko. Uma kunamanani amaningi afanayo ohlwini, elokuqala lizobuyiswa.
4. Umsebenzi INDEX (INDEX) ibuyisela igama elihambisana nalesi sikhundla ohlwini lwamagama.

=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))