Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек)

В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби -5 1/4 или как (-)5,25 или 5,25- или, вообще в произвольном формате, например, ++(5)руб.###25коп. Рассмотрены также форматы денежных сумм, процентов и экспоненциального представления.


Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0

Пользовательский числовой формат не влияет на вычисления, меняется лишь отображения числа в ячейке. Пользовательский формат можно ввести через диалоговое окно Формат ячеек , вкладка Число , ( все форматы ), нажав CTRL+1 . Сам формат вводите в поле Тип , предварительно все из него удалив.

Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.

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

  • Символ решетка (#) означает любую цифру.
  • Символ пробела в конструкции # ##0 определяет разряд (пробел показывает, что в разряде 3 цифры). В принципе можно было написать # ###, но нуль нужен для отображения 0, когда целая часть равна нулю и есть только дробная. Без нуля (т.е. # ###) число 0,33 будет отражаться как ,33.
  • Следующие 3 символа ,00 (запятая и 00) определяют, как будет отображаться дробная часть. При вводе 3,333 будут отображаться 3,33; при вводе 3,3 – 3,30. Естественно, на вычисления это не повлияет.

Вторая часть формата – для отображения отрицательных чисел. Т.е. можно настроить разные форматы для отражения положительных и отрицательных чисел. Например, при формате # ##0,00;-###0;0 число 123456,3 будет отображаться как 123 456,30, а число -123456,3 как -123456. Если формата убрать минус, то отрицательные числа будут отображаться БЕЗ МИНУСА.

Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа ).

Есть еще и 4 часть – она определяет вывод текста. Т.е. если в ячейку с форматом # ##0,00;-# ##0,00;0;"Вы ввели текст" ввести текстовое значение, то будет отображено Вы ввели текст .

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

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

Рассмотрим пользовательские форматы на конкретных примерах.

Значение в ячейке

Формат ячейки

Отображение

Примечание

123456789

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

123 456 789,00

стандарт

123456789

# ##0

123 456 789

Без дробной части

0,333

# ###,00

,33

без отображения 0 в целой части

-123

# ##0,00; [Красный]-# ##0,00;0

-123,00

Меняем цвет только для отрицательных чисел

123

# ##0,00+;-# ##0,00;0

123,00+

Отображаем символ+ только у положительных значений

123

(плюс)# ##0,00;(минус)# ##0,00;0

(плюс)123,00

Отображаем знак числа словом в скобках

1,25

#" "?/?

1 1/4

дроби

1,255

#" "??/??

1 13/51

дроби

0

# ##0,00;-# ##0,00; O

O

"другой" ноль

любое число, любой текст

;;;

отображено ничего не будет

любое число, любой текст

# ##0,00р.; -# ##0,00р.;; [Красный]Основной

Если число не равное 0, то формат денежный, если 0, то ничего не будет отображено, если текст, то он будет выделен красным

-9 12 0 текст

[Красный]+0"°С"; [Синий]-0"°С"; [Зеленый]0"°С"; @" (нет данных)"

-9°С +12°С 0°С текст (нет данных)

значение температуры

100,3

# ##0,00 " кг"

100,3 кг

наличие текста не влияет на вычисления

100 -50 0 папа

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

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

выводится только тип числа в текстовой форме или слово текст

0,1

0,00%

10,00%

Стандартный процентный формат

0,1

0,00E+00

1,00Е-01

Стандартный экспоненциальный формат

-1

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

(1,00)

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

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

Более сложные примеры пользовательского форматирования приведены в файле примера .

Не могу рекомендовать использовать пользовательский формат слишком часто. Во-первых, на 90% встроенных форматов достаточно, они всем понятны и их легко применять. Во-вторых, как правило, пользовательский формат может существенно изменить отображение значения в ячейке от самого значения (иначе, зачем нужен еще пользовательский формат?)  Например, число 222 можно отобразить в виде «ABCD333-222». Можно забыть и перепутать, что в ячейке находится не текст, а не просто число. А это уже возможная причина ошибки. Взвесьте все ЗА и ПРОТИВ перед использованием сложного пользовательского формата.

СОВЕТ: Вышеуказанные форматы можно также применять в функции ТЕКСТ() - в результате будет получено значение текстовом формате (см. статью Пользовательский числовой формат (Функция ТЕКСТ) ). В статье Пользовательский формат даты и времени рассказано соответственно про форматы Даты и Времеми.


Комментарии
Только для авторизованных пользователей

Аноним, 16 сентября 2018 г.
где находится файл примера? не нашла ссылку
Михаил, 19 сентября 2018 г.
внизу статьи синяя кнопка
Аноним, 11 мая 2020 г.
А можно ли как-то настроить подавление знака при изменении разрядности? Например, я создал формат +0;-0;0 чтобы у чисел всегда был знак. Тогда -0.4 при уменьшении разрядности на один знак превращается в -0, а +0.3 в +0. Тогда как чистый числовой формат превратит оба значения в 0. Но его код не посмотреть, чтобы понять как это сделано...
Михаил, 11 мая 2020 г.
Если я правильно понимаю, то при уменьшении разрядности у -0,4 получается 0. Это математика. Вам нужно, чтобы ячейка обладала "памятью", откуда этот 0 появился: справа или слева. Эту информацию нужно где-то хранить. Ячейка EXCEL не предназначена для этого, кроме того потребуется передать информацию еще и пользовательскому формату. Наверно, проще сделать формулами, а формат можно отображать в соседней ячейке с помощью функции ТЕКСТ(), она тоже поддерживает пользовательский формат. Правда, значение -0 будет уже текстовой строкой, а не числом.
Аноним, 12 мая 2020 г.
Спасибо за ответ. Просто я к тому, что обычный числовой формат успешно убирает знак, когда надо. Наверное, это зашито где-то глубже в коде и в пользовательском формате не применить. Все, чего я хотел - такой же формат как обычный числовой, только чтоб положительные числа тоже были со знаком.
Аноним, 7 сентября 2020 г.
А можно ли 0,0021 перевести в 21? Какая формула?
Михаил, 7 сентября 2020 г.
Может просто умножить на 10000? =0,0021*10000
Константин, 18 сентября 2020 г.
а если нет десятичных в некоторых значениях, то возможно как-то скрыть нули после запятой?
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved