Пользовательский ЧИСЛОвой формат в MS 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;"Вы ввели текст" ввести текстовое значение, то будет отображено Вы ввели текст.

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

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

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

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

Отображение

Примечание

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». Можно забыть и перепутать, что в ячейке находится не текст, а не просто число. А это уже возможная причина ошибки. Взвесьте все ЗА и ПРОТИВ перед использованием сложного пользовательского формата.

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

 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.3 (11 оценок)

Комментарии

Елена (не проверено)

Полезная статья. Прочтение ее помогло мне найти решение. Спасибо.

ritaburina

Спасибо, теперь решение найдено!

Яндекс.Метрика