Microsoft Excel 2010

Форматирование таблиц. Условное форматирование, применение стилей и тем.

Лекция посвящена вопросам повышения эффективности оформления таблиц с использованием условного форматирования, применения стилей и тем. Дано понятие об условном форматировании и показаны его возможности. Представлены различные варианты условного форматирования с использованием средств Microsoft Excel 2010. Показана процедура установки условного формата и управления правилами. Описана возможность поиска ячеек с уловным форматированием. Показана процедура изменения условного формата. Дано представление о стилях. Показано применение стилей для оформления таблиц. Описана процедура изменения параметров стиля. Показано удаления стиля. Дано понятие темы документа. Показано применение темы к документу. Описаны возможности настройки параметров темы и сохранения пользовательской темы. Рассмотрены способы копирования оформления. Описана возможность очистки параметров форматирования (по материалам сайта intuit.ru, Работа в Microsoft Excel 2010).
 
Содержание

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

Об условном форматировании

Условное форматирование применяют для выделения из общего массива данных, отвечающих некоторым условиям. Условное форматирование можно применять как к данным, введенным с клавиатуры, так и к результатам вычислений. Чаще всего условное форматирование применяют к числовым данным.

Данные, отвечающие заданным условиям, выделяются специальными элементами оформления или измененным форматированием (параметры шрифта, границы, заливки).

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

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

Выделение значений

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

  1. Выделите ячейку или диапазон ячеек.
  2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила выделения ячеек, а затем в подчиненном меню выберите условие (рис.1).

    Выбор правила выделения значений
    Рис. 1.  Выбор правила выделения значений

  3. Настройте параметры условия и выберите способ выделения (рис.2).

    Настройка параметров условного форматирования

    увеличить изображение
    Рис. 2.  Настройка параметров условного форматирования

Название и содержание окна настройки параметров условия зависит от выбранного условия. Например, при выборе условия Между можно указать минимальное и максимальное значения, а также выбрать способ выделения (см. рис.2).

При выборе условия Дата можно выбрать отношение выделяемых ячеек к сегодняшней дате (Вчера, Сегодня, Завтра, За последние 7 дней и т.д.) и выбрать способ выделения (рис.3)

Настройка параметров условного форматирования для дат

увеличить изображение
Рис. 3.  Настройка параметров условного форматирования для дат

Выделение крайних значений

С помощью условного форматирования можно выделить максимальные или минимальные значения.

  1. Выделите ячейку или диапазон ячеек.
  2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Правила отбора первых и последних значений, а затем в подчиненном меню выберите принцип отбора (рис.4).

    Выбор правила отбора крайних значений

    увеличить изображение
    Рис. 4.  Выбор правила отбора крайних значений
  3. Настройте параметры отбора и выберите способ выделения (рис.5).

    Настройка параметров отбора максимальных значений

    увеличить изображение
    Рис. 5.  Настройка параметров отбора максимальных значений

Название и содержание окна настройки параметров отбора зависит от выбранного принципа. Например, при выборе 10 первых элементов указать количество отбираемых значений, а также выбрать способ выделения (см. рис.5).

Форматирование с использованием гистограммы

Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее - тем выше значение.

  1. Выделите диапазон ячеек.
  2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Гистограммы, а затем в подчиненном выберите цветовое оформление (рис.6). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.

    Условное форматирование с использованием гистограммы

    увеличить изображение
    Рис. 6.  Условное форматирование с использованием гистограммы

Форматирование с использованием трехцветной шкалы

Цветовые шкалы помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в красно-желто-зеленой шкале можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средним значением будут желтыми, а ячейки с низкими значениями будут красными.

  1. Выделите диапазон ячеек.
  2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Цветовые шкалы, а затем в подчиненном выберите цветовую схему (рис.7). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.

    Условное форматирование с использованием трехцветной шкалы

    увеличить изображение
    Рис. 10.7.  Условное форматирование с использованием трехцветной шкалы

Форматирование с использованием набора значков

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

  1. Выделите диапазон ячеек.
  2. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная, в галерее выберите команду Наборы значков, а затем в подчиненном выберите набор значков (рис.8). При наведении указателя мыши на выбираемый вариант оформления срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.

    Условное форматирование с использованием набора значков

    увеличить изображение
    Рис. 10.8.  Условное форматирование с использованием набора значков

Управление правилами условного форматирования

Правила условного форматирования можно изменять и удалять.

  1. Щелкните по кнопке Условное форматирование группы Стили вкладки Главная и выберите команду Управление правилами (см. рис.10.1).
  2. В окне Диспетчер правил условного форматирования в поле списка Показать правила форматирования для выберите лист, для которого производятся изменение правил (рис.9).

    Управление правилами условного форматирования

    увеличить изображение
    Рис. 9.  Управление правилами условного форматирования

  3. Выделите правило, которое надо изменить и нажмите кнопку Изменить правило.
  4. В окне Изменение правила форматирования (рис.10) можно выбрать тип другой правила, изменить условие и параметры форматирования.

    Изменение правил условного форматирования

    увеличить изображение
    Рис. 10.  Изменение правил условного форматирования

  5. При наличии нескольких правил для одного диапазона изменить кнопками Верх и Вниз (см. рис.9) можно изменить порядок их применения.
  6. Для удаления правила нажмите кнопку Удалить правило (см. рис.9).

Для удаления сразу всех правил условного форматирования в выделенном диапазоне или на листе щелкните по кнопке Условное форматирование группы Стили вкладки Главная, выберите команду Удалить правила, а затем в подчиненном меню нужную команду (рис.11).

Удаление правил условного форматирования

увеличить изображение
Рис. 11.  Удаление правил условного форматирования

Поиск ячеек с условным форматированием

Для выделения всех диапазонов листа, к ячейкам которых применено условное форматирование в группе Редактирование вкладки Главная нажмите кнопку Найти и выделить и в меню выберите команду Условное форматирование (рис.12).

Поиск ячеек с условным форматированием

увеличить изображение
Рис. 12.  Поиск ячеек с условным форматированием

Создание и оформление "Таблиц"

О таблицах

Данные, организованные на листе, можно оформить как "Таблицу". При создании таблицы можно анализировать ее данные и управлять ими независимо от данных за пределами таблицы. На листе можно создать любое количество таблиц.

Таблица может использоваться для быстрой сортировки, отбора, суммирования или публикации данных, содержащихся в ней.

С другой стороны, наличие таблицы на листе и в книге создает и некоторые проблемы. Так, например, таблицу нельзя структурировать. Нельзя использовать и автоматическое подведение итогов.

Создание таблицы

Таблица обычно создается на основе имеющихся на листе данных.

  1. Выделите любую ячейку в диапазоне данных.
  2. Нажмите кнопку Форматировать как таблицу в группе Стили вкладки Главная и выберите стиль оформления (рис.10.13). Предпросмотр в данной ситуации не работает.

    Выбор стиля оформления таблицы

    увеличить изображение
    Рис. 13.  Выбор стиля оформления таблицы
  3. В поле окна Форматирование таблицы будет автоматически указан диапазон данных, который преобразуется в таблицу (рис.14). При необходимости можно очистить поле и на листе выделить другой диапазон ячеек с данными, которые оформляются в виде таблицы. Нажмите кнопку ОК .

    Создание таблицы

    увеличить изображение
    Рис. 14.  Создание таблицы

В результате будет создана таблица. В каждый столбец автоматически добавляется значок автофильтра. Автоматически будет отображена контекстная вкладка Работа с таблицами/Конструктор (рис.15).

Таблица

увеличить изображение
Рис. 15.  Таблица

Работа с таблицей

Помимо обычных инструментов, для работы с таблицей можно использовать элементы вкладки Конструктор (см. рис.15). Эта вкладка автоматически появляется при выделении любой ячейки таблицы и также автоматически скрывается при выделении любой ячейки вне таблицы.

Можно применить к таблице другой стиль оформления.

  1. Выделите любую ячейку таблицы.
  2. Нажмите кнопку Экспресс-стили во вкладке Конструктор и выберите стиль оформления (рис.16). Предпросмотр в данной ситуации работает и таблица отображается с указанным оформлением.

    Изменение стиля оформления таблицы

    увеличить изображение
    Рис. 16.  Изменение стиля оформления таблицы

Для изменения стиля оформления таблицы можно также нажать кнопку Форматировать как таблицу в группе Стили вкладки Главная и выбрать стиль оформления.

Во вкладке Конструктор в группе Параметры стилей таблицы (рис.17) можно настроить особенности параметров оформления таблицы.

Изменение стиля оформления таблицы

увеличить изображение
Рис. 17.  Изменение стиля оформления таблицы

Для включения или отключения строки заголовков установите или снимите флажок Строка заголовка.

Для включения или отключения итоговой строки установите или снимите флажок Строка итогов.

Для отображения специального форматирования первого столбца таблицы установите или снимите флажок Первый столбец.

Для отображения специального форматирования последнего столбца таблицы установите или снимите флажок Последний столбец.

Для различного отображения четных и нечетных строк в целях облегчения чтения установите или снимите флажок Чередующиеся строки.

Для различного отображения четных и нечетных столбцов в целях облегчения чтения установите или снимите флажок Чередующиеся столбцы.

Преобразование таблицы в диапазон

Таблицу можно преобразовать в обычный диапазон данных. Все установленное форматирование при этом сохранится.

  1. Выделите любую ячейку таблицы.
  2. Нажмите кнопку Преобразовать в диапазон в группе Сервис вкладки Конструктор (см. рис. 16 или рис.10.17).
  3. В окне запроса на преобразование нажмите кнопку Да.

Использование стилей

О стилях

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

Стилем называется набор параметров форматирования данных и ячеек: числовой формат; выравнивание; шрифт; граница; заливка; защита. Стиль не обязательно должен включать в себя все параметры форматирования.

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

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

Стиль сохраняется вместе с книгой, в которой он создан. Стили можно копировать из одной книги в другую.

Применение стиля

  1. Выделите ячейку или диапазон ячеек.
  2. Щелкните по кнопке Стили ячеек группы Стили вкладки Главная и выберите нужный стиль (рис.18). При наведении указателя мыши на выбираемый стиль срабатывает функция предпросмотра, и фрагмента листа отображается с указанным оформлением.

    Применение стиля

    увеличить изображение
    Рис. 18.  Применение стиля

Изменение стиля

Изменение стиля можно начать еще при его создании. Можно изменять стиль и тогда, когда он уже создан и применен.

  1. Выделите ячейку, оформленную изменяемым стилем.
  2. Щелкните по кнопке Стили ячеек группы Стили вкладки Главная.
  3. В галерее стилей (см. рис.18) щелкните правой кнопкой мыши по названию изменяемого стиля и в контекстном меню выберите команду Изменить.
  4. В диалоговом окне Стиль нажмите кнопку Формат (рис.19).

    Изменение стиля

    увеличить изображение
    Рис. 19.  Изменение стиля
  5. Во вкладках диалогового окна Формат ячеек (рис.20) установите параметры оформления стиля.

    Изменение параметров оформления стиля

    увеличить изображение
    Рис. 20.  Изменение параметров оформления стиля
  6. В диалоговом окне Формат ячеек (см. рис.20) нажмите кнопку ОК. В диалоговом окне Стиль (см. рис.19) нажмите кнопку ОК.

После изменения стиля автоматически изменится оформление всех ячеек, к которым был применен изменяемый стиль.

Следует с осторожностью изменять стиль Обычный, поскольку именно этот стиль установлен по умолчанию ко всем ячейкам. Изменение стиля Обычный приведет к изменению оформления не только всех ячеек с данными, но и всех пустых ячеек, в том числе и во вновь вставляемых листах.

Удаление стиля

Из книги можно удалить любой стиль, как встроенный, так и пользовательский, кроме стиля Обычный.

  1. Щелкните по кнопке Стили ячеек группы Стили вкладки Главная.
  2. В галерее стилей (см. рис.18) щелкните правой кнопкой мыши по названию изменяемого стиля и в контекстном меню выберите команду Удалить.

Стили удаляются только из текущей книги. При создании новой книги она будет содержать все встроенные стили.

Отменить удаления стиля невозможно.

Для восстановления в книге встроенных стилей можно создать новую книгу, а затем копировать из нее все стили.

Применение темы

О темах

Тема - набор унифицированных элементов, определяющих внешний вид документа с помощью цвета, шрифтов и графических объектов.

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

Применение темы

Можно изменить тему документа, используемую по умолчанию, выбрав другую заранее определенную или пользовательскую тему документа.

Во вкладке Разметка страницы щелкните кнопку Темы и выберите тему документа (рис.21). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанным оформлением.

Выбор темы

увеличить изображение
Рис. 21.  Выбор темы

При выборе темы выделять какие-либо ячейки нет необходимости. Параметры темы будет применены ко всем листам книги.

Настройка темы

Можно изменить отдельные параметры оформления, характерные для выбранной темы.

Выбор темы определяет набор шрифтов (основного и для заголовков). Для изменения шрифтов темы во вкладке Разметка страницы щелкните кнопку Шрифты и выберите нужный набор шрифтов (рис.22). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанными шрифтами.

Выбор шрифтов темы

увеличить изображение
Рис. 22.  Выбор шрифтов темы

Выбор набора шрифтов не означает, что при оформлении нельзя использовать другие шрифты.

Для изменения цветового оформления темы во вкладке Разметка страницы щелкните кнопку Цвета и выберите нужный набор цветов (рис.23). При наведении указателя мыши на тему срабатывает предпросмотр и таблица отображается с указанными цветами.

Выбор цветов темы

увеличить изображение
Рис. 23.  Выбор цветов темы

Выбор набора цветов не означает, что при оформлении нельзя использовать другие цвета.

Сохранение пользовательской темы

Измененную тему можно сохранить для последующего использования.

  1. Во вкладке Разметка страницы щелкните кнопку Темы и выберите команду Сохранить текущую тему (см. рис.21).
  2. В окне Сохранение текущей темы в поле Имя файла введите имя сохраняемой темы (рис.24) и нажмите кнопку Сохранить. Не рекомендуется изменять папку, в которой сохраняется файл темы.

    Сохранение пользовательской темы

    увеличить изображение
    Рис. 24.  Сохранение пользовательской темы

Пользовательские сохраненные темы отображаются в верхней части галереи тем (см. рис.21).

Копирование форматов

Копирование формата по образцу

Простейшим и наиболее быстрым способом копирования оформления одной ячейки на другие ячейки является использование кнопки Формат по образцу группы Буфер обмена вкладки Главная.

  1. Выделите ячейку, оформление которой требуется копировать.
  2. Нажмите кнопку Формат по образцу (рис.25).
  3. После этого кнопка Формат по образцу останется нажатой, а справа от указателя мыши появится значок копирования формата.
  4. Выделите ячейку или диапазон ячеек, для которых устанавливается копируемый формат (рис.25).

    Копирование формата с использованием кнопки Формат по образцу

    увеличить изображение
    Рис. 25.  Копирование формата с использованием кнопки Формат по образцу

Предложенный способ пригоден для однократного копирования формата.

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

Копирование формата по образцу используют чаще всего при копировании оформления в пределах видимой части одного листа. Но этот способ можно использовать и для копирования оформления на другие листы и другие книги.

Копирование автозаполнением

Копирование автозаполнением используют при копировании оформления одной ячейки на рядом расположенные ячейки.

  1. Выделите ячейку с копируемым оформлением
  2. Перетащите маркер автозаполнения, как это делается при обычном автозаполнении. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки
  3. Щелкните по кнопке Параметры автозаполнения в правом нижнем углу области заполнения и выберите команду Заполнить только форматы (рис.26).

    Копирование формата автозаполнением

    увеличить изображение
    Рис. 26.  Копирование формата автозаполнением

Следует обратить внимание, что воспользоваться кнопкой Параметры автозаполнения можно только в течение некоторого времени после копирования. При выполнении некоторых действий (сохранение файла, копирование и вставка ячеек и др.) кнопка исчезает, и воспользоваться копированием формата не удастся.

Копирование с использованием буфера обмена

Копирование форматов с использованием буфера обмена обычно используют при копировании оформления на ячейки других листов или книг.

  1. Ячейку с копируемым форматом скопируйте в буфер обмена.
  2. Выделите ячейки, на которые копируется оформление.
  3. Извлеките скопированную ячейку из буфера обмена. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки.
  4. Щелкните по кнопке Параметры вставки в правом нижнем углу области вставки и выберите режим Форматирование (рис.27)

    Копирование формата с использованием буфера обмена

    увеличить изображение
    Рис. 27.  Копирование формата с использованием буфера обмена

Следует обратить внимание, что воспользоваться кнопкой Параметры вставки можно только в течение некоторого времени после копирования. При выполнении некоторых действий (сохранение файла, копирование и вставка ячеек и др.) кнопка исчезает, и воспользоваться копированием формата не удастся.

Копирование с использованием специальной вставки

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

  1. Ячейку с копируемым форматом скопируйте в буфер обмена.
  2. Выделите ячейки, на которые копируется оформление.
  3. Щелкните по стрелке кнопки Вставить в группе Буфер обмена вкладки Главная и выберите команду Специальная вставка или щелкните по выделенной области правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка.
  4. В диалоговом окне Специальная вставка установите переключатель форматы (рис.28). Нажмите кнопку ОК.

    Копирование формата с использованием специальной вставки

    увеличить изображение
    Рис. 28.  Копирование формата с использованием специальной вставки

Очистка (удаление) форматирования

Можно удалить сразу все параметры оформления (числовые форматы, параметры выравнивания, параметры шрифта, заливки, границы и т. д.).

  1. Выделите ячейку или диапазон ячеек, для которых удаляется оформление.
  2. В группе Редактирование вкладки Главная щелкните по кнопке Очистить и выберите команду Очистить форматы (рис.29).

    Очистка форматирования

    увеличить изображение
    Рис. 29.  Очистка форматирования

Содержание

В начало
Вернуться на сайт