Как получить несколько значений того же критериев в Microsoft Excel 2010
В этой статье мы узнаем, как получить несколько значений из одних и тех же критериев в Microsoft Excel 2010.
Легко найти значение с помощью одного уникального ключа в таблице. Мы можем просто использовать ВПР. Но если у вас нет этого уникального столбца в ваших данных и вам нужно искать в нескольких столбцах, чтобы найти значение, ВПР не поможет.
Поэтому для поиска значения в таблице с несколькими критериями мы будем использовать формулу INDEX-MATCH-INDEX.
Общая формула для поиска по нескольким критериям
|
=INDEX(lookup_range,MATCH(1,INDEX((criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0,1),0)) |
_lookup_range: _ Это диапазон, из которого вы хотите получить значение.
_Criteria1, Criteria2, Criteria N: _ Это критерии, которым вы хотите соответствовать в диапазоне range1, range2 и Range N. Вы можете иметь до 270 пар критериев — диапазонов.
_Range1, range2, rangeN: _ Это диапазоны, в которых вы будете соответствовать вашим критериям.
Как это будет работать? Давайте посмотрим… ===== Пример INDEX и MATCH с несколькими критериями Здесь у меня есть таблица данных. Я хочу получить имя клиента, используя дату бронирования, строителя и площадь. Итак, у меня есть три критерия и один диапазон поиска.
Запишите эту формулу в ячейку I4, нажмите Enter.
|
=INDEX(E2:E16,MATCH(1,INDEXI1=A2:A16)(I2=B2:B16)(I3=C2:C16),0,1),0 |
Как это работает:
Мы уже знаем, как INDEX и функция MATCH работают в EXCEL, поэтому я не собираюсь здесь объяснять. Мы поговорим о трюке, который мы здесь использовали.
(I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16): вот основная часть. Каждая часть этого оператора возвращает массив истинных ложных значений.
Когда логические значения умножаются, они возвращают массив из 0 и 1.
Умножение работает как оператор И. Hense, когда все значения истинны, только тогда он возвращает 1 иначе 0 (I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16) Это в целом вернет [width = «100%», cols = «100 % «,]
|
\{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}* \{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}* \{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} |
Что переведет в
|
\{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
ИНДЕКС ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): функция ИНДЕКС вернет тот же массив (\ {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) в функцию ПОИСКПОЗ как поисковый массив.
ПОИСКПОЗ (1, ИНДЕКС ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): функция ПОИСКПОЗ будет искать 1 в массиве \ {0; 0; 0; 0 ; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. И вернет номер индекса первой 1, найденной в массиве. Здесь 8.
INDEX (E2: E16, MATCH (1, INDEX I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1), 0:
Наконец, INDEX вернет значение из заданного диапазона (E2: E16) по найденному индексу (8).
Просто ?. К сожалению, не могу сделать это проще.
Решение с массивом Если вы можете нажать CTRL + SHIFT + ENTER, следовательно, вы можете исключить внутреннюю функцию ИНДЕКС. Просто напишите эту формулу и нажмите CTRL + SHIFT ENTER.
|
=INDEX(E2:E16,MATCH(1,(I1=A2:A16)(I2=B2:B16)(I3=C2:C16),0)) |
Общая формула массива для поиска по нескольким критериям
|
=INDEX(lookup_range,MATCH(1,(criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0)) |
Формула работает так же как указано выше.
Надеюсь, эта статья о том, как получить несколько значений из одних и тех же критериев в Microsoft Excel 2010, носит пояснительный характер. Дополнительные статьи об извлечении значений и соответствующих формулах Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ею со своими друзья на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].
Статьи по теме:
link: / lookup-formulas-use-vlookup-from-two-or-more-lookup-tables [Использовать ВПР из двух или более таблиц поиска] | Для поиска из нескольких таблиц мы можем использовать подход ЕСЛИОШИБКА. Для поиска из нескольких таблиц ошибка используется как переключатель для следующей таблицы. Другой метод может быть подходом If.
link: / lookup-Formulas-how-to-do-case-sensitive-lookup-in-excel [Как делать поиск с учетом регистра в Excel] | функция ВПР в Excel не чувствительна к регистру и возвращает первое совпадающее значение из списка. INDEX-MATCH не является исключением, но его можно изменить, чтобы сделать его чувствительным к регистру. Давайте посмотрим, как… link: / lookup-formulas-lookup-часто-появляющийся-текст-с-критериями-в-excel [Поиск часто появляющегося текста с критериями в Excel] | Поиск наиболее часто появляется в тексте в диапазоне, который мы используем ИНДЕКС-ПОИСКПОЗ с функцией РЕЖИМ. Вот способ.
Популярные статьи:
link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.