Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек)
31 марта 2013 г.
- Группы статей
- Пользовательский формат
- Условное форматирование
- Пользовательский Формат Числовых значений
В 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». Можно забыть и перепутать, что в ячейке находится не текст, а не просто число. А это уже возможная причина ошибки. Взвесьте все ЗА и ПРОТИВ перед использованием сложного пользовательского формата.
СОВЕТ: Вышеуказанные форматы можно также применять в функции ТЕКСТ() - в результате будет получено значение текстовом формате (см. статью Пользовательский числовой формат (Функция ТЕКСТ) ). В статье Пользовательский формат даты и времени рассказано соответственно про форматы Даты и Времеми.
Комментарии