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

2020-12-19

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

Перевод статьи «Google Sheets Custom Number Format»

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

Введение

Правила пользовательского формата чисел в Google Таблицах используются для определения специальных правил форматирования чисел.

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

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

Использование пользовательского формата чисел в Google Таблицах

Доступ к настраиваемым числовым форматам через меню:

Формат> Число> Дополнительные форматы> Пользовательский числовой формат

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

Вы вводите правило в верхнем поле и нажимаете «Применить», чтобы применить его к выделенной ячейке или диапазону.

Под полем ввода вы увидите предварительный просмотр того, что будет делать правило. Это дает вам полезное и довольно точное указание на то, как будут выглядеть ваши числа при применении этого правила.

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

Структура пользовательского числового формата Google Таблиц

У вас есть четыре «правила», которые вводятся в следующем порядке и разделяются точкой с запятой:

  1. Формат положительных чисел.
  2. Формат отрицательных чисел.
  3. Формат для нулей.
  4. Формат текста.

1. Формат положительных чисел

#, ## 0.00 ; [red] (#, ## 0.00); 0,00; «Какой-то текст» @

Первое правило, которое стоит перед первой точкой с запятой (;), сообщает Google Таблицам, как отображать положительные числа.

2. Формат отрицательных чисел

#, ## 0.00; [red] (#, ## 0.00) ; 0,00; «Какой-то текст» @

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

3. Форматирование нулей

#, ## 0.00; [red] (#, ## 0.00); 0,00 ; «Какой-то текст» @

Третье правило, которое находится между второй и третьей точкой с запятой, сообщает Google Sheets, как отображать нулевые значения.

Правило До После
0;0;“Zero” 0 Zero

4. Формат текста

#, ## 0.00; [red] (#, ## 0.00); 0,00; «Какой-то текст» @

Четвертое правило, которое следует после третьей точки с запятой, сообщает Google Sheets, как отображать текстовые значения.

Нужно ли использовать все четыре правила?

Нет, необязательно указывать их все каждый раз.

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

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

Вот несколько примеров форматов с одним и несколькими правилами:

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

Правило нулевой цифры (0)

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

Правило До После
0.00 1.5 1.50
00000 721 00721

Правило знака решётки (#)

Знак решётки (#) является заполнителем для необязательных цифр. Если ваше значение содержит меньше цифр, чем # символов в правиле формата, дополнительный # ничего не отобразит.

Правило До После
#### 15 15
#### 1589 1589
#.## 1.5 1.5

Разделитель тысяч (,)

Запятая (,) используется для добавления разделителей тысяч в правило форматирования. Правило #,##0будет работать для тысяч и миллионов чисел.

Правило До После
#,##0 1495 1,495
#,##0.00 1234567,89 1,234,567.89

Точка (.)

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

Правило До После
0. 10 10.
0. 10.1 10.
0.00 10 10.00

Тысячи (k или K) или миллионы (m или M)

Если вы добавите разделители тысяч, но не укажете формат после запятой (например, 0,), то сотни будут вырезаны из числа. Объедините это с буквой «k» или «K», чтобы указать тысячи, и вы получите хороший способ продемонстрировать сокращенные числа. Чтобы добиться этого с миллионами, вам нужно указать две запятые.

Правило До После
0.0, 2500 2.5
0,“k” 2500 3k
0.0,“k” 2500 2.5k
0.0,,“M” 1234567 1.2M

Отрицательное число в скобках ()

К правилу отрицательных чисел можно добавить скобки, чтобы изменить формат с -100 на (100), что часто встречается в бухгалтерских и финансовых сценариях.

Правило До После
0;(0) -100 (100)

Звездочка (астериск) (*)

Звездочка (*) используется для повторения цифр в правиле форматирования. Символ, следующий за звездочкой, повторяется по всей ширине ячейки.

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

Правило До После
*-0 100 ——————100

Вопросительный знак (?)

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

Нижнее подчеркивание (_)

Знак подчеркивания (_) также добавляет пробела к числовым форматам.

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

Например, #,##0.00_);(#,##0.00)после положительного знака добавляется пробел, равный ширине одной скобки, так что десятичная точка выравнивается с отрицательными числами в скобках.

Вы можете это увидеть на следующем изображении, где в первой строке НЕТ интервала, а во второй строке есть. Автор добавил красное выделение, чтобы показать результат расстановки интервалов:

Escape-символ (\) — экранирование

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

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

Это относится и к любым другим специальным символам.

Правило До После
#0 10 10
\#0 10 #10

Собачка (@)

Символ собачки (@) используется в качестве заполнителя для текста, что означает, что вводимый текст нельзя изменять.

Правило До После
0;0;0;“Special text value!” Some text Special text value!
0;0;0;@ Some text Some text

Дробная часть (/)

Косая черта (/) используется для обозначения дробей.

Например, в правиле # ?/? числа будут отображаться в виде дробей:

Правило До После
# ?/? 2.3333333333 2 1/3

Процент (%)

Знак процента (%) используется для форматирования значений как %. Как и в случае с другими правилами, вы сначала указываете цифры, а затем используете знак% для изменения на процент, например0.00%

Правило До После
0.00% 0.2829 28.29%

Экспонента (E)

Для очень больших (или очень маленьких) чисел используйте правило формата экспоненты, чтобы показать их более компактно.

Правило: число * E + n, в котором E (показатель степени) умножает предыдущее число на 10 в n-й степени.

Посмотрим на пример:

Правило До После
0.00E+00 23976986 2.40E + 07

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

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

Например:

Правило До После
[<100]“Small” ; [>500]“Large” ; “Medium” 50 Small
[<100]“Small” ; [>500]“Large” ; “Medium” 300 Medium
[<100]“Small” ; [>500]“Large” ; “Medium” 800 Large

Условные правила

  • Условия могут быть указаны только в первых двух правилах
  • Третье правило используется как формат для всего остального, что не удовлетворяет первым двум условиям.
  • Четвертое правило всегда используется для текста, поэтому его нельзя использовать для условного форматирования.

Справка Google

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

Добавьте цвета к своим правилам с помощью квадратных скобок [].

Вы можете использовать 8 названных цветов:

[black], [white], [red], [green], [blue], [yellow], [magenta], [cyan]

Чтобы получить больше цветов, используйте 2-значные коды цветов:

[Color1], [Color2], [Color3], ..., [Color56]

Для полного изложения цветовой палитры этих 56 цветов перейдите по ссылке.

Примеры цветов

Вот еще один пример использования правил пользовательского формата чисел в Google Таблицах с цветами: Как создать таблицу в Google Таблицах и сделать ее великолепной.

где правило:

Мета-инструкции для правил цвета из документации Google Sheets API.

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

Номер телефона

Превратите любой 11-значный номер в форматированный телефонный номер с правилом нулевых цифр и тире:

Правило До После
0 000-000-0000 18004567891 1 800-456-7891

Множественное число

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

Правило До После
[=1]0" day"; 0" days" 1 1 days
[=1]0" day"; 0" days" 2 2 days
[=1]0" day"; 0" days" 100 100 days

Условный

Используйте условные выражения для прямой классификации чисел:

Правило До После
[<250]“Small”* 0 ; [>750]“Large”* 0 ; “Medium”* 0 70 Small 70
[<250]“Small”* 0 ; [>750]“Large”* 0 ; “Medium”* 0 656 Medium 656
[<250]“Small”* 0 ; [>750]“Large”* 0 ; “Medium”* 0 923 Large 923

Примечание: это все еще числа под капотом, поэтому вы можете выполнять с ними арифметические операции. «Small», «Medium» и «Large» существуют только в слое формата и не могут быть доступны в формулах. Например, вы не можете использовать COUNTIF для подсчета всех значений с «Large». Для этого вам нужно фактически изменить значение так, чтобы в ячейке было слово «Large», или добавить вспомогательный столбец.

Часть правила со знаком «*» добавляет пробел между словом и числом, чтобы заполнить всю ширину ячейки.

Условный + Цвет

Добавьте к условному примеру цветовые шкалы:

Пример температуры

Комбинируйте условные выражения со смайликами, чтобы превратить числа в шкалу смайлов, как в этом примере температуры:

Правило До После
[>90]🔥🔥🔥;[>50]🙂;❄️;“Нет данных” 37 ❄️
[>90]🔥🔥🔥;[>50]🙂;❄️;“Нет данных” 75 🙂
[>90]🔥🔥🔥;[>50]🙂;❄️;“Нет данных” 110 🔥🔥🔥
[>90]🔥🔥🔥;[>50]🙂;❄️;“Нет данных” Нет данных “Нет данных”

Другие источники

Как добавить подстрочный и надстрочный индексы в Google Таблицы

Документация Google о том, как форматировать числа в Таблицах.

Конструктор пользовательских числовых форматов для Google Таблиц и Excel.

google sheetsпереводы

Таблица ID Стран Similarweb

comments powered by Disqus