Търсене по множество критерии с INDEX и MATCH
Когато работите с голяма база данни в електронна таблица на Excel с няколко колони и надписи на редове, винаги е трудно да намерите нещо, което отговаря на множество критерии. В този случай можете да използвате формула за масив с the INDEX намлява MATCH fсанкции.
Как да извършим търсене с множество критерии?
За да разберете продукт че е бял намлява среда-размерени с цена $18 както е показано на горната снимка, можете да се възползвате от булевата логика, за да генерирате масив от 1s и 0s, за да покажете редовете, които отговарят на критериите. След това функцията MATCH ще намери позицията на първия ред, който отговаря на всички критерии. След това INDEX ще намери съответния ID на продукта на същия ред.
Общ синтаксис
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете.
- върнат_обхват: Диапазонът, от който искате комбинираната формула да върне идентификатора на продукта. Тук се отнася за гамата ID на продукта.
- критерии_стойност: Критериите, използвани за намиране на позицията на идентификатора на продукта. Тук се отнася за стойностите в клетките H4, H5 и H6.
- критерии_обхват: Съответните диапазони, където критерии_стойности са изброени. Тук се отнасят за цвят, размер и ценови диапазони.
- match_type 0: Принуждава MATCH да намери първата стойност, която е точно равна на търсена_стойност.
За да намерите продукта, който е бял намлява среда-размерени с цена $18, моля, копирайте или въведете формулата по-долу в клетка H8 и натиснете Ctrl + Превключване + Въведете за да получите резултата:
=ИНДЕКС(B5: B10,МАЧ(1,("бяло"=C5:C10) * ("среден"=D5: D10) * (18=E5: E10),0))
Или използвайте препратка към клетка, за да направите формулата динамична:
=ИНДЕКС(B5: B10,МАЧ(1,(H4=C5:C10) * (H5=D5: D10) * (H6=E5: E10),0))
Обяснение на формулата
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Формулата сравнява цвета в клетката H4 срещу всички цветове в гамата C5:C10; сравнява размера в H5 срещу всички размери в D5: D10; сравнява цената в H6 срещу всички цени в E5: E10. Първоначалният резултат е следният:
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
Умножението ще преобразува TRUE и FALSE в 1s и 0s:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
След умножението ще имаме единичен масив като този:
{0;0;0;0;1;0}. - MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = MATCH(1,{0;0;0;0;1;0}, 0): Типът_съвпадение 0 пита функцията MATCH, за да намери точното съвпадение. След това функцията ще върне позицията на 1 в масива {0;0;0;0;1;0}, кое е 5.
- ИНДЕКС(B5: B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = ИНДЕКС(B5: B10,5): Функцията INDEX връща 5та стойност в диапазона на идентификатора на продукта B5: B10, кое е 30005.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Свързани формули
Търсете стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от функции INDEX, MATCH и IF можете бързо да го направите в Excel.
Приблизително съвпадение с INDEX и MATCH
Има моменти, когато трябва да намерим приблизителни съвпадения в Excel, за да оценим представянето на служителите, да оценим резултатите на учениците, да изчислим пощенските разходи въз основа на теглото и т.н. В този урок ще говорим как да използваме функциите INDEX и MATCH, за да извлечем резултати, от които се нуждаем.
Търсете стойности от друг работен лист или работна книга
Ако знаете как да използвате функцията VLOOKUP за търсене на стойности в работен лист, стойностите на vlookup от друг работен лист или работна книга няма да представляват проблем за вас. Урокът ще ви покаже как да търсите vlooking стойности от друг работен лист в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
