Общая формула
=СЧЁТЕСЛИ(A1;»*текст»)
=COUNTIF(A1,»*text»)
Объяснение
Примечание. Excel содержит множество встроенных правил для выделения значений с условным форматированием, включая правило, чтобы выделить ячейки, которые заканчиваются определенным текстом. Однако, если вы хотите больше гибкости, вы можете использовать свою собственную формулу, как описано в этой статье.
Если вы хотите выделить ячейки, которые заканчиваются определенным текстом, вы можете использовать простую формулу, основанную на функции СЧЁТЕСЛИ. Например, если вы хотите выделить состояния в диапазоне B4:G12, которые заканчиваются на «ота», вы можете использовать:
=СЧЁТЕСЛИ(B4;»*ота»)
=COUNTIF(B4,»*ota»)
Примечание: важно, чтобы формула условного форматирования была указана относительно «активной ячейки», которой в данном случае считается B4.
Как работает формула
Когда вы используете формулу для применения условного форматирования, формула вычисляется относительно активной выбранной ячейки при создании правила. В этом случае правило вычисляется для каждой ячейки в B4:G12, а ссылка на B4 будет изменяться на адрес каждой вычисляемой ячейки, поскольку это относительный адрес.
Сама формула использует функцию СЧЁТЕСЛИ для «подсчета» ячеек, которые заканчиваются «ота», используя шаблон «*ота», который использует подстановочный знак (*) для соответствия любой последовательности символов, за которой следует «ота».
С практической точки зрения мы каждый раз подсчитываем по 1 ячейке, что означает, что мы либо собираемся вернуть 1 или 0, что отлично подходит для условного форматирования.
Более простое, более гибкое правило с использованием именованных диапазонов
Назвав ячейку ввода как именованный диапазон и ссылаясь на это имя в формуле, вы можете сделать формулу более мощной и гибкой. Например, если вы назовете «ввод» G2, вы можете переписать формулу следующим образом:
=СЧЁТЕСЛИ(B4;»*»&ввод)
=COUNTIF(B4,»*»&input)
Эта формула просто добавляет «*» к началу того, что вы помещаете во входную ячейку. В результате правило условного форматирования будет применяться мгновенно, когда это значение будет изменено.
Вариант, чувствительный к регистру
СЧЁТЕСЛИ не чувствителен к регистру, поэтому, если вам нужно также проверить регистр, вы можете использовать более сложную формулу, которая использует функции ПРАВСИМВ и СОВПАД:
=СОВПАД(ПРАВСИМВ(A1;ДЛСТР(подстрока));подстрока)
=EXACT(RIGHT(A1,LEN(substring)),substring)
В этом случае ПРАВСИМВ извлекает текст справа от каждой
ячейки и только количество символов в подстроке, которую вы ищете, которая вычисляется
функцией ДЛСТР. Наконец, СОВПАД сравнивает извлеченный текст с текстом, который
вы ищете (подстрока). СОВПАД чувствителен к регистру, поэтому возвращается
только ИСТИНА, когда все символы точно совпадают.
