Можете да користите функцију Екцел МАТЦХ да бисте пронашли релативни положај одређене вредности у опсегу ћелија или низу.
Функција МАТЦХ је слична функцији ВЛООКУП јер су обе категорисане под Екцел функције претраживања/референце. ВЛООКУП тражи одређену вредност у колони и враћа вредност у истом реду док функција МАТЦХ тражи одређену вредност у опсегу и враћа позицију те вредности.
Екцел функција МАТЦХ тражи наведену вредност у опсегу ћелија или низу и враћа релативну позицију првог појављивања те вредности у опсегу. Функција МАТЦХ се такође може користити за тражење одређене вредности и враћање њене одговарајуће вредности уз помоћ функције ИНДЕКС (баш као Влоокуп). Хајде да видимо како да користимо функцију Екцел МАТЦХ да пронађемо позицију тражене вредности у опсегу ћелија.
Екцел МАТЦХ функција
Функција МАТЦХ је уграђена функција у Екцел-у и првенствено се користи за лоцирање релативне позиције тражене вредности у колони или реду.
Синтакса функције МАТЦХ:
=МАТЦХ(вредност_проналажења,низ_погледавања,[тип_подударања})Где:
лоокуп_валуе – Вредност коју желите да потражите у одређеном опсегу ћелија или у низу. То може бити нумеричка вредност, текстуална вредност, логичка вредност или референца ћелије која има вредност.
лоокуп_арраи – Низови ћелија у којима тражите вредност. Мора да буде једна колона или један ред.
матцх_типе – То је опциони параметар који се може подесити на 0,1 или -1, а подразумевана вредност је 1.
- 0 тражи тачно подударање, када није пронађено, враћа грешку.
- -1 тражи најмању вредност која је већа или једнака лоокуп_валуе када је низ за тражење у растућем редоследу.
- 1 тражи највећу вредност која је мања или једнака вредности лоок_уп када је низ за тражење у опадајућем редоследу.
Пронађите позицију тачног подударања
Претпоставимо да имамо следећи скуп података где желимо да пронађемо позицију одређене вредности.
У овој табели желимо да пронађемо позицију имена града (Мемфис) у колони (А2:А23), па користимо ову формулу:
=МАТЦХ("мемфис",А2:А23,0)Трећи аргумент је постављен на „0“ јер желимо да пронађемо тачно подударање имена града. Као што видите да је назив града „мемфис“ у формули написан малим словима, док је у табели прво слово назива града великим (Мемфис). Ипак, формула може да пронађе позицију наведене вредности у датом опсегу. То је зато што функција МАТЦХ не разликује велика и мала слова.
Белешка: Ако лоокуп_валуе није пронађена у опсегу претраживања или ако наведете погрешан опсег претраживања, функција ће вратити грешку #Н/А.
Можете користити референцу ћелије у првом аргументу функције уместо директне вредности. Формула испод проналази позицију вредности у ћелији Ф2 и враћа резултат у ћелију Ф3.
Пронађите позицију приближног подударања
Постоје два начина на која можете тражити приближно или тачно подударање вредности тражења и вратити њену позицију.
- Један од начина је да пронађете најмању вредност која је већа или једнака (следеће највеће подударање) наведеној вредности. То се може постићи постављањем последњег аргумента (матцх_типе) функције као '-1'
- Други начин је највећа вредност која је мања или једнака (следеће најмање подударање) датој вредности. То се може постићи постављањем матцх_типе функције као '1'
Следећи најмањи меч
Ако функција не може да пронађе тачно подударање са наведеном вредношћу када је тип подударања подешен на '1', она лоцира највећу вредност која је нешто мања од наведене вредности (што значи следећу најмању вредност) и враћа њену позицију . Да би ово функционисало, потребно је да сортирате низ у растућем редоследу, ако не, то ће довести до грешке.
У примеру, користимо формулу испод да пронађемо следеће најмање подударање:
=МАЦХ(Ф2,Д2:Д23,1)Када ова формула не може да пронађе тачно подударање за вредност у ћелији Ф2, она показује на позицију (16) следеће најмање вредности, тј. 98.
Следећи највећи меч
Када је тип подударања подешен на „-1“ и функција МАТЦХ не може да пронађе тачно подударање, она проналази најмању вредност која је већа од наведене вредности (што значи следећу највећу вредност) и враћа њену позицију. Низ претраживања мора бити сортиран у опадајућем редоследу за овај метод, иначе ће вратити грешку.
На пример, унесите следећу формулу да бисте пронашли следеће највеће подударање са вредношћу тражења:
=МАЦХ(Ф2,Д2:Д23,-1)Ова функција МАТЦХ тражи вредност у Ф2 (55) у опсегу претраживања Д2:Д23, а када не може да пронађе тачно подударање, враћа позицију (16) следеће највеће вредности, тј. 58.
Вилдцард Матцх
Замјенски знакови се могу користити у функцији МАТЦХ само када је матцх_типе постављен на '0', а вриједност претраживања је текстуални низ. Постоје џокер знакови које можете да користите у функцији МАТЦХ: звездица (*) и знак питања (?).
- Знак питања (?) користи се за подударање било ког појединачног знака или слова са текстуалним низом.
- звездица (*) се користи за подударање било ког броја знакова са стрингом.
На пример, користили смо два џокер знака „?“ у лоокуп_валуе (Ло??н) функције МАТЦХ да бисмо пронашли вредност која одговара текстуалном низу са било која два знака (на местима за замене). А функција враћа релативну позицију одговарајуће вредности у ћелији Е5.
=МАТЦХ("Ло??н",А2:А22,0)
Можете користити (*) џокер на исти начин као (?), али се звездица користи за подударање са било којим бројем знакова, док се знак питања користи за подударање са било којим појединачним знаком.
На пример, ако користите 'сп*', функција би се могла подударати са звучником, брзином или Спилбергом итд. Али ако функција пронађе вишеструке/дуплициране вредности које одговарају вредности тражења, вратиће само позицију прве вредности.
У примеру смо унели „Кил*о“ у аргумент лоокуп_валуе. Дакле, функција МАТЦХ() тражи текст који садржи 'Кил' на почетку, 'о' на крају и било који број знакова између. „Кил*о“ одговара Килиманџару у низу и стога функција враћа релативну позицију Килиманџара, која је 16.
ИНДЕКС и ПОДРЖАВАЊЕ
МАТЦХ функције се ретко користе саме. Често су упарени са другим функцијама да би створили моћне формуле. Када се функција МАТЦХ комбинује са функцијом ИНДЕКС, може извршити напредно тражење. Многи људи и даље више воле да користе ВЛООКУП за тражење вредности, јер је једноставније, али ИНДЕКС МАТЦХ је флексибилнији и бржи од ВЛООКУП-а.
ВЛООКУП може да тражи вредност само вертикално, тј. колоне, док комбинација ИНДЕКС МАТЦХ може да ради и вертикална и хоризонтална претраживања.
Функција ИНДЕКС која се користи за преузимање вредности на одређеној локацији у табели или опсегу. Функција МАТЦХ враћа релативну позицију вредности у колони или реду. Када се комбинује, МАТЦХ проналази број реда или колоне (локацију) одређене вредности, а функција ИНДЕКС преузима вредност на основу тог броја реда и колоне.
Синтакса функције ИНДЕКС:
=ИНДЕКС(низ,број_реда,[број_колца],)У сваком случају, да видимо како ИНДЕКС МАТЦХ функционише на примеру.
У примеру испод, желимо да преузмемо резултат „Куиз2“ за ученицу „Анне“. Да бисмо то урадили, користићемо формулу у наставку:
=ИНДЕКС(Б2:Ф20,МАЦХ(Х2,А2:А20,0),3)ИНДЕКС-у је потребан број реда и колоне да би добио вредност. У горњој формули, угнежђена функција МАТЦХ проналази број реда (позицију) вредности „Анне“ (Х2). Затим уносимо тај број реда у функцију ИНДЕКС са опсегом Б2:Ф20 и бројем колоне (3), које наведемо. А функција ИНДЕКС враћа резултат '91'.
Двосмерно тражење са ИНДЕКС и МАТЦХ
Такође можете користити функције ИНДЕКС и МАТЦХ за тражење вредности у дводимензионалном опсегу (двосмерно тражење). У горњем примеру, користили смо функцију МАТЦХ да лоцирамо број реда вредности, али смо ручно унели број колоне. Али можемо пронаћи и ред и колону тако што ћемо угнеждити две функције МАТЦХ, једну у аргументу ров_нум и другу у аргументу цолумн_нум функције ИНДЕКС.
Користите ову формулу за двосмерно тражење са ИНДЕКС и МАТЦХ:
=ИНДЕКС(А1:Ф20,МАЦХ(Х2,А2:А20,0),МАЦХ(Х3,А1:Ф1,0))Као што знамо, функција МАТЦХ може тражити вредност и хоризонтално и вертикално. У овој формули, друга функција МАТЦХ у аргументу Цолум_нум проналази позицију Куиз2 (4) и доставља је функцији ИНДЕКС. А ИНДЕКС преузима резултат.
Сада знате како да користите функцију Матцх у Екцел-у.