Подсветка наборов строк цветом
Очень несложный (на первый взгляд) и весьма актуальный для многих трюк: подсветить чередующейся заливкой группы строк с общим значением по какому-либо столбцу. Например модели автомобилей вот в таком списке по столбцу А:
Чересстрочную заливку зеброй мы уже разбирали, разграничение наборов строк горизонтальными линиями тоже. Теперь давайте рассмотрим и такой вариант, тем более, что в нем есть пара интересных фишек даже для продвинутых 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. Макрос запросит у пользователя номер столбца, по которому надо анализировать данные и затем отформатирует строки в выделенном диапазоне, чередуя заливку при смене значений в указанной колонке.
Ссылки по теме
- Разделительная линия между наборами строк
- Условное форматирование в Excel
- Полосатая заливка строк таблицы "зеброй"
Если просто скопировать и вставить макрос в модуль, то ругается на отступы и пустые строки.
Вручную в модуле удаляю. Так и должно быть?
Если выделить вместе с шапкой таблицу, то макрос выдает ошибку.
С уважением, Зураб.
Обычная «зебра» в условном форматировании =ОСТАТ(СТРОКА();2)=0
Теперь отдыхает!!!
=-1^СУММ(Ч($A$1:$A1<>$A$2:$A2))<0
=$A2<>$A1
Макрос лучше модернизировать. Сейчас пытался раскрасить таблицу в 150тыс+ строк и выскакивала ошибка "Overflow".
Докопался, что проблема в больших числах, поэтому лучше указывать тип данных Currency, а не Integer. Для маленьких Юстинианов-завовевателей
можно использовать тип данных Decimal ))
И еще от себя: хорошо бы в этот макрос добавить комментарии описание, какие значения поменять, чтобы изменить форматирование, хотя бы с парой примеров)
P.S. А зачем нужна эта строка в макросе? -> Selection.Interior.ColorIndex = -4142