Эмне үчүн INDEX жана MATCH Excelдеги VLOOKUP караганда жакшыраак

VLOOKUP (англисче VLOOKUP, аббревиатурасы “вертикалдуу издөө функциясы” дегенди билдирет) башталгычтарга мурдараак түшүндүрүп бергенбиз. Ал эми тажрыйбалуу колдонуучуларга дагы бир нече татаал формулалар көрсөтүлдү.

Ал эми бул макалада биз вертикалдуу издөө менен иштөөнүн дагы бир ыкмасы жөнүндө маалымат берүүгө аракет кылабыз.

Сиз: "Бул эмне үчүн керек?" деп ойлонуп жатышы мүмкүн. Жана бул бардык мүмкүн болгон издөө ыкмаларын көрсөтүү үчүн зарыл. Мындан тышкары, VLOOKUP көптөгөн чектөөлөр каалаган натыйжаны алууга тоскоолдук кылат. Бул жагынан алганда, INDEX( ) MATCH( ) алда канча функционалдык жана ар түрдүү жана аларда дагы азыраак чектөөлөр бар.

Негиздери INDEX MATCH

Бул колдонмонун максаты бул өзгөчөлүк канчалык жакшы экенин көрсөтүү болгондуктан, биз анын ишинин принциптерине байланыштуу негизги маалыматтарды карап көрөлү. Биз мисалдарды көрсөтөбүз, ошондой эле эмне үчүн VLOOKUP () караганда жакшыраак экенин карап чыгабыз.

INDEX функциясынын синтаксиси жана колдонулушу

Бул функция тилкенин же саптын номеринин негизинде көрсөтүлгөн издөө аймактарынын арасынан керектүү маанини табууга жардам берет. синтаксиси:

=INDEX(массив, сап номери, мамычанын номери):

• массив – издөө жүргүзүлө турган аймак;
• сап номери – көрсөтүлгөн массивде изделүүчү саптын номери. Эгерде саптын номери белгисиз болсо, тилкенин номери көрсөтүлүшү керек;
• мамычанын номери – көрсөтүлгөн массивде табыла турган тилкенин номери. Маани белгисиз болсо, саптын номери талап кылынат.

Жөнөкөй формуланын мисалы:

=ИНДЕКС(A1:S10,2,3)

Функция A1ден C10го чейинки диапазондо издейт. Сандар кайсы саптан (2) жана тилкеден (3) керектүү маани көрсөтүлөрүн көрсөтөт. Натыйжада C2 клеткасы болот.

Абдан жөнөкөй, туурабы? Бирок сиз чыныгы документтер менен иштегенде, сизде тилке номерлери же уячалар тууралуу маалымат болушу күмөн. MATCH() функциясы дал ушул үчүн.

MATCH функциясынын синтаксиси жана колдонулушу

MATCH() функциясы керектүү маанини издейт жана көрсөтүлгөн издөө аймагында анын болжолдуу санын көрсөтөт.

searchpos() синтаксиси төмөнкүдөй көрүнөт:

=MATCH(издөө үчүн маани, издөө үчүн массив, дал келген түрү)

• издөө мааниси – табыла турган сан же текст;
• изделген массив – издөө жүргүзүлө турган аймак;
• дал келүү түрү - так маанини же ага жакын маанилерди издөө керектигин көрсөтөт:
  • 1 (же маани көрсөтүлгөн эмес) – көрсөтүлгөн мааниге барабар же андан аз болгон эң чоң маанини кайтарат;
  • 0 – изделген мааниге так дал келүүнү көрсөтөт. INDEX() MATCH() комбинациясында дээрлик дайыма так дал келүү керек болот, ошондуктан биз 0 деп жазабыз;
  • -1 – Формулада көрсөтүлгөн мааниден чоң же барабар болгон эң кичине маанини көрсөтөт. Сорттоо кемүү тартибинде жүргүзүлөт.

Мисалы, В1:В3 диапазонунда Нью-Йорк, Париж, Лондон катталган. Төмөнкү формула 3 санын көрсөтөт, анткени Лондон тизмеде үчүнчү орунда:

=АЖЫРАТУУ(Лондон,B1:B3,0)

INDEX MATCH функциясы менен кантип иштөө керек 

Сиз, балким, бул функциялардын биргелешкен иши кандай принцип менен курулганын түшүнө баштагандырсыз. Кыскасы, анда INDEX() көрсөтүлгөн саптар менен мамычалардын арасынан керектүү маанини издейт. Жана MATCH() бул маанилердин сандарын көрсөтөт:

=INDEX(маани кайтарылган тилке, MATCH(издөө үчүн маани, издөө үчүн тилке, 0))

Анын кантип иштээрин түшүнүү дагы эле кыйын болуп жатабы? Балким, бир мисал жакшыраак түшүндүрөт. Сизде дүйнөлүк борборлордун жана алардын калкынын тизмеси бар дейли:

Белгилүү бир капиталдын, мисалы, Япониянын борборунун калкынын санын билүү үчүн төмөнкү формуланы колдонобуз:

=ИНДЕКС(C2:C10, MATCH(Япония, A2:A10,0))

Explanation:

• MATCH() функциясы A2:A10 массивинен “Япония” маанисин издейт жана 3 санын кайтарат, анткени Япония тизмедеги үчүнчү маани. 
• Бул көрсөткүч баратсап номери” INDEX() формуласында жана функцияга бул саптан маани басып чыгарууну айтат.

Ошентип, жогорудагы формула стандарттык формула болуп калат ИНДЕКС(C2:C10,3). Формула C2 уячаларынан C10го чейин издейт жана бул диапазондогу үчүнчү уячадан маалыматтарды кайтарат, башкача айтканда, C4, анткени артка эсептөө экинчи катардан башталат.

Шаардын атын формулага жазгыңыз келбей жатабы? Андан кийин аны каалаган уячага жазыңыз, F1 деп айтыңыз жана аны MATCH() формуласында шилтеме катары колдонуңуз. Жана сиз динамикалык издөө формуласы менен аяктайсыз:

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

Маанилүү! саптардын саны согуштук тизме INDEX() ичиндеги саптардын саны менен бирдей болушу керек массив деп эсептелет MATCH() ичинде, антпесе туура эмес жыйынтыкка ээ болосуз.

Бир аз күтө туруңуз, эмне үчүн VLOOKUP() формуласын колдонбойсуз?

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

 INDEX MATCHтин бардык татаалдыктарын түшүнүү үчүн убакытты текке кетирүүнүн эмне кереги бар?

Бул учурда, кайсы функцияны колдонуу маанилүү эмес. Бул INDEX() жана MATCH() функциялары кантип чогуу иштешерин түшүнүү үчүн бир гана мисал. Башка мисалдар VLOOKUP кубатсыз болгон учурларда бул функциялар эмнеге жөндөмдүү экенин көрсөтөт. 

INDEX MATCH же VLOOKUP

Кайсы издөө формуласын колдонууну чечип жатканда, көптөр INDEX() жана MATCH() VLOOKUPтан алда канча жогору экенине макул болушат. Бирок, көп адамдар дагы эле VLOOKUP() колдонушат. Биринчиден, VLOOKUP() жөнөкөй, экинчиден, колдонуучулар INDEX() жана MATCH() менен иштөөнүн бардык артыкчылыктарын толук түшүнүшпөйт. Бул билимсиз эч ким өз убактысын татаал системаны изилдөөгө коротууга макул болбойт.

Бул жерде VLOOKUP()га караганда INDEX() жана MATCH() негизги артыкчылыктары:

• Оңдон солго издөө. VLOOKUP() оңдон солго карай издей албайт, андыктан сиз издеп жаткан баалуулуктар дайыма таблицанын эң сол мамычаларында болушу керек. Бирок INDEX() жана MATCH() муну көйгөйсүз чече алат. Бул макалада бул иш жүзүндө кандай экенин айтып берет: сол жагында керектүү маанини кантип табууга болот.

1. Мамычаларды коопсуз кошуу же алып салуу. VLOOKUP() формуласы мамычаларды алып салууда же кошууда туура эмес натыйжаларды көрсөтөт, анткени VLOOKUP() ийгиликтүү болушу үчүн так мамычанын номерин талап кылат. Албетте, мамычалар кошулганда же алынып салынганда, алардын саны да өзгөрөт. 

Жана INDEX() жана MATCH() формулаларында жеке мамычалар эмес, мамычалардын диапазону көрсөтүлгөн. Натыйжада, ар бир жолу формуланы жаңыртпастан тилкелерди коопсуз кошуп жана алып салсаңыз болот.

1. Издөө көлөмү боюнча чектөөлөр жок. VLOOKUP() колдонууда издөө критерийлеринин жалпы саны 255 белгиден ашпашы керек, болбосо сиз #VALUE аласыз! Демек, эгер сиздин маалыматтарыңыз көп сандагы символдорду камтыса, INDEX() жана MATCH() эң жакшы вариант.
2. Жогорку иштетүү ылдамдыгы. Эгерде сиздин үстөлүңүз салыштырмалуу кичине болсо, анда сиз эч кандай айырманы байкабайсыз. Бирок, эгерде таблица жүздөгөн же миңдеген саптарды камтыса жана ошого жараша жүздөгөн жана миңдеген формулалар бар болсо, INDEX () жана MATCH () VLOOKUP () караганда алда канча тезирээк күрөшөт. Чынында, Excel бүт таблицаны иштетүүнүн ордуна, формулада көрсөтүлгөн тилкелерди гана иштетет. 

VLOOKUP() функциясынын эффективдүүлүгү, эгерде сиздин иш барагыңыз VLOOKUP() жана SUM() сыяктуу көп сандагы формулаларды камтыса, өзгөчө байкалат. Массивдеги ар бир маанини талдоо үчүн VLOOKUP() функцияларын өзүнчө текшерүү талап кылынат. Ошентип, Excel чоң көлөмдөгү маалыматты иштеп чыгышы керек жана бул ишти бир кыйла жайлатат.

Формула мисалдары 

Биз буга чейин бул функциялардын пайдалуулугун түшүндүк, ошондуктан биз эң кызыктуу бөлүгүнө: билимди практикада колдонууга өтө алабыз.

Оңдон солго издөө үчүн формула

Жогоруда айтылгандай, VLOOKUP издөөнүн бул түрүн аткара албайт. Демек, каалаган маанилер эң сол тилкеде болбосо, VLOOKUP() натыйжа бербейт. INDEX() жана MATCH() функциялары ар тараптуу жана баалуулуктардын жайгашкан жери алардын иштеши үчүн чоң роль ойнобойт.

Мисалы, биз таблицабыздын сол жагына даражалуу тилке кошуп, өлкөбүздүн борбору калкынын саны боюнча кайсы орунду ээлегенин аныктоого аракет кылабыз.

G1 уячасына биз табыла турган маанини жазабыз, андан кийин C1:C10 диапазонунда издөө үчүн төмөнкү формуланы колдонобуз жана A2:A10дон тиешелүү маанини кайтарабыз:

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

Prompt. Эгер сиз бул формуланы бир нече уячалар үчүн колдонууну пландап жатсаңыз, абсолюттук даректин жардамы менен диапазондорду оңдоңуз (мисалы, $A$2: $A$10 жана $C$2: 4C$10).

ИНДЕКС КӨБҮРӨК АЧЫКТАДЫ  тилкелерде жана саптарда издөө

Жогорудагы мисалдарда биз бул функцияларды VLOOKUP() үчүн алмаштыруу катары колдонуп, алдын ала аныкталган саптар диапазонунан маанилерди кайтардык. Бирок матрица же эки тараптуу издөө керек болсочу?

Бул татаал угулат, бирок мындай эсептөөлөр үчүн формула стандарттуу INDEX() MATCH() формуласына окшош, бир гана айырмасы бар: MATCH() формуласы эки жолу колдонулушу керек. Биринчи жолу саптын номерин алуу үчүн, экинчи жолу мамычанын номерин алуу үчүн:

=INDEX(массив, MATCH(тик издөө мааниси, издөө тилкеси, 0), MATCH(горизонталдуу издөө мааниси, издөө сабы, 0))

Келгиле, төмөнкү таблицага карап, формула түзүүгө аракет кылалы INDEX() EXPRESS() EXPRESS() тандалган жыл үчүн белгилүү бир өлкөдө демографияны көрсөтүү үчүн.

Максаттуу өлкө G1 уячасында (вертикалдуу издөө) жана максаттуу жыл G2 уячасында (горизонталдуу издөө). Формула мындай болот:

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

Бул формула кантип иштейт

Башка татаал формулалардай эле, аларды жеке теңдемелерге бөлүү аркылуу түшүнүү оңой. Ошондо сиз ар бир жеке функция эмне экенин түшүнө аласыз:

• МАТЧА(G1,A2:A11,0) – (G1) маанисин A2:A11 диапазонунан издейт жана бул маанинин санын көрсөтөт, биздин учурда 2;
• ИЗДОО(G2,B1:D1,0) – B2:D1 диапазонунда маанини (G1) издейт. Бул учурда, жыйынтыгы 3 болду.

Табылган сап жана мамычанын номерлери INDEX() формуласындагы тиешелүү мааниге жөнөтүлөт:

=ИНДЕКС(B2:D11,2,3)

Натыйжада, биз B2:D3 диапазонундагы 2 сап жана 11 мамычанын кесилишиндеги уячадагы мааниге ээ болдук. Ал эми формула D3 уячасында турган керектүү маанини көрсөтөт.

INDEX жана MATCH менен бир нече шарттар боюнча издеңиз

VLOOKUP() боюнча биздин колдонмону окуган болсоңуз, сиз бир нече издөө формулаларын сынап көргөнсүз. Бирок бул издөө ыкмасынын бир олуттуу чектөөсү бар - кошумча тилке кошуу зарылдыгы.

Бирок жакшы жаңылык ошол INDEX() жана MATCH() менен сиз иш барагыңызды түзөтүп же өзгөртпөстөн бир нече шарттарды издей аласыз.

Бул жерде INDEX() MATCH() үчүн көп шарттуу издөө формуласы:

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

Эскертүү: бул формула баскычтоптун жарлыгы менен бирге колдонулушу керек CTRL+SHIFT+ENTER.

Сиз 2 шарттын негизинде издеп жаткан бааны табышыңыз керек дейли: сатып алуучу и Продукт.

Бул төмөнкү формуланы талап кылат:

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

Бул формулада C2:C10 издөө боло турган диапазон, F1 - бул шарт, A2:A10 — шартты салыштыруу диапазону болуп саналат, F2 - 2-шарт, V2: V10 – 2-шартты салыштыруу диапазону.

Формула менен иштин аягында комбинацияны басууну унутпаңыз CTRL+SHIFT+ENTER – Excel мисалда көрсөтүлгөндөй, формуланы тармал кашаалар менен автоматтык түрдө жабат:

Эгерде сиз жумушуңуз үчүн массив формуласын колдонгуңуз келбесе, анда формулага башка INDEX() кошуп, ENTER баскычын басыңыз, ал мисалдагыдай болот:

Бул формулалар кантип иштейт

Бул формула стандарттык INDEX() MATCH() формуласы сыяктуу иштейт. Бир нече шарттарды издөө үчүн, сиз жөн гана туура жана туура эмес жеке шарттарды билдирген бир нече False жана True шарттарын түзөсүз. Анан бул шарттар массивдин бардык тиешелүү элементтерине тиешелүү. Формула False жана True аргументтерин 0 жана 1ге айлантат жана массивди чыгарат, мында 1 сапта табылган дал келген маанилер. MATCH() 1ге дал келген биринчи маанини таап, аны INDEX() формуласына өткөрүп берет. Жана ал, өз кезегинде, керектүү тилкеден көрсөтүлгөн сапта керектүү маанини кайтарып берет.

Массивсиз формула INDEX() функциясынын аларды өз алдынча иштетүү мүмкүнчүлүгүнөн көз каранды. Формуладагы экинчи INDEX() жалганга (0) дал келет, ошондуктан ал бардык массивди ошол маанилер менен MATCH() формуласына өткөрөт. 

Бул формуланын артында турган логиканын бир топ узун түшүндүрмөсү. Көбүрөөк маалымат алуу үчүн макаланы окуңуз "бир нече шарттар менен INDEX MATCH«.

INDEX жана MATCH боюнча AVERAGE, MAX жана MIN

Excel орточо, максимум жана минимумдарды табуу үчүн өзүнүн атайын функцияларына ээ. Бирок ошол баалуулуктар менен байланышкан клеткадан маалыматтарды алууну кааласаңызчы? Бул учурда AVERAGE, MAX жана MIN INDEX жана MATCH менен бирге колдонулушу керек.

INDEX MATCH жана MAX

D тилкесинде эң чоң маанини табуу жана аны С тилкесинде көрсөтүү үчүн формуланы колдонуңуз: 

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

INDEX MATCH жана MIN

D тилкесинде эң кичине маанини табуу жана аны С тилкесинде көрсөтүү үчүн төмөнкү формуланы колдонуңуз:

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

Издөө индекси жана ЖЫЛАН

D тилкесинде орточо маанини табуу жана бул маанини Сда көрсөтүү үчүн:

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

Сиздин берилиштериңиздин кантип жазылганына жараша, MATCH() үчүн үчүнчү аргумент 1, 0 же -1 болуп саналат:

• эгерде мамычалар өсүү тартибинде иреттелсе, 1ди коюңуз (анда формула орточо мааниден аз же ага барабар болгон максималдуу маанини эсептейт);
• эгерде сорт азайса, анда -1 (формула орточодон чоң же ага барабар болгон минималдуу маанини чыгарат);
• эгерде издөө массиви орточого так барабар маанини камтыса, анда аны 0гө коюңуз. 

 Биздин мисалда популяция азуу тартибинде иреттелген, ошондуктан биз -1 коёбуз. Натыйжада Токио, анткени калктын саны (13,189) орточо мааниге (000) эң жакын.

VLOOKUP() ошондой эле мындай эсептөөлөрдү жасай алат, бирок массив формуласы катары гана: ОРТОЧОН, MIN жана MAX менен VLOOKUP.

INDEX MATCH жана ESND/IFERROR

Эгер формула керектүү маанини таба албаса, ката кетирерин байкаган чыгарсыз # N / A. Сиз стандарттуу ката кабарын көбүрөөк маалымат берүүчү нерсе менен алмаштырсаңыз болот. Мисалы, формуладагы аргументти коюңуз XNUMX-да:

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

Бул формула менен, эгер сиз таблицада жок маалыматтарды киргизсеңиз, форма сизге көрсөтүлгөн билдирүүнү берет.

Эгерде сиз бардык каталарды кармагыңыз келсе, андан башкасы XNUMX-да колдонсо болот КАТА:

=ЭГЕРROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Бир жерден ката кетти!")

Бирок эсиңизде болсун, каталарды ушундай жол менен маскалоо жакшы идея эмес, анткени стандарттык каталар формуладагы бузууларды билдирет.

INDEX MATCH() функциясын колдонуу боюнча биздин колдонмо сизге пайдалуу болду деп үмүттөнөбүз.