Соответствие индексу Excel проверка нескольких столбцов

проблема по сути, я пытаюсь решить-это ВПР, что это проверка Столбцах А:Е значение, и вернуть значение в столбце F должен быть найден в любом из этих.

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

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, он должен вернуть морковь.

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

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

H------Я------------

1------(Apple) - - - -

2------(Apple) - - - -

12-----(Banana) -

etc.-----------------

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

6
задан Richard Allan
08.11.2022 16:43 Количество просмотров материала 3237
Распечатать страницу

4 ответа

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

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

мы будем использовать столбец "помощник". В G1 enter:

=COUNTIF(A1:E1,13)

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

enter image description here

теперь мы можем использовать MATCH () / INDEX ():

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

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

enter image description here

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

EDIT#1:

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



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

4
отвечен Gary's Student 2022-11-10 00:31

основываясь на моих собственных исследованиях и обсуждениях с @Gary'sstudent, решение, которое я использовал, состояло в том, чтобы создать формулу соответствия для каждого из возможных столбцов, в которых может содержаться значение, а также пустой оператор "IFERROR".

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

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

затем я создал индекс, который накапливает их в одно значение, которое должно соответствовать строке, о которой идет речь. Опять же, есть проверка (первая SUM), чтобы ввести это значение как пустое значение, если оно не найдено в таблице.

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

INDEX-MATCH ARRAY Наконец, я ввел несколько формул условного форматирования, чтобы убедиться, что пользователь идентифицирует и заменяет/удаляет любые дубликаты данных.

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

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

Это просто подсказка для пользователя, чтобы удалить дубликаты данных.

enter image description here

2
отвечен Richard Allan 2022-11-10 02:48

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

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

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

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

enter image description here


EDIT

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

=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))),"")

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

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

и потому, что это не массив полные ссылки столбцов не наносят ущерба времени calc.

![enter image description here

1
отвечен Scott Craner 2022-11-10 05:05

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

если выше указано в Листе1, добавьте Лист2. На этом месте четыре столбца; строка, столбец, идентификатор, имя

Формула Row должен быть (в psuedo код, "последний" означает "для строки выше в лист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>количество строк в исходной таблице).

наконец, вы используете новую таблицу с обычным vlookup или index / match.

PRO: гораздо проще формулы, проще в использовании и понимании.

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

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

0
отвечен NiklasJ 2022-11-10 07:22

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Вверх