Условное форматирование с несколькими COUNTIFS в Excel
У меня есть около 26 правил условного форматирования на одном листе, около 23 из них являются функциями СЧЁТЕСЛИ, которые выделяют повторяющиеся строки на основе категории выше 1. Есть ли способ сократить эти правила? Кажется, что рабочий лист намного медленнее, а также влияет на некоторый код VBA, который я использую для удаления / добавления строк внутри таблицы.
Ниже приведены несколько примеров формул, которые подсчитывают 2 критерия, и все они возвращают один и тот же цвет / формат, также есть некоторые случаи, когда элементы в столбце J должны быть освобождены, поэтому я не написал его для сопоставления ВСЕХ дубликатов в Столбец J:
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Critical")>1,$J14 = "Critical")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"High")>1,$J14 = "High")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Low")>1,$J14 = "Low")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Pending")>1,$J14 = "Pending")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Under Review")>1,$J14 = "Under Review")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Open")>1,$J14 = "Open")
Есть предположения?
Да, они возвращают тот же цвет / формат. Столбец E - это идентифицирующее поле, такое как номер счета, Столбец J - поле раскрывающейся категории. Если столбец E возвращает тот же номер счета, проверьте, относится ли столбец J к той же категории. Я также добавил еще несколько формул.
Что ж, вы определенно можете комбинировать некоторые из этих правил, используя OR, например: =OR(AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Critical")>1,$J14 = "Critical"),AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"High")>1,$J14 = "High"),AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Low")>1,$J14 = "Low")). Попробуйте это и посмотрите, улучшит ли это производительность.
Получение сообщения о том, что я не могу использовать ссылочные операторы (такие как объединения, пересечения и диапазоны) или константы массива для критериев условного форматирования. :(
Вы имеете в виду, что пытаетесь выделить случаи, когда номер учетной записи имеет одну и ту же категорию дважды?
Да, но есть некоторые категории в столбце J, которые не могут быть дубликатами.
Ответы 1
Вот один из способов сократить количество формул, и я думаю, это, вероятно, ускорит процесс. Не тестировал.
Сначала создайте именованный диапазон для категорий, которые вы ДЕЙСТВИТЕЛЬНО хотите проверить на дублирование:
Ваш первый критерий условного форматирования приведет к остановке, если категории нет в приведенном выше списке.
=COUNTIF(Category,$J14)=0 '<Set this one to "Stop if True", and don't set any format>
Следующие критерии условного форматирования будут проверять как столбцы E, так и J на предмет дублирования.
=COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,$J14)>1 '<This only processes if the first condition evaluated to False>
Когда я вводил вторую формулу, она выделяла всю таблицу, какие-нибудь мысли? Я убедился, что первая формула находится ниже второй формулы в иерархии условного форматирования.
Я бы порекомендовал сначала поставить формулу =COUNTIF(Category,$J14)=0, А ТАКЖЕ установив флажок справа с надписью Stop If True. Таким образом, ваша основная функция COUNTIFS вообще никогда не будет оценивать, если категории нет в списке.
Кроме того, дважды проверьте формулу Rule и диапазон Applies to в диспетчере правил условного форматирования. Иногда, когда вы изменяете применимость к диапазону, Excel изменяет формулу, и наоборот. Каждый раз, когда я меняю один, я дважды проверяю другой, чтобы убедиться, что он читается так, как я хочу.
Есть ли способ сделать это для двух именованных диапазонов? У меня есть список, который будет выделяться на основе 2 критериев, но мне нужно создать еще одну функцию counttifs, где мне нужно выделить на основе 3 столбцов.
Если я вас правильно понимаю, вы просто воспользуетесь формулой AND, чтобы собрать столько, сколько захотите: =AND(COUNTIF(Category,$J14)=0,COUNTIF(OtherNamedRange,$Z14)=0). Просто подумайте о первом условии (с Stop If True) как о выражении IF. ЕСЛИ column1 не исключен, а column2 не исключен и т. д. ЗАТЕМ запустите последующие правила условного форматирования.
К вашему сведению, вы также можете свести это к одному правилу: =AND(COUNTIF(Category,$J14)>0,COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,$J14)>1). Но я думаю, что это будет более понятно, когда вы разделите его на две части.
Возможно, вам потребуется привести больше примеров. Кроме того, все ли они возвращают разные цвета / форматирование? Или все равно?