Индекс съвпадение на множество критерии Редове и колони
Всички ние използваме VLOOKUP ден след ден, за да извлечем данните, а също така сме наясно с факта, че VLOOKUP може да извлича данните отляво надясно, така че стойността за търсене винаги трябва да е от лявата страна на колоните с резултати. Имаме обаче няколко алтернативи, които могат да се използват като алтернатива на функцията VLOOKUP в Excel. С усъвършенствана технология можем да използваме тези формули INDEX + MATCH, за да съответстваме на множество критерии за редове и колони. Така че тази специална статия ще ви преведе подробно за тази техника.
Как да използвам формула INDEX + MATCH за съвпадение на множество критерии?
Тук ние обясняваме как да използваме формулата индекс + съвпадение за съвпадение на множество критерии за редове и колони с примери.
Пример # 1 - Формула INDEX + MATCH
Не по-голямата част от функциите за търсене на потребители на Excel извън VLOOKUP, причините могат да бъдат толкова много. Както и да е, нека имаме просто въведение в тази формула, преди да преминем към напреднало ниво.
Например погледнете по-долу структурата на данните в Excel.
Имаме имена на „Rep. Продажби“ и съответните им стойности на продажби. От друга страна, имаме падащ списък на „Rep. Продажби“ в клетка D2.
Въз основа на избора, който правим от падащия списък, сумата на продажбите трябва да се появи в клетка E2.
Проблемът е, че не можем да приложим формулата VLOOKUP, тъй като справочната стойност „Sales Rep“ е вдясно от колоната с резултати „Продажби“, така че в тези случаи можем да използваме формула за търсене на комбинация INDEX + MATCH.
INDEX търси споменатата стойност на номера на реда в диапазона A2: A11 и в този диапазон трябва да посочим от кой ред трябва да идва стойността на продажбите. Тази стойност на реда се основава на името „Sales Rep“, избрано в падащия списък в Excel, така че функцията MATCH търси номера на реда „Sales Rep“ в диапазона B2: B11 и връща номера на реда на съответната стойност .
Пример # 2 - Множество критерии във формула INDEX + MATCH
Сега имаме структура на данни като тази по-долу.
Имаме месечни стойности на продажбите на „Търговски представител“. От тази таблица се нуждаем от динамични резултати, като в клетка A15, създадох падащ списък „Търговски представител“, а в клетка B14 създадох падащ списък „Месец“.
Въз основа на селекцията, направена в тези две клетки, нашата формула трябва да извлече данните от горната таблица.
Например, ако избера „Rep 8“ и „Apr“, тогава той трябва да покаже стойността на продажбите на „Rep 8“ за месеца „април“
Така че, в тези случаи трябва да съпоставим както редове, така и колони. Следвайте стъпките по-долу, за да приложите формулата, за да съответства както на редове, така и на колони.
Стъпка 1: Отворете функцията INDEX в клетка B15.
Стъпка 2: Първият аргумент на функцията INDEX е „Array“, т.е. от кой диапазон клетки се нуждаем от резултата. Така че, в този случай се нуждаем от стойности на продажбите, така че изберете диапазона от клетки от B2 до G11.
Стъпка 3: Следващ аргумент на функцията INDEX, от кой ред от избрания диапазон се нуждаем от резултата. В този случай трябва да стигнем до номера на реда „Търговски представител“ въз основа на избора, направен в падащата клетка на клетка A15. Така че за динамично извличане на номера на реда въз основа на селекцията отворена функция MATCH.
Стъпка 4: ТЪРСЕНОТО СТОЙНОСТ на функцията MATCH е „Търговски представител“, така че изберете клетка A15 като референтна.
Стъпка 5: Lookup Array ще бъде обхватът на имената на търговския представител в основната таблица. Така че изберете диапазон от A2 до A11.
Стъпка 6: Типът на съвпадение на функцията MATCH ще бъде точен, така че въведете нула като стойност на аргумента.
Стъпка 7: Следващият аргумент на функцията INDEX е „Номер на колона“, т.е. от избрания диапазон от клетки, от коя колона се нуждаем от резултата. Това зависи от месеца, който избираме от падащия списък на клетката B14. Затова, за да получите автоматично номера на колоната, отворете друга функция MATCH.
Стъпка 8: Тази стойност за търсене по време ще бъде името на месеца, така че изберете клетка B14 като референция.
Стъпка 9: Масивът за търсене ще бъде месечен диапазон от клетки в основната таблица, т.е. от B1 до G1.
Стъпка 10: Последният аргумент е тип съвпадение; изберете „Точно съвпадение“ като критерии. Затворете две скоби и натиснете клавиша Enter, за да получите резултата.
Както виждаме по-горе, избрахме „Rep 6“ и „Apr“ като месец и нашата формула върна стойността на продажбите за месец „Apr“ за „Rep 6“.
Забележка: Жълто оцветената клетка е ориентир за вас.
Неща за запомняне
- Комбинацията от INDEX + MATCH може да бъде по-мощна от формулата VLOOKUP.
- INDEX & MATCH могат да съвпадат както със заглавките на редове, така и с колони и да върнат резултата от средната таблица.
- MATCH може да върне номера на реда и номера на колоната на заглавките на таблицата на двата реда и колоните.
