Excel прекрасно умеет хранить одно число и представлять другое число. Выберите любую ячейку и выберите Формат валюты. Excel добавляет знак доллара и запятую и представляет число, округленное до двух десятичных знаков. На рисунке ниже ячейка D2 фактически содержит 6,42452514. К счастью, встроенный пользовательский числовой формат представляет результаты в удобном для чтения формате.
Код пользовательского числового формата в D2: $ #, ## 0.00. В этом коде 0 являются обязательными цифрами. Любые # являются необязательными цифрами.
Однако коды форматирования могут быть намного сложнее. Код выше имеет один формат. Этот формат применяется к каждому значению в ячейке. Если вы предоставляете код с двумя форматами, первый формат предназначен для неотрицательных чисел, а второй - для отрицательных чисел. Вы разделяете форматы точкой с запятой. Если вы предоставите код с тремя форматами, первый предназначен для положительного, затем отрицательного и затем нулевого. Если вы предоставляете код с четырьмя форматами, они используются для положительного, отрицательного, нуля и текста.
Даже если вы используете встроенный формат, вы можете перейти в «Формат ячеек», «Число», «Пользовательский» и увидеть код, используемый для создания этого формата. На рисунке ниже показан код формата учета.
Чтобы создать свой собственный формат, перейдите в раздел Формат ячеек, Число, Пользовательский и введите код в поле Тип. Посмотрите пример в поле Образец, чтобы убедиться, что все выглядит правильно.
В следующем примере используются три зоны. Текст в кавычках добавляется к числовому формату, чтобы настроить сообщение.
Если вы создадите зону для нуля, но ничего не укажете, вы скроете все нулевые значения. В следующем коде используются цветовые коды для положительного и отрицательного. Код заканчивается точкой с запятой, создавая зону для нулевых значений. Но поскольку зона пуста, нулевые значения не отображаются.
Вы можете расширить это, сделав все зоны пустыми. Код произвольного формата ;;; скроет значения на дисплее и распечатке. Однако вы по-прежнему сможете видеть значения в строке формул. Если вы скрываете значения, делая шрифт белым, то ;;; останется скрытым, даже если люди изменят цвет заливки. На следующем рисунке показаны некоторые интересные приемы форматирования.
В B2 и B3, если вы поставите ** перед числовым кодом, Excel заполнит слева от числа звездочками, как это делали бы старые машины для записи чеков. Но нет ничего, что говорит о том, что вы должны использовать звездочки. Все, что вы поставили после первой звездочки, повторяется, чтобы заполнить пространство. В строке 3 используется *! повторять восклицательные знаки.
В B4 и B5 каждая запятая, которую вы ставите после конечного нуля, делит число на 1000. Код 0, K показывает числа в тысячах, с буквой K после. Если хотите показать миллионы, используйте две запятые. Код «M» должен включать кавычки, так как M уже означает месяцы.
В B6 добавьте суровое сообщение в четвертую зону, чтобы предупредить любого, кто вводит данные, о том, что вы хотите ввести число в ячейку. Если они случайно введут текст, появится сообщение.
В B7 – B9 нормальные зоны «Положительные», «Отрицательные» и «Ноль» заменяются условиями, заключенными в квадратные скобки. Цифры до 70 - красные. Цифры старше 90 - синие. Все остальное черное.
В B10 эти нечетные символы _ (в формате учета говорят Excel оставить столько места, сколько потребуется левой круглой скобке. Оказывается, подчеркивание, за которым следует любой символ, оставит столько же пробелов, сколько и этот символ. В B10, код содержит 4 нуля. Но между ними есть разное количество пробелов. Расстояние между 1 и 2 - это ширина 2 символов W. Промежуток между 2 и 3 - это ширина N. Пробел между 3 и 4 ширина строчной буквы i.
На следующем рисунке показаны различные коды форматирования даты.
Заметка
Формат mmmmm в строке 8 полезен для создания подписей диаграмм JFMAMJJASOND.
Спасибо Дэйву Бейлису, Брэду Эдгару, Майку Гирвину и @best_excel за предложение этой функции.
