Как подсчитать отфильтрованные ячейки с текстом в Excel?
В Excel подсчет ячеек с текстом выполняется просто. Но когда дело доходит до подсчет отфильтрованных ячеек с текстом, все становится сложно. Этот учебник познакомит три пути для подсчета ячеек с текстом из отфильтрованного списка.
Подсчет отфильтрованных ячеек с текстом с помощью вспомогательного столбца
Используя функцию СЧЁТЕСЛИ, дополнительно с помощью вспомогательного столбца, мы можем легко подсчитать отфильтрованные ячейки с текстом. Пожалуйста, сделайте следующее.
1. Пожалуйста, скопируйте приведенную ниже формулу в ячейку D2, затем нажмите клавишу Enter, чтобы получить первый результат.
=ПРОМЕЖУТОЧНЫЙ ИТОГ (103, A2)
Примечание. Вспомогательный столбец с формулой ПРОМЕЖУТОЧНЫЕ.ИТОГИ предназначен для проверки того, отфильтрована строка или нет. А также 103 означает COUNTA функции в номер_функции аргумент.
2. затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу.
3. Пожалуйста, скопируйте приведенную ниже формулу в ячейку F2, затем нажмите Enter Ключ, чтобы получить окончательный результат.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Мы видим, что есть 4 ячейки с текстом в отфильтрованных данных.
Другой метод подсчета отфильтрованных ячеек с текстом — использование сочетание SUMPRODUCT, SUBTOTAL, КОСВЕННЫЕ, РЯД & ISTEXT Функции. Пожалуйста, сделайте следующее.
Пожалуйста, скопируйте приведенную ниже формулу в ячейку E2, затем нажмите Enter ключ для получения результата.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Объяснение формулы:
- РЯД(A2:A18) возвращает соответствующие номера строк диапазона A2:A18.
- ДВССЫЛ ("A"&СТРОКА(A2:A18)) возвращает действительные ссылки на ячейки из заданного диапазона.
- ИТОГО(103, ДВССЫЛ("A"&СТРОКА(A2:A18))) проверяет, отфильтрована строка или нет, и возвращает 1 для видимых ячеек, 0 для скрытых и пустых ячеек.
- ИСТЕКСТ(A2:A18) проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, и возвращает True для ячеек с текстом, False для остальных ячеек. Двойной унарный оператор (--) преобразует значения TRUE и FALSE в 1 и 0.
- 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.
Третий метод подсчета ячеек с текстом из отфильтрованных данных: конкатенации SUMPRODUCT, SUBTOTAL, OFFSET, MIN, РЯД & ISTEXT Функции. Пожалуйста, сделайте следующее.
Пожалуйста, скопируйте приведенную ниже формулу в ячейку E2, затем нажмите Enter ключ для получения результата.
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1)), -- (ВСТЭКСТ(A2:A18)) )
Объяснение формулы:
- OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) возвращает ссылки на отдельные ячейки из диапазона A2:A18.
- SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) проверяет, отфильтрована ли строка или нет, и возвращает 1 для видимых ячеек, 0 для скрытых и пустых ячеек.
- ИСТЕКСТ(A2:A18) проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, и возвращает True для ячеек с текстом, False для остальных ячеек. Двойной унарный оператор (--) преобразует значения TRUE и FALSE в 1 и 0.
- СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1)), -- (ВСТЭКСТ(A2:A18))) можно рассматривать как SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Затем СУММПРОИЗВ умножает два массива вместе и возвращает сумму значений, которая равна 4.
Прочие операции (статьи)
Объедините функции COUNTIF и LEFT для подсчета ячеек, начинающихся с определенного символа в Excel
Чтобы подсчитать количество ячеек, которые начинаются с определенного символа в Excel, вы можете комбинировать функции СЧЁТЕСЛИ и ВЛЕВО. На самом деле есть разные формулы, позволяющие этого достичь. Это руководство поможет вам шаг за шагом.
Как подсчитать ячейки, содержащие числа или нет в Excel?
Если есть диапазон ячеек, некоторые из которых содержат числа, а другие - текст, как вы можете подсчитать ячейки, содержащие числа, или не быстро в Excel?
Как подсчитать ячейки, если в Excel выполнен один из нескольких критериев?
Что делать, если подсчет ячеек содержит один из нескольких критериев? Здесь я поделюсь способами подсчета ячеек, если они содержат X, Y или Z… и т. д. в Excel.
Как подсчитать ячейки с определенным текстом и цветом заливки/шрифта в Excel?
Знаете ли вы, как считать ячейки с несколькими условиями? Например, подсчитайте количество ячеек, которые содержат как определенный текст, так и цвет шрифта/заливки. Эта статья покажет вам решение.
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
- Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
