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


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



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

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

При создании пользовательского числового формата не обязательно указывать все четыре раздела. Другими словами, если вы создадите только два раздела, первый будет использоваться для положительных чисел и нулевых значений, а второй — для отрицательных чисел. Если создать только один раздел, все числовые типы будут выводиться в этом единственном формате. Пользовательские форматы влияют на текст, только когда вы указываете все четыре раздела; для текста будет применяться последний раздел.

Не следует считать, что пользовательские форматы применяются только к числовым данным. Числовые форматы применяются и к тексту.

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

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

Рис. 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 округляет значение.

В Excel предусмотрен флажок Точность, как на экране (Precision as Displayed), который можно установить, выбрав команду Сервис → Параметры → Вычисления (Tools → Options → Calculation), но необходимо помнить, что этот параметр навсегда изменяет хранимые в ячейках значения с полной точности (15 цифр) до того формата, включая десятичные разряды, в котором значение выводится на экран. Другими словами, если вы установите этот флажок и щелкните кнопку ОК, пути назад уже не будет. (Можно попробовать, но дополнительная информация о точности значения исчезнет навсегда.)

Формат по умолчанию для любой ячейки — Общий (General). Если вы вводите в ячейку число, Excel часто угадывает наиболее подходящий числовой формат. Например, если вы введете 10%, Excel отформатирует эту ячейку процентным форматированием. Чаще всего Excel угадывает правильно, однако иногда формат приходится менять вручную.
Находясь в диалоговом окне Формат → Ячейки (Format → Cells), постарайтесь избежать искушения принудительно включить форматирование по левому или правому краю или по центру. По умолчанию числа выравниваются по правому краю, а текст — по левому. Если вы не будете менять эти параметры, то сможете с первого взгляда сказать, является ячейка текстовой или числовой, как в предыдущем примере, когда в ячейке А1 выводился текст, но на самом деле она содержала число.

В каждом разделе данного формата используется собственный набор кодов форматов. Эти коды заставляют Excel выводить данные так, как вам это нужно. Так, например, предположим, что вы хотите, чтобы отрицательные числа выводились в круглых скобках, а для всех чисел (положительных, отрицательных и нулевого значения) выводились два десятичных разряда. Для этого используйте следующий пользовательский формат: 0.00_ :(-0.00). Если вы также хотите, чтобы отрицательные числа выводились красным цветом, используйте формат 0.00_ :[Red](-0.00), в русской версии Excel: 0.00_ ;[КРАСНЫЙ](-0.00). Обратите внимание на квадратные скобки в предыдущем коде. Этот код формата заставляет Excel выводить число красным цветом.
В разделах пользовательского формата можно применять множество кодов форматов. В таблицах 2.1-2.5 [скачать], полученных из документации Microsoft, объясняется значение этих кодов (в английской версии).

Обратите особое внимание на последний код формата в табл. 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 будут отформатированы по левому краю, а числа — по правому.

13.09.20128988

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




Читайте ранее:
Трюк №31. Как в Excel манипулировать данными при помощи расширенного фильтра

Если вы знакомы с средством Excel Автофильтр (AutoFilter), то вы также должны знать о его ограничениях. Для более гибких возможностей...

Закрыть