Всем известно, что данные в ячейке могут представлять собой число, текст или значение ошибки. Как отображаются те или иные данные в ячейке, зависит от примененного формата. В Excel существует множество встроенных форматов, с помощью которых мы можем видеть числа в денежном представлении или экспоненциальном, в виде даты или дроби. Форматы доступны в диалоговом окне Формат ячейки на вкладке Число. Но всё же иногда встроенных форматов не хватает, и тогда Excel предоставляет пользователю возможность создать свой формат. В этой статье рассматриваются основные правила построения пользовательского формата, и приводятся примеры различных форматов.
Код
Описание
Пример формата
Содержимое ячейки
Результат
Основной
общий числовой формат, числа отображаются так, как были введены
Основной
1234,56 78 0,9
1234,56 78 0,9
#
любая цифра необязательная, лишние цифры после запятой округляются
#,#
1234,56 78 0,9
1234,6 78, ,9
0
любая цифра обязательная. Недостающие цифры заменяются нулём, как в целой части, так и в дробной, лишние цифры после запятой округляются
000,0
1234,56 78 0,9
1234,6 078,0 000,9
?
любая цифра обязательная. Недостающие цифры заменяются пробелом (используется для выравнивания чисел по десятичной запятой), лишние цифры после запятой округляются
.
1234,56 78 0,9 -25 -3,4
1234,6 78, ,9 — 25, — 3,4
%
умножает число на 100 и добавляет знак % после него
0,0%
0,9
90,0%
пробел
используется как разделитель разрядов, если стоит в середине формата, и уменьшает число в 1000 раз, если стоит в конце формата
# ##0,0 # ##0,0 (пробел) # ##0,0 (два пробела)
123456,7
123 456,7 123,5 0,1
E+ E-
используется для экспоненциальной записи числа
0,0E+00 0E+0
123456,7
1,2E+05 1E+5
@
используется для вывода текста
» » (кавычки)
выводит текст, заключенный в кавычки
$- + / ( ) : пробел
эти символы отображаются в ячейке, даже если записаны в формате без кавычек
\
используется для экранирования, т.е. для отображения следующего за ним символа без изменений
*
заполняет ячейку символом, который следует за *
*-
1234,56
————
_ (подчёркивание)
делает символ, следующий за ним невидимым, но оставляет свободное место, равное ширине символа. Используется для выравнивания чисел по десятичной запятой, если, например, отрицательное число заключается в скобки
# ##0,00_);(# ##0,00);-
1234,5 -1234,5
1 234,50 (1 234,50)
Использование в числовом формате произвольного текста
Формат
Содержимое ячейки
Результат
Примечание
# ##0,00″ кг»
456
456,00 кг
# ##0,00 » т»
456
0,46 т
пробел после нуля уменьшает число в 1000 раз
0″ кг»,000″ г»
456,123
456 кг,123 г
«ABCD-«#
456
ABCD-456
«Приход»*.0,00;»Расход»*.0,00;-
456 -456 0
Приход…..456,00 Расход…..456,00 —
сочетание *. (звёздочка-точка) обеспечивает разделение текста и числа точками, т.е. т екст располагается по левому краю ячейки, число располагается по правому краю, а все пространство между ними заполняется точками
«Избыток»;»Недостаток»;»Баланс»;
456 -456 0 текст
Избыток Недостаток Баланс
так как четвёртый текстовый раздел пустой, а точка с запятой для него поставлена, то текст отображаться в ячейке не будет
Если цвет шрифта установить в цвет фона ячейки (белый по белому), то при таком формате скроются значения ошибок
0,0;-0,0;-;[Цвет46]
456 -456 0 текст
456 -456 — текст
Выведет текст цветом 46 (оранжевым)
Пользовательские форматы Даты/Времени
В большинстве случаев для форматирования ячеек, содержащих дату/время бывает достаточно встроенных форматов Дата и Время. Эти форматы составляются из символов Д (день), М (месяц), Г (год), ч (часы), м (минуты), с (секунды). Различия заключаются в разном количестве этих символов в записи формата, а также в разделителях. В се буквы, кроме «м» можно писать как строчными, так и прописными буквами
Формат
Результат для 09.02.2014 9:05:00
Примечание
Д ДД ДДД ДДДД
9 09 Вс воскресенье
Использование в формате различных символов
Символы, отсутствующие на клавиатуре, можно ввести в запись формата путём копирования/вставки из таблицы символов или с помощью сочетания клавиши Alt и числового кода символа, набираемого на цифровой клавиатуре.
Формат
Содержимое ячейки
Результат
Примечание
0,0°
Символ градуса ° можно ввести с помощью набора на клавиатуре Alt+0176 (цифры набираются на цифровой клавиатуре) или через буфер обмена из таблицы символов
[ч]°мм’сс»
55:45:30
55°45’30»
Такой формат можно применять для отображения величины угла (градусы, минуты, секунды). Так как в угловой мере в градусе 60 минут, а в минуте 60 секунд (как и при измерении времени), вычисления будут производиться корректно. После сс ставятся не кавычки, а два апострофа
# ##0,0″ м²»
45,6 13,58
45,6 м² 13,6 м²
Верхний индекс ² можно взять из таблицы символов или получить с помощью Alt+0178 (правда, для этого должна быть включена английская раскладка)
[Зеленый]↑;[Красный]↓;[Черный]↔
5 -5 0
Символы стрелок берутся из таблицы символов
[Красный][=3];[Желтый][=2];[Зеленый];
1 2 3
Символ (круг) берётся из таблицы символов, в данном случае шрифт Wingdings 2, но в отличие от стрелок эти символы при вставке в запись формата не отображаются. Но они там есть.
Несколько слов о текстовом формате
Если для ячейки установлен текстовый формат @, то всё, что вводится в ячейку, будет восприниматься как текст, будь то число, дата или формула. Иногда это создаёт проблемы при использовании этих значений в формулах
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 выдает «нет данных».
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Пользовательский ЧИСЛОвой формат в 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. Все положительные числа будут отображены как целые числа (с обычным округлением).
В создаваемый числовой формат необязательно включать все части формата (раздела). Если заданы только два раздела, первый из них используется для положительных чисел и нулей, а второй — для отрицательных чисел. Если задан только один раздел, этот формат будут иметь все числа. Если требуется пропустить какой-либо раздел кода и использовать следующий за ним раздел, в коде необходимо оставить точку с запятой, которой завершается пропускаемый раздел.
Рассмотрим пользовательские форматы на конкретных примерах.
В 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.
в группе Число 
и выберите формат Числовой.
