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

Здравствуйте, друзья. В статье о форматировании данных в Эксель мы рассмотрели, какие форматы существуют и как их применить. Но что, если их недостаточно? Если нам нужно самим, вручную задать, как должны отображаться значения в ячейке? Конечно, есть решение! В этом посте учимся делать пользовательские форматы и значительно упрощать себе жизнь. Представьте, Вы абсолютно точно говорите программе, как отображать данные, а она безотказно выполняет. Это то, к чему мы с Вами стремимся – полный контроль и разумная автоматизация!
Если Вы внимательно читали статью о форматировании чисел, там я рассказывал все способы вызова меню «Формат ячеек». Самый простой из них – выделить ячейки для форматирования и нажать Ctrl+1. На вкладке «Число» Вы увидите все доступные форматы данных. Последней строкой здесь будет «все форматы». Как раз в этом пункте меню, в строке «Тип» записываются пользовательские форматы.

Пользовательский формат
Как это работает? С помощью специальных символов, которые я опишу ниже, мы говорим программе, как отображать данные. Более того, мы можем через точку с запятой задать форматы для:
1. Положительного числа
2. Отрицательного числа
3. Нуля
4. Текста
В зависимости о того, сколько вариантов формата мы зададим, Эксель по-разному их трактует:
1. Задаем один формат – он применится для всех видов значений;
2. Два формата – первый применится для положительных и нулевых значений, второй – для отрицательных;
3. Три формата: первый – для отрицательных, второй – для положительных, третий – для нулевых;
4. Четыре формата: последовательно – положительные, отрицательные, нулевые, текст.
А вот какие служебные символы используются для форматирования данных:

Символ Обозначение
Общий Применяется формат «Общий»
# Значащее числовое значение. Незначащие числа не отображаются
0 (ноль) Значащее число. Несуществующие разряды заменяются нулями слева в старших разрядах
? Значащее число. Несуществующие разряды заменяются пробелами слева в старших разрядах
, Десятичный разделитель (запятая)
Пробел Разделяет тысячные разряды
% Проценты
\ Повторяет символы, следующие за ним
* Повторяет символы, следующие за ним до конца ячейки
_ (подчеркивание) Пропуск на ширину следующего за ним символа
“” Вставляет текст, заданный в кавычках
@ Текст
[цвет] Выберите цвет текста из списка: черный, синий, голубой, зеленый, фиолетовый, красный, белый, желтый
[цвет n] n – номер цвета из палитры Excel

Так же, есть служебные символы форматирования дат:

Символ Обозначение
ГГ Год двузначным числом
ГГГГ Год четырехзначным числом
М Номер месяца без первого нуля
ММ Номер месяца с первым нулем
МММ Месяц аббревиатурой
ММММ Месяц (полное название)
МММММ Первая буква месяца
Д День месяца без первого нуля
ДД День месяца с первым нулем
ДДД День недели аббревиатурой
ДДДД День недели (полное название)
ч Час без первого нуля
чч Час с первым нулем
м Минуты без первого нуля
мм Минуты с первым нулем
с Секунды без первого нуля
сс Секунды с первым нулем

Здесь я привел самые полезные служебные символы, полный перечень можно найти в справке Excel.
Повторюсь, строка с форматом записывается в поле «Тип» меню «Формат ячеек», вкладка «Все форматы»
Комбинируя обычные и служебные символы между собой, можно добиться того вида, который нужен. Давайте посмотрим как это работает на нескольких примерах:

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

Пользовательские форматы – действительно простая тема, но очень мощная и полезная в работе. Напомню, что при установке формата, значение в ячейке не изменяется. Изменяется лишь способ его отображения.
Пользовательский формат сохраняется вместе с рабочей книгой. Чтобы перенести его в другую – переопределите такой же формат и в ней, либо скопируйте в нее одну из отформатированных ячеек.
Проведите пару экспериментов с пользовательскими форматами, и увидите, это может даже быть увлекательным! Часто мои знакомые и ученики пишут огромные форматы один раз, чтобы потом в разы быстрее получать читаемые результаты своих вычислений. Берите на вооружение этот подход и будьте еще на один шаг эффективнее!
Заканчиваю пост об условном форматировании, и обещаю следующую статью – о проверке данных. Для своих постоянных читателей, да и для новичков назову ее обязательной. Читайте и узнаете почему. До новых встреч!

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *