Функцията MATCH в Microsoft Excel

Един от най-популярните оператори сред потребителите на Excel е функцията MATCHING . Неговата задача е да определи номера на елемента в даден набор от данни. Най-голямата полза, която носи, когато се използва съвместно с други оператори. Да видим каква е функцията на MATCH и как може да се използва на практика.

Използване на оператора MAP

Операторът на MAP принадлежи към категорията "Връзки и масиви" . Той търси определения елемент в посочения масив и връща номера на своята позиция в този диапазон на отделна клетка. Всъщност дори името му показва това. Също така, когато се комбинира с други оператори, тази функция ги информира за номера на позицията на даден елемент за последваща обработка на тези данни.

Синтаксисът на израза MATCH изглежда така:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

Сега разгледайте всеки от тези три аргумента отделно.

"Търсенето на смисъл" е елементът, който трябва да се намери. Тя може да има текстова, цифрова форма и да има логическа стойност. Този аргумент може да бъде и препратка към клетка, която съдържа някоя от горепосочените стойности.

"Сканираният масив" е адресът на диапазона, в който се намира стойността. Позицията на този елемент в този масив трябва да бъде определена от оператора на MAP .

"Тип на съвпадението " показва точното съвпадение, което искате да търсите или неточно. Този аргумент може да има три стойности: "1" , "0" и "-1" . Ако стойността е "0", операторът търси само точно съвпадение. Ако стойността "1" е посочена, при отсъствието на точно съвпадение MATCH произвежда най-близкия елемент към нея в низходящ ред. Ако стойността е "-1" , тогава в случая, ако точното съвпадение не е намерено, функцията произвежда най-близкия елемент към него във възходящ ред. Важно е, ако търсенето не е точна, но приблизителна, така че масива, който ще се сканира, се подрежда във възходящ ред (тип на картографиране "1" ) или низходящ (тип на картографиране "-1" ).

Аргументът "Съответстващ тип" е по избор. Това може да се пропусне, ако не е необходимо. В този случай стойността му по подразбиране е "1" . Прилагането на аргумента "Тип на съвпадението" има преди всичко смисъл само когато се обработват цифрови стойности, а не текст.

Ако MATCH за определените настройки не може да намери желания елемент, операторът показва грешката "# N / D" в клетката.

При извършване на търсене операторът не прави разлика между регистрите на знаци. Ако има няколко точни съвпадения в масива, тогава MATCHING показва позицията на първия в клетката.

Метод 1: Показване на мястото на елемент в диапазон от текстови данни

Нека да разгледаме примера на най-простия случай, когато използвате MATCH, можете да определите местоположението на посочения елемент в масив от текстови данни. Ще открием коя позиция в обхвата, в който се намират имената на стоките, е думата "захар" .

1. Изберете клетката, в която ще се извежда обработеният резултат. Кликнете върху иконата "Вмъкване на функция" до лентата за формули.
2. Стартира съветникът за функции . Отворете категорията "Пълен азбучен списък" или "Връзки и масиви" . В списъка с оператори търсим името "MATCH" . След като го намерите и изберете, кликнете върху бутона "OK" в долната част на прозореца.
3. Прозорецът за аргументите на оператора на MAP е активиран . Както можете да видите, в този прозорец има три полета според броя на аргументите. Трябва да ги запълним.

   Тъй като трябва да намерим позицията на думата "Sugar" в диапазона, караме това име в полето "Search value" .

   В полето "Сканиран масив" трябва да посочите координатите на самия диапазон. Той може да бъде изкован ръчно, но е по-лесно да поставите курсора в полето и да изберете този масив на листа, като задържите левия бутон на мишката. След това адресът му ще се покаже в прозореца с аргументи.

   В третото поле "Съответстващ тип" поставяме числото "0" , тъй като ще работим с текстови данни и затова се нуждаем от точен резултат.

   След като сте задали всички данни, кликнете върху бутона "OK" .

4. Програмата изчислява и показва редния номер на позицията "Захар" в избрания масив в клетката, която определихме в първата стъпка на тази инструкция. Номерът на позицията ще бъде "4" .

Урокът: Съветник за функции в Excel

Метод 2: Автоматизиране на използването на оператора MATCH

По-горе разгледахме най-примитивния случай на използване на POSITION оператора, но дори и той може да бъде автоматизиран.

1. За удобство на листа добавете две допълнителни полета: "Специфична стойност" и "Номер" . В полето " Assigned стойност" караме името, което трябва да намерим. Сега нека бъде "Месо" . В полето "Номер" задайте курсора и отидете до прозореца с аргументи на оператора по същия начин, както бе обсъдено по-горе.
2. В прозореца за функцията аргументи в полето "Търсене стойност" ние посочваме адреса на клетката, където е написана думата "месо" . В полетата "Сканиран масив" и "Тип на картографиране" посочваме същите данни като при предишния метод - съответно адреса на диапазона и числото "0" . След това кликнете върху бутона "OK" .
3. След като извършихме горепосочените действия, полето "Номер" показва позицията на думата "Месо" в избрания диапазон. В този случай той е равен на "3" .
4. Този метод е добър, защото ако искаме да знаем позицията на което и да е друго име, няма да се налага да въвеждаме отново или да променяме формулата всеки път. В полето "Специфична стойност" е съвсем лесно да въведете нова дума за търсене вместо предишната. Обработката и извеждането на резултата след това ще се извършат автоматично.

Метод 3: Използвайте MAP оператора за цифрови изрази

Сега нека разгледаме как можете да използвате MATCHING, за да работите с цифрови изрази.

Задачата е да се намерят стоките за реализирането на 400 рубли или най-близкото до тази сума във възходящ ред.

1. На първо място, трябва да сортираме елементите в графата "Сума" в низходящ ред. Изберете тази графа и отидете в раздела "Начало" . Кликваме върху иконата "Сортиране и филтриране" , която се намира на лентата в блока "Редактиране" . В списъка, който се показва, изберете "Сортиране от максимум до минимум" .
2. След като приключи сортирането, изберете клетката, където ще се показва резултата, и изпълнете прозореца с аргументи по същия начин, както е описано в първия метод.

   В полето "Стойност за търсене" караме номер "400" . В полето "Сканиран масив" посочете координатите на графата "Сума" . В полето "Съвпадение тип" задайте стойността на "-1" , тъй като ние търсим равна или по-голяма стойност от тази, която търсим. След като завършите всички настройки, кликнете върху бутона "OK" .

3. Резултатът от обработката се показва в предварително зададената клетка. Това е позиция "3" . Тя отговаря на "Картофи" . Всъщност размерът на приходите от продажбата на този продукт е най-близо до 400 във възходящ ред и е 450 рубли.

По същия начин можете да търсите най-близката позиция до "400" в низходящ ред. Само за тази цел е необходимо данните да се филтрират във възходящ ред, а в полето "Съответстващ тип" на аргументите за функциите задайте стойността на "1" .

Урокът: Сортиране и филтриране на данни в Excel

Метод 4: Използване заедно с други оператори

Най-ефективно е да се използва тази функция с други оператори като част от сложна формула. Най-често се използва заедно с функцията INDEX . Този аргумент извежда съдържанието на диапазона, определен от номера на неговия ред или колона, до определената клетка. Номерацията, както в случая на оператора POSITION , се извършва не по отношение на целия лист, а само в обхвата. Синтаксисът на тази функция е:

=ИНДЕКС(массив;номер_строки;номер_столбца)

В този случай, ако масивът е едноизмерен, можете да използвате само един от двата аргумента: "Номер на ред" или "Колонен номер" .

Особеността на връзката между функциите INDEX и MATCH е, че последната може да се използва като първи аргумент, т.е. да се посочи позицията на ред или колона.

Нека да разгледаме как това може да се направи на практика, като се използва същата таблица. Ние имаме задачата да изведем името на стоките в допълнителното поле на листа "Стоки" , чийто общ размер на приходите е равен на 350 рубли или най-близко до тази стойност в низходящ ред. Този аргумент е посочен в полето "Приблизителна сума на приходите на листа" .

1. Сортирайте елементите в графата "Стойност на приходите" във възходящ ред. За да направите това, изберете необходимата графа и в раздела "Начало" кликнете върху иконата "Сортиране и филтриране" и след това кликнете върху елемента "Сортиране от минимум до максимум" в менюто, показано.
2. Изберете клетката в полето "Продукт" и извикайте функционалният помощник по обичайния начин чрез бутона "Вмъкване на функция" .
3. В отворения прозорец на помощника на функциите в категорията "Връзки и масиви" търсим името "INDEX" , изберете го и кликнете върху бутона "OK" .
4. След това се отваря прозорец, който предлага избор на опцията ИНДЕКС : за масив или за справка. Нуждаем се от първия вариант. Следователно оставаме всички настройки по подразбиране в този прозорец и кликнете върху бутона "OK" .
5. Отваря прозореца с аргументи на функцията INDEX . В полето "Array" посочете адреса на диапазона, в който INDEX операторът ще търси името на продукта. В нашия случай това е графата "Име на продукта" .

   Полето "Номер на линия" ще съдържа вградената функция MATCH . Той трябва да бъде изкован ръчно, използвайки синтаксиса, споменат в самото начало на статията. Веднага напишете името на функцията - "MATCH" без кавички. След това отворете скобата. Първият аргумент на този оператор е "стойността на интереса" . Той се намира на листа в полето "Приблизителна сума на приходите". Посочете координатите на клетката, съдържаща числото 350 . Поставете точка и запетая. Вторият аргумент е "Сканираният масив" . MATCHING ще разгледа обхвата, в който се намира размерът на приходите, и ще търси най-много приблизително 350 рубли. Ето защо в този случай посочваме координатите на графата "Размер на приходите" . Отново поставяме точка и запетая. Третият аргумент е "Тип на съвпадението" . Тъй като търсим число, равно на даденото число или най-близкото по-малко, след това зададете цифрата "1" тук . Затворете скобите.

   Третият аргумент на функцията INDEX "Номер на колоната" остава празен. След това кликнете върху бутона "OK" .

6. Както можете да видите, функцията INDEX с помощта на оператора POSITION в избраната клетка показва името "Чай" . Всъщност сумата от продажбата на чай (300 рубли) е най-близо в низходящ ред до сумата от 350 рубли от всички стойности в обработената маса.
7. Ако променим номера в полето "Приблизителна сума на приходите" на друго, съдържанието на полето "Продукт" автоматично ще бъде преизчислено.

Урокът: Функция INDEX в Excel

Както можете да видите, операторът на MAP е много удобна функция за определяне на редовния номер на посочения елемент в масива с данни. Но ползата от него значително се увеличава, ако се използва в сложни формули.

• ← Изчисляване на NPV в Microsoft Excel
• Функция INDEX в Microsoft Excel →