Формулы Excel - подсчитать количество вхождений в нескольких листах в книге

Подсчитать количество вхождений в нескольких листах в книге

Синтаксис используемых функций

 INDIRECT (ref_text, [a1])
COUNTIF (критерий_диапазон, критерии)
SUMPRODUCT (массив1, [массив2], [массив3], ...)

Функция INDIRECT возвращает ссылку, указанную в текстовой строке. Ссылки немедленно оцениваются для отображения их содержания.
Функция COUNTIF используется для подсчета количества ячеек, которые соответствуют определенным критериям.
Функция SUMPRODUCT используется для умножения соответствующих компонентов в данных массивах и возвращает сумму этих продуктов.

Что делать?

Для подсчета количества вхождений в нескольких рабочих листах рабочей книги можно использовать функцию SUMPRODUCT вместе с функциями COUNTIF и INDIRECT.

формула

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B1:E12"),B6))

Как работает эта формула

В выражении INDIRECT ("'" & Sheets & "'! B1: E12") "Sheets" - это имя диапазона B9: B11, который содержит "Sheet1", "Sheet2", "Sheet3". Когда это выражение вычисляется, массив создает как -

{"'Sheet1'!B1:E12";"'Sheet2'!B1:E12";"'Sheet3'!B1:E12";}

Эта рабочая тетрадь имеет три листа, и каждый лист содержит таблицу в диапазоне B1: E12. Листы ниже:

Массив имеет три значения, каждое из которых является текстовым значением и содержит имя листа, объединенное путем объединения в диапазон B1: E12. Таким образом, функция INDIRECT преобразует каждое текстовое значение в правильную ссылку и передает его в параметр attribute_range функции COUNTIF вместе со значением критерия B6.

COUNTIF затем возвращает три результата из трех предоставленных диапазонов и создает массив

{1;1;1;}

Наконец, функция SUMPRODUCT выдает сумму массива и возвращает результат.

Предыдущий: Excel Formulas - Подсчитать количество строк, содержащих несколько условий ИЛИ
Далее: Excel Formulas - Подсчитать количество строк, содержащих конкретное значение