ИНДЕКС & МАТЦХ функције Комбиноване у Екцелу (10 једноставних примера)

Екцел има много функција - око 450+ њих.

И многи од њих су једноставно сјајни. Количина посла коју можете обавити са неколико формула и даље ме изненађује (чак и након што сте Екцел користили више од 10 година).

Међу свим овим невероватним функцијама, истиче се комбинација функција ИНДЕКС МАТЦХ.

Обожавам комбинацију ИНДЕКС МАТЦХ и то сам више пута јасно ставио до знања.

Чак сам написао чланак о Индек Матцх ВС ВЛООКУП -у који је изазвао малу расправу (неки од ватромета можете погледати у одељку коментара).

И данас пишем овај чланак искључиво фокусиран на Индек Матцх како бих вам показао неке једноставне и напредне сценарије у којима можете користити ову моћну комбинацију формула и обавити посао.

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

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

ИНДЕКС функција: Проналази вриједност засновану на координатама

Најлакши начин да разумете како функционише Индек функција је размишљање о њој као ГПС сателиту.

Чим сателиту кажете координате географске ширине и дужине, он ће тачно знати куда да оде и пронађе ту локацију.

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

Брзо сам претражио локацију свог посла и ево шта сам добио.

У сваком случају, доста географије.

Баш као што сателиту требају координате географске ширине и дужине, функцији ИНДЕКС у Екцелу ће бити потребни број ретка и колоне да би знао на коју ћелију мислите.

А то је Екцел ИНДЕКС функција у љусци ораха.

Дозволите ми да вам то дефинишем једноставним речима.

ИНДЕКС функција ће користити број ретка и број колоне да пронађе ћелију у датом опсегу и врати вредност у њој.

ИНДЕКС је сама по себи врло једноставна функција, без услужних програма. На крају крајева, у већини случајева вероватно нећете знати бројеве редова и колона.

Али…

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

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

= ИНДЕКС (низ, број редака, [број_колона]) = ИНДЕКС (низ, број редака, [број_колона], [број_подручја])

• низ - а опсег ћелија или константа низа.
• ред_број - број реда из којег треба да се преузме вредност.
• [број_колона] - број колоне из које треба да се преузме вредност. Иако је ово изборни аргумент, али ако број_реда није наведен, потребно га је навести.
• [број_подручја] - (Опционално) Ако се аргумент низа састоји од више опсега, овај број ће се користити за одабир референце из свих распона.

ИНДЕКС функција има 2 синтаксе (само ФИИ).

У већини случајева користи се први. Други се користи само у напредним случајевима (као што је тросмерно претраживање), што ћемо размотрити у једном од примера касније у овом водичу.

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

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

МАТЦХ функција: Проналази позицију засновану на вредности тражења

Враћајући се на мој претходни пример географске дужине и ширине, МАТЦХ је функција која може пронаћи ове позиције (у свету Екцел табела).

Једноставним језиком, функција Екцел МАТЦХ може пронаћи положај ћелије у опсегу.

И на основу чега би пронашао положај ћелије?

На основу тражене вредности.

На пример, ако имате листу као што је приказано у наставку и желите да пронађете позицију имена „Означи“ у њој, онда можете да користите функцију МАТЦХ.

Функција враћа 3, јер је то положај ћелије са именом Марк у њој.

МАТЦХ функција почиње од врха до дна тражити вредност тражења (која је „Означи“) у наведеном опсегу (што је А1: А9 у овом примеру). Чим пронађе име, враћа позицију у том одређеном опсегу.

Испод је синтакса функције МАТЦХ у Екцелу.

= МАТЦХ (лоокуп_валуе, лоокуп_арраи, [матцх_типе])

• лоокуп_валуе - Вредност за коју тражите подударање у лоокуп_арраи.
• лоокуп_арраи - Опсег ћелија у којима тражите вредност лоокуп_валуе.
• [матцх_типе] - (Опционално) Ово одређује како Екцел треба да тражи одговарајућу вредност. Може узети три вредности -1, 0 или 1.

Разумевање аргумента за тип подударања у функцији МАТЦХ

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

Трећи аргумент функције МАТЦХ може бити 0, 1 или -1.

Испод је објашњење како ови аргументи функционишу:

• 0 - ово ће тражити потпуно подударање вредности. Ако се пронађе потпуно подударање, функција МАТЦХ ће вратити положај ћелије. У супротном ће вратити грешку.
• 1 - ово проналази највећу вредност која је мања или једнака вредности тражења. Да би ово функционисало, опсег података морате сортирати по растућем редоследу.
• -1 - ово проналази најмању вредност која је већа или једнака вредности тражења. Да би ово функционисало, опсег података морате сортирати опадајућим редоследом.

Испод је видео који објашњава како се користи функција МАТЦХ (заједно са аргументом типа подударања)

Да резимирамо и изразимо једноставним речима:

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

Комбинујмо их да направимо моћну кућу (ИНДЕКС + МАТЦХ)

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

Да бих ово боље разумео, имам неколико примера који користе комбинацију ИНДЕКС МАТЦХ.

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

Кликните овде за преузимање датотеке примера

Пример 1: Једноставно претраживање помоћу комбинације ИНДЕКС МАТЦХ

Урадимо једноставно претраживање помоћу ИНДЕКС/МАТЦХ.

Испод је табела на којој имам оцене за десет ученика.

Из ове табеле желим да пронађем ознаке за Јима.

Испод је формула која то лако може учинити:

= ИНДЕКС ($ А $ 2: $ Б $ 11, МАТЦХ ("Јим", $ А $ 2: $ А $ 11,0), 2)

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

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

Дозволите ми сада да покажем корист од ИНДЕКС МАТЦХ -а.

Претпоставимо да имате исте податке, али уместо да их имате у колонама, имате их у редовима (као што је приказано испод).

Знате шта, још увек можете да користите комбинацију ИНДЕКС МАТЦХ да бисте добили Јим -ове оцене.

Испод је формула која ће вам дати резултат:

= ИНДЕКС ($ Б $ 1: $ К $ 2,2, МАТЦХ („Јим“, $ Б $ 1: $ К $ 1,0))

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

Ово се не може урадити помоћу ВЛООКУП -а, али и даље можете то учинити лако помоћу ХЛООКУП -а.

Комбинација ИНДЕКС МАТЦХ може лако руковати хоризонталним и вертикалним подацима.

Кликните овде за преузимање датотеке примера

Пример 2: Претражите лево

То је чешће него што мислите.

Много пута ће вам можда бити потребно да преузмете податке из колоне која је лево од колоне која има вредност тражења.

Нешто као што је приказано испод:

Да бисте сазнали Мицхаелову продају, мораћете да погледате лево.

Ако мислите на ВЛООКУП, дозволите ми да вас зауставим овде.

ВЛООКУП није направљен за тражење и дохваћање вредности са леве стране.

Можете ли то учинити помоћу ВЛООКУП -а?

Да ти то можеш!

Али то се може претворити у дугачку и ружну формулу.

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

Испод је формула која ће добити Мицхаелов продајни број:

= ИНДЕКС ($ А $ 2: $ Ц $ 11, МАТЦХ ("Мицхаел", Ц2: Ц11,0), 2)

Још једна тачка овде за ИНДЕКС МАТЦХ. ВЛООКУП може дохватити податке само из колона које су десно од колоне која има вредност тражења.

Пример 3: Двосмерно тражење

До сада смо видели примере где смо хтели да дохватимо податке из колоне која се налази поред колоне која има вредност тражења.

Али у стварном животу подаци се често простиру кроз више колона.

ИНДЕКС МАТЦХ може лако руковати двосмерним претраживањем.

Испод је скуп података о оценама ученика у три различита предмета.

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

Формула испод ће вам дати оцене за Јима за сва три предмета (копирајте и залепите у једну ћелију и превуците да бисте попунили друге ћелије или копирајте и залепите у друге ћелије).

= ИНДЕКС ($ Б $ 2: $ Д $ 11, МАТЦХ ($ Ф $ 3, $ А $ 2: $ А $ 11,0), МАТЦХ (Г $ 2, $ Б $ 1: $ Д $ 1,0))

Дозволите ми да брзо објасним и ову формулу.

ИНДЕКС формула користи Б2: Д11 као опсег.

Први МАТЦХ користи име (Јим у ћелији Ф3) и преузима његову позицију у колони са именима (А2: А11). Ово постаје број ретка из којег је потребно преузети податке.

Друга МАТЦХ формула користи назив субјекта (у ћелији Г2) да би добила позицију тог специфичног имена субјекта у Б1: Д1. На пример, математика је 1, физика 2 и хемија 3.

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

Ова формула је динамична, што значи да ће, ако промените име ученика или називе предмета, и даље радити и доносити тачне податке.

Једна сјајна ствар у коришћењу ИНДЕКС -а/МАТЦХ -а је та што ће вам чак и ако промените имена предмета, наставити да даје тачан резултат.

Пример 4: Тражење вредности из више колона/критеријума

Претпоставимо да имате скуп података као што је приказано испод и желите да преузмете ознаке за „Марк Лонг“.

Пошто се подаци налазе у две колоне, не могу да потражим Марка и добијем податке.

Ако то урадим на тај начин, добићу податке о оценама за Марка Фроста, а не за Марка Лонга (јер ће ми функција МАТЦХ дати резултат за ознаку коју испуњава).

Један од начина за то је креирање помоћне колоне и комбиновање имена. Када добијете помоћну колону, можете користити ВЛООКУП и добити податке о ознакама.

Ако сте заинтересовани да научите како то да урадите, прочитајте овај водич о коришћењу ВЛООКУП -а са више критеријума.

Али са комбинацијом ИНДЕКС/МАТЦХ не треба вам помоћна колона. Можете креирати формулу која обрађује више критеријума у ​​самој формули.

Формула испод ће дати резултат.

= ИНДЕКС ($ Ц $ 2: $ Ц $ 11, МАТЦХ ($ Е $ 3 & "|" & $ Ф $ 3, $ А $ 2: А11 & "|" & $ Б $ 2: $ Б $ 11,0))

Дозволите ми да брзо објасним чему служи ова формула.

Део формуле МАТЦХ комбинује вредност за тражење (Марк и Лонг), као и цео низ за претраживање. Када се $ А $ 2: А11 & ”|” & $ Б $ 2: $ Б $ 11 користи као низ за тражење, он заправо проверава вредност претраживања у односу на комбиновани низ имена и презимена (одвојен симболом цеви).

Ово осигурава да ћете добити прави резултат без употребе колона помоћника.

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

Пример 5: Добијте вредности из целог реда/колоне

У горе наведеним примерима користили смо функцију ИНДЕКС за добијање вредности из одређене ћелије. Ви уносите број реда и колоне и он враћа вредност у тој одређеној ћелији.

Али можете учинити више.

Такође можете користити функцију ИНДЕКС за добијање вредности из целог реда или колоне.

Питајте како ово може бити корисно!

Претпоставимо да желите да знате укупан резултат Јима у сва три предмета.

Можете користити функцију ИНДЕКС да бисте прво добили све Јим -ове оцене, а затим помоћу функције СУМ да бисте добили укупан резултат.

Да видимо како то учинити.

У наставку имам резултате свих ученика из три предмета.

Формула испод ће ми дати укупан резултат Јим -а у сва три предмета.

= СУМ (ИНДЕКС ($ Б $ 2: $ Д $ 11, МАТЦХ ($ Ф $ 4, $ А $ 2: $ А $ 11,0), 0))

Дозволите ми да објасним како ова формула функционише.

Трик овде је да користите 0 као број колоне.

Када користите 0 као број колоне у функцији ИНДЕКС, она ће вратити све вредности реда. Слично, ако за број реда користите 0, вратит ће се све вриједности у колони.

Дакле, доњи део формуле враћа низ вредности - {97, 70, 73}

ИНДЕКС ($ Б $ 2: $ Д $ 11, МАТЦХ ($ Ф $ 4, $ А $ 2: $ А $ 11,0), 0)

Ако само унесете ову горњу формулу у ћелију у Екцелу и притиснете ентер, видећете #ВРЕДНОСТ! грешка. То је зато што не враћа једну вредност, већ низ вредности.

Али не брините, низ вредности је још увек ту. Ово можете проверити одабиром формуле и притиском на тастер Ф9. Показаће вам резултат формуле која је у овом случају низ од три вредности - {97, 70, 73}

Сада, ако ову формулу ИНДЕКС умотате у функцију СУМ, добићете збир свих оцена које је Јим постигао.

Можете их користити и за добијање највећих, најнижих и просечних оцена Јима.

Баш као што смо ово урадили за ученика, можете то учинити и за предмет. На пример, ако желите просечан резултат у предмету, можете да задржите број реда као 0 у формули ИНДЕКС и он ће вам дати све вредности колона тог предмета.

Кликните овде за преузимање датотеке примера

Пример 6: Пронађите оцену ученика (приближна техника подударања)

До сада смо користили формулу МАТЦХ да бисмо добили тачно подударање вредности тражења.

Али можете га користити и за приближно подударање.

Шта је, дођавола, приближно подударање?

Дозволи да објасним.

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

На пример, као разредни старешина, можда ћете желети да знате која је оцена сваког ученика у предмету, а оцена се одлучује на основу резултата.

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

Дакле, ако ученик добије мање од 33, оцена је Ф, а ако он/она добије мање од 50, али више од 33, то је Е, итд.

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

= ИНДЕКС ($ Ф $ 3: $ Ф $ 8, МАТЦХ (Б2, $ Е $ 3: $ Е $ 8,1), 1)

Дозволите ми да објасним како ова формула функционише.

У функцији МАТЦХ користили смо 1 као аргумент [матцх_типе]. Овај аргумент ће вратити највећу вредност која је мања или једнака вредности тражења.

То значи да МАТЦХ формула пролази кроз распон ознака, и чим пронађе опсег ознака који је једнак или мањи од вредности ознака за претраживање, зауставиће се тамо и вратити своју позицију.

Дакле, ако је вредност ознаке за претраживање 20, функција МАТЦХ би вратила 1, а ако је 85, вратила би 5.

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

ВАЖНО: Да би ово функционисало, ваше податке морате сортирати по растућем редоследу. Ако није, можете добити погрешне резултате.

Имајте на уму да се горе наведено може урадити и помоћу формуле ВЛООКУП испод:

= ВЛООКУП (Б2, $ Е $ 3: $ Ф $ 8,2, Тачно)

Али функција МАТЦХ може отићи корак даље када је у питању приближно подударање.

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

Да бих то урадио, све што треба да урадим је да променим аргумент [матцх_типе] у -1.

Испод је формула коју сам користио:

= ИНДЕКС ($ Ф $ 3: $ Ф $ 8, МАТЦХ (Б2, $ Е $ 3: $ Е $ 8, -1), 1)

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

Пример 7: Претраге осетљиве на велика и мала слова

До сада су све претраге које смо урадили биле неосетљиве на велика и мала слова.

То значи да није важно да ли је вредност за претраживање Јим или ЈИМ или јим, није важно. Добићете исти резултат.

Али шта ако желите да претраживање буде осетљиво на велика и мала слова?

То је обично случај када имате велике скупове података и могућност понављања или различитих имена/ИД -ова (с једином разликом у томе)

На пример, претпоставимо да имам следећи скуп података о студентима где постоје два ученика са именом Јим (једина разлика је у томе што је један уписан као Јим, а други као јим).

Имајте на уму да постоје два ученика са истим именом - Јим (ћелија А2 и А5).

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

Испод је формула која ће вам дати прави резултат. Пошто је ово формула низа, морате користити Цонтрол + Схифт + Ентер.

= ИНДЕКС ($ Б $ 2: $ Б $ 11, МАТЦХ (ТРУЕ, ЕКСАЦТ (Д3, А2: А11), 0), 1)

Дозволите ми да објасним како ова формула функционише.

Функција ЕКСАЦТ проверава да ли се тачно подудара са вредношћу претраге (која је у овом случају „јим“). Пролази кроз сва имена и враћа ФАЛСЕ ако се не подудара и ТРУЕ ако се подудара.

Дакле, излаз функције ЕКСАЦТ у овом примеру је - {ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ}

Имајте на уму да постоји само једно ТРУЕ, када је функција ЕКСАЦТ пронашла савршено подударање.

Функција МАТЦХ тада проналази позицију ТРУЕ у низу коју враћа функција ЕКСАЦТ, што је 4 у овом примеру.

Када имамо позицију, функција ИНДЕКС користи је за проналажење ознака.

Пример 8: Пронађите најближе подударање

Хајдемо сада мало напредовати.

Претпоставимо да имате скуп података у коме желите да пронађете особу која има радно искуство најближе потребном искуству (поменуто у ћелији Д2).

Иако формуле за претраживање нису направљене за ово, можете их комбиновати са другим функцијама (попут МИН и АБС) да бисте то постигли.

Испод је формула која ће пронаћи особу са искуством најближим траженом и вратити име особе. Имајте на уму да искуство мора бити најближе (што може бити мање или више).

= ИНДЕКС ($ А $ 2: $ А $ 15, МАТЦХ (МИН (АБС (Д2-Б2: Б15)), АБС (Д2- $ Б $ 2: $ Б $ 15), 0))

Пошто је ово формула низа, морате користити Цонтрол + Схифт + Ентер.

Трик у овој формули је да промените вредност претраживања и низ претраге да бисте пронашли минималну разлику искуства у потребним и стварним вредностима.

Пре него што објасним формулу, схватимо како бисте то урадили ручно.

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

Управо то радимо са овом формулом.

Дозволи да објасним.

Тражена вредност у формули МАТЦХ је МИН (АБС (Д2-Б2: Б15)).

Овај део вам даје минималну разлику између датог искуства (које износи 2,5 године) и свих осталих искустава. У овом примеру враћа 0,3

Имајте на уму да сам користио АБС да бих се уверио да тражим најближе (што може бити више или мање од датог искуства).

Сада ова минимална вредност постаје наша вредност за претраживање.

Прегледни низ у функцији МАТЦХ је АБС (Д2- $ Б $ 2: $ Б $ 15).

Ово нам даје низ бројева од којих је одузето 2,5 (потребно искуство).

Дакле, сада имамо вредност тражења (0,3) и низ претраге ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

МАТЦХ функција проналази позицију 0,3 у овом низу, што је такође позиција имена особе која има најближе искуство.

Овај број позиције затим користи функција ИНДЕКС за враћање имена особе.

Повезано читање: Пронађите најближе подударање у Екцелу (примери помоћу формула за тражење)

Кликните овде за преузимање датотеке примера

Пример 9: Користите ИНДЕКС МАТЦХ са џокер знаковима

Ако желите да потражите вредност када постоји делимично подударање, онда морате да користите џокер знакове.

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

Пошто се ово не подударају, у овом случају не можете редовно да тражите.

Али и даље можете добити праве податке користећи звездицу (*), која је џокер знак.

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

= ИНДЕКС ($ Б $ 2: $ Б $ 10, МАТЦХ (Д2 & ”*”, $ А $ 2: $ А $ 10,0), 1)

Дозволите ми да објасним како ова формула функционише.

Пошто не постоји тачно подударање вредности за претраживање, користио сам Д2 & ”*” као вредност претраживања у функцији МАТЦХ.

Звездица је заменски знак који представља било који број знакова. То значи да би Аппле* у формули значио било који текстуални низ који почиње речју Аппле и може имати било који број знакова иза себе.

Па кад Аппле* се користи као вредност за претраживање и формула МАТЦХ то тражи у колони А, враћа позицију „Аппле Инц.“, јер почиње речју Аппле.

Такође можете користити заменске знакове за проналажење текстуалних низова где је вредност за претраживање између. На пример, ако користите * Аппле * као вредност за претраживање, он ће пронаћи било који низ који има реч јабука било где у себи.

Напомена: Ова техника добро функционише ако имате само једну инстанцу подударања. Али ако имате више примерака подударања (на пример Аппле Инц и Аппле Цорпоратион, функција МАТЦХ би вратила положај само прве инстанце која се подудара.

Пример 10: Тространо тражење

Ово је напредна употреба ИНДЕКС МАТЦХ -а, али ћу је и даље покрити да вам покажем моћ ове комбинације.

Запамтите да сам рекао да функција ИНДЕКС има две синтаксе:

= ИНДЕКС (низ, број редака, [број_колона]) = ИНДЕКС (низ, број редака, [број_колона], [број_подручја])

До сада смо у свим нашим примерима користили само први.

Али за тросмерно тражење, морате да користите другу синтаксу.

Дозволите ми прво да објасним шта значи тросмерни изглед.

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

Тросмерни изглед додао би му још једну димензију. На пример, претпоставимо да имате скуп података као што је приказано у наставку и желите да знате резултат Јим-а из математике на средњорочном испиту, онда би ово било тросмерно тражење.

Испод је формула која ће дати резултат.

= ИНДЕКС (($ Б $ 3: $ Д $ 7, $ Б $ 11: $ Д $ 15, $ Б $ 19: $ Д $ 23), МАТЦХ ($ Ф $ 5, $ А $ 3: $ А $ 7,0), МАТЦХ (Г $ 4 , $ Б $ 2: $ Д $ 2,0), (ИФ (Г $ 3 = "Унит Тест", 1, ИФ (Г $ 3 = "Мид Терм", 2,3))))

Горња формула проверила је три ствари - име студента, предмет и испит. Након што пронађе праву вредност, враћа је у ћелију.

Дозволите ми да објасним како ова формула функционише разбијањем формуле на делове.

• низ - ($ Б $ 3: $ Д $ 7, $ Б $ 11: $ Д $ 15, $ Б $ 19: $ Д $ 23): Уместо да користим један низ, у овом случају сам користио три низа унутар заграда.
• ров_нум - МАТЦХ ($ Ф $ 5, $ А $ 3: $ А $ 7,0): МАТЦХ функција се користи за проналажење положаја имена ученика у ћелији $ Ф $ 5 на листи имена ученика.
• цол_нум - МАТЦХ (Г $ 4, $ Б $ 2: $ Д $ 2,0): МАТЦХ функција се користи за проналажење позиције имена субјекта у ћелији $ Б $ 2 на листи имена субјекта.
• [ареа_нум] - ИФ (Г $ 3 = "Унит Тест", 1, ИФ (Г $ 3 = "Мид Терм", 2,3)): Вредност броја подручја говори функцији ИНДЕКС који од три низа треба користити за дохваћање вредности. Ако је испит јединица термина, функција ИФ ће вратити 1, а функција ИНДЕКС ће користити први низ за дохваћање вредности. Ако је испит средњорочан, ИФ формула би вратила 2, иначе ће вратити 3.

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

Кликните овде за преузимање датотеке примера

Зашто је ИНДЕКС/МАТЦХ бољи од ВЛООКУП -а?

Или је то?

Да, то је - у већини случајева.

Изложићу свој случај за неко време.

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

Дакле, ако желите да извршите основно претраживање, боље је да користите ВЛООКУП.

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

Без да се превише развлачим, дозволите ми да вам на брзину наведем разлоге зашто је ИНДЕКС/МАТЦХ бољи од ВЛООКУП -а.

ИНДЕКС/МАТЦХ може погледати лево (као и десно) вредности тражења

Описао сам то у једном од горе наведених примера.

Ако имате вредност која је лево од вредности за претраживање, то не можете учинити помоћу ВЛООКУП -а

Барем не само са ВЛООКУП -ом.

Да, можете комбиновати ВЛООКУП са другим формулама и обавити то, али постаје компликовано и неуредно.

С друге стране, ИНДЕКС/МАТЦХ је направљен да тражи свуда (било лево, десно, горе или доле)

ИНДЕКС/МАТЦХ може радити са вертикалним и хоризонталним опсезима

Опет, уз пуно поштовање према ВЛООКУП -у, то није направљено за то.

На крају крајева, В у ВЛООКУП -у означава вертикалу.

ВЛООКУП може пролазити само кроз податке који су вертикални, док ИНДЕКС/МАТЦХ може пролазити кроз податке и вертикално и хоризонтално.

Наравно, постоји функција ХЛООКУП која брине о хоризонталном тражењу, али то онда није ВЛООКУП … зар не?

Свиђа ми се чињеница да је комбинација ИНДЕКС МАТЦХ довољно флексибилна за рад са вертикалним и хоризонталним подацима.

ВЛООКУП не може да ради са силазним подацима

Што се тиче приближног подударања, ВЛООКУП и ИНДЕКС/МАТЦХ су на истом нивоу.

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

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

ИНДЕКС/МАТЦХ може бити нешто бржи

Бићу искрен. Нисам сам водио овај тест.

Ослањам се на мудрост коју има Екцел мајстор - Цхарлеи Кид.

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

У свом чланку, Цхарлеи Кид наводи:

„У најгорем случају, ИНДЕКС-МАТЦХ метода је брза као ВЛООКУП; у најбољем случају, то је много брже. "

ИНДЕКС/МАТЦХ је независан од стварне позиције колоне

Ако имате скуп података као што је приказано у наставку док постижете резултат Јим -а у физици, то можете учинити помоћу ВЛООКУП -а.

А да бисте то урадили, можете да наведете број колоне као 3 у ВЛООКУП -у.

Све је у реду.

Али шта ако избришем колону Математика.

У том случају, формула ВЛООКУП ће се сломити.

Зашто? - Зато што је било тешко користити трећу колону, а када избришем колону између, трећа колона постаје друга колона.

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

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

Када користите ИНДЕКС/МАТЦХ, можете безбедно да уметнете/избришете колоне у свом скупу података.

Упркос свим овим факторима, постоји разлог зашто је ВЛООКУП толико популаран.

И то је велики разлог.

ВЛООКУП је лакши за употребу

ВЛООКУП узима највише четири аргумента. Ако можете омотати главу око ове четири, добро је да кренете.

А будући да већину основних случајева претраживања води и ВЛООКУП, брзо је постала најпопуларнија Екцел функција.

Зовем то краљ Екцел функција.

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

И ово није игра са нултом сумом.

Дакле, ако сте тек почели да тражите свет и не знате како да користите ВЛООКУП, боље то научите.

Имам детаљан водич о коришћењу ВЛООКУП -а у Екцелу (са пуно примера)

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

Надам се да вам је овај чланак био користан.

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