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

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

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

Author Zhoumandy Last modified

В 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 по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ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% и уменьшите количество щелчков мышью на сотни ежедневно!

Все надстройки Kutools. Один установщик

Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.

Excel Word Outlook Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек