Найдите ячейки с простыми критериями

Question

У меня есть данные как:

Мне нужно найти (получить номера строк) для клеток, начинающихся с буквы А.

Мой текущий подход заключается в использовании столбца "помощник". В B1 я ввожу:

=IF(LEFT(A1)="A",1,"")

и в B2 я ввожу:

=IF(LEFT(A2)="A",MAX($B$1:B1)+1,"")

и скопировать вниз. Это определяет интересующие строки. Наконец, в C1 я использую:

=IFERROR(MATCH(ROWS($1:1),$B$1:$B$23,0),"")

и скопировать вниз.

Моя цель - убрать столбец "помощник".

Я вижу только два варианта:

• использовать автофильтр
• использовать VBA UDF

Подход автофильтрации плох, потому что фильтр должен был бы повторно запускаться всякий раз, когда данные обновляются. UDF подходит плохо, потому что лист должен работать в среде без VBA.

Есть ли какая-либо формула или формула массива, которая может получить номера строк, или я застрял в столбце "помощник" ??

Answer 1

Попробуйте это ввести в виде формулы массива с помощью Ctrl+Shift+Enter:

{=SMALL(IF(LEFT($A$1:$A$23)="A",ROW($A$1:$A$23),9E+99),ROW())}

Я предполагаю, что ваши критерии сложнее, чем это в вашем реальном приложении, хотя. Вы можете заменить LEFT($A$1:$A$23)="A" на любые критерии, которые вам нравятся, и все равно будет работать.

Обратите внимание, что это вернет 9E+99 если вы скопируете его для большего количества строк, чем те, которые начинаются с "A". Вы можете настроить его так, чтобы он возвращался пустым:

{=IFERROR(SMALL(IF(LEFT($A$1:$A$14)="A",ROW($A$1:$A$14),""),ROW()),"")}

Answer 2

Попробуй это:
Поместите 0 в С1.
От C2: =match(indirect("a"&c1+1&":a<last row>",true),"a*",0)+C1
Примечания: совпадение не чувствительно к регистру. Вы должны управлять ошибками после того, как спросите совпадения найдены.