Пользовательские форматы чисел в Excel: полное руководство
Одним из мощнейших инструментов Excel для профессионального представления данных являются пользовательские форматы чисел. Они позволяют отделить хранимое в ячейке значение от его визуального отображения, создавая понятные и информативные отчеты.
Форматы ниже вводятся в настольном Excel для Windows или Mac. Создавать пользовательские числовые форматы в Excel для Интернета нельзя; для этого откройте книгу в настольном приложении. Коды зависят от языка и региональных разделителей.
Например, при английских региональных настройках число 6.42452514 можно показать как $6.42 форматом $#,##0.00. Для русских разделителей аналогичный код — "$"# ##0,00. Значение остаётся исходным; 0 задаёт обязательную цифру, # — необязательную.
Структура кода формата: зоны и разделители
Код формата может содержать до четырех отдельных зон, разделенных точкой с запятой ;. Каждая зона отвечает за отображение разных типов данных.
- Одна зона: применяется ко всем числам; текст без отдельной текстовой секции обычно отображается как введён.
- Две зоны: первая — для неотрицательных чисел, вторая — для отрицательных.
- Три зоны: для положительных, отрицательных чисел и нуля соответственно.
- Четыре зоны: добавляется зона для текстовых значений.
Чтобы создать свой формат, перейдите в Формат ячеек → Число → (все форматы) и введите код в поле «Тип». Предварительный результат отобразится в поле «Образец».
Практические приемы и примеры
1. Добавление текста и скрытие нулей
Текст в кавычках добавляет пояснение к отображаемому числу. В следующем примере с английскими разделителями третья секция показывает для нуля «Нет баланса». Чтобы вместо этого скрыть нули, оставьте третью секцию пустой, например 0;-0;.
"Пожалуйста, оплатите "$0.00;"Кредитовый баланс "$0.00" (Не оплачивать)";"Нет баланса"
2. Цветовое кодирование
Цвет задаётся в квадратных скобках в начале секции. Здесь и далее цветовые коды показаны для английского Excel; в русской локализации используются названия вроде [Зеленый], [Синий] и [Красный].
[Green]0;[Blue]-0;
Код выше окрасит положительные числа в зеленый, отрицательные — в синий, а нули будут скрыты из-за пустой третьей зоны.
3. Заполнители и разделение на разряды
- Заполнители: Символ
*перед любым символом заставляет Excel повторять этот символ для заполнения ширины ячейки в месте расположения кода (например,**0или*!0). - Разделение на тысячи/миллионы: При английских разделителях каждая запятая после последнего цифрового заполнителя уменьшает отображаемый масштаб в 1000 раз. Значение ячейки не меняется. Код
0,"K"отобразит 1500 как «2K», а0,,"M"— 2 000 000 как «2M».
4. Условное форматирование в коде
Вы можете встроить условия прямо в код формата, используя квадратные скобки.
[Red][<70]0;[Blue][>90]0;0
Этот код сделает числа меньше 70 красными, больше 90 — синими, а остальные отобразит с обычным цветом шрифта ячейки. Это условия числового формата, а не правила инструмента «Условное форматирование».
5. Форматы дат
При английской локализации для дат применяются m (месяц), d (день), y (год). Для 4 июля 2022 года формат dddd" "d" of "mmmm при английском языке даты даёт «Monday 4 of July». В русском Excel используйте коды д, м, г, например дд.мм.гггг. Названия дней и месяцев зависят от языка даты.
Примечание: Английский код mmmmm (русский ммммм) выводит первую букву названия месяца на выбранном языке даты, что полезно для компактных подписей на диаграммах.
Заключение
Пользовательские форматы чисел — это ключ к созданию профессионально выглядящих и легко читаемых таблиц в Excel. Они позволяют не менять исходные данные, а управлять только их представлением, добавляя контекст, цвет и ясность. Освоив этот инструмент, вы значительно повысите эффективность своей работы с данными.