Формула за търсене в Excel с няколко критерия

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

Формулата за масив включва вграждането на функцията MATCH в рамките на функцията INDEX .

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

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

01 от 09

Въвеждане на данните за урока

Първата стъпка в урока е да въведете данните в работен лист на Excel.

За да изпълните стъпките в урока, въведете данните в изображението по-горе в следните клетки .

• Въведете най-горния диапазон от данни в клетки D1 ​​до F2
• Въведете втория обхват в клетки D5 до F11

Редове 3 и 4 остават празни, за да могат да се приспособят към формулата на масива, създадена по време на този урок.

Урокът не включва форматирането, което се вижда в изображението, но това няма да повлияе на начина на работа на формулата за търсене.

Информация за опциите за форматиране, подобни на тези, показани по-горе, можете да намерите в това базово обучение за форматиране на Excel.

02 от 09

Стартиране на функцията INDEX

Функцията INDEX е една от малкото в Excel, която има няколко формуляра. Функцията има формуляр Array и референтен формуляр .

Формулярът Array връща действителните данни от база данни или таблица с данни, докато Референтната форма ви дава референтната клетка или местоположението на данните в таблицата.

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

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

Инструкции Стъпки

1. Кликнете върху клетката F3, за да я превърнете в активна клетка . Тук ще въведем вложената функция.
2. Кликнете върху раздела Формули в лентата с менюта.
3. Изберете " Търсене и справка" от панделката, за да отворите падащия списък на функциите.
4. Кликнете върху INDEX в списъка, за да изведете диалоговия прозорец Избор на аргументи .
5. Изберете опцията масив, row_num, col_num в диалоговия прозорец.
6. Кликнете върху OK, за да отворите диалоговия прозорец на функцията INDEX.

03 от 09

Въвеждане на Argument аргумент на функцията INDEX

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

За този урок този аргумент ще бъде нашата примерна база данни .

Инструкции Стъпки

1. В диалоговия прозорец на функцията INDEX кликнете върху линията на масива .
2. Маркирайте клетки D6 до F11 в работния лист, за да въведете диапазона в диалоговия прозорец.

04 от 09

Стартиране на вложената MATCH функция

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

Вложената функция трябва да бъде въведена като един от аргументите на първата функция.

В този наръчник вмъкнатата функция MATCH и нейните аргументи ще бъдат въведени във втория ред на диалоговия прозорец на функцията INDEX - ред Row_num .

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

Въвеждане на аргумента на Lookup_value на функцията MATCH

Първата стъпка при въвеждане на вградената функция MATCH е да въведете аргумента Lookup_value .

Lookup_value ще бъде местоположението или референтната клетка за термина за търсене, който искаме да съпоставим в базата данни.

Обикновено Lookup_value приема само един критерий или термин за търсене. За да търсите няколко критерия, трябва да разширим Lookup_value .

Това става чрез обединяване или свързване на две или повече референтни клетки заедно със символа " & " на амперсанда.

Инструкции Стъпки

1. В диалоговия прозорец на функцията INDEX кликнете върху реда Row_num .
2. Въведете съвпадението на името на функцията, последвано от отворена кръгла конзола " ( "
3. Кликнете върху клетка D3, за да въведете тази клетка в диалоговия прозорец.
4. Въведете амперсанд " & " след референтната клетка D3, за да добавите втора клетка.
5. Кликнете върху клетка E3, за да въведете втората клетка в диалоговия прозорец.
6. Въведете запетая "," след референтната клетка E3, за да завършите въвеждането на аргумента Lookup_value на MATCH функцията.
7. Оставете диалоговия прозорец на функцията INDEX отворен за следващата стъпка в урока.

В последната стъпка на урока, Lookup_values ​​ще бъдат въведени в клетки D3 и E3 на работния лист.

05 от 09

Добавяне на Lookup_array за функцията MATCH

Тази стъпка обхваща добавянето на аргумента Lookup_array за вложената MATCH функция.

Lookup_array е диапазонът от клетки, които функцията MATCH ще търси, за да намери аргумента Lookup_value , добавен в предишната стъпка на урока.

Тъй като идентифицирахме две полета за търсене в аргумента Lookup_array , трябва да направим същото за Lookup_array . Функцията MATCH търси само един масив за всеки определен срок.

За да въведем множество масиви отново използваме ampersand " & ", за да обединим масивите заедно.

Инструкции Стъпки

Тези стъпки трябва да бъдат въведени след запетаята, въведена в предишната стъпка на реда Row_num в диалоговия прозорец на функцията INDEX.

1. Кликнете върху реда Row_num след запетаята, за да поставите точката за вмъкване в края на текущото записи.
2. Маркирайте клетки D6 до D11 в работния лист, за да влезете в обхвата. Това е първият масив, който трябва да търсите.
3. Въведете амперсанд " & " след референтната клетка D6: D11, защото искаме функцията да търси две масиви.
4. Маркирайте клетки от E6 до E11 в работния лист, за да влезете в обхвата. Това е вторият масив, който трябва да търсите.
5. Въведете запетая "," след референтната клетка E3, за да завършите въвеждането на аргумента Lookup_array на MATCH функцията.
6. Оставете диалоговия прозорец на функцията INDEX отворен за следващата стъпка в урока.

06 от 09

Добавяне на типа на съвпадението и завършване на функцията 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.

1. Следвайки запетаята на реда Row_num , въведете нула " 0 ", тъй като искаме вложената функция да върне точните съвпадения на термините, които въвеждаме в клетките D3 и E3.
2. Въведете затваряща кръгла скоба " ) ", за да завършите функцията MATCH.
3. Оставете диалоговия прозорец на функцията INDEX отворен за следващата стъпка в урока.

07 от 09

Назад към функцията INDEX

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

Този трети и последен аргумент е аргументът Column_num , който показва на Excel числото на колоната в диапазона D6 до F11, където ще намери информацията, която искаме да върне от функцията. В този случай доставчик на джаджи за титан .

Инструкции Стъпки

1. Кликнете върху колоната Column_num в диалоговия прозорец.
2. Въведете номер три " 3 " (без кавички) на този ред, тъй като ние търсим данни в третата колона от диапазона D6 до F11.
3. Не кликнете върху OK или затворете диалоговия прозорец на функцията INDEX. Той трябва да остане отворен за следващата стъпка в урока - създаване на матрична формула .

08 от 09

Създаване на формулата на масива

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

Формулата с масива е това, което й позволява да търси няколко термини в таблицата с данни. В този урок искаме да съчетаем два термина: Widgets от колона 1 и титан от колона 2.

Създаването на формула за масив в Excel става чрез натискане на клавишите CTRL , SHIFT и ENTER на клавиатурата едновременно.

Ефектът от натискането на тези клавиши е да заобиколите функцията с къдрави скоби: {}, което показва, че сега тя е матрична формула.

Инструкции Стъпки

1. С приключения диалогов прозорец, който все още е отворен от предишната стъпка на този урок, натиснете и задръжте клавишите CTRL и SHIFT на клавиатурата, след това натиснете и освободете клавиша ENTER .
2. Ако се направи правилно, диалоговият прозорец ще се затвори и в клетката F3 - клетката, в която въведохме функцията, ще се появи грешка # N / A.
3. В клетката F3 се появява грешка # N / A, защото клетките D3 и E3 са празни. D3 и E3 са клетките, за които ние казахме на функцията да намерим Lookup_values ​​в стъпка 5 на урока. След като данните бъдат добавени към тези две клетки, грешката ще бъде заменена с информация от базата данни .

09 от 09

Добавяне на критериите за търсене

Последната стъпка в урока е да добавите думите за търсене в нашия работен лист.

Както бе споменато в предишната стъпка, ние търсим да съответстваме на думите Widgets от колона 1 и Titanium от колона 2.

Ако и само ако нашата формула намери съвпадение и за двата термина в съответните колони в базата данни, тя ще върне стойността от третата колона.

Инструкции Стъпки

1. Кликнете върху клетката D3.
2. Напишете Widgets и натиснете клавиша Enter на клавиатурата.
3. Кликнете върху клетката E3.
4. Въведете титан и натиснете клавиша Enter на клавиатурата.
5. Името на доставчика, Widgets Inc., трябва да се появи в клетка F3 - местоположението на функцията, тъй като е единственият доставчик, който продава Titanium Widgets.
6. Когато кликнете върху клетката F3 пълната функция
   {= ИНДЕКС (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   се появява в лентата за формули над работния лист .

Забележка: В нашия пример имаше само един доставчик на джаджи за титан. Ако има повече от един доставчик, доставчикът, посочен първо в базата данни, се връща от функцията.