Този урок ще ви научи как да използвате комбинацията INDEX & MATCH за извършване на търсения в Excel и Google Sheets.
INDEX & MATCH, Перфектната двойка
Нека разгледаме по -отблизо някои от начините, по които можете да комбинирате функциите INDEX и MATCH. Функцията MATCH е проектирана да връща относителното положение на елемент в масив, докато функцията INDEX може да извлече елемент от масив с определена позиция. Тази синергия между двете им позволява да извършват почти всеки вид търсене, от което може да се нуждаете.
Комбинацията INDEX / MATCH исторически е била използвана като заместител на функцията VLOOKUP. Една от основните причини е възможността да търсите вляво (вижте следващия раздел).
Забележка: новата функция XLOOKUP вече може да извършва търсене с ляв поглед.
Търсене вляво
Нека използваме тази таблица с баскетболна статистика:
Искаме да намерим играча на Боб #. Тъй като Player # е вляво от колоната с име, не можем да използваме VLOOKUP.
Вместо това бихме могли да направим основна MATCH заявка за изчисляване на реда на Боб
= МАТЧ (H2, B2: B5, 0)
Това ще търси точно съвпадение на думата „Боб“ и затова нашата функция ще върне числото 2, тъй като „Боб“ е в 2nd позиция.
След това можем да използваме функцията INDEX, за да върнем Player #, съответстващ на ред. Засега нека просто въведем ръчно „2“ във функцията:
= ИНДЕКС (A2: A5, 2)
Тук INDEX ще се позовава на A3, тъй като това е 2nd клетка в диапазона A2: A5 и връща резултата от 42. След това за нашата обща цел можем да комбинираме тези две в:
= ИНДЕКС (A2: A5, MATCH (H2, B2: B5, 0))
Предимството тук е, че успяхме да върнем резултат от колона вляво от мястото, където търсихме.
Двуизмерно търсене
Нека да разгледаме нашата таблица от преди:
Този път обаче искаме да извлечем конкретна статистика. Извикахме, че искаме да търсим Rebounds в клетка H1. Вместо да се налага да пишете няколко оператора IF, за да определите от коя колона да получите резултата, можете отново да използвате функция MATCH. Функцията INDEX ви позволява да посочите стойността на реда и стойността на колоната. Ще добавим друга функция MATCH тук, за да определим коя колона искаме. Това ще изглежда така
= MATCH (H1, A1: E1, 0)
Нашата клетка в H1 е падащо меню, което ни позволява да изберем каква категория искаме да търсим, а след това нашата MATCH определя към коя колона в таблицата принадлежи. Нека включим този нов бит в предишната формула. Обърнете внимание, че трябва да променим първия аргумент за две измерения, тъй като вече не просто искаме резултат от колона А.
= ИНДЕКС (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
В нашия пример искаме да намерим Rebounds за Чарли. Нашата формула ще оцени това така:
= INDEX (A2: E5, MATCH ("Чарли", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Сега създадохме гъвкава настройка, която позволява на потребителя да извлича всяка стойност, която иска от нашата таблица, без да се налага да пише множество формули или разклоняващи IF изрази.
Множество секции
Не се използва често, но INDEX има пети аргумент, който може да се даде, за да се определи кой ■ площ в рамките на един аргумент да се използва. Това означава, че имаме нужда от начин да прехвърлим множество области в първия аргумент. Можете да направите това, като използвате допълнителен набор от скоби. Този пример ще илюстрира как можете да извлечете резултати от различни таблици на работен лист, използвайки INDEX.
Ето оформлението, което ще използваме. Имаме статистика за три различни тримесечия на игра.
В клетки H1: H3 създадохме падащи списъци за валидиране на данни за различните ни възможности за избор. Падащото меню за тримесечието идва от J2: J4. Ще използваме това за друг MATCH израз, за да определим коя област да използваме. Нашата формула в H4 ще изглежда така:
= ИНДЕКС ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Вече обсъждахме как работят вътрешните две функции MATCH, така че нека се съсредоточим върху първия и последния аргумент:
= ИНДЕКС ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Ние дадохме на функцията INDEX множество масиви в първия аргумент, като ги ограждаме всички в скоби. Другият начин, по който можете да направите това, е да използвате формули - Определете име. Можете да определите име, наречено „MyTables“ с дефиниция на
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Да се върнем на цялото изявление. Нашите различни функции MATCH ще кажат на функцията INDEX точно къде да търсите. Първо, ще определим, че „Чарли“ е 3rd ред. След това искаме „Rebounds“, който е 4th колона. И накрая, ние решихме, че искаме резултата от 2nd маса. Формулата ще оцени по този начин така:
= ИНДЕКС ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Както споменахме в началото на този пример, вие сте ограничени до това таблиците да са на един и същ работен лист. Ако можете да напишете правилни начини да кажете на вашия INDEX от кой ред, колона и/или област искате да извличате данни, INDEX ще ви служи много добре.
Google Таблици -ИНДЕКС & МАТЧ
Всички горни примери работят абсолютно същите в Google Sheets, както и в Excel.
