Найти не чувствительную к регистру текстовую строку в диапазоне ячеек

Question

У меня есть небольшая проблема, которую нужно решить в Excel, и я не совсем уверен, как это сделать. Я провел несколько поисков в Интернете и не нашел формул, которые могут оказаться полезными. Вот ситуация (немного упрощенная для целей этого вопроса):

У меня есть данные в столбцах AE. Мне нужно сопоставить данные в ячейках A и B с данными в CE и вернуть TRUE или FALSE в столбец F.

Верните TRUE, если:
- Строка в A находится внутри любой строки в CE.
ИЛИ ЖЕ
- Строка в B находится внутри любой строки в CE.

В противном случае верните FALSE.

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

Я вполне уверен, что мне нужно будет использовать IF или на самом внешнем слое формулы, за которым, вероятно, следует OR. Затем, что касается аргументов OR, я ожидаю, что IFERROR будет задействован. Но что я в растерянности, так это функция, которую я мог бы наиболее эффективно использовать для поиска текстовых строк.

Я думаю, что VLOOKUP очень ограничен в этом отношении. Может быть выполнимо выполнить сравнение всей строки с целыми строками, но я вполне уверен, что она не даст точных результатов для частичных совпадений строк. НАЙТИ и ПОИСК ограничены только поиском по одной цели, а также чувствительны к регистру. Я полагаю, что я мог бы использовать UPPER или LOWER, чтобы вызвать нечувствительность к регистру в поиске, но мне все еще нужно что-то, что может выполнять точное частичное сопоставление и искать в указанном диапазоне ячеек.

Есть ли какая-либо функция или комбинация функций, которая могла бы работать здесь?

В идеале я хочу сделать это с простой формулой Excel. Я совсем не знаком с VBScript или подобными инструментами, и у меня нет времени изучать его для этого проекта.

Answer 1

Это мое грязное решение, я не уверен, что оно лучшее.

Вы должны попытаться добавить временный столбец, затем вы можете скрыть его позже. И попробуйте преобразовать все данные в строчные или прописные буквы, а затем следуйте логике, которую вы уже сделали.

Именно так:

A2: ABcdeF
B2: =lower(A2) --> abcdef
C2: aBc
D2: =lower(C2) --> abc
E2: =find(D2, B2) --> found
F2: =find('poi', B2) --> not found

Функция VLOOKUP имеет необязательный параметр: range_lookup

Логическое значение (только TRUE или FALSE), которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение с lookup_value

http://spreadsheets.about.com/od/excelfunctions/ss/vlookup_2.htm

Answer 2

В идеале я хочу сделать это с простой формулой Excel. Я совсем не знаком с VBScript или подобными инструментами, и у меня нет времени изучать его для этого проекта.

Диапазон VBA .Метод поиска существует только для таких задач. Было бы стыдно не использовать его. :)

Синтаксис

ДиапазонНайти (Что, После, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Нам нужно только 3 параметра для вашей задачи. Оставь остальные

• Диапазон: диапазон, где искать
• Что: данные для поиска
• LookAt: может быть один xlWhole (полное совпадение) или xlPart (частичное совпадение)
• MatchCase: True, чтобы сделать поиск чувствительным к регистру. Неверно для поиска без учета регистра

Короткий пример будет выглядеть

 Set result = Columns(C:E).find(what:=[A1], LookAt:=xlPart, MatchCase:=false)

---

Этот код учитывает ваши дополнительные условия, такие как

• Проверьте данные из столбца A и столбца B. Если один из них совпадает, верните true
• Вернуть истину даже при частичных совпадениях
• Игнорировать прописные или строчные буквы
• Вернуть true или false в столбце F

Sub SearchMatches()    
For i = 2 To 5    
    On Error Resume Next
    Set checkColA = Columns("C:E").Find(Cells(i, 1), , , xlPart, , , False)
    Set checkColB = Columns("C:E").Find(Cells(i, 2), , , xlPart, , , False)
    On Error GoTo 0        
    If checkColA Is Nothing And checkColB Is Nothing Then
        Cells(i, 6) = False
    Else
        Cells(i, 6) = True
    End If        
Next i    
End Sub

Answer 3

Попробуйте объединить текст этих ячеек, это мой трюк:

A1: a
A2: b
A3: c
B1: ">" & A1 & "<" --> >a<
B2: = B1 & ">" & A2 & "<" --> >a<>b<
B3: = B2 & "<" & A3 & "<" --> >a<>b<>c<

... вы можете автоматизировать с помощью Excel, просто поверьте, это займет не более 1 минуты :-)

поэтому последняя ячейка должна объединять весь текст из всех ячеек

Затем просто выполните обычный поиск, но убедитесь, что вы включили «>» и «<» в свои критерии, что-то вроде

=find(">" & "abc" & "<", $B$3)

и опять это мой подвох!