У меня есть условное форматирование с ячейками вроде
= 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, которые информируют и впечатляют