Мы используем cookie, чтобы сайтом было удобно пользоваться
Пользовательские форматы в Google Таблицах
Пользовательские форматы — мощная и полезная штука, о которой знают не все пользователи. Они доступны и в Excel, и в Google Таблицах.
Ячейки с приростом на скриншоте — это обычные пользовательские форматы, не условное форматирование.
С помощью них можно делать свои таблицы нагляднее, оформлять должным образом количественные данные, не трогая сами значения, и добавлять условия без условного форматирования — например, красить отрицательные числа красным или же добавлять какой-то символ к числам больше тысячи. А еще показывать телефонные номера в нужном формате и многое другое. При этом это все — обычное форматирование, а не условное (которое может существенно тормозить работу документов в Google Таблицах). Давайте разберемся с пользовательскими форматами.
Файл со всеми примерами из статьи
С помощью них можно делать свои таблицы нагляднее, оформлять должным образом количественные данные, не трогая сами значения, и добавлять условия без условного форматирования — например, красить отрицательные числа красным или же добавлять какой-то символ к числам больше тысячи. А еще показывать телефонные номера в нужном формате и многое другое. При этом это все — обычное форматирование, а не условное (которое может существенно тормозить работу документов в Google Таблицах). Давайте разберемся с пользовательскими форматами.
Файл со всеми примерами из статьи
Где они настраиваются?
Формат -> Другие форматы -> Другие форматы чисел
(можно не только «чисел», но и пользовательские форматы «валютный» и «дата и время» — там более очевидный и удобный интерфейс, с ними проще работать).
Мы в основном поговорим про числа, с которыми разобраться сложнее. В конце статьи немного расскажу и о форматах даты-времени.
(можно не только «чисел», но и пользовательские форматы «валютный» и «дата и время» — там более очевидный и удобный интерфейс, с ними проще работать).
Мы в основном поговорим про числа, с которыми разобраться сложнее. В конце статьи немного расскажу и о форматах даты-времени.
Символы, используемые в пользовательских форматах
0 цифра. Незначащий нуль, то есть если в числе меньше цифр, чем в таких нулей в формате, то будут отображаться «пустые» нули.
# цифра. Только значащая, без «пустых» лишних нулей (например, формат #,## покажет 2.3 как 2.3, а 2.37 как 2.37).
? цифра с добавлением пробела вместо незначащих нулей, чтобы десятичные запятые в разных числах были выровнены (ниже будет пример).
. точка=десятичная запятая.
, при применении в самом конце формата одна запятая означает округление до тысяч — пример, разумеется, ниже. При применении между символами числа 0,0 — добавляются разделители разрядов.
% проценты.
*(после звездочки следует символ) заполняет ячейку указанным символом до конца.
[Color] или [ColorN] добавляет цвет шрифта для формата.
\ позволяет отображать «служебные» символы как просто текст — это касается символов * # %.
@ это текстовое значение. Например, формат @@ будет повторять текст в ячейке дважды.
«текст» в кавычках можно указать текст, который будет отображаться в данном формате. Например, формат «Сумма: «0 всегда будет показывать текст «Сумма: «перед числом.
# цифра. Только значащая, без «пустых» лишних нулей (например, формат #,## покажет 2.3 как 2.3, а 2.37 как 2.37).
? цифра с добавлением пробела вместо незначащих нулей, чтобы десятичные запятые в разных числах были выровнены (ниже будет пример).
. точка=десятичная запятая.
, при применении в самом конце формата одна запятая означает округление до тысяч — пример, разумеется, ниже. При применении между символами числа 0,0 — добавляются разделители разрядов.
% проценты.
*(после звездочки следует символ) заполняет ячейку указанным символом до конца.
[Color] или [ColorN] добавляет цвет шрифта для формата.
\ позволяет отображать «служебные» символы как просто текст — это касается символов * # %.
@ это текстовое значение. Например, формат @@ будет повторять текст в ячейке дважды.
«текст» в кавычках можно указать текст, который будет отображаться в данном формате. Например, формат «Сумма: «0 всегда будет показывать текст «Сумма: «перед числом.
Отдельные форматы для положительных, отрицательных чисел, нуля и текста
Можно предложить отдельные форматы для положительных чисел, отрицательных чисел, нуля и текста.
Перечислять их нужно через точку с запятой в этом порядке. Указывать все не обязательно. Можно прописать только два формата — для положительных и отрицательных, например.
Так, например, этот формат не будет отображать нули вообще, а отрицательные числа будут в скобках и без знака минус:
0;(0);
Перечислять их нужно через точку с запятой в этом порядке. Указывать все не обязательно. Можно прописать только два формата — для положительных и отрицательных, например.
Так, например, этот формат не будет отображать нули вообще, а отрицательные числа будут в скобках и без знака минус:
0;(0);
А такой формат вообще скроет любые данные (ведь для каждого из четырех вариантов мы указали пустой формат):
;;;
;;;
Видите число? А оно там есть.
Цвет в пользовательских форматах
Чтобы добавить к формату цвет, нужно указать его название на английском в квадратных скобках в начале формата.
[Green]0;[Red]-0;0;[Blue]@
это зеленый цвет для положительных чисел, красный для отрицательных, синий для текста.
[Green]0;[Red]-0;0;[Blue]@
это зеленый цвет для положительных чисел, красный для отрицательных, синий для текста.
Понятно, что таким образом вариантов цветов можно указать не так много.
Для большего разнообразия пользуйтесь кодом цвета в формате [ColorN]. Код N можно посмотреть в этой таблице — спасибо за нее порталу Excel Jet:
Для большего разнообразия пользуйтесь кодом цвета в формате [ColorN]. Код N можно посмотреть в этой таблице — спасибо за нее порталу Excel Jet:
Условие на величину числа
В пользовательских форматах можно добавлять условия: [условие] формат; [условие] формат; формат для остальных случаев. В таком варианте уже нельзя будет добавлять четыре разных варианта для положительных-отрицательных-нуля-текста. Больше трех условий добавить не получится — два явных и одно для остальных случаев.
Давайте рассмотрим простой пример, а в следующем пункте будет более интересный. Если вы хотите в случае единицы отображать «1 штука», а в остальных случаях — «2 штуки», «3 штуки» и т. д. — нужен следующий формат:
[=1]0 «штука»;0 «штуки»
Давайте рассмотрим простой пример, а в следующем пункте будет более интересный. Если вы хотите в случае единицы отображать «1 штука», а в остальных случаях — «2 штуки», «3 штуки» и т. д. — нужен следующий формат:
[=1]0 «штука»;0 «штуки»
Напомню, формат на значение никак не влияет, истинное значение ячейки всегда можно видеть в строке формул. Наличие слова «Штуки» или любого другого текста в формате не повлияет на возможность проведения арифметических операций с числами. И не добавит никакого текста в сами ячейки, в их значения.
Добавим огня (символы в пользовательских форматах)
К пользовательским форматам можно добавлять символы. Например, огонь — как на скриншоте:
Как сделать такой формат?
Нужен следующий код:
[<2000][Red]#,# * 🔥;[>3000][Green]#,# * 🔥🔥🔥;[Blue]#,# * 🔥🔥
Расшифруем его:
[<2000] - условия. У нас есть формат для чисел меньше 2000, для чисел больше 3000 и для остальных случаев.
[Red] - цвет шрифта
#,# - числовой формат с разделителями разрядов
🔥 - текст, который мы хотим видеть в ячейке помимо числа (вы можете взять любой другой символ или написать разные слова для разных случаев)
* - выравнивает число слева, а текст справа
Нужен следующий код:
[<2000][Red]#,# * 🔥;[>3000][Green]#,# * 🔥🔥🔥;[Blue]#,# * 🔥🔥
Расшифруем его:
[<2000] - условия. У нас есть формат для чисел меньше 2000, для чисел больше 3000 и для остальных случаев.
[Red] - цвет шрифта
#,# - числовой формат с разделителями разрядов
🔥 - текст, который мы хотим видеть в ячейке помимо числа (вы можете взять любой другой символ или написать разные слова для разных случаев)
* - выравнивает число слева, а текст справа
Незначащие нули
Если вы хотите отображать незначащие нули — введите столько нулей, сколько должно быть цифр в ячейке. Это подходит для индексов и артикулов.
Например, вам нужно отображать числа как артикулы из семи знаков — 874 как 874, 9872 как 9 872 и так далее.
Для этого случая подойдет формат:
0
Пример с 10 знаками:
Например, вам нужно отображать числа как артикулы из семи знаков — 874 как 874, 9872 как 9 872 и так далее.
Для этого случая подойдет формат:
0
Пример с 10 знаками:
Пользовательские форматы для номеров телефонов
Чтобы отображать телефонные номера, введенные просто как последовательность цифр, в нормальном виде — нужны пользовательские форматы.
Давайте посмотрим, как ими воспользоваться.
Формат (Alt+O) -> Числа (N) -> Другие форматы — > Другие форматы чисел
А дальше можно вводить формат. В самом простом виде просто вводим нули для цифр и знаки, которые хотим отображать:
0(000)000−00−00
Но такой формат не будет работать для номеров вида 9 101 234 567, 1 234 567.
Только для 79 161 234 567 или 89 161 234 567.
Поэтому можно пойти дальше и вспомнить, что есть возможность задавать условия в пользовательских форматах.
Так, можно предусмотреть отдельный формат для чисел меньше 9 999 999 — без кода:
[<9 999 999]000−00−00;0(000)000−00−00
Такой вариант корректно отобразит и номера из 11 цифр, и номера из 7.
Но все еще не справится с номером из 10 цифр — как 9 101 234 567.
Так что можно сделать еще один шаг и добавить решетку до скобок — на ее месте цифра будет отображаться только при ее наличии:
[<9 999 999]000−00−00;#(000)000−00−00
Теперь у нас предусмотрены три варианта.
Давайте посмотрим, как ими воспользоваться.
Формат (Alt+O) -> Числа (N) -> Другие форматы — > Другие форматы чисел
А дальше можно вводить формат. В самом простом виде просто вводим нули для цифр и знаки, которые хотим отображать:
0(000)000−00−00
Но такой формат не будет работать для номеров вида 9 101 234 567, 1 234 567.
Только для 79 161 234 567 или 89 161 234 567.
Поэтому можно пойти дальше и вспомнить, что есть возможность задавать условия в пользовательских форматах.
Так, можно предусмотреть отдельный формат для чисел меньше 9 999 999 — без кода:
[<9 999 999]000−00−00;0(000)000−00−00
Такой вариант корректно отобразит и номера из 11 цифр, и номера из 7.
Но все еще не справится с номером из 10 цифр — как 9 101 234 567.
Так что можно сделать еще один шаг и добавить решетку до скобок — на ее месте цифра будет отображаться только при ее наличии:
[<9 999 999]000−00−00;#(000)000−00−00
Теперь у нас предусмотрены три варианта.
Пользовательские форматы: округление до тысяч и миллионов
Продолжим исследовать бездонный мир пользовательских форматов и поговорим про округление.
Достаточно одной запятой в конце числового формата, чтобы округлить число до тысяч:
0,
Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
0.00,
Сработает округление и для форматов с разделителями разрядов:
#,##0,
(То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. — то в нем появятся разделители разрядов)
Можно добавить надпись после числа — чтобы не запутаться и точно понимать, что здесь числа в тысячах:
#,##0, тыс
Помня о том, что мы можем задавать отдельные форматы (через точку с запятой) для положительных и отрицательных чисел, можно задать формат с округлением для положительных чисел, а отрицательные показывать в обычном формате:
#,##0,;#,##0
И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
#,##0, — миллионы с разделителями разрядов
0, — миллиарды без разделителей разрядов
Достаточно одной запятой в конце числового формата, чтобы округлить число до тысяч:
0,
Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
0.00,
Сработает округление и для форматов с разделителями разрядов:
#,##0,
(То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. — то в нем появятся разделители разрядов)
Можно добавить надпись после числа — чтобы не запутаться и точно понимать, что здесь числа в тысячах:
#,##0, тыс
Помня о том, что мы можем задавать отдельные форматы (через точку с запятой) для положительных и отрицательных чисел, можно задать формат с округлением для положительных чисел, а отрицательные показывать в обычном формате:
#,##0,;#,##0
И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
#,##0, — миллионы с разделителями разрядов
0, — миллиарды без разделителей разрядов
Знак вопроса для выравнивания по десятичной запятой
Знаки вопроса после запятой — это значащее число после десятичной запятой, и при этом вместо незначащих будет появляться пробел. Таким образом, числа будут выравниваться по запятой, но пустые нули не будут отображаться. В следующем формате будет до трех знаков после запятой:
0???
0???
Заполнение ячейки символом
Если поставить после звездочки символ, то вся ячейка до упора будет заполняться этим символом. В следующем варианте у нас будет число, а все свободное пространство заполнится дефисами:
*-0
*-0
Функция ТЕКСТ / TEXT
С помощью этой функции можно отобразить значение из ячейки в любом нужном формате, не залезая в меню форматов. Особенно полезно, когда мы соединяем с помощью амперсанда разный текст из нескольких ячеек — в таких случаях форматирование теряется, остаются только значения.
Синтаксис у функции простой — значение (ссылка на ячейку, как правило); формат (в кавычках, так как это текстовое значение).
Синтаксис у функции простой — значение (ссылка на ячейку, как правило); формат (в кавычках, так как это текстовое значение).
Вот пример задачи, для которой ТЕКСТ полезна — составляем целую фразу из разных значений формулой, и для формулы прироста указываем процентный формат без знака минус в отрицательных случаях:
Пользовательские форматы даты и времени
Как я уже писал, с ними чуть проще. Есть встроенный мини-редактор, в котором можно «собрать» нужный формат в конструкторе. Найти его можно по адресу:
Формат -> Другие форматы -> Другие форматы даты и времени
Здесь щелкаем на стрелку и там появляются все возможные варианты. «Продолжительность» подойдет для времени больше суток — например, если у вас событие, которое длится 30 часов.
Формат -> Другие форматы -> Другие форматы даты и времени
Здесь щелкаем на стрелку и там появляются все возможные варианты. «Продолжительность» подойдет для времени больше суток — например, если у вас событие, которое длится 30 часов.
Выбираем что-то, например «День», он появляется слева в окне формата и далее можно на него щелкнуть и выбрать нужный тип отображения.
В итоге можно собирать самые разные форматы — на скриншоте во всех ячейках одна и та же функция ТДАТА / NOW, возвращающая текущие время и дату:
Меню "Другие форматы даты и времени" - не единственный вариант создания форматов даты. Можно использовать и коды, которые пригодятся для той же функции ТЕКСТ / TEXT и которые можно использовать в числовых форматах.
Используются там следующие символы:
S - секунда, M - минута, H - час (в случае с продолжительностью - в квадратных скобках)
D - день, M - месяц, Y - год
В зависимости от количества символов меняется формат. То есть D - это дата без нуля впереди, DD - с нулем, DDD - краткий формат дня недели, DDDD - полное название дня недели.
YY - краткий формат года (20), YYYY - полный (2020).
Файл со всеми примерами из статьи
Источники - что почитать по теме
Джон Уокенбах - "Формулы в Microsoft Excel"
Excel custom number formats
Используются там следующие символы:
S - секунда, M - минута, H - час (в случае с продолжительностью - в квадратных скобках)
D - день, M - месяц, Y - год
В зависимости от количества символов меняется формат. То есть D - это дата без нуля впереди, DD - с нулем, DDD - краткий формат дня недели, DDDD - полное название дня недели.
YY - краткий формат года (20), YYYY - полный (2020).
Файл со всеми примерами из статьи
Источники - что почитать по теме
Джон Уокенбах - "Формулы в Microsoft Excel"
Excel custom number formats
Другие статьи