Формула Excel: Підрахувати кількість випадків у всій книзі -

Загальна формула

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))

Резюме

Для підрахунку збігів у всій книзі ви можете використовувати формулу на основі функцій COUNTIF і SUMPRODUCT. У наведеному прикладі формула в D5 має вигляд:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))

де "аркуші" - це названий діапазон B8: B10.

Контекст

У цій книзі є чотири аркуші. Три з аркушів "Аркуш1", "Аркуш2" та "Аркуш3" містять 1000 випадкових імен у таблиці, яка виглядає так:

Примітка: діапазон, який ми використовуємо у формулі, A1: Z10000, є довільним і повинен бути скоригований відповідно до ваших даних.

Пояснення

Працюючи зсередини, ми спочатку маємо такий вираз усередині функції INDIRECT:

"'"&sheets&"'!A1:Z10000"

Оскільки "аркуші" - це іменований діапазон, який містить "Аркуш1", "Аркуш2" та "Аркуш3", ми отримуємо такий масив, як тільки вираз обчислюється:

("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")

Усередині масиву ми маємо три значення, і кожне - це ім’я аркуша, об’єднане через об’єднання в діапазон A1: Z10000. Зверніть увагу, що це всі текстові значення.

Далі, функція INDIRECT використовується для перетворення кожного текстового значення у відповідне посилання, яке передається функції COUNTIF як аргумент діапазону, разом із значенням у D5 для критеріїв.

Оскільки ми дали COUNTIF три окремі діапазони, ми повертаємо три результати в масиві, як це: