Если вы здесь, это означает, что вы пытаетесь использовать ВПР для получения нескольких значений из набора данных. Но давайте прямо скажем, ВПР НЕ МОЖЕТ ВОЗВРАТИТЬ НЕСКОЛЬКО ЗНАЧЕНИЙ. Но это не значит, что мы не можем этого сделать. Мы можем выполнить поиск, который извлекает несколько значений, а не только первое.

286

Общая формула

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Массив: диапазон, из которого вы хотите получить данные.

lookup_value: ваше lookup_value, которое вы хотите отфильтровать.

lookup_value_range: диапазон, в котором вы хотите отфильтровать lookup_value.

Первая ячейка в диапазоне lookup_value: если ваш диапазон lookup_value равен $ A $ 5: $ A $ 100, то это $ A $ 5.

Важно: все должно быть link: / excel-range-name-absolute-reference-in-excel [absolute referenced]. lookup_value может быть относительным в соответствии с требованиями.

Введите его как формулу массива. После написания формулы нажмите клавиши CTRL + SHIFT + ENTER, чтобы преобразовать ее в формулу массива.

Пример поиска нескольких результатов. У меня есть данные об учениках в диапазоне A2: E14. В ячейке G1 у меня есть раскрывающийся список значений региона, например. Центр, Восток, Север, Юг и Запад.

Теперь я хочу, чтобы какой бы регион у меня ни был в G1, список всех студентов из этого региона должен отображаться в столбце H.

287

Чтобы найти несколько значений в Excel, давайте определим наши переменные.

Массив: $ C $ 2: $ C $ 14 lookup_value: $ G $ 1 Диапазон значений lookup: $ A $ 2: $ A $ 14 Первая ячейка диапазона lookup_value: $ A $ 2 Согласно приведенным выше данным, наша формула для получения нескольких значений в Excel будет:

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

Скопируйте эту формулу в H2 и нажмите CTRL + SHIFT + ENTER. Теперь перетащите эту формулу вниз, пока не получите ошибку # ЧИСЛО. #NUM указывает на то, что для этого lookup_value не осталось других значений, соответствующих этому lookup_value, и это конец списка.

288

Если #NUM вас раздражает, вы можете поставить перед формулой link: / logic-formulas-excel-iferror-function [IFERROR function] и отобразить некоторую значимую информацию вместо ошибки.

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

Это покажет — Список концов — когда будут показаны все элементы.

Теперь давайте разберемся, КАК ЭТО РАБОТАЕТ.

Хотя формула может показаться сложной, но идея проста. Нам нужно получить порядковый номер каждого вхождения значения, а затем получить значения с помощью link: / lookup-formulas-excel-index-function [INDEX function] Excel.

Следовательно, основная задача — получить массив номеров индексов lookup_value. Чтобы получить номера индексов, мы использовали link: / tips-if-condition-in-excel [IF] ʻand`link: / lookup-and-reference-excel-row-function [ROW]

функции. Формула действительно сложная, давайте разберемся с ней.

Мы хотим получить значения из столбца учеников, поэтому наш массив для link: / lookup-formulas-excel-index-function [INDEX function] равен $ C $ 2: $ C $ 14. Теперь нам нужно указать номера строк из $ A $ 2: $ A $ 14 (значение поиска), в котором существует значение G1 (на данный момент предположим, что G1 имеет центральное значение).

IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): теперь эта часть возвращает номер строки, если значение G1 (центральное) в ячейке находится в диапазоне $ A $ 2: $ A $ 14 else вернет FALSE. В этом примере будет возвращено

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.

  • Теперь, поскольку указанный выше массив содержит номер строки из 1-й строки (1: 1), и нам нужна строка, начинающаяся с нашего массива (A2: A14). Для этого мы используем -ROW ($ A $ 2) +1 в формуле ЕСЛИ. Это вернет номер строки перед началом нашего массива.

В этом примере это -1. Если бы он начинался с A3, он вернул бы -2 и т. Д. число будет вычтено из каждого числа в массиве, возвращаемом IF. Итак, наконец, IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) это будет преобразовано в \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}.

  • Затем этот массив окружен `ссылкой: / статистическая-формула-excel-small-function [SMALL] `

функция. Эта функция возвращает N-й малый есть значение в данном массиве. Теперь у нас есть link: / statistics-formula-excel-small-function [SMALL] (\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} , СТРОКА (1: 1)).

СТРОКА (1: 1) вернет 1. Следовательно, вышеуказанная функция вернет 1-е наименьшее значение в массиве, то есть 2.

Когда вы скопируете эту формулу в ячейки ниже, ROW (1: 1) превратится в ROW (2: 2) и вернет второе наименьшее значение в массиве, то есть 7 и так далее. Это позволяет функции сначала возвращать первое найденное значение. Но если вы хотите сначала получить последнее найденное значение, используйте функцию LARGE вместо функции SMALL.

Теперь, используя значения, возвращаемые вышеуказанными функциями, функция ИНДЕКС легко возвращает каждое совпадающее значение из диапазона.

Итак, да, ребята, вы можете ПРОСМОТРЕТЬ несколько соответствующих значений по одному значению поиска. В приведенном выше гифке я использовал функцию ЕСЛИОШИБКА для обнаружения ошибок и условное форматирование, чтобы сделать его немного наглядным. В приведенном выше примере я использовал множество других функций и методов, которые я объяснил в других статьях.

Скачать файл: