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

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

  • автор:

Условное форматирование данных: примеры и инструкции

Улучшенное условное форматирование является востребованной функцией Microsoft Excel. Анализ данных никогда прежде не был таким увлекательным и наглядным. Теперь вы можете гораздо эффективнее отслеживать тенденции, проверять состояние, выявлять закономерности и находить нужные значения.

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

Поиск определенных чисел, дат и текста в списке продуктов

Значение ячейки (с формулой)

Поиск динамически изменяемого числового или текстового значения в списке продуктов

Верхние и нижние значения
Выше/ниже среднего

Поиск верхнего и нижнего значений, а также значений выше и ниже среднего в последнем отчете по книге

Верхнее и нижнее значения

Определение двух лучших учащихся в классе

Уникальные и повторяющиеся

Поиск повторяющихся строк в списке клиентов

Быстрый просмотр состояния дохода и тенденций по кварталам

Сравнение продуктов по различным критериям с помощью шкалы оценок

Изучение тенденций изменения прибыли помесячно

Поиск объемов региональных продаж на сумму менее 900 000 рублей.

Сравнение высоты самых высоких гор

Изучение общего распределения продаж в основных категориях продуктов

На основе формулы

Затенение чередующихся строк в диапазоне

На основе формулы

Сравнение данных в ячейке, не входящей в диапазон ячеек, к которым применено условное форматирование

На основе формулы

Затенение всей строки в случае, если несколько условий истинны

На основе формулы

Затенение всей строки, если она является уникальным значением

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Условное форматирование сводной таблицы в Excel

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

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

Excel выделяет город с наибольшими и наименьшими продажами, причем правило форматирования сохраняется при смене фильтра с помощью среза.

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

Пошаговое применение условного форматирования к сводной таблице

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

Задача: В приведенной ниже сводной таблице необходимо применить «3-цветную шкалу» к значениям «Месяц».

Исходная сводная таблица

  • Прежде всего, выберите любую из ячеек, которые имеют значение месяца.

Выберите ячейку для условного форматирования в сводной таблице

  • Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».

Создать правило условного форматирования

  • Вы увидите диалоговое окно для применения условного форматирования к сводной таблице.

Всплывающее окно для применения условного форматирования в сводной таблице

  • В этом всплывающем окне у вас есть три различных варианта применения условного форматирования в сводной таблице.
  1. К выделенным ячейкам: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
  2. Ко всем ячейкам, содержащим значения «Сумма»: все ячейки, которые имеют значения суммы.
  3. Ко всем ячейкам, содержащим «Сумма» для «Месяц»: все ячейки, значения которых имеют сумму, но только за месяцы.

Три варианта применения условного форматирования в сводной таблице

  • Выберите «Ко всем ячейкам, содержащим значения «Сумма» для «Месяц».
  • В «Измените описание правила» выберите 3-х цветную шкалу.
  • Измените тип минимума, среднего значения и максимума на проценты. После этого выберите цвет для всех трех.

Выберите параметры для применения условного форматирования в сводной таблице

  • В конце нажмите ОК.

Сводная таблица с условным форматированием в сводной таблице

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

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

Использование условного форматирования в сводной таблице — разумный шаг, и вы можете использовать его по-разному.

Вот список примеров, которые вы можете сразу же применять в своей работе.

1. Динамическое условное форматирование с фильтрами

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

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

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

В приведенной ниже сводной таблице представлены данные о продажах по продуктам и месяцам за 2019 год. Задача выделить ячейку, которая имеет наибольшие продажи для конкретного продукта.

применить условное форматирование в сводной таблице в строках

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

  • Выберите любую из ячеек.
  • Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
  • Применить правило: третий вариант.
  • Из типов правила выберите «Форматировать только первые или последние значения».
  • В описании правила введите 1 в поле ввода и в раскрывающемся меню выберите «Каждой группы столбцов».

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

  • Примените форматирование, которое вы хотите.
  • Нажмите ОК.

Условное форматирование в сводной таблице для выделения строк

При выборе «каждой группы столбцов» условное форматирование будет сравнивать значения только из строки. Если вы хотите применить это правило к столбцам вместо строк, вы можете выбрать «каждой группы строк» ​​в описании правила.

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

3. Условное форматирование в сводной таблице на основе другой ячейки

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

Условное форматирование в сводной таблице с использованием другой ячейки

Для реализации нужно сделать следующие шаги:

  • Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
  • Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
  • Теперь в описании правила выберите «Гистограмма», в раскрывающемся меню «Тип» выберите число для максимального и минимального значений.
  • В поле минимального значения введите 0, а в поле максимального значения введите ссылку на ячейку D4.

Применить условное форматирование гистограмма в сводной таблице

  • В конце укажите цвета заливки и нажмите кнопку «ОК».

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

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

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

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

Действуем по шагам:

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

  • Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
  • Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
  • В описании правила выберите «Наборы значков» и стиль значков.
  • Теперь в правиле отображения значков используйте процент для применения условного форматирования к промежуточным итогам.
  • В конце нажмите ОК.

Когда вы применяете эти наборы значков для промежуточных итогов, они будут рассматривать самое высокое значение как 100%. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже — красный значок.

Вы можете изменить значки и процентные значения в соответствии с вашими потребностями.

5. Применение условное форматирование к пустым ячейкам в сводной таблице

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

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

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

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

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

  • Определите цвет ячейки для форматирования и нажмите ОК.

Разработка макета и формата сводной таблицы

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

Изменение формы макета сводной таблицы

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

Сводная таблица в сжатой форме

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

Сводная таблица в табличной форме

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

    Сводная таблица в форме структуры

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

    Приведение сводной таблицы к сжатой, структурной или табличной форме

    Изображение ленты Excel

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

    Изменение способа отображения подписей элементов в форме макета

    Изображение ленты Excel

    1. В сводной таблице выберите поле строки. На ленте откроется вкладка Работа со сводными таблицами. Также в форме структуры или табличной форме можно дважды щелкнуть поле строки и перейти к действию 3.
    2. На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.
    • Чтобы элементы полей отображались в форме структуры, установите переключатель в виде структуры.
    • Чтобы отобразить или скрыть подписи следующего поля в этом же столбце в сжатой форме, щелкните переключатель в виде структуры и установите флажок Отобразить подписи из следующего поля в том же столбце (сжатая форма).
    • Чтобы элементы полей отображались в табличной форме, установите переключатель в виде таблицы.

    Изменение расположения полей в сводной таблице

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

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

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

    Дополнительные сведения о работе со списком полей сводной таблицы см. в статье Упорядочение полей сводной таблицы с помощью списка полей.

    Добавление полей в сводную таблицу

    Выполните одно или несколько из указанных ниже действий.

    • Установите флажок рядом с каждым именем поля в разделе полей. Поля будут помещены в область макета, используемую по умолчанию. При необходимости их положение можно изменить. По умолчанию текстовые поля добавляются в область Названия строк, числовые поля — в область Значения, а иерархии даты и времени OLAP — в область Названия столбцов.
    • Чтобы поместить поле в определенную область раздела макета, щелкните его имя правой кнопкой мыши и выберите пункт Добавить в фильтр отчета, Добавить в названия столбцов, Добавить в названия строк или Добавить в значения.
    • Щелкните имя поля и, удерживая нажатой кнопку мыши, перетащите его из раздела полей в одну из областей раздела макета.

    Копирование полей в сводной таблице

    В сводной таблице, основанной на данных с листа Excel или внешних данных из источника, не относящегося к OLAP, можно добавить в область Значения несколько копий одного поля, чтобы отображать разные вычисления с помощью возможности Дополнительные вычисления. Например, можно сравнить вычисления (такие как коэффициенты валовой и чистой прибыли, минимальные и максимальные показатели продаж или количество клиентов и процент от их общего числа) расположив их рядом. Дополнительные сведения см. в статье Отображение различных вычислений в полях значений сводной таблицы.

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

    • При добавлении в область значений двух или нескольких полей (как копий одного поля, так и разных полей) список полей автоматически добавляет в область Значения название столбца значений. С помощью этого поля можно перемещать поле вверх и вниз в области Значения. Название столбца значений можно даже переместить в область Названия столбцов или Названия строк. Однако переместить название столбца значений в область фильтров отчетов нельзя.
    • Поле можно добавить в область Фильтр отчета, Названия строк или Названия столбцов только один раз как для числовых, так и для нечисловых типов данных. При попытке добавить одно поле несколько раз (например, в области Названия строк и Названия столбцов в разделе макета) поле автоматически удаляется из исходной области и перемещается в новую.
    • Кроме того, добавить копию поля в область Значения можно с помощью формулы (также называемой вычисляемым столбцом), в которой используется это поле.
    • Одно и то же поле невозможно добавить несколько раз в сводную таблицу, созданную на основе источника данных OLAP.

    Изменение положения полей в сводной таблице

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

    Отчет сводной таблицы

    Сводная диаграмма

    Используются для вывода итоговых числовых данных.

    Используются для вывода итоговых числовых данных.

    Названия строк

    Используются для отображения полей в виде строк сбоку от отчета. Строка, расположенная ниже, вложена в строку, непосредственно предшествующую ей.

    Поля осей (категории)

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

    Названия столбцов

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

    Названия полей легенды (рядов)

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

    Фильтр отчета

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

    Фильтр отчета

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

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

    Переместить вверх

    Перемещение поля в области на одну позицию вверх.

    Переместить вниз

    Перемещение поля в области на одну позицию вниз.

    Переместить в начало

    Перемещение поля к началу области.

    Переместить в конец

    Перемещение поля к концу области.

    Переместить в фильтр отчета

    Перемещение поля в область фильтра отчета.

    Переместить в названия строк

    Перемещение поля в область названий строк.

    Переместить в названия столбцов

    Перемещение поля в область названий столбцов.

    Переместить в значения

    Перемещение поля в область значений.

    Параметры поля значений, Параметры поля

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

    в верхней части диалогового окна.

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

    Удаление полей из сводной таблицы

    Изображение ленты Excel

    1. Щелкните сводную таблицу. На ленте откроется вкладка Работа со сводными таблицами.
    2. Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей. Вы также можете щелкнуть правой кнопкой мыши сводную таблицу и выбрать Показать список полей.

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

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

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

    Включение и отключение заголовков полей столбцов и строк

    Изображение ленты Excel

    1. Щелкните сводную таблицу. На ленте откроется вкладка Работа со сводными таблицами.
    2. Чтобы переключаться между режимами отображения и скрытия заголовков полей, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Заголовки полей.

    Отображение промежуточных итогов выше или ниже их строк

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

    Совет: В форме структуры или табличной форме также можно дважды щелкнуть поле строки и перейти к действию 3.

    Изображение ленты Excel

    На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

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

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

    Изменение порядка элементов строк и столбцов

    Выполните любое из следующих действий.

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

    Настройка ширины столбцов при обновлении.

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

    Перемещение столбца в область названий строк или строки в область названий столбцов

    Для оптимизации структуры и удобочитаемости сводной таблицы может потребоваться переместить поле столбца в область названий строк или поле строки в область названий столбцов. При перемещении столбца в строку или строки в столбец выполняется транспонирование вертикальной или горизонтальной ориентации поля. Эта операция также называется «поворотом» строки или столбца.

    Используйте команду правой кнопки мыши

    Выполните любое из следующих действий.

    • Щелкните поле строки правой кнопкой мыши, наведите указатель на команду Переместить и выберите пункт Переместить в столбцы.
    • Щелкните поле столбца правой кнопкой мыши и выберите команду Переместить в строки.

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

    1. Переключитесь в классический режим, поместив указатель на сводную таблицу, выбрав Анализ сводной таблицы >Параметры, выбрав вкладку Отображение, а затем выбрав Классический макет сводной таблицы.
    2. Перетащите поле строки или столбца в другую область. На рисунке ниже показано, как переместить поле столбца в область названий строк.

    Объединение и отмена объединения ячеек для элементов внешних строк и столбцов

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

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

    Примечание: В сводной таблице нельзя использовать флажок Объединить ячейки, который находится на вкладке Выравнивание.

    Изменение отображения пустых ячеек, пустых строк и ошибок

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

    Изменение способа отображения ошибок и пустых ячеек

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

    • Чтобы изменить способ отображения ошибок, установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
    • Чтобы изменить способ отображения пустых ячеек, установите флажок Для пустых ячеек отображать и введите в текстовом поле значение, которое будет отображаться в пустых ячейках.

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

    Отображение и скрытие пустых строк после строк или элементов

    Для строк выполните следующие действия.

      В сводной таблице выберите поле строки. На ленте откроется вкладка Работа со сводными таблицами.

    Совет: В форме структуры или табличной форме также можно дважды щелкнуть поле строки и перейти к действию 3.

    Изображение ленты Excel

    На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

    Для элементов выполните следующие действия.

    Изображение ленты Excel

    • В сводной таблице выберите нужный элемент. На ленте откроется вкладка Работа со сводными таблицами.
    • На вкладке Конструктор в группе Макет нажмите кнопку Пустые строки и установите флажок Вставить пустую строку после каждого элемента или Удалить пустую строку после каждого элемента.

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

    Изменение способа отображения элементов и подписей без данных

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

      Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.

    Примечание: Этот параметр доступен только для источника данных OLAP.
    Примечание: Этот параметр доступен только для источников данных OLAP.

    Примечание: Этот флажок применим только к сводным таблицам, созданным с использованием версий Excel, предшествующих Office Excel 2007.

    Изменение и удаление форматирования

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

    Применение стиля для форматирования сводной таблицы

    Быстро изменить внешний вид и формат сводной таблицы можно с помощью одного из готовых стилей сводных таблиц (или экспресс-стилей).

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Конструктор в группе Стили сводной таблицы выполните любое из следующих действий.
    3. Щелкните отображаемый стиль сводной таблицы или прокрутите коллекцию, чтобы увидеть другие стили.
    4. Чтобы посмотреть все доступные стили, нажмите кнопку Дополнительные параметры внизу панели прокрутки.

    Применение чередования для изменения формата сводной таблицы

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Конструктор в группе Параметры стилей сводной таблицы выполните одно из следующих действий:
    3. Чтобы применить чередование строк со светлым и темным фоном, установите флажок Чередующиеся строки.
    4. Чтобы применить чередование столбцов со светлым и темным фоном, установите флажок Чередующиеся столбцы.
    5. Чтобы включить заголовки строк в стиль чередования, установите флажок Заголовки строк.
    6. Чтобы включить заголовки столбцов в стиль чередования, установите флажок Заголовки столбцов.

    Удаление стиля или формата чередования из сводной таблицы

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Конструктор в группе Стили сводной таблицы нажмите кнопку Дополнительные параметры внизу панели прокрутки, чтобы отобразить все доступные стили, и выберите команду Очистить внизу коллекции.

    Условное форматирование данных в сводной таблице

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

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

    Примечание: В иерархии данных дочерние элементы не наследуют условное форматирование от родительских, а родительские — от дочерних.

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

    Изображение ленты Excel

    1. Выделите в сводной таблице нужное поле. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

    Включение форматирования сервера OLAP

    Если вы подключены к базе данных Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP), вы можете указать, какие форматы сервера OLAP следует извлекать и отображать вместе с данными.

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Данные нажмите кнопку Изменить источник данных и выберите пункт Свойства подключения.

    • Чтобы включить или отключить форматирование чисел, таких как денежные значения, даты и время, установите или снимите флажок Числовой формат.
    • Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.
    • Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.
    • Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

    Сохранение и игнорирование форматирования

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

    Примечание: Хотя этот параметр также влияет на форматирование сводных диаграмм, линии тренда, подписи данных, пределы погрешностей и другие изменения определенных рядов данных не сохраняются.

    Откройте панель параметров сводной таблицы.

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

    Кнопка

      Выделив сводную таблицу, на ленте щелкните Сводная таблица >Параметры.

    Область

    Примечание: Раздел «Срез» отображается только в том случае, если к вашей сводной таблице подключен срез.

    Показать или скрыть общие итоги и промежуточные итоги

    Чтобы показать общие итоги

    • Установите или снимите флажок Строки, Столбцы или оба.

    Чтобы показать промежуточные итоги

    • Выберите Не показать, чтобы скрыть промежуточные итоги.
    • Выберите Сверху, чтобы отобразить их над значениями, которые они суммируют.
    • Выберите Внизу, чтобы отобразить их под значениями, которые они суммируют.

    Изменить расположение строк и столбцов

    Размещение полей из области строк

    Выберите Отдельные столбцы, чтобы предоставить отдельные фильтры для каждого поля строк, или Один столбец, чтобы объединить поля строк в один фильтр.

    Область строк с объединенными фильтрами

    Чтобы показать или скрыть метки элементов

    Выберите Повторять или Не повторять, чтобы указать, будут ли метки элементов отображаться для каждого элемента или только один раз для каждого значения метки элемента.

    Повторение подписей элементов в отчете сводной таблицы.

    Чтобы добавить пустую строку после каждого элемента

    Выберите Показать или Не показывать.

    Сортировка и отображение

    Автоподгонка ширины столбцов при обновлении

    Установите этот параметр, чтобы автоматически менять размер столбцов в соответствии с данными при обновлении сводной таблицы.

    Отображение кнопок развертывания и свертывания

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

    Чтобы показать значения ошибок

    Выберите этот параметр, чтобы отображать значение в текстовом поле для ячеек с ошибками.

    Чтобы показать пустые ячейки

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

    Обновление и сохранение

    Чтобы сохранить исходные данные с файлом

    Выберите этот параметр, чтобы включить исходные данные сводной таблицы в файл Excel при сохранении. Обратите внимание, что это может привести к созданию довольно большого файла.

    Чтобы обновить данные при открытии файла

    Выберите этот параметр, чтобы при каждом открытии файла Excel обновлял данные сводной таблицы.

    Добавить замещающий текст

    Чтобы добавить заголовок

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

    Чтобы добавить описание

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

    Изменение формы макета сводной таблицы

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

    Сводная таблица в сжатой форме

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

    Сводная таблица в табличной форме

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

    Сводная таблица в форме структуры

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

    Приведение сводной таблицы к сжатой, структурной или табличной форме

    Изображение ленты Excel

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

    Изменение расположения полей в сводной таблице

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

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

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

    Дополнительные сведения о работе со списком полей сводной таблицы см. в статье Упорядочение полей сводной таблицы с помощью списка полей.

    Добавление полей в сводную таблицу

    Выполните одно или несколько из указанных ниже действий.

    • Установите флажок рядом с каждым именем поля в разделе полей. Поля будут помещены в область макета, используемую по умолчанию. При необходимости их положение можно изменить. По умолчанию текстовые поля добавляются в область Названия строк, числовые поля — в область Значения, а иерархии даты и времени OLAP — в область Названия столбцов.
    • Чтобы поместить поле в определенную область раздела макета, щелкните его имя правой кнопкой мыши и выберите пункт Добавить в фильтр отчета, Добавить в названия столбцов, Добавить в названия строк или Добавить в значения.
    • Щелкните имя поля и, удерживая нажатой кнопку мыши, перетащите его из раздела полей в одну из областей раздела макета.

    Копирование полей в сводной таблице

    В сводной таблице, основанной на данных с листа Excel или внешних данных из источника, не относящегося к OLAP, можно добавить в область Значения несколько копий одного поля, чтобы отображать разные вычисления с помощью возможности Дополнительные вычисления. Например, можно сравнить вычисления (такие как коэффициенты валовой и чистой прибыли, минимальные и максимальные показатели продаж или количество клиентов и процент от их общего числа) расположив их рядом. Дополнительные сведения см. в статье Отображение различных вычислений в полях значений сводной таблицы.

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

    • При добавлении в область значений двух или нескольких полей (как копий одного поля, так и разных полей) список полей автоматически добавляет в область Значения название столбца значений. С помощью этого поля можно перемещать поле вверх и вниз в области Значения. Название столбца значений можно даже переместить в область Названия столбцов или Названия строк. Однако переместить название столбца значений в область фильтров отчетов нельзя.
    • Поле можно добавить в область Фильтр отчета, Названия строк или Названия столбцов только один раз как для числовых, так и для нечисловых типов данных. При попытке добавить одно поле несколько раз (например, в области Названия строк и Названия столбцов в разделе макета) поле автоматически удаляется из исходной области и перемещается в новую.
    • Кроме того, добавить копию поля в область Значения можно с помощью формулы (также называемой вычисляемым столбцом), в которой используется это поле.
    • Одно и то же поле невозможно добавить несколько раз в сводную таблицу, созданную на основе источника данных OLAP.

    Изменение положения полей в сводной таблице

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

    Отчет сводной таблицы

    Сводная диаграмма

    Используются для вывода итоговых числовых данных.

    Используются для вывода итоговых числовых данных.

    Названия строк

    Используются для отображения полей в виде строк сбоку от отчета. Строка, расположенная ниже, вложена в строку, непосредственно предшествующую ей.

    Поля осей (категории)

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

    Названия столбцов

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

    Названия полей легенды (рядов)

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

    Фильтр отчета

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

    Фильтр отчета

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

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

    Переместить вверх

    Перемещение поля в области на одну позицию вверх.

    Переместить вниз

    Перемещение поля в области на одну позицию вниз.

    Переместить в начало

    Перемещение поля к началу области.

    Переместить в конец

    Перемещение поля к концу области.

    Переместить в фильтр отчета

    Перемещение поля в область фильтра отчета.

    Переместить в названия строк

    Перемещение поля в область названий строк.

    Переместить в названия столбцов

    Перемещение поля в область названий столбцов.

    Переместить в значения

    Перемещение поля в область значений.

    Параметры поля значений, Параметры поля

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

    в верхней части диалогового окна.

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

    Удаление полей из сводной таблицы

    Изображение ленты Excel

    1. Щелкните сводную таблицу. На ленте откроется вкладка Работа со сводными таблицами.
    2. Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей. Вы также можете щелкнуть правой кнопкой мыши сводную таблицу и выбрать Показать список полей.

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

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

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

    Включение и отключение заголовков полей столбцов и строк

    Изображение ленты Excel

    1. Щелкните сводную таблицу. На ленте откроется вкладка Работа со сводными таблицами.
    2. Чтобы переключаться между режимами отображения и скрытия заголовков полей, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Заголовки полей.

    Отображение промежуточных итогов выше или ниже их строк

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

    Совет: В форме структуры или табличной форме также можно дважды щелкнуть поле строки и перейти к действию 3.

    Изображение ленты Excel

    На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

    Добавить поле сводной таблицы

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

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

    Изменение порядка элементов строк и столбцов

    Выполните любое из следующих действий.

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

    Настройка ширины столбцов при обновлении.

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

    Перемещение столбца в область названий строк или строки в область названий столбцов

    Для оптимизации структуры и удобочитаемости сводной таблицы может потребоваться переместить поле столбца в область названий строк или поле строки в область названий столбцов. При перемещении столбца в строку или строки в столбец выполняется транспонирование вертикальной или горизонтальной ориентации поля. Эта операция также называется «поворотом» строки или столбца.

    Выполните любое из следующих действий.

    • Щелкните поле строки правой кнопкой мыши, наведите указатель на команду Переместить и выберите пункт Переместить в столбцы.
    • Щелкните поле столбца правой кнопкой мыши и выберите команду Переместить в строки.
    • Перетащите поле строки или столбца в другую область. На рисунке ниже показано, как переместить поле столбца в область названий строк.

    Объединение и отмена объединения ячеек для элементов внешних строк и столбцов

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

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

    Примечание: В сводной таблице нельзя использовать флажок Объединить ячейки, который находится на вкладке Выравнивание.

    Изменение отображения пустых ячеек, пустых строк и ошибок

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

    Изменение способа отображения ошибок и пустых ячеек

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

    • Чтобы изменить способ отображения ошибок, установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
    • Чтобы изменить способ отображения пустых ячеек, установите флажок Для пустых ячеек отображать и введите в текстовом поле значение, которое будет отображаться в пустых ячейках.

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

    Изменение способа отображения элементов и подписей без данных

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

      Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.

    Примечание: Этот параметр доступен только для источника данных OLAP.
    Примечание: Этот параметр доступен только для источников данных OLAP.
    Изменение и удаление форматирования

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

    Применение стиля для форматирования сводной таблицы

    Быстро изменить внешний вид и формат сводной таблицы можно с помощью одного из готовых стилей сводных таблиц (или экспресс-стилей).

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Конструктор в группе Стили сводной таблицы выполните любое из следующих действий.
    3. Щелкните отображаемый стиль сводной таблицы или прокрутите коллекцию, чтобы увидеть другие стили.
    4. Чтобы посмотреть все доступные стили, нажмите кнопку Дополнительные параметры внизу панели прокрутки.

    Применение чередования для изменения формата сводной таблицы

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Конструктор в группе Параметры стилей сводной таблицы выполните одно из следующих действий:
    3. Чтобы применить чередование строк со светлым и темным фоном, установите флажок Чередующиеся строки.
    4. Чтобы применить чередование столбцов со светлым и темным фоном, установите флажок Чередующиеся столбцы.
    5. Чтобы включить заголовки строк в стиль чередования, установите флажок Заголовки строк.
    6. Чтобы включить заголовки столбцов в стиль чередования, установите флажок Заголовки столбцов.

    Удаление стиля или формата чередования из сводной таблицы

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Конструктор в группе Стили сводной таблицы нажмите кнопку Дополнительные параметры внизу панели прокрутки, чтобы отобразить все доступные стили, и выберите команду Очистить внизу коллекции.

    Условное форматирование данных в сводной таблице

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

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

    Примечание: В иерархии данных дочерние элементы не наследуют условное форматирование от родительских, а родительские — от дочерних.

    Включение форматирования сервера OLAP

    Если вы подключены к базе данных Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP), вы можете указать, какие форматы сервера OLAP следует извлекать и отображать вместе с данными.

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Данные нажмите кнопку Изменить источник данных и выберите пункт Свойства подключения.

    • Чтобы включить или отключить форматирование чисел, таких как денежные значения, даты и время, установите или снимите флажок Числовой формат.
    • Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.
    • Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.
    • Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

    Сохранение и игнорирование форматирования

    Изображение ленты Excel

    1. Щелкните в любом месте сводной таблицы. На ленте откроется вкладка Работа со сводными таблицами.
    2. На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

    Примечание: Хотя этот параметр также влияет на форматирование сводных диаграмм, линии тренда, подписи данных, пределы погрешностей и другие изменения определенных рядов данных не сохраняются.

    Как Использовать Условное Форматирование в Microsoft Excel

    Электронные таблицы, иногда недолюбливают, из-за того, что их сложно читать. Создание таблицы «дружелюбной» для пользователя требует форматирования — основные элементы которого: добавление шапок таблицам, выравнивание и использование в таблицах правильных цветов.

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

    Conditional Formating Excel

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

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

    Sales Team Review conditional formatting in Excel

    • Делать все отрицательные числа в списке данных — красными.
    • Создание гистограмм для визуального представления максимальных и минимальных значений в ваших данных.
    • Использование иконок что бы показывать самые высокие и низкие величины в списке данных.

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

    Как Использовать Условное Форматирование в Excel (Смотрите и Учитесь)

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

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

    1. Использование Встроенного Условного Форматирования

    Этот урок предполагает использование примеров электронных таблиц, которые вы можете загрузить и использовать по ходу урока.

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

    Мы взглянем на встроенные стили Excel предназначенные для условного форматирования. Каждый из этих стилей вы можете использовать, если выделите данные которые вы хотите форматировать, а затем найдете кнопку Условное Форматирование на вкладке Главная, в верхней строке меню.

    Conditional Formatting menu

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

    Выделение Ячеек Цветом

    Выбор варианта выделения ячеек позволит вам выполнять условное форматирование для ряда факторов. Кликните по Условное Форматирование и затем выберите в меню опцию Правила Выделения Ячеек, что бы начать работать с условным форматированием.

    Highlight cells

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

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

    Давайте рассмотрим на примере. В данном случае, я выбрал Больше. . Затем я выбрал значение в выпадающем окне. На скриншоте ниже, я установил величину $22,000, и использовал опцию «Зеленая заливка».

    Format Cells greater than

    Как вы можете видеть, Excel применил условное форматирование в режиме просмотра (сразу по ходу задания значений) и ячейки, значения в которых выше $22,000 выделены зеленым цветом.

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

    Применение Правил Отбора Первых и Последних Значений

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

    Щелкните по кнопке Условное Форматирование, выберите Правило Отбора Первых и Последних Значений и выберите одну из опций форматирования.

    TopBottom Rules

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

    Выпадающее окно подобно тому, что было в предыдущем примере, и мы выберем Зеленую Заливку в выпадающем окне.

    Above Average highlight in Excel

    Заметьте на скриншоте выше Excel автоматически выделил зеленым цветом ячейки.

    2. Используйте Встроенные Стили в Excel

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

    Использование Гистограмм

    Один из самых быстрых способов показать какое значение имеют данные в ваших электронных таблицах — это использовать гистограммы. Опция гистограмм помещает столбики в ваши ячейки, которые показывают «размер» ваших данных.

    Data bars option in Excel

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

    На скриншоте ниже показана возможность гистограмм при демонстрации ваших данных. В колонке January 2017, длинной зеленой столбиков обозначается величина данных. Наименьшим значениям, соответствуют самые короткие столбики, в то время как наибольшим — длинные.

    Data bar example in Excel

    Гистограммы -прекрасная иллюстрация возможностей условного форматирования в Excel. Обратите внимание на сколько легче найти самое большое значение в колонке для Февраля, просто сравнив длины столбиков.

    Использование Цветовых Шкал

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

    Color Scales conditional formatting

    Эскизы Цветовых Шкал показывают какие цвета будет применены к вашим данным. Я выбрал первую иконку из списка, и ниже вы можете увидеть результаты моего выбора:

    Color Conditional formatting in Excel

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

    Цветовые шкалы в Excel имеют набор различных правил условного форматирования. Попробуйте применить к данным другие варианты, выбрав другие эскизы.

    Добавьте Значки (Иконки)

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

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

    Icon Set conditional formatting in Excel

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

    Icon to show change in Excel

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

    Во всех случая при условном форматировании отображение меняется при изменении данных. Если данные в продажах изменятся, Excel автоматически изменит иконки, в соответствии с данными.

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

    Подводим Итоги и Продолжаем Обучение

    В этом уроке мы разобрали Условное Форматирование в Excel. Вы научились, как задавать стили для ячеек, которые динамически изменяются в зависимости от содержимого.

    Суть в том, что: форматирование улучшает читабельность ваших электронных таблиц. А условное форматирование в Excel поможет изменить представление каждой ячейки. Это позволяет вам быстро пробежаться по данным и уловить суть.

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

    • Если вам нужны уроки начального уровня по Excel, то посмотрите тьюториал для начинающих: Как Добавить Элементарную Формулу в Excel.
    • Узнайте больше о форматировании в уроке: Как Использовать Формат по Образцу в Excel — Урок за 60 Секунд.
    • В этом уроке мы рассматривали условное форматирование, которое изменяет стиль отображения в зависимости от содержания. Но мы также можем использовать условия, что бы изменить данные в ячейках, о чем вы можете узнать из урока: Как Использовать Выражение ЕСЛИ в Excel.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *