Формат на основе содержимого ячейки

Рубрика: 7. Полезняшки Excel

В апреле 2014 г. я написал заметку Пользовательский формат числа в Excel. (Кстати, она довольно популярна – более 50 посетителей в день.) Несколько неожиданным для меня было большое число вопросов о возможности изменения формата в зависимости от содержимого ячейки. И я решил описать эту ситуацию подробнее.

Напомню, что числовые форматы могут иметь до четырех разделов кода, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных, нулевых значений и текста. При этом использование формул внутри формата не предусмотрено. Нельзя, например, вставить функцию ЕСЛИ в код формата, и попытаться применить разные форматы в случае различного содержимого ячеек. Однако…

…для этих целей можно применить условное форматирование на основе формул.

Рис. 1. Условное форматирование на основе формул

Рис. 1. Условное форматирование на основе формул

Скачать заметку в формате Word или pdf, примеры в формате Excel

Начнем с вопросов Андрея и Варвары. Формат числа должен отвечать нескольким условиям:

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

Другими словами, надо, чтобы числа, набранные, как в левой колонке, отображались, как в правой:

23345,10 -> 23 345,1

23345,15 -> 23 345,15

23345,146 -> 23 345,15

23345,00 -> 23 345

Наиболее точно указанным условиям отвечают форматы:

  • Общий, но у него два недостатка: он не разделяет группы разрядов, и его нельзя использовать внутри функции ТЕКСТ(значение; формат);
  • # ##0,##, но он оставляет «глупую» запятую, если число целое: 23345,00 -> 23 345,

Подобрать формат, который полностью отвечал бы указанным условиям вроде бы нельзя… На помощь приходит условное форматирование. Назначаем требуемой области листа формат # ##0,##, который отработает верно во всех случаях, кроме целых чисел. Далее для этой же области задаем условное форматирование с использованием формулы =ОКРУГЛ(A1;0)=A1 (рис. 1). Это условие выполняется только если число в ячейке целое. И для этого условия задаем стандартный числовой формат с разделением разрядов на группы и нулем знаков после запятой. Чтобы задать условное форматирование, выделите область на листе, и пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек. Введите формулу и кликните кнопку Формат. В окне Формат ячеек перейдите на закладку Число. Выберите формат Числовой, число десятичных знаков – 0, поставьте галочку напротив Разделитель групп разрядов (рис. 2). Кликните Ok.

Рис. 2. Формат ячеек

Рис. 2. Формат ячеек

Вуаля)) Получилось то, что требовалось (рис. 3).

Рис. 3. Использование формата # ##0,## и условного форматирования

Рис. 3. Использование формата # ##0,## и условного форматирования

Следующий вопрос пришел от Владимира. Возможно ли пользовательское форматирование с условием проверки введённого? Например, если вводится целое число, то добавить " шт.", а если дробное, то " кг".

Опять же, невозможно задать проверку целочисленности внутри пользовательского формата, но можно задать один формат на листе, а второй с помощью условного форматирования (как в примере выше), или оба формата задать при помощи условного форматирования. Во втором случае, условия представлены на рис. 2. Формат для дробных (первое условие) # ##0,00" кг", для целых (второе условие) – # ##0" шт."

Рис. 4. Два различных формата для целых и дробных чисел

Рис. 4. Два различных формата для целых и дробных чисел

Вопрос Сергея. Можно ли отформатировать часть текста в ячейке по какому-либо признаку? Например, изменить цвет шрифта текста, заключенного в скобки?

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

Вопрос от Дарьи. Можно ли к числу в ячейке с помощью формата добавить через слэш то же самое число? Например, в ячейку введено 12, а отражается 12/12.

Для этого применяется формат @"/"@. Напомню. Для того, чтобы вводимый в ячейку текст отображался на экране, в соответствующее место текстового раздела формата следует поместить знак @. Если текстовый раздел не содержит знака @, вводимый в ячейку текст на экране отображаться не будет. Чтобы вводимый текст дополнялся определенными символами, заключите эти символы в двойные кавычки (" "), например, "валовой доход за "@. Если нужно, чтобы вводимый текст отображался дважды, следует использовать символ @ два раза.

Условный формат на основе параметров ячейки. В Excel есть редко используемая функция ЯЧЕЙКА(тип_сведений;[ссылка]), которая позволяет получить некоторые сведения о содержимом или формате ячейки. Например, у нас есть ничем не примечательный диапазон А1:А7 (рис. 5), и мы хотим с помощью условного форматирования выделить пустые ячейки.

Рис. 5. Фрагмент листа с вроде бы пустыми ячейками

Рис. 5. Фрагмент листа с вроде бы пустыми ячейками

Используем для этой цели условное форматирование на основе формулы =ЯЧЕЙКА("тип";A1)="b" (рис. 6). Значение b соответствует пустой ячейке (подробнее см. описание функции на сайте Microsoft). Напомню, что строка формул в окне условного форматирования должна содержать выражение (формулу), возвращающую значения ИСТИНА или ЛОЖЬ. Таким образом, если проверяемая ячейка пустая, то функция ЯЧЕЙКА вернет значение b, а формула =ЯЧЕЙКА("тип";A1)="b" – значение ИСТИНА, и ячейка окрасится в зеленый цвет.

Рис. 6. Выделение пустых ячеек с помощью условного форматирования

Рис. 6. Выделение пустых ячеек с помощью условного форматирования на основе формулы, использующей функцию ЯЧЕЙКА

Оказалось, что в ячейке А2 содержится формула ="", а в ячейке А5 – число 5, написанное шрифтом белого цвета 🙂

Рис. 7. Подстава, ячейки, выглядящие пустыми, таковыми не являются

Рис. 7. «Подстава»: ячейки, выглядящие пустыми, таковыми не являются

Комментарии: 3 комментария

Думаю, стоит заметить, что злоупотребление условным формативанием может существенно тормозить Эксель.

Время от времени пользуюсь форматом : ;;; или ;;;@
Для того чтобы хранить системную информацию и не отображать её совсем или не отображать только числовую информацию соответственно

Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
Скорректированная стоимость =
= Стоимость * (К1 + К2 + … + КN – (N — 1);
где:
К1, К2, КN — коэффициенты, отличные от 1
N – количество коэффициентов, отличных от 1.


Прокомментировать