количество ячеек, содержащих определенный текст (точное и частичное совпадение)
В учебнике показано, как подсчитать количество ячеек с определенным текстом в Excel. Вы найдете примеры формул для точного совпадения, частичного совпадения и отфильтрованных ячеек.
На прошлой неделе мы рассмотрели, как считать ячейки с текстом в Excel, то есть все ячейки с любым текстом. При анализе больших объемов информации вам также может понадобиться узнать, сколько ячеек содержит конкретный текст. Этот учебник объясняет, как это сделать простым способом.
Как посчитать ячейки с определенным текстом в Excel
В Microsoft Excel есть специальная функция для условного подсчета ячеек — функция СЧЁТЕСЛИ. Все, что вам нужно сделать, это указать целевую текстовую строку в критерии аргумент.
Вот общая формула Excel для подсчета количества ячеек, содержащих определенный текст:
СЧЁТЕСЛИ(диапазон, “текст“)
Следующий пример показывает это в действии. Предположим, у вас есть список идентификаторов элементов в A2:A10, и вы хотите подсчитать количество ячеек с определенным идентификатором, скажем, «AA-01». Введите эту строку во второй аргумент, и вы получите простую формулу:
=СЧЁТЕСЛИ(A2:A10, “AA-01”)
Чтобы ваши пользователи могли подсчитывать ячейки с любым заданным текстом без необходимости изменять формулу, введите текст в предопределенную ячейку, скажем, D1, и укажите ссылку на ячейку:
=СЧЁТЕСЛИ(A2:A10, D1)
Примечание. Функция СЧЁТЕСЛИ в Excel нечувствительна к регистру, то есть она не различает регистр букв. Чтобы обрабатывать символы верхнего и нижнего регистра по-разному, используйте эта формула с учетом регистра.
Как подсчитать ячейки с определенным текстом (частичное совпадение)
Формула, рассмотренная в предыдущем примере, точно соответствует критериям. Если в ячейке есть хотя бы один другой символ, например лишний пробел в конце, это не будет точным совпадением, и такая ячейка не будет засчитана.
Чтобы найти количество ячеек, содержащих определенный текст как часть их содержимого, используйте подстановочные знаки в ваших критериях, а именно звездочку.
который представляет любую последовательность или символы. В зависимости от вашей цели формула может выглядеть следующим образом.
Подсчитайте ячейки, которые содержат определенный текст в самом начале:СЧЁТЕСЛИ(диапазон, “текст
*”)
Подсчет ячеек, содержащих определенный текст в любой позиции:СЧЁТЕСЛИ(диапазон, “*текст
*”)
Например, чтобы узнать, сколько ячеек в диапазоне A2:A10 начинаются с «AA», используйте следующую формулу:
=СЧЁТЕСЛИ(A2:A10, “АА*”)
Чтобы получить количество ячеек, содержащих «AA» в любой позиции, используйте это:
=СЧЁТЕСЛИ(A2:A10, “*АА*”)
Чтобы сделать формулы более динамичными, замените жестко заданные строки ссылками на ячейки.
Чтобы подсчитать ячейки, начинающиеся с определенного текста:
=СЧЁТЕСЛИ(A2:A10, D1&”*”)
Чтобы подсчитать ячейки с определенным текстом в любом месте:
=СЧЁТЕСЛИ(A2:A10, “*”&D1&”*”)
Формула для подсчета ячеек, содержащих заданную текстовую строку
Подсчет ячеек, содержащих определенный текст (с учетом регистра)
В ситуации, когда вам нужно различать прописные и строчные буквы, функция СЧЁТЕСЛИ не будет работать. В зависимости от того, ищете ли вы точное или частичное совпадение, вам придется построить другую формулу.
Формула с учетом регистра для подсчета ячеек с определенным текстом (точное совпадение)
Чтобы подсчитать количество ячеек с определенным текстом, распознающим регистр текста, мы будем использовать комбинацию функций СУММПРОИЗВ и ТОЧНОЕ:СУММПРОИЗВ(–ТОЧНО(“текст “,диапазон
))
- Как работает эта формула: EXACT сравнивает каждую ячейку в диапазоне с образцом текста и возвращает массив значений TRUE и FALSE, где TRUE представляет точные совпадения, а FALSE все остальные ячейки. Двойной дефис (называемыйдвойной унарный
- ) преобразует TRUE и FALSE в 1 и 0.
СУММПРОИЗВ суммирует все элементы массива. Эта сумма представляет собой количество единиц, то есть количество совпадений.
Например, чтобы получить количество ячеек в A2:A10, которые содержат текст в D1 и обрабатывают прописные и строчные буквы как разные символы, используйте следующую формулу:
Формула с учетом регистра для получения количества ячеек с определенным текстом
Формула с учетом регистра для подсчета ячеек с определенным текстом (частичное совпадение)
Чтобы построить формулу с учетом регистра, которая может найти интересующую текстовую строку в любом месте ячейки, мы используем 3 разные функции:СУММПРОИЗВ(–(ЧИСЛО(НАЙТИ(“текст “,диапазон
))))
- Как работает эта формула:
- Функция НАЙТИ с учетом регистра ищет целевой текст в каждой ячейке диапазона. В случае успеха функция возвращает позицию первого символа, иначе #ЗНАЧ! ошибка. Для ясности нам не нужно знать точную позицию, любое число (в отличие от ошибки) означает, что ячейка содержит целевой текст.
- Функция ISNUMBER обрабатывает массив чисел и ошибок, возвращенных функцией НАЙТИ, и преобразует числа в ИСТИНА, а все остальное в ЛОЖЬ. Двойной унарный (–) приводит логические значения к единицам и нулям.
СУММПРОИЗВ суммирует массив единиц и нулей и возвращает количество ячеек, содержащих указанный текст как часть их содержимого.
Чтобы проверить формулу на реальных данных, давайте найдем, сколько ячеек в A2:A10 содержат подстроку, введенную в D1:
=СУММПРОИЗВ(–(ЧИСЛО(НАЙТИ(D1, A2:A10))))
Формула с учетом регистра для подсчета ячеек, содержащих определенный текст в любом месте.
Как подсчитать отфильтрованные ячейки с определенным текстом
Чтобы подсчитать видимые элементы в отфильтрованном списке, вам нужно будет использовать комбинацию из 4 или более функций в зависимости от того, хотите ли вы точное или частичное совпадение. Чтобы упростить понимание примеров, давайте сначала быстро взглянем на исходные данные. Предположим, у вас есть таблица с Идентификаторы заказов в столбце Б и Количество
Как подсчитать отфильтрованные ячейки с определенным текстом
Формула для подсчета отфильтрованных ячеек с определенным текстом (точное совпадение)
Для подсчета отфильтрованных ячеек, содержимое которых точно соответствует образцу текстовой строки, используйте одну из следующих формул:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, ДВССЫЛ(“A”&СТРОКА(A2:A10))), –(B2:B10=F1))
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, СМЕЩ(A2:A10, СТРОКА(A2:A10) – МИН(СТРОКА(A2:A10)),,1)), –(B2:B10=F1))
Формула для подсчета отфильтрованных ячеек с определенным текстом
Как работают эти формулы:
- В основе обеих формул вы выполняете 2 проверки: Определите видимые и скрытые строки. Для этого вы используете функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номер_функции
- аргументу присвоено значение 103. Чтобы предоставить все ссылки на отдельные ячейки в ПРОМЕЖУТОЧНЫЙ ИТОГ, используйте либо ДВССЫЛ (в первой формуле), либо комбинацию СМЕЩ, СТРОКА и МИН (во второй формуле). Поскольку мы стремимся найти видимые и скрытые строки, на самом деле не имеет значения, на какой столбец ссылаться (A в нашем примере). Результатом этой операции является массив единиц и нулей, где единицы представляют видимые строки, а нули — скрытые строки.
Найдите ячейки, содержащие заданный текст. Для этого сравните образец текста (F1) с диапазоном ячеек (B2:B10). Результатом этой операции является массив значений ИСТИНА и ЛОЖЬ, которые преобразуются в 1 и 0 с помощью двойного унарного оператора.
Наконец, функция СУММПРОИЗВ умножает элементы двух массивов в одинаковых позициях, а затем суммирует полученный массив. Поскольку умножение на ноль дает ноль, только ячейки, имеющие 1 в обоих массивах, имеют 1 в конечном массиве. Сумма единиц — это количество отфильтрованных ячеек, содержащих указанный текст.
Формула для подсчета отфильтрованных ячеек с определенным текстом (частичное совпадение)
Чтобы подсчитать отфильтрованные ячейки, содержащие определенный текст, как часть содержимого ячейки, измените приведенные выше формулы следующим образом. Вместо того, чтобы сравнивать образец текста с диапазоном ячеек, найдите целевой текст, используя ISNUMBER и FIND, как описано в одном из предыдущих примеров:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, ДВССЫЛ(“A”&СТРОКА(A2:A10))), –(ЧИСЛО(НАЙТИ(F1, B2:B10))))
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЙ ИТОГ(103, СМЕЩ(A2:A10, СТРОКА(A2:A10) – МИН(СТРОКА(A2:A10)),,1)), –(ЧИСЛО(НАЙТИ(F1, B2:B10))) )
Формула для подсчета отфильтрованных ячеек с определенной текстовой строкой в любой позиции Примечание. Функция ПРОМЕЖУТОЧНЫЙ ИТОГ со 103 в номер_функции аргумент, идентифицирует все скрытые ячейки, отфильтрованные и скрытые вручную. В результате приведенные выше формулы учитывают только видимые ячейки, независимо от того, как были скрыты невидимые ячейки. Чтобы исключить только отфильтрованные ячейки, но включить скрытые вручную, используйте 3 дляномер_функции
.
Вот как подсчитать количество ячеек с определенным текстом в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Формулы Excel для подсчета ячеек с определенным текстом
Вас также могут заинтересовать
