Как посчитать ячейки с текстом в excel?

На прошлой неделе один из моих читателей попросил меня дать простую формулу, которую он может использовать для подсчета ячеек с текстом. По сути, он пытался выяснить, какой метод лучше всего использовать.

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

Я думаю, это зависит от ситуации. Здесь вам нужно понять одну вещь: ячейки с текстом означают любые ячейки, в которых нет чисел, пустых ячеек или ошибок.

Сегодня в этой статье я хотел бы поделиться 6 различными способами подсчета ячеек с текстом в разных ситуациях.

1. СЧЕТЕСЛИ с подстановочными знаками для подсчета ячеек с текстовыми значениями.

Лучшая формула для подсчета ячеек с текстом — СЧЕТЕСЛИ с подстановочным знаком (* — Звездочка). В таблице данных ниже у вас есть список имен и номеров мобильных телефонов в одном столбце. И из этого списка нужно посчитать количество ячеек с названиями (текстом). Формула будет:

 =COUNTIF(A2:A20,"*")

Когда вы введете его, он вернет количество ячеек только с текстом.

Вот как работает эта формула

Здесь мы использовали СЧЕТЕСЛИ для подсчета ячеек с определенными критериями, а для критериев мы использовали звездочку. Когда вы используете звездочку, она подсчитывает ячейки, содержащие любое количество символов, кроме логических значений, чисел (если они не введены в виде текста) и ошибок.

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

 =COUNTIFS(A2:A20,"*",A2:A20,"<> ")

2. СУММПРОИЗВ и ЕСТЕКСТ для подсчета количества ячеек с текстом.

Чтобы подсчитать количество ячеек, содержащих текст, вы также можете создать формулу, объединив СУММПРОИЗВ и ЕСТЕКСТ. И формула будет такая:

 =SUMPRODUCT(--ISTEXT(A2:A20))

Когда вы введете его, он вернет количество ячеек, содержащих текст.

Вот как работает эта формула

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

Во-первых, вы использовали ISTEXT, чтобы проверить, содержит ли ячейка текст или нет. Здесь ISTEXT возвращает массив , в котором для всех ячеек указано TRUE для ячеек с текстом и FALSE для всех остальных ячеек.

Во-вторых, вы добавляете двойной знак минус, чтобы преобразовать массив ИСТИНА/ЛОЖЬ в 1/0. Теперь для всех ячеек с текстом у вас есть 1 и 0 для всех остальных.

В-третьих, вы использовали СУММПРОИЗВ для суммирования этого массива. И эта сумма — количество ячеек, содержащих текст.

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

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

Теперь давайте копнем немного глубже. Предположим, вам нужно посчитать ячейки, в которых есть определенный текст. Вот пример. В таблице данных ниже вам нужно посчитать ячейку, в которой стоит «Джон». И для этого формула будет такой:

 =COUNTIF(A2:A20,"John")

Эта формула вернет количество ячеек, в которых находится указанный вами текст.

4. Ячейки с частичным текстом

Иногда это происходит, когда вам нужно подсчитать ячейки, сопоставив части текста. И в этом вам могут помочь COUNTIF и подстановочные знаки.

См. таблицу ниже, в которой объединены название продукта и номера счетов. И оттуда нужно посчитать количество ячеек с «Продуктом-А». Используйте формулу ниже:

 =COUNTIF(A2:A20,"Product-A*")

Эта формула вернет количество ячеек, в которых есть «Продукт-А».

5. Текст с учетом регистра

Теперь вот другое дело. Вам нужно подсчитать ячейки с определенным текстом, и это число должно быть чувствительным к регистру.

В таблице ниже вам нужно посчитать ячейки с именем «ДЖОН», но здесь у вас то же имя строчными буквами, но число должно быть в верхнем регистре.

Для этого вы можете объединить СУММПРОИЗВ с ТОЧНО.

 =SUMPRODUCT(--EXACT("JOHN",A1:A20))

Он вернет количество ячеек, в которых есть «ДЖОН».

Как работает эта формула?

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

Сначала вы использовали ТОЧНО для сравнения всего диапазона ячеек с текстом «ДЖОН». Он возвращает массив, в котором у вас есть TRUE для ячеек, где «ДЖОН», игнорируя «Джон», «Джон» и все остальные ячейки.

Во-вторых, вы добавляете двойной знак минус, чтобы преобразовать этот массив ИСТИНА/ЛОЖЬ в 1/0. Теперь для всех ячеек с «ДЖОНОМ» у вас есть 1 и 0 для всех остальных.

В-третьих, вы использовали СУММПРОИЗВ для суммирования этого массива. И эта сумма — количество ячеек, содержащих «ДЖОН».

5. VBA для подсчета ячеек с текстом

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

Для выбора:

 Sub countTextSelection() Dim rng As Range Dim i As Integer For Each rng In Selection If Application.WorksheetFunction.IsText(rng) Then i = i + 1 End If Next rng MsgBox i End Sub

Вся таблица:

 Sub countTextWorksheet() Dim rng As Range Dim i As Integer For Each rng In ActiveSheet.UsedRange If Application.WorksheetFunction.IsText(rng) Then i = i + 1 End If Next rng MsgBox i End Sub

Связанный: Что такое VBA в Excel

Заключение

Я уверен, что всех этих методов вам хватит, чтобы посчитать ячейки с текстом в той или иной ситуации. Вы можете использовать любой из методов, который, по вашему мнению, идеально подходит для вас.

В большинстве ситуаций первый метод применим без каких-либо проблем. Но, если вы хотите пойти дальше, вы можете использовать другие методы. Для меня VBA позволяет значительно сэкономить время, поскольку мне не нужно применять формулы. Теперь твоя очередь сказать мне кое-что.

Есть ли у вас другой метод, который мы можем использовать для подсчета количества ячеек с текстом?

Обязательно поделитесь со мной своими мыслями в комментариях, мне будет интересно услышать ваше мнение. И, пожалуйста, не забудьте поделиться этим постом со своими друзьями, я уверен, им он понравится.