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

Числа в Google Таблицах выглядят так, как их отображает формат ячейки, – и это не одно и то же, что само значение. В ячейке может храниться число 1500, а отображаться «1 500 руб.», «1,5 тыс.» или даже «высокий показатель» – при этом для формул ячейка по-прежнему остается числом 1500. Именно это и делают пользовательские форматы: меняют внешний вид данных, не трогая их содержимое.

Встроенные форматы закрывают большинство стандартных потребностей, но как только нужно добавить подпись «руб.» после числа, показать длительность в часах и минутах или отобразить телефон в читаемом виде – приходится создавать формат вручную. Разберем, как это делается и какие задачи решает.

Как открыть редактор форматов

Выделите ячейку или диапазон, которому нужно задать формат, затем пройдите по пути «Формат» – «Числа» – «Другие форматы чисел». 

Откроется небольшое окно с полем для ввода кода формата и предпросмотром того, как будет выглядеть число. Там же хранятся все ранее созданные форматы – они доступны для повторного применения в любой момент.

Для форматов даты и времени путь немного другой: «Формат» – «Числа» – «Другие форматы» – «Пользовательские дата и время». 

Там интерфейс удобнее – с кнопками вместо кода, хотя коды тоже работают.

Символы, из которых строится формат

Прежде чем разбирать конкретные примеры, стоит познакомиться с базовыми символами. Их не так много, и каждый отвечает за конкретную вещь.

• 0 – цифра. Если в числе цифр меньше, чем нулей в формате, добавляются незначащие нули. Например, формат 00000 покажет число 42 как «00042».
• # – цифра без незначащих нулей. Формат # покажет 42 просто как «42», а не как «042». Удобен там, где пустые нули только мешают.
• ? – цифра с пробелом вместо незначащего нуля. Используется для выравнивания десятичных запятых в столбце, когда числа имеют разное количество знаков после запятой.
• , – символ с тремя разными значениями в зависимости от позиции. Внутри числового шаблона между целой и дробной частью (0,00) – это десятичный разделитель. Между группами цифр в числе (#,##0) – разделитель разрядов, то есть тысячный пробел. Одна или две запятые в самом конце шаблона (#,##0, или #,##0,,) – округление до тысяч или миллионов. Google Таблицы принимают и точку, и запятую в роли десятичного разделителя, но на экране всегда отображается тот символ, который задан в региональных настройках – при русском регионе это запятая, поэтому в примерах ниже используется она.
• % – умножает число на 100 и добавляет знак процента.
• "текст" – любой текст в кавычках добавляется в ячейку как есть, не влияя на числовое значение.
• @ – текстовое значение ячейки. Пригодится, если нужно добавить что-то к тексту форматом.
• [Цвет] – цвет шрифта. Указывается на английском в квадратных скобках: [Red], [Green], [Blue], [Black], [White], [Yellow], [Cyan], [Magenta]. Для расширенной палитры используется [Color1]–[Color56].
• [условие] – условие для применения формата, например [>1000] или [<=0].

Структура формата: до четырех секций

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

положительные; отрицательные; ноль; текст

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

#,##0;(#,##0);

Третья секция пустая – это означает, что ноль не будет виден в ячейке, хотя значение там есть. Этот прием удобен в отчетах, где нули захламляют таблицу.

Практические примеры

Ниже собраны форматы для задач, с которыми сталкиваются чаще всего: добавление единиц измерения и валюты, округление больших чисел, работа с процентами, цветовая индикация, корректный подсчет времени и телефонные номера. Каждый пример можно скопировать и вставить в редактор форматов как есть или адаптировать под свои данные. Там же, в конце раздела, разобраны случаи, когда нужно наоборот – скрыть нули или вообще сделать ячейку визуально пустой, не удаляя из нее значение.

Числа с разделителем разрядов и единицей измерения

Самый частый запрос: сделать «1 500 руб.» вместо «1500». Для этого нужен формат:

#,##0" руб."

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

#,##0" кг"

#,##0" шт."

0,00" м²"

Если нужно добавить обозначение и перед числом тоже:

"$ "#,##0,00

Важно: текст в кавычках – это только отображение. В формулах ячейка все равно остается числом, и с ней можно складывать, умножать и делать все остальное.

Округление до тысяч и миллионов

Одна запятая в конце числового формата округляет значение до тысяч при отображении:

#,##0,

Число 1 500 000 с использованием подобного формата будет показано как «1 500». При этом можно использовать и две запятые, получая при этом округление до миллионов.

#,##0,,

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

#,##0," тыс."

#,##0,," млн."

Число 1 500 000 с первым форматом будет выглядеть как «1 500 тыс.», со вторым – «2 млн.».

Проценты с нужной точностью

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

0%

0,0%

0,00%

"+"0,0%;"−"0,0%

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

Цвет по знаку числа

Классический прием для финансовых таблиц – зеленый цвет для положительных значений, красный для отрицательных:

[Green]+#,##0;[Red]-#,##0;0

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

[Red][<0]#,##0;[Green][>=0]#,##0

Или более сложный вариант с тремя зонами – например, для KPI:

[Red][<80]0"%";[Green][>=100]0"%";[Color46]0"%"

Здесь значения до 80 красные, от 100 и выше – зеленые, все остальное (от 80 до 99) – оранжевое (Color46 в стандартной палитре). Знак % здесь взят в кавычки как литеральный текст, то есть умножения на 100 не происходит – данные в ячейках должны быть целыми числами вида 85 или 102, а не долями вроде 0,85. Условий может быть не более двух явных – третья секция будет «для остальных случаев».

Длительность: часы и минуты

Это одно из мест, где пользователи чаще всего путаются. Стандартный формат времени hh:mm:ss сбрасывается каждые 24 часа – если суммарная продолжительность больше суток, он покажет остаток, а не итог. Чтобы показывать именно накопленное время, буква h берется в квадратные скобки:

[h]:mm:ss

[h]:mm

[m]:ss

Первый вариант покажет 26 часов 15 минут как «26:15:00», а не сбросится в «2:15:00». Второй подходит для таймшитов, когда секунды не нужны. Третий – для записи длительности звонков или треков, где нужны минуты и секунды без часов.

Чтобы Google Таблицы вообще воспринимали значение как время, вводить его нужно в формате ч:мм или ч:мм:сс – например, 2:30 или 26:15:00. Если написать «2ч 30м», ячейка будет содержать текст, и никакой формат времени не сработает.

Телефонные номера

Телефонный номер выглядит как число, но хранить его числом неудобно – теряются ведущие нули, а «+7» в начале воспринимается как знак плюса перед формулой. Есть два подхода.

Первый – хранить номер как число из 10 цифр (без «+7» или «8» в начале) и показывать его форматом:

+7" ("000") "000"-"00"-"00

Например, число 9991234567 будет отображаться как «+7 (999) 123-45-67». Значение в ячейке останется числом, и его можно, например, склеивать с другим текстом через формулы.

Второй подход – хранить как текст, поставив перед номером апостроф при вводе ('79991234567). Апостроф не виден в ячейке, но сигнализирует Таблицам, что дальше идет текст. В этом случае форматирование числом не применить, но зато можно хранить любой вид записи, включая «+7 (999) 123-45-67» как есть.

Скрытие нулей и данных

Иногда нужно, чтобы нули в ячейках не отображались визуально – например, в разреженных таблицах, где ноль и пустота означают разное. Формат:

#,##0;-#,##0;;

Третья секция (для нуля) пустая – ноль не показывается. Четвертая (для текста) тоже пустая – текст тоже скрыт. Совсем скрыть любые данные, оставив ячейку визуально пустой:

;;;

Значение при этом никуда не пропадает – оно просто не отображается. Формулы с такими ячейками работают в штатном режиме.

Форматы в функции ТЕКСТ

Пользовательские форматы работают не только через меню, но и прямо в формулах через функцию ТЕКСТ. Это удобно, когда нужно вставить число в текстовую строку с нужным оформлением:

=ТЕКСТ(A1;"#,##0")&" руб."

=ТЕКСТ(A1;"0,0%")

=ТЕКСТ(A1;"[h]:mm")

="Итого: "&ТЕКСТ(СУММ(B2:B20);"#,##0")&" руб."

Разница с форматом ячейки в том, что ТЕКСТ возвращает именно текст – результат нельзя использовать в арифметических вычислениях. Зато его можно склеивать с другими строками через &, что и делает функцию незаменимой для сборки подписей, заголовков и пояснений прямо внутри формулы.

Частые проблемы

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

• Формат применен, но ячейка не меняется. Почти всегда это означает, что в ячейке хранится текст, а не число. Так бывает при вставке данных из внешних источников. Проверить можно по выравниванию: числа по умолчанию прижимаются вправо, текст – влево. Чтобы преобразовать текст в число, умножьте его на 1 через вспомогательный столбец (=A1*1) или воспользуйтесь функцией ЗНАЧЕН.

• Время считается неправильно после суммирования. Если сумма нескольких временных значений отображается некорректно, почти всегда помогает замена формата hh:mm на [h]:mm. Квадратные скобки вокруг h – принципиальное отличие, без них часы сбрасываются каждые сутки.

• Номер телефона с «+» воспринимается как формула. Плюс в начале ячейки Таблицы интерпретируют как начало формулы. Решение – перевести столбец в текстовый формат до ввода данных («Формат» – «Числа» – «Обычный текст») или ставить апостроф перед числом при вводе вручную.

• Разделитель разрядов не работает. В коде формата разделитель задается запятой (#,##0), но отображается согласно региональным настройкам таблицы. Если в регионе установлена Россия, разделителем будет пробел, если США – запятая. Изменить регион: «Файл» – «Настройки» – вкладка «Общие» – поле «Региональные настройки».

Заключение

Пользовательские форматы – один из тех инструментов, которые практически не влияют на то, как работает таблица, но сильно влияют на то, как она читается. Разобравшись с базовыми символами и логикой четырехсекционной структуры, можно быстро собрать нужный формат для любой ситуации: добавить подпись к числам, выделить цветом пороговые значения, корректно показать продолжительность или привести в порядок телефонную базу. Главное, что при этом данные в ячейках остаются нетронутыми и пригодными для любых вычислений – формат меняет только картинку, а не содержимое.

Изображение на обложке: Flaticon