Условное форматирование
Об условном форматировании
Условное форматирование применяют для выделения из общего массива данных, отвечающих некоторым условиям. Условное форматирование можно применять как к данным, введенным с клавиатуры, так и к результатам вычислений. Чаще всего условное форматирование применяют к числовым данным.
Данные, отвечающие заданным условиям, выделяются специальными элементами оформления или измененным форматированием (параметры шрифта, границы, заливки).
Условный формат можно установить для одной ячейки или диапазона ячеек. На ячейку или диапазон ячеек можно накладывать до нескольких разных условий.
Установка условного форматирования ограничивает обычное форматирование ячеек: параметры формата, определяемые наложенным условием, невозможно изменить обычными средствами.
Выделение значений
С помощью условного форматирования можно выделить значения, отвечающие какому либо условию (больше, меньше, между, равно и др.).
Выделите ячейку или диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила выделения ячеек, а затем в подчиненном меню выберите условие (рис.10.1).
(рис 10.1) Выбор правила выделения значенийрис. 10.1
Настройте параметры условия и выберите способ выделения (рис.10.2).
(рис 10.2) Настройка параметров условного форматированиярис. 10.2
Название и содержание окна настройки параметров условия зависит от выбранного условия. Например, при выборе условия Между можно указать минимальное и максимальное значения, а также выбрать способ выделения (см. рис.10.2).
При выборе условия Дата можно выбрать отношение выделяемых ячеек к сегодняшней дате (Вчера, Сегодня, Завтра, За последние 7 дней и т.д.) и выбрать способ выделения (рис.10.3)
(рис 10.3) Настройка параметров условного форматирования для датрис. 10.3
Выделение крайних значений
С помощью условного форматирования можно выделить максимальные или минимальные значения.
Выделите ячейку или диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила отбора первых и последних значений, а затем в подчиненном меню выберите принцип отбора (рис.10.4).
(рис 10.4) Выбор правила отбора крайних значенийрис. 10.4
Настройте параметры отбора и выберите способ выделения (рис.10.5).
(рис 10.5) Настройка параметров отбора максимальных значенийрис. 10.5
Название и содержание окна настройки параметров отбора зависит от выбранного принципа. Например, при выборе 10 первых элементов указать количество отбираемых значений, а также выбрать способ выделения (см. рис.10.5).
Форматирование с использованием гистограммы
Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее - тем выше значение.
Выделите диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Гистограммы, а затем в подчиненном выберите цветовое оформление (рис.10.6). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмент листа отображается с указанным оформлением.
(рис 10.6) Условное форматирование с использованием гистограммырис. 10.6
Форматирование с использованием трехцветной шкалы
Цветовые шкалы помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в красно-желто-зеленой шкале можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средним значением будут желтыми, а ячейки с низкими значениями будут красными.
Выделите диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Цветовые шкалы, а затем в подчиненном выберите цветовую схему (рис.10.7). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмент листа отображается с указанным оформлением.
(рис 10.7) Условное форматирование с использованием трехцветной шкалырис. 10.7
Форматирование с использованием набора значков
Набор значков используется для аннотирования и классификации данных по трем-пяти категориям, разделенным пороговым значением. Каждый значок соответствует диапазону значений. Например, в наборе значков красная стрелка вверх соответствует высоким значениям, желтая средняя стрелка, направленная в сторону, соответствует средним значениям, а зеленая стрелка вниз соответствует низким значениям.
Выделите диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Наборы значков, а затем в подчиненном выберите набор значков (рис.10.8). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.
(рис 10.8) Условное форматирование с использованием набора значковрис. 10.8
Управление правилами условного форматирования
Правила условного форматирования можно изменять и удалять.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная и выберите команду Управление правилами (см. рис.10.1).
В окне Диспетчер правил условного форматирования в поле списка Показать правила форматирования для выберите лист, для которого производятся изменение правил (рис.10.9).
(рис 10.9) Управление правилами условного форматированиярис. 10.9
Выделите правило, которое надо изменить и нажмите кнопку Изменить правило.
В окне Изменение правила форматирования (рис.10.10) можно выбрать тип другого правила, изменить условие и параметры форматирования.
(рис 10.10) Изменение правил условного форматированиярис. 10.10
При наличии нескольких правил для одного диапазона изменить кнопками Верх и Вниз (см. рис.10.9) можно изменить порядок их применения.
Для удаления правила нажмите кнопку Удалить правило (см. рис.10.9).
Для удаления сразу всех правил условного форматирования в выделенном диапазоне или на листе щелкните по кнопке Условное форматирование группы Стили вкладки Главная, выберите команду Удалить правила, а затем в подчиненном меню нужную команду (рис.10.11).
(рис 10.11) Удаление правил условного форматированиярис. 10.11
Поиск ячеек с условным форматированием
Для выделения всех диапазонов листа, к ячейкам которых применено условное форматирование в группе Редактирование вкладки Главная нажмите кнопку Найти и выделить и в меню выберите команду Условное форматирование (рис.10.12).
(рис 10.12) Поиск ячеек с условным форматированиемрис. 10.12
Создание и оформление "Таблиц"
О таблицах
Данные, организованные на листе, можно оформить как "Таблицу". При создании таблицы можно анализировать ее данные и управлять ими независимо от данных за пределами таблицы. На листе можно создать любое количество таблиц.
Таблица может использоваться для быстрой сортировки, отбора, суммирования или публикации данных, содержащихся в ней.
С другой стороны, наличие таблицы на листе и в книге создает и некоторые проблемы. Так, например, таблицу нельзя структурировать. Нельзя использовать и автоматическое подведение итогов.
Создание таблицы
Таблица обычно создается на основе имеющихся на листе данных.
Выделите любую ячейку в диапазоне данных.
Нажмите кнопку Форматировать как таблицу в группе Стили вкладки Главная и выберите стиль оформления (рис.10.13). Предпросмотр в данной ситуации не работает.
(рис 10.13) Выбор стиля оформления таблицырис. 10.13
В поле окна Форматирование таблицы будет автоматически указан диапазон данных, который преобразуется в таблицу (рис.10.14). При необходимости можно очистить поле и на листе выделить другой диапазон ячеек с данными, которые оформляются в виде таблицы. Нажмите кнопку ОК .
(рис 10.14) Создание таблицырис. 10.14
В результате будет создана таблица. В каждый столбец автоматически добавляется значок автофильтра. Автоматически будет отображена контекстная вкладка Работа с таблицами/Конструктор (рис.10.15).
(рис 10.15) Таблицарис. 10.15
Работа с таблицей
Помимо обычных инструментов, для работы с таблицей можно использовать элементы вкладки Конструктор (см. рис.10.15). Эта вкладка автоматически появляется при выделении любой ячейки таблицы и также автоматически скрывается при выделении любой ячейки вне таблицы.
Можно применить к таблице другой стиль оформления.
Выделите любую ячейку таблицы.
Нажмите кнопку Экспресс-стили во вкладке Конструктор и выберите стиль оформления (рис.10.16). Предпросмотр в данной ситуации работает и таблица отображается с указанным оформлением.
(рис 10.16) Изменение стиля оформления таблицырис. 10.16
Для изменения стиля оформления таблицы можно также нажать кнопку Форматировать как таблицу в группе Стили вкладки Главная и выбрать стиль оформления.
Во вкладке Конструктор в группе Параметры стилей таблицы (рис.10.17) можно настроить особенности параметров оформления таблицы.
(рис 10.17) Изменение стиля оформления таблицырис. 10.17
Для включения или отключения строки заголовков установите или снимите флажок Строка заголовка.
Для включения или отключения итоговой строки установите или снимите флажок Строка итогов.
Для отображения специального форматирования первого столбца таблицы установите или снимите флажок Первый столбец.
Для отображения специального форматирования последнего столбца таблицы установите или снимите флажок Последний столбец.
Для различного отображения четных и нечетных строк в целях облегчения чтения установите или снимите флажок Чередующиеся строки.
Для различного отображения четных и нечетных столбцов в целях облегчения чтения установите или снимите флажок Чередующиеся столбцы.
Преобразование таблицы в диапазон
Таблицу можно преобразовать в обычный диапазон данных. Все установленное форматирование при этом сохранится.
Выделите любую ячейку таблицы.
Нажмите кнопку Преобразовать в диапазон в группе Сервис вкладки Конструктор (см. рис. 16 или рис.10.17).
В окне запроса на преобразование нажмите кнопку Да.
Использование стилей
О стилях
Использование стилей обеспечивает единообразие оформления данных и ячеек во всей книге, позволяет быстро устанавливать выбранный набор параметров форматирования, а также мгновенно изменять оформление всех ячеек, к которым применен один стиль.
Стилем называется набор параметров форматирования данных и ячеек: числовой формат; выравнивание; шрифт; граница; заливка; защита. Стиль не обязательно должен включать в себя все параметры форматирования.
При изменении параметров стиля автоматически изменяется оформление всех ячеек, к которым применен изменяемый стиль.
Для ячеек, к которым применен какой-либо стиль, можно применять также и любое другое оформление. Параметры оформления, установленные после применения стиля, не будут автоматически изменяться при изменении стиля.
Стиль сохраняется вместе с книгой, в которой он создан. Стили можно копировать из одной книги в другую.
Применение стиля
Выделите ячейку или диапазон ячеек.
Щелкните по кнопке Стили ячеек группы Стили вкладки Главная и выберите нужный стиль (рис.10.18). При наведении указателя мыши на выбираемый стиль срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.
(рис 10.18) Применение стилярис. 10.18
Изменение стиля
Изменение стиля можно начать еще при его создании. Можно изменять стиль и тогда, когда он уже создан и применен.
Выделите ячейку, оформленную изменяемым стилем.
Щелкните по кнопке Стили ячеек группы Стили вкладки Главная.
В галерее стилей (см. рис.10.18) щелкните правой кнопкой мыши по названию изменяемого стиля и в контекстном меню выберите команду Изменить.
В диалоговом окне Стиль нажмите кнопку Формат (рис.10.19).
(рис 10.19) Изменение стилярис. 10.19
Во вкладках диалогового окна Формат ячеек (рис.10.20) установите параметры оформления стиля.
(рис 10.20) Изменение параметров оформления стилярис. 10.20
В диалоговом окне Формат ячеек (см. рис.10.20) нажмите кнопку ОК. В диалоговом окне Стиль (см. рис.10.19) нажмите кнопку ОК.
После изменения стиля автоматически изменится оформление всех ячеек, к которым был применен изменяемый стиль.
Следует с осторожностью изменять стиль Обычный, поскольку именно этот стиль установлен по умолчанию ко всем ячейкам. Изменение стиля Обычный приведет к изменению оформления не только всех ячеек с данными, но и всех пустых ячеек, в том числе и во вновь вставляемых листах.
Удаление стиля
Из книги можно удалить любой стиль, как встроенный, так и пользовательский, кроме стиля Обычный.
Щелкните по кнопке Стили ячеек группы Стили вкладки Главная.
В галерее стилей (см. рис.10.18) щелкните правой кнопкой мыши по названию изменяемого стиля и в контекстном меню выберите команду Удалить.
Стили удаляются только из текущей книги. При создании новой книги она будет содержать все встроенные стили.
Отменить удаления стиля невозможно.
Для восстановления в книге встроенных стилей можно создать новую книгу, а затем копировать из нее все стили.
Применение темы
О темах
Тема - набор унифицированных элементов, определяющих внешний вид документа с помощью цвета, шрифтов и графических объектов.
Можно легко и быстро отформатировать весь документ, придав ему профессиональный и современный вид путем применения темы документа. Тема документа представляет собой набор вариантов форматирования, включающих цветовую тему (набор цветов), тему шрифтов (набор шрифтов заголовков и основного текста) и тему эффектов (набор линий и заливок).
Применение темы
Можно изменить тему документа, используемую по умолчанию, выбрав другую заранее определенную или пользовательскую тему документа.
Во вкладке Разметка страницы щелкните кнопку Темы и выберите тему документа (рис.10.21). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанным оформлением.
(рис 10.21) Выбор темырис. 10.21
При выборе темы выделять какие-либо ячейки нет необходимости. Параметры темы будет применены ко всем листам книги.
Настройка темы
Можно изменить отдельные параметры оформления, характерные для выбранной темы.
Выбор темы определяет набор шрифтов (основного и для заголовков). Для изменения шрифтов темы во вкладке Разметка страницы щелкните кнопку Шрифты и выберите нужный набор шрифтов (рис.10.22). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанными шрифтами.
(рис 10.22) Выбор шрифтов темырис. 10.22
Выбор набора шрифтов не означает, что при оформлении нельзя использовать другие шрифты.
Для изменения цветового оформления темы во вкладке Разметка страницы щелкните кнопку Цвета и выберите нужный набор цветов (рис.10.23). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанными цветами.
(рис 10.23) Выбор цветов темырис. 10.23
Выбор набора цветов не означает, что при оформлении нельзя использовать другие цвета.
Сохранение пользовательской темы
Измененную тему можно сохранить для последующего использования.
Во вкладке Разметка страницы щелкните кнопку Темы и выберите команду Сохранить текущую тему (см. рис.10.21).
В окне Сохранение текущей темы в поле Имя файла введите имя сохраняемой темы (рис.10.24) и нажмите кнопку Сохранить. Не рекомендуется изменять папку, в которой сохраняется файл темы.
(рис 10.24) Сохранение пользовательской темырис. 10.24
Пользовательские сохраненные темы отображаются в верхней части галереи тем (см. рис.10.21).
Копирование форматов
Копирование формата по образцу
Простейшим и наиболее быстрым способом копирования оформления одной ячейки на другие ячейки является использование кнопки Формат по образцу группы Буфер обмена вкладки Главная.
Выделите ячейку, оформление которой требуется копировать.
Нажмите кнопку Формат по образцу (рис.10.25).
После этого кнопка Формат по образцу останется нажатой, а справа от указателя мыши появится значок копирования формата.
Выделите ячейку или диапазон ячеек, для которых устанавливается копируемый формат (рис.10.25).
(рис 10.25) Копирование формата с использованием кнопки Формат по образцурис. 10.25
Предложенный способ пригоден для однократного копирования формата.
Если требуется многократно применять оформление выбранной ячейки, то необходимо два раза щелкнуть по кнопке Формат по образцу (щелкать надо быстро, чтобы кнопка осталась нажатой), а затем выделять разные фрагменты. Для окончания копирования формата нажмите клавишу Esc или кнопку Формат по образцу, чтобы она пришла в обычное состояние.
Копирование формата по образцу используют чаще всего при копировании оформления в пределах видимой части одного листа. Но этот способ можно использовать и для копирования оформления на другие листы и другие книги.
Копирование автозаполнением
Копирование автозаполнением используют при копировании оформления одной ячейки на рядом расположенные ячейки.
Выделите ячейку с копируемым оформлением
Перетащите маркер автозаполнения, как это делается при обычном автозаполнении. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки
Щелкните по кнопке Параметры автозаполнения в правом нижнем углу области заполнения и выберите команду Заполнить только форматы (рис.10.26).
(рис 10.26) Копирование формата автозаполнениемрис. 10.26
Следует обратить внимание, что воспользоваться кнопкой Параметры автозаполнения можно только в течение некоторого времени после копирования. При выполнении некоторых действий (сохранение файла, копирование и вставка ячеек и др.) кнопка исчезает, и воспользоваться копированием формата не удастся.
Копирование с использованием буфера обмена
Копирование форматов с использованием буфера обмена обычно используют при копировании оформления на ячейки других листов или книг.
Ячейку с копируемым форматом скопируйте в буфер обмена.
Выделите ячейки, на которые копируется оформление.
Извлеките скопированную ячейку из буфера обмена. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки.
Щелкните по кнопке Параметры вставки в правом нижнем углу области вставки и выберите режим Форматирование (рис.10.27)
(рис 10.27) Копирование формата с использованием буфера обменарис. 10.27
Следует обратить внимание, что воспользоваться кнопкой Параметры вставки можно только в течение некоторого времени после копирования. При выполнении некоторых действий (сохранение файла, копирование и вставка ячеек и др.) кнопка исчезает, и воспользоваться копированием формата не удастся.
Копирование с использованием специальной вставки
Копирование форматов с использованием специальной вставки чаще всего используют при копировании оформления на ячейки других листов или книг. Этот способ несколько более трудоемок, чем с использованием буфера обмена и обычной вставки. С другой стороны, он более надежен, так как снижает риск вставки не только формата, но и данных из копируемой ячейки.
Ячейку с копируемым форматом скопируйте в буфер обмена.
Выделите ячейки, на которые копируется оформление.
Щелкните по стрелке кнопки Вставить в группе Буфер обмена вкладки Главная и выберите команду Специальная вставка или щелкните по выделенной области правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка.
В диалоговом окне Специальная вставка установите переключатель форматы (рис.10.28). Нажмите кнопку ОК.
(рис 10.28) Копирование формата с использованием специальной вставкирис. 10.28
Очистка (удаление) форматирования
Можно удалить сразу все параметры оформления (числовые форматы, параметры выравнивания, параметры шрифта, заливки, границы и т. д.).
Выделите ячейку или диапазон ячеек, для которых удаляется оформление.
В группе Редактирование вкладки Главная щелкните по кнопке Очистить и выберите команду Очистить форматы (рис.10.29).
(рис 10.29) Очистка форматированиярис. 10.29
Упражнение 10
Файлы к данному упражнению Вы можете скачать здесь.
Запустите Microsoft Excel 2010.
Задание 1
Откройте файл exercise_10_1.xlsx.
Перейдите к листу Лист 1.
С использованием условного форматирования в столбце С выделите ячейки со значениями более 60.
С использованием условного форматирования в столбце Е выделите 10 ячеек с максимальными значениями.
Примените условное форматирование с использованием гистограмм для ячеек столбца Е.
Примените условное форматирование с использованием цветовых схем для ячеек столбца F.
Примените условное форматирование с использованием значков для ячеек столбца D.
Перейдите к листу Лист 2.
Удалите все условные форматы.
Задание 2
Перейдите к листу Лист 3.
Отформатируйте данные на листе как таблицу. Выберите стиль оформления таблицы Светлый 9.
Перейдите к листу Лист 4.
Примените к таблице стиль оформления таблицы Средний 9.
Добавьте к таблице сроку итогов.
Перейдите к листу Лист 5.
Преобразуйте имеющуюся таблицу в обычный диапазон.
Задание 3
Откройте файл exercise_10_2.xlsx.
Перейдите к листу Лист 1.
Для ячеек А1:F1 установите стиль Название.
Для ячеек А2:F2 установите стиль Заголовок 2.
Для ячеек А8:F8 установите стиль Итог.
Для ячеек А3:F6 установите стиль Нейтральный.
Измените параметры стиля Нейтральный: цвет шрифта установите темно-синий.
Задание 4
Откройте файл exercise_10_3.xlsx.
Просмотрите листы книги.
Перейдите к листу Лист 4.
Примените к книге тему Городская. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось.
Измените цветовое оформление темы. Выберите цветовую схему Официальная. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось.
Измените шрифты темы. Выберите схему Классическая. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось.
Сохраните установленное оформление как тему под именем Новая.
Откройте файл exercise_10_4.xlsx.
Просмотрите листы книги.
Примените к книге созданную ранее тему Новая. Просмотрите листы книги и убедитесь, что оформление таблиц изменилось.
Задание 5
Откройте файл exercise_10_5.xlsx.
Перейдите к листу Лист 1.
Используя возможности кнопки Формат по образцу скопируйте формат ячейки А2 на ячейки В2:В14.
Используя возможности автозаполнения скопируйте формат ячейки А2 на ячейки А3:А14.
С использованием возможностей специальной вставки скопируйте формат ячейки А2 на ячейки А2:В14 листа Лист 2.
На листе Лист 1 очистите форматы ячеек G2:H14.
Сохраните файл exercise_10_1.xlsx под именем Lesson_10_1. Сохраните файл exercise_10_2.xlsx под именем Lesson_10_2. Сохраните файл exercise_10_3.xlsx под именем Lesson_10_3. Сохраните файл exercise_10_4.xlsx под именем Lesson_10_4. Сохраните файл exercise_10_5.xlsx под именем Lesson_10_5.
Закройте Microsoft Excel 2010.
Условное форматирование
Об условном форматировании
Условное форматирование применяют для выделения из общего массива данных, отвечающих некоторым условиям. Условное форматирование можно применять как к данным, введенным с клавиатуры, так и к результатам вычислений. Чаще всего условное форматирование применяют к числовым данным.
Данные, отвечающие заданным условиям, выделяются специальными элементами оформления или измененным форматированием (параметры шрифта, границы, заливки).
Условный формат можно установить для одной ячейки или диапазона ячеек. На ячейку или диапазон ячеек можно накладывать до нескольких разных условий.
Установка условного форматирования ограничивает обычное форматирование ячеек: параметры формата, определяемые наложенным условием, невозможно изменить обычными средствами.
Выделение значений
С помощью условного форматирования можно выделить значения, отвечающие какому либо условию (больше, меньше, между, равно и др.).
Выделите ячейку или диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила выделения ячеек, а затем в подчиненном меню выберите условие (рис.10.1).(рис 10.1) Выбор правила выделения значенийрис. 10.1
Настройте параметры условия и выберите способ выделения (рис.10.2).(рис 10.2) Настройка параметров условного форматированиярис. 10.2
Название и содержание окна настройки параметров условия зависит от выбранного условия. Например, при выборе условия Между можно указать минимальное и максимальное значения, а также выбрать способ выделения (см. рис.10.2).
При выборе условия Дата можно выбрать отношение выделяемых ячеек к сегодняшней дате (Вчера, Сегодня, Завтра, За последние 7 дней и т.д.) и выбрать способ выделения (рис.10.3)
(рис 10.3) Настройка параметров условного форматирования для датрис. 10.3
Выделение крайних значений
С помощью условного форматирования можно выделить максимальные или минимальные значения.
Выделите ячейку или диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила отбора первых и последних значений, а затем в подчиненном меню выберите принцип отбора (рис.10.4).(рис 10.4) Выбор правила отбора крайних значенийрис. 10.4
Настройте параметры отбора и выберите способ выделения (рис.10.5).(рис 10.5) Настройка параметров отбора максимальных значенийрис. 10.5
Название и содержание окна настройки параметров отбора зависит от выбранного принципа. Например, при выборе 10 первых элементов указать количество отбираемых значений, а также выбрать способ выделения (см. рис.10.5).
Форматирование с использованием гистограммы
Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее - тем выше значение.
Выделите диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Гистограммы, а затем в подчиненном выберите цветовое оформление (рис.10.6). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмент листа отображается с указанным оформлением.(рис 10.6) Условное форматирование с использованием гистограммырис. 10.6
Форматирование с использованием трехцветной шкалы
Цветовые шкалы помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в красно-желто-зеленой шкале можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средним значением будут желтыми, а ячейки с низкими значениями будут красными.
Выделите диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Цветовые шкалы, а затем в подчиненном выберите цветовую схему (рис.10.7). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмент листа отображается с указанным оформлением.(рис 10.7) Условное форматирование с использованием трехцветной шкалырис. 10.7
Форматирование с использованием набора значков
Набор значков используется для аннотирования и классификации данных по трем-пяти категориям, разделенным пороговым значением. Каждый значок соответствует диапазону значений. Например, в наборе значков красная стрелка вверх соответствует высоким значениям, желтая средняя стрелка, направленная в сторону, соответствует средним значениям, а зеленая стрелка вниз соответствует низким значениям.
Выделите диапазон ячеек.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Наборы значков, а затем в подчиненном выберите набор значков (рис.10.8). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.(рис 10.8) Условное форматирование с использованием набора значковрис. 10.8
Управление правилами условного форматирования
Правила условного форматирования можно изменять и удалять.
Щелкните по кнопке Условное форматирование группы Стили вкладки Главная и выберите команду Управление правилами (см. рис.10.1).
В окне Диспетчер правил условного форматирования в поле списка Показать правила форматирования для выберите лист, для которого производятся изменение правил (рис.10.9).(рис 10.9) Управление правилами условного форматированиярис. 10.9
Выделите правило, которое надо изменить и нажмите кнопку Изменить правило.
В окне Изменение правила форматирования (рис.10.10) можно выбрать тип другого правила, изменить условие и параметры форматирования.(рис 10.10) Изменение правил условного форматированиярис. 10.10
При наличии нескольких правил для одного диапазона изменить кнопками Верх и Вниз (см. рис.10.9) можно изменить порядок их применения.
Для удаления правила нажмите кнопку Удалить правило (см. рис.10.9).
Для удаления сразу всех правил условного форматирования в выделенном диапазоне или на листе щелкните по кнопке Условное форматирование группы Стили вкладки Главная, выберите команду Удалить правила, а затем в подчиненном меню нужную команду (рис.10.11).
(рис 10.11) Удаление правил условного форматированиярис. 10.11
Поиск ячеек с условным форматированием
Для выделения всех диапазонов листа, к ячейкам которых применено условное форматирование в группе Редактирование вкладки Главная нажмите кнопку Найти и выделить и в меню выберите команду Условное форматирование (рис.10.12).
(рис 10.12) Поиск ячеек с условным форматированиемрис. 10.12
Создание и оформление "Таблиц"
О таблицах
Данные, организованные на листе, можно оформить как "Таблицу". При создании таблицы можно анализировать ее данные и управлять ими независимо от данных за пределами таблицы. На листе можно создать любое количество таблиц.
Таблица может использоваться для быстрой сортировки, отбора, суммирования или публикации данных, содержащихся в ней.
С другой стороны, наличие таблицы на листе и в книге создает и некоторые проблемы. Так, например, таблицу нельзя структурировать. Нельзя использовать и автоматическое подведение итогов.
Создание таблицы
Таблица обычно создается на основе имеющихся на листе данных.
Выделите любую ячейку в диапазоне данных.
Нажмите кнопку Форматировать как таблицу в группе Стили вкладки Главная и выберите стиль оформления (рис.10.13). Предпросмотр в данной ситуации не работает.(рис 10.13) Выбор стиля оформления таблицырис. 10.13
В поле окна Форматирование таблицы будет автоматически указан диапазон данных, который преобразуется в таблицу (рис.10.14). При необходимости можно очистить поле и на листе выделить другой диапазон ячеек с данными, которые оформляются в виде таблицы. Нажмите кнопку ОК .(рис 10.14) Создание таблицырис. 10.14
В результате будет создана таблица. В каждый столбец автоматически добавляется значок автофильтра. Автоматически будет отображена контекстная вкладка Работа с таблицами/Конструктор (рис.10.15).
(рис 10.15) Таблицарис. 10.15
Работа с таблицей
Помимо обычных инструментов, для работы с таблицей можно использовать элементы вкладки Конструктор (см. рис.10.15). Эта вкладка автоматически появляется при выделении любой ячейки таблицы и также автоматически скрывается при выделении любой ячейки вне таблицы.
Можно применить к таблице другой стиль оформления.
Выделите любую ячейку таблицы.
Нажмите кнопку Экспресс-стили во вкладке Конструктор и выберите стиль оформления (рис.10.16). Предпросмотр в данной ситуации работает и таблица отображается с указанным оформлением.(рис 10.16) Изменение стиля оформления таблицырис. 10.16
Для изменения стиля оформления таблицы можно также нажать кнопку Форматировать как таблицу в группе Стили вкладки Главная и выбрать стиль оформления.
Во вкладке Конструктор в группе Параметры стилей таблицы (рис.10.17) можно настроить особенности параметров оформления таблицы.
(рис 10.17) Изменение стиля оформления таблицырис. 10.17
Для включения или отключения строки заголовков установите или снимите флажок Строка заголовка.
Для включения или отключения итоговой строки установите или снимите флажок Строка итогов.
Для отображения специального форматирования первого столбца таблицы установите или снимите флажок Первый столбец.
Для отображения специального форматирования последнего столбца таблицы установите или снимите флажок Последний столбец.
Для различного отображения четных и нечетных строк в целях облегчения чтения установите или снимите флажок Чередующиеся строки.
Для различного отображения четных и нечетных столбцов в целях облегчения чтения установите или снимите флажок Чередующиеся столбцы.
Преобразование таблицы в диапазон
Таблицу можно преобразовать в обычный диапазон данных. Все установленное форматирование при этом сохранится.
Выделите любую ячейку таблицы.
Нажмите кнопку Преобразовать в диапазон в группе Сервис вкладки Конструктор (см. рис. 16 или рис.10.17).
В окне запроса на преобразование нажмите кнопку Да.
Использование стилей
О стилях
Использование стилей обеспечивает единообразие оформления данных и ячеек во всей книге, позволяет быстро устанавливать выбранный набор параметров форматирования, а также мгновенно изменять оформление всех ячеек, к которым применен один стиль.
Стилем называется набор параметров форматирования данных и ячеек: числовой формат; выравнивание; шрифт; граница; заливка; защита. Стиль не обязательно должен включать в себя все параметры форматирования.
При изменении параметров стиля автоматически изменяется оформление всех ячеек, к которым применен изменяемый стиль.
Для ячеек, к которым применен какой-либо стиль, можно применять также и любое другое оформление. Параметры оформления, установленные после применения стиля, не будут автоматически изменяться при изменении стиля.
Стиль сохраняется вместе с книгой, в которой он создан. Стили можно копировать из одной книги в другую.
Применение стиля
Выделите ячейку или диапазон ячеек.
Щелкните по кнопке Стили ячеек группы Стили вкладки Главная и выберите нужный стиль (рис.10.18). При наведении указателя мыши на выбираемый стиль срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.(рис 10.18) Применение стилярис. 10.18
Изменение стиля
Изменение стиля можно начать еще при его создании. Можно изменять стиль и тогда, когда он уже создан и применен.
Выделите ячейку, оформленную изменяемым стилем.
Щелкните по кнопке Стили ячеек группы Стили вкладки Главная.
В галерее стилей (см. рис.10.18) щелкните правой кнопкой мыши по названию изменяемого стиля и в контекстном меню выберите команду Изменить.
В диалоговом окне Стиль нажмите кнопку Формат (рис.10.19).(рис 10.19) Изменение стилярис. 10.19
Во вкладках диалогового окна Формат ячеек (рис.10.20) установите параметры оформления стиля.(рис 10.20) Изменение параметров оформления стилярис. 10.20
В диалоговом окне Формат ячеек (см. рис.10.20) нажмите кнопку ОК. В диалоговом окне Стиль (см. рис.10.19) нажмите кнопку ОК.
После изменения стиля автоматически изменится оформление всех ячеек, к которым был применен изменяемый стиль.
Следует с осторожностью изменять стиль Обычный, поскольку именно этот стиль установлен по умолчанию ко всем ячейкам. Изменение стиля Обычный приведет к изменению оформления не только всех ячеек с данными, но и всех пустых ячеек, в том числе и во вновь вставляемых листах.
Удаление стиля
Из книги можно удалить любой стиль, как встроенный, так и пользовательский, кроме стиля Обычный.
Щелкните по кнопке Стили ячеек группы Стили вкладки Главная.
В галерее стилей (см. рис.10.18) щелкните правой кнопкой мыши по названию изменяемого стиля и в контекстном меню выберите команду Удалить.
Стили удаляются только из текущей книги. При создании новой книги она будет содержать все встроенные стили.
Отменить удаления стиля невозможно.
Для восстановления в книге встроенных стилей можно создать новую книгу, а затем копировать из нее все стили.
Применение темы
О темах
Тема - набор унифицированных элементов, определяющих внешний вид документа с помощью цвета, шрифтов и графических объектов.
Можно легко и быстро отформатировать весь документ, придав ему профессиональный и современный вид путем применения темы документа. Тема документа представляет собой набор вариантов форматирования, включающих цветовую тему (набор цветов), тему шрифтов (набор шрифтов заголовков и основного текста) и тему эффектов (набор линий и заливок).
Применение темы
Можно изменить тему документа, используемую по умолчанию, выбрав другую заранее определенную или пользовательскую тему документа.
Во вкладке Разметка страницы щелкните кнопку Темы и выберите тему документа (рис.10.21). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанным оформлением.
(рис 10.21) Выбор темырис. 10.21
При выборе темы выделять какие-либо ячейки нет необходимости. Параметры темы будет применены ко всем листам книги.
Настройка темы
Можно изменить отдельные параметры оформления, характерные для выбранной темы.
Выбор темы определяет набор шрифтов (основного и для заголовков). Для изменения шрифтов темы во вкладке Разметка страницы щелкните кнопку Шрифты и выберите нужный набор шрифтов (рис.10.22). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанными шрифтами.
(рис 10.22) Выбор шрифтов темырис. 10.22
Выбор набора шрифтов не означает, что при оформлении нельзя использовать другие шрифты.
Для изменения цветового оформления темы во вкладке Разметка страницы щелкните кнопку Цвета и выберите нужный набор цветов (рис.10.23). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанными цветами.
(рис 10.23) Выбор цветов темырис. 10.23
Выбор набора цветов не означает, что при оформлении нельзя использовать другие цвета.
Сохранение пользовательской темы
Измененную тему можно сохранить для последующего использования.
Во вкладке Разметка страницы щелкните кнопку Темы и выберите команду Сохранить текущую тему (см. рис.10.21).
В окне Сохранение текущей темы в поле Имя файла введите имя сохраняемой темы (рис.10.24) и нажмите кнопку Сохранить. Не рекомендуется изменять папку, в которой сохраняется файл темы.(рис 10.24) Сохранение пользовательской темырис. 10.24
Пользовательские сохраненные темы отображаются в верхней части галереи тем (см. рис.10.21).
Копирование форматов
Копирование формата по образцу
Простейшим и наиболее быстрым способом копирования оформления одной ячейки на другие ячейки является использование кнопки Формат по образцу группы Буфер обмена вкладки Главная.
Выделите ячейку, оформление которой требуется копировать.
Нажмите кнопку Формат по образцу (рис.10.25).
После этого кнопка Формат по образцу останется нажатой, а справа от указателя мыши появится значок копирования формата.
Выделите ячейку или диапазон ячеек, для которых устанавливается копируемый формат (рис.10.25).(рис 10.25) Копирование формата с использованием кнопки Формат по образцурис. 10.25
Предложенный способ пригоден для однократного копирования формата.
Если требуется многократно применять оформление выбранной ячейки, то необходимо два раза щелкнуть по кнопке Формат по образцу (щелкать надо быстро, чтобы кнопка осталась нажатой), а затем выделять разные фрагменты. Для окончания копирования формата нажмите клавишу Esc или кнопку Формат по образцу, чтобы она пришла в обычное состояние.
Копирование формата по образцу используют чаще всего при копировании оформления в пределах видимой части одного листа. Но этот способ можно использовать и для копирования оформления на другие листы и другие книги.
Копирование автозаполнением
Копирование автозаполнением используют при копировании оформления одной ячейки на рядом расположенные ячейки.
Выделите ячейку с копируемым оформлением
Перетащите маркер автозаполнения, как это делается при обычном автозаполнении. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки
Щелкните по кнопке Параметры автозаполнения в правом нижнем углу области заполнения и выберите команду Заполнить только форматы (рис.10.26).(рис 10.26) Копирование формата автозаполнениемрис. 10.26
Следует обратить внимание, что воспользоваться кнопкой Параметры автозаполнения можно только в течение некоторого времени после копирования. При выполнении некоторых действий (сохранение файла, копирование и вставка ячеек и др.) кнопка исчезает, и воспользоваться копированием формата не удастся.
Копирование с использованием буфера обмена
Копирование форматов с использованием буфера обмена обычно используют при копировании оформления на ячейки других листов или книг.
Ячейку с копируемым форматом скопируйте в буфер обмена.
Выделите ячейки, на которые копируется оформление.
Извлеките скопированную ячейку из буфера обмена. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки.
Щелкните по кнопке Параметры вставки в правом нижнем углу области вставки и выберите режим Форматирование (рис.10.27)(рис 10.27) Копирование формата с использованием буфера обменарис. 10.27
Следует обратить внимание, что воспользоваться кнопкой Параметры вставки можно только в течение некоторого времени после копирования. При выполнении некоторых действий (сохранение файла, копирование и вставка ячеек и др.) кнопка исчезает, и воспользоваться копированием формата не удастся.
Копирование с использованием специальной вставки
Копирование форматов с использованием специальной вставки чаще всего используют при копировании оформления на ячейки других листов или книг. Этот способ несколько более трудоемок, чем с использованием буфера обмена и обычной вставки. С другой стороны, он более надежен, так как снижает риск вставки не только формата, но и данных из копируемой ячейки.
Ячейку с копируемым форматом скопируйте в буфер обмена.
Выделите ячейки, на которые копируется оформление.
Щелкните по стрелке кнопки Вставить в группе Буфер обмена вкладки Главная и выберите команду Специальная вставка или щелкните по выделенной области правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка.
В диалоговом окне Специальная вставка установите переключатель форматы (рис.10.28). Нажмите кнопку ОК.(рис 10.28) Копирование формата с использованием специальной вставкирис. 10.28
Очистка (удаление) форматирования
Можно удалить сразу все параметры оформления (числовые форматы, параметры выравнивания, параметры шрифта, заливки, границы и т. д.).
Выделите ячейку или диапазон ячеек, для которых удаляется оформление.
В группе Редактирование вкладки Главная щелкните по кнопке Очистить и выберите команду Очистить форматы (рис.10.29).(рис 10.29) Очистка форматированиярис. 10.29
Упражнение 10
Файлы к данному упражнению Вы можете скачать здесь.
Запустите Microsoft Excel 2010.
Задание 1
Откройте файл exercise_10_1.xlsx.
Перейдите к листу Лист 1.
С использованием условного форматирования в столбце С выделите ячейки со значениями более 60.
С использованием условного форматирования в столбце Е выделите 10 ячеек с максимальными значениями.
Примените условное форматирование с использованием гистограмм для ячеек столбца Е.
Примените условное форматирование с использованием цветовых схем для ячеек столбца F.
Примените условное форматирование с использованием значков для ячеек столбца D.
Перейдите к листу Лист 2.
Удалите все условные форматы.
Задание 2
Перейдите к листу Лист 3.
Отформатируйте данные на листе как таблицу. Выберите стиль оформления таблицы Светлый 9.
Перейдите к листу Лист 4.
Примените к таблице стиль оформления таблицы Средний 9.
Добавьте к таблице сроку итогов.
Перейдите к листу Лист 5.
Преобразуйте имеющуюся таблицу в обычный диапазон.
Задание 3
Откройте файл exercise_10_2.xlsx.
Перейдите к листу Лист 1.
Для ячеек А1:F1 установите стиль Название.
Для ячеек А2:F2 установите стиль Заголовок 2.
Для ячеек А8:F8 установите стиль Итог.
Для ячеек А3:F6 установите стиль Нейтральный.
Измените параметры стиля Нейтральный: цвет шрифта установите темно-синий.
Задание 4
Откройте файл exercise_10_3.xlsx.
Просмотрите листы книги.
Перейдите к листу Лист 4.
Примените к книге тему Городская. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось.
Измените цветовое оформление темы. Выберите цветовую схему Официальная. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось.
Измените шрифты темы. Выберите схему Классическая. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось.
Сохраните установленное оформление как тему под именем Новая.
Откройте файл exercise_10_4.xlsx.
Просмотрите листы книги.
Примените к книге созданную ранее тему Новая. Просмотрите листы книги и убедитесь, что оформление таблиц изменилось.
Задание 5
Откройте файл exercise_10_5.xlsx.
Перейдите к листу Лист 1.
Используя возможности кнопки Формат по образцу скопируйте формат ячейки А2 на ячейки В2:В14.
Используя возможности автозаполнения скопируйте формат ячейки А2 на ячейки А3:А14.
С использованием возможностей специальной вставки скопируйте формат ячейки А2 на ячейки А2:В14 листа Лист 2.
На листе Лист 1 очистите форматы ячеек G2:H14.
Сохраните файл exercise_10_1.xlsx под именем Lesson_10_1. Сохраните файл exercise_10_2.xlsx под именем Lesson_10_2. Сохраните файл exercise_10_3.xlsx под именем Lesson_10_3. Сохраните файл exercise_10_4.xlsx под именем Lesson_10_4. Сохраните файл exercise_10_5.xlsx под именем Lesson_10_5.
Закройте Microsoft Excel 2010.
