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

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

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

Предположим, в вашей книге есть четыре рабочих листа — Лист2, Лист3, Лист4 и Лист5 — и вы хотите узнать, сколько раз на них встречается определённый текст «Ноутбук». Выполните следующие действия, чтобы получить результат.

Универсальная формула

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

Аргументы

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

1. Сначала создайте новый лист и укажите все имена листов в диапазоне ячеек — в данном случае они перечислены в диапазоне B7:B10.

2. Выберите пустую ячейку, куда будет выведен результат.

3. Введите в неё одну из приведённых ниже формул и нажмите клавишу Enter, чтобы instantly получить результат.

Если вы уже ввели искомый текст в ячейку (например, 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 раз на каждом из этих листов.
• SUMPRODUCT({13;13;13;13}): функция СУММПРОИЗВ складывает все числа в массиве и возвращает итоговый результат — 52.

Связанные функции

Связанные формулы