May 27th, 2012 
admin
Настраиваемые нестандартные форматы создаются с помощью уже знакомой нам вкладки Число диалогового окна Формат ячеек. На ней необходимо выбрать категорию Все форматы (Custom) (при этом вкладка принимает вид, изображенный на рис. 8.14) и ввести специальные коды форматирования в поле Тип (Type). Excel добавляет все новые форматы в конец списка Тип, который начинается со встроенных форматов. Чтобы удалить любой пользовательский формат, выделите его в списке и нажмите кнопку Удалить (Delete). Встроенные форматы удалить невозможно.
Создание новых форматов
Эту задачу можно ускорить, если в качестве отправной точки взять один из уже имеющихся форматов Excel. Ниже мы рассмотрим порядок действий по получению нового формата на основе уже существующего, а также описание специальных символов, используемых в поле Тип для записи формата.
1. Введите число и присвойте ячейке встроенный формат, наиболее приближенный к целевому. Ячейку оставьте выделенной.
2. На вкладке Число диалогового окна Формат ячеек выберите категорию Все форматы. В поле Тип будет представлена маска (кодовая запись) формата, примененного к ячейке.
3. Отредактируйте маску, используя символы, представленные в табл. 8.1. Исходный формат сохранится без изменений, а новый формат добавится в конец списка Тип.
Рис. 8.14. Создайте новый формат на основе уже существующего
Пусть нас интересует формат для отображения дат в очередности: день недели (название), месяц (название), день месяца и год. Начните с ввода в ячейку самой даты (например 25/07/04) и затем выделите эту ячейку. В списке Числовые форматы выберите категорию Все форматы. Далее в поле Тип измените запись этого формата на ДДДД, ММММ ДД, ГГГГ (с учетом пробелов и запятых) и нажмите кнопку ОК. Результат выполнения этой процедуры показан на рис. 8.15.
Рис. 8.15. Мы создали новый формат даты, введя код формата в поле Тип
СОВЕТ
При сохранении книги сохраняются и новые форматы, но чтобы перенести пользова-
тельский формат из одной книги в другую, необходимо скопировать и затем вставить в новую книгу ячейку, которой присвоен этот формат. Для упрощения доступа к нестандартным форматам старайтесь сохранять их в одной книге.
Числовые форматы записываются с помощью символов, приведенных в табл. 8.1.
Таблица 8.1. Символы, используемые для кодировки форматов
Рис. 8.16. Excel допускает запись форматов из четырех разных частей
Среди всех встроенных форматов все четыре части используются только в финансовых — это видно из рис. 8.16. Ниже приводятся основные правила записи кода многовариантных форматов:
? если код записи содержит только одну маску, Excel применяет ее ко всем по-
ложительным, отрицательным и нулевым значениям;
? если же формат состоит из двух частей, то первая применяется ко всем положительным и нулевым значениям, а вторая — только к отрицательным;
? третья секция формата относится только к нулевым значениям;
? четвертый и последний элемент в четырехвариантной записи формата контролирует отображение текстовых значений. Если последняя часть не включена в код пользовательского формата, то созданный формат не будет влиять на текстовые значения.
СОВЕТ
При желании на рабочем листе можно скрыть все нулевые значения. В меню Сервис выберите команду Параметры и в открывшемся диалоговом окне перейдите на вкладку Вид. Снимите флажок Нулевые значения (Zero Values) в разделе Параметры окна (Window Options).
Предположим, вы хотите создать нестандартный финансовый формат, где в зависимости от значения денежные суммы выводились бы по-разному. Пример такого пользовательского формата приведен на рис. 8.16; для него был написан следующий код:
"Перечислено:" # ##0,00р.; "Кредит: "-# ##0,00р.; "Чистый счет."; "Обратите внимание: "@
Или, допустим, вы создаете рабочий лист для учета материальных ценностей и хотите, чтобы все числовые значения в одном из столбцов отображались в формате Инв. № ХХХХХХХ, как на рис. 8.16. В этом случае воспользуйтесь кодом:
"Инв. №"###-####
Окрашивание форматированных данных
Числовые форматы, содержащие код для изменения цвета, влияют на цвет значений выделенных ячеек. Это позволяет, например, выделить отдельные промежуточные результаты или итоги. Можно даже создавать форматы, в которых, например, значения больше какой-либо величины будут записываться одним цветом, а меньше этой величины
— другим.
СОВЕТ
Можно написать код, обеспечивающий разную окраску в зависимости от самих значений, но гораздо проще воспользоваться для этого встроенным средством Excel — командой Условное форматирование (Conditional Formatting) меню Формат. Подробнее об этом рассказывается в разделе «Условное форматирование» главы 9.
Для изменения цвета значения вставьте в код формата в начало соответствующей маски название нового цвета в квадратных скобках. Например, если требуется создать формат Денежный «синего цвета» с двумя знаками после десятичной точки, отредактируйте один из имеющихся денежных кодов
_-$# ##0,00:_$# ##0,00
следующим образом:
[Синий]_-$# ##0,00;-$# ##0,00
В результате применения этого формата к листу положительные и нулевые значения будут выделены синим цветом, а текстовые и отрицательные останутся черными (по умолчанию). А приведенный ниже формат выводит все положительные значения синим цветом, отрицательные — красным, нулевые — желтым, а текст — зеленым цветом:
[Синий] ; [Красный.} ; [Желтый] ; [Зеленый]
В масках форматов употребляются следующие названия цветов: Черный (Black), Синий (Blue), Голубой (Cyan), Зеленый (Green), Розовый (Magenta), Красный (Red), Белый (White) и Желтый (Yellow). Кроме этого, цвет можно задавать кодом ЦВЕТи, где п — любой индекс из диапазона от 1 до 16. Excel в этом случае выбирает соответствующий цвет из текущей 16-цветной палитры. Если выбранный цвет получается смешиванием нескольких чистых цветов, используется ближайший чистый цвет палитры.
ПРИМЕЧАНИЕ -:-
Если задать оттенок, отсутствующий среди полного набора системных цветов, Excel
образует его смешиванием точек чистых цветов. Полученные оттенки называются
«смешанными» (dithered) и прекрасно подходят для заливок. Но для текста и линий Excel использует только чистые цвета, выбирая вместо смешанного ближайший к нему чистый оттенок.
СМОТРИ ТАКЖЕ –
Создание и использование пользовательских форматов описывается в разделе «Не-
стандартные форматы» данной главы.
РЕШЕНИЕ ПРОБЛЕМ –
Иногда при работе с денежными форматами, такими как канадский доллар или рубль (где символ валюты — концевой), нет необходимости ставить после каждого числового значения символ валюты. В практике GAAP бывает достаточно поместить этот знак сверху или снизу колонки чисел. Поскольку в таком случае символы валюты не отображаются в каждой ячейке, возникает вопрос: как выровнять все числа по десятичной точке?
Одним из способов преодоления проблемы является создание нового формата, в котором будет отсутствовать символ валюты. Знак подчеркивания (_) в маске сообщает программе о том, что нужно оставлять пустой промежуток, равный ширине символа, следующего за знаком подчеркивания. Например, код _$ означает, что требуется оставить пространство, равное ширине знака доллара. Таким образом, нашу задачу можно решить, записав следующий код:
# ##0,00 _$’. [Красный]* ##0,00 _$
Не забудьте добавить пробел между нулем и символом подчеркивания, чтобы все числа в столбце были выровнены по десятичной точке.
Использование условий в кодах форматов
Помимо всего прочего, Excel позволяет управлять отображением чисел в ячейках в зависимости от выполнения (или невыполнения) определенных условий. Для этого необходимо добавить условия в две первые маски стандартного че-тырехвариантного формата, которые при отсутствии условий соответствуют положительным и отрицательным значениям. Тогда в третьей маске будет описываться формат всех значений, не удовлетворяющих условиям первых двух. При определении форматов с условиями используются операторы сравнения <, >, =, <=, >= и <> в сочетании с любыми числами.
Положим, вы подводите баланс по счетам. Чтобы вывести на экран счета с балансом,
превышающим $50 000, синим цветом, отрицательные значения — красным, а все
другие значения — цветом, используемым по умолчанию, создайте следующий формат:
[Синий][>50000]_-$# ##0,00;[Красный][<0]-$# ##0,00;_-$# ##0.00
Условные операторы также могут оказаться удобным средством для масштабирования числовых значений. Например, если фирма производит продукт, для изготовления которого требуется определенное количество миллилитров какого-либо компонента на каждую единицу выпускаемой продукции, а объем производства при этом составляет тысячи единиц в день, то при подведении итогов придется переводить миллилитры (мл) сначала в литры (л), а затем и в килолитры (кл). Для решения этой задачи можно создать следующий числовой формат:
[>999999]# ##0 _м"кл";[>999]#0 _к_м"л";#_к"мл"
Тогда, если вы введете в ячейку небольшое значение 72, показано будет 72 мл. При вводе большого числа (например, 7286957), оно будет выражено уже в килолитрах (соответственно 7 кл). Промежуточные значения переведутся в литры (например: 7632 — 8 л).
Таким образом, включив в формат условия, разделитель групп разрядов и символ подчеркивания (_), можно без дополнительных формул улучшить и визуальное восприятие таблицы, и, соответственно, продуктивность работы с ней.
Скрытие значений с помощью форматирования
Чтобы скрыть какую-либо величину на рабочем листе, можно применить к ней «пустой» формат. Делается это очень просто — нужно из соответствующей величине части кода формата убрать все символы, оставив только разделители масок (запятые). Например, скрыть только отрицательные и нулевые значения позволяет такой формат:
$# ##0,00;;
Чтобы скрыть любые записи в ячейке, сведите код к его простейшей форме:
;;;
Пустая маска скрывает значение на листе, но оно остается по-прежнему видимым в строке формул и доступным по ссылкам из формул. Если необходимо спрятать содержимое ячеек так, чтобы «след» не оставался ни в ячейках, ни в строке формул, воспользуйтесь куда более надежным средством — защитой ячеек. Более подробно о ней рассказывается в разделе «Защита данных» главы 5.
Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.
Опубликовано в рубрике