Резюме
- Применение прямого форматирования к разрозненным массивам в Excel может вызвать проблемы, если данные изменят форму или размер.
- Правила условного форматирования на основе формул позволяют автоматически корректировать форматирование при изменении параметров данных.
- Размещение символа доллара перед ссылкой на столбец позволяет применить правила ко всем строкам данных.
В Excel можно применять прямое форматирование к значениям ячеек или фону, чтобы сделать электронную таблицу более удобной для чтения. Однако, когда формула Excel возвращает набор значений, известный как пролитый массив, применение прямого форматирования может вызвать проблемы, если размер или форма данных изменятся.
Допустим, у вас есть электронная таблица, содержащая развернутый результат формулы PIVOTBY, которая показывает количество зрителей по каждому виду спорта в различных регионах за четыре года.
Чтобы следить за чтением, загрузите копию этой книги Excel. После того, как вы нажмете на ссылку, вы найдете кнопку загрузки в правом верхнем углу экрана.
Поскольку функция PIVOTBY не включает аргумент, позволяющий форматировать результат, сложно различать строки заголовков, строки данных, строки промежуточных итогов и строку общего итога.
На этом этапе у вас может возникнуть соблазн применить прямое форматирование — через группу «Шрифт» на вкладке «Главная» на ленте, — чтобы визуально разделить различные типы строк в данных.
Однако если вы позже измените параметры в формуле PIVOTBY или если результат увеличится или уменьшится из-за изменений в исходных данных, примененное вами прямое форматирование не будет соответствующим образом скорректировано. Это связано с тем, что прямое форматирование в Excel связано с ячейками, а не с данными, которые они содержат. Посмотрите на путаницу, которую это может вызвать, на скриншоте ниже, где данные уменьшились, но прямое форматирование по-прежнему применяется к тем же строкам.
Вместо этого следует использовать условное форматирование, которое позволяет форматировать ячейки и строки в соответствии с их значениями.
Выберите все данные (плюс несколько дополнительных строк внизу для обеспечения вертикального роста) и на вкладке «Главная» на ленте нажмите «Условное форматирование» > «Управление правилами».
Далее в диалоговом окне «Диспетчер правил условного форматирования» нажмите «Новое правило».
Для каждого из правил, которые вы собираетесь создать для форматирования вашего выброшенного массива, вам нужно будет использовать формулу. Поэтому в области Select A Rule Type диалогового окна New Formatting Rule выберите последний параметр «Use A Formula To Identify Which Cells To Formatt».
Первое правило, которое вы хотите создать, относится к строкам заголовков. В частности, вы хотите, чтобы эти ячейки приняли серый фон.
Чтобы добиться этого, потратьте немного времени на то, чтобы определить, что делает строки заголовков уникальными по сравнению с другими строками в вашей таблице. В этом случае строки заголовков — единственные строки, которые не содержат чисел в столбце G. Поэтому в поле формулы введите:
=ЕСТЕКСТ($G1)
Поскольку функция ЕСТЕКСТ обрабатывает пустые ячейки и ячейки, содержащие текст, как текстовые значения, правило условного форматирования будет рассматривать ячейки G1–G3 как содержащие текст, а остальные ячейки в столбце G будут содержать числовые значения.
Важно отметить, что добавление знака доллара ($) перед ссылкой на столбец (также известного как смешанная ссылка) исправляет условное форматирование для этого столбца, позволяя Excel применять правило к остальным строкам.
После ввода ссылки на ячейку вместо того, чтобы вручную вводить знак доллара, нажмите клавишу F4, чтобы переключаться между абсолютными ссылками (например, $G$1), смешанными ссылками (например, $G1 или G$1) и относительными ссылками (например, G1).
Наконец, поскольку изначально вы выбрали данные в столбцах A–G, условное форматирование будет применено ко всей строке, где выполняется условие.
Теперь нажмите «Формат», чтобы выбрать форматирование для строк заголовков. В этом случае вы хотите, чтобы они были окрашены в серый цвет. Затем нажмите «ОК» в диалоговых окнах Формат ячеек и Изменить правило форматирования.
При нажатии кнопки «Применить» в диалоговом окне «Диспетчер правил условного форматирования» вы увидите, что только те строки, в которых столбец G содержит пустые ячейки или текст (иными словами, строки заголовков), будут заполнены серым цветом.
Далее необходимо отформатировать строки промежуточных итогов так, чтобы они имели светло-зеленую заливку.
Снова внимательно просмотрите данные, чтобы увидеть, какие условия можно использовать для применения форматирования только к этим строкам. В этом случае строки промежуточных итогов содержат текст в столбце A, но ничего в столбце B. Кроме того, поскольку строка общего итога также соответствует этим критериям, вам необходимо исключить все ячейки в столбце A, содержащие слова «Grand Total».
Пока диалоговое окно Conditional Formatting Rules Manager все еще открыто, нажмите «New Rule» и выберите опцию, которая позволяет использовать формулу для форматирования ячеек. На этот раз в поле формулы введите:
=И($A1<>»»,$B1=»»,$A1<>»Общий итог»)
в котором
- Функция AND позволяет указать в скобках более одного условия,
- $ A1<>»» указывает Excel искать ячейки в столбце A, которые не содержат (<>) пустых ячеек («»),
- $B1=»» сообщает Excel, что нужно искать ячейки в столбце B, которые содержат (=) пустые ячейки («»), и
- $ A1<>»Общий итог» сообщает Excel о необходимости исключить (<>) все ячейки в столбце A, содержащие текст «Общий итог».
Как и в предыдущем правиле, не забудьте вставить знак $ перед ссылками на столбцы, чтобы Excel мог применить то же правило ко всем выбранным строкам.
Теперь нажмите «Формат», чтобы выбрать светло-зеленый цвет заливки, и после закрытия диалоговых окон «Формат ячеек» и «Изменить правило форматирования» нажмите «Применить», чтобы увидеть строки промежуточных итогов, заполненные светло-зеленым цветом.
Наконец, вы хотите, чтобы ячейки в строке общего итога были залиты более ярким зеленым цветом.
Поскольку строка общего итога — единственная строка, которая содержит слова «Grand Total» в столбце A, это критерий, который вы можете использовать для условного форматирования. В диалоговом окне Conditional Formatting Rules Manager нажмите «New Rule» и выберите последний вариант в списке Select A Rule Type. Теперь в поле формулы введите:
=$A1=»Общий итог»
Затем нажмите «Формат» и выберите жирный зеленый цвет заливки, чтобы применить его к ячейкам, которые соответствуют этому критерию. Теперь, когда вы закроете диалоговые окна Формат ячеек и Изменить правило форматирования и нажмете «Применить» в диалоговом окне Диспетчер правил условного форматирования, вы увидите, что ваша строка общего итога приняла это форматирование.
Теперь, когда вы применили все свои правила, нажмите «Закрыть» в диалоговом окне «Управление правилами условного форматирования». Затем скорректируйте некоторые данные в исходной таблице и посмотрите на выведенный результат и его форматирование, обновившись соответствующим образом.
В этом примере, несмотря на то, что я удалил 12 строк из исходной таблицы данных, слитый результат PIVOTBY по-прежнему правильно отформатирован: строки заголовков окрашены в серый цвет, строки промежуточных итогов — в светло-зеленый, а строка общего итога — в жирный зеленый цвет.
Если вам нужно внести какие-либо изменения в созданные и примененные вами правила, просто выберите любую ячейку в данных и нажмите Условное форматирование > Управление правилами, чтобы перезапустить менеджер правил условного форматирования. Затем дважды щелкните правило, чтобы изменить его условия.
