Нестандартные форматы

Настраиваемые нестандартные форматы создаются с помощью уже знакомой нам вкладки Число  диалогового окна  Формат ячеек.  На  ней  необходимо  выбрать категорию  Все форматы (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 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий