Чаро INDEX ва MATCH аз VLOOKUP дар Excel беҳтаранд

Мо қаблан ба шурӯъкунандагон фаҳмонидем, ки чӣ тавр истифода бурдани вазифаҳои асосии 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))

Шарҳ:

• Функсияи 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, бардурӯғ)

 Барои фаҳмидани ин ҳама мураккабии INDEX MATCH вақтро беҳуда сарф кардан чӣ маъно дорад?

Дар ин ҳолат, муҳим нест, ки кадом функсия истифода шавад. Ин танҳо як мисолест барои фаҳмидани он ки чӣ тавр функсияҳои INDEX() ва MATCH() якҷоя кор мекунанд. Намунаҳои дигар нишон медиҳанд, ки ин функсияҳо дар ҳолатҳое, ки VLOOKUP беқувват аст, чӣ кор карда метавонанд. 

Мутобиқати INDEX ё VLOOKUP

Ҳангоми интихоби кадом формулаи ҷустуҷӯ, бисёриҳо розӣ ҳастанд, ки INDEX() ва MATCH() аз VLOOKUP хеле бартарӣ доранд. Аммо, бисёр одамон то ҳол VLOOKUP () -ро истифода мебаранд. Аввалан, VLOOKUP() соддатар аст ва дуюм, корбарон тамоми бартариҳои кор бо INDEX() ва MATCH()-ро пурра дарк намекунанд. Бе ин дониш касе розй нест, ки вакти худро ба омузиши системаи мураккаб сарф кунад.

Инҳоянд бартариҳои асосии INDEX() ва MATCH() нисбат ба VLOOKUP():

• Ҷустуҷӯ аз рост ба чап. 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))

Савол. Агар шумо ният доред, ки ин формуларо барои якчанд ячейка истифода баред, боварӣ ҳосил кунед, ки диапазонҳоро бо истифодаи суроғаи мутлақ ислоҳ кунед (масалан, $ 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) – арзиши (G2) дар диапазони B1:D1 меҷӯяд. Дар ин ҳолат, натиҷа 3 буд.

Рақамҳои сатр ва сутуни ёфтшуда ба арзиши мувофиқ дар формулаи INDEX() фиристода мешаванд:

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

Дар натиҷа, мо арзиш дорем, ки дар чашмак дар чорроҳаи 2 сатр ва 3 сутун дар диапазони B2:D11 ҷойгир аст. Ва формула арзиши дилхоҳро нишон медиҳад, ки дар чашмаки 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 -ро мутаносибан ба 0 ва 1 табдил медиҳад ва массиверо мебарорад, ки дар он 1 арзишҳои мувофиқе мебошад, ки дар сатр пайдо шудаанд. MATCH() арзиши аввалинеро, ки ба 1 мувофиқ аст, пайдо мекунад ва онро ба формулаи INDEX() мегузаронад. Ва он, дар навбати худ, арзиши аллакай дилхоҳро дар сатри муайяншуда аз сутуни дилхоҳ бармегардонад.

Формулаи бидуни массив аз қобилияти INDEX() барои мустақилона идора кардани онҳо вобаста аст. INDEX () дуюми формула ба бардурӯғ (0) мувофиқат мекунад, бинобар ин он тамоми массивро бо он арзишҳо ба формулаи MATCH() мегузаронад. 

Ин шарҳи хеле тӯлонии мантиқи паси ин формула аст. Барои маълумоти бештар мақоларо хонед "INDEX MATCH бо якчанд шартҳо".

AVERAGE, MAX ва MIN дар INDEX ва MATCH

Excel дорои вазифаҳои махсуси худро барои дарёфти миёна, ҳадди аксар ва ҳадди ақалл. Аммо агар шумо хоҳед, ки маълумотро аз чашмаки бо ин арзишҳо алоқаманд ба даст оред? Дар ин маврид AVERAGE, MAX ва MIN бояд дар якҷоягӣ бо INDEX ва MATCH истифода шаванд.

INDEX MATCH ва MAX

Барои дарёфти арзиши бузургтарин дар сутуни D ва нишон додани он дар сутуни C, формуларо истифода баред: 

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

INDEX MATCH ва MIN

Барои пайдо кардани арзиши хурдтарин дар сутуни D ва нишон додани он дар сутуни C, формулаи зеринро истифода баред:

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

ИНДЕКСИ Ҷустуҷӯ ва SERPENT

Барои дарёфти арзиши миёна дар сутуни D ва нишон додани ин арзиш дар C:

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

Вобаста аз тарзи навиштани маълумоти шумо, далели сеюм ба MATCH() ё 1, 0 ё -1 аст:

• агар сутунҳо бо тартиби афзоиш ҷудо карда шаванд, 1-ро муқаррар кунед (он гоҳ формула арзиши максималиро ҳисоб мекунад, ки аз арзиши миёна камтар ё баробар аст);
• агар навъ коҳишёбанда бошад, пас -1 (формула арзиши ҳадди ақалро, ки аз ҳисоби миёна бузургтар ё баробар аст, мебарорад);
• агар массиви ҷустуҷӯ дорои арзише бошад, ки комилан ба ҳисоби миёна баробар бошад, пас онро ба 0 таъин кунед. 

 Дар мисоли мо, аҳолӣ бо тартиби кам ҷудо карда шудааст, бинобар ин мо -1-ро мегузорем. Ва натиҷа Токио аст, зеро арзиши аҳолӣ (13,189) ба арзиши миёна (000) наздиктарин аст.

VLOOKUP() инчунин метавонад чунин ҳисобҳоро иҷро кунад, аммо танҳо ҳамчун формулаи массив: VLOOKUP бо AVERAGE, MIN ва MAX.

INDEX MATCH ва ESND/IFERROR

Эҳтимол шумо аллакай пай бурдед, ки агар формула арзиши дилхоҳро пайдо карда натавонад, он хато содир мекунад # Н / А. Шумо метавонед паёми хатогии стандартиро бо чизи бештар иттилоотӣ иваз кунед. Масалан, аргументро дар формула таъин кунед Дар XNUMX:

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

Бо ин формула, агар шумо маълумотеро, ки дар ҷадвал мавҷуд нестанд, ворид кунед, форма ба шумо паёми муайяншударо медиҳад.

Агар шумо хоҳед, ки ҳамаи хатогиҳоро ба даст оред, ба истиснои Дар XNUMX мумкин аст истифода шаванд ХАТО:

=ХАТО(ИНДЕКС(C2:C10,MATCH(F1,A2:A10,0)), "Чизе хато кард!")

Аммо дар хотир доред, ки никоб кардани хатогиҳо бо ин роҳ фикри хуб нест, зеро хатогиҳои стандартӣ дар бораи вайронкуниҳо дар формула хабар медиҳанд.

Умедворем, ки дастури мо барои истифодаи функсияи INDEX MATCH() муфид буд.