Пользовательские форматы (Custom format)

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Дальше: Как изменить шаблон книги Excel по умолчанию

Файл с примерами: Пользовательские форматы.xlsx

Пользовательский формат — числовой формат, создаваемый «с нуля» на специальном языке. Используя коды, применяемые в пользовательских форматах, мы можем создавать собственные варианты отображения чисел, дат, текста в ячейках. И сделать наши таблицы наглядными (и нарядными) и настроенными именно под наши задачи.

Формат настраивается в окне формата ячеек — Ctrl + 1 (

+ 1) — выбирайте «Все форматы» (Custom) и вводите код формата в поле сверху.

В Google Таблицах: Формат → Числа → Другие форматы чисел (Format → Number → Custom number format).

СИМВОЛЫ, ИСПОЛЬЗУЕМЫЕ В КОДАХ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМАТОВ

0 — незначащие цифры (отображаются всегда)

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

, — десятичная запятая (знак, отделяющий целую часть от дробной)

В американских региональных настройках и в Google Таблицах при любых настройках — точка, а не запятая.

Используйте ее в формате, если нужно отображать знаки после запятой.

Например:

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

# — значащие цифры (отображаются, если на этой позиции есть значение)

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

? — цифры после запятой, если нужно выравнивать числа по десятичной запятой

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

# ## — разделители разрядов

В американских региональных настройках и Google Таблицах #,## (с запятой между решетками, а не пробелом).

# ## — разделители групп разрядов (пробелы в российских региональных настройках) к числу. Например, # ##0,00 — это числовой формат с разделителями групп разрядов и двумя обязательными знаками после запятой.

0% — процентный формат

Здесь все просто: 0% — это процентный формат, полностью соответствующий стандартному процентному формату, который формируется в ячейках автоматически при вводе знака процента. Но в пользовательских форматах вы можете его модифицировать, например добавить разделители групп разрядов.

* — заполнение ячейки указанным символом до конца

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

"Текст" — текст, указанный в кавычках, будет отображаться в ячейке

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

В следующем примере к числу с разделителями разрядов мы добавляем текст "шт.".

Пробел — после формата числа округляет его до тысяч (два пробела — до миллиона, три — до миллиарда и так далее).

В американских региональных настройках — запятая, а не пробел.

В следующем примере в качестве формата используется 0 (с одним пробелом) после него. Это округляет числа до тысяч.

@ — текст, введенный в ячейке

Этот символ обозначает текст из ячейки. Например, @@ — повторение текста дважды. А @*- — формат с заполнением ячейки дефисами после текста.

_ (нижнее подчеркивание) — отступ на ширину указанного после нижнего подчеркивания символа

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

Без отступа эти числа выглядели бы так.

[Цвет] — цвет значения

Задается по названию (в Excel — на языке интерфейса, в Google Таблицах — на английском) [Красный][Red] или номеру [ЦветN][ColorN].

Указывается перед форматом.

Например, [Синий]0,00 — синий цвет, число с двумя знаками после запятой.

Коды цветов есть в файле с примерами — «Пользовательские форматы.xlsx».

СТРУКТУРЫ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМАТОВ

Есть две структуры пользовательских форматов.

По типу данных. Разные форматы для нескольких или для всех четырех вариантов: положительных, отрицательных чисел, нуля и текста. Форматы задаются именно в таком порядке через точку с запятой. Если пропустить формат (например, указать только два варианта), это будут форматы для положительных и отрицательных чисел. Специального форматирования к нулям и тексту применяться не будет. Если указать три, то будут заданы форматы для всех типов данных, кроме текста. Если что-то пропустить (то есть оставить формат пустым, как в первом примере ниже), то соответствующие данные не будут отображаться.

Положительные; Отрицательные; Ноль; Текст

Пример 1: положительные числа — зеленым, отрицательные — красным, ноль не отображаем, текст — синим.

[Зеленый]0;[Красный]-0;;[Синий]@

Пример 2: положительные числа со знаком «плюс» и в процентном формате, отрицательные — со знаком «минус», красным цветом, в процентном формате. Ноль — как прочерк (дефис, «-»).

+ 0%;[Красный]-0%;"-"

По условиям. Один формат для одного условия, опционально — другой для второго и третий для всех остальных случаев.

Условие 1; Условие 2; Остальные случаи

Условия указываются в квадратных скобках с использованием знаков «равно» (=), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=).

Пример 1: числа больше 2000 с разделителями разрядов, меньше — с одним знаком после запятой:

[>2000]# ##0;0,0

Пример 2: единицы отображаем как слово «один», двойки — как «два», остальные числа — в обычном формате, как числа (без разделителей групп разрядов, без знаков после запятой).

Пользовательские форматы в Google Таблицах

Все очень похоже на Excel, но некоторые нюансы и внешний вид диалогового окна с форматами отличаются, так что, если пользуетесь таблицами от Google, предлагаю вам статью и видео для ознакомления:

^Видео:^,

^{Пользовательские числовые форматы в Google Таблицах (Custom number formats in Google Sheets):}

Дополнительные примеры в статье и видео будут полезны и пользователям Excel.

Назад: Стили (Cell Styles)

Дальше: Как изменить шаблон книги Excel по умолчанию