Блог

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

Например,

• учитывая следующую таблицу B2:D5 и текст im , я ожидаю, что формула вернет ячейку B2, значение которой равно Jim .
• учитывая таблицу B2:D5 и текст omas , я ожидаю, что формула вернет ячейку C5, значение которой равно Thomas .

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

Идеально,

• формула возвращает ячейку, а не значение ячейки. под ячейкой я имею в виду, что мы могли бы даже объединить ее с другой ячейкой : , например, для создания диапазона ...:F10 .
• Я бы предпочел решение без функции ЛЯМБДА, которая все еще находится в предварительном просмотре.

Кто-нибудь знает, как этого добиться?

Создайте матрицу совпадений с ПОИСКОМ по условию и входной таблице. Найдите первый столбец с совпадением и первую строку в этом столбце с совпадением, затем передайте эти числа в ИНДЕКС. Использование LET для облегчения чтения:

 =LET(  t, R1C1:R5C3,  cond, "*" amp; RC5 amp; "*",  matches, IFERROR(SEARCH(cond, t), 9999),  col, MIN(matches*COLUMN(t)),  rw, MIN(INDEX(matches*ROW(t), FALSE, col)),  INDEX(t, rw, col) )  

Поскольку ссылка на ячейку автоматически вычисляется по значению ячейки, я ввел вторую формулу в col 7, чтобы просто сбросить строку ссылки.

Определения: t — входная таблица. совпадения-это массив совпадений. Это будет либо 1, либо ошибка, поэтому я включаю IFERROR, и, поскольку я собираюсь получить минимальное значение позже, я использую сколь угодно большое число, чтобы мой массив был либо 1, либо 9999. col использует MIN для поиска наименьшего совпадающего столбца, затем я использую ИНДЕКС, чтобы вернуть этот столбец из входной таблицы (t) и получить минимальную строку. Объединение этих чисел дает мне строку, которую я могу использовать КОСВЕННО.

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

Две возможности:

а) Если вы ищете решение, которое показывает все совпадения, вы можете использовать эту красивую формулу 🙂

 =IFERROR(INDEX($A$1:$D$5,  AGGREGATE(15,6,ROW($A$1:$A$5)/(SEARCH($G$1,$A$1:$D$5)gt;0),ROW(A1)),  AGGREGATE(15,6,COLUMN($A$1:$D$1)/((SEQUENCE(ROWS($A$1:$A$5),COLUMNS($A$1:$D$1),1,1)/AGGREGATE(15,6,(SEQUENCE(ROWS($A$1:$A$5),COLUMNS($A$1:$D$1),1,1)*(SEARCH($G$1,$A$1:$D$5)gt;0)),ROW(A1)))=1),1))  ,"-")  

Вам «просто» нужно изменить диапазоны в соответствии с вашими данными. 🙂

б) Если вас устраивает только первый матч, то вы можете использовать

 =IFERROR(INDEX($A$1:$D$5,  AGGREGATE(15,6,ROW($A$1:$A$5)/(SEARCH($G$1,$A$1:$D$5)gt;0),1),  AGGREGATE(15,6,COLUMN($A$1:$D$5)/(SEARCH($G$1,$A$1:$D$5)gt;0),1)),  "-")  

что не приведет к сбою, если существует несколько возможных совпадений. Если вы хотите знать, сколько существует совпадений, то просто используйте

 =COUNTIF(A1:D5,"*"amp;G1amp;"*")  

=INDEX(A:D,SUMPRODUCT(ISNUMBER(SEARCH("?"amp;"im",B2:D5))*(ROW(B2:D5))),SUMPRODUCT(ISNUMBER(SEARCH("?"amp;"im",B2:D5))*(COLUMN(B2:D5))))

Будьте осторожны при использовании этого, так как несколько совпадений вернут ошибку или неправильный результат!

Вы также можете отказаться от использования "?"amp; перед обоими поисками, но это будет включать совпадения, которые начинаются со значения поиска.