Търсете стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от INDEX, MATCH намлява IF функции, можете бързо да го направите в Excel.
Как да потърся стойност на най-близкото съвпадение с повече от един критерий?
Както е показано на екранната снимка по-долу, трябва да намерите подходящия човек за работата въз основа на два критерия „специалността е компютър” и „трудовият стаж е 15 години”.
Забележка: За да работи това правилно, ако има дублиращи се специалности, работният опит на тези дублирани специалности трябва да бъде сортиран във възходящ ред.
1. Изберете празна клетка, за да изведете резултата, след това копирайте формулата по-долу в нея и натиснете Ctrl + Превключване + Въведете ключове, за да получите резултата.
=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))
Забележки: в тази формула:
- D3: D8 диапазонът от колони съдържа резултата, който търсите;
- G5 съдържа втория критерий (опит номер 15), въз основа на който ще търсите стойност;
- G4 съдържа първия критерий (Компютър), въз основа на който ще търсите стойност;
- B3: B8 е диапазонът от клетки, отговарящи на първия критерий;
- C3:C8 е диапазонът от клетки, отговарящи на втория критерий;
- Телефон за връзка: 1 е приблизително търсене, което означава, че ако точната стойност не може да бъде намерена, ще се намери най-голямата стойност, която е по-малка от търсената стойност;
- Тази формула трябва да бъде въведена като формула за масив с Ctrl + Превключване + Въведете ключове.
Как работи тази формула
Тази формула може да бъде разделена на няколко компонента:
- IF(B3:B8=G4,C3:C8): функцията IF тук връща резултата като {9;13;FALSE;FALSE;FALSE;FALSE}, което идва от тестване на стойностите в B3:B8, за да се види дали съвпадат със стойността в G4. Ако има съвпадение, връща съответната стойност, в противен случай връща FALSE. Тук намира две съвпадения и четири несъответствия.
- Формула за масив =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): функцията MATCH намира позицията на число 15 (стойността в G5) в диапазон C3:C8. Тъй като числото 15 не може да бъде намерено, то съответства на следващата най-малка стойност 13. Така че резултатът тук е 2.
- И =INDEX(D3:D8,2): Функцията INDEX връща стойността на втората клетка в диапазона D3:D8. Така че крайният резултат е Ейми.
Свързани функции
Excel IF функция
Функцията IF е една от най-простите и полезни функции в работната книга на Excel. Той извършва прост логически тест, който в зависимост от резултата от сравнението, и връща една стойност, ако резултатът е TRUE, или друга стойност, ако резултатът е FALSE.
Excel MATCH функция
Функцията MATCH на Microsoft Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на тази стойност.
Функция ИНДЕКС на Excel
Функцията INDEX връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Свързани статии
Средни клетки въз основа на множество критерии
В Excel повечето от нас може да са запознати с функциите COUNTIF и SUMIF, те могат да ни помогнат да броим или сумираме стойности въз основа на критерии. Но опитвали ли сте някога да изчислите средната стойност на стойностите въз основа на един или повече критерии в Excel? Този урок предоставя примери и формули в подробности, за да ви помогне лесно да го направите.
Кликнете, за да научите повече...
Преброяване на клетките, ако е изпълнен един от множеството критерии
Този урок споделя начините за преброяване на клетки, ако съдържат X, Y или Z... и т.н. в Excel.
Кликнете, за да научите повече...
Преброяване на уникални стойности въз основа на множество критерии
Тази статия взема някои примери за преброяване на уникални стойности въз основа на един или повече критерии в работен лист с подробни методи стъпка по стъпка.
Кликнете, за да научите повече...
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
