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
- Enghraifft i gyfuno MYNEGAI a MATCH
- MYNEGAI a MATCH i gymhwyso edrych chwith
- MYNEGAI a MATCH i gymhwyso edrych dwy ffordd
- MYNEGAI a MATCH i gymhwyso edrychiad achos-sensitif
- MYNEGAI a MATCH i gymhwyso chwiliad gyda sawl maen prawf
- MYNEGAI a MATCH i gymhwyso edrych ar draws sawl colofn
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:
- 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
√ 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
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:
- 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
√ 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
☞ 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
Wel, gan y gallai'r fformiwla edrych yn gymhleth, gadewch i ni fynd trwy bob rhan ohoni.
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))
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
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:
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:
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:
√ 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:
=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:
√ 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:
=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:
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:
=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:
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?
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.
3. Dewiswch Chwilio o Math Fformiwla, yna cliciwch ar Mynegai a chyfateb ar sawl colofn.
4. a. Cliciwch y 1af 
botwm 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 botwm 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 botwm ar ochr dde Lookup_value i ddewis y gell i edrych arni, hy enw'r myfyriwr yr ydych am ei baru gyda'i ddosbarth.
5. Cliciwch Ok, fe welwch enw dosbarth Jimmy i'w weld yn y gell gyrchfan.
6. Nawr gallwch lusgo'r handlen llenwi i lawr i lenwi dosbarthiadau myfyrwyr eraill.
Yr Offer Cynhyrchedd Swyddfa Gorau
Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%
- 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...
- 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!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.
