Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

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

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

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

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

Шапка сводной таблицы

Эту таблицу необходимо заполнить, т.е. сложить доходы соответствующих продуктов и регионов. Это легко сделать с помощью функции SUMMESLYMN. Также сложите итоговые суммы. Вы получите сводный отчет о продажах в разделе «продукт-регион».

Сведение данных с помощью формулы

Вы выполнили задание и показали отчет директору. Глядя на стол, у вас сразу возникают отличные идеи.

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

После введения формулы нажмите CTRL+Enter.

Читайте так же:
Как выделить / условное форматирование дат старше 30 дней в Excel?

Посмотрите, как создать электронную таблицу в Excel.

Загрузка данных для шаблона анализа

Давайте уточним данные:

  1. На новый лист скопируйте данные из таблицы ниже во фрейме:
  2. На основе этих исходных данных создадим сводную таблицу: «ВСТАВКА»-«Сводная таблица» Сводная таблица.
  3. Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа. Сводный отчет.
  4. Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях: Настройка полей данных.

В сводной таблице заданы следующие поля значений:

  • В поле PAGES — значения для клиента и года;
  • В поле COLUMNS — значения для продукта;
  • В поле VALUES — значения для количества.

Автоматически созданная таблица pivot очень нечитабельна и не очень понятна для визуального анализа данных. При желании можно сформировать сводную таблицу. Результат выглядит следующим образом:

Сводная таблица.

Теперь, когда данные готовы, можно вернуться к шаблону анализа. Автоматически заполните табличную часть, сложную формулу, ссылающуюся на имя «Рост» и сводную таблицу на листе «Сводный отчет». Предварительно выделите диапазон таблицы B2:F11, чтобы активной ячейкой была ячейка B2. Нажмите F2 и введите формулу:

После ввода формулы необходимо нажать комбинацию клавиш CTRL + Enter.

Автоматическое заполнение формулами.

В этой формуле используется функция =FOLLOW.DATA.CONNECT.TABLE(). Название этой функции говорит само за себя. Первый аргумент задает поле входных данных (QUANTITY). Второй аргумент задает любой диапазон в сводной таблице (‘Сводный отчет’!$A$3). Третий аргумент содержит пару поле/элемент, описывающую данные запроса (например, ‘Year’;2014). В этом примере год продукта и год клиента должны быть равны 2014. Выбор клиентов и продуктов, соответственно, постоянно меняется. Поэтому они должны быть установлены со смешанными ссылками: $A2 — для клиента и B$1 — для продукта.

Читайте так же:
Как заблокировать и защитить формулы в Excel?

В этом шаблоне изначально предполагалось, что при составлении бюджета будет учтено увеличение продаж на +5% в следующем году. Поэтому в шаблоне мы умножаем функцию NEXT.DATA.TABLE() на (1+urGrow). В конце концов, рост — это имя ячейки, которая в настоящее время содержит значение 5%. Вся эта формула помещается в функцию =ESLIO ERROR(). Он заменяет все ошибочные результаты вычислений значением 0. Например, если конкретный клиент не приобрел определенный продукт в 2014 году, формула возвращает ошибку #ESLIO ERROR! Но с помощью функции ELIGIBILITY() мы просто получаем 0.

Второй способ – «автоматический».

  • Выделяем нужный нам ряд диаграммы;
  • Кликаем по нему правой кнопкой мыши;
  • В появившемся контекстном меню выбираем пункт «Формат ряда данных…»;Формат ряда данных в эксель диаграмме
  • Во вкладке «Заливка» выбираем «Сплошная заливка»;
  • Далее ставим флажок (галочку) напротив пункта «Инверсия для чисел < 0 »;инверсия заливки в эксель
  • Выбираем цвет заливки в появившейся палитре цветов;
  • После проделанных операций закрываем меню формата ряда нажатием кнопки «Ок».

итоги заливки диаграммы

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

Другие параметры

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

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

Для этого выберите диапазон B6:E6, перейдите в меню Условное форматирование > Полосы данных и выберите зеленый цвет для гистограмм. В параметрах форматирования выберите второй вариант (см. рисунок ниже). Теперь условное форматирование охватывает как итоговые показатели, так и итоговые значения (Grand Total), которые, как и все данные в сводной таблице, являются средними. Таким образом, их сравнение не противоречит закону.

Читайте так же:
Как добавить или вычесть часы из поля даты / времени в Excel?

Здесь все ячейки содержат средние значения, поэтому допустимо только одно правило условного форматирования

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

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

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

  1. Если данные в исходной таблице не совсем корректны, они переносятся в сводную таблицу аналогичным образом. Это означает, что, например, если в исходной таблице в одной строке написано «Running shoes, size 35», а в другой — «Running shoes, size 35», то в сводной таблице они будут отображаться как две отдельные строки, и данные в них не будут объединены.
  2. Если исходная таблица не была преобразована в «умную» на начальном этапе и сводная таблица создается из простой таблицы, проверьте, что все заголовки в шапке таблицы заполнены. В противном случае программа может выдать ошибку.
  3. Если перекрестная таблица основана на простой (не «умной») таблице, обновляйте данные перекрестной таблицы после внесения изменений в исходную таблицу с помощью кнопки Обновить все на вкладке Данные. Однако обратите внимание, что обновление невозможно, если строки или столбцы были добавлены сверх заданных пределов.
голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector