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

Пользовательские форматы - мощная и полезная штука, о которой знают не все пользователи. Они доступны и в Excel, и в Google Таблицах.

Ячейки с приростом на скриншоте - это обычные пользовательские форматы, не условное форматирование.

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

Файл со всеми примерами из статьи

Где они настраиваются?

Формат -> Другие форматы -> Другие форматы чисел

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

Символы, используемые в пользовательских форматах

0 цифра. Незначащий нуль, то есть если в числе меньше цифр, чем в таких нулей в формате, то будут отображаться "пустые" нули.

# цифра. Только значащая, без "пустых" лишних нулей (например, формат #,## покажет 2.3 как 2.3, а 2.37 как 2.37).

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

. точка=десятичная запятая.

, при применении в самом конце формата одна запятая означает округление до тысяч - пример, разумеется, ниже. При применении между символами числа 0,0 - добавляются разделители разрядов.

% проценты.

*(после звездочки следует символ) заполняет ячейку указанным символом.

[Color] или [ColorN] добавляет цвет шрифта для формата.

\ позволяет отображать "служебные" символы как просто текст - это касается символов * # %.

@ это текстовое значение. Например, формат @@ будет повторять текст в ячейке дважды.

"текст" в кавычках можно указать текст, который будет отображаться в данном формате. Например, формат "Сумма: "0 всегда будет показывать текст "Сумма: " перед числом.

Отдельные форматы для положительных, отрицательных чисел, нуля и текста

Можно предложить отдельные форматы для положительных чисел, отрицательных чисел, нуля и текста.

Перечислять их нужно через точку с запятой в этом порядке. Указывать все не обязательно. Можно прописать только два формата - для положительных и отрицательных, например.

Так, например, этот формат не будет отображать нули вообще, а отрицательные числа будут в скобках и без знака минус:

0;(0);

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

;;;

Видите число? А оно там есть.

Цвет в пользовательских форматах

Чтобы добавить к формату цвет, нужно указать его название на английском в квадратных скобках в начале формата.

[Green]0;[Red]-0;0;[Blue]@
это зеленый цвет для положительных чисел, красный для отрицательных, синий для текста.

Понятно, что таким образом вариантов цветов можно указать не так много.

Для большего разнообразия пользуйтесь кодом цвета в формате [ColorN]. Код N можно посмотреть в этой таблице - спасибо за нее порталу Excel Jet:

Условие на величину числа

В пользовательских форматах можно добавлять условия: [условие] формат; [условие] формат; формат для остальных случаев. В таком варианте уже нельзя будет добавлять четыре разных варианта для положительных-отрицательных-нуля-текста. Больше трех условий добавить не получится - два явных и одно для остальных случаев.

Давайте рассмотрим простой пример, а в следующем пункте будет более интересный. Если вы хотите в случае единицы отображать "1 штука", а в остальных случаях - "2 штуки", "3 штуки" и т.д. - нужен следующий формат:

[=1]0 " штука";0 " штуки"

Напомню, формат на значение никак не влияет, истинное значение ячейки всегда можно видеть в строке формул. Наличие слова "Штуки" или любого другого текста в формате не повлияет на возможность проведения арифметических операций с числами. И не добавит никакого текста в сами ячейки, в их значения.

Добавим огня (символы в пользовательских форматах)

К пользовательским форматам можно добавлять символы. Например, огонь - как на скриншоте:

Как сделать такой формат?

Нужен следующий код:
[<2000][Red]#,# * 🔥;[>3000][Green]#,# * 🔥🔥🔥;[Blue]#,# * 🔥🔥

Расшифруем его:
[<2000] - условия. У нас есть формат для чисел меньше 2000, для чисел больше 3000 и для остальных случаев.
[Red] - цвет шрифта

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

* - выравнивает число слева, а текст справа

Незначащие нули

Если вы хотите отображать незначащие нули - введите столько нулей, сколько должно быть цифр в ячейке. Это подходит для индексов и артикулов.
Например, вам нужно отображать числа как артикулы из семи знаков - 874 как 0000874, 9872 как 0009872 и так далее.
Для этого случая подойдет формат:
0000000

Пример с 10 знаками:

Пользовательские форматы для номеров телефонов

Чтобы отображать телефонные номера, введенные просто как последовательность цифр, в нормальном виде - нужны пользовательские форматы.
Давайте посмотрим, как ими воспользоваться.

Формат (Alt+O) -> Числа (N) -> Другие форматы - > Другие форматы чисел

А дальше можно вводить формат. В самом простом виде просто вводим нули для цифр и знаки, которые хотим отображать:
0(000)000-00-00

Но такой формат не будет работать для номеров вида 9101234567, 1234567.
Только для 79161234567 или 89161234567.

Поэтому можно пойти дальше и вспомнить, что есть возможность задавать условия в пользовательских форматах.
Так, можно предусмотреть отдельный формат для чисел меньше 9999999 - без кода:
[<9999999]000-00-00;0(000)000-00-00

Такой вариант корректно отобразит и номера из 11 цифр, и номера из 7.
Но все еще не справится с номером из 10 цифр - как 9101234567.

Так что можно сделать еще один шаг и добавить решетку до скобок - на ее месте цифра будет отображаться только при ее наличии:

[<9999999]000-00-00;#(000)000-00-00

Теперь у нас предусмотрены три варианта.

Пользовательские форматы: округление до тысяч и миллионов

Продолжим исследовать бездонный мир пользовательских форматов и поговорим про округление.

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

Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
0.00,

Сработает округление и для форматов с разделителями разрядов:
#,##0,
(То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. - то в нем появятся разделители разрядов)

Можно добавить надпись после числа - чтобы не запутаться и точно понимать, что здесь числа в тысячах:
#,##0, тыс

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

И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
#,##0,, - миллионы с разделителями разрядов
0,,, - миллиарды без разделителей разрядов

Знак вопроса для выравнивания по десятичной запятой

Знаки вопроса после запятой - это значащее число после десятичной запятой, и при этом вместо незначащих будет появляться пробел. Таким образом, числа будут выравниваться по запятой, но пустые нули не будут отображаться. В следующем формате будет до трех знаков после запятой:

0.???

Заполнение ячейки символом

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

*-0

Функция ТЕКСТ / TEXT

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

Синтаксис у функции простой - значение (ссылка на ячейку, как правило); формат (в кавычках, так как это текстовое значение).

Вот пример задачи, для которой ТЕКСТ полезна - составляем целую фразу из разных значений формулой, и для формулы прироста указываем процентный формат без знака минус в отрицательных случаях:

Пользовательские форматы даты и времени

Как я уже писал, с ними чуть проще. Есть встроенный мини-редактор, в котором можно "собрать" нужный формат в конструкторе.  Найти его можно по адресу:

Формат -> Другие форматы -> Другие форматы даты и времени

Здесь щелкаем на стрелку и там появляются все возможные варианты. "Продолжительность" подойдет для времени больше суток  - например, если у вас событие, которое длится 30 часов.

Выбираем что-то, например "День", он появляется слева в окне формата и далее можно на него щелкнуть и выбрать нужный тип отображения.

В итоге можно собирать самые разные форматы - на скриншоте во всех ячейках одна и та же функция ТДАТА / NOW, возвращающая текущие время и дату:

Меню "Другие форматы даты и времени" - не единственный вариант создания форматов даты. Можно использовать и коды, которые пригодятся для той же функции ТЕКСТ / TEXT и которые можно использовать в числовых форматах.

Используются там следующие символы:

S - секунда, M - минута, H - час (в случае с продолжительностью - в квадратных скобках)

D - день, M - месяц, Y - год

В зависимости от количества символов меняется формат. То есть D - это дата без нуля впереди, DD - с нулем, DDD - краткий формат дня недели, DDDD - полное название дня недели.

YY - краткий формат года (20), YYYY - полный (2020).

Файл со всеми примерами из статьи

Источники - что почитать по теме

Джон Уокенбах - "Формулы в Microsoft Excel"

Excel custom number formats

***

Мой курс по Google Таблицам в Skillbox