Підрахувати входження певного тексту у всій книзі Excel

Нам легко підрахувати, скільки разів певний текст або числове значення зустрічається в діапазоні клітинок з функцією COUNTIF. Однак, щоб підрахувати появу певного тексту у всій книзі, потрібна більш складна формула. У цій статті буде продемонстровано формулу на основі функцій SUMPRODUCT, COUNTIF та INDIRECT для вирішення цієї проблеми.

Як підрахувати випадки у всій книзі?

Припустимо, що у вашій робочій книзі є 4 робочі аркуші (аркуш 2, аркуш 3, аркуш 4 і аркуш 5), і ви хочете підрахувати, скільки разів на цих 4 робочих аркушах з’являється певний текст «Ноутбук».

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

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&range1&”‘!”&range2),criteria))

Аргументи

Як користуватися цією формулою?

1. Спочатку потрібно створити новий аркуш і перелічити всі назви аркушів у діапазоні клітинок. У цьому випадку я перераховую всі назви аркушів у діапазоні B7: B10.

2. Виберіть порожню комірку для виведення результату.

3. Введіть одну з наведених нижче формул і натисніть клавішу Enter, щоб отримати результат.

Якщо ви вже ввели певний текст у клітинку (наприклад, B3), ви можете посилатися на цю клітинку у формулі таким чином:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

Або безпосередньо введіть певний текст з подвійними лапками у формулі так:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),"Laptop"))

Примітка: Тут діапазон A1: Z10000 - це довільний діапазон, ви можете змінити його відповідно до ваших даних

Як працює ця формула?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

• "'" & B7: B10 & "'! A1: Z10000": Тут кожне ім'я аркуша в масиві приєднано за допомогою конкатенації до діапазону A1: Z10000, і ви отримаєте масив як {"'Sheet2'! A1: Z10000"; "'Sheet3'! A1: Z10000"; "'Sheet4' ! A1: Z10000 ";" 'Аркуш5'! A1: Z10000 "}.
• INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): Функція INDIRECT позначає діапазони на чотирьох аркушах.
• COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): Функція COUNTIF підраховує кількість разів, коли конкретне значення з'являється на кожному аркуші, і повертає масив {13; 13; 13; 13}. Це означає, що певний текст з'являється 13 разів на кожному аркуші.
• ПІДВИСОК ({13; 13; 13; 13}): Функція SUMPRODUCT підсумовує всі числа в масиві та повертає кінцевий результат як 52.

Супутні функції

Пов’язані формули