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

Обновлено: 21.11.2024

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

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

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

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

Формула СЧЕТЕСЛИ для подсчета всех ячеек с текстом

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

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

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

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

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

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

  • Ячейки с любым текстом
  • Специальные символы
  • Числа в текстовом формате
  • Визуально пустые ячейки, содержащие пустую строку (""), апостроф ('), пробел или непечатаемые символы.
  • Числа
  • Даты
  • Логические значения TRUE и FALSE
  • Ошибки
  • Пустые ячейки

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

На снимке экрана ниже показан результат:

Подсчет ячеек с текстом без пробелов и пустых строк

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

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

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

Если ваш целевой диапазон содержит какие-либо данные на основе формул, некоторые формулы могут привести к пустой строке (""). Чтобы также игнорировать ячейки с пустыми строками, замените "*" на "*?*" в аргументе criteria1:

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

На снимке экрана ниже пробел в формате A7, апостроф в формате A8 и пустая строка (="") в формате A9. Наша формула исключает все эти ячейки и возвращает количество текстовых ячеек, равное 3:

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

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

Чтобы точно соответствовать образцу текста, введите полный текст, заключенный в кавычки:

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

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

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

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

В зависимости от ввода в D2 формула может полностью или частично соответствовать образцу текста:

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

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

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

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

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

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

А теперь вы сужаете список по каким-то критериям, скажем, отфильтровываете товары, количество которых больше 10. Вопрос в том, сколько товаров осталось?

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

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

Чтобы идентифицировать все скрытые ячейки, отфильтрованные и спрятанные вручную, введите 103 в function_num:

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

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

  • Критерий 1 – поиск ячеек с любым текстом ("*") в диапазоне A2:A10.
  • Критерий 2 – поиск 1 в диапазоне D2:D10 для обнаружения видимых ячеек.

Теперь вы можете фильтровать данные нужным вам образом, и формула сообщит вам, сколько отфильтрованных ячеек в столбце A содержат текст (в нашем случае 3):

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

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

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

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

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(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. На данный момент формула выглядит следующим образом:

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

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

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

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

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

где данные — это именованный диапазон B5:B15.

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

В этом примере нужно подсчитать количество ячеек в диапазоне, содержащих любой текст. Это может быть жестко заданный текст, например «яблоко» или «красный», числа, введенные как текст, или формулы, возвращающие текстовые значения. В решении используется функция СЧЁТЕСЛИ, которая подсчитывает количество ячеек, соответствующих предоставленным критериям. В этом случае критерий предоставляется в виде звездочки (*), которая представляет собой подстановочный знак, соответствующий любому количеству текстовых символов. Формула в H5:

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

В этой формуле используется логический оператор "не равно" (<>).

  • Логические значения ИСТИНА и ЛОЖЬ не считаются текстом.
  • Числа не учитываются с помощью "*", если только они не введены в виде текста.
  • Пустые ячейки не считаются текстом.

Опция СЧЁТЕСЛИМН

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

Опция СУММПРОИЗВ

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

Двойное отрицательное значение (--) преобразует результаты ИСТИНА и ЛОЖЬ из ISTEXT в 1 и 0. Затем СУММПРОИЗВ суммирует результат.

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

Как посчитать текст в Excel

Если вы хотите научиться считать текст в Excel, вам нужно использовать функцию СЧЕТЕСЛИ с критериями, определенными с помощью подстановочного знака *, с формулой: =СЧЕТЕСЛИ(диапазон;"*") . Диапазон — это определенный диапазон ячеек, в котором вы хотите подсчитать текст в Excel, а подстановочный знак * — это критерии для всех вхождений текста в указанном диапазоне.

В этом руководстве будут рассмотрены некоторые интересные и очень полезные примеры, основное внимание будет уделено функции СЧЁТЕСЛИ и различному использованию этой функции в подсчёте текста. Ограничения функции СЧЁТЕСЛИ были рассмотрены в этом руководстве с дополнительным объяснением других функций, таких как комбинация функций СУММПРОИЗВ/ЧИСЛО/НАЙТИ. После этого руководства вы сможете подсчитывать текстовые ячейки в Excel, подсчитывать определенные текстовые ячейки, текстовые ячейки с учетом регистра и текстовые ячейки с несколькими определенными критериями, что является очень хорошей базой для дальнейшего творческого решения задач Excel.

Подсчет текстовых ячеек в Excel

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

Для подсчета нетекстовых ячеек формула должна быть немного изменена в части критериев:

Если есть несколько критериев подсчета ячеек, то следует использовать функцию СЧЁТЕСЛИМН.Например, если мы хотим посчитать количество сотрудников из Техаса с номером проекта больше 20, то функция будет выглядеть так:

В диапазоне критериев в столбце «Штат» конкретный текстовый критерий определяется в кавычках «Техас». Второй критерий — числовой, диапазон критериев — столбец «Количество проектов», а критерий — числовое значение больше 20, также в кавычках «>20». Если бы мы искали точное значение, формула выглядела бы так:

Подсчет определенного текста в ячейках

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

Первая часть формулы — это диапазон, а вторая — текстовые критерии, в нашем примере «*Майк*». Если подстановочный знак * не использовался до и после текста критерия, результатом формулы будет 1 (формула найдет ячейки только со словом Майк). Подстановочный знак * до и после текста критерия означает, что будут учитываться все ячейки, содержащие символы критерия. Как и в другом примере ниже с текстовым критерием Sun, были найдены три ячейки ( sun , Sun ny, sun is shining)

Примечание. Функция СЧЁТЕСЛИ не чувствительна к регистру. Альтернативной функцией для текстового поиска с учетом регистра является комбинация функций СУММПРОИЗВ/НАЙТИ.

Подсчет текста с учетом регистра

Для подсчета текста с учетом регистра следует использовать комбинацию трех формул: СУММПРОИЗВ, IЧИСЛО и НАЙТИ . Давайте посмотрим на пример ниже. Если мы хотим подсчитать ячейки, содержащие текст Sun, с учетом регистра, функция COUNTIF не будет подходящим решением, вместо этой функции необходимо использовать комбинацию трех функций, упомянутых выше.

Чтобы понять комбинацию функций, нам нужно пройти отдельное объяснение функций. Функция НАЙТИ ищет конкретный текст в определенной ячейке и возвращает номер начальной позиции текста, используемого в качестве критерия. Это объяснение актуально, если диапазон поиска составляет всего одну ячейку. Если мы хотим использовать функцию НАЙТИ в диапазоне ячеек, то необходима комбинация с функцией СУММПРОИЗВ.

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

Чтобы изменить значения ошибок с помощью логического оператора TRUE/FALSE, следует использовать формулу ISNUMERIC (определение числовых значений как TRUE, а нечисловых значений как FALSE, как показано на экране печати ниже).

Возможно, вам интересно, что означает символ — в функции СУММПРОИЗВ. Он преобразует логические значения ИСТИНА/ЛОЖЬ в числовые значения 1/0, позволяя функции СУММПРОИЗВ обрабатывать числовые операции (без символа — в функции СУММПРОИЗВ конечным результатом будет 0).

Помните, если вы хотите подсчитать определенные текстовые ячейки, которые не чувствительны к регистру, подходит функция СЧЁТЕСЛИ. Для всех поисковых запросов с учетом регистра подходит комбинация функций СУММПРОИЗВ/ЧИСЛО/НАЙТИ.

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

Если вы хотите подсчитать ячейки с несколькими критериями, при этом все критерии допустимы, есть интересный способ решения этой проблемы — комбинация функций СУММПРОИЗВ/ЧИСЛО/НАЙТИ. Пожалуйста, взгляните на пример ниже. Мы должны подсчитать все ячейки, содержащие Майк или $. Сложная часть может заключаться в ячейках, содержащих и Майка, и $.

Формула просто выглядит сложной, для облегчения понимания я разделю ее на несколько шагов. Кроме того, для дальнейшей работы потребуются знания из предыдущего раздела руководства, поскольку было объяснено сочетание функций НАЙТИ, ЕСЛИЧИСЛО и СУММПРОИЗВ.

В первой части функции мы перебираем таблицу и находим ячейки, содержащие Майка:

Выходными данными этой части функции будет массив со значениями , где 1 означает, что критерии были соблюдены, а 0 – если нет.

Во второй части функции критерием цикла является $, подсчитывая ячейки, содержащие это значение:

Выходными данными этой части функции будет массив со значениями , где 1 означает, что критерии были соблюдены, а 0 – если нет.

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

Вывод этого шага: число больше 0 означает, что выполнено одно из условий (2 – оба условия, 1 – одно условие)

Без функциональной части >0 окончательная функция будет удваивать количество ячеек, удовлетворяющих обоим условиям, и окончательный результат будет равен 7 (сумма всех чисел массива). Во избежание этого в формулу нужно добавить >0:

Выводом этого шага является массив , предыдущий массив был проверен, и только значения больше 0 являются ИСТИННЫМИ (в массиве имеют значение 1), а остальные — ЛОЖНЫ (в массиве имеют значение 0).

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

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

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

Посмотрите на приведенный ниже пример. Количество ячеек, содержащих и Майка, и $, легко вычислить с помощью функции СЧЁТЕСЛИМН:

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

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

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

Подсчитайте, сколько ячеек используется в столбце с текстом внутри них вместо чисел (например, значение или количество, но без задействования чисел)

Мне нужно подсчитать ячейку, содержащую определенный текст, но пропустить текст, содержащий тот же текст, но с дополнительными элементами. Например, я хочу подсчитать ячейку, содержащую «HM2», но не хочу подсчитывать ячейку, содержащую «HM21». Пожалуйста, помогите, я так долго застрял.

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

<р>1а. Используйте функцию СЧЕТЕСЛИ и символ звездочки (*) для подсчета ячеек с текстом.

1б. Вы также можете создать формулу массива для подсчета ячеек с текстом.

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

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

Примечание: посетите нашу страницу о функции СУММПРОИЗВ, чтобы узнать больше об этой функции.

2а. Числа, логические значения (ИСТИНА или ЛОЖЬ), пробелы и ошибки не учитываются как текст.

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

3а. Введите пробел в ячейку A4. Функция СЧЁТЕСЛИ на шагах 1а и 2а также подсчитывает ячейки с пробелом.

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

Примечание. Функция СЧЁТЕСЛИМН в Excel подсчитывает количество ячеек на основе двух или более критериев.

Читайте также: