Підрахувати входження певного тексту у всій книзі 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.
Супутні функції
Функція 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 - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
