Обща формула
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Обобщение
За да търсите стойности с INDEX и MATCH, използвайки множество критерии, можете да използвате формула на масив. В показания пример формулата в H8 е:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
Обяснение
Това е по-усъвършенствана формула. За основи вижте Как да използвате INDEX и MATCH.
Обикновено формулата INDEX MATCH е конфигурирана с MATCH, зададен да преглежда обхвата от една колона и да предоставя съвпадение въз основа на зададени критерии. Без обединяване на стойности в помощна колона или в самата формула, няма начин да се предоставят повече от един критерий.
Тази формула работи около това ограничение, като използва логическа логика, за да създаде масив от единици и нули, за да представи редове, отговарящи на всички 3 критерия, след което използва MATCH, за да съответства на първия 1 намерен. Временният масив от единици и нули се генерира с този фрагмент:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Тук сравняваме елемента в H5 спрямо всички елементи, размера в H6 спрямо всички размери и цвета в H7 спрямо всички цветове. Първоначалният резултат е три масива от TRUE / FALSE резултати като този:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Съвет: използвайте F9, за да видите тези резултати. Просто изберете израз в лентата с формули и натиснете F9.
Операцията по математика (умножение) преобразува стойностите TRUE FALSE в 1s и 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
След умножението имаме един масив като този:
(0;0;1;0;0;0;0)
който се подава във функцията MATCH като справочен масив със справочна стойност 1:
MATCH(1,(0;0;1;0;0;0;0))
На този етап формулата е стандартна формула INDEX MATCH. Функцията MATCH връща 3 към INDEX:
=INDEX(E5:E11,3)
и INDEX връща краен резултат от $ 17,00.
Визуализация на масива
Обяснените по-горе масиви могат да бъдат трудни за визуализиране. Изображението по-долу показва основната идея. Колони B, C и D съответстват на данните в примера. Колона F се създава чрез умножаване на трите колони заедно. Това е масивът, предаден на MATCH.
Версия без масив
Възможно е да добавите още ИНДЕКС към тази формула, като се избягва необходимостта да се въвежда като формула на масив с управление + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Функцията INDEX може да обработва масиви от самото начало, така че вторият INDEX се добавя само за „улавяне“ на масива, създаден с логическата операция на булевата логика, и връщането на същия масив отново в MATCH. За целта INDEX е конфигуриран с нула редове и една колона. Трикът с нулев ред кара INDEX да върне колона 1 от масива (който така или иначе вече е една колона).
Защо бихте искали версията без масив? Понякога хората забравят да въведат формула на масив с control + shift + enter и формулата връща неправилен резултат. Така че, формула без масив е по-„бронирана“. Компромисът обаче е по-сложна формула.
Забележка: В Excel 365 не е необходимо да въвеждате формули за масив по специален начин.
