змест
Раней мы тлумачылі пачаткоўцам, як карыстацца асноўнымі функцыямі VLOOKUP (англ. VLOOKUP, абрэвіятура расшыфроўваецца як «функцыя вертыкальнага пошуку»). А дасведчаным карыстальнікам было паказана некалькі больш складаных формул.
А ў гэтым артыкуле мы паспрабуем даць інфармацыю аб іншым спосабе працы з вертыкальным пошукам.
Вы можаце задацца пытаннем: «Навошта гэта трэба?». А гэта трэба для таго, каб паказаць усе магчымыя метады пошуку. Акрамя таго, шматлікія абмежаванні VLOOKUP часта перашкаджаюць атрымаць жаданы вынік. У гэтым плане INDEX( ) MATCH( ) нашмат больш функцыянальны і разнастайны, і яны таксама маюць менш абмежаванняў.
Асновы INDEX MATCH
Паколькі мэта гэтага кіраўніцтва - паказаць, наколькі добрая гэтая функцыя, мы Давайце разгледзім асноўныя звесткі, якія тычацца прынцыпаў яго працы. І мы пакажам прыклады, а таксама разгледзім, чаму, гэта лепш VLOOKUP ().
Сінтаксіс і выкарыстанне функцыі INDEX
Гэтая функцыя дапамагае знайсці патрэбнае значэнне сярод зададзеных абласцей пошуку па нумары слупка або радка. сінтаксіс:
=INDEX(масіў, нумар радка, нумар слупка):
- масіў – вобласць, у якой будзе праводзіцца пошук;
- нумар радка – нумар радка, які шукаецца ў паказаным масіве. Калі нумар радка невядомы, неабходна ўказаць нумар слупка;
- нумар слупка – нумар слупка, які трэба знайсці ў паказаным масіве. Калі значэнне невядомае, патрабуецца нумар радка.
Прыклад простай формулы:
=ІНДЭКС(A1:S10,2,3;XNUMX;XNUMX)
Функцыя будзе шукаць у дыяпазоне ад A1 да C10. Лічбы паказваюць, з якога радка (2) і слупка (3) паказаць патрэбнае значэнне. У выніку атрымаецца ячэйка C2.
Даволі проста, праўда? Але калі вы працуеце з рэальнымі дакументамі, вы наўрад ці будзеце мець інфармацыю пра нумары слупкоў або вочак. Вось для чаго прызначана функцыя MATCH().
Сінтаксіс і выкарыстанне функцыі MATCH
Функцыя MATCH() шукае патрэбнае значэнне і паказвае яго прыблізны лік у зададзенай вобласці пошуку.
Сінтаксіс searchpos() выглядае так:
=MATCH(значэнне для пошуку, масіў для пошуку, тып супадзення)
- пошукавае значэнне – лік або тэкст, якія трэба знайсці;
- шуканы масіў – вобласць, дзе будзе праводзіцца пошук;
- тып супадзення - вызначае, шукаць дакладнае значэнне або найбольш блізкія да яго значэння:
- 1 (або значэнне не вызначана) – вяртае найбольшае значэнне, роўнае або меншае за зададзенае значэнне;
- 0 – паказвае дакладнае супадзенне з шуканым значэннем. У камбінацыі INDEX() MATCH() амаль заўсёды спатрэбіцца дакладнае супадзенне, таму пішам 0;
- -1 – Паказвае найменшае значэнне, якое больш або роўна значэнню, указанаму ў формуле. Сартаванне вядзецца па змяншэнні.
Напрыклад, у дыяпазоне B1:B3 прапісаны Нью-Ёрк, Парыж, Лондан. Формула ніжэй пакажа лічбу 3, таму што Лондан займае трэцяе месца ў спісе:
=EXPOSE(Лондан,B1:B3,0;XNUMX)
Як працаваць з функцыяй INDEX MATCH
Напэўна, вы ўжо пачалі разумець, па якім прынцыпе будуецца сумесная праца гэтых функцый. Карацей, значыць INDEX() шукае патрэбнае значэнне сярод зададзеных радкоў і слупкоў. І MATCH() паказвае нумары гэтых значэнняў:
=INDEX(слупок, з якога вяртаецца значэнне, MATCH(значэнне для пошуку, слупок для пошуку, 0))
Вам усё яшчэ цяжка зразумець, як гэта працуе? Можа быць, прыклад растлумачыць лепш. Выкажам здагадку, што ў вас ёсць спіс сусветных сталіц і іх насельніцтва:
Каб высветліць колькасць насельніцтва той ці іншай сталіцы, напрыклад, сталіцы Японіі, скарыстаемся наступнай формулай:
=INDEX(C2:C10, MATCH(Японія, A2:A10,0;XNUMX))
Тлумачэнне:
- Функцыя MATCH() шукае значэнне «Японія» ў масіве A2:A10 і вяртае лік 3, таму што Японія з'яўляецца трэцім значэннем у спісе.
- Гэтая лічба ідзе данумар радка» у формуле INDEX() і загадвае функцыі надрукаваць значэнне з гэтага радка.
Такім чынам, прыведзеная вышэй формула становіцца стандартнай формулай INDEX(C2:C10,3;XNUMX). Формула шукае ад ячэек C2 да C10 і вяртае даныя з трэцяй ячэйкі гэтага дыяпазону, гэта значыць C4, таму што адлік пачынаецца з другога радка.
Не хочаце прапісваць назву горада ў формуле? Затым запішыце яго ў любую ячэйку, скажам, F1, і выкарыстоўвайце ў якасці спасылкі ў формуле MATCH(). І вы атрымаеце формулу дынамічнага пошуку:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Важна! Колькасць радкоў у масіў INDEX() павінен супадаць з колькасцю радкоў у разглядаецца масіў у MATCH(), інакш вы атрымаеце няправільны вынік.
Пачакайце, чаму б проста не выкарыстоўваць формулу VLOOKUP()?
=ВПР(F1, A2:C10, 3, ілжыва)
Які сэнс марнаваць час, спрабуючы высветліць усе гэтыя складанасці INDEX MATCH?
У гэтым выпадку не мае значэння, якую функцыю выкарыстоўваць. Гэта толькі прыклад, каб зразумець, як функцыі INDEX() і MATCH() працуюць разам. Іншыя прыклады пакажуць, на што здольныя гэтыя функцыі ў сітуацыях, калі VLOOKUP бяссільны.
INDEX MATCH або VLOOKUP
Вырашаючы, якую формулу пошуку выкарыстоўваць, многія згодныя з тым, што INDEX() і MATCH() значна лепш VLOOKUP. Аднак многія людзі ўсё яшчэ выкарыстоўваюць VLOOKUP(). Па-першае, VLOOKUP() прасцей, а па-другое, карыстальнікі не да канца разумеюць усе перавагі працы з INDEX() і MATCH(). Без гэтых ведаў ніхто не пагодзіцца марнаваць свой час на вывучэнне складанай сістэмы.
Вось ключавыя перавагі INDEX() і MATCH() перад VLOOKUP():
- Пошук справа налева. VLOOKUP() не можа шукаць справа налева, таму значэнні, якія вы шукаеце, заўсёды павінны быць у крайніх левых слупках табліцы. Але INDEX() і MATCH() могуць справіцца з гэтым без праблем. Як гэта выглядае на практыцы: як знайсці шуканае значэнне злева, раскажа гэты артыкул.
- Бяспечнае даданне або выдаленне слупкоў. Формула VLOOKUP() паказвае няправільныя вынікі пры выдаленні або даданні слупкоў, таму што VLOOKUP() патрабуе дакладнага нумара слупка, каб быць паспяховым. Натуральна, пры даданні або выдаленні слупкоў іх колькасць таксама змяняецца.
А ў формулах INDEX() і MATCH() задаецца дыяпазон слупкоў, а не асобныя слупкі. У выніку вы можаце бяспечна дадаваць і выдаляць слупкі без неабходнасці кожны раз абнаўляць формулу.
- Няма абмежаванняў на аб'ём пошуку. Пры выкарыстанні VLOOKUP() агульная колькасць крытэрыяў пошуку не павінна перавышаць 255 сімвалаў, інакш вы атрымаеце #ЗНАЧЭННЕ! Такім чынам, калі вашы даныя ўтрымліваюць вялікую колькасць сімвалаў, INDEX() і MATCH() - лепшы варыянт.
- Высокая хуткасць апрацоўкі. Калі вашы сталы адносна невялікія, то вы наўрад ці заўважыце якую-небудзь розніцу. Але, калі табліца змяшчае сотні ці тысячы радкоў, і, адпаведна, ёсць сотні і тысячы формул, 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).
INDEX БОЛЬШ АДКРЫТЫ БОЛЬШ АДКРЫТАНЫ для пошуку ў слупках і радках
У прыведзеных вышэй прыкладах мы выкарыстоўвалі гэтыя функцыі ў якасці замены 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;XNUMX) – шукае значэнне (G1) у дыяпазоне A2:A11 і паказвае нумар гэтага значэння, у нашым выпадку гэта 2;
- ПОШУК(G2;B1:D1,0;XNUMX) – шукае значэнне (G2) у дыяпазоне B1:D1. У гэтым выпадку вынік быў 3.
Знойдзеныя нумары радкоў і слупкоў перадаюцца ў адпаведнае значэнне ў формуле INDEX():
=ІНДЭКС(B2:D11,2,3;XNUMX;XNUMX)
У выніку мы маем значэнне, якое знаходзіцца ў вочку на скрыжаванні 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, якія прадстаўляюць правільныя і няправільныя асобныя ўмовы. І тады гэтыя ўмовы прымяняюцца да ўсіх адпаведных элементаў масіва. Формула пераўтворыць аргументы False і True у 0 і 1 адпаведна і выводзіць масіў, дзе 1 - гэта адпаведныя значэнні, знойдзеныя ў радку. MATCH() знойдзе першае значэнне, якое адпавядае 1, і перадасць яго ў формулу INDEX(). А яно, у сваю чаргу, верне ўжо патрэбнае значэнне ў названай радку з патрэбнага слупка.
Формула без масіва залежыць ад здольнасці INDEX() апрацоўваць іх самастойна. Другі INDEX() у формуле супадае з false (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))
ІНДЭКС ПОШУКУ і ЗМЯЯ
Каб знайсці сярэдняе значэнне ў слупку D і адлюстраваць гэта значэнне ў C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
У залежнасці ад таго, як запісваюцца даныя, трэці аргумент MATCH() роўны 1, 0 ці -1:
- калі слупкі адсартаваныя па ўзрастанні, усталюйце 1 (тады формула вылічыць максімальнае значэнне, якое менш або роўна сярэдняму);
- калі сартаванне па змяншэнні, то -1 (формула вывядзе мінімальнае значэнне, якое больш або роўна сярэдняму);
- калі пошукавы масіў утрымлівае значэнне, дакладна роўнае сярэдняму, то ўсталюйце яго ў 0.
У нашым прыкладзе сукупнасць адсартавана ў парадку змяншэння, таму мы ставім -1. І вынік — Токіо, бо значэнне насельніцтва (13,189 000) бліжэйшае да сярэдняга значэння (XNUMX).
VLOOKUP() таксама можа выконваць такія вылічэнні, але толькі ў выглядзе формулы масіва: VLOOKUP з AVERAGE, MIN і MAX.
INDEX MATCH і ESND/IFERROR
Вы, напэўна, ужо заўважылі, што калі формула не можа знайсці патрэбнае значэнне, яна выдае памылку # N / A. Вы можаце замяніць стандартнае паведамленне пра памылку чымсьці больш інфарматыўным. Напрыклад, задайце аргумент у формуле У XNUMX-м:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
З дапамогай гэтай формулы, калі вы ўводзіце дадзеныя, якіх няма ў табліцы, форма выдасць вам указанае паведамленне.
Калі вы хочаце выявіць усе памылкі, то за выключэннем У XNUMX-м можа быць выкарыстаны КАЛІПАМЫЛКА:
=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), «Нешта пайшло не так!»)
Але памятайце, што маскіраваць памылкі такім чынам не вельмі добра, таму што стандартныя памылкі паведамляюць аб парушэннях у формуле.
Мы спадзяемся, што наша кіраўніцтва па выкарыстанні функцыі INDEX MATCH() было вам карысным.