Общая формула
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Резюме
Чтобы найти значение на нескольких листах в книге и вернуть счетчик, можно использовать формулу, основанную на функциях СЧЁТЕСЛИ и ДВССЫЛ. После предварительной настройки вы можете использовать этот подход для поиска определенного значения во всей книге. В показанном примере формула C5 имеет следующий вид:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Контекст - образцы данных
В рабочей тетради всего 4 листа. Каждый лист Sheet1 , Sheet2 и Sheet3 содержит 1000 случайных имен, которые выглядят следующим образом:
Объяснение
Диапазон B7: B9 содержит имена листов, которые мы хотим включить в поиск. Это просто текстовые строки, и нам нужно поработать, чтобы они распознавались как действительные ссылки на лист.
Работая изнутри, это выражение используется для создания ссылки на весь лист:
"'"&B7&"'!"&"1:1048576"
Одиночные кавычки добавляются, чтобы разрешить имена листов с пробелами, а восклицательный знак - это стандартный синтаксис для диапазонов, которые включают имя листа. Текст «1: 1048576» - это диапазон, который включает каждую строку на листе.
После оценки B7 и объединения значений приведенное выше выражение возвращает:
"'Sheet1'!1:1048576"
который входит в функцию ДВССЫЛ как аргумент ref_text. INDIRECT оценивает этот текст и возвращает стандартную ссылку на каждую ячейку в Sheet1 . Это входит в функцию СЧЁТЕСЛИ как диапазон. Критерий предоставляется в виде абсолютной ссылки на C4 (заблокирован, поэтому формулу можно скопировать в столбец C).
Затем СЧЁТЕСЛИ возвращает количество всех ячеек со значением, равным «mary», в данном случае 25.
Примечание. СЧЁТЕСЛИ не чувствительно к регистру.
Содержит против равных
Если вы хотите подсчитать все ячейки, содержащие значение в C4, вместо всех ячеек, равных C4, вы можете добавить подстановочные знаки к критериям следующим образом:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Теперь СЧЁТЕСЛИ будет подсчитывать ячейки с подстрокой «Джон» в любом месте ячейки.
Спектакль
Как правило, указывать диапазон, включающий все ячейки листа, не рекомендуется. Это может вызвать проблемы с производительностью, поскольку диапазон включает миллионы и миллионы ячеек. В этом примере проблема усугубляется, поскольку формула использует функцию ДВССЫЛ, которая является изменчивой функцией. Изменчивые функции пересчитываются при каждом изменении рабочего листа, поэтому влияние на производительность может быть огромным.
По возможности ограничивайте диапазоны до разумного размера. Например, если вы знаете, что данные не появятся после строки 1000, вы можете искать только первые 1000 строк следующим образом:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
