У Кима есть столбец данных, в котором содержится код местоположения. Этот код состоит из одной буквы, за которой следуют две цифры, например A03 или B12.

Ким хотел бы условно отформатировать столбец, чтобы все, что введено в столбец, не использующее этот шаблон, было выделено каким-либо образом.

Есть много способов решения этой проблемы. Каждый подход зависит от разработки формулы, которая может использоваться в правиле условного форматирования, чтобы возвращать True или False и запускать условное форматирование. (Этот совет не будет касаться того, как создать правило условного форматирования, вместо этого он сосредоточится на различных формулах, которые могут использоваться в правиле. Как вы создаете правила условного форматирования, описано в других ExcelTips.)

Какая бы формула ни была составлена, необходимо проверить три вещи:

  • В строке ровно три символа.

  • Первый символ — это буква.

  • Второй и третий символы — цифры.

Определить, состоит ли текст в ячейке только из трех символов, довольно просто; для этого можно использовать функцию LEN:

=LEN(A1)=3

Узнать, является ли первый символ буквой, тоже довольно просто. На самом деле это можно сделать двумя способами. Любой из следующих вариантов вернет True, если первый символ — буква:

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

Они проверяют, чтобы в первой позиции была только заглавная буква. Если вы хотите также принимать строчные буквы, вы можете использовать вариант второго теста:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

Если допустимы как прописные, так и строчные буквы (наряду с практически любым другим символом), вы можете рассмотреть возможность использования следующего теста:

=NOT(ISNUMBER(LEFT(A1,1)+0))

Вот несколько способов применить третий тест — являются ли второй и третий символы цифрами:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

Обратите внимание, что эти подходы обрабатывают последние два символа вместе. Это означает, что все «1», «11» и «111» пройдут проверку — они успешно проверяются как числа. Если ваша формула проверяет только последние две цифры, это может быть проблемой, но тот факт, что вы также включите первую проверку (для общей длины строки в ячейке и что она должна быть 3), то это не значит вообще не представляет проблемы.

Теперь уловка состоит в том, чтобы объединить выбранный вами подход для каждого из трех тестов в единую формулу. Это можно сделать с помощью функции И. Я просто выберу самые короткие из каждого теста и объединю их следующим образом:

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

Как написано, эта формула возвращает True, если все тесты пройдены, что означает, что ячейка содержит код местоположения с допустимым шаблоном. Это было бы прекрасно в качестве условного формата, если бы Ким отформатировал столбец как цвет (скажем, зеленый), а затем использовал бы условный формат для удаления зеленого цвета. Это может показаться отсталым, и на самом деле вы можете захотеть применить формат, только если шаблон не соблюдается. В этом случае просто заключите формулу в функцию НЕ, чтобы изменить значение True / False, которое возвращается:

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

Как вы понимаете, использование такой формулы может быть немного сложным. Вы можете, если хотите, создать UDF (определяемую пользователем функцию), которая сделает правило условного форматирования немного короче. Следующий макрос — отличный вариант:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

Чтобы использовать UDF в правиле условного форматирования, все, что вам нужно сделать, это использовать следующую формулу:

=IsBadPattern(A1)

Результатом UDF будет ИСТИНА, если строка в указанной ячейке не соответствует желаемому шаблону. Как написано, это не позволяет использовать строчные буквы в позиции первого символа. Если вам нужно разрешить строчные буквы, вам не нужно изменять UDF. Вместо этого измените формулу на следующую:

=IsBadPattern(UPPER(A1))

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9976) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.