Загальна формула
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Резюме
Щоб шукати значення в книзі та повернути підрахунок у кількох робочих аркушах, можна скористатися формулою на основі функцій COUNTIF та INDIRECT. За допомогою деяких попередніх налаштувань ви можете використовувати цей підхід для пошуку в цілій книзі певного значення. У наведеному прикладі формула в С5 має вигляд:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Контекст - зразок даних
Робоча книга містить 4 аркуші. Лист1 , Лист2 і Sheet3 кожен з яких міститься 1000 випадкових імена , які виглядають так:
Пояснення
Діапазон B7: B9 містить назви аркушів, які ми хочемо включити в пошук. Це лише текстові рядки, і нам потрібно виконати певну роботу, щоб визнати їх дійсними посиланнями на аркуші.
Працюючи зсередини, цей вираз використовується для побудови повного посилання на аркуш:
"'"&B7&"'!"&"1:1048576"
Поодинокі лапки додаються, щоб дозволити імена аркушів із пробілами, а знак оклику є стандартним синтаксисом для діапазонів, що включають назву аркуша. Текст "1: 1048576" - це діапазон, який включає кожен рядок на аркуші.
Після того, як B7 обчислюється і значення об'єднуються, наведений вище вираз повертає:
"'Sheet1'!1:1048576"
який переходить у функцію INDIRECT як аргумент 'ref_text'. INDIRECT обробляє цей текст і повертає стандартне посилання на кожну комірку в аркуші1 . Це переходить у функцію COUNTIF як діапазон. Критерії подані як абсолютне посилання на С4 (заблоковано, щоб формулу можна було скопіювати в стовпець С).
Потім COUNTIF повертає підрахунок усіх клітинок зі значенням, рівним "mary", 25 у цьому випадку.
Примітка: COUNTIF не враховує регістр.
Містить проти рівних
Якщо ви хочете порахувати всі комірки, що містять значення в C4, замість усіх комірок, рівних C4, ви можете додати підстановочні знаки до таких критеріїв:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Тепер COUNTIF буде рахувати клітинки з підрядком "Джон" де завгодно в комірці.
Продуктивність
Загалом, не є доброю практикою вказувати діапазон, який включає всі комірки робочого аркуша. Це може спричинити проблеми з продуктивністю, оскільки асортимент включає мільйони та мільйони комірок. У цьому прикладі проблема ускладнюється, оскільки формула використовує функцію INDIRECT, яка є нестабільною функцією. Нестабільні функції перераховуються при кожній зміні робочого аркуша, тому вплив на продуктивність може бути величезним.
По можливості обмежте діапазони розумним розміром. Наприклад, якщо ви знаєте, що дані не відображатимуться після 1000 рядків, ви можете шукати лише перші 1000 рядків таким чином:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
