Формула Excel: извлечение всех совпадений с помощью вспомогательного столбца -

Содержание

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

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Резюме

Один из способов извлечения нескольких совпадений в Excel - использовать ИНДЕКС и ПОИСКПОЗ со вспомогательным столбцом, который помечает совпадающие данные. Это позволяет избежать сложности более сложной формулы массива. В показанном примере формула H6 имеет следующий вид:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

где ct (G3), data (B3: E52) и helper (E3: E52) являются именованными диапазонами.

Объяснение

Проблема с формулами поиска, которые извлекают более одного совпадения, заключается в управлении дубликатами (т. Е. Множественными совпадениями). Формулы поиска, такие как ВПР и ИНДЕКС + ПОИСКПОЗ, могут легко найти первое совпадение, но гораздо сложнее найти "все совпадения", когда критерии находят более одного совпадения.

Эта формула решает эту проблему с помощью вспомогательного столбца, который возвращает числовое значение, которое можно использовать для простого извлечения нескольких совпадений. Формула в вспомогательном столбце выглядит так:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Вспомогательный столбец проверяет каждую строку данных, чтобы убедиться, что отдел в столбце C соответствует значению в I3, а здание в столбце D соответствует значению в J3. Оба логических теста должны возвращать ИСТИНА, чтобы И вернуло ИСТИНА.

Для каждой строки результат функции И добавляется к «значению выше» во вспомогательном столбце, чтобы сгенерировать счетчик. Практический эффект этой формулы - увеличивающийся счетчик, который изменяется только при обнаружении (нового) совпадения. Затем значение остается неизменным до тех пор, пока не будет найдено следующее совпадение. Это работает, потому что результаты ИСТИНА / ЛОЖЬ, возвращаемые с помощью И, приводятся к значениям 1/0 как часть операции суммирования. ЛОЖНЫЕ результаты ничего не добавляют, а ИСТИННЫЕ результаты добавляют 1.

Вернувшись в область извлечения, формула поиска для имени в столбце H выглядит следующим образом:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Часть формулы INDEX + MATCH ищет имя первого найденного совпадения, используя номер строки в столбце G в качестве значения совпадения:

INDEX(data,MATCH($G6,helper,0),1)

INDEX получает все 3 столбца данных в виде массива (именованный диапазон «данные»), а ПОИСКПОЗ настроен на соответствие номеру строки внутри вспомогательного столбца (именованный диапазон «помощник») в режиме точного совпадения (третий аргумент установлен на ноль) .

Здесь становится очевидным умение формулы. Вспомогательный столбец, очевидно, содержит дубликаты, но это не имеет значения, потому что MATCH будет соответствовать только первому значению. По замыслу, каждое «первое значение» соответствует правильной строке в таблице данных.

Формулы в столбцах I и J такие же, как и в H, за исключением номера столбца, который в каждом случае увеличивается на единицу.

Оператор IF, который является оболочкой для формулы ИНДЕКС / ПОИСКПОЗ, выполняет простую функцию - он проверяет номер каждой строки в области извлечения, чтобы убедиться, что номер строки меньше или равен значению в G3 (именованный диапазон "ct"), который равен общее количество всех совпадающих записей. Если да, запускается логика ИНДЕКС / ПОИСКПОЗ. Если нет, IF выводит пустую строку ("").

Формула в G3 (названный диапазон "ct") проста:

=MAX(helper)

Поскольку максимальное значение в вспомогательном столбце совпадает с общим количеством совпадений, функция MAX - это все, что нам нужно.

Примечание: область извлечения необходимо настроить вручную, чтобы обрабатывать столько данных, сколько необходимо (например, 5 строк, 10 строк, 20 строк и т. Д.). В этом примере он ограничен 5 строками только для того, чтобы рабочий лист оставался компактным.

Я изучил эту технику в книге Майка Гирвина Control + Shift + Enter.