30 Excel e sebetsa ka matsatsi a 30: MATCH

Maobane lebelong la marathone 30 Excel e sebetsa ka matsatsi a 30 re fumane likhoele tsa mongolo re sebelisa ts'ebetso SEARCH (BATLISA) hape e sebelisitsoe IFEREKE (IFERROR) le ISNUMBER (ISNUMBER) maemong ao tšebetso e etsang phoso.

Ka letsatsi la 19 la marathone ea rona, re tla ithuta mosebetsi TŠOANELO (BATLA). E sheba boleng ka tatellano, 'me haeba boleng bo fumanoa, e khutlisetsa boemo ba eona.

Kahoo, a re ke re shebeng tlhahisoleseling mabapi le ts'ebetso TŠOANELO (MATCH) 'me u shebe mehlala e seng mekae. Haeba u na le mehlala ea hau kapa mekhoa ea ho sebetsa le ts'ebetso ena, ka kopo arolelana maikutlo.

Mosebetsi oa 19: MATCH

mosebetsi TŠOANELO (MATCH) e khutlisa boemo ba boleng ho sehlopha, kapa phoso #AT (#N/A) haeba e sa fumanehe. Sehlopha se ka hlophisoa kapa sa hlophisoa. Mosebetsi TŠOANELO (MATCH) ha e nahanele maemo.

U ka sebelisa tšebetso ea MATCH joang?

mosebetsi TŠOANELO (MATCH) e khutlisa boemo ba element ka tatellano, 'me sephetho sena se ka sebelisoa ke mesebetsi e meng joalo ka INDEX (INDEX) kapa VLOOKUP (VPR). Ka mohlala:

• Fumana boemo ba element lenaneng le sa hlophisoang.
• Sebelisa le KHETHA (KHETHA) ho fetolela tšebetso ea moithuti ho ea ho limaraka tsa mangolo.
• Sebelisa le VLOOKUP (VLOOKUP) bakeng sa khetho ea kholomo e feto-fetohang.
• Sebelisa le INDEX (INDEX) ho fumana boleng bo haufi.

MATCH ea Syntax

mosebetsi TŠOANELO (MATCH) e na le syntax e latelang:

MATCH(lookup_value,lookup_array,[match_type])

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

• sootho_nkoalo (lookup_value) - E ka ba mongolo, nomoro, kapa boolean.
• lookup_array (lookup_array) - tšupiso ea sehlopha kapa ea sehlopha (lisele tse haufi tse kholomong e le 'ngoe kapa mola o le mong).
• match_mofuta (match_type) e ka nka litekanyetso tse tharo: -1, 0 or 1. Haeba khang e siiloe, e lekana le 1.

Traps MATCH (MATCH)

mosebetsi TŠOANELO (MATCH) e khutlisa boemo ba ntho e fumanoeng, empa eseng boleng ba eona. Haeba u batla ho khutlisa boleng, sebelisa TŠOANELO (MATCH) mmoho le tshebetso INDEX (INDEX).

Mohlala 1: Ho fumana element lethathamong le sa hlophisoang

Bakeng sa lethathamo le sa hlophisoang, u ka sebelisa 0 joalo ka boleng ba khang match_mofuta (match_type) ho batla e tšoanang hantle. Haeba u batla ho fumana e tšoanang hantle le khoele ea mongolo, u ka sebelisa litlhaku tsa wildcard ho boleng ba ho batla.

Mohlaleng o latelang, ho fumana boemo ba khoeli lethathamong, re ka ngola lebitso la khoeli, ebang ke e feletse kapa e sa fella, re sebelisa li- wildcards.

=MATCH(D2,B3:B7,0)

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

E le khang lookup_array (lookup_array) o ka sebelisa li-constants tse ngata. Mohlaleng o latelang, khoeli e lakatsehang e kenngoa ka seleng ea D5, 'me mabitso a likhoeli a nkeloa sebaka e le khang ea bobeli ea mosebetsi. TŠOANELO (MATCH) joalo ka letoto la li-constants. Haeba u kenya khoeli hamorao ka seleng ea D5, mohlala, Oct (Mphalane), joale phello ea mosebetsi e tla ba #AT (#N/A).

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

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

Mohlala 2: Fetola limaraka tsa baithuti ho tloha ho liphesente ho ea ho litlhaku

U ka fetolela limaraka tsa baithuti ho sistimi ea litlhaku u sebelisa ts'ebetso TŠOANELO (MATCH) joalo ka ha u entse ka VLOOKUP (VPR). Mohlala ona, mosebetsi o sebelisoa hammoho le KHETHA (KHETHO), e khutlisang khakanyo eo re e hlokang. Khang match_mofuta (match_type) e behiloe e lekana le -1, hobane lintlha tse tafoleng li hlophisoa ka tatellano e theohang.

Ha khang match_mofuta (match_mofuta) ke -1, sephetho ke boleng bo bonyenyane ka ho fetisisa bo boholo ho feta kapa bo lekanang le boleng bo lakatsehang. Mohlala oa rona, boleng bo lakatsehang ke 54. Kaha ha ho na boleng bo joalo lethathamong la lintlha, ntho e lumellanang le boleng ba 60 e khutlisetsoa. Kaha 60 e sebakeng sa bone lethathamong, sephetho sa mosebetsi KHETHA (KHETHA) e tla ba boleng bo boemong ba 4, ke hore, sele C6, e nang le lintlha D.

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

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

Mohlala 3: Etsa khetho ea kholomo e feto-fetohang bakeng sa VLOOKUP (VLOOKUP)

Ho fana ka bonolo haholoanyane mosebetsing VLOOKUP (VLOOKUP) U ka e sebelisa TŠOANELO (MATCH) ho fumana nomoro ea kholomo, ho fapana le ho khouta ka thata boleng ba eona tšebetsong. Mohlala o latelang, basebelisi ba ka khetha sebaka seleng ea H1, bona ke boleng boo ba bo batlang VLOOKUP (VPR). Ka mor'a moo, ba ka khetha khoeli ka seleng ea H2, le mosebetsi TŠOANELO (MATCH) e tla khutlisa nomoro ea kholomo e tsamaellanang le khoeli eo.

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

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

Mohlala 4: Ho fumana boleng bo haufi ka ho sebelisa INDEX (INDEX)

mosebetsi TŠOANELO (MATCH) e sebetsa hantle hammoho le ts'ebetso INDEX (INDEX), eo re tla e sheba ka hloko hanyenyane hamorao marathone ena. Mohlala ona, mosebetsi TŠOANELO (MATCH) e sebelisoa ho fumana nomoro e haufi le e nepahetseng ho tsoa lipalong tse 'maloa tse hakantsoeng.

1. mosebetsi Abs e khutlisetsa modulus oa phapang lipakeng tsa nomoro e 'ngoe le e 'ngoe e hakanyetsoang le e nepahetseng.
2. mosebetsi mets (MIN) e fumana phapang e nyane haholo.
3. mosebetsi TŠOANELO (MATCH) e fumana aterese ea phapang e nyane ka ho fetisisa lethathamong la liphapang. Haeba ho na le lipalo tse ngata tse tsamaellanang lethathamong, la pele le tla khutlisoa.
4. mosebetsi INDEX (INDEX) e khutlisa lebitso le tsamaellanang le boemo bona lethathamong la mabitso.

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