Перейти к основному содержанию

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

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

Видео

Подсчет ячеек, содержащих любой текст

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

Здесь в качестве примера я буду подсчитывать ячейки с любым текстом в диапазоне (например, диапазон A2:A14, как показано на снимке экрана ниже).

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

=COUNTIF(A2:A14, "*")

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

Заметки:
  • Вы также можете использовать SUMPRODUCT функция с ISTEXT функция для подсчета ячеек с текстом в Excel. 
    =SUMPRODUCT(--ISTEXT(A2:A14))
  • В формулах A2: A14 — это диапазон, в котором вы хотите подсчитать ячейки, содержащие текст.
  • В этом примере вы заметите, что число, отображаемое в ячейке A7 также считается одним из текстовых результатов. Это потому, что это число было введено в виде текста (с апострофом в начале).
  • В таблице ниже указано, какие ячейки будут считаться текстовыми, а какие нет.
    Ячейки, которые будут подсчитаны Ячейки, которые не будут учитываться
    • Ячейки с любым текстом
    • Ячейки с любыми специальными символами
    • Ячейки с номером, введенным в виде текста
    • Ячейки только с пробелами
    • Ячейки с апострофом (')
    • Ячейки с пустой строкой (="")
    • Ячейки, содержащие только непечатаемые символы
    • Ячейки с цифрами
    • Ячейки с датами
    • Клетки со временем
    • Ячейки со значениями ошибок, вызванными формулами
    • Ячейки с логическими значениями (ИСТИНА и ЛОЖЬ)
    • Пустые ячейки

Подсчитайте ячейки только с видимым текстом

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

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

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

=COUNTIFS(A2:A13,"*?*", A2:A13, "<> ")

Как видно на скриншоте выше, результат «5» в ячейке D3 — это количество видимых текстовых ячеек в диапазоне A2:A13.

Заметки:
  • В формулах A2:A13 — это диапазон, в котором вы хотите подсчитать ячейки, содержащие видимый текст.
  • Используя эту формулу, из результата будут исключены ячейки, которые кажутся пустыми, но содержат невидимые символы, такие как пробелы, апострофы, пустые строки и т. д.
  • Чтобы подсчитать ячейки с текстом, исключая только пробелы, попробуйте эту формулу. 
    =COUNTIFS(A2:A13,"*",A2:A13,"<> ")

Подсчет ячеек, содержащих определенный текст в Excel

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

Подсчитать ячейки с определенным текстом (точное совпадение)

Для подсчета ячеек, точно соответствующих определенному тексту, например, как показано на снимке экрана ниже, для подсчета ячеек в диапазоне A2:A12, полностью соответствующих тексту «Apple», формула в этом разделе может помочь. Пожалуйста, сделайте следующее.

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

=COUNTIF(A2:A12, D4)

Как показано на скриншоте выше, теперь подсчитано количество ячеек, которые точно соответствуют тексту «Яблоко».

Заметки:
  • В этой формуле, A2: A12 — это диапазон, в котором вы хотите подсчитать ячейки, содержащие определенный текст. D4 — это ячейка, содержащая определенный текст, на основе которого вы будете подсчитывать ячейки.
  • Вы можете напрямую ввести конкретный текст в формулу, если он не введен в ячейку заранее. В таких случаях измените формулу следующим образом: 
    =COUNTIF(A2:A12, "Apple")
  • Эта формула нечувствительна к регистру, что означает, что если ячейка содержит текст "яблоко"Или"APPLE", он также будет учтен. Чтобы выполнить подсчет с учетом регистра, перейдите к Подсчет ячеек с определенным текстом (точное совпадение) с учетом регистра .
  • Здесь я хотел бы порекомендовать вам удобный инструмент – Выбрать определенные ячейки of Kutools for Excel. Этот инструмент помогает вам легко подсчитывать и выбирать ячейки с определенным текстом в одном или нескольких диапазонах. Просто выполните следующие настройки, чтобы получить общее количество ячеек, содержащих определенный текст, и одновременно выберите соответствующие ячейки. Попробуйте сейчас и получите 30-дневную бесплатную пробную версию.

Подсчитать ячейки с определенным текстом (частичное совпадение)

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

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

=COUNTIF(A2:A12, "*"&D4&"*")

Как показано на скриншоте выше, теперь сгенерировано количество всех ячеек, частично соответствующих тексту «Яблоко».

Заметки:
  • В этой формуле, A2: A12 — это диапазон, в котором вы хотите подсчитать ячейки, содержащие определенный текст. D4 — это ячейка, содержащая определенный текст, на основе которого вы будете подсчитывать ячейки.
  • Символ звездочки (*) — это наиболее общий подстановочный знак, который может представлять любое количество символов.
  • Вы можете напрямую ввести конкретный текст в формулу, если он не введен в ячейку заранее. В таких случаях измените формулу следующим образом: 
    =COUNTIF(A2:A12, "*Apple*")
  • Эта формула подсчитывает ячейки, содержащие "Apple"В любом положении.
    • Чтобы подсчитать количество ячеек, начинающихся с «Apple»,используйте эту формулу: 
      =COUNTIF(A2:A12, "Apple*")
    • Чтобы подсчитать количество ячеек, заканчивающихся на «Apple»,используйте эту формулу: 
      =COUNTIF(A2:A12, "*Apple")
  • Эта формула нечувствительна к регистру, что означает, что если ячейка содержит текст "яблоко"Или"APPLE", он также будет учтен. Чтобы выполнить подсчет с учетом регистра, перейдите к Подсчет ячеек с определенным текстом (частичное совпадение) с учетом регистра .

Подсчет ячеек с определенным текстом, чувствительным к регистру

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

Подсчет ячеек с определенным текстом (точное совпадение и с учетом регистра)

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

=SUMPRODUCT(--EXACT(D4, A2:A12))

Внимание: вы можете напрямую ввести текст в формулу, если он не введен в ячейку заранее.
=SUMPRODUCT(--EXACT("Apple", A2:A12))
Подсчет ячеек с определенным текстом (частичное совпадение и с учетом регистра)

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

=SUMPRODUCT(--(ISNUMBER(FIND(D4, A2:A12))))

Внимание: вы можете напрямую ввести текст в формулу, если он не введен в ячейку заранее.
=SUMPRODUCT(--(ISNUMBER(FIND("Apple", A2:A12))))

Несколько кликов для подсчета и выбора ячеек, содержащих определенный текст

Если вы ищете более простой метод подсчета ячеек с определенным текстом, Выбрать определенные ячейки особенность Kutools for Excel будет хорошим выбором для вас. Эта функция делает больше, чем просто подсчет, она позволяет одновременно выбирать ячейки на основе указанного текста. Инструмент предоставляет различные условия, такие как «Равно», «Начинается с», «Заканчивается на», «Содержит» и т. д., а также поддерживает сопоставление ячеек на основе двух критериев одновременно. Это позволяет пользователям быстро подсчитывать и выбирать ячейки, соответствующие предоставленному им тексту, что является преимуществом, которого нелегко достичь с помощью стандартных формул.

После загрузка и установка Kutools для Excel, наведите на Кутулс > Выберите > Выбрать определенные ячейки. В открытии Выбрать определенные ячейки диалоговое окно, вам необходимо:

  1. Выберите диапазон, в котором вы хотите подсчитать ячейки, содержащие определенный текст.
  2. Выберите Ячейка вариант в Тип выбора .
  3. Укажите условие в Укажите тип выпадающий список.
  4. Введите конкретный текст, который вы хотите посчитать.
  5. Нажмите OK .
    В данном случае, так как мне нужно посчитать ячейки, которые точно соответствуют тексту «Apple», я выбираю Равно из выпадающего списка и введите текст Apple в текстовом поле.

Результат

A Kutools for Excel Затем появится диалоговое окно, показывающее количество найденных ячеек и их выбор.

Заметки:
  • Чтобы подсчитать ячейки, содержащие определенный текст в любом месте ячейки, вам нужно выбрать Комплект из файла Конкретный тип выпадающий список.
  • Чтобы подсчитать ячейки, которые начинаются или заканчиваются определенным текстом, необходимо выбрать значок Начинается с or Заканчивается из файла Конкретный тип выпадающий список.
  • Эта функция может обрабатывать несколько диапазонов одновременно.
  • Эта функция также поддерживает подсчет и выбор ячеек, которые одновременно соответствуют двум критериям. На скриншоте ниже показаны условия подсчета и выделения ячеек, которые не только содержат в любом месте ячейки конкретный текст «Яблоко», но также должны заканчиваться символом «А».
  • Чтобы использовать эту функцию, вам следует сначала установить Kutools for Excel, пожалуйста нажмите, чтобы загрузить и получить 30-дневную бесплатную пробную версию прямо сейчас.
  • Чтобы узнать больше об этой функции, посетите эту страницу: Выбор определенных ячеек, целых строк или столбцов на основе критериев.

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

Лучшие инструменты для офисной работы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup  |   VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар   |  Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки   |  Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов:  12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

Описание

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!