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

В Excel предусмотрены встроенные числовые форматы, но иногда вам нужно использовать числовой формат, не встроенный в Excel. При помощи трюков этого раздела можно создавать числовые форматы и настраивать в соответствии с вашими требованиями.

Перед тем как приступать к изучению этих трюков, полезно понять, как Excel распознает форматы ячеек. С точки зрения Excel, формат ячейки состоит из четырех разделов (слева направо): положительные числа, отрицательные числа, нулевые значения и текстовые значения. Они отделяются друг от друга точкой с запятой (;).

При создании пользовательского числового формата не обязательно указывать все четыре раздела. Другими словами, если вы создадите только два раздела, первый будет использоваться для положительных чисел и нулевых значений, а второй — для отрицательных чисел. Если создать только один раздел, все числовые типы будут выводиться в этом единственном формате. Пользовательские форматы влияют на текст, только когда вы указываете все четыре раздела; для текста будет применяться последний раздел.
[stextbox id=»warning»]Не следует считать, что пользовательские форматы применяются только к числовым данным. Числовые форматы применяются и к тексту.[/stextbox]
Пользовательский числовой формат, показанный на рис. 2.18, — это стандартный формат валюты Excel, который отображает отрицательные значения валюты красным цветом. Мы модифицировали его, добавив отдельный формат для нулевых значений и текста. Если в качестве значения валюты вы введете положительное число, Excel автоматически отформатирует его, чтобы на месте разделительного знака для разряда тысяч вставлялась запятая, и в числе использовалось два десятичных разряда. То же самое будет сделано и для отрицательных значений, но они будут отображаться красным цветом. Для любого нулевого значения символ валюты отображаться не будет, но будут присутствовать два десятичных разряда. Если вы введете в ячейку текст, Excel отобразит сообщение «No Text Please», независимо от того, какой это будет текст.

Рис. 2.18. Разделы пользовательского числового формата

Важно помнить, что форматирование значения в ячейке не влияет на его настоящее значение. Например, введите любое число в ячейку А1. Выберите команду Главная → Формат → Ячейки → Число → Все форматы (Home → Format → Cells → Number → Custom) и, выбрав любой формат, введите «Hello» (в кавычках). Затем щелкните кнопку ОК.

Во всем мире индустрия туризма является высокоэффективной, и для некоторых стран доход от нее составляет существенную часть ВВП. В России туризм бурно развивается, особенно в последнее десятилетие. Эффективность и качество работы туристских предприятий, таких как краснодар гостиница уют напрямую зависят от скорости и качества обработки туристской информации. Успешное функционирование предприятия туриндустрии практически немыслимо без использования современных информационных технологий.

Хотя в ячейке появится слово Hello, вы можете увидеть ее настоящее значение, выделив ячейку и взглянув на строку формул или нажав клавишу F2. Если вы хотите сослаться на эту ячейку в формуле, например, =А1+20, то в результирующей ячейке также будет применяться пользовательский формат. Если вы хотите сослаться на ячейку А1 вместе со многими другими ячейками со стандартным форматом Excel, например, =SUM(A1:A10), то в результирующей ячейке также будет применяться пользовательский формат ячейки Al. Excel считает, что вы хотите, чтобы результирующая ячейка была отформатирована так же, как и ячейки, на которые ссылается формула. Если эти ячейки содержат несколько типов форматирования, любой пользовательский формат будет иметь преимущественное значение.

Это означает, что всегда необходимо помнить: Excel использует для вычислений настоящее значение ячейки, а не отображаемое. При этом могут возникнуть сюрпризы, если вычисления проводятся с использованием ячеек, отформатированных, например, без десятичных разрядов или всего с несколькими. Чтобы увидеть, как это работает, введите (в английской версии) 1.4 в ячейке А1 и 1.4 в ячейке А2, отформатируйте обе ячейки так, чтобы они не показывали десятичные разряды, а затем в другую ячейку поместите формулу =А1+А2. В результате, конечно же, получится 3, так как Excel округляет значение.
[stextbox id=»warning»]В Excel предусмотрен флажок Точность, как на экране (Precision as Displayed), который можно установить, выбрав команду Сервис → Параметры → Вычисления (Tools → Options → Calculation), но необходимо помнить, что этот параметр навсегда изменяет хранимые в ячейках значения с полной точности (15 цифр) до того формата, включая десятичные разряды, в котором значение выводится на экран. Другими словами, если вы установите этот флажок и щелкните кнопку ОК, пути назад уже не будет. (Можно попробовать, но дополнительная информация о точности значения исчезнет навсегда.)[/stextbox]
Формат по умолчанию для любой ячейки — Общий (General). Если вы вводите в ячейку число, Excel часто угадывает наиболее подходящий числовой формат. Например, если вы введете 10%, Excel отформатирует эту ячейку процентным форматированием. Чаще всего Excel угадывает правильно, однако иногда формат приходится менять вручную.
[stextbox id=»warning»]Находясь в диалоговом окне Формат → Ячейки (Format → Cells), постарайтесь избежать искушения принудительно включить форматирование по левому или правому краю или по центру. По умолчанию числа выравниваются по правому краю, а текст — по левому. Если вы не будете менять эти параметры, то сможете с первого взгляда сказать, является ячейка текстовой или числовой, как в предыдущем примере, когда в ячейке А1 выводился текст, но на самом деле она содержала число.[/stextbox]
В каждом разделе данного формата используется собственный набор кодов форматов. Эти коды заставляют Excel выводить данные так, как вам это нужно. Так, например, предположим, что вы хотите, чтобы отрицательные числа выводились в круглых скобках, а для всех чисел (положительных, отрицательных и нулевого значения) выводились два десятичных разряда. Для этого используйте следующий пользовательский формат: 0.00_ :(-0.00). Если вы также хотите, чтобы отрицательные числа выводились красным цветом, используйте формат 0.00_ :[Red](-0.00), в русской версии Excel: 0.00_ ;[КРАСНЫЙ](-0.00). Обратите внимание на квадратные скобки в предыдущем коде. Этот код формата заставляет Excel выводить число красным цветом.
[stextbox id=»download»]В разделах пользовательского формата можно применять множество кодов форматов. В таблицах 2.1-2.5 [скачать], полученных из документации Microsoft, объясняется значение этих кодов (в английской версии).[/stextbox]
Обратите особое внимание на последний код формата в табл. 2.5 — операторы сравнения. Предположим, вы хотите, чтобы пользовательский числовой формат 0.00_ ;[Red] (-0.00) (0,00_ ;[Красный] (-0,00)) отображал отрицательные числа красным цветом и в скобках, только если они меньше -100. Для этого укажите следующий формат: 0.00_ :[Red][< -100](-0.00);0.00, в русской версии Excel 0.00_ ;[Красный][< -100](-0.00);0.00.

Добиться нужного результата помогают коды формата [Red][< -100](-0.00) ([Kpacный][<-100](-0,00)) в разделе для отрицательных чисел. При помощи этого метода и условного форматирования вы можете увеличить количество условий в условном формате с трех до шести.

Часто пользователи хотят отобразить знак доллара словами. Для этого используйте следующий пользовательский формат: 0 "Dollars and" .00 "Cents". Этот формат позволит числа, например, 55.25 отобразить как 55 Dollars and 25 Cents. Если вы хотите преобразовывать числа в доллары и центы, то, чтобы узнать об этих двух пользовательских функциях Microsoft, обратитесь на сайты http://www.ozgrid.com/VBA/ValueToWords.htm и http://www.ozgrid.com/VBA/CurrencyToWords.htm.

Кроме того, при помощи пользовательского формата можно отображать слова Low, Average или High вместе с введенными словами. Укажите следующий код формата: [<11]"Low"* 0;[>20]"High"* 0:"Average"* 0. Обратите внимание на звездочку. Она повторяет следующий символ в формате, заполняя столбец на всю ширину, благодаря чему слова Low, Average или High будут отформатированы по левому краю, а числа — по правому.