Note: The other languages of the website are Google-translated. Back to English

Підрахувати входження певного тексту у всій книзі 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 - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL