Как использовать функцию AND и функцию MATCH вместе, чтобы получить что-то, что не "Нет" и не было возвращено ранее

Question

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

    E 
5   No  
6   No  
7   IC3  
8   No   
9   J1   
10  J2
11  J3
12  No

Через минуту я могу получить первое значение, не равное "Нет". Я пытаюсь использовать функцию MATCH для возврата значений <> "Нет" И <> [значения в предыдущих ячейках].

У меня есть следующая формула в ячейке L12

{=INDEX($E$5:$E$12,AND(MATCH(TRUE,$E$5:$E$12<>"No"),MATCH(TRUE,$E$5:$E$12<>$L$5:L11)),0)}

У кого-нибудь есть идеи?

Answer 1

С данными от A1 до A14, в B1 введите:

=IF(A1="No","",A1)

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

=IFERROR(INDEX($B$1:$B$14,SMALL((IF(LEN($B$1:$B$14),ROW(INDIRECT("1:"&ROWS($B$1:$B$14))))),ROW(A1)),1),"")

и скопировать вниз. Например:

Answer 2

Поместите эту формулу, чтобы определить ожидаемое количество возвратов, например, в L1:

=COUNTIF($E$5:$E$12,"<>No")

Тогда эта формула в вашей первой ячейке выбора:

=IF(ROWS($1:1)>$L$1,"",INDEX($E:$E,AGGREGATE(15,6,ROW(E$5:E$12)/(E$5:E$12<>"No"),ROWS($1:1))))

(Вам понадобится небольшая модификация, если вы используете версию Excel до 2010 года.)

Скопируйте эту формулу вниз (но не в L1), пока не начнете получать пробелы для результатов.

С уважением

Answer 3

Вот тот, который не требует вспомогательного столбца. Предполагая, что ваши данные начинаются с A1:

=IFERROR(INDEX(A$1:A$14,SMALL(IF(A$1:A$14<>"No",ROW(A$1:A$14)-MIN(ROW(A$1:A$14))+1,""),ROW())),"")

Подтвердите как формула массива с помощью Ctrl + Shift + Enter и автозаполнения вниз. То, как это написано, должно начинаться с первой строки, но вы можете настроить последний ROW(), чтобы начать где-то еще, если вам нужно.