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

Подсчет вхождений определенного текста во всей книге Excel

Нам легко подсчитать, сколько раз конкретный текст или числовое значение встречается в диапазоне ячеек с помощью функции СЧЁТЕСЛИ. Однако для подсчета вхождений определенного текста во всей книге требуется более сложная формула. В этой статье будет продемонстрирована формула, основанная на функциях СУММПРОИЗВ, СЧЁТЕСЛИ и КОСВЕННО для решения этой проблемы.


Как подсчитать вхождения во всей книге?

Предположим, что в вашей книге 4 рабочих листа (Sheet2, Sheet3, Sheet4 и Sheet5), и вы хотите подсчитать, сколько раз конкретный текст «Портативный компьютер» появляется на этих 4 рабочих листах, вы можете сделать следующее, чтобы сделать это.

Общая формула

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

аргументы

Range1: В диапазоне ячеек перечислены все имена листов текущей книги.
Range2: Диапазон ячеек, из которых вы хотите подсчитать определенное значение.
Критерии: Конкретный текст, который вы будете считать по листам.

Как пользоваться этой формулой?

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 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места