Подсветка наборов строк цветом

Очень несложный (на первый взгляд) и весьма актуальный для многих трюк: подсветить чередующейся заливкой группы строк с общим значением по какому-либо столбцу. Например модели автомобилей вот в таком списке по столбцу А:

Подсветка наборов строк с помощью заливки

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

Вариант 1. Вспомогательный столбец с формулой

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

Вспомогательный столбец с номером группы

Логика формулы проста: если содержимое текущей ячейки (A2) не равно предыдущей (A1), то мы прибавляем к предыдущему значению вспомогательного столбца единицу (F1+1), в противном случае оставляем значение, которое было ранее (F1). 

Чтобы чередовать цвета к полученному значению номера группы применим функцию вычисления остатка от деления на 2:

Считаем остаток

В английской версии Excel эта формула будет выглядеть как =MOD(IF(A2<>A1;F1+1;F1);2)

И, наконец, осталось применить условное форматирование для заливки строк с 1 во вспомогательном столбце. Для этого выделяем нашу таблицу, начиная с ячейки A2 и до конца, жмем Главная - Условное форматирование - Создать правило (Home - Conditional Formatting - Create Rule), выбираем тип правила Использовать формулу... (Use formula) и вводим простое условие:

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

Удостоверьтесь, что правильно ввели знаки долларов и выбрали цвет кнопкой Формат (Format). После нажатия на ОК блоки строк по моделям будут выделены цветом.

Способ 2. Формулой массива без вспомогательного столбца

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

Выделите список, начиная с ячейки A2 и до конца, жмем Главная - Условное форматирование - Создать правило (Home - Conditional Formatting - Create Rule), выбираем тип правила Использовать формулу... (Use formula) и вводим вот такую формулу:

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

В английской версии эта формула будет, соответственно:

=MOD(INT(SUM(1/COUNTIF($A$2:$A2;$A$2:$A2)));2)

Здесь логика похитрее. По-сути, это формула массива, которая вычисляет номер группы (модели) в списке и определяет - четный он или нет:

  • СЧЁТЕСЛИ($A$2:$A2;$A$2:$A2) - вычисляет количество вхождений каждой модели в список, т.е. для Avensis=3, для Corolla=2 и т.д.
  • ЦЕЛОЕ(СУММ(1/СЧЁТЕСЛИ($A$2:$A2;$A$2:$A2))) - вычисляет порядковый номер для каждой модели, т.е. для Avensis=1, для Corolla=2, для Escape=3 и т.д.
  • ОСТАТ(...;2) - вычисляет остаток порядкового номера от деления на 2, чтобы чередовать цвета для каждого блока строк, т.е. для всех строк с Avensis=0, для всех строк с Corolla=1, для все строк с Escape=1 и т.д.

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

Способ 3. Макрос

Ну и, как всегда, почти любую задачу в Microsoft Excel можно решить и макросом. Нажмите сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), чтобы открыть редактор макросов. Затем вставьте новый пустой модуль через меню Insert - Module и скопируйте туда этот несложный код:

Sub Highlight_Rows_Blocks()
    Dim nCol As Integer
    Dim nGr As Integer

    nCol = Application.InputBox(Prompt:="Введите номер столбца", Type:=1)
    If nCol < 1 Or nCol > Selection.Columns.Count Then Exit Sub
    Selection.Interior.ColorIndex = -4142

    For r = 1 To Selection.Rows.Count
        If Selection.Cells(r, nCol) <> Selection.Cells(r - 1, nCol) Then nGr = nGr + 1
        If nGr Mod 2 Then Selection.Rows(r).Interior.ColorIndex = 36
    Next r
End Sub

Теперь можно выделить диапазон с данными и запустить макрос сочетанием клавиш Alt+F8. Макрос запросит у пользователя номер столбца, по которому надо анализировать данные и затем отформатирует строки в выделенном диапазоне, чередуя заливку при смене значений в указанной колонке.

Ссылки по теме




10.03.2017 10:16:38
Добрый день!
Если просто скопировать и вставить макрос в модуль, то ругается на отступы и пустые строки.
Вручную в модуле удаляю. Так и должно быть?
Если выделить вместе с шапкой таблицу, то макрос выдает ошибку.
С уважением, Зураб.
12.03.2017 08:35:25
Зураб, скачайте лучше файл примера по ссылке в заголовке - там живой макрос и все точно работает.
10.03.2017 18:23:21
Круто!
Обычная «зебра» в условном форматировании =ОСТАТ(СТРОКА();2)=0
Теперь отдыхает!!!
10.03.2017 18:24:34
Кстати с появлением «умных таблиц» – «зебра» уже встроена в форматирование умной таблицы;)
10.03.2017 18:32:05
Кому интересно, я тут как-то писал статью в том числе про «Умные таблицы» в рамках мастер-класса Бизнес-аналитика в Excel: http://biweb.ru/bi-excel-master-klass.html
MCH
13.03.2017 13:21:38
2й способ, формула для УФ:
=-1^СУММ(Ч($A$1:$A1<>$A$2:$A2))<0
26.03.2017 18:29:08
Как всегда. Пришел МСН и все улучшил (т.е. укоротил). Спасибо, Михаил! Нет предела совершенству :)