Excel: подсчитать количество условно отформатированных строк

Question

У меня есть лист Excel, в котором я применяю условное форматирование для двух столбцов. Строка подсвечивается, только если ячейки в этой строке удовлетворяют условию. В частности, я хочу выделить строки, в которых содержимое в одном столбце начинается с определенной буквы, а число в другом столбце превышает 500. Я использую эту функцию:

=AND($B2 > 500, SEARCH("A", $C2)=1)

Правильные строки подсвечены, но есть ли способ подсчитать количество выделенных строк?

Я попытался с помощью функции COUNTIF:

=COUNTIF($B$2:$C$50, AND($B2 > 500, SEARCH("A", $C2)=1))

Но он возвращает 0. Какие-либо предложения?

Я использую Microsoft Excel 2016 для Mac

Answer 1

Использование вашей конкретной формулы условного форматирования требует решения, введенного в массив:

Массив введите (Ctrl+Shift+Enter) следующую формулу в D2 (не забудьте удалить { и }):

{=SUM((B2:B50>500)*IFERROR((SEARCH("A",C2:C50)=1),FALSE))}

Поскольку это формула ввода массива, массив генерируется для каждого из диапазонов B2:B50 и C2:C50 , причем значения массива являются результатами соответствующих сравнений для каждой строки.

Ключевым моментом для понимания является то, что при умножении значение TRUE преобразуется в 1 , а значение FALSE - в 0 . Таким образом, массив, сгенерированный умножением двух массивов, будет содержать элементы со значением 1 если и только если оба столбца B больше 500, а столбец C начинается с буквы "A". (В противном случае значение элемента будет 0 )

Суммирование массива приводит к подсчету строк, в которых выполняются оба условия.

---

Вместо того, чтобы проверить, является ли индекс первой буквы "A" найденного значения в столбце C "1", лучшим решением было бы проверить, является ли первый символ "A" напрямую:

{=SUM((B2:B50>500)*(LEFT(C2:C50,1)="A"))}

С формулой в этом новом формате и понимая, что функция COUNTIFS() может использовать подстановочные знаки, становится очевидным, что альтернативное решение, не введенное в массив:

=COUNTIFS(B2:B50,">500",C2:C50,"=A*")