Съдържание
Преди това обяснихме на начинаещите как да използват основните функции на VLOOKUP (на английски VLOOKUP, съкращението означава „вертикална функция за търсене“). И на опитни потребители бяха показани няколко по-сложни формули.
И в тази статия ще се опитаме да дадем информация за друг метод за работа с вертикално търсене.
Може би се чудите: „Защо е необходимо това?“. И това е необходимо, за да се покажат всички възможни методи за търсене. Освен това многобройните ограничения на VLOOKUP често възпрепятстват получаването на желания резултат. В това отношение INDEX( ) MATCH( ) е много по-функционален и разнообразен и те също имат по-малко ограничения.
Основи ИНДЕКС МАЧ
Тъй като целта на това ръководство е да покаже колко добра е тази функция, ние Нека да разгледаме основната информация относно принципите на неговата работа. И ние ще покажем примери, а също така ще помислим защо е по-добре от VLOOKUP ().
Синтаксис и използване на функцията 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)
Как се работи с функцията INDEX MATCH
Вероятно вече сте започнали да разбирате принципа, по който се изгражда съвместната работа на тези функции. Накратко тогава INDEX() търси желаната стойност сред посочените редове и колони. И MATCH() показва числата на тези стойности:
=INDEX(колона, от която се връща стойността, MATCH(стойност за търсене, колона за търсене, 0))
Все още ви е трудно да разберете как работи? Може би един пример ще обясни по-добре. Да предположим, че имате списък със световни столици и тяхното население:
За да разберем размера на населението на определена столица, например столицата на Япония, използваме следната формула:
=ИНДЕКС(C2:C10, MATCH(Япония, A2:A10,0))
Обяснение:
- Функцията MATCH() търси стойността – “Япония” в масива A2:A10 и връща числото 3, тъй като Япония е третата стойност в списъка.
- Тази цифра отива къмномер на реда” във формулата INDEX() и указва на функцията да отпечата стойност от този ред.
Така горната формула става стандартната формула ИНДЕКС(C2:C10,3;XNUMX). Формулата търси от клетки 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(). Без тези знания никой няма да се съгласи да прекарва времето си в изучаване на сложна система.
Ето основните предимства на INDEX() и MATCH() пред VLOOKUP():
- Търсете от дясно на ляво. VLOOKUP() не може да търси отдясно наляво, така че стойностите, които търсите, винаги трябва да са в най-левите колони на таблицата. Но INDEX() и MATCH() могат да се справят с това без проблем. Тази статия ще ви каже как изглежда на практика: как да намерите желаната стойност от лявата страна.
- Безопасно добавяне или премахване на колони. Формулата VLOOKUP() показва неправилни резултати при премахване или добавяне на колони, тъй като VLOOKUP() се нуждае от точния номер на колона, за да бъде успешна. Естествено, когато се добавят или премахват колони, техните номера също се променят.
И във формулите INDEX() и MATCH() се посочва диапазон от колони, а не отделни колони. В резултат на това можете безопасно да добавяте и премахвате колони, без да се налага да актуализирате формулата всеки път.
- Няма ограничения за обемите на търсене. Когато използвате VLOOKUP(), общият брой критерии за търсене не трябва да надвишава 255 знака или ще получите #VALUE! Така че, ако вашите данни съдържат голям брой знаци, 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).
ИНДЕКС ПОВЕЧЕ ЕКСПОЗИРАН ПОВЕЧЕ ЕКСПОЗИРАН за търсене в колони и редове
В горните примери сме използвали тези функции като заместител на VLOOKUP() за връщане на стойности от предварително дефиниран диапазон от редове. Но какво ще стане, ако трябва да направите матрично или двустранно търсене?
Звучи сложно, но формулата за такива изчисления е подобна на стандартната формула INDEX() MATCH(), само с една разлика: формулата MATCH() трябва да се използва два пъти. Първият път, за да получите номера на реда, а вторият път, за да получите номера на колоната:
=INDEX(масив, MATCH(стойност за вертикално търсене, колона за търсене, 0), MATCH(стойност за хоризонтално търсене, ред за търсене, 0))
Нека разгледаме таблицата по-долу и се опитаме да направим формула INDEX() EXPRESS() EXPRESS() за да покажете демографски данни в конкретна държава за избрана година.
Целевата държава е в клетка G1 (вертикално търсене), а целевата година е в клетка G2 (хоризонтално търсене). Формулата ще изглежда така:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Как работи тази формула
Както при всички други сложни формули, те са по-лесни за разбиране, като се разделят на отделни уравнения. И тогава можете да разберете какво прави всяка отделна функция:
- MATCH(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() ви е било полезно.