Можете да използвате функцията MATCH на Excel, за да намерите относителната позиция на конкретна стойност в диапазон от клетки или масив.
Функцията MATCH е подобна на функцията VLOOKUP, тъй като и двете са категоризирани в Excel Търсене/референтни функции. VLOOKUP търси конкретна стойност в колона и връща стойност в същия ред, докато функцията MATCH търси определена стойност в диапазон и връща позицията на тази стойност.
Функцията MATCH на Excel търси определена стойност в диапазон от клетки или масив и връща относителната позиция на първото появяване на тази стойност в диапазона. Функцията MATCH може също да се използва за търсене на определена стойност и връщане на съответната стойност с помощта на функцията INDEX (точно като Vlookup). Нека да видим как да използваме функцията MATCH на Excel, за да намерим позицията на стойност за търсене в диапазон от клетки.
Функция на Excel MATCH
Функцията MATCH е вградена функция в Excel и се използва основно за намиране на относителната позиция на стойност за търсене в колона или ред.
Синтаксис на функция MATCH:
=MATCH(търсен_стойност,търсен_масив,[тип_съвпадение})Където:
търсеща_стойност – Стойността, която искате да търсите в определен диапазон от клетки или в масив. Тя може да бъде числова стойност, текстова стойност, логическа стойност или препратка към клетка, която има стойност.
търсещ_масив – Масивите от клетки, в които търсите стойност. Трябва да е една колона или един ред.
тип_съвпадение – Това е незадължителен параметър, който може да бъде настроен на 0,1 или -1 и по подразбиране е 1.
- 0 търси точно съвпадение, когато не е намерено, връща грешка.
- -1 търси най-малката стойност, която е по-голяма или равна на lookup_value, когато масивът за търсене е във възходящ ред.
- 1 търси най-голямата стойност, която е по-малка или равна на стойността look_up, когато масивът за търсене е в низходящ ред.
Намерете позиция на точно съвпадение
Да предположим, че имаме следния набор от данни, където искаме да намерим позицията на определена стойност.
В тази таблица искаме да намерим позицията на името на града (Мемфис) в колона (A2:A23), така че използваме тази формула:
=МАЧИ("мемфис",A2:A23,0)Третият аргумент е зададен на „0“, защото искаме да намерим точно съвпадение на името на града. Както можете да видите, името на града „мемфис“ във формулата е с малки букви, докато в таблицата първата буква на името на града е с главни (Мемфис). Все пак формулата е в състояние да намери позицията на посочената стойност в дадения диапазон. Това е така, защото функцията MATCH не е чувствителна към главни букви.
Забележка: Ако lookup_value не е намерен в диапазона за търсене или ако посочите грешен диапазон за търсене, функцията ще върне грешката #N/A.
Можете да използвате препратка към клетка в първия аргумент на функцията вместо директна стойност. Формулата по-долу намира позицията на стойността в клетка F2 и връща резултата в клетка F3.
Намерете позицията на приблизително съвпадение
Има два начина, по които можете да потърсите приблизително или точно съвпадение на стойността за търсене и да върнете нейната позиция.
- Един от начините е да намерите най-малката стойност, която е по-голяма или равна (следващото най-голямо съвпадение) на посочената стойност. Това може да бъде постигнато чрез задаване на последния аргумент (match_type) на функцията като „-1“
- Друг начин е най-голямата стойност, която е по-малка или равна (следващото най-малко съвпадение) на дадената стойност. Това може да се постигне чрез задаване на match_type на функцията като „1“
Следващо най-малко съвпадение
Ако функцията не може да намери точно съвпадение с посочената стойност, когато типът на съвпадението е зададен на '1', тя намира най-голямата стойност, която е малко по-малка от определената стойност (което означава следващата най-малка стойност) и връща нейната позиция . За да работи това, трябва да сортирате масива във възходящ ред, ако не, това ще доведе до грешка.
В примера използваме формулата по-долу, за да намерим следващото най-малко съвпадение:
=МАЧИ(F2,D2:D23,1)Когато тази формула не може да намери точното съвпадение за стойността в клетка F2, тя сочи към позицията (16) на следващата най-малка стойност, т.е. 98.
Следващ най-голям мач
Когато типът на съвпадението е зададен на „-1“ и функцията MATCH не може да намери точно съвпадение, тя намира най-малката стойност, която е по-голяма от определената стойност (което означава следващата най-голяма стойност) и връща нейната позиция. Масивът за търсене трябва да бъде сортиран в низходящ ред за този метод, в противен случай той ще върне грешка.
Например въведете следната формула, за да намерите следващото най-голямо съвпадение със стойността за търсене:
=МАЧИ(F2,D2:D23,-1)Тази функция MATCH търси стойността във F2 (55) в диапазона на търсене D2:D23 и когато не може да намери точното съвпадение, връща позицията (16) на следващата най-голяма стойност, т.е. 58.
Съвпадение с заместващи символи
Заместващите символи могат да се използват във функцията MATCH само когато match_type е зададен на „0“ и стойността за търсене е текстов низ. Има заместващи знаци, които можете да използвате във функцията MATCH: звездичка (*) и въпросителен знак (?).
- Въпросителен знак (?) се използва за съпоставяне на всеки единичен знак или буква с текстовия низ.
- звездичка (*) се използва за съпоставяне на произволен брой символи с низа.
Например, използвахме два заместващи знака „?“ в lookup_value (Lo??n) на функцията MATCH, за да намерим стойност, която съответства на текстовия низ с произволни два знака (на местата за заместващи знаци). И функцията връща относителната позиция на съответстващата стойност в клетка E5.
=MATCH("Lo??n",A2:A22,0)
Можете да използвате (*) заместващ знак по същия начин като (?), но звездичка се използва за съвпадение на произволен брой знаци, докато въпросителният знак се използва за съвпадение на всеки единичен знак.
Например, ако използвате „sp*“, функцията може да съвпадне с високоговорител, скорост или спилбърг и т.н. Но ако функцията намери множество/дублирани стойности, съответстващи на стойността за търсене, тя ще върне само позицията на първата стойност.
В примера въведохме „Kil*o“ в аргумента lookup_value. Така че функцията MATCH() търси текст, който съдържа „Kil“ в началото, „o“ в края и произволен брой знаци между тях. „Kil*o“ съвпада с Килиманджаро в масива и следователно функцията връща относителната позиция на Килиманджаро, която е 16.
ИНДЕКС и СЪВПАДЕНИЕ
Функциите MATCH рядко се използват самостоятелно. Те често се съчетават с други функции за създаване на мощни формули. Когато функцията MATCH се комбинира с функцията INDEX, тя може да извършва разширени търсения. Много хора все още предпочитат да използват VLOOKUP за търсене на стойност, защото е по-просто, но INDEX MATCH е по-гъвкаво и по-бързо от VLOOKUP.
VLOOKUP може да търси стойност само вертикално, т.е. колони, докато комбинацията INDEX MATCH може да прави както вертикални, така и хоризонтални търсения.
Функция INDEX, използвана за извличане на стойност на определено място в таблица или диапазон. Функцията MATCH връща относителната позиция на стойност в колона или ред. Когато се комбинира, MATCH намира номера на реда или колоната (местоположението) на конкретна стойност, а функцията INDEX извлича стойност въз основа на този номер на ред и колона.
Синтаксис на функция INDEX:
=ИНДЕКС(масив,номер_ред,[номер_кола],)Както и да е, нека да видим как работи INDEX MATCH с пример.
В примера по-долу искаме да извлечем резултата от „Quiz2“ за ученичката „Ан“. За целта ще използваме следната формула:
=ИНДЕКС(B2:F20,СВЪВПАД(H2,A2:A20,0),3)INDEX се нуждае от номер на ред и колона, за да извлече стойност. В горната формула вложената функция MATCH намира номера на реда (позицията) на стойността „Anne“ (H2). След това предоставяме този номер на реда на функцията INDEX с диапазон B2:F20 и номер на колона (3), който указваме. И функцията INDEX връща резултат „91“.
Двупосочно търсене с INDEX и MATCH
Можете също да използвате функциите INDEX и MATCH, за да търсите стойност в двуизмерен диапазон (двупосочно търсене). В горния пример използвахме функцията MATCH, за да намерим номера на реда на стойност, но въведохме номера на колоната ръчно. Но можем да намерим както ред, така и колона, като вмъкнем две функции MATCH, една в аргумент row_num и друга в аргумент column_num на функцията INDEX.
Използвайте тази формула за двупосочно търсене с INDEX и MATCH:
=ИНДЕКС(A1:F20,СЪВЪВСТАВЯНЕ(H2,A2:A20,0),СЪВТОРЕНИЕ(H3,A1:F1,0))Както знаем, функцията MATCH може да търси стойност както хоризонтално, така и вертикално. В тази формула втората функция MATCH в аргумента colum_num намира позицията на Quiz2 (4) и я предоставя на функцията INDEX. И INDEX извлича резултата.
Сега знаете как да използвате функцията Match в Excel.