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

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

Author: Zhoumandy Last Modified: 2025-06-05

В 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
Популярные функции: Найти, выделить или отметить дубликаты | Удалить пустые строки | Объединить столбцы или ячейки без потери данных |   Округлить без формулы ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP с несколькими значениями | Многолистовой поиск | Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Избранные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгами и листами | Библиотека автотекста | Выбор даты | Объединить данные | Шифрование/расшифровка ячеек | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр ячеек с жирным/курсивом/зачёркнутым...) ...
Топ-15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ практических формул (Расчет возраста на основе даты рождения, ...) |19 инструментов вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов для объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое

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


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

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