Зошто INDEX и MATCH се подобри од VLOOKUP во Excel

Претходно им објаснивме на почетниците како да ги користат основните функции на VLOOKUP (англиски VLOOKUP, кратенката значи „функција за вертикално пребарување“). И на искусните корисници им беа прикажани неколку покомплицирани формули.

И во оваа статија ќе се обидеме да дадеме информации за друг метод на работа со вертикално пребарување.

Можеби се прашувате: „Зошто е ова потребно?“. И ова е неопходно за да се прикажат сите можни методи за пребарување. Дополнително, бројните ограничувања на VLOOKUP често го спречуваат добивањето на посакуваниот резултат. Во овој поглед, 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 бидејќи Лондон е трет на листата:

=EXPOSE(Лондон,B1:B3,0)

Како да се работи со функцијата INDEX MATCH 

Веројатно веќе сте почнале да го разбирате принципот според кој се гради заедничката работа на овие функции. Накратко, тогаш INDEX() ја бара саканата вредност меѓу наведените редови и колони. И MATCH() ги прикажува броевите на овие вредности:

=INDEX(колона од која се враќа вредноста, MATCH(вредност за пребарување, колона за пребарување, 0))

Сè уште ви е тешко да разберете како функционира? Можеби еден пример ќе објасни подобро. Да претпоставиме дека имате список на светски метрополи и нивната популација:

За да ја дознаеме големината на населението на одреден главен град, на пример, главниот град на Јапонија, ја користиме следнава формула:

=INDEX(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 е немоќен. 

ПОСТАВУВАЊЕ НА ИНДЕКС или 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))

Како функционира оваа формула

Како и со сите други сложени формули, тие се полесно да се разберат со нивно разложување на поединечни равенки. И тогаш можете да разберете што прави секоја поединечна функција:

• MATCH(G1,A2:A11,0) – бара вредност (G1) во опсегот A2:A11 и го покажува бројот на оваа вредност, во нашиот случај тоа е 2;
• SEARCH(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(). 

Ова е прилично долго објаснување за логиката зад оваа формула. За повеќе информации прочитајте ја статијата “ПОСТАВУВАЊЕ НА ИНДЕКС со повеќе услови".

AVERAGE, MAX и MIN во INDEX и MATCH

Excel има свои посебни функции за наоѓање на просеци, максимални и минимуми. Но, што ако сакате да добиете податоци од ќелијата поврзана со тие вредности? Во овој случај AVERAGE, MAX и MIN мора да се користат заедно со INDEX и MATCH.

ПОСТАВУВАЊЕ НА ИНДЕКС и МАКС

За да ја пронајдете најголемата вредност во колоната D и да ја прикажете во колоната C, користете ја формулата: 

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

ПОСТАВУВАЊЕ НА ИНДЕКС и МИН

За да ја пронајдете најмалата вредност во колоната 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).

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().