Форматирование таблиц. Использование условного форматирования

Цель занятия. Освоение возможностей форматирования таблиц и условного форматирования в Excel.

 

Порядок работы

Изменение внешнего вида таблицы без изменения ее содержимого называется форматированием. В Ехсеl имеются богатые возможности форматирования, благодаря которым таблица принимает удобный для восприятия вид. Форматирование включает в себя такие операции, как изменение ширины строк и высоты столбцов таблицы, выбор типа шрифта, его размера и начертания, выравнивание содержимого ячеек, выбор представления чисел, обрамление ячеек и выделениеих цветом. Мы не будем подробно изучать весь арсенал форматирования в Ехсеl, так как наша основная задача — научиться проводить в Ехсеl расчеты.

Создайте новую рабочую книгу Excel 06.х1s.

1. Общие сведения

Команды форматирования сосредоточены в меню "Формат". Наиболее часто используется команда меню "Формат/ Ячейки" (полезно запомнить для нее комбинацию клавиш Ctrl+1). Разумеется, эту команду можно применить сразу к блоку, предварительно выделив его. Наиболее важные команды привязаны к кнопкам панели инструментов "Форматирование".

Ехсеl предоставляет возможность очень быстро оформить список по вашему вкусу. Для этого нужно применить команду меню: "Формат/ Автоформат".

ПРИМЕР 1.

Автоформатирование таблицы. Скопируйте из рабочей книги Excel 05.хls таблицу с рабочего листа "Фирма". Выделите какую-нибудь ячейку в таблице. В меню выберите команду "Формат/ Автоформат". Выберите один из списка форматов. Испытайте, например, "Классический I". Многие из предлагаемых в списке форматов используют затенение и цветной фон в оформлении ячеек. Этого нужно избегать — и порошок в лазерном принтере расходуется бесполезно, и читать труднее.

Как избавиться от наложенного на таблицу формата? Нужно выделить таблицу и выбрать в меню команду "Правка/ Очистить/Форматы".

Когда Вы удаляете содержимое ячейки или блока нажатием на клавишу Del, то наложенный формат сохраняется. Полная очистка и содержимого, и форматов — команда "Правка/ Очистить/ Все".

2. Оформление таблиц

В диалоговом окне "Формат ячеек" есть вкладки "Выравнивание", "Шрифт", "Граница", "Вид", с помощью которых можно профессионально оформить заголовки таблиц. Используя эти средства, можно также подготовить бланки документов.

Перейдите на новый рабочий лист и назовите его "Книги".

ПРИМЕР 2.

Для книжного магазина составляется ежедневная таблица продаж книг (рис.6.1).

Продано книг
Д а т а	В с е г о	в том числе
Научная	Техническая	Художест-венная
15-мар
16-мар
17-мар
18-мар

Рис. 6.1

Нужно оформить такую таблицу на рабочем листе. (Вообще-то, вертикальное расположение строк так, как это сделано со строками "Дата" и "Всего", не соответствует отечественным стандартам. Но мы познакомимся с возможностями Ехсе1.) Сначала введем в ячейки текстовые строки. В ячейку А2 — "Продано книг", в А4 — "Дата", в В4 — "Всего", в С4 — "научная", в D4 — "техническая", в Е4 — "художественная", в С3 —"в том числе".

В ячейку А5 введем дату 15/03. В С5:Е5 введем числа 150, 200, 173. Выделим блок С5:F5 и щелкнем кнопку "Автосумма". В F5 появится формула =СУММ(С5:Е5). Перетащим ячейку F5 в ячейку В5. Таблица примет вид, как показано на рис. 6.2.

	А	В	С	D	Е	F
	Продано книг
			в том числе
	Дата	Всего	Научная	Техничес	Художественная
	15 мар

Рис. 6.2

Приступим к оформлению заголовка таблицы. Выделим ячейки А2:Е2 и щелкнем кнопку "Объединить и поместить в центре". В выпадающем списке "Размер" (шрифта) выберем размер 20 пунктов. Автоматически увеличится высота строки 2. Аналогично поступим с блоком СЗ:ЕЗ. Размер шрифта назначим 14 пунктов.

Выделим блок А4:В4. Нажмем клавиши Сtr1+1. Перейдем на вкладку "Выравнивание". Выберем вертикальную ориентацию текста. По вертикали выберем выравнивание по центру. Перейдем на вкладку "Шрифт" и выберем размер 14 пунктов. Щелкнем кнопку "ОК".

Выделим блок С4:Е4. Нажмем клавиши Сtr1+1. Перейдем на вкладку "Выравнивание". Выберем вертикальную ориентацию текста. По вертикали и горизонтали выберем выравнивание по центру. Перейдем на вкладку "Шрифт" и выберем размер 13 пунктов.

Слово "художественная" — слишком длинное и делает заголовок сильно вытянутым по вертикали. Чтобы этого избежать, вставим в это слово символ переноса и пробел: "художественная" (пробел нужен, чтобы Ехсеl воспринимал это как два слова). Выделим ячейку с этим словом, нажмем клавиши Сtr1+1 и на вкладке "Выравнивание" установим флажок "Переносить по словам". Переноса не произойдет — высота столбца еще увеличится. Схватим мышью нижнюю границу строки 4 на адресной полосе и принудительно уменьшим ее. Тогда строка "художест- венная" превратится в две строки.

Теперь выполним действие, которое стало возможным только в Ехсеl 97, — объединение ячеек по вертикали. Выделим блок АЗ:А4, нажмем клавиши Сtr1+1, выбираем вкладку "Выравнивание", установим флажок "объединение ячеек". То же проделаем с блоком ВЗ:В4.

Нарисуем рамки вокруг элементов заголовка. Выделим блок А2:Е4, нажмем Сtr1+1, выберем вкладку "Граница". Щелкнем кнопки "Внешняя" и "Внутренняя". В результате элементы заголовка будут разделены линиями. Можно выбрать разную толщину линий для границы общего заголовка в блоке А2:Е2 и других заголовков. Разберитесь самостоятельно, как это сделать.

Покрасим заголовок. Для этого выделим блок А2:Е4, нажмем Сtr1+1, выберем вкладку "Вид". Укажем на палитре цвет со слабой интенсивностью, например светло-голубой.

Отформатируем первую строку таблицы. Выделимее, установим выравнивание по центру и величину шрифта 12 пунктов. Введем еще несколько строк (при этом копируем ячейку с суммой). Чтобы копировать формат в новые строки, удобно пользоваться кнопкой "Формат по образцу" (на ней нарисована кисть).

Если нужно, чтобы в заголовках, состоящих из нескольких слов, слова располагались по строкам так, как нам требуется, а не произвольно, то нажимайте в местах принудительного переноса слов сочетание клавиш Аlt+Еntеr.

3. Условное форматирование

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

Перейдите на новый рабочий лист и назовите его "Условное форматирование".

ПРИМЕР 3.

Введите в А1 число 1, в блок В1:В15 арифметическую прогрессию 1, 2, ..., 15, в С1 введите формулу =$А$1*В1 и скопируйте ее в С2:С15. Скройте столбец В. Материал для экспериментов подготовлен. Требуется наложить на С1:С15 формат: если число лежит в диапазоне от 10 до 20, то выводится курсивом; если от 20 до 40, то выводится полужирным курсивом в рамке; если больше 40, то выводится красными цифрами на голубом фоне.

Решение. Выделите блок С1:С15. Выберите в меню: "Формат/ Условное форматирование". В диалоговом окне укажите для Условия 1: значение между 10 и 20 (заполняете четыре поля ввода), щелкните кнопку "Формат". Появится новое диалоговое окно с тремя вкладками. На вкладке "Шрифт" выберите "курсив" и щелкните "ОК". После этого щелкните кнопку "А также >>". Диалоговое окно расширится. Заполните "Условие 2", еще раз щелкните "А также >>" и заполните "Условие 3". Теперь вводите в ячейку А1 различные числа и прослеживайте, как изменяется формат ячеек в зависимости от выводимого значения.

Отменим “Условие 2”. Для этого выделите С1:С15, откройте окно "Условное форматирование" и щелкните кнопку "Удалить". Вам будет предложено новое диалоговое окно, где Вы сможете установить флажки для удаляемых условий. Установите флажок для условия 2.

ПРИМЕР 4.

Условное форматирование можно задавать формулой. Введем в Е1:Е15 прогрессию 1, 2, ..., 15. Нужно выделить полужирным шрифтом числа, кратные трем, но не кратные двум.

Решение. Выделим блок Е1:Е15, выберем в меню пункт "Формат/ Условное форматирование". В диалоговом окне укажите для Условия 1 в выпадающем списке не "значение", как в предыдущем примере, а "формула". В следующем поле ввода введите формулу =И(ОСТАТ(E1; 3) = 0; ОСТАТ(E1; 2) <> 0). Щелкните кнопку "Формат" и выберите полужирный шрифт. Будут выделены числа 3, 9,15.

Проанализируем введенную формулу. Она должна возвращать значение ИСТИНА или ЛОЖЬ. В ней фигурирует относительный адрес активной ячейки, входящей в блок. Напомню, что в выделенном блоке активная ячейка показана другим цветом.

Три интересных примера приведены в Справке. Найдите раздел "Форматирование листов/ Форматирование текста и ячеек/ Выделение данных, отвечающих определенным свойствам". (В Ехсеl 2000 — "Форматирование листов/ Условное форматирование/ Формула в качестве критерия форматирования".)