Как посчитать ячейки с текстом в excel
Перейти к содержимому

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

  • автор:

Способы подсчета количества ячеек в диапазоне с данными

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

  1. Щелкните ячейку, в которой должен выводиться результат.
  2. На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
  3. СЧЁТЗ: подсчитывает количество непустых ячеек.
  4. СЧЁТ: подсчитывает количество ячеек, содержащих числа.
  5. СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
  6. СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

  • =СЧЁТЕСЛИ(где нужно искать;что нужно найти)
  • =СЧЁТЕСЛИ(A2:A5;»Лондон»)
  • =СЧЁТЕСЛИ(A2:A5;A4)

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

диапазон (обязательный)

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

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

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

Примеры

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

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист.

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

Рекомендации

Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.

Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

В критериях можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.

Убедитесь, что данные не содержат ошибочных символов.

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

Для удобства используйте именованные диапазоны.

ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit;»>=32″)-COUNTIF(fruit;»>85″). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Подсчет ячеек с текстом

Уважаемая редакция! Возникла проблема: на рабочем листе Excel в заданном диапазоне нужно посчитать количество ячеек, которые содержат текстовую информацию. Как решить эту задачу?

В. Петров, г. Харьков

Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

Описанная вами проблема периодически возникает при работе с данными в Excel, например при расчете средневзвешенных показателей в учете. Я столкнулся с такой ситуацией, когда импортировал в Excel документ, в котором вместо пустых значений были проставлены

прочерки (символы «-»), а даты в базу попали в виде текстовых строк. Существует несколько способов решения такой задачи. Посмотрим их на конкретном примере: я хочу определить количество ячеек с текстом в диапазоне рабочего листа «(A1:A10)».

Для того чтобы посчитать количество ячеек с

числами, в Excel есть функция «Счёт()». Для подсчета количества непустых ячеек в Excel используют функцию «СчётЗ()». Скомбинировав две эти функции, можно предложить первый способ решения задачи — воспользоваться формулой «=СЧЁТЗ(A1:A10)-СЧЁТ(A1:A10)».

Второй способ — воспользоваться

формулой массива. Для этого сделайте так:

1) в ячейку рабочего листа введите выражение «

2) нажмите комбинацию «

В этой формуле функция «

Етекст» анализирует содержимое ячейки. Если ячейка содержит текст, результат функции равен «Истина». Проверку этого значения выполняет функция «Если». В случае, когда «Етекст» равен «Истина», функция «Если» вернет значение «1». А функция «Сумм» просуммирует результат и получит количество ячеек с текстом (т. е. количество «единиц») в указанном диапазоне.

Обратите внимание, что изначально функции «

Етекст()»,«Если()» могут работать только с одной ячейкой. Чтобы распространить их действие на группу ячеек, мы и записали формулу в виде массива, нажав «Ctrl+Shift+Enter». В этом случае Excel автоматически откорректирует наше выражение, и в строке формул оно будет выглядеть так: «».

Просто написать формулу «<=Сумм(Если (Етекст(A1:A10);1))>» и ввести ее в ячейку обычным способом (нажав «Enter») нельзя.

Жду ваших писем, замечаний и предложений на

Excel: как подсчитать, если ячейки содержат текст

Excel: как подсчитать, если ячейки содержат текст

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

Метод 1: подсчет ячеек, содержащих один конкретный текст

=COUNTIF( A2:A13 , "\*text\*") 

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

Метод 2: подсчет ячеек, содержащих один из нескольких текстов

=SUM(COUNTIF( A2:A13 ,)) 

Эта формула будет подсчитывать количество ячеек в диапазоне A2:A13 , которые содержат «текст1», «текст2» или «текст3» в ячейке.

В следующих примерах показано, как использовать каждый метод на практике со следующим набором данных в Excel:

Пример 1. Подсчет ячеек, содержащих один конкретный текст

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

=COUNTIF( A2:A13 , "\*avs\*") 

На следующем снимке экрана показано, как использовать эту формулу на практике:

Мы видим, что всего 4 ячейки в столбце « Команда » содержат «avs» в названии.

Пример 2. Подсчет ячеек, содержащих один из нескольких текстов

Мы можем использовать следующую формулу для подсчета количества ячеек в столбце Team , которые содержат «avs», «urs» или «pockets» в имени:

=SUM(COUNTIF( A2:A13 ,)) 

На следующем снимке экрана показано, как использовать эту формулу на практике:

Мы видим, что в общей сложности 7 ячеек в столбце « Команда » содержат «avs», «urs» или «pockets» в названии.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

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

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