Какво да знаете
- Първо създайте функция INDEX, след това стартирайте вложената функция MATCH, като въведете аргумента Lookup_value.
- След това добавете аргумента Lookup_array, последван от аргумента Match_type, след което посочете диапазона на колоните.
- След това превърнете вложената функция във формула за масив, като натиснете Ctrl+ Shift+ Enter. Накрая добавете думите за търсене към работния лист.
Тази статия обяснява как да създадете формула за търсене, която използва множество критерии в Excel, за да намерите информация в база данни или таблица с данни, като използвате формула за масив. Формулата на масива включва влагане на функцията MATCH във функцията INDEX. Информацията обхваща Excel за Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel за Mac.
Следвайте урока
За да следвате стъпките в този урок, въведете примерните данни в следните клетки, както е показано на изображението по-долу. Редове 3 и 4 са оставени празни, за да поемат формулата на масива, създадена по време на този урок. (Обърнете внимание, че този урок не включва форматирането, което се вижда на изображението.)
- Въведете горния диапазон от данни в клетки D1 до F2.
- Въведете втория диапазон в клетки D5 до F11.
Създаване на функция INDEX в Excel
Функцията ИНДЕКС е една от малкото функции в Excel, която има множество форми. Функцията има форма на масив и форма за справка. Формулярът за масив връща данните от база данни или таблица с данни. Референтният формуляр дава препратката към клетката или местоположението на данните в таблицата.
В този урок формулярът за масиви се използва за намиране на името на доставчика за титанови джаджи, а не препратката към клетката към този доставчик в базата данни.
Следвайте тези стъпки, за да създадете функцията INDEX:
- Изберете клетка F3, за да я направите активната клетка. Тази клетка е мястото, където ще бъде въведена вложената функция.
-
Отидете на Формули.
Image - Изберете Търсене и справка, за да отворите падащия списък с функции.
- Изберете INDEX, за да отворите диалоговия прозорец Избор на аргументи.
- Изберете масив, номер_на_ред, номер_на_колона.
- Изберете OK, за да отворите диалоговия прозорец Аргументи на функцията. В Excel за Mac се отваря Formula Builder.
- Поставете курсора в текстовото поле Масив.
-
Маркирайте клетки D6 до F11 в работния лист, за да въведете диапазона в диалоговия прозорец.
Оставете диалоговия прозорец Аргументи на функцията отворен. Формулата не е завършена. Ще завършите формулата в инструкциите по-долу.
Image
Стартирайте вложената функция MATCH
Когато влагате една функция в друга, не е възможно да отворите конструктора на формула на втората или вложена функция, за да въведете необходимите аргументи. Вложената функция трябва да бъде въведена като един от аргументите на първата функция.
Когато въвеждате функции ръчно, аргументите на функцията се разделят един от друг със запетая.
Първата стъпка за въвеждане на вложената функция MATCH е да въведете аргумента Lookup_value. Lookup_value е местоположението или референтната клетка за думата за търсене, която трябва да бъде съпоставена в базата данни.
Потърсената_стойност приема само един критерий или термин за търсене. За да търсите по множество критерии, разширете Lookup_value, като свържете или съедините две или повече препратки към клетки с помощта на символа амперсанд (&).
- В диалоговия прозорец Аргументи на функцията поставете курсора в текстовото поле Row_num.
- Въведете MATCH(.
- Изберете клетка D3, за да въведете препратката към тази клетка в диалоговия прозорец.
- Въведете & (амперсанда) след препратката към клетка D3, за да добавите втора препратка към клетка.
- Изберете клетка E3, за да въведете препратката към втората клетка.
-
Въведете , (запетая) след референтната клетка E3, за да завършите въвеждането на аргумента Lookup_value на функцията MATCH.
Image В последната стъпка от урока, Lookup_values ще бъдат въведени в клетки D3 и E3 на работния лист.
Завършете функцията Nested MATCH
Тази стъпка обхваща добавянето на аргумента Lookup_array за вложената функция MATCH. Lookup_array е диапазонът от клетки, които функцията MATCH търси, за да намери аргумента Lookup_value, добавен в предишната стъпка от урока.
Тъй като две полета за търсене бяха идентифицирани в аргумента Lookup_array, същото трябва да се направи и за Lookup_array. Функцията MATCH търси само един масив за всеки указан термин. За да въведете множество масиви, използвайте амперсанд, за да свържете масивите заедно.
- Поставете курсора в края на данните в текстовото поле Row_num. Курсорът се появява след запетаята в края на текущия запис.
- Маркирайте клетки D6 до D11 в работния лист, за да въведете диапазона. Този диапазон е първият масив, който търси функцията.
- Въведете & (амперсанд) след препратката към клетката D6:D11. Този символ кара функцията да търси два масива.
- Маркирайте клетки E6 до E11 в работния лист, за да въведете диапазона. Този диапазон е вторият масив, който търси функцията.
-
Въведете , (запетая) след референтната клетка E3, за да завършите въвеждането на аргумента Lookup_array на функцията MATCH.йени
Image - Оставете диалоговия прозорец отворен за следващата стъпка в урока.
Добавете аргумента за тип MATCH
Третият и последен аргумент на функцията MATCH е аргументът Match_type. Този аргумент казва на Excel как да съпостави Lookup_value със стойностите в Lookup_array. Наличните опции са 1, 0 или -1.
Този аргумент не е задължителен. Ако е пропуснато, функцията използва стойността по подразбиране 1.
- Ако Match_type=1 или е пропуснато, MATCH намира най-голямата стойност, която е по-малка или равна на Lookup_value. Данните в Lookup_array трябва да бъдат сортирани във възходящ ред.
- Ако Match_type=0, MATCH намира първата стойност, която е равна на Lookup_value. Данните Lookup_array могат да бъдат сортирани в произволен ред.
- Ако Match_type=-1, MATCH намира най-малката стойност, която е по-голяма или равна на Lookup_value. Данните за Lookup_array трябва да бъдат сортирани в низходящ ред.
Въведете тези стъпки след запетаята, въведена в предишната стъпка на реда Row_num във функцията INDEX:
- Въведете 0 (нула) след запетаята в текстовото поле Row_num. Това число кара вложената функция да върне точни съвпадения на термините, въведени в клетки D3 и E3.
-
Въведете ) (затваряща кръгла скоба), за да завършите функцията MATCH.
Image - Оставете диалоговия прозорец отворен за следващата стъпка в урока.
Завършете функцията INDEX
Функцията MATCH е готова. Време е да преминете към текстовото поле Column_num на диалоговия прозорец и да въведете последния аргумент за функцията INDEX. Този аргумент казва на Excel, че номерът на колоната е в диапазона D6 до F11. Този диапазон е мястото, където намира информацията, върната от функцията. В този случай, доставчик на титаниеви джаджи.
- Поставете курсора в текстовото поле Column_num.
-
Въведете 3 (числото три). Това число казва на формулата да търси данни в третата колона от диапазона D6 до F11.
Image - Оставете диалоговия прозорец отворен за следващата стъпка в урока.
Създайте формулата на масива
Преди да затворите диалоговия прозорец, превърнете вложената функция във формула за масив. Този масив позволява на функцията да търси множество термини в таблицата с данни. В този урок се съпоставят два термина: Widgets от колона 1 и Titanium от колона 2.
За да създадете формула за масив в Excel, натиснете CTRL, SHIFT и ENTERклавиша едновременно. След като бъде натисната, функцията е заобиколена от фигурни скоби, което показва, че функцията вече е масив.
- Изберете OK, за да затворите диалоговия прозорец. В Excel за Mac изберете Готово.
- Изберете клетка F3, за да видите формулата, след което поставете курсора в края на формулата в лентата за формули.
- За да конвертирате формулата в масив, натиснете CTRL+ SHIFT+ ENTER.
- A N/A грешка се появява в клетка F3. Това е клетката, в която е въведена функцията.
-
Грешката N/A се появява в клетка F3, защото клетки D3 и E3 са празни. D3 и E3 са клетките, в които функцията търси, за да намери Lookup_value. След добавяне на данни към тези две клетки грешката се заменя с информация от базата данни.
Image
Добавяне на критерии за търсене
Последната стъпка е да добавите думите за търсене към работния лист. Тази стъпка съответства на термините Widgets от колона 1 и Titanium от колона 2.
Ако формулата намери съвпадение и за двата термина в съответните колони в базата данни, тя връща стойността от третата колона.
- Изберете клетка D3.
- Въведете Widgets.
- Изберете клетка E3.
- Въведете Titanium и натиснете Enter.
- Името на доставчика, Widgets Inc., се появява в клетка F3. Това е единственият доставчик в списъка, който продава Titanium Widgets.
-
Изберете клетка F3. Функцията се появява в лентата с формули над работния лист.
{=ИНДЕКС(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
В този пример има само един доставчик за титаниеви джаджи. Ако е имало повече от един доставчик, доставчикът, посочен първи в базата данни, се връща от функцията.
Image
