☰

Excel INDEX MATCH Проверка нескольких столбцов

tag-icon • tag-icon microsoft-excel vlookup array
Excel INDEX MATCH Проверка нескольких столбцов

Проблема, которую я по сути пытаюсь решить, — это функция VLOOKUP, которая проверяет столбцы A:E на наличие значения и возвращает значение, хранящееся в столбце F, если оно найдено в любом из них.

Поскольку VLOOKUP не справляется с этой задачей, я изучил синтаксис INDEX-MATCH, но мне сложно понять, как это сделать для массива значений, а не для одного столбца. Я построил пример набора данных ниже, чтобы попытаться объяснить это:

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

Если проверяемая ячейка содержит 1,2,3,4 или 5, результатом формулы должно быть Яблоко. Если 12 или 13, она должна вернуть Банан, и, наконец, если она содержит 14, она должна вернуть Морковь.

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

Итак, чтобы продемонстрировать, есть еще одна таблица в другом месте (как ниже), в которой есть эти значения. Я пытаюсь заставить систему определить, какую строку, и, следовательно, какие из значений "Яблоко, Банан, Морковь" связать с каждым столбцом. Таблица будет выглядеть так, как показано ниже

ПРИВЕТ------------

1------(Яблоко)----

2------(Яблоко)----

12-----(Банан)-

и т. д.-----------------

Значения в скобках указывают, где формула рассчитывает эти значения.

решение1

У вас есть несколько разных случаев. Давайте рассмотрим один случай:

Где-то в колонкахАчерезЭесть одна-единственная ячейка, содержащая 13, вернуть содержимое ячейки в столбцеФв том же ряду.

Мы будем использовать «вспомогательный» столбец.Г1входить:

=COUNTIF(A1:E1,13)

и копируем вниз. Это позволяет нам идентифицировать строку:

введите описание изображения здесь
Теперь мы можем использоватьСОВПАДЕНИЕ()/ИНДЕКС():

Выберите ячейку и введите:

=INDEX(F:F,MATCH(1,G:G,0))

введите описание изображения здесь

Если «правила» изменятся и в строке может оказаться больше одного числа 13 или несколько строк, содержащих число 13, мы изменим вспомогательный столбец.

ПРАВКА №1:

На основе вашего обновления, первым шагом будет извлечение жестко закодированного13из формул в столбце «помощник» и поместить его в отдельную ячейку,(сказатьН1). Затем вы можете запускать различные случаи, просто изменяя одну ячейку.

Если у вас большое количество случаев в таблице, вы можете создать макрос для настройки каждого случая(обновлятьН1)и запишите результаты.

решение2

Основываясь на собственных исследованиях и обсуждениях с @Gary'sStudent, я применил решение, создав формулу ПОИСКПОЗ для каждого из возможных столбцов, в которых может содержаться значение, а также оператор «ЕСЛИОШИБКА», перехватывающий пустые значения.

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

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

Затем я создал индекс, который аккумулирует их в одно значение, которое должно соответствовать рассматриваемой СТРОКЕ. Опять же, есть проверка (первая СУММА), чтобы ввести это как пустое значение, если значение не найдено в таблице.

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

ИНДЕКС-МАТЧ МАССИВ Наконец, я ввел несколько формул условного форматирования, чтобы гарантировать, что пользователь идентифицирует и заменяет/удаляет любые дублирующиеся данные.

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

Это всего лишь сигнал пользователю о необходимости удалить дублирующиеся данные.

введите описание изображения здесь

решение3

Для одной формулы в H1:

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

Это формула массива, поэтому нам нужно ограничить ссылки размером набора данных. Все делают INDEX(E:E,MATCH("ZZZ",F:F))это. Это возвращает последнюю строку в столбце F, которая содержит текст. Затем она устанавливается как последняя строка для итерации.

Метод @Gary'sStudent избегает формул массива и может быть тем методом, который нужен. По мере увеличения набора данных и количества формул увеличивается и время вычислений. Даже в какой-то момент до сбоя Excel. Обычно это занимает несколько тысяч, но я хочу сделать предупреждение.

введите описание изображения здесь

РЕДАКТИРОВАТЬ

Чтобы избежать использования формул массива и при этом иметь одну формулу:

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

Это основано на ответе автора, просто объединив этот метод в одну формулу.

Эта формула проигнорирует повторяющиеся записи и вернет первую строку, в которой найдено число.

А поскольку это не массив, ссылки на полные столбцы не оказывают отрицательного влияния на время вычислений.

![введите описание изображения здесь

решение4

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

Если вышеперечисленное есть на Листе 1, добавьте Лист 2. На нем разместите четыре столбца: Строка, Столбец, ID, Имя

Формула в Rowдолжна быть (в псевдокоде «Last» означает «для строки выше на листе2»)

=IF(Column = 1, Last row + 1 , Last row)

Формула в Column:

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

Формула в IDи Name:

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

Затем вы заполняете это поле (в основном до тех пор, пока rowколичество строк не станет > количества строк в исходной таблице).

Наконец, вы используете новую таблицу с обычным ВПР или индексом/соответствием.

ЗА: Гораздо более простые формулы, более легкие в использовании и понимании.

МИНУСЫ: Нужна дополнительная таблица, необходимо поддерживать длину таблицы. С точки зрения производительности есть риск, так как это практически требует одного потока для всей «строки» значений.

Кроме того, если пара строк с ошибками в порядке, код может быть несколько проще и, возможно, более производительным; тогда мы можем предположить, что количество столбцов всегда равно 5, что даст и строку, и столбец.

Связанный контент