Pam mae INDEX a MATCH yn well na VLOOKUP yn Excel

Rydym wedi egluro i ddechreuwyr o'r blaen sut i ddefnyddio swyddogaethau sylfaenol VLOOKUP (Saesneg VLOOKUP, mae'r talfyriad yn sefyll am “fertical lookup function”). A dangoswyd nifer o fformiwlâu mwy cymhleth i ddefnyddwyr profiadol.

Ac yn yr erthygl hon byddwn yn ceisio rhoi gwybodaeth am ddull arall o weithio gyda chwilio fertigol.

Efallai eich bod yn pendroni: “Pam mae hyn yn angenrheidiol?”. Ac mae hyn yn angenrheidiol er mwyn dangos yr holl ddulliau chwilio posibl. Yn ogystal, mae nifer o gyfyngiadau VLOOKUP yn aml yn atal cael y canlyniad a ddymunir. Yn hyn o beth, mae MYNEGAI ( ) MATCH ( ) yn llawer mwy swyddogaethol ac amrywiol, ac mae ganddynt lai o gyfyngiadau hefyd.

Basics MYNEGAI MATCH

Gan mai pwrpas y canllaw hwn yw dangos pa mor dda yw'r nodwedd hon, rydym ni Edrychwn ar y wybodaeth sylfaenol am egwyddorion ei weithrediad. A byddwn yn dangos enghreifftiau, a hefyd yn ystyried pam, mae'n well na VLOOKUP ().

MYNEGAI Swyddogaeth Cystrawen a Defnydd

Mae'r swyddogaeth hon yn helpu i ddod o hyd i'r gwerth dymunol ymhlith yr ardaloedd chwilio penodedig yn seiliedig ar y golofn neu'r rhif llinell. Cystrawen:

= MYNEGAI (arae, rhif rhes, rhif colofn):

• arae – yr ardal lle bydd y chwiliad yn digwydd;
• rhif llinell - rhif y llinell i'w chwilio yn yr arae penodedig. Os yw rhif y rhes yn anhysbys, rhaid nodi rhif y golofn;
• rhif colofn - rhif y golofn sydd i'w chanfod yn yr arae penodedig. Os yw'r gwerth yn anhysbys, mae angen rhif llinell.

Enghraifft o fformiwla syml:

=MYNEGAI(A1:S10,2,3)

Bydd y swyddogaeth yn chwilio yn yr ystod o A1 i C10. Mae'r rhifau'n dangos o ba res (2) a cholofn (3) i ddangos y gwerth dymunol. Y canlyniad fydd cell C2.

Eithaf syml, iawn? Ond pan fyddwch chi'n gweithio gyda dogfennau go iawn, mae'n annhebygol y bydd gennych chi wybodaeth am rifau colofn neu gelloedd. Dyna beth yw pwrpas swyddogaeth MATCH().

MATCH Swyddogaeth Cystrawen a Defnydd

Mae'r ffwythiant MATCH() yn chwilio am y gwerth dymunol ac yn dangos ei rif yn fras yn yr ardal chwilio benodol.

Mae cystrawen searchpos() yn edrych fel hyn:

=MATCH(gwerth i chwilio, arae i chwilio, math paru)

• gwerth chwilio – y rhif neu'r testun i'w ganfod;
• arae a chwiliwyd – yr ardal lle cynhelir y chwiliad;
• math cyfatebol - yn nodi a ddylid chwilio am yr union werth neu'r gwerthoedd sydd agosaf ato:
  • 1 (neu ddim gwerth penodol) – yn dychwelyd y gwerth mwyaf sy'n hafal i neu'n llai na'r gwerth a nodwyd;
  • 0 – yn dangos cyfatebiaeth union gyda'r gwerth a chwiliwyd. Yn y cyfuniad MYNEGAI() MATCH() byddwch bron bob amser angen cyfatebiaeth union, felly rydym yn ysgrifennu 0;
  • -1 – Yn dangos y gwerth lleiaf sy'n fwy neu'n hafal i'r gwerth a nodir yn y fformiwla. Mae didoli yn cael ei wneud mewn trefn ddisgynnol.

Er enghraifft, yn yr ystod B1:B3 mae Efrog Newydd, Paris, Llundain wedi'u cofrestru. Bydd y fformiwla isod yn dangos y rhif 3 oherwydd bod Llundain yn drydydd ar y rhestr:

= EXPOSE (Llundain, B1: B3,0)

Sut i weithio gyda'r swyddogaeth INDEX MATCH 

Mae'n debyg eich bod eisoes wedi dechrau deall yr egwyddor a ddefnyddir i adeiladu cydweithio'r swyddogaethau hyn. Yn fyr, felly Mae MYNEGAI () yn chwilio am y gwerth dymunol ymhlith y rhesi a'r colofnau penodedig. Ac mae MATCH() yn dangos niferoedd y gwerthoedd hyn:

=INDEX(colofn y dychwelir y gwerth ohoni, MATCH(gwerth i chwilio, colofn i chwilio ynddi, 0))

Yn dal i gael amser caled yn deall sut mae'n gweithio? Efallai y bydd enghraifft yn esbonio'n well. Tybiwch fod gennych restr o brifddinasoedd y byd a'u poblogaeth:

Er mwyn darganfod maint poblogaeth cyfalaf penodol, er enghraifft, prifddinas Japan, rydym yn defnyddio'r fformiwla ganlynol:

= MYNEGAI(C2:C10, MATCH(Japan, A2:A10,0))

Eglurhad:

• Mae'r ffwythiant MATCH() yn edrych am y gwerth – “Japan” yn yr arae A2:A10 ac yn dychwelyd y rhif 3, oherwydd Japan yw'r trydydd gwerth yn y rhestr. 
• Mae'r ffigur hwn yn mynd irhif llinell” yn y fformiwla INDEX() ac yn dweud wrth y ffwythiant i argraffu gwerth o'r rhes hon.

Felly mae'r fformiwla uchod yn dod yn fformiwla safonol MYNEGAI(C2:C10,3). Mae'r fformiwla yn chwilio o gelloedd C2 i C10 ac yn dychwelyd data o'r drydedd gell yn yr ystod hon, hynny yw, C4, oherwydd bod y cyfrif i lawr yn dechrau o'r ail res.

Ddim eisiau rhagnodi enw'r ddinas yn y fformiwla? Yna ysgrifennwch ef mewn unrhyw gell, dywedwch F1, a defnyddiwch hi fel cyfeiriad yn y fformiwla MATCH(). Ac yn y pen draw bydd gennych fformiwla chwilio ddeinamig:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Pwysig! Nifer y llinellau yn amrywiaeth Rhaid i MYNEGAI() fod yr un fath â nifer y rhesi yn arae ystyrir yn MATCH(), fel arall byddwch yn cael y canlyniad anghywir.

Arhoswch funud, beth am ddefnyddio'r fformiwla VLOOKUP() yn unig?

=VLOOKUP(F1, A2:C10, 3, Gau)

 Beth yw pwynt gwastraffu amser yn ceisio darganfod yr holl gymhlethdodau hyn o MATCH MYNEGAI?

Yn yr achos hwn, nid oes ots pa swyddogaeth i'w defnyddio. Dim ond enghraifft yw hon i ddeall sut mae swyddogaethau MYNEGI() a MATCH() yn gweithio gyda'i gilydd. Bydd enghreifftiau eraill yn dangos yr hyn y mae'r swyddogaethau hyn yn gallu ei wneud mewn sefyllfaoedd lle mae'r VLOOKUP yn ddi-rym. 

MATCH MYNEGAI neu VLOOKUP

Wrth benderfynu pa fformiwla chwilio i'w defnyddio, mae llawer yn cytuno bod INDEX() a MATCH() yn llawer gwell na VLOOKUP. Fodd bynnag, mae llawer o bobl yn dal i ddefnyddio VLOOKUP(). Yn gyntaf, mae VLOOKUP() yn symlach, ac yn ail, nid yw defnyddwyr yn deall yn llawn yr holl fanteision o weithio gyda INDEX() a MATCH(). Heb y wybodaeth hon, ni fydd neb yn cytuno i dreulio eu hamser yn astudio system gymhleth.

Dyma fanteision allweddol INDEX() a MATCH() dros VLOOKUP():

• Chwiliwch o'r dde i'r chwith. Ni all VLOOKUP() chwilio o'r dde i'r chwith, felly mae'n rhaid i'r gwerthoedd yr ydych yn chwilio amdanynt fod yng ngholofnau mwyaf chwith y tabl bob amser. Ond gall INDEX() a MATCH() drin hyn heb broblem. Bydd yr erthygl hon yn dweud wrthych sut mae'n edrych yn ymarferol: sut i ddod o hyd i'r gwerth a ddymunir ar yr ochr chwith.

1. Ychwanegu neu dynnu colofnau'n ddiogel. Mae'r fformiwla VLOOKUP() yn dangos canlyniadau anghywir wrth dynnu neu ychwanegu colofnau oherwydd mae angen rhif union y golofn ar VLOOKUP() i fod yn llwyddiannus. Yn naturiol, pan fydd colofnau'n cael eu hychwanegu neu eu tynnu, mae eu niferoedd hefyd yn newid. 

Ac yn y fformiwlâu MYNEGAI () a MATCH(), mae ystod o golofnau wedi'i nodi, nid colofnau unigol. O ganlyniad, gallwch chi ychwanegu a thynnu colofnau'n ddiogel heb orfod diweddaru'r fformiwla bob tro.

1. Dim cyfyngiadau ar nifer y chwiliadau. Wrth ddefnyddio VLOOKUP(), ni ddylai cyfanswm y meini prawf chwilio fod yn fwy na 255 nod neu fe gewch #VALUE! Felly os yw'ch data'n cynnwys nifer fawr o nodau, INDEX() a MATCH() yw'r opsiwn gorau.
2. Cyflymder prosesu uchel. Os yw eich tablau'n gymharol fach, yna mae'n annhebygol y byddwch yn sylwi ar unrhyw wahaniaeth. Ond, os yw'r tabl yn cynnwys cannoedd neu filoedd o resi, ac, yn unol â hynny, mae cannoedd ar filoedd o fformiwlâu, bydd MYNEGAI () a MATCH () yn ymdopi'n llawer cyflymach na VLOOKUP (). Y ffaith yw y bydd Excel yn prosesu'r colofnau a nodir yn y fformiwla yn unig, yn hytrach na phrosesu'r tabl cyfan. 

Bydd effaith perfformiad VLOOKUP() yn arbennig o amlwg os yw eich taflen waith yn cynnwys nifer fawr o fformiwlâu fel VLOOKUP() a SUM(). Mae angen gwiriadau ar wahân o'r swyddogaethau VLOOKUP() i ddosrannu pob gwerth mewn arae. Felly mae'n rhaid i Excel brosesu llawer iawn o wybodaeth, ac mae hyn yn arafu'r gwaith yn sylweddol.

Enghreifftiau Fformiwla 

Rydym eisoes wedi cyfrifo defnyddioldeb y swyddogaethau hyn, felly gallwn symud ymlaen i'r rhan fwyaf diddorol: cymhwyso gwybodaeth yn ymarferol.

Fformiwla i chwilio o'r dde i'r chwith

Fel y soniwyd eisoes, ni all VLOOKUP berfformio'r math hwn o chwiliad. Felly, os nad yw'r gwerthoedd dymunol yn y golofn ar y chwith, ni fydd VLOOKUP() yn cynhyrchu canlyniad. Mae'r swyddogaethau INDEX() a MATCH() yn fwy amlbwrpas, ac nid yw lleoliad y gwerthoedd yn chwarae rhan fawr iddynt weithio.

Er enghraifft, byddwn yn ychwanegu colofn safle ar ochr chwith ein tabl ac yn ceisio darganfod pa safle o ran poblogaeth y mae prifddinas Ein Gwlad yn ei feddiannu.

Yng nghell G1, rydym yn ysgrifennu'r gwerth i'w ddarganfod, ac yna'n defnyddio'r fformiwla ganlynol i chwilio yn yr ystod C1:C10 a dychwelyd y gwerth cyfatebol o A2: A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Yn brydlon. Os ydych yn bwriadu defnyddio'r fformiwla hon ar gyfer celloedd lluosog, gwnewch yn siŵr eich bod yn trwsio'r ystodau gan ddefnyddio cyfeiriadau absoliwt (er enghraifft, $A$2: $A$10 a $C$2:4C$10).

MYNEGAI MWY DIOGELWCH MWY DIOGELWCH  i chwilio mewn colofnau a rhesi

Yn yr enghreifftiau uchod, rydym wedi defnyddio'r swyddogaethau hyn yn lle VLOOKUP() i ddychwelyd gwerthoedd o ystod rhagnodedig o resi. Ond beth os oes angen i chi wneud chwiliad matrics neu ddwy ochr?

Mae’n swnio’n gymhleth, ond mae’r fformiwla ar gyfer cyfrifiadau o’r fath yn debyg i’r fformiwla INDEX() MATCH() safonol, gyda dim ond un gwahaniaeth: rhaid defnyddio'r fformiwla MATCH() ddwywaith. Y tro cyntaf i gael rhif y rhes, a'r ail dro i gael rhif y golofn:

=INDEX(arae, MATCH(gwerth chwilio fertigol, colofn chwilio, 0), MATCH (gwerth chwilio llorweddol, rhes chwilio, 0))

Gadewch i ni edrych ar y tabl isod a cheisio gwneud fformiwla MYNEGAI() EXPRESS() EXPRESS() er mwyn arddangos demograffeg mewn gwlad benodol am flwyddyn ddethol.

Mae'r wlad darged yng nghell G1 (edrych fertigol) ac mae'r flwyddyn darged yng nghell G2 (edrych llorweddol). Bydd y fformiwla yn edrych fel hyn:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Sut mae'r fformiwla hon yn gweithio

Fel gydag unrhyw fformiwlâu cymhleth eraill, maent yn haws eu deall trwy eu rhannu'n hafaliadau unigol. Ac yna gallwch chi ddeall beth mae pob swyddogaeth unigol yn ei wneud:

• MATCH(G1,A2:A11,0) – yn edrych am werth (G1) yn yr ystod A2:A11 ac yn dangos rhif y gwerth hwn, yn ein hachos ni mae'n 2;
• CHWILIO(G2,B1:D1,0) – yn edrych am werth (G2) yn yr ystod B1:D1. Yn yr achos hwn, y canlyniad oedd 3.

Mae'r rhifau rhes a cholofn a ddarganfuwyd yn cael eu hanfon i'r gwerth cyfatebol yn y fformiwla INDEX():

=MYNEGAI(B2:D11,2,3)

O ganlyniad, mae gennym werth sydd mewn cell ar groesffordd 2 res a 3 cholofn yn yr ystod B2:D11. Ac mae'r fformiwla yn dangos y gwerth a ddymunir, sydd yng nghell D3.

Chwiliwch yn ôl amodau lluosog gyda INDEX a MATCH

Os ydych chi wedi darllen ein canllaw i VLOOKUP(), mae'n debyg eich bod wedi rhoi cynnig ar fformiwlâu chwilio lluosog. Ond mae gan y dull chwilio hwn un cyfyngiad sylweddol - yr angen i ychwanegu colofn ategol.

Ond y newyddion da yw hynny Gyda INDEX() a MATCH() gallwch chwilio am gyflyrau lluosog heb orfod golygu na newid eich taflen waith.

Dyma'r fformiwla chwilio aml-gyflwr cyffredinol ar gyfer INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Y nodyn: rhaid defnyddio'r fformiwla hon ynghyd â llwybr byr y bysellfwrdd CTRL+SHIFT+ENTER.

Tybiwch fod angen i chi ddod o hyd i'r gwerth rydych chi'n edrych amdano yn seiliedig ar 2 amod: Prynwr и Cynnyrch.

Mae hyn yn gofyn am y fformiwla ganlynol:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Yn y fformiwla hon, C2:C10 yw'r ystod y bydd y chwiliad yn digwydd ynddo, F1 - y cyflwr hwn, A2: A10 - yw'r ystod i gymharu'r cyflwr, F2 – amod 2, V2: V10 – ystod ar gyfer cymharu cyflwr 2.

Peidiwch ag anghofio pwyso'r cyfuniad ar ddiwedd y gwaith gyda'r fformiwla CTRL+SHIFT+ENTER - Bydd Excel yn cau'r fformiwla yn awtomatig gyda braces cyrliog, fel y dangosir yn yr enghraifft:

Os nad ydych am ddefnyddio fformiwla arae ar gyfer eich gwaith, yna ychwanegwch INDEX() arall at y fformiwla a gwasgwch ENTER, bydd yn edrych fel yn yr enghraifft:

Sut mae'r fformiwlâu hyn yn gweithio

Mae'r fformiwla hon yn gweithio yn yr un ffordd â'r fformiwla INDEX() MATCH() safonol. I chwilio am amodau lluosog, rydych chi'n creu amodau Gau a Gwir lluosog sy'n cynrychioli'r amodau unigol cywir ac anghywir. Ac yna mae'r amodau hyn yn berthnasol i holl elfennau cyfatebol yr arae. Mae'r fformiwla yn trosi'r dadleuon Gau a Gwir i 0 ac 1, yn y drefn honno, ac yn allbynnu arae lle mae 1 yn werthoedd cyfatebol a ddarganfuwyd yn y llinyn. Bydd MATCH() yn dod o hyd i'r gwerth cyntaf sy'n cyfateb i 1 ac yn ei drosglwyddo i'r fformiwla INDEX(). A bydd, yn ei dro, yn dychwelyd y gwerth a ddymunir eisoes yn y llinell benodedig o'r golofn a ddymunir.

Mae fformiwla heb arae yn dibynnu ar allu MYNEGAI() i'w trin ar ei phen ei hun. Mae'r ail INDEX() yn y fformiwla yn cyfateb i ffug (0), felly mae'n trosglwyddo'r arae gyfan gyda'r gwerthoedd hynny i fformiwla MATCH(). 

Mae hwn yn esboniad eithaf hir o'r rhesymeg y tu ôl i'r fformiwla hon. Am ragor o wybodaeth darllenwch yr erthygl “CYFATEB MYNEGAI â chyflyrau lluosog'.

CYFARTALEDD, MAX a MIN mewn MYNEGAI a MATCH

Mae gan Excel ei swyddogaethau arbennig ei hun ar gyfer dod o hyd i gyfartaleddau, uchafsymiau ac isafswm. Ond beth os ydych chi am gael data o'r gell sy'n gysylltiedig â'r gwerthoedd hynny? Yn yr achos hwn Rhaid defnyddio CYFARTALEDD, MAX a MIN ar y cyd â MYNEGAI a MATCH.

MYNEGAI MATCH a MAX

I ddod o hyd i'r gwerth mwyaf yng ngholofn D a'i ddangos yng ngholofn C, defnyddiwch y fformiwla: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

CYFATEB MYNEGAI a MIN

I ddod o hyd i'r gwerth lleiaf yng ngholofn D a'i ddangos yng ngholofn C, defnyddiwch y fformiwla ganlynol:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

MYNEGAI CHWILIO a SERPENT

I ddarganfod y gwerth cyfartalog yng ngholofn D ac arddangos y gwerth hwn yn C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Yn dibynnu ar sut y caiff eich data ei ysgrifennu, y drydedd ddadl i MATCH() yw naill ai 1, 0, neu -1:

• os yw'r colofnau wedi'u didoli mewn trefn esgynnol, gosodwch 1 (yna bydd y fformiwla yn cyfrifo'r gwerth mwyaf, sy'n llai na neu'n hafal i'r gwerth cyfartalog);
• os yw'r didoli yn disgyn, yna -1 (bydd y fformiwla yn allbynnu'r isafswm gwerth sy'n fwy na neu'n hafal i'r cyfartaledd);
• os yw'r arae chwilio yn cynnwys gwerth sy'n union hafal i'r cyfartaledd, yna gosodwch ef i 0. 

 Yn ein hesiampl ni, mae'r boblogaeth yn cael ei didoli mewn trefn ddisgynnol, felly rydyn ni'n rhoi -1. A'r canlyniad yw Tokyo, gan mai gwerth y boblogaeth (13,189) yw'r agosaf at y gwerth cyfartalog (000).

Gall VLOOKUP() hefyd wneud cyfrifiadau o'r fath, ond dim ond fel fformiwla arae: VLOOKUP gyda AVERAGE, MIN a MAX.

CYFATEB MYNEGAI ac ESND/IFERROR

Mae'n debyg eich bod eisoes wedi sylwi, os na all y fformiwla ddod o hyd i'r gwerth a ddymunir, mae'n taflu gwall # N / A. Gallwch ddisodli'r neges gwall safonol gyda rhywbeth mwy addysgiadol. Er enghraifft, gosodwch y ddadl yn y fformiwla Yn yr XNUMXeg:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Gyda'r fformiwla hon, os rhowch ddata nad yw yn y tabl, bydd y ffurflen yn rhoi'r neges benodol i chi.

Os ydych chi am ddal yr holl wallau, yna heblaw am Yn yr XNUMXeg gellir ei ddefnyddio IFERROR:

=IFERROR(MYNEGAI(C2:C10,MATCH(F1,A2:A10,0)), "Aeth rhywbeth o'i le!")

Ond cofiwch nad yw cuddio gwallau yn y modd hwn yn syniad da, oherwydd mae gwallau safonol yn adrodd am dorri'r fformiwla.

Gobeithiwn fod ein canllaw ar ddefnyddio'r swyddogaeth INDEX MATCH() yn ddefnyddiol i chi.