Автоматическое форматирование результатов Excel GROUPBY и PIVOTBY с помощью условного форматирования

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

Вы, вероятно, слышали о двух недавних дополнениях к динамическим функциям массива Excel: GROUPBY и PIVOTBY. Эти функции отлично справляются с группировкой и суммированием данных, но результаты часто кажутся простыми и трудными для понимания из-за отсутствия форматирования. Для лучшей читаемости и представления вы, конечно, можете вручную отформатировать ключевые элементы, такие как заголовки, промежуточные итоги и итоги. Однако, поскольку результирующий массив является динамическим, эти элементы могут перемещаться, чтобы соответствовать изменениям в исходном наборе данных. Естественно, вы не захотите переформатировать данные каждый раз, когда они меняются. К счастью, вы можете автоматизировать этот процесс с помощью условного форматирования, которое динамически обновляется на основе установленных вами критериев.

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

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

1. Выберите диапазон для форматирования. Начните с выбора всех результатов, возвращаемых вашей формулой GROUPBY или PIVOTBY. Если ваш исходный набор данных может быть изменен и вы ожидаете будущие дополнения, расширьте свой выбор, включив дополнительные пустые строки ниже. Таким образом, при добавлении дополнительных данных условное форматирование будет применяться автоматически.
2. Доступ к условному форматированию. На вкладке «Главная» нажмите «Условное форматирование» > «Новое правило…».
3. Создайте правило на основе формулы. В окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек». Затем введите формулу в соответствующее поле.
4. Выберите форматы. Нажмите кнопку Формат, чтобы открыть параметры форматирования. Здесь вы можете выбрать желаемые форматы, которые будут применяться на основе вашего правила.
5. Сохраните правило. Подтвердите настройки, нажав кнопку «ОК» столько раз, сколько необходимо, что сохранит и применит ваше новое правило условного форматирования.

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

Формула Excel GROUPBY с условным форматированием

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

Выберите диапазон, к которому будет применено условное форматирование.

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

Выбрав соответствующий диапазон, нажмите Условное форматирование > Новое правило…, чтобы настроить первое правило.

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

Форматировать строку заголовка

Форматирование строки заголовка результатов GROUPBY имеет решающее значение для ее отличия от остальных данных.

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

Формула точного соответствия

Используйте этот метод, если ваши заголовки содержат уникальные согласованные термины. Например, если “Project type” является первым заголовком в возвращаемом массиве, вы можете определить его, найдя текст “Project type” в столбце F:

=$F3=”Тип проекта”

Формула частичного соответствия

Если ваш заголовок может отличаться (например, «тип проекта» или «название проекта»), используйте функцию ПОИСК для частичного совпадения:

=ПОИСК(“проект”, $F3)

Поскольку функция ПОИСК нечувствительна к регистру, вы можете вводить текст с любым регистром букв.

Примечание. Какую бы формулу вы ни выбрали, не забудьте использовать смешанную ссылку на ячейку (например, $F3). Это заблокирует столбец и позволит скорректировать номер строки, гарантируя, что форматирование будет применено последовательно по всей строке.

Форматирование

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

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

Этот простой, но эффективный вариант форматирования значительно улучшает читаемость и профессиональный вид результатов GROUPBY.

Выделить промежуточные итоги

Улучшение видимости промежуточных итогов в результатах GROUPBY может сделать ваш анализ данных более интуитивным и доступным. Для этого вам нужно будет точно определить строки промежуточных итогов. Обычно эти строки содержат значения в столбце группировки первого уровня (в этом примере — тип проекта) и агрегированных столбцах (например, доход), тогда как ячейки во вторичном столбце группировки (например, статус) пустые.

Формула

Чтобы построить соответствующую формулу, можно использовать функцию И, которая проверяет оба условия — является ли столбец F (Тип проекта) пустым, а столбец G (Статус) пустым:

=И($F3<>“”, $G3=””)

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

=И($F3<>“”, $G3=””, $F3<>“общий итог”)

Форматирование

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

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

Отформатируйте строку общего итога

Привлечение внимания к общему итогу имеет решающее значение для того, чтобы сделать ключевое резюме ваших данных сразу заметным.

Формула

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

=$F3=”общий итог”

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

Форматирование

Чтобы лучше выделить строку общего итога, мы изменим два элемента — шрифт и границу ячейки:

• Чтобы выделить текст общей суммы, перейдите на вкладку «Шрифт» и выберите «Жирный» в разделе «Стили шрифтов».
• Чтобы добавить линию над строкой общего итога, перейдите на вкладку Граница. Здесь выберите предпочтительный стиль и цвет линии, а затем примените верхнюю границу, чтобы создать четкое разделение с остальными данными.

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

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

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

Формула Excel PIVOTBY с условным форматированием

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

Перед созданием правила условного форматирования выберите результаты формулы PIVOTBY. При желании включите несколько пустых ячеек ниже, чтобы автоматически форматировать любые новые данные, которые могут быть добавлены позже. В нашем случае мы выбираем диапазон G3:M32, а затем нажимаем Условное форматирование > Новое правило…, чтобы начать создание правила для строки заголовка.

Выделите строку заголовка

Первый шаг — выделить строку заголовка.

Формула

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

=И($G3=””, $H3=””)

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

Форматирование

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

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

Выделить промежуточные итоги

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

Формула

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

=И($G3<>“”, $H3=””, $G3<>“общий итог”)

Форматирование

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

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

Отформатируйте строку общего итога

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

Формула

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

=$G3=”общий итог”

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

Форматирование

Чтобы выделить строку общего итога, примените следующее форматирование:

• Жирный текст. Выделите жирным текст общей суммы, чтобы подчеркнуть его важность.
• Строка выше. Добавьте строку над строкой общего итога, чтобы отделить ее от данных выше.
• Цвет фона. Примените к строке общего итога особый цвет заливки, чтобы выделить ее.

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

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

Завершая статью, я надеюсь, что вы готовы использовать эти приемы условного форматирования, чтобы ваши формулы GROUPBY и PIVOTBY рассказывали убедительную историю и выглядели потрясающе.

Практическая рабочая тетрадь для скачивания

GROUPBY и PIVOTBY с условным форматированием (файл .xlsx)

Вас также может заинтересовать: