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

Как посчитать ячейки с текстом в 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, игнорируя те, которые содержат пробел, используйте следующую формулу:
=СЧЁТЕСЛИМН(A2:A7,“)” title=”Формула для подсчета ячеек с текстом, исключая ячейки, содержащие пробелы”/>

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

аргумент:

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

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

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

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

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

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

“)

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

*”)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=СЧЁТЕСЛИ(A2:A10, “*”)
А теперь вы сужаете список по какому-то критерию, скажем, отфильтровываете товары, количество которых больше 10. Вопрос в том, сколько товаров осталось?

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

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

    аргумент:

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

    :

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

    В этом примере мы хотим подсчитывать только видимые ячейки с текстом независимо от того, как были скрыты другие ячейки, поэтому мы вводим вторую формулу в A2 и копируем ее в A10.
    Для видимых ячеек формула возвращает 1. Как только вы отфильтруете или скроете вручную некоторые строки, формула вернет для них 0.  (Вы не увидите эти нули, потому что они возвращаются для скрытых строк. Чтобы убедиться, что это работает таким образом, просто скопируйте содержимое скрытой ячейки с формулой промежуточного итога в любую видимую, скажем, =D2, предполагая, что строка 2 скрыта. .)

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

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

=СЧЁТЕСЛИМН(A2:A10, “*”, D2:D10, 1)
Теперь вы можете фильтровать данные так, как хотите, и формула сообщит вам, сколько отфильтрованных ячеек в столбце A содержат текст (3 в нашем случае):

Формула 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 для подсчета ячеек с текстом

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *