Перейти до основного матеріалу

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

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


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

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

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

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

Аргументи

Діапазон1: Діапазон комірок містить усі назви аркушів поточної книги.
Діапазон2: Діапазон клітинок, з яких потрібно підрахувати конкретне значення.
Критерії: Конкретний текст, який ви будете перераховувати на аркушах.

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

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.

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

Функція Excel SUMPRODUCT
Функцію Excel SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім для отримання суми продуктів.

Функція Excel COUNTIF
Функція Excel COUNTIF - це статистична функція в Excel, яка використовується для підрахунку кількості клітинок, які відповідають критерію.

Функція Excel INDIRECT
Функція Excel INDIRECT перетворює текстовий рядок у дійсне посилання.


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

Порахувати числа, які починаються з певного числа
У цьому посібнику представлена ​​формула на основі функцій SUMPRODUCT та LEFT для підрахунку кількості клітинок, що містять числа, які починаються з певних чисел у Excel.

Підрахуйте кілька критеріїв з логікою NOT в Excel
У цій статті буде показано, як підрахувати кількість клітинок з кількома критеріями з логікою NOT у Excel.

Підрахувати або підсумувати лише цілі числа в Excel
Ця публікація містить дві формули на основі функції SUMPRODUCT та функції MOD, які допоможуть підрахувати та підсумувати лише цілі числа в діапазоні клітинок у Excel.

Порахуйте числа, де n -а цифра дорівнює даному числу
Цей посібник містить формулу на основі SUMPRODUCT та функції MID для підрахунку чисел, де n -а цифра дорівнює заданому числу в Excel.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations