код числового формата в excel
Применение пользовательских форматов
Создание пользовательских форматов
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]»любой текст»КодФормата»любой текст»
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Далее рассмотрим коды форматов в зависимости от типа данных.
Числовые форматы
| Символ | Описание применения | Пример формата | До форматирования | После форматирования |
|---|---|---|---|---|
| # | Символ числа. Незначащие нули в начале или конце число не отображаются | ###### | 001234 | 1234 |
| 0 | Символ числа. Обязательное отображение незначащих нулей | 000000 | 1234 | 001234 |
| , | Используется в качестве разделителя целой и дробной части | ####,# | 1234,12 | 1234,1 |
| пробел | Используется в качестве разделителя разрядов | # ###,#0 | 1234,1 | 1 234,10 |
Форматы даты
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.
Форматы времени
Аналогичные форматы есть и для времени.
Текстовые форматы
Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.
Создание пользовательских форматов для категорий значений
Все что мы описали выше применяется к ячейке вне зависимости от ее значения. Однако существует возможность указывать различные форматы, в зависимости от следующих категорий значений:
Для этого мы можем в поле Тип указать следующую конструкцию:
Формат положительных значений ; отрицательных ; нулевых ; текстовых
Соответственно для каждой категории можно применять формат уже описанного нами вида:
[цвет]»любой текст»КодФормата»любой текст»
В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.
Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Рекомендации по настройке числовых форматов
Чтобы создать пользовательский числовой формат, сначала выберите один из встроенных числовых форматов в качестве отправной точки. Затем вы можете изменить любой из разделов его кода.
Числовые форматы могут иметь до четырех разделов кода, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных, нулевых значений и текста (в указанном порядке).
Например, с помощью разделов кода можно создать такой пользовательский формат:
[Синий]# ##0,00_);[Красный](# ##0,00);0,00;»продажи «@
Ниже указаны рекомендации по настройке любого из этих разделов кода числовое формата.
Для отображения перечисленных ниже знаков кавычки добавлять не нужно.
Левая круглая скобка
Левая фигурная скобка
Добавление раздела для текста. Текстовый раздел всегда должен быть последним разделом числового формата. Чтобы вводимый в ячейку текст отображался на экране, в соответствующее место текстового раздела следует добавить знак @. Если текстовый раздел не содержит знака @, вводимый текст отображаться не будет. Чтобы в тексте всегда отображались определенные текстовые символы, заключив дополнительный текст в двойные кавычках ( » » ). Например, «валовой доход для «@
Если в формате нет текстового раздела, то он никак не влияет на нечисловые значения, вводимые в ячейку, к которой он применен. Кроме того, вся ячейка преобразуется в текст.
Добавление пробелов Чтобы создать пробел с шириной, равной ширине символа в числовом формате, добавьте соответствующий знак, предваренный знаком подчеркивания ( _). Например, если за знаком подчеркивания следует правая круглая скобка ( _)), положительные числа будут расположены в одну колонку с отрицательными, заключенными в скобки.
Повторение символов Для заполнения всей ячейки определенным знаком добавьте перед ним звездочку ( *) в числовом формате. Например, для заполнения знаками тире пустой области ячейки справа от числа следует указать 0*—, для добавления нулей перед значением — *0.
Десятичные знаки и значимые цифры. Для форматирования дробей и чисел с запятой можно добавить в раздел кода заполнители разрядов, десятичные разделители и разделители групп разрядов, указанные ниже.
Если число имеет меньше цифр, чем нулей в формате, в этом цифровом месте отображаются незначавные нули. Например, если ввести 8,9и вы хотите, чтобы оно отображалось как 8,90,используйте формат #.00.
Этот цифровой заметель следует тем же правилам, что и 0 (ноль). Однако Excel число не отображает лишние нули, если число, которое вы введите, имеет меньше цифр по обе стороны от десятичной за десятичной части, чем символы # в формате. Например, если в ячейке имеется пользовательский формат #.##,а в ячейке ввести 8,9, отобразилось число 8,9.
Этот цифровой заметель следует тем же правилам, что и 0 (ноль). Однако Excel добавляет пробел для незначачных нулей с обеих сторон десятичной за десятичной замера, чтобы выровнять точки в столбце. Например, формат 0,0? обеспечивает выравнивание запятых для чисел 8,9 и 88,99.
В этом десятичной замеесячести отображается десятичной замер в числе.
Microsoft Excel
трюки • приёмы • решения
Коды пользовательских форматов чисел
В табл. 49.1 кратко описаны коды форматирования, доступные для различных пользовательских (настраиваемых) форматов.
Таблица 49.1. Коды, используемые для создания пользовательских числовых форматов
| Код | Что он делает |
|---|---|
| Основной | Отображает число в общем формате |
| # | Служит в качестве числового заполнителя, который отображает значащие цифры, но не отображает незначащие нули |
| 0 (нуль) | Служит в качестве числового заполнителя, который показывает незначащие нули, если в числе меньше цифр, чем нулей в формате |
| ? | Служит в качестве числового заполнителя, который добавляет пробелы для незначащих нулей на каждой стороне от десятичной точки так, чтобы десятичная точка выравнивалась при форматировании шрифтом фиксированной ширины; также используется для дробей с различным количеством цифр |
| . (точка) | Отображает десятичную точку |
| % | Выводит проценты |
| , (запятая) | Отображает разделитель тысячных |
| Е- Е+ е- е+ | Показывает экспоненциальное значение |
| $ — + / () : пробел | Отображает указанный символ |
| Выводит следующий символ в формате | |
| * (звездочка) | Повторяет следующий символ так, чтобы оказалась занятой вся ширина столбца |
| _ (подчеркивание) | Оставляет пространство, равное ширине следующего символа |
| «текст» | Отображает текст внутри двойных кавычек |
| @ | Служит в качестве текстового заполнителя |
| [цвет] | Отображает символы в заданном цвете и может быть любой из следующих текстовых строк (без учета регистра): Черный, Синий, Голубой, Зеленый, Пурпурный, Красный, Белый или Желтый |
| [ЦВЕТ n] | Отображает соответствующий цвет в цветовой палитре, где n — число от 0 до 56 |
| [условное значение] | Позволяет вам устанавливать собственные критерии для каждого раздела числового формата |
В табл. 49.2 представлены коды, используемые для создания форматов даты и времени.
Доступные числовые форматы в Excel
В Excel числа, содержащиеся в ячейках, можно преобразовать, например, в денежные единицы, проценты, десятичные числа, даты, номера телефонов или номера социального страхования США.
Выделите ячейку или диапазон ячеек.
На вкладке Главная выберите в раскрывающемся списке формат Числовой.
Можно также выбрать один из следующих вариантов:
Нажмите клавиши CTRL+1 и выберите формат Числовой.
Щелкните ячейку или диапазон ячеек правой кнопкой мыши, выберите команду Формат ячеек. и формат Числовой.
Нажмите кнопку вызова диалогового окна 
в группе Число 
и выберите формат Числовой.
Выберите нужный формат.
Числовые форматы
Чтобы просмотреть все доступные числовые форматы, на вкладке Главная в группе Число нажмите кнопку вызова диалогового окна рядом с надписью Число.
При вводе чисел в Excel этот формат используется по умолчанию. В большинстве случаев числа, имеющие формат Общий, отображаются так, как вводятся. Однако если ширины ячейки недостаточно для отображения всего числа, числа в формате Общий округляются. Для чисел, содержащих более 12 знаков, в формате Общий используется научное (экспоненциальное) представление.
Используется как основной для вывода чисел. Можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел.
Используется для денежных значений и выводит рядом с числом обозначение денежной единицы по умолчанию. Можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел.
Используется для отображения денежных значений с выравниванием обозначений денежных единиц и десятичных разделителей в столбце.
Отображает числовые представления даты и времени как значения даты в соответствии с заданным типом и языковым стандартом (местоположением). Форматы даты, начинающиеся со звездочки ( *), соответствуют формату отображения даты и времени, заданному на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
Отображает числовые представления даты и времени как значения времени в соответствии с заданным типом и языковым стандартом (местоположением). Форматы времени, начинающиеся со звездочки ( *), соответствуют формату отображения даты и времени, заданному на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
В этом формате значение ячейки умножается на 100, а результат отображается со знаком процента ( %). Можно задать количество знаков в дробной части.
Отображает число в виде дроби выбранного типа.
Отображает число в экспоненциальном представлении, заменяя часть числа на E+n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n. Например, экспоненциальный формат с двумя знаками в дробной части отображает 12345678901 как 1,23E+10, то есть 1,23, умноженное на 10 в 10-й степени. Можно задать количество знаков в дробной части.
Содержимое ячейки (включая числа) обрабатывается как текст и отображается именно так, как было введено.
Число отображается в виде почтового индекса, телефонного номера или страхового номера (SSN).
Позволяет изменять копию существующего кода числового формата. При этом создается пользовательский числовой формат, добавляемый в список кодов числовых форматов. В зависимости от языковой версии Microsoft Excel можно ввести от 200 до 250 пользовательских числовых форматов. Дополнительные сведения см. в статье Создание и удаление пользовательских числовых форматов.
Вы можете применять к числам различные форматы, чтобы изменить способ их отображения. Форматы изменяют только способ отображения чисел и не влияют на значения. Например, если вы хотите, чтобы число отображалось в виде валюты, щелкните ячейку с числом > Денежный.
Форматы влияют только на отображение чисел, но не значения в ячейках, используемые в вычислениях. Фактическое значение можно увидеть в строка формул.
Ниже представлены доступные числовые форматы и описано, как их можно использовать в Excel в Интернете:
Числовой формат по умолчанию. Если ширины ячейки недостаточно, чтобы отобразить число целиком, оно округляется. Например, 25,76 отображается как 26.
Кроме того, если число состоит из 12 или более цифр, при использовании формата Общий значение отображается в экспоненциальном виде.
Этот формат похож на Общий, но в отличие от него отображает числа с десятичным разделителем, а также отрицательные числа. Ниже представлено несколько примеров отображения чисел в обоих форматах:
Отображает рядом с числами денежный символ. Чтобы задать необходимое количество знаков после запятой, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.
Используется для отображения денежных значений с выравниванием символов валюты и десятичных разделителей в столбце.
Краткий формат даты
В этом формате дата отображается в следующем виде:
Длинный формат даты
В этом формате месяц, день и год отображаются в следующем виде:
В этом формате числовые представления даты и времени отображаются как значения времени.
В этом формате значение ячейки умножается на 100, а результат отображается со знаком процента ( %).
Чтобы задать необходимое количество знаков после запятой, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.
Отображает число в виде дроби. Например, при вводе 0,5 отображается ½.
Отображает числа в экспоненциальной нотации, заменяя часть числа на E+ n, где E (степень) умножает предыдущее число на 10 в степени n. Например, экспоненциальный формат с двумя знаками в дробной части отображает 12345678901 как 1,23E+10, то есть 1,23, умноженное на 10 в 10-й степени. Чтобы задать необходимое количество знаков после запятой, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.
Содержимое ячейки (включая числа) обрабатывается как текст и отображается именно так, как было введено. Дополнительные сведения о форматировании чисел в виде текста.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек)
history 31 марта 2013 г.
Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0
Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.
Точки с запятой разделяют части формата: формат для положительных значений; для отрицательных значений; для нуля. Для описания формата используют специальные символы.
Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа ).
Например, формат 0;\0;\0;\0 позволяет заменить все отрицательные, равные нулю и текстовые значения на 0. Все положительные числа будут отображены как целые числа (с обычным округлением).
В создаваемый числовой формат необязательно включать все части формата (раздела). Если заданы только два раздела, первый из них используется для положительных чисел и нулей, а второй — для отрицательных чисел. Если задан только один раздел, этот формат будут иметь все числа. Если требуется пропустить какой-либо раздел кода и использовать следующий за ним раздел, в коде необходимо оставить точку с запятой, которой завершается пропускаемый раздел.
Рассмотрим пользовательские форматы на конкретных примерах.