В этой статье мы рассмотрим, как создать свой собственный пользовательский формат в Excel. Часто случается так, что стандартных форматов чисел оказывается недостаточно. Поэтому нужно уметь создавать собственные пользовательские форматы, соответствующие стилю и данным вашей таблицы.
Ранее мы уже рассмотрели, как изменить формат данных ячейки Excel. Но мы должны уметь не только использовать стандартные форматы чисел, но и создавать собственные соответствии со своими нуждами.
Содержание
- Что такое числовой формат в Excel и из чего он состоит?
- Основные правила форматирования чисел в Excel
- Символы и заполнители в числовом формате
- Советы и рекомендации по созданию пользовательского формата в Excel
- Как контролировать количество десятичных знаков
- Особенности округления и разделители тысяч
- Отображение текста и интервалов вместе с числами
- Вывод ведущих нулей
- Пользовательский процентный формат
- Отображение дробей
- Создание экспоненциального формата
- Отрицательные числа и их форматирование
- Вывод нулей в виде тире или пробелов
- Добавление отступов в ячейках
- Изменение цвета шрифта с помощью пользовательских числовых форматов
- Повтор символов и выравнивание
- Числовые форматы с добавлением текста
- Скрытие содержимого ячейки
- Пользовательский формат с условием
- Часто задаваемые вопросы и ответы
- Итоговые рекомендации
Что такое числовой формат в Excel и из чего он состоит?
Чтобы создать формат в excel, необходимо разобраться в правилах его построения. К примеру, рассмотрим следующий пример настраиваемого формата:
Формат состоит из четырёх частей, разделённых точками с запятой. Их порядок следующий:
- Положительные числа. Здесь задаётся отображение с двумя десятичными знаками и разделителем тысяч.
- Отрицательные числа. Обычно формат похож на первый, но с дополнительными круглыми скобками.
- Нули. Здесь можно задать альтернативное представление, например, выводить тире вместо нулевого значения.
- Текстовые значения. Можно настроить особое форматирование для текста, например, задать определённый цвет.
Важно: использовать можно не все четыре раздела. Если указан только один, он применяется ко всем типам данных. При наличии двух разделов первый отвечает за положительные числа и нули, а второй – за отрицательные. Если же заданы три раздела, текст будет выводиться так, как он введён. Чтобы пропустить раздел, достаточно оставить пустое место, поставив точку с запятой.
Дополнительную информацию вы также можете посмотреть здесь: Что такое числовой формат в Excel и как он работает.
Также отметим что в этой статье мы не будем рассматривать как создать собственный формат для даты и времени в Excel. Подробно эти вопросы рассмотрены в этой статье: Формат даты в Excel и Формат времени в Excel.
Основные правила форматирования чисел в Excel
Чтобы правильно создать пользовательский формат чисел в Excel, помните следующие моменты:
- Формат изменяет только вид отображения, а исходное значение в ячейке остаётся неизменным.
- Редактирование встроенного формата всегда создаёт его копию, то есть исходные форматы изменить нельзя.
- Присутствие всех четырёх разделов формата не обязательно:
- Если задан только один раздел, он применяется для любых числовых значений.
- При использовании двух разделов: первый служит для для положительных чисел и нулей, второй – для отрицательных.
- Для текстовых значений специальное форматирование работает только при наличии всех четырёх разделов.
- Можно применять числовой формат «Общий» для указания стандартного оформления, например:
Общий; - Общий; "-"; Общий - Если нужно скрыть определённые значения при помощи форматирования, достаточно оставить раздел пустым, например:
Общий; ; ;Общий. В этом случае нули и отрицательные числа будут видны только в строке формул, но не в ячейке. - Чтобы удалить пользовательский формат, откройте диалоговое окно «Формат ячеек», выберите нужный формат и нажмите «Удалить».
Символы и заполнители в числовом формате
Стандартные символы
Большинство букв и цифр выводятся без изменений, однако некоторые знаки требуют специальных обозначений. К примеру, их нужно брать в кавычки или заключать в квадратные скобки. Ниже вы видите список символов, которые можно использовать напрямую:
| Символ | Назначение |
|---|---|
| $ | Доллар |
| +, – | Знак плюс и минус |
| () | Круглые скобки |
| {} | Фигурные скобки |
| <> | Меньше, больше |
| = | Равно |
| : | Двоеточие |
| ^ | Степенная функция |
| ‘ | Апостроф |
| / | Косая черта |
| ! | Восклицательный знак |
| & | Амперсанд |
| ~ | Тильда |
Примеры применения этих символов вы найдете длалее в этой статье.
Заполнители для цифр и текста
Заполнители – это специальные символы, обозначающие места для цифр или текста:
- 0 – заставляет показывать незначащие нули, если число имеет меньше нужного числа знаков после запятой. Например, формат
#.00всегда выводит два знака после запятой. Если в ячейке записано 5.5, то оно покажется как 5.50, а число 0 – как 0.00.
- # – выводит только значащие цифры, не добавляя лишние нули. Пример: формат
#.##отобразит 1.15 как 1.15, а 1.1 – как 1.1.
- ? – используется для выравнивания чисел. Если на месте отсутствует значащая цифра, вставляется пробел для выравнивания по десятичной точке.
- . (точка) – обозначает десятичную точку. Она всегда появляется в формате, даже если число целое.
- , (запятая) – указывает на разделитель тысяч. Его отображение зависит от региональных настроек. Если запятая используется в конце формата, число округляется до тысяч, а при двойной запятой – до миллионов.
- * (звездочка) – повторяет символ, который идёт сразу после неё, заполняя оставшееся пространство в ячейке.
- _ (подчеркивание) – добавляет пробел, ширина которого определяется следующим за ним символом. Это особенно полезно для выравнивания положительных и отрицательных значений, когда те заключаются в круглые скобки. Например, формат
0_);(0)создаёт отступ справа у положительных чисел. Рис10 - @ – заполнитель, используемый для отображения текста. Пример: cssКопироватьРедактировать
0;0;0;[Синий]@отобразит текст синим цветом.
Также стоит отметить, что поведение заполнителей цифр таково:
- Если число содержит больше знаков после десятичной точки, чем определено заполнителями, оно округляется до требуемого количества знаков. Например, 12.25 при формате
#.0будет показано как 12.3. - Целая часть числа отображается полностью независимо от количества заполнителей слева от десятичной точки.
Советы и рекомендации по созданию пользовательского формата в Excel
Существует огромное количество вариантов настраиваемых числовых форматов. Вот какие силы вы можете при этом использовать:
| Код | Описание |
|---|---|
| Общий | Стандартное числовое оформление |
| # | Заполнитель для значащих цифр, не выводит лишние нули |
| 0 | Обязательный вывод цифры, в том числе незначащих нулей |
| ? | Выравнивает цифры, оставляя место для незначащих нулей без их отображения |
| . | Десятичная точка |
| , | Разделитель тысяч. Если запятая стоит сразу после заполнителя, число умножается на 1000 |
| % | Умножает значение на 100, добавляя знак процента |
| / | Отображает число в виде дроби |
| E | Научный (экспоненциальный) формат |
| [] | Для создания условий отображения, например, изменение цвета в зависимости от условий |
Если требуется вывести специальные знаки (например, символы *, # или %) в реальном виде, то их необходимо предварять обратной косой чертой (\). Например:
| Число | Код | Результат |
|---|---|---|
| 100 | #0 | #100 |
| 100 | *0 | *100 |
| 100 | %0 | %100 |
Также используются специальные последовательности:
- «» (двойные кавычки) позволяют выводить текст, заключённый между ними.
- ** выводит стандартные служебные символы.
- @ показывает текст, введённый в ячейку.
- _ добавляет пробел, ширину которого задаёт следующий символ.
- * повторяет следующий символ до заполнения всей ширины ячейки.
Как контролировать количество десятичных знаков
Для задания числа знаков после десятичной точки используйте соответствующее количество символов «0» или «#». Примеры:
0или#– число отображается как целое.0.0или#.0– один знак после запятой.0.00или#.00– два знака после запятой и так далее.
Разница между 0 и # в том, что если до запятой стоит только #, для чисел меньше единицы начало будет с точки. Например,
- В формате
#.00число 0.25 будет показано как .25, - А с форматом
0.00– как 0.25.
Особенности округления и разделители тысяч
Автоматическое округление:
Excel визуально округляет число, если количество знаков после запятой превышает число заданных заполнителей. При этом дополнительные разряды в целой части отображаются без ограничения. Это влияет только на вид, фактическое значение остаётся неизменным.
Разделитель тысяч:
Чтобы создать формат в excel с разделителем тысяч, включите пробел или запятую в формат. Примеры:
# ###– выводит число без десятичных знаков с разделителем тысяч.# ##0.00– выводит число с двумя десятичными знаками и разделителем тысяч.
Примечание: Вид разделителя определяется региональными настройками Windows: в русских версиях чаще используется пробел, а в английских – запятая.
Также можно масштабировать число до тысяч, миллионов и так далее, если после разделителя нет заполнителя. Например, формат - #.00 с пробелом в конце округлит 5000 до 5.00. При использовании двух запятых – до миллиона.
Отображение текста и интервалов вместе с числами
Чтобы создать формат в Excel, который выведет в одной ячейке текст и число, попробуйте эти рекомендации:
- Добавьте обратную косую черту (
\) перед одиночными символами. - Для текстовых строк используйте двойные кавычки.
Например: - Для вывода тысяч:
#.00 \т\ы\с\. - Для вывода миллионов:
#.000 \млн
Если нужно объединить текст и число, например, добавить слово «Увеличение» для положительных чисел и «Уменьшение» для отрицательных, воспользуйтесь следующим примером:
"Увеличение " #.00; "Уменьшение " -#.00; 0
Совет: чтобы текст не сливался с числом, добавьте пробел внутри кавычек.
Также можно включать в пользовательский формат чисел в Excel стандартные символы вроде валюты, авторского знака или товарного знака, используя их ANSI-коды. Некоторые часто используемые примеры:
| Условное обозначение | Код | Описание |
|---|---|---|
| ™ | Alt+0153 | Товарный знак |
| © | Alt+0169 | Символ авторского права |
| ° | Alt+0176 | Градус |
| ± | Alt+0177 | Знак плюс-минус |
Например, формат для температуры может быть таким:
# "° С"Чтобы добавить к тексту ещё и фиксированный дополнительный текст, поместите его в двойные кавычки рядом с текстовым заполнителем (@). Например, можно предварить текст словом «Доставлено»:
Вывод ведущих нулей
При вводе чисел вроде 005 или 00025 Excel по умолчанию убирает ведущие нули, трактуя их как обычное число. Чтобы сохранить ведущие нули:
- Примените текстовый формат или введите апостроф (‘) перед числом.
- Если нужно, чтобы в столбце всегда было определённое количество цифр, создайте формат, состоящий исключительно из символов 0. Например, формат
000000гарантирует, что число 5 будет отображаться как 000005, а число 50 – как 000050.
Совет: для телефонных номеров или идентификаторов можно использовать один из встроенных специальных форматов, либо создать свой, например:
- Международные семизначные почтовые индексы:
0000000 - Для артикулов с разделителями:
000-00-0000
Пользовательский процентный формат
Чтобы отобразить число в виде процента, добавьте знак % в код формата.
Например,
#.00%Это отобразит число 0.25 как 25%.
Если требуется показать проценты без десятичных знаков – используйте формат #%, а с разделителем тысяч и двумя знаками после запятой –
# ##.00%
Отображение дробей
Дроби в Excel могут выводиться разными способами. Ключевыми моментами при создании пользовательского формата здесь являются:
- Для вывода дробей добавьте косую черту (/) и разделите целую часть пробелом.
- Примеры форматов:
# #/#– дробная часть до 1 знака.# ##/##– дробная часть до 2 знаков.# ###/###– дробь до 3 знаков.###/###– неправильная дробь (числитель больше или равен знаменателю).
Если требуется округлить дробь до определённого знаменателя, укажите его после /, например:
# #/8
Совет: для корректного ввода дробей в ячейке с форматом «Общий» начните с цифры 0, за которой следует пробел, например:
0 4/8Создание экспоненциального формата
Для научного формата включите в код заглавную букву E. Примеры:
00E+00– отображает 1,500,500 как 1,50E+06.#0.0E+0– выведет число 1,500,500 как 1,5E+6.#E+#– результат будет 2E+6.
Отрицательные числа и их форматирование
Чтобы задать пользовательский формат для отрицательных чисел в Excel, необходимо использовать минимум два раздела кода формата: первый для положительных чисел и нулей, второй – для отрицательных.
Например, для отображения отрицательных чисел в круглых скобках можно задать следующий код:
#.00;(#.00)Совет: для выравнивания положительных и отрицательных чисел по десятичной точке добавьте отступ, например:
0.00_);(0.00)Вывод нулей в виде тире или пробелов
Чтобы нули отображались как тире (как в финансовом формате), используйте третий раздел кода:
0.00;(0.00);"-"Если же нужно, чтобы нули не отображались вообще (оставлялись пустыми), можно задать код следующим образом:
Общий; -Общий; ; Общий
Добавление отступов в ячейках
Чтобы содержимое ячейки не «прилипало» к её краям, можно добавить отступ с помощью символа подчеркивания _. Примеры:
- Отступ от левого края:
_(. - Отступ от правого края:
_ ).
Например, формат:
0.00_);(0.00); 0_);_(@обеспечит отступ справа для положительных чисел, чтобы они визуально совпадали с отрицательными, заключёнными в скобки.
Если требуется отступ с обеих сторон, можно использовать:
_(0.00_);_((0.00);_(0_);_(@_)Для большего отступа можно повторить символ подчеркивания.
Изменение цвета шрифта с помощью пользовательских числовых форматов
Одним из простых способов создать пользовательский формат в Excel является изменение цвета шрифта для определённых типов значений. Excel по умолчанию поддерживает 8 основных цветов. Для задания цвета поместите название цвета в квадратные скобки в начале соответствующего раздела. Допустимые значения:
- [Черный], [Синий], [Голубой], [Зеленый], [Фиолетовый], [Красный], [Белый], [Желтый].
Например, чтобы задать цвета для всех типов данных, можно использовать такой код:
[Зеленый]Общий;[Красный]Общий;[Желтый]Общий;[Голубой]ОбщийА для более сложного форматирования, например, вывода валюты, двух десятичных знаков и разделителя тысяч, с нулями в виде тире:
[Синий]# ##0.00\₽;[Красный] -# ##0.00\₽;[Черный] "-";[Фиолетовый]@
Также можно задавать цвета по индексу от 1 до 56, используя запись [ЦветX], где X – нужное число. Пример:
[Цвет1] 0 "▲ ▼"
Повтор символов и выравнивание
Чтобы повторить символ до заполнения ячейки, используйте звездочку *. Например, формат:
#*=повторяет знак равенства после числа, заполняя всю ширину ячейки.
Аналогичным образом можно добавить ведущие нули:
*0#Этот приём также можно использовать для задания жёсткого выравнивания. Например, чтобы выровнять числа по левому краю, можно использовать:
#, ### * ; -# ### * ; 0* ;* @
Встроенный финансовый формат в Excel использует такой метод для создания отступов между знаком минус и числом.
Числовые форматы с добавлением текста
Чтобы вывести текст вместе с числовым значением, заключите текст в двойные кавычки. Примеры:
| Число | Код | Результат |
|---|---|---|
| 10 | 0.0 "шт." | 10.0 шт. |
| 5.5 | 0.0 "литров" | 5.5 литров |
| 300 | 0 "чел." | 300 чел. |
| 95,2 | "Оценка:" 0.0 | Оценка: 95.2 |
| 1 июнь | "Дата:" д мммм | Дата: 1 июнь |
Если текст идёт непосредственно после числового кода, кавычки можно не использовать.
Скрытие содержимого ячейки
Используя собственный пользовательский формат в Excel, можно полностью скрыть содержимое ячейки. Вот код такого формата:
;;;Для возврата к обычному отображению нажмите CTRL + Shift + ~ для применения формата «Общий».
Пользовательский формат с условием
Чтобы создать пользовательский формат в Excel, применяемый только при соблюдении определённого условия, укажите в коде формата оператор сравнения и значение в квадратных скобках. Например:
[Красный][<100];[Зеленый][>=100]Можно комбинировать с числовым форматом, например, для вывода 2 десятичных знаков:
[Красный][<100]0.00;[Зеленый][>=100]0.00
Ещё один интересный пример:
[>=1000]#.000" т";#.0##" кг"При этом если значение меньше 1000, число отображается с единицами измерения «кг», а при значении более 1000 – округляется до тысяч с обозначением «т».
Также можно применять условное форматирование при выводе дробей:
[<1]?.???;# ?/?
Примечание: Для более сложных условий лучше использовать встроенные инструменты условного форматирования Excel.
Часто задаваемые вопросы и ответы
Итоговые рекомендации
Чтобы создать пользовательский формат в Excel под собственные нужды, помните, что:
- Форматы влияют только на отображение данных.
- Используйте заполнители (0, #, ? и т.д.) для контроля количества выводимых цифр.
- Для отделения разных типов значений (положительные, отрицательные, нули, текст) можно использовать до четырех разделов.
- Применяйте специальные символы и управляющие последовательности для задания цвета, отступов и добавления текста.
- Экспериментируйте с условиями отображения, чтобы автоматически изменять формат в зависимости от значений.
Эти подробные объяснения и примеры помогут вам настроить числовой формат по вашим требованиям, будь то вывод процентов, дробей, валют или текстово-числовых комбинаций.