Условное форматирование числовых ячеек, включающих символы

Условное форматирование числовых ячеек, включающих символы

Общая формула

=ЕСЛИ(ЕЧИСЛО(B4);B4<ЧИСЛО;ЕСЛИ(ЛЕВСИМВ(B4)="<";(ПСТР(B4;2;ДЛСТР(B4))+0)<ЧИСЛО))

=IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)="<",(MID(B4,2,LEN(B4))+0)<input))

Объяснение

Перед нами стоит задача выделить числа в таблице, которые меньше определенного значения, в том числе и числа, введенные в виде текста,например, «<9», «<10» и т.д.

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

В показанном примере «ЧИСЛО» является именованным диапазоном для ячейки G2, то есть имя, заданное для ячейки G2.

Как работает формула

Формула сначала использует функцию ЕЧИСЛО, чтобы проверить, является ли конкретное значение таблицы числом.

=ЕСЛИ(ЕЧИСЛО(B4)

=IF(ISNUMBER(B4)

Для любого числа, которое меньше значения, заданного в ячейке G2 (ЧИСЛО), формула дает значение ИСТИНА и к ячейкам будет применено условное форматирование.

Если же значение не является числом, формула проверяет, является ли первый символ знаком «<», используя функцию ЛЕВСИМВ:

ЕСЛИ(ЛЕВСИМВ(B4)="<"

=IF(LEFT(B4)="<"

Если это так, функция ПСТР отображает все знаки, идущие после символа «<»:

ПСТР(B4;2;ДЛСТР(B4)

=MID(B4,2,LEN(B4)

Технически функция ДЛСТР возвращает значение числа без учета стоящего перед ним знака «<», т.е. на 1 больше, чем нам нужно. Например, ячейка E9, значение которой <10, не была выделена цветом. Чтобы избежать данного обстоятельства, нужно дополнительно вычесть 1.

Результатом функции ПСТР всегда является текст, и для того, чтобы Excel преобразовал текст в число, в формуле прибавляется нуль. Затем это число сравнивается с заданным значением (ЧИСЛО).

Оцените статью
Добавить комментарий