KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторЧжоу МаньдиДата изменения

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

Снимок экрана подсчёта отфильтрованных ячеек с текстом в Excel

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

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

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

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

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

1. Скопируйте приведённую ниже формулу в ячейку D2 и нажмите клавишу «Enter», чтобы получить первый результат.

=SUBTOTAL (103, A2)

Снимок экрана формулы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для подсчёта отфильтрованных ячеек с текстом, расположенной в ячейке D2

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

2. Затем перетащите маркер заполнения вниз до ячеек, к которым нужно применить эту формулу.
Снимок экрана заполненной формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ, протягиваемой вниз в Excel

3. Скопируйте приведённую ниже формулу в ячейку F2 и нажмите Enter, чтобы получить окончательный результат.

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

Снимок экрана формулы СЧЁТЕСЛИМН, используемой в Excel для подсчёта отфильтрованных ячеек с текстом в наборе данных

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

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

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

Скопируйте приведённую ниже формулу в ячейку E2 и нажмите Enter, чтобы instantly получить результат.

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

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

Пояснение формулы:
  1. «ROW(A2:A18)» возвращает номера строк, соответствующие диапазону A2:A18.
  2. Формула «INDIRECT("A"&ROW(A2:A18))» корректно возвращает ссылки на ячейки указанного диапазона.
  3. Формула «SUBTOTAL(103; INDIRECT(«A»&ROW(A2:A18)))» определяет, отфильтрована ли строка, и возвращает 1 для видимых ячеек, а также 0 — для скрытых и пустых.
  4. Формула «ISTEXT(A2:A18)» проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, возвращая ИСТИНА для ячеек с текстом и ЛОЖЬ — для всех остальных. Унарный оператор двойного минуса (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно.
  5. Формулу «SUMPRODUCT(SUBTOTAL(103, INDIRECT(«A»&ROW(A2:A18))), --(ISTEXT(A2:A18)))» можно представить как «SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})». Далее функция СУММПРОИЗВ перемножает элементы двух массивов и возвращает сумму результатов, равную 4.

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

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

Скопируйте приведённую ниже формулу в ячейку E2 и нажмите Enter, чтобы instantly получить результат.

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

Снимок экрана формулы СУММПРОИЗВ с функциями ДВССЫЛ, МИН и ЕТЕКСТ для подсчёта отфильтрованных ячеек с текстом в Excel

Пояснение формулы:
  1. Формула «OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)» возвращает отдельные ссылки на ячейки из диапазона A2:A18.
  2. Формула «SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 - MIN(ROW(A2:A18)-2),,1))» определяет, отфильтрована ли строка, и возвращает 1 для видимых ячеек, 0 — для скрытых и пустых.
  3. Формула «ISTEXT(A2:A18)» проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, возвращая ИСТИНА для ячеек с текстом и ЛОЖЬ — для всех остальных. Унарный оператор двойного минуса (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно.
  4. Формулу «SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)))» можно представить как «SUMPRODUCT({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 Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  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.

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