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

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

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

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

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

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

Здесь я привел самые полезные служебные символы, полный перечень можно найти в справке Excel. Повторюсь, строка с форматом записывается в поле «Тип» меню «Формат ячеек», вкладка «Все форматы» Комбинируя обычные и служебные символы между собой, можно добиться того вида, который нужен. Давайте посмотрим как это работает на нескольких примерах: Интересным решением станет условный формат, когда способ форматирования зависит от содержимого ячейки. Это очень похоже на условное форматирование. Например, мне приходилось получать в работу прайс-лист, где в поле «Остатки» было указано не количество, а «много», «мало» и т.д. Отличный вариант, чтобы не дать точных цифр, а лишь подтолкнуть клиента сделать заказ побольше. Вот как это можно реализовать: Пользовательские форматы – действительно простая тема, но очень мощная и полезная в работе. Напомню, что при установке формата, значение в ячейке не изменяется. Изменяется лишь способ его отображения. Пользовательский формат сохраняется вместе с рабочей книгой. Чтобы перенести его в другую – переопределите такой же формат и в ней, либо скопируйте в нее одну из отформатированных ячеек. Проведите пару экспериментов с пользовательскими форматами, и увидите, это может даже быть увлекательным! Часто мои знакомые и ученики пишут огромные форматы один раз, чтобы потом в разы быстрее получать читаемые результаты своих вычислений. Берите на вооружение этот подход и будьте еще на один шаг эффективнее! Заканчиваю пост об условном форматировании, и обещаю следующую статью – о проверке данных. Для своих постоянных читателей, да и для новичков назову ее обязательной. Читайте и узнаете почему. До новых встреч!