Как создать пользовательский формат в excel

Как создать пользовательский формат в excel

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
Информация о сайте
Развите сайта
Приемы работы
Инструменты и настройки
Интеграция
Готовые решения
Вопросы и решения
Работа и общение
Работа форума и сайта
Библиотека
Книги по Excel
Книги по VBA
Видеосалон
Справочники
Разработчик
КАТЕГОРИИ РАЗДЕЛА

Приёмы работы с книгами, листами, диапазонами, ячейками [6]

Приёмы работы с формулами [14]

Настройки Excel [3]

Инструменты Excel [4]

Интеграция Excel с другими приложениями [5]

Форматирование [1]

Выпадающие списки [2]

Примечания [1]

Сводные таблицы [1]

Гиперссылки [1]

Excel и интернет [1]

Excel для Windows и Excel для Mac OS [2]

Пользовательский формат

Всем известно, что данные в ячейке могут представлять собой число, текст или значение ошибки. Как отображаются те или иные данные в ячейке, зависит от примененного формата. В 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 текст	Избыток Недостаток Баланс	так как четвёртый текстовый раздел пустой, а точка с запятой для него поставлена, то текст отображаться в ячейке не будет

Задание в формате цвета шрифта

Цвет шрифта может задаваться в любом из четырёх разделов формата. Название цвета указывается в квадратных скобках и должно быть первым в разделе. Допускается использовать один из восьми цветов: чёрный (black), зеленый (green), белый (white), синий (blue), фиолетовый (magenta), желтый (yellow), голубой (cyan), красный (red), а также задавать цвет индексом цвет n, где n может принимать значения от 1 до 56, правда 10 из них повторяются по два раза. По умолчанию используется чёрный цвет.

Формат	Содержимое ячейки	Результат	Примечание
[Синий]# ##0,00;[Красный]-# ##0,00	456 -456	456,00 -456,00
# ##0,00″ кг»;[Красный]»Ошибка!»	456 -456	456 кг Ошибка!
[Красный]»Избыток»;[Синий]»Недостаток»;[Зеленый]»Баланс»;	456 -456 0	Избыток Недостаток Баланс
[Черный]Основной	456 #ЗНАЧ!	456	Если цвет шрифта установить в цвет фона ячейки (белый по белому), то при таком формате скроются значения ошибок
0,0;-0,0;-;[Цвет46]	456 -456 0 текст	456 -456 — текст	Выведет текст цветом 46 (оранжевым)

Создание и применение пользовательского числового формата

Если встроенный числовой формат не подходит, можно создать новый числовой формат на основе существующего и добавить его в список пользовательских числовых форматов. Например, при создании электронной таблицы, содержащей сведения о клиенте, можно создать числовой формат для телефонных номеров. Затем пользовательский числовой формат можно применить к строке чисел в ячейке, чтобы отформатировать их в виде телефонного номера.

Важно: Пользовательские числовые форматы влияют только на способ отображения числа и не влияют на его базовое значение. Пользовательские числовые форматы хранятся в активной книге и недоступны для открываемых новых книг.

Создание пользовательского числового формата

1. На вкладке Главная в группе Число нажмите кнопку Другие числимы в нижней части списка Числовом формате .
2. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
3. В списке Тип выберите встроенный формат, больше всего похожий на тот, который требуется создать. Например: 0,00. Выбранный формат отображается в поле Тип.
4. В поле Тип измените коды числового формата, чтобы создать точно такой формат, как нужно. Например: 000-000-0000. Внесенные изменения не повлияют на встроенный формат. Вместо этого будет создан новый пользовательский числовой формат.
5. По завершении нажмите кнопку ОК.

Применение пользовательского числового формата

1. Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
2. На вкладке Главная в группе Число нажмите кнопку Другие числимы в нижней части списка Числовом формате .
3. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
4. В нижней части списка Тип выберите встроенный формат, который вы только что создали. Например: 000-000-0000. Выбранный формат отображается в поле Тип.
5. Нажмите кнопку ОК.

Удаление пользовательского числового формата

1. На вкладке Главная в группе Число нажмите кнопку Другие числимы в нижней части списка Числовом формате .
2. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
3. В списке Тип выберите пользовательский числовой формат и нажмите кнопку Удалить.

• Встроенные числовые форматы удалить невозможно.
• Все ячейки в книге, к которым был применен удаленный пользовательский формат, будут отображаться с использованием формата «Общий», заданного по умолчанию.

Создание пользовательского числового формата

1. На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
2. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
3. В списке Тип выберите встроенный формат, больше всего похожий на тот, который требуется создать. Например: 0,00. Выбранный формат отображается в поле Тип.
4. В поле Тип измените коды числового формата, чтобы создать точно такой формат, как нужно. Например: 000-000-0000. Внесенные изменения не повлияют на встроенный формат. Вместо этого будет создан новый пользовательский числовой формат.
5. По завершении нажмите кнопку ОК.

Применение пользовательского числового формата

1. Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
2. На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
3. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
4. В нижней части списка Тип выберите встроенный формат, который вы только что создали. Например: 000-000-0000. Выбранный формат отображается в поле Тип.
5. Нажмите кнопку ОК.

Удаление пользовательского числового формата

1. На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
2. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
3. В списке Тип выберите пользовательский числовой формат и нажмите кнопку Удалить.

• Встроенные числовые форматы удалить невозможно.
• Все ячейки в книге, к которым был применен удаленный пользовательский формат, будут отображаться с использованием формата «Общий», заданного по умолчанию.

Создание пользовательского числового формата

Создайте и настройте пользовательский числовой формат для отображения чисел в виде процентов, денежных единиц, дат и т. д. Чтобы узнать, как изменять коды числовых форматов, ознакомьтесь со статьей Рекомендации по настройке числовых форматов.

1. Выберите числовые данные.
2. На вкладке Главная в группе Число выберите маленькую стрелку, чтобы открыть диалоговое окно.
3. Выберите пункт (все форматы).
4. В списке Тип выберите существующий формат или введите в поле новый.
5. Чтобы добавить текст в числовой формат, сделайте следующее:
   • Введите текст в кавычках.
   • Добавьте пробел, чтобы отделить текст от числа.
6. Нажмите кнопку ОК.

В приложении Excel числа можно отобразить в различных форматах (например в процентном, денежном или в формате даты). Если встроенные форматы не подходят, может потребоваться создать пользовательский числовой формат.

Вы не можете создавать пользовательские форматы в Excel в Интернете, но если у вас есть настольное приложение Excel, вы можете щелкнуть кнопку Открыть в Excel, чтобы открыть книгу и создать их. Дополнительные сведения см. в статье Создание пользовательского числового формата.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Пользовательские форматы ячеек в Excel

Сегодня хотелось бы поговорить о пользовательском формате в Excel. Если необходимо, то вы можете изменить формат ячеек по-своему усмотрению.

Например, давайте рассмотрим такой пример, у нас есть баланс на какие-то даты. Баланс может быть как отрицательный так и положительный. Нам необходимо, чтобы плюсовой баланс был закрашен синим цветом и со знаком плюс, а отрицательный в красный цвет со знаком минус. По умолчанию, если мы введем положительное число в ячейку с плюсом, то плюс будет убран автоматически программой Excel.

Давайте рассмотрим на предыдущем примере как работают пользовательские форматы.

Выделим весь диапазон с цифрами, далее необходимо нажать на правую кнопку мыши выбрать «Формат ячеек» (Format Cells) , и на вкладке «Число» (Number) выберете пункт «Все форматы» (Custom) .

Далее в поле «Тип» введем следующий текст

[Синий]+0,0 ; [Красный]-0,0 ; 0,0

Нажимаем «ОК» и получим желаемый результат.

Ну а теперь, давайте подробнее рассмотрим данный синтаксис, чтобы вы могли делать свои собственные пользовательские форматы чисел.

В данное поле можно ввести 4 формата через «;» (точка с запятой) в следующей последовательности:

1. положительное число;
2. отрицательное число;
3. ноль;
4. если формат — текст;

Три последних — необязательные форматы, их, как правило, можно вообще не писать. В некоторых случаях пишется только один формат.

Например, чтобы отображать номер телефона в формате +7(905)222-35-96
Необходимо написать следующий формат +7(#)000-00-00
в данном случае введенное число 79052223596 будет отображено как +7(905)222-35-96

Если указать квадратные скобки («[]«) перед форматом, то в них можно указать цвет текста, всего поддерживается около 7 основных цветов. В этом есть некоторые неудобство, так, например, зеленый цвет слишком яркий и сливается с фоном и оттенок поменять нельзя, поэтому я выбрал синий цвет.

В нашем примере для положительного формата числа я написал:
[Синий]+0,0
где [Синий] — это цвет ячейки
«+» — это необходимость всегда вставлять знак «+» в положительных числах
0,0 — это формат самого числа. 0 (ноль) — это обязательное знакоместо, которое показывает, что в данном месте будет обязательно введено какое-то целое число, потом запятая и после запятой будет только одна цифра (один разряд). Например, если число было бы 256,253 — то оно будет отображено как +256,2 (256 — как целое число и одна двойка после запятой).

Еще пример, если указать формат «000,00», то число 25,2365 будет отражено как 025,23 и так далее.

Далее мы указали формат отрицательного числа
[Красный]-0,0
Тут аналогично [Красный] — это цвет числа, «—» — указать минус перед отрицательным числом в формате 0,0 (одна цифра после запятой)

Можно использовать (пробел), чтобы разделитель группы разрядов по три между тысячами, миллионами, миллиардами и так далее.

# (решетка) — в примере выше, это одно необязательное знакоместо, практически то же самое, что и ноль, но в случае отсутствия числа ничего выводиться не будет, а если бы был ноль, то был бы отражен ноль.

Далее в нашем примере для нулевого значения мы указали формат «0,0«, а для текста просто пропустили.

Надеюсь, теперь вас стало понятно как сделать пользовательский формат ячеек.

Давайте рассмотрим еще парочку примеров для закрепления пройденного материала

Например, вы указываете вес товара в «кг«, чтобы во-первых не писать во всех числах «кг», а так же, чтобы сохранить числовой формат ячейки вы можете создать пользовательский формат.

0,00″ кг»

В данном случае введено число 25,2336 будет отражено как 25,23 кг

Любой текст, в том числе пробел и другие символы в числовом пользовательском формате должны вводиться в кавычках для корректной работы.

Еще пример отображения дюймов в ячейке
Введите следующую маску формата

0,0″»» (два апострофа в кавычках)

Как отобразить логические значения? Введем формат

«Да»;»Да»;»Нет»

в данном случае, любое положительное число и отрицательное значение будет отражено как «Да», а ноль будет отражен как «Нет»

Вы так же можете указать любой символ в формате, например если нам необходимо указать стрелки вверх при положительной динамике и вниз — при отрицательной, то можно указать данные символы в формате. Например, вот так:

[Синий]↑+0,0%;[Красный] ↓-0,0%;0,0

Специальные символы вы можете найти в Excel на вкладке «Вставка», панель «Символы», значок «Символ»

Если вам понравилась статья, то, пожалуйста ставьте лайки («мне нравится») и до встречи в новых уроках.