Note: The other languages of the website are Google-translated. Back to English
Mewngofnodi  \/ 
x
or
x
Cofrestru  \/ 
x

or

Sut i ddefnyddio MYNEGAI a MATCH gyda'i gilydd yn Excel

Wrth weithio gyda thablau Excel, efallai y byddwch chi'n dod o hyd i sefyllfaoedd y mae angen i chi edrych am werth yn gyson. Yn y tiwtorial hwn, byddwn yn dangos i chi sut i gymhwyso'r cyfuniad o swyddogaethau MYNEGAI a MATCH i wneud edrychiadau llorweddol a fertigol, edrychiadau dwyffordd, edrychiadau achos-sensitif, a'r edrychiadau sy'n cwrdd â meini prawf lluosog.

Beth mae swyddogaethau MYNEGAI a MATCH yn ei wneud yn Excel

Sut i ddefnyddio swyddogaethau MYNEGAI a MATCH gyda'i gilydd


Beth mae swyddogaethau MYNEGAI a MATCH yn ei wneud 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.

Defnydd o swyddogaeth INDEX yn Excel

Mae'r 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.
  • row_num (yn ofynnol, oni bai bod column_num yn bresennol) yn cyfeirio at rif rhes yr arae.
  • colofn_num (dewisol, ond yn ofynnol os hepgorir row_num) yn cyfeirio at rif colofn yr arae.

Er enghraifft, i wybod sgôr arholiad olaf Jeff, y 6ed myfyriwr ar y rhestr, gallwch ddefnyddio'r swyddogaeth MYNEGAI fel hyn:

= MYNEGAI (E2: E11, 6) >>> yn dychwelyd 60

cyfateb mynegai excel 01

√ Nodyn: Yr ystod E2: E11 yw lle mae'r arholiad terfynol wedi'i restru, tra bod y rhif 6 yn darganfod sgôr arholiad y 6th myfyriwr.

Yma, gadewch i ni wneud ychydig o brawf. Ar gyfer y fformiwla = MYNEGAI (B2: E2,3), pa werth fydd yn ei ddychwelyd? --- Bydd, bydd yn dychwelyd Tsieina, Y 3rd gwerth yn yr ystod benodol.

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 y wlad y daw Emily gydag MYNEGAI, gallwn leoli'r gwerth gyda rhif rhes o 8 a cholofn rhif 3 yn y celloedd trwy B2 i E11 fel hyn:

= MYNEGAI (B2: E11,8,3) >>> yn dychwelyd Tsieina

cyfateb mynegai excel 02

Yn ôl yr enghreifftiau uchod, am swyddogaeth INDEX yn Excel, dylech wybod:

  • Gall swyddogaeth INDEX weithio gydag ystodau fertigol a llorweddol.
  • Nid yw'r swyddogaeth INDEX yn sensitif i achos.
  • Mae rhif y rhes yn mynd o flaen rhif y golofn (os oes angen y ddau rif arnoch) yn fformiwla INDEX.

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.

Nawr, gadewch i ni ddysgu am hanfodion swyddogaeth MATCH yn gyntaf.


Defnydd o 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_array (gofynnol) yn cyfeirio at yr ystod o gelloedd lle rydych chi am i MATCH chwilio.
  • match_type (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 lookup_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 lookup_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 lookup_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", C2: C11,0) >>> yn dychwelyd 4

cyfateb mynegai excel 03

√ Sylwch: Nid yw'r swyddogaeth MATCH yn sensitif i achosion. Mae'r canlyniad “4” yn nodi bod yr enw “Vera” yn y 4ydd safle ar y rhestr. Yr “0” yn y fformiwla yw'r math paru a fydd yn dod o hyd i'r gwerth cyntaf yn yr arae edrych sy'n cyfateb yn union i'r gwerth edrych “Vera”.

I gwybod safle'r sgôr “96” yn y rhes o B2 i E2, gallwch ddefnyddio MATCH fel hyn:

= MATCH (96, B2: E2,0) >>> yn dychwelyd 4

cyfateb mynegai excel 04

☞ 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 hefyd.
  • Os yw gwerth edrych fformiwla MATCH ar ffurf testun, amgaewch ef mewn dyfyniadau.

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 ddefnyddio swyddogaethau MYNEGAI a MATCH gyda'i gilydd

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

Enghraifft i gyfuno MYNEGAI a MATCH

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

Er enghraifft, i wybod Sgôr arholiad olaf Evelyn, dylem ddefnyddio'r fformiwla:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> yn dychwelyd 90

cyfateb mynegai excel 05

Wel, gan y gallai'r fformiwla edrych yn gymhleth, gadewch i ni fynd trwy bob rhan ohoni.

cyfateb mynegai excel 06

Fel y gwelwch uchod, y mawr MYNEGAI mae'r fformiwla'n cynnwys tair dadl:

  • amrywiaeth: A2: D11 yn dweud wrth INDEX am ddychwelyd y gwerth paru o'r celloedd drwodd A2 i D11.
  • row_num: MATCH ("evelyn", B2: B11,0) yn dweud wrth MYNEGAI union res y gwerth.
  • Ynglŷn â fformiwla MATCH, gallwn ei egluro fel a ganlyn: dychwelyd safle'r gwerth cyntaf sy'n union yr un fath ag “evelyn” yn y celloedd o B2 i B11 mewn gwerth rhifol, sef 5.
  • colofn_num: MATCH ("arholiad terfynol", A1: D1,0) yn dweud wrth MYNEGAI union golofn y gwerth.
  • Ynglŷn â fformiwla MATCH, gallwn ei egluro fel a ganlyn: dychwelyd safle'r gwerth cyntaf sy'n union yr un fath ag “arholiad terfynol” yn y celloedd o A1 i D1 mewn gwerth rhifol, sef 4.

Felly, gallwch weld y fformiwla fawr mor syml â'r un a ddangoswyd gennym isod:

MYNEGAI (A2: D11,5,4)

Yn yr enghraifft, gwnaethom ddefnyddio gwerthoedd cod caled, "evelyn" ac "arholiad terfynol". Fodd bynnag, mewn fformiwla mor fawr, nid ydym eisiau gwerthoedd â chod caled gan y bydd yn rhaid i ni eu newid bob tro y byddwn yn chwilio am rywbeth newydd. Mewn amgylchiadau o'r fath, gallwn ddefnyddio cyfeiriadau celloedd i wneud y fformiwla'n ddeinamig fel hyn:

MYNEGAI (A2: D11,MATCH (G2, B2: B11,0),MATCH (F3, A1: D1,0))

cyfateb mynegai excel 07


MYNEGAI a MATCH i gymhwyso edrych chwith

Nawr, gadewch i ni ddweud bod angen i chi wybod dosbarth Evelyn, sut allwn ni ddefnyddio MYNEGAI a MATCH i wybod yr ateb? Os gwnaethoch dalu sylw, dylech sylwi bod y golofn ddosbarth ar ochr chwith y golofn enw, a'i bod y tu hwnt i allu swyddogaeth edrych bwerus Excel arall, y VLOOKUP.

Mewn gwirionedd, mae'r gallu edrych i'r chwith yn digwydd bod yn un o'r agweddau lle mae'r cyfuniad o MYNEGAI a MATCH yn well na VLOOKUP.

I gwybod Dosbarth Evelyn, y cyfan sy'n rhaid i chi ei wneud yw newid y gwerth yn y gell F3 i “Dosbarth”, a defnyddio'r un fformiwla ag a ddangosir uchod, bydd y swyddogaethau MYNEGAI a MATCH wedyn yn dweud yr ateb wrthych ar unwaith:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> yn dychwelyd A

cyfateb mynegai excel 08

Os oes gennych instalarwain Kutools ar gyfer Excel, ychwanegyn Excel proffesiynol a ddatblygwyd gan ein tîm, gallwch hefyd gymhwyso chwiliad chwith am werthoedd penodol gyda'i LOOKUP o'r Dde i'r Chwith nodwedd heb lawer o gliciau. I weithredu'r nodwedd, ewch i'r Kutools tab yn eich excel, darganfyddwch y Fformiwla grwp, a chlicio LOOKUP o'r Dde i'r Chwith ar y gwymplen o LOOKUP Super. Fe welwch flwch deialog naid fel hyn:

cyfateb mynegai excel 09

Cliciwch yma i gael camau concrit i gymhwyso'r nodwedd edrych chwith gyda Kutools ar gyfer Excel.


MYNEGAI a MATCH i gymhwyso edrych dwy ffordd

Nawr, a ydych chi'n gallu gwneud fformiwla cyfuniad INDEX a MATCH gyda gwerthoedd edrych deinamig i gymhwyso edrychiadau dwyffordd? Gadewch i ni ymarfer gwneud fformwlâu yn y celloedd G3, G4, a G5 fel y dangosir isod:

cyfateb mynegai excel 10

Dyma'r atebion:

Cell G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Cell G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Cell G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Nodyn: Ar ôl defnyddio'r fformwlâu, gallwch gael gwybodaeth unrhyw fyfyrwyr yn hawdd trwy newid yr enw yng nghell G2.


MYNEGAI a MATCH i gymhwyso edrychiad achos-sensitif

O'r enghreifftiau uchod, gwyddom nad yw'r swyddogaethau MYNEGAI a MATCH yn sensitif i achosion. Fodd bynnag, yn yr achosion mae angen eich fformiwla arnoch i wahaniaethu rhwng nodau llythrennau bach a llythrennau bach, gallwch ychwanegu EXACT swyddogaeth i'ch fformiwlâu fel hyn:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Nodyn: Mae hon yn fformiwla arae sy'n gofyn i chi fynd i mewn iddi Ctrl + Shift + Enter. Yna bydd pâr o fracedi cyrliog yn ymddangos yn y bar fformiwla.

Er enghraifft, i wybod Sgôr arholiad JIMMY, defnyddiwch y swyddogaethau fel hyn:

cyfateb mynegai excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> yn dychwelyd 86

Neu gallwch ddefnyddio cyfeiriadau celloedd:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> yn dychwelyd 86
Nodyn: Peidiwch ag anghofio ymuno â Ctrl + Shift + Enter.


MYNEGAI a MATCH i gymhwyso chwiliad gyda sawl maen prawf

Wrth ddelio â chronfa ddata fawr gyda sawl colofn a chapsiwn rhes, mae bob amser yn anodd dod o hyd i rywbeth sy'n cwrdd â sawl cyflwr. Yn yr achos hwn, gweler y fformiwla isod i edrych ar feini prawf lluosog:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Nodyn: Mae hon yn fformiwla arae sy'n gofyn i chi fynd i mewn iddi Ctrl + Shift + Enter. Yna bydd pâr o fracedi cyrliog yn ymddangos yn y bar fformiwla.

Er enghraifft, i ddod o hyd i'r sgôr arholiad olaf Coco Dosbarth A sy'n dod o India, mae'r fformiwla fel a ganlyn:

cyfateb mynegai excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> yn dychwelyd 88
√ Nodyn: Peidiwch ag anghofio ymuno â Ctrl + Shift + Enter.

Wel, beth os ydych chi'n anghofio ei ddefnyddio'n gyson Ctrl + Shift + Enter i gwblhau'r fformiwla fel bod y fformiwla'n dychwelyd canlyniadau anghywir? Yma mae gennym fformiwla fwy cymhleth, y gallwch chi ei chwblhau gydag un syml yn unig Rhowch allweddol:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Am yr un enghraifft uchod i ddod o hyd i'r sgôr arholiad olaf Coco Dosbarth A sy'n dod o India, y fformiwla sydd ond angen arferol Rhowch mae'r taro fel a ganlyn:

cyfateb mynegai excel 13

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

Yma ni fyddwn yn defnyddio gwerthoedd â chod caled, gan y byddwn ni eisiau fformiwla gyffredinol yn achos sawl maen prawf. Dim ond fel hyn, y gallwn yn hawdd gael y canlyniad yr ydym ei eisiau trwy newid y gwerthoedd yng nghelloedd G2, G3, G4 yn yr enghraifft uchod.

gyda Kutools ar gyfer Excel's Nodwedd Edrych Aml-gyflwr, gallwch edrych ar werthoedd penodol gyda meini prawf lluosog mewn ychydig gliciau. I weithredu'r nodwedd, ewch i'r Kutools tab yn eich excel, darganfyddwch y Fformiwla grwp, a chlicio Edrych Aml-gyflwr ar y gwymplen o LOOKUP Super. Yna fe welwch flwch deialog naid fel y dangosir isod:

cyfateb mynegai excel 14

Cliciwch yma i gael camau concrit i gymhwyso'r nodwedd edrych aml-gyflwr gyda Kutools ar gyfer Excel.


MYNEGAI a MATCH i gymhwyso edrych ar draws sawl colofn

Os oes gennym daenlen Excel gyda gwahanol golofnau yn rhannu un pennawd fel y dangosir isod, sut allwn ni baru enw pob myfyriwr gyda'i ddosbarth gyda MYNEGAI a MATCH?

cyfateb mynegai excel 15

Yma, gadewch imi ddangos i chi'r ffordd i gyflawni'r dasg gyda'n teclyn proffesiynol Kutools ar gyfer Excel. Gyda'i Cynorthwyydd Fformiwla, gallwch baru myfyrwyr â'u dosbarthiadau yn gyflym fel y camau a ddangosir isod:

1. Dewiswch y gell gyrchfan lle rydych chi am gymhwyso'r swyddogaeth.

2. O dan y Kutools tab, ewch i Cynorthwyydd Fformiwla, Cliciwch Cynorthwyydd Fformiwla ar y gwymplen.

cyfateb mynegai excel 16

3. Dewiswch Chwilio o Math Fformiwla, yna cliciwch ar Mynegai a chyfateb ar sawl colofn.

cyfateb mynegai excel 17

4. a. Cliciwch y 1af eicon paru mynegai excelbotwm ar ochr dde Edrych_col i ddewis y celloedd rydych chi am ddychwelyd gwerth ohonynt, hy enwau'r dosbarth. (Dim ond colofn neu res sengl y gallwch chi ei ddewis yma.)
    b. Cliciwch yr 2il eicon paru mynegai excelbotwm ar ochr dde Tabl_rng i ddewis y celloedd i gyd-fynd â'r gwerthoedd yn y rhai a ddewiswyd Edrych_col, hy, enwau'r myfyrwyr.
    c. Cliciwch y 3ydd eicon paru mynegai excelbotwm ar ochr dde Lookup_value i ddewis y gell i edrych arni, hy enw'r myfyriwr yr ydych am ei baru gyda'i ddosbarth.

cyfateb mynegai excel 18

5. Cliciwch Ok, fe welwch enw dosbarth Jimmy i'w weld yn y gell gyrchfan.

cyfateb mynegai excel 19

6. Nawr gallwch lusgo'r handlen llenwi i lawr i lenwi dosbarthiadau myfyrwyr eraill.

cyfateb mynegai excel 20

Cliciwch i lawrlwytho Kutools ar gyfer Excel i gael treial am ddim 30 diwrnod.



  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau a Cadw Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi Dyblyg a Swm / Cyfartaledd... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Fformiwlâu Hoff a Mewnosod yn Gyflym, Meysydd, Siartiau a Lluniau; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Grwpio Tabl Pivot yn ôl rhif wythnos, diwrnod o'r wythnos a mwy ... Dangos Celloedd Datgloi, wedi'u Cloi yn ôl gwahanol liwiau; Amlygu Celloedd sydd â Fformiwla / Enw...
tab kte 201905
  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.