Ayyukan Excel 30 a cikin kwanaki 30: MATCH

Jiya a gudun marathon 30 Excel yana aiki a cikin kwanaki 30 mun sami igiyoyin rubutu ta amfani da aikin SEARCH (SEARCH) kuma ana amfani dashi IFEROR (IFERROR) da ISNUMBER (ISNUMBER) a cikin yanayin da aikin ke jefa kuskure.

A ranar 19th na marathon mu, za mu yi nazarin aikin MATCH (NENE). Yana duba ƙima a cikin tsararru kuma, idan an sami ƙima, yana mayar da matsayinsa.

Don haka, bari mu juya zuwa bayanin tunani akan aikin MATCH (MATCH) kuma dubi wasu misalai. Idan kuna da misalan ku ko hanyoyin don aiki tare da wannan aikin, da fatan za a raba su a cikin sharhi.

Aiki 19: MATCH

aiki MATCH (MATCH) yana dawo da matsayin ƙima a cikin tsararru, ko kuskure #AT (#N/A) idan ba'a samu ba. Za a iya tsara tsararru ko dai ba a tsara su ba. Aiki MATCH (MATCH) ba shi da hankali.

Ta yaya za ku yi amfani da aikin MATCH?

aiki MATCH (MATCH) yana mayar da matsayin wani abu a cikin tsararru, kuma ana iya amfani da wannan sakamakon ta wasu ayyuka kamar su. INDEX (INDEX) ko KYAUTA (VPR). Misali:

  • Nemo matsayin wani kashi a jerin da ba a ware ba.
  • Amfani da zabi (SELECT) don canza aikin ɗalibi zuwa maki haruffa.
  • Amfani da KYAUTA (VLOOKUP) don zaɓin shafi mai sassauƙa.
  • Amfani da INDEX (INDEX) don nemo ƙimar mafi kusa.

Syntax MATCH

aiki MATCH (MATCH) yana da ma'ana mai zuwa:

MATCH(lookup_value,lookup_array,[match_type])

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

  • dubawa (lookup_value) - Zai iya zama rubutu, lamba, ko boolean.
  • duba_array (lookup_array) - tsararru ko tunani (kwayoyin da ke kusa a cikin ginshiƙi ɗaya ko jere ɗaya).
  • nau'in matches (match_type) na iya ɗaukar dabi'u uku: -1, 0 or 1. Idan aka bar hujja, daidai yake da 1.

Matsalolin tarko (MATCH)

aiki MATCH (MATCH) yana mayar da matsayin abin da aka samo, amma ba darajarsa ba. Idan kuna son dawo da ƙima, yi amfani MATCH (MATCH) tare da aikin INDEX (INDEX).

Misali 1: Nemo wani abu a jerin da ba a ware ba

Don lissafin da ba a ware ba, zaku iya amfani da shi 0 a matsayin hujja darajar nau'in matches (match_type) don nemo madaidaicin wasa. Idan kana son nemo madaidaicin madaidaicin igiyar rubutu, zaka iya amfani da haruffan kati a cikin ƙimar bincike.

A cikin misali na gaba, don nemo matsayin wata a cikin jeri, za mu iya rubuta sunan watan, ko dai gaba ɗaya ko a wani ɓangare, ta amfani da kati.

=MATCH(D2,B3:B7,0)

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

Ayyukan Excel 30 a cikin kwanaki 30: MATCH

A matsayin hujja duba_array (lookup_array) za ku iya amfani da tsararru na akai-akai. A cikin misali mai zuwa, ana shigar da watan da ake so a cikin cell D5, kuma ana musanya sunayen watanni a matsayin hujja ta biyu ga aikin. MATCH (MATCH) a matsayin tsararru na akai-akai. Idan kun shigar da wata daga baya a cikin cell D5, misali, Oct (Oktoba), sannan sakamakon aikin zai kasance #AT (#N/A).

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

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

Ayyukan Excel 30 a cikin kwanaki 30: MATCH

Misali 2: Canja maki dalibi daga kashi zuwa haruffa

Kuna iya canza maki ɗalibai zuwa tsarin haruffa ta amfani da aikin MATCH (MATCH) kamar yadda kuka yi da KYAUTA (VPR). A cikin wannan misali, ana amfani da aikin tare da haɗin gwiwa zabi (ZABI), wanda ya dawo da kimanta da muke bukata. Hujja nau'in matches (match_type) an saita daidai da -1, saboda maki a cikin tebur an jera su a cikin tsari mai saukowa.

Lokacin da jayayya nau'in matches (match_type) shine -1, sakamakon shine mafi ƙarancin ƙima wanda ya fi girma ko daidai da ƙimar da ake so. A cikin misalinmu, ƙimar da ake so shine 54. Tun da babu irin wannan darajar a cikin jerin ƙididdiga, an mayar da kashi daidai da darajar 60. Tun da 60 yana cikin matsayi na hudu a cikin jerin, sakamakon aikin zabi (SELECT) zai zama darajar da ke matsayi na 4, watau cell C6, wanda ya ƙunshi maki D.

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

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

Ayyukan Excel 30 a cikin kwanaki 30: MATCH

Misali 3: Ƙirƙiri zaɓi mai sassauƙa don VLOOKUP (VLOOKUP)

Don ƙarin sassauci ga aikin KYAUTA (VLOOKUP) Kuna iya amfani da shi MATCH (MATCH) don nemo lambar shafi, maimakon hard-codeing darajarsa cikin aikin. A cikin misali mai zuwa, masu amfani zasu iya zaɓar yanki a cikin tantanin halitta H1, wannan shine ƙimar da suke nema KYAUTA (VPR). Na gaba, za su iya zaɓar wata ɗaya a cikin tantanin halitta H2, da aikin MATCH (MATCH) zai dawo da lambar shafi daidai da waccan watan.

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

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

Ayyukan Excel 30 a cikin kwanaki 30: MATCH

Misali 4: Neman ƙimar mafi kusa ta amfani da INDEX (INDEX)

aiki MATCH (MATCH) yana aiki mai girma a hade tare da aikin INDEX (INDEX), wanda za mu duba da kyau nan gaba kadan a cikin wannan tseren marathon. A cikin wannan misali, aikin MATCH Ana amfani da (MATCH) don nemo mafi kusa da madaidaicin lamba daga lambobi da yawa da aka tsinta.

  1. aiki ABS yana dawo da ma'aunin bambanci tsakanin kowace ƙididdiga da madaidaicin lamba.
  2. aiki MIN (MIN) ya sami mafi ƙarancin bambanci.
  3. aiki MATCH (MATCH) yana nemo adireshin mafi ƙarancin bambanci a cikin jerin bambance-bambance. Idan akwai madaidaitan dabi'u da yawa a cikin jerin, za a mayar da na farko.
  4. aiki INDEX (INDEX) yana mayar da sunan daidai da wannan matsayi daga jerin sunayen.

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

Ayyukan Excel 30 a cikin kwanaki 30: MATCH

Leave a Reply