Перейти к содержимому

Как подсчитать отфильтрованные ячейки с текстом в Excel?

Author: Zhoumandy Last Modified: 2025-08-07

В Excel подсчет ячеек с текстом прост. Но когда дело доходит до подсчета отфильтрованных ячеек с текстом, задача становится сложнее. В этом руководстве будут представлены три способа подсчета ячеек с текстом из отфильтрованного списка.

A screenshot of counting filtered cells with text in Excel

Подсчет отфильтрованных текстовых ячеек с использованием вспомогательного столбца

Подсчет отфильтрованных текстовых ячеек с использованием функций СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, ДВССЫЛ, СТРОКА и ЕТЕКСТ

Подсчет отфильтрованных текстовых ячеек с использованием функций СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, СМЕЩ, МИН, СТРОКА и ЕТЕКСТ

Подсчет отфильтрованных текстовых ячеек с использованием вспомогательного столбца

Используя функцию СЧЁТЕСЛИ вместе с вспомогательным столбцом, мы можем легко подсчитать отфильтрованные текстовые ячейки. Пожалуйста, выполните следующие действия.

1. Пожалуйста, скопируйте формулу ниже в ячейку D2, затем нажмите клавишу "Enter", чтобы получить первый результат.

=SUBTOTAL (103, A2)

A screenshot of the formula for the SUBTOTAL function in Excel for counting filtered cells with text, placed in cell D2

Примечание: Вспомогательный столбец с формулой ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для проверки, отфильтрована ли строка или нет. А "103" означает функцию "СЧЁТЗ" в аргументе "номер_функции".
A screenshot showing the SUBTOTAL function returning results for counting filtered cells with text in Excel

2. Затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу.
A screenshot showing the filled SUBTOTAL formula being dragged down in Excel

3. Пожалуйста, скопируйте формулу ниже в ячейку "F2", затем нажмите клавишу "Enter", чтобы получить окончательный результат.

=COUNTIFS(A2:A18,"*", D2:D18, 1)

A screenshot of the COUNTIFS formula used in Excel to count filtered cells with text in the dataset

Мы видим, что в отфильтрованных данных есть "4" ячейки с текстом.

Подсчет отфильтрованных текстовых ячеек с использованием функций СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, ДВССЫЛ, СТРОКА и ЕТЕКСТ

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

Пожалуйста, скопируйте формулу ниже в ячейку "E2", затем нажмите клавишу "Enter", чтобы получить результат.

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))

A screenshot of the SUMPRODUCT and SUBTOTAL combination formula used to count filtered cells with text in Excel

Объяснение формулы:
  1. "СТРОКА(A2:A18)" возвращает соответствующие номера строк диапазона A2:A18.
  2. "ДВССЫЛ("A"&СТРОКА(A2:A18))" возвращает действительные ссылки на ячейки из заданного диапазона.
  3. "ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103, ДВССЫЛ("A"&СТРОКА(A2:A18)))" проверяет, отфильтрована ли строка или нет, и возвращает 1 для видимых ячеек, 0 для скрытых и пустых ячеек.
  4. "ЕТЕКСТ(A2:A18)" проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, и возвращает Да для ячеек с текстом, Нет для других ячеек. Двойной унарный оператор (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0.
  5. "СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103, ДВССЫЛ("A"&СТРОКА(A2:A18))), --(ЕТЕКСТ(A2:A18)))" можно рассматривать как "СУММПРОИЗВ({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})". Затем СУММПРОИЗВ перемножает два массива и возвращает сумму значений, которая равна 4.

Подсчет отфильтрованных текстовых ячеек с использованием функций СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, СМЕЩ, МИН, СТРОКА и ЕТЕКСТ

Третий метод подсчета ячеек с текстом из отфильтрованных данных — это "объединение" функций "СУММПРОИЗВ", "ПРОМЕЖУТОЧНЫЕ.ИТОГИ", "СМЕЩ", "МИН", "СТРОКА" и "ЕТЕКСТ". Пожалуйста, выполните следующие действия.

Пожалуйста, скопируйте формулу ниже в ячейку "E2", затем нажмите клавишу "Enter", чтобы получить результат.

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)))

A screenshot of the SUMPRODUCT formula with OFFSET, MIN, and ISTEXT functions to count filtered cells with text in Excel

Объяснение формулы:
  1. "СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1)" возвращает индивидуальные ссылки на ячейки из диапазона A2:A18.
  2. "ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103, СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1))" проверяет, отфильтрована ли строка или нет, и возвращает 1 для видимых ячеек, 0 для скрытых и пустых ячеек.
  3. "ЕТЕКСТ(A2:A18)" проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, и возвращает Да для ячеек с текстом, Нет для других ячеек. Двойной унарный оператор (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0.
  4. "СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103, СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1)), -- (ЕТЕКСТ(A2:A18)))" можно рассматривать как "СУММПРОИЗВ({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})". Затем СУММПРОИЗВ перемножает два массива и возвращает сумму значений, которая равна 4.

Другие операции (статьи)

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

Как подсчитать ячейки, содержащие числа или нет, в Excel?
Если есть диапазон ячеек, некоторые содержат числа, а другие текст, как быстро подсчитать ячейки, содержащие числа или нет, в Excel?

Как подсчитать ячейки, если выполняется одно из нескольких условий в Excel?
Что делать, если нужно подсчитать ячейки, содержащие одно из нескольких условий? Здесь я поделюсь способами подсчета ячеек, если они содержат X или Y или Z … и т. д., в Excel.

Как подсчитать ячейки с определенным текстом и цветом заливки/шрифта в Excel?
Знаете ли вы, как подсчитывать ячейки с несколькими условиями? Например, подсчитать количество ячеек, содержащих определенный текст и цвет шрифта/заливки. Эта статья покажет вам решение.

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Переворот в анализе данных на основе: Интеллектуальное выполнение   |  Сгенерировать код  |  Создать Пользовательские Формулы  |  Анализ данных и генерация диаграмм  |  Вызов Kutools Functions…
Популярные функции: Найти, выделить или отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячейки без потери данных   |   Округлить...
Супер ПОИСК: VLookup с несколькими критериями  |   VLookup с несколькими значениями  |   Многолистовой поиск   |   Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскр. список   |  Зависимый раскрывающийся список   |  Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки   |  Дизайн листа   |   Улучшенная строка формулы   |  Управление книгой и листами   |  Библиотека автотекста (Auto Text)   |  Выбор даты   |  Объединить данные   |  Шифрование/Расшифровка ячеек   |  Отправить письмо по списку   |  Супер фильтр   |   Специальный фильтр (фильтр по жирному/курсиву/зачеркиванию...) ...
Топ–15 наборов инструментов: 12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...)   |   50+ типов диаграмм (Диаграмма Ганта, ...)   |   40+ практических формул (Расчет возраста на основе даты рождения, ...)   |   19 инструментов вставки (Вставить QR-код, Вставить изображение из пути, ...)   |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...)   |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...)   |   ... и многое другое
Используйте Kutools на вашем предпочитаемом языке – поддерживаются английский, испанский, немецкий, французский, китайский и более40 других языков!

Повысьте свои навыки работы в Excel с Kutools для Excel и ощутите новую эффективность. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени.  Щелкните здесь, чтобы получить наиболее нужную вам функцию...

Office Tab добавляет вкладочный интерфейс в Office, делая вашу работу значительно проще

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в новых окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни кликов мышью ежедневно!