Ищем формулу для поиска определенного шаблона в Excel

613
ajit maurya

Я ищу формулу для поиска «слова» с определенным рисунком буквы, как INNSA1или INMAA4или INPTPB, где слово начинается с INи заканчивается 4, 1, 6или B. В словах всегда ровно шесть символов, а буквы могут быть в верхнем или нижнем регистре.

Каждая коллекция ячеек Excel содержит предложение, в котором будет найдено одно или несколько таких слов (всегда хотя бы одно). Например:

  • Мое пользовательское местоположение INNSA1
  • Я работаю в INMAA4
  • Я хочу пойти в INDEL6
  • ИННСАБ РАСПОЛОЖЕН

Слово (или слова, если их больше одного) может находиться в любом положении в предложении. Целевые слова всегда будут отдельными словами, а не частью более крупного слова.

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

-1
Тяжелое голосование за крики во всех столицах. Настоятельно рекомендуем вам отредактировать. Michael Harvey 6 лет назад 2
У меня нет четкой картины вашего требования. Вы ищете наличие (true / false) «слова» (т. Е. Строки символов, содержащей только буквенно-цифровые символы - буквы / цифры, но без пробелов или других видов символов), соответствующих шаблону (начинается с `IN`, и оканчивается на `4, 1, 6` или` b`), которые могут быть расположены в любом месте "предложения", но отделены от него пробелом или пунктуацией, которые содержатся в одной ячейке? Это всегда отдельное «слово» или это может быть строка символов, встроенная в более длинное «слово»? (Продолжение) fixer1234 6 лет назад 0
Это всегда точно 6 символов? Письма всегда в верхнем регистре? Как выглядят «предложения»? Может ли «слово» появляться более одного раза, в том числе как часть более крупного слова? Это будет легче понять, если вы включите примеры предложений, которые представляют диапазон условий. fixer1234 6 лет назад 0
Кроме того, как истолковал студент Гэри, является ли слово единственным в ячейке или оно находится где-то внутри другого текста в ячейке (что является совершенно другими требованиями)? fixer1234 6 лет назад 0
Я хочу найти конкретный шаблон в предложении в ячейке. например, «Мое пользовательское местоположение - INNSA1» ИЛИ «Я работаю на INMAA4» или «Я хочу перейти на INDEL6» ajit maurya 6 лет назад 0
В предложении выше я хочу найти / найти INNSA1 ИЛИ INMAA4 ИЛИ INDEL6 и вернуть это слово в качестве значения ajit maurya 6 лет назад 0
Почти готово. Вопросы: 1. Всегда ли в словах ровно 6 символов? 2. Буквы слова всегда заглавные? 3. Будет ли каждое предложение всегда содержать уточняющее слово? 4. Может ли предложение содержать более одного уточняющего слова? 5. Всегда ли целевое слово является отдельным словом, не содержащимся в большем слове? 6. Может ли целевое слово появляться в любой позиции слова в предложении или оно всегда будет последним словом в предложении? 7. Может ли целевое слово появляться более чем в одном предложении или целевое слово в каждом предложении уникально? fixer1234 6 лет назад 0
1. Да, это всегда будет 6 символов и начинаются с «IN» и заканчиваются на «1» ИЛИ «4» ИЛИ «6» ИЛИ «B» 2. Это может быть верхний или нижний регистр 3. Да 4. Иногда может быть 5. Да, это отдельное слово 6. Любое, где есть предложение 7. Уникальный ajit maurya 6 лет назад 0
EG -Мое местоположение INNSA1 Мое местоположение INNSA4 Мое местоположение INNSA5 Мое местоположение INNSA6 Мое местоположение INNSA7 Мое местоположение INNSA8 INNSAB IS МЕСТОПОЛОЖЕНИЕ СОВЕТА ajit maurya 6 лет назад 0

2 ответа на вопрос

0
Gary's Student

Это всего лишь пример, который вы можете адаптировать к вашей схеме.

С данными от A2 до A26, в B2 введите:

=IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"") 

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

enter image description here

Как видите, каждый элемент «хороших» данных помечен простым последовательным индексом.

0
fixer1234

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

Используя функции, будет сложно найти в предложении потенциально более одной цели. Более простой подход - разбить предложение на слова, а затем проверить слова. Простой способ разбора предложений - встроенный мастер преобразования текста в столбцы.

Предполагая, что предложения находятся в столбце A, оставьте следующие несколько столбцов пустыми для ваших результатов. Таким образом, ваши результаты всегда будут в известном месте, видимом рядом с вашими данными. Я предполагаю, что максимум два, поэтому столбцы B и C будут содержать значения результата.

Изобразите максимальное количество слов, которое может быть в предложении, и оставьте столько последующих столбцов для вспомогательных столбцов. Они будут использованы для проверки каждого слова. Допустим, вы допускаете пять слов. Столбцы D: H будут содержать формулы, указывающие на столбцы I: M соответственно.

Допустим, строка 2 - это ваша первая строка данных, поэтому первая формула будет в D2, указывая на I2. Формула будет похожа на то, что предложил студент Гэри:

=IF(AND(LEFT(I2,2)="IN",OR(RIGHT(I2,1)="B",RIGHT(I2,1)="1",RIGHT(I2,1)="4",RIGHT(I2,1)="6")),1,0) 

Альтернативная формула будет:

=IFERROR((LEFT(I2,2)="IN")*(SEARCH(RIGHT(I2,1)," 416b")>1),0) 

Это просто дает вам пару идей о том, как определить, соответствует ли целевое слово шаблону. Вторая формула использует SEARCH (потому что она не чувствительна к регистру), чтобы увидеть, есть ли последний символ в вашем списке. Тестирование первых двух символов INи, наконец, последнего символа, каждый из которых приводит к True или False, что Excel обрабатывает как 1и 0, соответственно. Так что, если оба теста верны, результат - 1иначе 0.

В выражении ПОИСК я начал с пробела и теста на результат> 1. Причина, по которой я это сделал, заключается в том, что я использовал MS Office Online для его тестирования, и он считает, что самый правый символ пустой ячейки находится в позиции 1 тестовой строки.

Если SEARCH не находит совпадения, он возвращает ошибку, поэтому перенос формулы в IFERROR гарантирует, что любое слово, не соответствующее совпадению, приведет к 0.

Таким образом, либо формула возвращает 1, подходит ли слово вашему шаблону, либо 0нет (или если его целевая ячейка пуста).

Введите любую формулу в D2 и перетащите ее или скопируйте в H2, а затем в последнюю нужную строку.

Чтобы заполнить целевые ячейки, выберите все данные в столбце A, скопируйте и вставьте их в столбец I (текст в столбцы перезаписывает данные и начинается в столбце данных). Выберите данные в столбце I. На Dataвкладке в Data Toolsгруппе щелкните Text to Columns. Вы получите волшебника, который говорит само за себя.

Скажите, что данные разделены, и выберите spaceв качестве разделителя. Это даст вам предварительный просмотр того, как он думает, что вы хотите, чтобы он проанализировал слова. Когда вы закончите работу мастера, он поместит каждое слово в последовательную ячейку в строке.

Ваши формулы в D: H должны показывать хотя бы одну 1в каждой строке. Это ваши слова, и их столбец находится в той же относительной позиции, что и анализируемое слово.

Чтобы получить значение первого слова в столбце B, используйте INDEX и MATCH, чтобы найти первое 1в строке в D: H и получить соответствующее значение из той же строки в I: M. Например, B2 будет:

=INDEX(I2:M2,MATCH(1,D2:H2,0)) 

Это извлекает значение из ячейки в I2: M2, соответствующее точному совпадению, 1найденному в D2: H2.

Чтобы получить последующие значения для столбца C (и далее, если их больше двух), существует ряд решений для поиска N-го соответствия 1. Вот ссылка на один метод.

Похожие вопросы