Я пытаюсь выяснить формулу, которая помогает мне искать строку для подстановочного текста (например, w *) и возвращает все совпадающие значения (в одной или нескольких ячейках).
Что у меня выглядит примерно так:
Что я хотел бы сделать формулу это:
Или это:
Пока что я нашел решения для поиска по столбцам (и часто без подстановочных знаков), но я не могу заставить его работать для поиска строк. Кто-нибудь может помочь с этим?
Я могу дать вам подходящее решение. Я уверен, что есть лучшие решения, но я обычно использую LO Calc, и он ведет себя совсем по-другому, и единственным Excel, к которому у меня был готов доступ, был Excel онлайн, и он не работает с формулами массивов.
Это решение основано на вашем комментарии о том, что у вас есть десятки или сотни столбцов значений и максимум шесть значений "w". Он возвращает шесть значений "w" для каждой строки, некоторые или все из которых могут быть пустыми, если совпадений меньше. Он использует вспомогательные столбцы для промежуточных значений, которые вы можете скрыть или прикрепить в удобном месте. Вот пример с шестью столбцами значений, и я объясню, как масштабировать его под ваши данные.
Ваши образцы данных, а также шестой столбец, который я добавил, находятся в столбцах B:G (плюс люди в столбце A). Для ваших собственных данных используйте соответствующий диапазон столбцов, просто соблюдайте знаки доллара для абсолютной адресации. Мои вспомогательные столбцы в I:N, а результаты в P:U.
В вспомогательных столбцах используются две формулы: одна для первого столбца и одна для последующих столбцов. Вспомогательные столбцы определяют местоположение совпадений. Аналогичным образом, есть две формулы для столбцов результатов, первый и последующий. Введите эти четыре формулы и скопируйте при необходимости.
1-я вспомогательная колонна
Формула в I2 (при необходимости скопируйте):
=IFERROR(MATCH("w*",$B2:$G2,0),0)
Он находит первое совпадение с подстановочным знаком или возвращает 0 если в строке нет ни одного.
Последующие вспомогательные столбцы (с 2 по 6)
Формула в I3 (копия справа и вниз):
=IF(SUM($I2:I2)=COLUMN($G2)-COLUMN($A2),0,IFERROR(MATCH("w*",OFFSET($B2:$G2,0,SUM($I2:I2)),0),0))
Мясо формулы работает так же, как первый столбец, но вместо просмотра всей строки он смотрит справа от местоположения последнего совпадения. Первый тест IF проверяет, является ли последнее совпадение последним столбцом значений, поэтому он не пытается сопоставить несуществующие столбцы. Для ваших фактических данных замените COLUMN($ G2) на последний столбец значений и COLUMN($ A2) на столбец перед первым столбцом значений.
Первый столбец результатов
Формула в P2 (при необходимости скопируйте):
=IFERROR(INDEX($B2:$G2,1,$I2),"")
При этом извлекается значение из столбца, идентифицированного первым совпадением, или отображается пустое значение, если его не было.
Последующие столбцы результатов (с 2 по 6)
Формула в Q2 (копия справа и вниз):
=IF(J2=0,"",IFERROR(INDEX($B2:$G2,1,SUM($I2:J2)),""))
Опять же, это работает так же, как первый, но суммирует предыдущие смещения соответствия, чтобы найти местоположение. Он также проверяет, не было ли совпадений, и в этом случае отображается пустое поле.
@ Шерли, это улучшенный ответ, который ищет буквы W's в строках вместо столбцов, как я показал ранее.
Результат:
Запишите эту формулу массива (CSE) в H141 , завершите с помощью Ctrl+Shift+Enter , Fill Down, затем Right.
{=IFERROR(INDEX($B141:$G141, SMALL(IF(LEFT($B141:$G141,1)="w", MATCH(COLUMN($B141:$G141), COLUMN($B141:$G141)), ""),COLUMNS($A$1:A1))),"")}
При необходимости измените ссылки на ячейки в формуле.
@Sherly ,, Я хотел бы предложить формулу массива (CSE), которая извлечет все значения, начинающиеся с W, из указанного диапазона.
Обратите внимание, поскольку Wildcard не работает с Numbers, а данные в диапазоне имеют W а затем Numbers, так что я использовал трюк для решения этой проблемы:
(IF(LEFT(B$141:B$150,1)=B$153
где LEFT вытягивает 1st Character который совпадает с B153 имеет w, и это поддерживает только формулу для вытягивания всех Ws за которыми следуют числа.
Как это устроено:
B153 to G153 w (которая является вспомогательной строкой ).Запишите эту формулу в ячейку B154, завершите с помощью Ctrl+Shift+Enter, Fill Down, затем Right.
{=IFERROR(INDEX(B$141:B$150, SMALL(IF(LEFT(B$141:B$150,1)=B$153, MATCH(ROW(B$141:B$150), ROW(B$141:B$150)), ""),ROWS($A$1:A1))),"")}
Отредактировано:
Для поиска и извлечения W в Row вам нужно написать эту формулу в B160 (заполните справа, затем вниз):
=IF(LEFT(B141:G141,1)="w",LEFT(B141:G141,1)&RIGHT(B141:G141,LEN(B141:G141)-2+1),"")
