MYNEGAI a MATCH ar draws sawl colofn
I edrych ar werth trwy baru ar draws sawl colofn, fformiwla arae yn seiliedig ar y MYNEGAI ac MATCH swyddogaethau sy'n ymgorffori MMULT, TROSGLWYDDO ac COLUMN a wnewch chwi ffafr.
Sut i edrych ar werth trwy baru ar draws sawl colofn?
I lenwi'r dosbarth cyfatebol pob myfyriwr fel y dangosir y tabl uchod, lle mae'r rhestrir gwybodaeth ar draws sawl colofn, yn gyntaf gallwch ddefnyddio tric y swyddogaeth MMULT, TRANSPOSE a COLUMN i gynhyrchu arae matrics. Yna bydd swyddogaeth MATCH yn rhoi lleoliad eich gwerth edrych i chi, a fydd yn cael ei fwydo i MYNEGAI i adfer y gwerth rydych chi'n edrych amdano yn yr arae.
Cystrawen generig
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Nodyn: Mae hon yn fformiwla arae sy'n gofyn i chi fynd i mewn iddi Ctrl + Symud + Rhowch.
- dychwelyd_range: Yr ystod lle rydych chi am i'r fformiwla ddychwelyd gwybodaeth y dosbarth. Yma yn cyfeirio at yr ystod dosbarth.
- Gwerth_edrych: Gwerth y fformiwla a ddefnyddir i ddod o hyd i'w gwybodaeth ddosbarth gyfatebol. Yma yn cyfeirio at yr enw a roddir.
- chwilio_arae: Yr ystod o gelloedd lle mae'r lookup_value wedi'i restru; Yr ystod gyda'r gwerthoedd i'w cymharu â'r lookup_value. Yma yn cyfeirio at yr ystod enwau.
- math_match 0: Yn gorfodi MATCH i ddod o hyd i'r gwerth cyntaf sy'n cyfateb yn union i'r lookup_value.
I ddod o hyd i'r dosbarth Jimmy, copïwch neu nodwch y fformiwla isod yn y gell H5, a gwasgwch Ctrl + Symud + Rhowch i gael y canlyniad:
MYNEGAI ($ B $ 5: $ B $ 7, (MATCH (1, MMULT (- ($ C $ 5: $ E $ 7=G5), TROSGLWYDDO (COLUMN ($ C $ 5: $ E $ 7) ^ 0)), 0)))
√ Nodyn: Mae'r arwyddion doler ($) uchod yn nodi cyfeiriadau absoliwt, sy'n golygu na fydd yr enw a'r ystodau dosbarth yn y fformiwla yn newid pan fyddwch chi'n symud neu'n copïo'r fformiwla i gelloedd eraill. Sylwch na ddylech ychwanegu arwyddion doler at y cyfeirnod celloedd sy'n cynrychioli'r gwerth edrych, gan eich bod am iddo fod yn gymharol pan fyddwch chi'n ei gopïo i gelloedd eraill. Ar ôl i chi nodi'r fformiwla, llusgwch y ddolen llenwi i lawr i gymhwyso'r fformiwla i'r celloedd isod.
Esboniad o'r fformiwla
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- - ($ C $ 5: $ E $ 7 = G5): Mae'r segment hwn yn gwirio pob gwerth yn yr ystod $ C $ 5: $ E $ 7 os ydyn nhw'n hafal i'r gwerth yn y gell G5, ac yn cynhyrchu amrywiaeth GWIR a GAU fel hyn:
{GWIR, ANGHYWIR, ANGHYWIR; ANGHYWIR, ANWIR, ANGHYWIR; ANWIR, ANWIR, ANGHYWIR}.
Yna bydd y negatif dwbl yn trosi'r GWIR a'r GAU i 1s a 0s i gynhyrchu amrywiaeth fel hyn:
{1,0,0; 0,0,0; 0,0,0}. - COLUMN ($ C $ 5: $ E $ 7): Mae'r swyddogaeth COLUMN yn dychwelyd rhifau'r colofnau ar gyfer yr ystod $ C $ 5: $ E $ 7 mewn amrywiaeth fel hyn: {3,4,5}.
- TROSGLWYDDO (COLUMN ($ C $ 5: $ E $ 7)^ 0) = TROSGLWYDDO ({3,4,5}^ 0): Ar ôl codi'r pŵer i 0, bydd yr holl rifau yn yr arae {3,4,5} yn cael eu trosi i 1: {1,1,1}. Yna mae'r swyddogaeth TRANSPOSE yn trosi'r arae golofn yn rhes rhes fel hyn: {1; 1; 1}.
- MMULT (- ($ C $ 5: $ E $ 7 = G5),TROSGLWYDDO (COLUMN ($ C $ 5: $ E $ 7)^ 0)) = MMULT ({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Mae'r swyddogaeth MMULT yn dychwelyd cynnyrch matrics y ddau arae fel hyn: {1; 0; 0}.
- MATCH (1,MMULT (- ($ C $ 5: $ E $ 7 = G5),TROSGLWYDDO (COLUMN ($ C $ 5: $ E $ 7)^ 0)), 0) = MATCH (1,{1; 0; 0}, 0): Y match_type 0 yn gorfodi swyddogaeth MATCH i ddychwelyd safle'r gêm gyntaf o 1 yn yr arae {1; 0; 0}, Sy'n 1.
- MYNEGAI ($ B $ 5: $ B $ 7,(MATCH (1,MMULT (- ($ C $ 5: $ E $ 7 = G5),TROSGLWYDDO (COLUMN ($ C $ 5: $ E $ 7)^ 0)), 0))) = MYNEGAI ($ B $ 5: $ B $ 7,1): Mae'r swyddogaeth INDEX yn dychwelyd y 1gwerth st yn yr ystod dosbarth $ B $ 5: $ B $ 7, Sy'n A.
Er mwyn edrych yn hawdd ar werth trwy baru ar draws sawl colofn, gallwch hefyd ddefnyddio ein ychwanegiad Excel proffesiynol Kutools Ar gyfer Excel. Gweler y cyfarwyddyd yma i gyflawni'r genhadaeth.
Swyddogaethau cysylltiedig
Mae swyddogaeth Excel INDEX yn dychwelyd y gwerth a arddangosir yn seiliedig ar safle penodol o ystod neu arae.
Mae swyddogaeth Excel MATCH yn chwilio am werth penodol mewn ystod o gelloedd, ac yn dychwelyd safle cymharol y gwerth.
Mae swyddogaeth Excel MMULT yn dychwelyd cynnyrch matrics dau arae. Mae gan ganlyniad yr arae yr un nifer o resi ag arae1 a'r un nifer o golofnau ag arae2.
Mae swyddogaeth Excel TRANSPOSE yn cylchdroi cyfeiriadedd ystod neu arae. Er enghraifft, gall gylchdroi bwrdd a drefnodd yn llorweddol mewn rhesi i fertigol mewn colofnau neu i'r gwrthwyneb.
Mae'r swyddogaeth COLUMN yn dychwelyd nifer y golofn y mae'r fformiwla'n ymddangos neu'n dychwelyd rhif colofn y cyfeirnod a roddir. Er enghraifft, fformiwla = COLUMN (BD) yn dychwelyd 56.
Fformiwlâu Cysylltiedig
Edrych ar feini prawf lluosog gyda MYNEGAI a MATCH
Wrth ddelio â chronfa ddata fawr mewn taenlen Excel gyda sawl colofn a chapsiwn rhes, mae bob amser yn anodd dod o hyd i rywbeth sy'n cwrdd â meini prawf lluosog. Yn yr achos hwn, gallwch ddefnyddio fformiwla arae gyda'r swyddogaethau MYNEGAI a MATCH.
Edrych dwy ffordd gyda MYNEGAI a MATCH
I chwilio am rywbeth ar draws rhesi a cholofnau yn Excel, neu dywedwn i edrych ar werth ar groesffordd y rhes a'r golofn benodol, gallwn ddefnyddio help swyddogaethau MYNEGAI a MATCH.
Edrych ar y gwerth cyfatebol agosaf â meini prawf lluosog
Mewn rhai achosion, efallai y bydd angen i chi edrych ar y gwerth cyfatebol agosaf neu fras yn seiliedig ar fwy nag un maen prawf. Gyda'r cyfuniad o swyddogaethau MYNEGAI, MATCH ac IF, gallwch chi ei gyflawni'n gyflym yn Excel.
Yr Offer Cynhyrchedd Swyddfa Gorau
Kutools for Excel - Yn Eich Helpu i Sefyll Allan O Dyrfa
Kutools ar gyfer Excel Mae ganddo Dros 300 o Nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...
Tab Office - Galluogi Darllen a Golygu Tabiau yn Microsoft Office (gan gynnwys Excel)
- Un eiliad i newid rhwng dwsinau o ddogfennau agored!
- Gostyngwch gannoedd o gliciau llygoden i chi bob dydd, ffarweliwch â llaw llygoden.
- Yn cynyddu eich cynhyrchiant 50% wrth wylio a golygu sawl dogfen.
- Yn dod â Thabiau Effeithlon i'r Swyddfa (gan gynnwys Excel), Just Like Chrome, Edge a Firefox.
