Двупосочно приблизително съвпадение с множество критерии
В този урок ще говорим за това как да търсим приблизително съвпадение въз основа на множество критерии, изброени както в колони, така и в редове в електронна таблица на Excel, с помощта на of INDEX, MATCH, и IF функции.
Как да извърша двупосочно приблизително съответствие с множество критерии в Excel?
За да разберете размер на дрехите за жена кой е 165.5см височина и тегло 55kg според таблицата по-горе, можете да добавите две функции MATCH към формулата: едната с функция IF получава номера на реда (пол и височина), другата получава номера на колоната (тегло). Тогава INDEX ще намери съответния размер според координатите. (Обърнете внимание, че номерът на реда е пред номера на колоната във формула INDEX.)
Общ синтаксис
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете.
- върнат_обхват: Диапазонът, от който искате комбинираната формула да върне размера. Тук се отнася за диапазона на размерите.
- търсена_стойност: Стойността, използвана от формулата за намиране на позицията на съответния размер. Тук се отнася информацията за дадения пол, височина и тегло.
- търсене_масив: Диапазонът от клетки със стойностите за сравнение с търсена_стойност. Тук се отнася до диапазоните на пол, височина и тегло.
- съвпадение_тип: 1 или -1.
1 или пропуснат (по подразбиране), MATCH ще намери най-голямата стойност, която е по-малка или равна на търсена_стойност. Стойностите в търсене_масив трябва да бъдат поставени във възходящ ред.
-1, MATCH ще намери най-малката стойност, която е по-голяма или равна на търсена_стойност. Стойностите в търсене_масив трябва да бъдат поставени в низходящ ред.
За да разберете размер на дрехите за жена кой е 165.5см височина и тегло 55kg, моля, копирайте или въведете формулата по-долу в клетка M9 и натиснете Ctrl + Превключване + Въведете за да получите резултата:
=ИНДЕКС(D5: J16,СЪВПАДА(M6,АКО(B5: B16=M5,C5:C16),1),МАЧ(M7,D4: J4,1))
Обяснение на формулата
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): Функцията IF проверява всяка стойност B5: B16 ако съвпадат със стойността в клетката M5, Жена. Ако е така, функцията IF ще запази съответните височини, изброени в C5:C16; Ако не, IF ще се върне ЛЪЖИ. Формулата ще върне масив като този: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- MATCH(M6,IF(B5:B16=M5,C5:C16), 1) = MATCH(M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): - match_type 1 принуждава функцията MATCH да върне позицията на най-голямата стойност, която е по-малка или равна на 165.5 (стойността в клетката M6) в масива. И така, MATCH ще се върне 4, позицията на стойността 165.
- MATCH(M7;D4:J4,1;XNUMX): Функцията MATCH се връща 3, тъй като най-голямата стойност, която е по-малка или равна на стойността в M7, 55, е на 3трета позиция от диапазона D4: J4.
- ИНДЕКС(D5: J16,MATCH(M6,IF(B5:B16=M5,C5:C16), 1),MATCH(M7;D4:J4,1;XNUMX)) = ИНДЕКС(D5: J16,4,3): Функцията INDEX извлича стойността в пресечната точка на 4ред и 3rd колона в диапазона на размера D5: J16, което е стойността в клетката F8, M.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Функцията IF е една от най-простите и полезни функции в работната книга на Excel. Той извършва прост логически тест, който в зависимост от резултата от сравнението, и връща една стойност, ако резултатът е TRUE, или друга стойност, ако резултатът е FALSE.
Свързани формули
Двупосочно търсене с INDEX и MATCH
За да търсим нещо както в редове, така и в колони в Excel или да кажем да търсим стойност в пресечната точка на конкретния ред и колона, можем да използваме помощта на функциите INDEX и MATCH.
Търсене на най-близко съвпадение
За да търсите най-близкото съвпадение на търсена стойност в числов набор от данни в Excel, можете да използвате функциите INDEX, MATCH, ABS и MIN заедно.
Търсете стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от функции INDEX, MATCH и IF можете бързо да го направите в Excel.
Търсене по множество критерии с INDEX и MATCH
Когато работите с голяма база данни в електронна таблица на Excel с няколко колони и надписи на редове, винаги е трудно да намерите нещо, което отговаря на множество критерии. В този случай можете да използвате формула за масив с функциите INDEX и MATCH.
Приблизително съвпадение с INDEX и MATCH
Има моменти, когато трябва да намерим приблизителни съвпадения в Excel, за да оценим представянето на служителите, да оценим резултатите на учениците, да изчислим пощенските разходи въз основа на теглото и т.н. В този урок ще говорим как да използваме функциите INDEX и MATCH, за да извлечем резултати, от които се нуждаем.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
