Числовое форматирование Excel - это одна из самых недооценённых пользователями функций программы.
В этой заметке я расскажу о принципах создания пользовательского числового формата, и по ходу мы разберем примеры, которые продемонстрируют всю мощь этой, на первый взгляд, не особо нужной функции Excel.
Но перед тем, как приступим, хочу обратить ваше внимание на один важный момент.
Суть числового форматирования
Не раз сталкивался с заблуждением пользователей, которые считают, что числовое форматирование как-то влияет на значение, находящееся в ячейке.
ВАЖНО понимать, что это НЕ ТАК!
Числовое форматирование влияет только на отображение значения. Само значение при этом никак не изменяется.
То есть если в ячейке находится цифра с двумя знаками после запятой (1), а мы с помощью числового форматирования уберем дробную часть (2), то визуально кажется, что значение было округлено, но это не так. В ячейке по-прежнему хранится значение с дробной частью (3) и в формулах будет использоваться именно оно.
Числовой формат позволяет изменить внешний вид и сделать данные более аккуратными и легко воспринимаемыми.
Ну а теперь поговорим о синтаксисе числового форматирования.
Синтаксис числового форматирования
Обычно числовое форматирование применяется следующим образом: выделяется ячейка или диапазон ячеек (1), к которому нужно числовой формат применить, и затем из выпадающего списка (2) выбирается готовый шаблон (3).
Как правило, пользователи не интересуются, как именно этот шаблон работает, а просто применяют готовые форматы в своей работе.
Также все готовые шаблоны доступны и в окне Формат ячеек.
Здесь в самом конце списка находится пункт «все форматы», который позволяет посмотреть на код, используемый в готовых шаблонах, а также создавать свои форматы.
Для создания формата нужно понимать синтаксис написания кода, то есть нужно изучить правила, по которым он формируется и узнать, какие вспомогательные символы в коде используются.
Именно об этом я и расскажу в этой заметке.
Итак, код числового формата в общем случае состоит из четырех разделов:
ПОЛОЖИТЕЛЬНЫЕ; ОТРИЦАТЕЛЬНЫЕ; НОЛЬ; ТЕКСТ
Сначала указывается правило форматирования положительных значений, затем отрицательных, далее нулевых и в конце текста. Все четыре раздела отделяются друг от друга точкой с запятой.
При этом необязательно в коде должны присутствовать все четыре раздела.
Если задан только один раздел, то он будет применяться ко всем числовым значениям вне зависимости от их величины и знака. Если указано два раздела, то первый будет использоваться для форматирования положительных числовых значений и нулей, а второй для отрицательных чисел. И только указание всех четырех разделов в коде позволит форматировать текстовые значения.
Код каждого раздела числового формата задается с помощью специальных символов. Давайте их рассмотрим на примерах и пока будем использовать самый простой шаблон - зададим только один раздел для всех числовых значений.
Символ 0 в числовом формате
Символ «ноль» в коде числового формата используется для вывода обязательной цифры. С его помощью можно вывести незначащие нули как в целой, так и в дробной части числа. То есть каждый ноль в коде резервирует место под цифру, которая будет введена или уже находится в ячейке. Если цифры нет, то будет выведен ноль.
Давайте рассмотрим на примере.
В первом столбце находятся значения в общем формате, а в последующих к этим же значениями применяется пользовательский формат ( шаблон в заголовке).
Что произошло после применения пользовательского формата ко второму столбцу?
Для первого значения появились три нуля в начале. Это как раз те незначащие нули, о которых я упоминал ранее. Мы задали шаблон из четырех нулей, а значит отформатированное число должно содержать в целой части минимум четыре цифры. Если цифр меньше, то число дополняется нулями. Так в следующих двух строках никаких изменений не произошло, потому что цифр в числе было столько же сколько в шаблоне или больше, а вот ниже в дробном числе мы видим, что нули в начале появились, а дробная часть исчезла. При это обратите внимание на то, что число было округлено. Это хорошо заметно на значении в ячейке ниже (123,6 -> 124). При этом само значение не изменилось. Если мы выберем соответствующую ячейку, то в строке формул увидим полное число с дробной частью.
Так как мы задали только один шаблон, то он применяется ко всем числовым значениями, поэтому и нулевое, и отрицательные значения были отформатированы аналогичным образом.
Ну и шаблон в третьем столбце никак не влияет на отображение целой части, а вот дробную он либо округляет до двух знаков после запятой, либо дополняет незначащими нулями. То есть незначащие нули выводятся всегда, если на зарезервированном ими месте нет цифры.
Символы # в числовом формате
Следующий символ - # (решетка), предназначен для вывода необязательной цифры. Здесь все аналогично нулю, но с той лишь разницей, что если в числе на месте кода нет цифры, то ничего выводиться не будет.
В первом случае выводится только само число без дробной части, во втором выводится и дробная часть.
Ни в одном из двух примеров дополнительные нули не появляются, хотя во втором примере разделитель присутствует даже у чисел без дробной части.
Также обратите внимание на то, что поскольку мы зарезервировали только два знака после запятой, то числа с бОльшим количеством знаков будут округлены.
Ну и так как символ # не отображает незначащие нули, то ячейки с нулевыми значениями не выводятся.
Символы ? в числовом формате
Вопросительный знак - предназначен для вывода необязательной цифры и позволяет выводить только значащие цифры, то есть нули выводиться не будут.
Однако в случае использования вопросительного знака в дробной части, при отсутствии значащих цифр место под них будет резервироваться. В итоге значения в столбце визуально выравниваются по разделителю.
Текст в коде числового формата
Есть и другие символы, которые применяются в коде, но перед тем, как мы их рассмотрим, давайте разберемся с тем, как подставить произвольный текст в числовой формат.
Например, у вас есть столбец с какими-то значениями. Пусть это будет количество товаров на складе.
Необходимо, чтобы в каждой ячейке с количеством выводилась единица измерения, например, «шт.».
Если мы во время заполнения данных в таблице кроме значения введем и текст, то числовой формат ячейки автоматически изменится на текстовый, что легко понять по изменению выравнивания значения в ячейке - по левому краю.
В итоге с данными мы не сможем работать как с числами. Например, если нужно посчитать сумму значений столбца, то в данном случае это сделать не получится.
Решить проблему позволит числовое форматирование.
Создадим пользовательский формат такого вида - 0"шт."
В двойных кавычках выводится необходимый текст. Если мы теперь применим формат, то увидим, что текст «приклеился» к знанию.
Откорректируем пользовательский формат и вставим пробел перед текстом в кавычках - 0" шт."
Сейчас очень важно, чтобы пробел был именно в кавычках, тогда он будет относиться к тексту. О других значениях пробела в коде речь пойдет дальше.
Теперь все отображается верно и так как формат числовой, то при выборе любой ячейки в строке формул мы увидим только значение, без текстовой «добавки». Соответственно, можем производить любые математические операции с данными.
Добавить текст можно и перед значением.
Символ \ в числовом формате
Также для вывода дополнительных символов можно использовать обратный слэш (косую черту) - он предназначен для вывода одного символа.
Фактически использование обратной косой черты эквивалентно использованию двойных кавычек.
Например, введем в любую ячейку число и изменим числовой формат на пользовательский. Создадим код следующего вида: 0\ \ш\т\.
То есть мы вводим символ шаблона 0, который выведет значение, затем нужен пробел, который отделит значение от единицы измерения. Так как пробел в данном случае является частью текста, то перед ним также ставим обратную косую черту. Фактически эта запись эквивалентна следующей: 0« шт.»
Обратный слэш удобнее применять, если нужно подставить только один произвольный символ. Если символов больше, то проще заключить их в двойные кавычки.
Например, если нужно перед числами подставить знак плюс-минус, то удобнее использовать косую черту. Знак можно заранее вставить в произвольную ячейку через вкладку вставка. Затем скопировать его в буфер обмена и затем использовать при создании числового формата.
Ну и есть ряд символов, которые можно использовать в коде без двойных кавычек и обратной черты. Они перечислены в справочной системе на сайте Майкрософт.
Рекомендую перейти на сайт и ознакомиться с полным перечнем символов.
Их можно просто подставлять в код:
Итак, мы рассмотрели базовые символы, применяемые в числовом формате Excel. Однако это далеко не все возможности пользовательского числового форматирования. Поэтому продолжение следует...
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
