В апреле 2014 г. я написал заметку Пользовательский формат числа в Excel. (Кстати, она довольно популярна – более 50 посетителей в день.) Несколько неожиданным для меня было большое число вопросов о возможности изменения формата в зависимости от содержимого ячейки. И я решил описать эту ситуацию подробнее.
Напомню, что числовые форматы могут иметь до четырех разделов кода, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных, нулевых значений и текста. При этом использование формул внутри формата не предусмотрено. Нельзя, например, вставить функцию ЕСЛИ в код формата, и попытаться применить разные форматы в случае различного содержимого ячеек. Однако…
…для этих целей можно применить условное форматирование на основе формул.
Рис. 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. Формат ячеек
Вуаля)) Получилось то, что требовалось (рис. 3).
Рис. 3. Использование формата # ##0,## и условного форматирования
Следующий вопрос пришел от Владимира. Возможно ли пользовательское форматирование с условием проверки введённого? Например, если вводится целое число, то добавить "
шт."
, а если дробное, то "
кг"
.
Опять же, невозможно задать проверку целочисленности внутри пользовательского формата, но можно задать один формат на листе, а второй с помощью условного форматирования (как в примере выше), или оба формата задать при помощи условного форматирования. Во втором случае, условия представлены на рис. 2. Формат для дробных (первое условие) # ##0,00"
кг"
, для целых (второе условие) – # ##0"
шт."
Рис. 4. Два различных формата для целых и дробных чисел
Вопрос Сергея. Можно ли отформатировать часть текста в ячейке по какому-либо признаку? Например, изменить цвет шрифта текста, заключенного в скобки?
К сожалению, нет. Ни пользовательский формат, ни условное форматирование, не могут быть применены к части содержимого ячейки. Во всех типах форматирования (см. перечень на рис. 1) речь идет о том или ином форматировании ячеек целиком. Эту задачу можно решить написанием кода VBA.
Вопрос от Дарьи. Можно ли к числу в ячейке с помощью формата добавить через слэш то же самое число? Например, в ячейку введено 12, а отражается 12/12.
Для этого применяется формат @"
/"
@. Напомню. Для того, чтобы вводимый в ячейку текст отображался на экране, в соответствующее место текстового раздела формата следует поместить знак @. Если текстовый раздел не содержит знака @, вводимый в ячейку текст на экране отображаться не будет. Чтобы вводимый текст дополнялся определенными символами, заключите эти символы в двойные кавычки ("
"
), например, "
валовой доход за "
@. Если нужно, чтобы вводимый текст отображался дважды, следует использовать символ @ два раза.
Условный формат на основе параметров ячейки. В Excel есть редко используемая функция ЯЧЕЙКА(тип_сведений;[ссылка]), которая позволяет получить некоторые сведения о содержимом или формате ячейки. Например, у нас есть ничем не примечательный диапазон А1:А7 (рис. 5), и мы хотим с помощью условного форматирования выделить пустые ячейки.
Рис. 5. Фрагмент листа с вроде бы пустыми ячейками
Используем для этой цели условное форматирование на основе формулы =ЯЧЕЙКА("
тип"
;A1)="
b"
(рис. 6). Значение b соответствует пустой ячейке (подробнее см. описание функции на сайте Microsoft). Напомню, что строка формул в окне условного форматирования должна содержать выражение (формулу), возвращающую значения ИСТИНА или ЛОЖЬ. Таким образом, если проверяемая ячейка пустая, то функция ЯЧЕЙКА вернет значение b, а формула =ЯЧЕЙКА("
тип"
;A1)="
b"
– значение ИСТИНА, и ячейка окрасится в зеленый цвет.
Рис. 6. Выделение пустых ячеек с помощью условного форматирования на основе формулы, использующей функцию ЯЧЕЙКА
Оказалось, что в ячейке А2 содержится формула ="
"
, а в ячейке А5 – число 5, написанное шрифтом белого цвета 🙂
Рис. 7. «Подстава»: ячейки, выглядящие пустыми, таковыми не являются
Думаю, стоит заметить, что злоупотребление условным формативанием может существенно тормозить Эксель.
Время от времени пользуюсь форматом : ;;; или ;;;@
Для того чтобы хранить системную информацию и не отображать её совсем или не отображать только числовую информацию соответственно
Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
Скорректированная стоимость =
= Стоимость * (К1 + К2 + … + КN – (N — 1);
где:
К1, К2, КN — коэффициенты, отличные от 1
N – количество коэффициентов, отличных от 1.