Как подсчитать отфильтрованные ячейки с текстом в Excel?
В Excel подсчет ячеек с текстом прост. Но когда дело доходит до подсчета отфильтрованных ячеек с текстом, задача становится сложнее. В этом руководстве будут представлены три способа подсчета ячеек с текстом из отфильтрованного списка.
Подсчет отфильтрованных текстовых ячеек с использованием вспомогательного столбца
Подсчет отфильтрованных текстовых ячеек с использованием вспомогательного столбца
Используя функцию СЧЁТЕСЛИ вместе с вспомогательным столбцом, мы можем легко подсчитать отфильтрованные текстовые ячейки. Пожалуйста, выполните следующие действия.
1. Пожалуйста, скопируйте формулу ниже в ячейку D2, затем нажмите клавишу "Enter", чтобы получить первый результат.
=SUBTOTAL (103, A2)
Примечание: Вспомогательный столбец с формулой ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для проверки, отфильтрована ли строка или нет. А "103" означает функцию "СЧЁТЗ" в аргументе "номер_функции".
2. Затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу.
3. Пожалуйста, скопируйте формулу ниже в ячейку "F2", затем нажмите клавишу "Enter", чтобы получить окончательный результат.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Мы видим, что в отфильтрованных данных есть "4" ячейки с текстом.
Подсчет отфильтрованных текстовых ячеек с использованием функций СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, ДВССЫЛ, СТРОКА и ЕТЕКСТ
Другой метод подсчета отфильтрованных ячеек с текстом заключается в использовании "комбинации" функций "СУММПРОИЗВ", "ПРОМЕЖУТОЧНЫЕ.ИТОГИ", "ДВССЫЛ", "СТРОКА" и "ЕТЕКСТ". Пожалуйста, выполните следующие действия.
Пожалуйста, скопируйте формулу ниже в ячейку "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 текст, и возвращает Да для ячеек с текстом, Нет для других ячеек. Двойной унарный оператор (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0.
- "СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(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)))
Объяснение формулы:
- "СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1)" возвращает индивидуальные ссылки на ячейки из диапазона A2:A18.
- "ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103, СМЕЩ(A2:A18, СТРОКА(A2:A18)-2 -- МИН(СТРОКА(A2:A18)-2),,1))" проверяет, отфильтрована ли строка или нет, и возвращает 1 для видимых ячеек, 0 для скрытых и пустых ячеек.
- "ЕТЕКСТ(A2:A18)" проверяет, содержит ли каждая ячейка в диапазоне A2:A18 текст, и возвращает Да для ячеек с текстом, Нет для других ячеек. Двойной унарный оператор (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0.
- "СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(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
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!