Я ищу формулу для поиска «слова» с определенным рисунком буквы, как INNSA1или INMAA4или INPTPB, где слово начинается с INи заканчивается 4, 1, 6или B. В словах всегда ровно шесть символов, а буквы могут быть в верхнем или нижнем регистре.
Каждая коллекция ячеек Excel содержит предложение, в котором будет найдено одно или несколько таких слов (всегда хотя бы одно). Например:
Слово (или слова, если их больше одного) может находиться в любом положении в предложении. Целевые слова всегда будут отдельными словами, а не частью более крупного слова.
Для каждого предложения (то есть ячейки) я хочу найти наличие любого слова, соответствующего описанному шаблону букв, и вернуть это слово в качестве значения.
Это всего лишь пример, который вы можете адаптировать к вашей схеме.
С данными от 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),"") и скопировать вниз:
Как видите, каждый элемент «хороших» данных помечен простым последовательным индексом.
Есть много способов решить каждую часть проблемы, но комбинация требований указывает на один простой и понятный подход или решение 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. Вот ссылка на один метод.