Skip i'r prif gynnwys

Excel MYNEGAI MATCH: Chwilio sylfaenol ac uwch

Yn Excel, mae adalw data penodol yn gywir yn aml yn anghenraid yn aml. Er bod gan y swyddogaethau INDEX a MATCH eu cryfderau eu hunain, mae eu cyfuno yn datgloi set offer pwerus ar gyfer chwilio data. Gyda'i gilydd, maent yn hwyluso ystod o alluoedd chwilio, o edrychiadau llorweddol a fertigol sylfaenol i swyddogaethau mwy datblygedig fel chwiliadau dwy ffordd, sy'n sensitif i achosion ac aml-feini prawf. Gan gynnig galluoedd gwell o gymharu â VLOOKUP, mae paru INDEX a MATCH yn caniatáu ystod ehangach o opsiynau chwilio data. Yn y tiwtorial hwn, gadewch i ni ymchwilio i ddyfnder y posibiliadau y gallant eu cyflawni gyda'i gilydd.


Sut i ddefnyddio INDEX a MATCH yn Excel

Cyn i ni ddefnyddio'r swyddogaethau MYNEGAI a MATCH, gadewch i ni sicrhau ein bod ni'n gwybod sut y gall MYNEGAI a MATCH ein helpu i edrych ar werthoedd yn gyntaf.


Sut i ddefnyddio swyddogaeth INDEX yn Excel

Mae adroddiadau MYNEGAI mae swyddogaeth yn Excel yn dychwelyd y gwerth mewn lleoliad penodol mewn ystod benodol. Mae cystrawen swyddogaeth INDEX fel a ganlyn:

=INDEX(array, row_num, [column_num])
  • amrywiaeth (gofynnol) yn cyfeirio at yr ystod lle rydych chi am ddychwelyd y gwerth.
  • rhes_num (gofynnol, oni bai colofn_num yn bresennol) yn cyfeirio at rif rhes yr arae.
  • colofn_num (dewisol, ond yn ofynnol os rhes_num yn cael ei hepgor) yn cyfeirio at rif colofn yr arae.

Er enghraifft, i wybod sgôr Jeff, 6fed myfyriwr ar y rhestr, gallwch ddefnyddio'r ffwythiant MYNEGAI fel hyn:

=INDEX(C2:C11,6)

cyfateb mynegai excel 01

√ Nodyn: Yr ystod C2: C11 yw lle mae'r sgoriau a restrir, tra bod y nifer 6 yn darganfod sgôr arholiad y 6th myfyriwr.

Yma, gadewch i ni wneud ychydig o brawf. Ar gyfer y fformiwla =MYNEGAI(A1:C1,2), pa werth y bydd yn ei ddychwelyd? --- Bydd, bydd yn dychwelyd Dyddiad geni, 2nd gwerth yn y rhes a roddir.

Nawr dylem wybod y gall swyddogaeth INDEX weithio'n berffaith gydag ystodau llorweddol neu fertigol. Ond beth os bydd ei angen arnom i ddychwelyd gwerth mewn ystod fwy gyda sawl rhes a cholofn? Wel, yn yr achos hwn, dylem gymhwyso rhif rhes a rhif colofn. Er enghraifft, i ddarganfod Sgôr Jeff o fewn yr ystod tabl yn lle colofn sengl, gallwn leoli ei sgôr gydag a rhif rhes o 6 a rhif colofn o 3 yn y celloedd trwy A2 i C11 fel hyn:

=INDEX(A2:C11,6,3)

cyfateb mynegai excel 02

Pethau y dylem eu gwybod am y swyddogaeth INDEX yn Excel:
  • Gall swyddogaeth INDEX weithio gydag ystodau fertigol a llorweddol.
  • Os y ddau rhes_num ac colofn_num defnyddio dadleuon, rhes_num yn mynd o flaen y colofn_num, ac mae MYNEGAI yn adennill y gwerth ar groesffordd y penodedig rhes_num ac colofn_num.

Fodd bynnag, ar gyfer cronfa ddata wirioneddol fawr gyda nifer o resi a cholofnau, yn sicr nid yw'n gyfleus i ni gymhwyso'r fformiwla gydag union rif rhes a rhif colofn. A dyma ni pan ddylem ni gyfuno'r defnydd o swyddogaeth MATCH.


Sut i ddefnyddio swyddogaeth MATCH yn Excel

Mae swyddogaeth MATCH yn Excel yn dychwelyd gwerth rhifol, lleoliad eitem benodol yn yr ystod benodol. Mae cystrawen swyddogaeth MATCH fel a ganlyn:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value (gofynnol) yn cyfeirio at y gwerth i gyfateb yn y chwilio_array.
  • chwilio_array (gofynnol) yn cyfeirio at yr ystod o gelloedd lle rydych chi am i MATCH chwilio.
  • math_mathau (dewisol): 1, 0 or -1.
    • 1 (diofyn), bydd MATCH yn dod o hyd i'r gwerth mwyaf sy'n llai na neu'n hafal i'r lookup_value. Mae'r gwerthoedd yn y chwilio_array rhaid eu rhoi mewn trefn esgynnol.
    • 0, Bydd MATCH yn dod o hyd i'r gwerth cyntaf sy'n cyfateb yn union i'r lookup_value. Mae'r gwerthoedd yn y chwilio_array gall fod mewn unrhyw drefn. (Ar gyfer yr achosion bod y math paru wedi'i osod i 0, gallwch ddefnyddio nodau cerdyn gwyllt.)
    • -1, Bydd MATCH yn dod o hyd i'r gwerth lleiaf sy'n fwy na neu'n hafal i'r lookup_value. Mae'r gwerthoedd yn y chwilio_array rhaid eu rhoi mewn trefn ddisgynnol.

Er enghraifft, i wybod safle Vera yn y rhestr enwau, gallwch ddefnyddio fformiwla MATCH fel hyn:

=MATCH("Vera",A2:A11,0)

cyfateb mynegai excel 3

√ Nodyn: Mae canlyniad “4” yn nodi bod yr enw “Vera” yn y 4ydd safle ar y rhestr.

Pethau y dylem eu gwybod am swyddogaeth MATCH yn Excel:
  • Mae'r swyddogaeth MATCH yn dychwelyd lleoliad y gwerth edrych yn yr arae edrych, nid y gwerth ei hun.
  • Mae'r swyddogaeth MATCH yn dychwelyd y gêm gyntaf rhag ofn dyblygu.
  • Yn union fel swyddogaeth INDEX, gall swyddogaeth MATCH weithio gydag ystodau fertigol a llorweddol hefyd.
  • Nid yw MATCH yn sensitif i achosion.
  • Os yw'r lookup_value o'r fformiwla MATCH ar ffurf testun, amgaewch ef mewn dyfyniadau.
  • Os yw'r lookup_value ni cheir yn y chwilio_array, # N / A gwall yn cael ei ddychwelyd.

Nawr ein bod ni'n gwybod am ddefnyddiau sylfaenol swyddogaethau INDEX a MATCH yn Excel, gadewch i ni dorchi ein llewys a pharatoi i gyfuno'r ddwy swyddogaeth.


Sut i gyfuno MYNEGAI a MATCH yn Excel

Gweler yr enghraifft isod i ddarganfod sut allwn ni gyfuno'r swyddogaethau MYNEGAI a MATCH:

i ddod o hyd Sgôr Evelyn, gyda'r wybodaeth bod y sgoriau arholiad yn y 3rd golofn, gallwn defnyddiwch y swyddogaeth MATCH i bennu lleoliad y rhes yn awtomatig heb fod angen ei gyfrif â llaw. Yn dilyn hynny, gallwn gyflogi'r swyddogaeth MYNEGAI i adfer y gwerth ar groesffordd y rhes a nodwyd a'r 3edd golofn:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

cyfateb mynegai excel 4

Wel, gan y gall y fformiwla edrych ychydig yn gymhleth, gadewch i ni fynd trwy bob rhan ohoni.

cyfateb mynegai excel 5

Mae adroddiadau MYNEGAI mae'r fformiwla'n cynnwys tair dadl:

  • rhes_num: MATCH ("Evelyn", A2:A11,0) yn darparu MYNEGAI gyda safle rhes y gwerth "Evelyn" yn yr ystod A2: A11, Sy'n 5.
  • colofn_num: 3 yn nodi'r 3ydd colofn ar gyfer MYNEGAI i leoli'r sgôr o fewn yr arae.
  • amrywiaeth: A2: C11 yn cyfarwyddo MYNEGAI i ddychwelyd y gwerth cyfatebol ar groesffordd y rhes a'r golofn benodedig, o fewn yr ystod sy'n rhychwantu o A2 i C11. Yn olaf, cawn y canlyniad 90.

Yn y fformiwla uchod, gwnaethom ddefnyddio gwerth cod caled, "Evelyn". Fodd bynnag, yn ymarferol, mae gwerthoedd cod caled yn anymarferol, gan y byddai angen eu haddasu bob tro y byddwn yn ceisio chwilio am ddata gwahanol, megis y sgôr ar gyfer myfyriwr arall. Mewn senarios o'r fath, gallwn ddefnyddio cyfeiriadau cell i greu fformiwlâu deinamig. Er enghraifft, yn yr achos hwn, gwnaf newid "Evelyn" i Dd2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

(AD) Symleiddiwch chwiliadau gyda Kutools: Nid oes angen teipio fformiwla!

Kutools ar gyfer Excel's Super-edrych yn darparu amrywiaeth o offer chwilio wedi'i deilwra i gwrdd â'ch holl anghenion. P'un a ydych chi'n gwneud chwiliadau aml-feini prawf, yn chwilio ar draws sawl tudalen, neu'n gwneud chwiliad un-i-lawer, Super-edrych yn symleiddio'r broses gyda dim ond ychydig o gliciau. Archwiliwch y nodweddion hyn i weld sut Super-edrych yn newid y ffordd rydych chi'n rhyngweithio â data Excel. Ffarwelio â'r drafferth o gofio fformiwlâu cymhleth.

Offer Edrych Kutools

Kutools ar gyfer Excel - Eich grymuso gyda dros 300 o swyddogaethau defnyddiol ar gyfer cynhyrchiant diymdrech. Peidiwch â cholli'ch cyfle i roi cynnig arni gyda threial 30 diwrnod llawn sylw am ddim! Dechrau arni nawr!


Enghreifftiau o fformiwla INEX a MATCH

Yn y rhan hon, byddwn yn siarad am wahanol amgylchiadau i ddefnyddio'r swyddogaethau MYNEGAI a MATCH i ddiwallu gwahanol anghenion.


MYNEGAI a MATCH i gymhwyso edrych dwy ffordd

Yn yr enghraifft flaenorol, roeddem yn gwybod rhif y golofn a defnyddio fformiwla MATCH i ddod o hyd i rif y rhes. Ond beth os ydym yn ansicr am rif y golofn hefyd?

Mewn achosion o'r fath, gallwn berfformio chwiliad dwy ffordd, a elwir hefyd yn chwilio matrics, trwy ddefnyddio dwy swyddogaeth MATCH: un i ddod o hyd i rif y rhes a'r llall i bennu rhif y golofn. Er enghraifft, i wybod Sgôr Evelyn, dylem ddefnyddio'r fformiwla:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

cyfateb mynegai excel 6

Sut mae'r fformiwla hon yn gweithio:
  • Mae'r fformiwla MATCH gyntaf yn canfod lleoliad Evelyn yn y rhestr A2:A11, ar yr amod 5 fel rhif rhes i MYNEGAI.
  • Mae ail fformiwla MATCH yn pennu'r golofn ar gyfer y sgoriau a'r dychweliadau 3 fel rhif colofn i MYNEGAI.
  • Mae'r fformiwla yn symleiddio i =MYNEGAI(A2:C11,5,3), a MYNEGAI yn dychwelyd 90.

MYNEGAI a MATCH i gymhwyso edrych chwith

Nawr, gadewch i ni ystyried senario lle mae angen i chi benderfynu ar ddosbarth Evelyn. Efallai eich bod wedi sylwi bod colofn y dosbarth wedi'i lleoli i'r chwith o'r golofn enw, sefyllfa sy'n rhagori ar alluoedd swyddogaeth chwilio Excel bwerus arall, VLOOKUP.

Mewn gwirionedd, mae'r gallu i berfformio edrychiadau ochr chwith yn un o'r agweddau lle mae'r cyfuniad o INDEX a MATCH yn drech na VLOOKUP.

i ddod o hyd Dosbarth Evelyn, cyflogi y fformiwla ganlynol i chwiliwch am Evelyn yn B2:B11 ac adalw'r gwerth cyfatebol o A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

cyfateb mynegai excel 7

Nodyn: Gallwch chi berfformio chwiliad chwith yn hawdd ar gyfer gwerthoedd penodol gan ddefnyddio'r LOOKUP o'r Dde i'r Chwith nodwedd o Kutools ar gyfer Excel gyda dim ond ychydig o gliciau. I weithredu'r nodwedd, llywiwch i'r Kutools tab yn eich Excel, a chliciwch Super-edrych > LOOKUP o'r Dde i'r Chwith yn y Fformiwla grŵp.

LOOKUP o'r Dde i'r Chwith

Os nad ydych wedi gosod Kutools, cliciwch yma i llwytho i lawr a chael treial 30 diwrnod llawn sylw am ddim!


MYNEGAI a MATCH i gymhwyso edrychiad achos-sensitif

Mae swyddogaethau MATCH yn eu hanfod yn ansensitif o ran achosion. Ac eto, pan fydd angen i'ch fformiwla wahaniaethu rhwng llythrennau mawr a llythrennau bach, gallwch ei wella trwy ymgorffori'r EXACT swyddogaeth. Trwy gyfuno'r swyddogaeth MATCH ag EXACT mewn fformiwla INDEX, gallwch wedyn berfformio chwiliad sy'n sensitif i achos yn effeithiol, fel y dangosir isod:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • amrywiaeth yn cyfeirio at yr ystod lle rydych chi am ddychwelyd y gwerth.
  • lookup_value yn cyfeirio at y gwerth i gyfateb, gan ystyried yr achos o nodau, yn y chwilio_array.
  • chwilio_array yn cyfeirio at yr ystod o gelloedd yr hoffech i MATCH gymharu â nhw lookup_value.

Er enghraifft, i wybod Sgôr arholiad JIMMY, defnyddiwch y fformiwla ganlynol:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Nodyn: Mae hon yn fformiwla arae sy'n gofyn i chi fynd i mewn iddi Ctrl + Symud + Rhowch, ac eithrio yn Excel 365 ac Excel 2021.

cyfateb mynegai excel 8

Sut mae'r fformiwla hon yn gweithio:
  • Mae'r swyddogaeth EXACT yn cymharu "JIMMY" gyda'r gwerthoedd yn y rhestr A2: A11, gan ystyried achos nodau: Os yw'r ddau linyn yn cyd-fynd yn union, gan gyfrif am nodau priflythrennau a llythrennau bach, mae EXACT yn dychwelyd TRUE; fel arall, mae'n dychwelyd Anghywir. O ganlyniad, rydym yn cael arae sy'n cynnwys gwerthoedd CYWIR ac ANGHYWIR.
  • Yna mae'r ffwythiant MATCH yn adfer y safle'r gwerth GWIR cyntaf yn yr arae, a ddylai fod 10.
  • Yn olaf, mae MYNEGAI yn adennill y gwerth yn y 10fed safle a ddarperir gan MATCH yn yr arae.

Nodiadau:

  • Cofiwch nodi'r fformiwla yn gywir trwy wasgu Ctrl + Shift + Enter, oni bai eich bod yn defnyddio Excel 365 or Excel 2021, ac os felly, pwyswch yn syml Rhowch.
  • Mae'r fformiwla uchod yn chwilio o fewn un rhestr C2: C11. Os ydych chi am chwilio o fewn ystod gyda cholofnau a rhesi lluosog, dywedwch A2: C11, dylech gynnig rhifau colofn a rhes i FYNEGAI:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • Yn y fformiwla ddiwygiedig hon, rydym yn defnyddio'r swyddogaeth MATCH i chwilio am "JIMMY", gan ystyried achos nodau, yn yr ystod A2: A11, ac unwaith y byddwn yn dod o hyd i gyfateb, rydym yn adennill y gwerth cyfatebol o'r 3colofn rd yr ystod A2: C11.

MYNEGAI a MATCH i ddod o hyd i'r un agosaf

Yn Excel, efallai y byddwch chi'n dod ar draws sefyllfaoedd lle mae angen i chi ddod o hyd i'r cyfatebiad agosaf neu agosaf at werth penodol o fewn set ddata. Mewn sefyllfaoedd o'r fath, gall defnyddio cyfuniad o swyddogaethau INDEX a MATCH, ynghyd â swyddogaethau ABS a MIN, fod yn hynod ddefnyddiol.

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • amrywiaeth yn cyfeirio at yr ystod lle rydych chi am ddychwelyd y gwerth.
  • chwilio_array yn cyfeirio at yr ystod o werthoedd yr ydych am ddod o hyd i'r cyfatebiad agosaf iddo lookup_value.
  • lookup_value yn cyfeirio at y gwerth i ddarganfod ei gydweddiad agosaf.

Er enghraifft, i ddarganfod y mae ei sgôr agosaf at 85, defnyddiwch y fformiwla ganlynol i chwiliwch am y sgôr agosaf at 85 yn C2:C11 ac adalw'r gwerth cyfatebol o A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Nodyn: Mae hon yn fformiwla arae sy'n gofyn i chi fynd i mewn iddi Ctrl + Symud + Rhowch, ac eithrio yn Excel 365 ac Excel 2021.

Sut mae'r fformiwla hon yn gweithio:
  • ABS(C2:C11-85) yn cyfrifo'r gwahaniaeth absoliwt rhwng pob gwerth yn yr amrediad C2: C11 ac 85, gan arwain at amrywiaeth o'r gwahaniaethau absoliwt.
  • MIN(ABS(C2:C11-85)) yn canfod y gwerth lleiaf yn yr arae o wahaniaethau absoliwt, sy'n cynrychioli'r gwahaniaeth agosaf at 85.
  • Swyddogaeth MATCH MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) wedyn yn canfod safle'r gwahaniaeth absoliwt lleiaf yn y gyfres o wahaniaethau absoliwt, a ddylai fod 10.
  • Yn olaf, mae INDEX yn adfer y gwerth yn y safle yn y rhestr A2: A11 sy'n cyfateb i'r sgôr agosaf ato 85 yn yr ystod C2: C11.

Nodiadau:

  • Cofiwch nodi'r fformiwla yn gywir trwy wasgu Ctrl + Shift + Enter, oni bai eich bod yn defnyddio Excel 365 or Excel 2021, ac os felly, pwyswch yn syml Rhowch.
  • Mewn achos o gyfartal, bydd y fformiwla hon yn dychwelyd y gêm gyntaf.
  • i ddod o hyd y gêm agosaf at y sgôr cyfartalog, disodli 85 yn y fformiwla gyda CYFARTALEDD(C2:C11).

MYNEGAI a MATCH i gymhwyso chwiliad gyda sawl maen prawf

I ddod o hyd i werth sy'n bodloni amodau lluosog, sy'n gofyn ichi chwilio ar draws dwy golofn neu fwy, defnyddiwch y fformiwla ganlynol. Mae'r fformiwla yn caniatáu ichi berfformio chwiliad aml-feini prawf trwy nodi amodau amrywiol ar draws gwahanol golofnau, gan eich helpu i ddod o hyd i'r gwerth dymunol sy'n bodloni'r holl feini prawf penodedig.

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ Nodyn: Mae hon yn fformiwla arae sy'n gofyn i chi fynd i mewn iddi Ctrl + Symud + Rhowch. Yna bydd pâr o fracedi cyrliog yn ymddangos yn y bar fformiwla.

  • amrywiaeth yn cyfeirio at yr ystod lle rydych chi am ddychwelyd y gwerth.
  • (lookup_value=lookup_array) cynrychioli un amod. Mae'r amod hwn yn gwirio a yw'n benodol lookup_value yn cyfateb i'r gwerthoedd yn y chwilio_array.

Er enghraifft, i ddod o hyd i'r sgôr Coco Dosbarth A, y mae ei ddyddiad geni yn 7/2/2008, gallwch ddefnyddio'r fformiwla ganlynol:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

cyfateb mynegai excel 9

Nodiadau:

  • Yn y fformiwla hon, rydym yn osgoi gwerthoedd codio caled, gan ei gwneud hi'n syml i gael sgôr gyda gwybodaeth wahanol trwy addasu'r gwerthoedd mewn celloedd G2, G3, a G4.
  • Dylech nodi'r fformiwla trwy wasgu Ctrl + Shift + Enter heblaw mewn Excel 365 or Excel 2021, lle gallwch chi wasgu'n syml Rhowch.
    Os byddwch yn gyson yn anghofio defnyddio Ctrl + Shift + Enter i gwblhau'r fformiwla a chael canlyniadau anghywir, defnyddiwch y fformiwla ychydig yn fwy cymhleth ganlynol, y gallwch chi ei chwblhau gyda syml Rhowch allweddol:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • Gall y fformiwlâu fod yn gymhleth ac yn heriol i'w cofio. I symleiddio chwiliadau aml-feini prawf heb fod angen mewnbynnu fformiwla â llaw, ystyriwch ddefnyddio Kutools ar gyfer Excel'S Edrych Aml-gyflwr nodwedd. Unwaith y byddwch wedi gosod Kutools, llywiwch i'r Kutools tab yn eich Excel, a chliciwch Super-edrych > Edrych Aml-gyflwr yn y Fformiwla grŵp.

    Edrych Aml-gyflwr

    Os nad ydych wedi gosod Kutools, cliciwch yma i llwytho i lawr a chael treial 30 diwrnod llawn sylw am ddim!


MYNEGAI a MATCH i gymhwyso edrych ar draws sawl colofn

Dychmygwch senario lle rydych chi'n delio â cholofnau data lluosog. Mae'r golofn gyntaf yn gweithredu fel allwedd i ddosbarthu'r data yn y colofnau eraill. I benderfynu ar y categori neu ddosbarthiad ar gyfer cofnod penodol, bydd yn rhaid i chi wneud chwiliad ar draws y colofnau data a'i gysylltu â'r allwedd berthnasol yn y golofn gyfeirio.

Er enghraifft, yn y tabl isod, sut gallwn ni baru'r myfyriwr Shawn â'i ddosbarth cyfatebol gan ddefnyddio MYNEGAI a MATCH? Wel, gallwch chi ei gyflawni gyda fformiwla, ond mae'r fformiwla yn eithaf helaeth a gall fod yn heriol i'w deall, heb sôn am gofio a theipio.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Dyna lle Kutools ar gyfer Excel's Mynegai a Chyfateb ar Golofnau Lluosog nodwedd yn dod yn ddefnyddiol. Mae'n symleiddio'r broses, gan ei gwneud hi'n gyflym ac yn hawdd paru cofnodion penodol â'u categorïau cyfatebol. I ddatgloi'r offeryn pwerus hwn a pharu Shawn â'i ddosbarth yn ddiymdrech, yn syml lawrlwythwch a gosodwch y Kutools ar gyfer Excel add-in, ac yna gwnewch fel a ganlyn:

  1. Dewiswch y gell cyrchfan lle rydych chi am arddangos y dosbarth cyfatebol.
  2. Ar y Kutools tab, cliciwch Cynorthwyydd Fformiwla > Edrych a Chyfeirio > Mynegai a Chyfateb ar Golofnau Lluosog.
  3. cyfateb mynegai excel 11
  4. Yn y blwch deialog naid, gwnewch fel a ganlyn:
    1. Cliciwch ar y 1af eicon paru mynegai excel botwm nesaf Edrych_col i ddewis y golofn sy'n cynnwys y wybodaeth allweddol yr ydych am ei dychwelyd, hy, enwau'r dosbarthiadau. (Dim ond un golofn y gallwch chi ei dewis yma.)
    2. Cliciwch ar yr 2il eicon paru mynegai excel botwm nesaf Tabl_rng i ddewis y celloedd i gyd-fynd â'r gwerthoedd yn y rhai a ddewiswyd Edrych_col, h.y., enwau y myfyrwyr.
    3. Cliciwch ar y 3ydd eicon paru mynegai excel botwm nesaf Edrych_gwerth i ddewis y gell sy'n cynnwys enw'r myfyriwr rydych chi am ei baru â'i ddosbarth, yn yr achos hwn, Shawn.
    4. Cliciwch OK.
    5. cyfateb mynegai excel 12

Canlyniad

Mae Kutools wedi cynhyrchu'r fformiwla yn awtomatig, a byddwch yn gweld enw dosbarth Shawn yn cael ei arddangos yn y gell cyrchfan ar unwaith.

Nodyn: I roi cynnig ar y Mynegai a Chyfateb ar Golofnau Lluosog nodwedd, bydd angen Kutools ar gyfer Excel arnoch chi wedi'i osod ar eich cyfrifiadur. Os nad ydych wedi ei osod eto, peidiwch ag aros --- Dadlwythwch a gosodwch ef nawr am dreial 30 diwrnod am ddim heb unrhyw gyfyngiadau. Gwnewch i Excel weithio'n gallach heddiw!


MYNEGAI a MATCH i chwilio am y gwerth cyntaf nad yw'n wag

I adalw'r gwerth nad yw'n wag cyntaf, gan anwybyddu gwallau, o golofn neu res, gallwch ddefnyddio fformiwla sy'n seiliedig ar swyddogaethau MYNEGAI a MATCH. Fodd bynnag, os nad ydych am anwybyddu'r gwallau o'ch ystod, ychwanegwch y swyddogaeth ISBLANK.

  • Sicrhewch y gwerth heb fod yn wag cyntaf mewn colofn neu res gan anwybyddu gwallau:
  • =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))
  • Sicrhewch y gwerth heb fod yn wag cyntaf mewn colofn neu res gan gynnwys gwallau:
  • =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

Nodiadau:


MYNEGAI a MATCH i chwilio am y gwerth rhifol cyntaf

I adalw'r gwerth rhifol cyntaf o golofn neu res, defnyddiwch y fformiwla sy'n seiliedig ar y ffwythiannau MYNEGAI, MATCH ac ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

Nodiadau:


INDEX a MATCH i chwilio am gymdeithasau MAX neu MIN

Os oes angen i chi adalw gwerth sy'n gysylltiedig â'r gwerth uchaf neu leiaf o fewn ystod, gallwch ddefnyddio'r swyddogaeth MAX neu MIN ynghyd â'r swyddogaethau MYNEGAI a MATCH.

  • MYNEGAI a MATCH i adalw gwerth sy'n gysylltiedig â'r gwerth mwyaf:
  • =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
  • MYNEGAI a MATCH i adalw gwerth sy'n gysylltiedig â'r isafswm gwerth:
  • =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
  • Mae dwy ddadl yn y fformiwlâu uchod:
    • amrywiaeth yn cyfeirio at yr ystod yr ydych am ddychwelyd y wybodaeth berthnasol ohoni.
    • chwilio_array cynrychioli'r set o werthoedd i'w harchwilio neu eu chwilio am feini prawf penodol, hy, uchafswm neu isafswm gwerthoedd.

Er enghraifft, os ydych chi am benderfynu pwy sydd â'r sgôr uchaf, defnyddiwch y fformiwla ganlynol:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Sut mae'r fformiwla hon yn gweithio:
  • MAX (C2: C11) yn chwilio am y gwerth uchaf yn yr ystod C2: C11, Sy'n 96.
  • Yna mae'r ffwythiant MATCH yn dod o hyd i leoliad y gwerth uchaf yn yr arae C2: C11, a ddylai fod 1.
  • Yn olaf, mae MYNEGAI yn adfer y 1st gwerth yn y rhestr A2: A11.

Nodiadau:

  • Yn achos mwy nag un uchafswm neu isafswm gwerthoedd, fel y gwelir yn yr enghraifft uchod lle cafodd dau fyfyriwr yr un sgôr uchaf, bydd y fformiwla hon yn dychwelyd y gêm gyntaf.
  • I benderfynu pwy sydd â'r sgôr isaf, defnyddiwch y fformiwla ganlynol:
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

Awgrym: Teilwriwch eich negeseuon gwall #D/A eich hun

Wrth weithio gyda swyddogaethau INDEX a MATCH Excel, efallai y byddwch yn dod ar draws y gwall # N/A pan nad oes canlyniad cyfatebol. Er enghraifft, yn y tabl isod, wrth geisio dod o hyd i sgôr myfyriwr o’r enw Samantha, mae gwall #D/A yn ymddangos gan nad yw’n bresennol yn y set ddata.

cyfateb mynegai excel 15

Er mwyn gwneud eich taenlenni'n haws eu defnyddio, gallwch addasu'r neges gwall hon trwy lapio'ch fformiwla INDEX MATCH yn y swyddogaeth IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

cyfateb mynegai excel 16

Nodiadau:

  • Gallwch chi addasu eich negeseuon gwall trwy amnewid "Heb ei ddarganfod" gydag unrhyw destun o'ch dewis.
  • Os ydych chi am drin pob gwall, nid dim ond #Amh, ystyriwch ddefnyddio'r IFERROR swyddogaeth yn lle IFNA:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    Sylwch efallai na fyddai'n ddoeth atal pob gwall oherwydd eu bod yn rhybuddio am faterion posibl yn eich fformiwlâu.

Uchod mae'r holl gynnwys perthnasol sy'n gysylltiedig â'r swyddogaethau MYNEGAI a MATCH yn Excel. Gobeithio y bydd y tiwtorial yn ddefnyddiol i chi. Os ydych chi am archwilio mwy o awgrymiadau a thriciau Excel, os gwelwch yn dda cliciwch yma i gael mynediad at ein casgliad helaeth o dros filoedd o sesiynau tiwtorial.

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations