Подсчет вхождений определенного текста во всей книге Excel
Нам легко подсчитать, сколько раз конкретный текст или числовое значение встречается в диапазоне ячеек с помощью функции СЧЁТЕСЛИ. Однако для подсчета вхождений определенного текста во всей книге требуется более сложная формула. В этой статье будет продемонстрирована формула, основанная на функциях СУММПРОИЗВ, СЧЁТЕСЛИ и КОСВЕННО для решения этой проблемы.
Как подсчитать вхождения во всей книге?
Предположим, что в вашей книге 4 рабочих листа (Sheet2, Sheet3, Sheet4 и Sheet5), и вы хотите подсчитать, сколько раз конкретный текст «Портативный компьютер» появляется на этих 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 ";" 'Sheet5'! A1: Z10000 "}.
- INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): Функция КОСВЕННО ссылается на диапазоны на четырех листах.
- COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): Функция СЧЁТЕСЛИ подсчитывает, сколько раз конкретное значение появляется на каждом листе, и возвращает массив {13; 13; 13; 13}. Это означает, что конкретный текст появляется на каждом листе 13 раз.
- СУММПРОИЗВ ({13; 13; 13; 13}): Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 52.
Связанные функции
Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.
Функция СЧЁТЕСЛИ в Excel
Функция Excel СЧЁТЕСЛИ - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.
Функция КОСВЕННО в Excel
Функция ДВССЫЛ в Excel преобразует текстовую строку в действительную ссылку.
Родственные формулы
Подсчитайте числа, которые начинаются с определенного числа
В этом руководстве представлена формула на основе функций СУММПРОИЗВ и ВЛЕВО для подсчета количества ячеек, содержащих числа, начинающиеся с определенного числа в Excel.
Подсчитайте несколько критериев с логикой НЕ в Excel
Эта статья покажет вам, как подсчитать количество ячеек с несколькими критериями с логикой НЕ в Excel.
Считайте или суммируйте только целые числа в Excel
В этом посте представлены две формулы на основе функции СУММПРОИЗВ и ИЗМЕНИТЬ, которые помогут вам подсчитывать и суммировать только целые числа в диапазоне ячеек в Excel.
Подсчитайте числа, где n-я цифра равна заданному числу
В этом руководстве представлена формула, основанная на функциях СУММПРОИЗВ и СРЕДНЕЕ, для подсчета чисел, где n-я цифра равна заданному числу в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
- Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
