Зашто су ИНДЕКС и МАТЦХ бољи од ВЛООКУП-а у Екцел-у

Раније смо објаснили почетницима како да користе основне функције ВЛООКУП-а (енглески ВЛООКУП, скраћеница значи „вертицал лоокуп фунцтион”). А искусним корисницима је приказано неколико компликованијих формула.

И у овом чланку ћемо покушати да дамо информације о још једном методу рада са вертикалном претрагом.

Можда се питате: „Зашто је ово потребно?“. А ово је неопходно како би се приказали сви могући начини претраживања. Поред тога, бројна ограничења ВЛООКУП-а често спречавају постизање жељеног резултата. У том погледу, ИНДЕКС() МАТЦХ() је много функционалнији и разноврснији, а такође имају мање ограничења.

Основе ИНДЕКС МАТЦХ

Пошто је сврха овог водича да покаже колико је ова функција добра, ми Погледајмо основне информације у вези са принципима његовог рада. И показаћемо примере, а такође и размотрити зашто је то боље од ВЛООКУП ().

Синтакса и употреба функције ИНДЕКС

Ова функција помаже у проналажењу жељене вредности међу наведеним областима претраге на основу броја колоне или реда. Синтакса:

=ИНДЕКС(низ, број реда, број колоне):

• низ – област у којој ће се вршити претрага;
• број реда – број реда који се тражи у наведеном низу. Ако је број реда непознат, мора се навести број колоне;
• број колоне – број колоне која се налази у наведеном низу. Ако је вредност непозната, потребан је број реда.

Пример једноставне формуле:

=ИНДЕКС(А1:С10,2,3)

Функција ће тражити у опсегу од А1 до Ц10. Бројеви показују из којег реда (2) и колоне (3) треба приказати жељену вредност. Резултат ће бити ћелија Ц2.

Прилично једноставно, зар не? Али када радите са стварним документима, мало је вероватно да ћете имати информације о бројевима колона или ћелијама. Томе служи функција МАТЦХ().

Синтакса и употреба функције МАТЦХ

Функција МАТЦХ() тражи жељену вредност и приказује њен приближни број у наведеној области претраге.

Сеарцхпос() синтакса изгледа овако:

=МАТЦХ(вредност за тражење, низ за тражење, тип подударања)

• вредност претраге – број или текст који треба пронаћи;
• сеарцхед арраи – област у којој ће се извршити претрага;
• тип подударања – одређује да ли треба тражити тачну вредност или вредности које су јој најближе:
  • 1 (или није наведена вредност) – враћа највећу вредност која је једнака или мања од вредности која је наведена;
  • 0 – приказује тачно подударање са траженом вредношћу. У комбинацији ИНДЕКС() МАТЦХ() ће вам скоро увек бити потребно тачно подударање, тако да пишемо 0;
  • -1 – Приказује најмању вредност која је већа или једнака вредности наведеној у формули. Сортирање се врши у опадајућем редоследу.

На пример, у опсегу Б1:Б3 регистровани су Њујорк, Париз, Лондон. Формула испод ће показати број 3 јер је Лондон трећи на листи:

=ЕКСПОСЕ(Лондон,Б1:Б3,0)

Како радити са функцијом ИНДЕКС МАТЦХ 

Вероватно сте већ почели да схватате принцип по коме се гради заједнички рад ових функција. Укратко, онда ИНДЕКС() тражи жељену вредност међу наведеним редовима и колонама. А МАТЦХ() показује бројеве ових вредности:

=ИНДЕКС(колона из које се враћа вредност, МАТЦХ(вредност за претрагу, колона за претрагу, 0))

И даље вам је тешко да разумете како то функционише? Можда ће неки пример боље објаснити. Претпоставимо да имате списак светских престоница и њиховог становништва:

Да бисмо сазнали величину становништва одређеног главног града, на пример, главног града Јапана, користимо следећу формулу:

=ИНДЕКС(Ц2:Ц10, ПОДРЖИ (Јапан, А2:А10,0))

objašnjenje:

• Функција МАТЦХ() тражи вредност – „Јапан” у низу А2:А10 и враћа број 3, јер је Јапан трећа вредност на листи. 
• Ова цифра иде наброј линије” у формули ИНДЕКС() и говори функцији да одштампа вредност из овог реда.

Дакле, горња формула постаје стандардна формула ИНДЕКС(Ц2:Ц10,3). Формула претражује од ћелија Ц2 до Ц10 и враћа податке из треће ћелије у овом опсегу, односно Ц4, јер одбројавање почиње од другог реда.

Не желите да пропишете име града у формули? Затим га упишите у било коју ћелију, рецимо Ф1, и користите га као референцу у формули МАТЦХ(). И на крају ћете добити формулу за динамичку претрагу:

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

Важно! Број линија у поредак ИНДЕКС() мора бити исти као број редова у сматрани низ у МАТЦХ(), иначе ћете добити погрешан резултат.

Чекајте мало, зашто једноставно не користите формулу ВЛООКУП()?

=ВЛООКУП(Ф1, А2:Ц10, 3, нетачно)

 Која је сврха губљења времена покушавајући да схватимо све ове сложености ИНДЕКС МАТЦХ-а?

У овом случају, није важно коју функцију користити. Ово је само пример за разумевање како функције ИНДЕКС() и МАТЦХ() раде заједно. Други примери ће показати шта су ове функције способне у ситуацијама када је ВЛООКУП немоћан. 

ИНДЕКС МАТЦХ или ВЛООКУП

Када одлучују коју формулу за претрагу да користе, многи се слажу да су ИНДЕКС() и МАТЦХ() знатно супериорнији од ВЛООКУП-а. Међутим, многи људи и даље користе ВЛООКУП(). Прво, ВЛООКУП() је једноставнији, а друго, корисници не разумеју у потпуности све предности рада са ИНДЕКС() и МАТЦХ(). Без овог знања, нико неће пристати да троши своје време проучавајући сложен систем.

Ево кључних предности ИНДЕКС() и МАТЦХ() у односу на ВЛООКУП():

• Тражи с десна на лево. ВЛООКУП() не може да тражи с десна на лево, тако да вредности које тражите увек морају бити у крајњим левим колонама табеле. Али ИНДЕКС() и МАТЦХ() могу ово да поднесу без проблема. Овај чланак ће вам рећи како то изгледа у пракси: како пронаћи жељену вредност на левој страни.

1. Безбедно додавање или уклањање колона. Формула ВЛООКУП() показује нетачне резултате приликом уклањања или додавања колона јер је ВЛООКУП() потребан тачан број колоне да би био успешан. Наравно, када се колоне додају или уклањају, њихов број се такође мења. 

А у формулама ИНДЕКС() и МАТЦХ() наведен је опсег колона, а не појединачне колоне. Као резултат тога, можете безбедно да додајете и уклањате колоне без потребе да сваки пут ажурирате формулу.

1. Нема ограничења у обиму претраге. Када користите ВЛООКУП(), укупан број критеријума за претрагу не сме бити већи од 255 карактера или ћете добити #ВРЕДНОСТ! Дакле, ако ваши подаци садрже велики број знакова, ИНДЕКС() и МАТЦХ() су најбоља опција.
2. Велика брзина обраде. Ако су ваши столови релативно мали, мало је вероватно да ћете приметити било какву разлику. Али, ако табела садржи стотине или хиљаде редова, и, сходно томе, постоје стотине и хиљаде формула, ИНДЕКС () и МАТЦХ () ће се носити много брже од ВЛООКУП (). Чињеница је да ће Екцел обрадити само колоне наведене у формули, уместо да обрађује целу табелу. 

Утицај функције ВЛООКУП() на перформансе биће посебно приметан ако ваш радни лист садржи велики број формула као што су ВЛООКУП() и СУМ(). За рашчлањивање сваке вредности у низу потребне су посебне провере функција ВЛООКУП(). Дакле, Екцел мора да обради огромну количину информација, а то значајно успорава рад.

Примери формуле 

Употребност ових функција смо већ схватили, па можемо да пређемо на најзанимљивији део: примену знања у пракси.

Формула за претрагу с десна на лево

Као што је већ поменуто, ВЛООКУП не може да изврши овај облик претраге. Дакле, ако жељене вредности нису у крајњој левој колони, ВЛООКУП() неће дати резултат. Функције ИНДЕКС() и МАТЦХ() су свестраније, а локација вредности не игра велику улогу да би функционисале.

На пример, на леву страну наше табеле ћемо додати колону ранга и покушати да схватимо који ранг по броју становника заузима главни град Наше земље.

У ћелију Г1 уписујемо вредност коју треба пронаћи, а затим користимо следећу формулу за претрагу у опсегу Ц1:Ц10 и враћамо одговарајућу вредност из А2:А10:

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

Промпт. Ако планирате да користите ову формулу за више ћелија, уверите се да сте поправили опсеге користећи апсолутно адресирање (нпр. $А$2: $А$10 и $Ц$2: 4Ц$10).

ИНДЕКС ИЗЛОЖЕНИЈИ ИЗЛОЖЕНИЈИ  за претрагу у колонама и редовима

У горњим примерима, користили смо ове функције као замену за ВЛООКУП() за враћање вредности из унапред дефинисаног опсега редова. Али шта ако треба да урадите матричну или двострану претрагу?

Звучи компликовано, али формула за такве прорачуне је слична стандардној формули ИНДЕКС() МАТЦХ(), са само једном разликом: формула МАТЦХ() мора се користити два пута. Први пут да добијете број реда, а други пут да добијете број колоне:

=ИНДЕКС(низ, МАТЦХ(вредност вертикалне претраге, колона за претрагу, 0), МАТЦХ(вредност хоризонталне претраге, ред за претрагу, 0))

Погледајмо доњу табелу и покушајмо да направимо формулу ИНДЕКС() ЕКСПРЕСС() ЕКСПРЕСС() како би се приказали демографски подаци у одређеној земљи за изабрану годину.

Циљна земља је у ћелији Г1 (вертикална претрага), а циљна година је у ћелији Г2 (хоризонтално тражење). Формула ће изгледати овако:

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

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

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

• ПОДРЖИ(Г1,А2:А11,0) – тражи вредност (Г1) у опсегу А2:А11 и приказује број ове вредности, у нашем случају је 2;
• ТРАЖИ(Г2,Б1:Д1,0) – тражи вредност (Г2) у опсегу Б1:Д1. У овом случају, резултат је био 3.

Пронађени бројеви редова и колона се шаљу на одговарајућу вредност у формули ИНДЕКС():

=ИНДЕКС(Б2:Д11,2,3)

Као резултат, имамо вредност која се налази у ћелији на пресеку 2 реда и 3 колоне у опсегу Б2:Д11. А формула показује жељену вредност, која се налази у ћелији Д3.

Претражујте по више услова помоћу ИНДЕКС и МАТЦХ

Ако сте прочитали наш водич за ВЛООКУП(), вероватно сте испробали више формула за претрагу. Али овај метод претраге има једно значајно ограничење – потребу за додавањем помоћне колоне.

Али добра вест је та Помоћу ИНДЕКС() и МАТЦХ() можете претраживати више услова без потребе да уређујете или мењате свој радни лист.

Ево опште формуле за претрагу са више услова за ИНДЕКС() МАТЦХ():

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

Белешка: ова формула се мора користити заједно са пречицом на тастатури ЦТРЛ+СХИФТ+ЕНТЕР.

Претпоставимо да треба да пронађете вредност коју тражите на основу 2 услова: Купац и Производ.

Ово захтева следећу формулу:

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

У овој формули, Ц2:Ц10 је опсег у којем ће се претрага одвијати, F1 – ово стање, А2:А10 — је опсег за упоређивање услова, F2 – услов 2, В2:В10 – опсег за поређење услова 2.

Не заборавите да притиснете комбинацију на крају рада са формулом ЦТРЛ + СХИФТ + ЕНТЕР – Екцел ће аутоматски затворити формулу са витичастим заградама, као што је приказано у примеру:

Ако не желите да користите формулу низа за свој рад, додајте још један ИНДЕКС() у формулу и притисните ЕНТЕР, то ће изгледати као у примеру:

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

Ова формула функционише на исти начин као и стандардна формула ИНДЕКС() МАТЦХ(). Да бисте тражили више услова, једноставно креирате више услова који су лажни и тачни који представљају исправне и нетачне појединачне услове. А онда се ови услови примењују на све одговарајуће елементе низа. Формула конвертује аргументе Фалсе и Труе у 0 и 1, респективно, и даје низ где је 1 одговарајуће вредности које су пронађене у низу. МАТЦХ() ће пронаћи прву вредност која одговара 1 и проследити је формули ИНДЕКС(). А он ће, заузврат, вратити већ жељену вредност у наведеном реду из жељене колоне.

Формула без низа зависи од способности ИНДЕКС() да их самостално рукује. Други ИНДЕКС() у формули одговара нетачном (0), тако да прослеђује цео низ са тим вредностима у формулу МАТЦХ(). 

Ово је прилично дугачко објашњење логике иза ове формуле. За више информација прочитајте чланак „ИНДЕКС ПОДАРХИВАЊЕ са више услова'.

АВЕРАГЕ, МАКС и МИН у ИНДЕКС-у и МАТЦХ

Екцел има своје посебне функције за проналажење просека, максимума и минимума. Али шта ако желите да добијете податке из ћелије повезане са тим вредностима? У овом случају АВЕРАГЕ, МАКС и МИН морају се користити заједно са ИНДЕКС и МАТЦХ.

ИНДЕКС МАТЦХ и МАКС

Да бисте пронашли највећу вредност у колони Д и приказали је у колони Ц, користите формулу: 

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

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

Да бисте пронашли најмању вредност у колони Д и приказали је у колони Ц, користите следећу формулу:

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

ПРЕТРАЖИВАЊЕ ИНДЕКСА и СЕРПЕНТ

Да бисте пронашли просечну вредност у колони Д и приказали ову вредност у Ц:

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

У зависности од тога како су ваши подаци написани, трећи аргумент за МАТЦХ() је или 1, 0 или -1:

• ако су колоне сортиране у растућем редоследу, поставите 1 (онда ће формула израчунати максималну вредност, која је мања или једнака просечној вредности);
• ако је сортирање опадајуће, онда -1 (формула ће дати минималну вредност која је већа или једнака просеку);
• ако низ за тражење садржи вредност која је тачно једнака просеку, онда је поставите на 0. 

 У нашем примеру, популација је сортирана у опадајућем редоследу, тако да стављамо -1. А резултат је Токио, пошто је вредност становништва (13,189) најближа просечној вредности (000).

ВЛООКУП() такође може да изврши такве прорачуне, али само као формула низа: ВЛООКУП са АВЕРАГЕ, МИН и МАКС.

ИНДЕКС МАТЦХ и ЕСНД/ИФЕРРОР

Вероватно сте већ приметили да ако формула не може да пронађе жељену вредност, она даје грешку # Н / А. Стандардну поруку о грешци можете заменити нечим информативнијим. На пример, поставите аргумент у формулу У XNUMX:

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

Са овом формулом, ако унесете податке који нису у табели, образац ће вам дати наведену поруку.

Ако желите да ухватите све грешке, осим за У XNUMX може да се користи ИФЕРРОР:

=ИФЕРРОР(ИНДЕКС(Ц2:Ц10,МАЦХ(Ф1,А2:А10,0)), „Нешто је пошло наопако!“)

Али запамтите да маскирање грешака на овај начин није добра идеја, јер стандардне грешке пријављују кршења формуле.

Надамо се да вам је наш водич за коришћење функције ИНДЕКС МАТЦХ() био од помоћи.