-1

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

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

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

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

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

2 ответа2

0

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

С данными от 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),"")

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

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

0

Есть много способов решить каждую часть проблемы, но комбинация требований указывает на один простой и понятный подход или решение 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 . Вот ссылка на один метод.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .