Ячейка Excel COUNTIF содержит заданный текст (частичное совпадение)

У меня есть условное форматирование с ячейками вроде

  = COUNTIF (A3: H2663; R5)  

If значение, введенное в R5 , найдено в другом месте, тогда поле станет красным.

Однако иногда это не точное совпадение, и тогда он его не распознает. Это может быть из-за лишней цифры в конце введенного числа.

Итак, мой вопрос: могу ли я изменить формулу, чтобы обеспечить соответствие, если ячейки из A3: H2663 просто содержат значение в R5 , и нет точного совпадения?


С помощью функции COUNTIF () вы можете использовать подстановочные знаки в своих критериях.

Если вы хотите найти любое значение ячейки, которое имеет значение поиска/критерия в самом начале:

= COUNTIF (A3: H2663, R5 & "*")

Если вы хотите найти любое значение ячейки, в котором есть значение поиска/критерия:

= COUNTIF (A3: H2663, "*" & R5 & "*")

Подстановочный знак * представляет ноль или более символов .



Функция ЕСЛИ в Excel с ЧАСТИЧНЫМ сопоставлением текста (ЕСЛИ с подстановочными знаками)

Как мы можем найти приблизительное совпадение, используя подстановочные знаки с функцией Excel IF ?

 = IF (YouTu * THEN…) 

Можете ли вы использовать подстановочные знаки в части логического тестирования функции IF ?

Кажется, это было бы просто «Да», но, к сожалению, это не так.

Подстановочные знаки хорошо работают с такими функциями, как СУММЕСЛИМН , СРЕДНИЙ g> и COUNTIFS; , к сожалению, они не работают с функциями IF .

Почему они не работают с функциями IF ? Давайте исследуем причину, почему и посмотрим, сможем ли мы найти обходной путь.

ПРИМЕЧАНИЕ. Если вам интересно узнать об использовании подстановочных знаков с СУММЕСЛИМН , просмотрите ссылку ниже на сообщение «EXCEL SUM на основе частичного совпадения текста».

 Сумма значений на основе частичного совпадения текста 

В приведенном ниже наборе данных мы хотим записать формулу в столбце B , который будет искать текст в столбце A . Наша формула будет искать в тексте столбца A текстовую последовательность «AT» и, если она найдена, отобразить «AT» в столбце B .

Не имеет значения, где буквы «AT» встречаются в столбце текст A , нам нужно видеть «AT» в соседней ячейке столбца B . Если буквы «AT» не встречаются в тексте столбца A , формула в столбце B не должна отображать ничего.

Начнем с выбора ячейки B5 и ввода следующей формулы IF .

 = IF (A5 =  ”* AT *”, ”AT”, ””) 

Обратите внимание, что формула ничего не возвращает, хотя текст в ячейке A5 содержит последовательность букв «AT».

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

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

« А как насчет функции СУММЕСЛИМН? », Я слышал, вы говорите.

Если вы исследуете функцию СУММЕСЛИМН , подстановочные знаки не появятся сразу после знака равенства; вместо этого они приходят после спора. В качестве примера:

 = SUMIFS ($ C $ 4: $ C $ 18, $ A $ 4: $ A $ 18, ”* AT *”) 

Использование подстановочного знака не появляется сразу после знака равенства.

Первое, что нам нужно понять, это синтаксис функции ПОИСК . Синтаксис следующий:

SEARCH (find_text, within_text, [start_num])

  • find_text — обязательный аргумент, определяющий текст, который вы ищете.
  • within_text — обязательный аргумент, определяющий текст в котором вы хотите найти значение, определенное в
  • Start_num — это аргумент параметра, определяющий символ число/позицию в аргументе within_text , по которому вы хотите начать поиск. Если не указано, позиция начального символа по умолчанию — 1 ( первый символ слева от текста. )

Чтобы проверить, работает ли функция поиска работает правильно сам по себе, давайте проведем простой тест по следующей формуле.

 = SEARCH («AT», A5) 

Нам возвращается позиция символа, в которой буквы «AT» были обнаружены функцией ПОИСК . Первый ПОИСК обнаружил буквы «AT», начинающиеся на позиции 1 st текста. Следующее открытие было в позиции 5 th символа, а последнее открытие было в позиции 4 th символа.

Ответы « #VALUE! » — это способ функции SEARCH сообщить нам, что буквы «AT» не были найдены в поисковом тексте.

Мы можем использовать эту новую информацию, чтобы определить, существует ли текст «AT» в строках сопутствующего текста.. Если мы видим какое-либо число в качестве ответа, мы знаем, что в текстовой строке существует «AT». Если мы получаем ответ с ошибкой, мы знаем, что текст «AT» не существует в текстовой строке.

ПРИМЕЧАНИЕ. Функция ПОИСК НЕ чувствительно к регистру. Поиск букв «AT» приведет к поиску «AT», «At», «aT» и «at». Если вы хотите искать текст и различать разные случаи (с учетом регистра), используйте функцию НАЙТИ . Функция FIND работает так же, как SEARCH , но с добавлением чувствительности к регистру.

Интересной функцией в Excel является функция ISNUMBER . Назначение функции ISNUMBER — вернуть «True», если что-то является числом, и «False», если что-то не является числом. Синтаксис следующий:

ISNUMBER (значение)

  • value — обязательный аргумент, определяющий исследуемые данные. Значение может относиться к ячейке, формуле или имени, которое относится к ячейке, формуле или значению.

Давайте добавим ISNUMBER в соответствии с логикой нашей предыдущей функции SEARCH .

 = ISNUMBER (SEARCH («AT», A5)) 

Теперь для любой ячейки, содержащей числовой ответ, отображается значение «True», а для любой ячейки, содержащей ошибку. теперь читает «False».

Теперь мы можем использовать функции ISNUMBER/SEARCH в качестве оператора подстановки в исходном IF функция.

 = IF (ISNUMBER (SEARCH («AT», A5)), «AT», »») 

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

Предположим, мы хотим найти два разных набора текста («AT» или «DE») и вернуть слово «Европа», если любой из эти текстовые строки обнаруживаются в искомом тексте. Мы объединим исходную формулу с функцией OR для поиска нескольких текстовых строк.

 = IF (OR (ISNUMBER (SEARCH («AT», A5)  ), ISNUMBER (ПОИСК («DE», A5))), «Европа», »») 

Практическое пособие

Вы можете скачать учебное пособие ЗДЕСЬ.

Информационные панели Excel, которые информируют и впечатляют

Используйте эти методы в своих собственных отчетах

Непревзойденная ценность!

ПОДРОБНЕЕ
Оцените статью
logicle.ru
Добавить комментарий