любые, определенные или отфильтрованные ячейки

Как посчитать ячейки с текстом в Excel? Существует несколько различных формул для подсчета ячеек, которые содержат любой текст, определенные символы или только отфильтрованные ячейки. Все формулы работают в Excel 2019, 2016, 2013 и 2010.

Изначально электронные таблицы Excel были предназначены для работы с числами. Но в наши дни мы также часто используем их для хранения и обработки текста. Хотите знать, сколько ячеек с текстом на вашем листе? Microsoft Excel имеет несколько функций для этого. Какой из них вы должны использовать? Ну, это зависит от ситуации. В этом уроке вы найдете множество формул и то, когда каждую формулу лучше всего использовать.

Как подсчитать количество ячеек с текстом в Excel

Есть две основные формулы, чтобы определить, сколько ячеек в заданном диапазоне содержат любую текстовую строку или символ.

Формула COUNTIF для подсчета всех ячеек с текстом

Если вы хотите найти количество ячеек с текстом в Excel, функция СЧЁТЕСЛИ со звездочкой в критерии аргумент – лучшее и самое простое решение:

СЧЁТЕСЛИ(диапазон“*”)

Потому что звездочка

является подстановочным знаком, который соответствует любой последовательности символов, формула подсчитывает все ячейки, содержащие любой текст.

Формула СУММПРОИЗВ для подсчета ячеек с любым текстом

Другой способ получить количество ячеек, содержащих текст, — объединить функции СУММПРОИЗВ и ИСТЕКСТ:СУММПРОИЗВ(–ИСТЕКСТ(диапазон

))

Или жеСУММПРОИЗВ(ИСТЕКСТ(диапазон

)*1)

Функция ISTEXT проверяет, содержит ли каждая ячейка в указанном диапазоне какие-либо текстовые символы, и возвращает массив значений TRUE (ячейки с текстом) и FALSE (другие ячейки). Двойная унарная (–) или операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно, создавая массив единиц и нулей. Функция СУММПРОИЗВ суммирует все элементы массива и возвращает количество единиц, то есть количество ячеек, содержащих текст.

Чтобы лучше понять, как работают эти формулы, посмотрите, какие значения учитываются, а какие нет:

• Что считается Что не считается
• Ячейки с любым текстом
• Специальные символы
• Числа в текстовом формате

• Визуально пустые ячейки, содержащие пустую строку (“”), апостроф (‘), пробел или непечатаемые символы.
• Числа
• Даты
• Логические значения ИСТИНА и ЛОЖЬ
• Ошибки

Пустые ячейки

Например, для подсчета ячеек с текстом в диапазоне A2:A10, исключая числа, даты, логические значения, ошибки и пустые ячейки, используйте одну из следующих формул:

=СЧЁТЕСЛИ(A2:A10, “*”)

=СУММПРОИЗВ(–ИСТЕКСТ(A2:A10))

=СУММПРОИЗВ(ИСТЕКСТ(A2:A10)*1)

Формула Excel для подсчета ячеек с текстом

Подсчет ячеек с текстом, исключая пробелы и пустые строки

Формулы, рассмотренные выше, подсчитывают все ячейки, в которых есть какие-либо текстовые символы. Однако в некоторых ситуациях это может сбивать с толку, поскольку некоторые ячейки могут только выглядеть пустыми, но на самом деле содержать символы, невидимые человеческому глазу, такие как пустые строки, апострофы, пробелы, разрывы строк и т. д. В результате визуально пустой ячейка подсчитывается по формуле, заставляющей пользователя рвать на себе волосы, пытаясь понять, почему 🙂

Чтобы исключить из подсчета «ложноположительные» пустые ячейки, используйте функцию СЧЁТЕСЛИМН с символом «исключено» во втором критерии.

Например, чтобы подсчитать ячейки с текстом в диапазоне A2:A7, игнорируя те, которые содержат пробел, используйте следующую формулу:
“)” title=”Формула для подсчета ячеек с текстом, исключая ячейки, содержащие пробелы”/>

Формула для подсчета ячеек с текстом, исключая ячейки, содержащие пробелы Если ваш целевой диапазон содержит какие-либо данные, управляемые формулой, некоторые формулы могут привести к пустой строке (“”). Чтобы игнорировать ячейки с пустыми строками, замените «*» на «*?*» в критерии1

аргумент:

=СЧЁТЕСЛИМН(A2:A9,”*?*”, A2:A9, “<>“)

Знак вопроса, окруженный звездочками, указывает на то, что в ячейке должен быть хотя бы один текстовый символ. Поскольку пустая строка не содержит символов, она не соответствует критериям и не учитывается. Пустые ячейки, начинающиеся с апострофа (‘), также не учитываются.

Подсчет ячеек с текстом, исключая пробелы и пустые строки

Как посчитать ячейки с определенным текстом в Excel Чтобы получить количество ячеек, содержащих определенный текст или символ, вы просто вводите этот текст в критерии

аргумент функции СЧЁТЕСЛИ. Приведенные ниже примеры поясняют нюансы.

Чтобы точно соответствовать образцу текста, введите полный текст, заключенный в кавычки:СЧЁТЕСЛИ(диапазон“текст

“)

Чтобы подсчитать ячейки с частичным совпадением, поместите текст между двумя звездочками, которые обозначают любое количество символов до и после текста:СЧЁТЕСЛИ(диапазон“*текст

*”)

Например, чтобы узнать, сколько ячеек в диапазоне A2:A7 содержат слово «бананы», используйте следующую формулу:

=СЧЁТЕСЛИ(A2:A7, “бананы”)

Чтобы подсчитать все ячейки, которые содержат «бананы» как часть своего содержимого в любой позиции, используйте это:

=СЧЁТЕСЛИ(A2:A7, “*бананы*”)

Чтобы сделать формулу более удобной для пользователя, вы можете поместить критерии в предопределенную ячейку, скажем, D2, и поместить ссылку на ячейку во второй аргумент:

=СЧЁТЕСЛИ(A2:A7, D2)

• В зависимости от ввода в D2 формула может полностью или частично соответствовать образцу текста: Для полного совпадения введите слово или фразу целиком, как они представлены в исходной таблице, напримерБананы
• . Для частичного совпадения введите образец текста, окруженный подстановочными знаками, например*бананы*

. Поскольку формула нечувствительна к регистру, вы можете не беспокоиться о регистре букв, а это означает, что *бананы*

Формулы для подсчета ячеек, содержащих определенный текст — точное и частичное совпадение

В качестве альтернативы, чтобы подсчитать ячейки с частичным совпадением, соедините ссылку на ячейку и подстановочные знаки, например:

Формула для подсчета ячеек с определенным текстом в Excel

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

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

При использовании фильтра Excel для отображения только актуальных на данный момент данных иногда может потребоваться подсчет видимых ячеек с текстом. К сожалению, для этой задачи не существует решения в один клик, но приведенный ниже пример с легкостью проведет вас по шагам. Предположим, у вас есть таблица, как показано на рисунке ниже. Некоторые записи были извлечены из более крупной базы данных с использованием формул, и при этом возникали различные ошибки. Вы хотите найти общее количество элементов в столбце A. Когда все строки видны, формула СЧЁТЕСЛИ, которую мы использовали для подсчет ячеек с текстом

работает удовольствие:

=СЧЁТЕСЛИ(A2:A10, “*”)

Отфильтрованные ячейки с текстом, который необходимо подсчитать

1. Чтобы подсчитать отфильтрованные ячейки с текстом, вам нужно сделать следующее:
2. В исходной таблице сделайте видимыми все строки. Для этого снимите все фильтры и отобразите скрытые строки.
   Добавьте вспомогательный столбец с формулой ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая указывает, отфильтрована строка или нет. Чтобы обрабатывать отфильтрованные ячейки, используйте 3 для номер_функции

   аргумент:

   =ПРОМЕЖУТОЧНЫЙ ИТОГ(3, A2) Чтобы идентифицировать все скрытые ячейки, отфильтрованные и спрятанные вручную, введите 103 вномер_функции

   :

   =ПРОМЕЖУТОЧНЫЙ ИТОГ(103, A2)

   В этом примере мы хотим подсчитывать только видимые ячейки с текстом независимо от того, как были скрыты другие ячейки, поэтому мы вводим вторую формулу в A2 и копируем ее в A10.
   

3. Идентификация видимых ячеек Используйте функцию СЧЁТЕСЛИМН с двумя разнымикритерии_диапазон/ критерии
   • пары для подсчета видимых ячеек с текстом:
   • Criteria1 — поиск ячеек с любым текстом (“*”) в диапазоне A2:A10.

   Criteria2 — ищет 1 в диапазоне D2:D10 для обнаружения видимых ячеек.

=СЧЁТЕСЛИМН(A2:A10, “*”, D2:D10, 1)

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

Если вы не хотите вставлять дополнительный столбец на лист, вам понадобится более длинная формула для выполнения задачи. Просто выберите тот, который вам больше нравится:

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, ДВССЫЛ(“A”&СТРОКА(A2:A10))), –(ISTEXT(A2:A10)))

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, СМЕЩ(A2:A10, СТРОКА(A2:A10) – МИН(СТРОКА(A2:A10)),,1)), — (ISTEXT(A2:A10)))

Оператор умножения также будет работать:

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, ДВССЫЛ(“A”&СТРОКА(A2:A10))) * (ISTEXT(A2:A10)))

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, СМЕЩ(A2:A10, СТРОКА(A2:A10)-МИН(СТРОКА(A2:A10)),,1)) * (ISTEXT(A2:A10)))

Формула для подсчета видимых ячеек с текстом

Как работают эти формулы

В первой формуле используется функция ДВССЫЛ, чтобы «подать» отдельные ссылки на все ячейки в указанном диапазоне в ПРОМЕЖУТОЧНЫЙ ИТОГ. Вторая формула использует комбинацию функций OFFSET, ROW и MIN для той же цели.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает массив из 1 и 0, где единицы представляют видимые ячейки, а нули соответствуют скрытым ячейкам (как во вспомогательном столбце выше).

Функция ISTEXT проверяет каждую ячейку в A2:A10 и возвращает TRUE, если ячейка содержит текст, иначе FALSE. Двойной унарный оператор (–) преобразует значения TRUE и FALSE в 1 и 0. На данный момент формула выглядит следующим образом:

=СУММПРОИЗВ({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})

Функция СУММПРОИЗВ сначала умножает элементы обоих массивов в одинаковых позициях, а затем суммирует полученный массив.

Поскольку умножение на ноль дает ноль, только ячейки, представленные 1 в обоих массивах, имеют 1 в конечном массиве.

=СУММПРОИЗВ({0;1;1;0;0;1;0;0;0})

А количество единиц в приведенном выше массиве — это количество видимых ячеек, содержащих текст.

Вот как посчитать ячейки с текстом в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Формулы Excel для подсчета ячеек с текстом

Вас также могут заинтересовать