Средства форматирования таблиц. Пользовательские форматы. Условное форматирование

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

- формат данных;

- формат шрифта. Шрифт – это гарнитура, рисунок цифр и символов;

- выравнивание содержимого ячеек;

- обрамление ячеек. Рамка – это линия, очерчивающая ячейку или выделяющая одну из ее сторон;

- палитра. Палитра – это совокупность используемых для оформления цветов;

- узоры;

- ширина и высота ячеек.

Форматы могут задаваться для активной ячейки или выделенного диапазона ячеек, до ввода данных или после него. Над форматами можно выполнять действия: применить, копировать, изменить.

Форматирование данных заключается в задании форматов чисел и текста. По умолчанию Excel вводит значения в соответствии с числовым форматом «Общий». Значения отображаются в том виде, в каком они были введены с клавиатуры. При этом Excel анализирует значения и автоматически присваивает им нужный формат, например 100 – число 100, а 10:52 – числовой формат даты и времени.

Выбор формата данных осуществляется на вкладке Число диалогового окна команды Ячейки меню ФОРМАТ. Поле списка Числовые форматы содержит категории форматов.

 

 

Для форматирования дробей и чисел с десятичной точкой в форматах используются следующие символы шаблонов:

# - символ указывает, что в данную позицию выводятся только значащие цифры, незначащие нули не отображаются. Например, ###,# 123,46 => 123,5; 0,12=>,1

0 (ноль) – символ указывает на отображение незначащих нулей. Например, #,00 1,2=>1,20; 1=>1,00.

? –при использовании этого символа до или после десятичной точки вместо незначащих нулей отображаются пробелы. Например,???,??? 1,2=> 1,2.

(пробел) - символ указывает на необходимость вставки пробела в качестве разделителя групп разрядов числа. Например, # ### 10000=>10 000

Запятая - как символ определяет положение десятичной точки.

Количество символов шаблона в формате определяет правила округления числа при выводе. Если дробная часть числа содержит больше цифр, чем символов шаблона в формате, то число округляется так, чтобы количество разрядов соответствовало количеству символов в шаблоне. Если целая часть числа содержит больше цифр, чем количество символов в шаблоне, то отображаются все значащие разряды. Если в формате целой части числа представлены только символы #, а число меньше 1, то число начинается с десятичной точки.

В числовом формате можно задавать цветовое представление значений. В этом случае первым элементов в шаблоне должен быть код цвета. Например, [синий]# ##0,00;[красный]-# ##0,00 => все положительные значения будут выведены синим цветом, все отрицательные – красным.

В числовом формате можно задавать формат, используемый только для чисел, удовлетворяющих заданному условию. Условие должно состоять из оператора сравнения и значения, заключенных в квадратные скобки. Например, [красный][<1000]0;[синий][>1000]0 => все значения, большие тысячи, будут выведены синим цветом, остальные – красным.

Форматы даты и времени

Для форматирования данных типа данных и времени используются следующие символы:

Д – для отображения дней: Д – дни 1 – 31; ДД – 01 –31; ДДД – Пн –Вс; ДДДД – Понедельник – Воскресенье.

М – для отображения месяца: М – 1 – 12; ММ – 01 – 12; МММ – янв – дек; ММММ – январь – декабрь; МММММ – первая буква месяца.

Г – для отображения лет: ГГ – 00 – 99; ГГГГ – 1900 – 9999.

ч – для отображения часов: ч – 0 – 23; чч – 00 – 23.

м – для отображения минут: м – 0 – 59; мм – 00 – 59.

с – для отображения секунд: с – 0 –59; сс – 00 – 59.

0 (ноль) – для отображения долей секунд: ч:мм:сс.00

AM/PM – для отображения двенадцатичасовой системы

[ ] – для отображения интервалов времени. Позволяет выводить значения, превышающие 24 часа, 60 минут или 60 секунд.

Форматы могут быть составными, т.е. иметь числовую и текстовую часть. Текстовая часть всегда является последней в формате. Символы шаблона:

“” \ - двойные кавычки или обратная косая черта указывают на необходимость вставки, начиная с позиции шаблона, текстовых символов, следующих за ним в формате. Например, 0,00р.»Излишки»;[красный]-0,00р.»Недостатки»

@ - указывает, что, начиная с этой позиции формата, могут выводиться любые символы (текст);

* - указывает, что, начиная с позиции символа в формате, необходимо многократно вывести символ, следующий за * (пока столбец не окажется заполненным по ширине);

_ - символ подчеркивания, используется для выравнивания содержимого ячейки; вместо него проставляется пробел, по ширине равный следующему за ним символу.

Пользовательские форматы чисел, дат и времени.

Если ни один формат вывода из стандартного набора не устраивает пользователя, он может создать свой пользовательский формат, сохранить его и применять. Пользовательский числовой формат создается путем описания его с помощью символов шаблона для отображения данных: числа, даты, времени, текста. Пользовательский формат может состоять из четырех частей (секций), которые разделяются знаком «;»:

формат полож.чисел;формат отриц.чисел;формат нулевых знач.;текст

Можно указать пустую секцию, если для данного вида значений формат отсутствует. Для этого следует сразу ввести знак «;«

Условное форматирование - это форматирование выделенных ячеек на основе условий, заданных числами и формулами. Предназначено для выделения данных. Если данные ячейки удовлетворяют заданным условиям, то к ячейке будут применены установленные форматы.

Критерий условного форматирования состоит из условных форматов. В критерии можно указать до трех условных форматов. Условный формат задается в виде условия:

<что сравниваем> операция сравнения <с чем сравниваем>

Параметр Что сравниваем может быть задан значением выделенной ячейки, формулой выделенной ячейки (формула должна начинаться с символа «=», результатом формулы должно быть логическое значение ИСТИНА или ЛОЖЬ).

Параметр С чем сравниваем может быть задан константой или формулой. Формула должна начинаться с символа «=» и может содержать абсолютные и относительные ссылки.

Операции сравнения кроме всех известных операций содержат также операцию Между - для задания интервала значений, в который может попадать значение параметра «что сравнивать» и операцию Вне для указания интервала, вне которого может находиться значение параметра Что сравнивать..

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

1). Выделить любую ячейку или ячейку, имеющую условный формат.

2). Выполнить команду Перейти меню ПРАВКА.

3). Выбрать кнопку Выделение.

4). В диалоговом окне Выделение группы ячеек выбрать и установить переключатель Условные форматы.

5). Для выделения всех ячеек листа, для которых заданы условные форматы, выбрать переключатель Всех.

6). Для выделения ячеек листа с теми же условными форматами, что и активная ячейка, выбрать переключатель Этих же.

Средства защиты данных

 

В Excel есть несколько команд, позволяющих защищать книги, структуры книг, отдельные ячейки, графические объекты, диаграммы, сценарии и окна от несанкционированного доступа или изменения. По умолчанию Excel блокирует (защищает) ячейки, графические объекты, диаграммы, сценарии и окна, но эта защита не вступает в силу, пока не выполнена команда Защита - Защитить лист меню СЕРВИС.

 

Установленные в диалоговом окне команды варианты защиты применяются только к текущему листу. После включения защиты нельзя изменить заблокированный элемент. При попытке сделать это на экран выводится соответствующее сообщение. В большинстве случаев нет необходимости в блокировке всех ячеек рабочего листа. Прежде чем защищать лист, следует выделить ячейки, которые нужно оставить незаблокированными. Затем снять флажок Защищаемая ячейка на вкладке Защита команды Ячейки меню ФОРМАТ.

Можно также предотвратить изменение структуры книги и зафиксировать положение ее окна. Для этого используется команда Защита - Защитить Книгу меню СЕРВИС.

 

Содержимое рабочего листа книги можно скрыть. В этом случае строка ярлыков не содержит ярлык скрытого рабочего листа. Чтобы сделать рабочий лист невидимым, следует выполнить команду Лист - Скрыть меню ФОРМАТ. В скрытых листах все данные и результаты вычислений сохраняются, они просто скрыты от просмотра. Чтобы затем вернуть режим вывода содержимого листа, следует выполнить команду Лист - Отобразить меню ФОРМАТ, в поле списка скрытых листов диалогового окна команды выбрать нужный и нажать клавишу Enter.

К ячейкам рабочего листа можно применить режим скрытия формул. При активизации таких ячеек содержащиеся в них формулы не выводятся в строке формул. Сами формулы в ячейках по-прежнему сохраняются, они просто недоступны для просмотра, а результаты вычислений остаются видимыми. Чтобы включить режим скрытия формул, необходимо выполнить следующие действия: выделить ячейки, которые нужно скрыть; установить флажок Скрыть Формулы на вкладке Защита команды Ячейки меню ФОРМАТ; в установить флажок Содержимого в диалоговом окне команды Защита - Защитить лист меню СЕРВИС

В окне диалога команд Защитить лист или Защитить книгу меню СЕРВИС можно назначить пароль, который должен быть введен для того, чтобы снять установленную защиту. Можно использовать разные пароли для всей книги и отдельных листов. Чтобы назначить пароль необходимо выполнить команду Защита - Защитить лист (Защитить книгу) меню СЕРВИС, ввести пароль, после запроса Excel подтвердить пароль, для чего ввести его повторно, нажать ОК для возврата в окно книги. После назначения пароля нет способа снятия защиты с листа или книги без ввода этого пароля. Следует запоминать свои пароли с точностью до регистра букв.