☰

Как выполнить частичное сравнение строк в Excel?

tag-icon • tag-icon microsoft-excel
Как выполнить частичное сравнение строк в Excel?

У меня есть два столбца в Excel. Оба столбца содержат список строк, которые заканчиваются случайным символом. Формат префикса — две строки, соединенные подчеркиванием, а суффикс также добавляется подчеркиванием.

то есть

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

Как мне сравнить, есть ли префикс в столбце B в столбце A?

Редактировать: Я знаю, что могу сделать это, разделив строку на три секции, а затем объединив первые две секции в один столбец, а затем проверив, находится ли столбец в другом с помощью VLOOKUP. Но я искал функцию oneliner.

решение1

Я предполагаю, что в ячейках A1 и B1 у вас есть:

введите описание изображения здесь

А в столбце C есть TRUE(VERDADEIRO) или FALSE(FALSO), которые возвращают, равен ли префикс или нет. (извините за картинку на португальском языке)

Формула, к которой я пришел, такова:

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

Объяснение:

=EXACT(str1,str2)

Он сравнивает, равны ли строки str1 и str2 или нет. Мы должны извлечь префикс из столбцов A и B. Для этого мы используем:

=LEFT(text, [num_chars])

Он извлекает [num_chars]символы из строки text. Чтобы узнать, сколько символов нам нужно извлечь, мы заменяем второе подчеркивание _на фиктивный символ \:

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

Хитрость здесь в необязательном аргументе [instance]. Мы устанавливаем его на 2, чтобы заменить второе вхождение символа подчеркивания.

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

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

Пошагово, если разбить формулу, то получим: введите описание изображения здесь

решение2

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

Я не смог уместить это в одну строку (потому что это должно искать везде в столбце A). Но для этого требуется только один дополнительный столбец.

Во-первых, формула для извлечения префикса из любой строки:

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

  • Delimiterэто символ "_"
  • Внутренний FIND()вызов находит местоположение первого подчеркивания. Затем он использует его как отправную точку для FINDместоположения второго подчеркивания.
  • Функция LEFT()возвращает ряд символов, начиная с левой части строки. Поэтому мы вычитаем 1из местоположения второго подчеркивания и получаем префикс строки.

Итак, вы используете это, чтобы получить список всех префиксов столбца A. Затем вы используете формулу массива, чтобы проверить, присутствует ли каждый префикс в столбце B в списке префиксов столбца A.

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

  • Prefix_A— это полный список префиксов столбца А.
  • Это та же формула для извлечения префиксов, которая была применена к столбцу A.
  • Это должна быть формула массива, поскольку оператор внутри функции OR()возвращает массив TRUEзначенийFALSE

Вот скриншот таблицы, которую я создал для ответа на этот вопрос: Проверьте, присутствует ли префикс столбца B в столбце A.

Для создания этих примеров формул я использовал FormulaChop (полное раскрытие информации: я написал FormulaChop).Здесьснимок экрана вывода FormulaChop для первой формулы.Здесьэто ссылка на электронную таблицу, которую я создал для ответа на этот вопрос.

Связанный контент