общее количество или конкретные символы в ячейке или диапазоне
В учебнике объясняется, как считать символы в Excel. Вы изучите формулы для получения общего количества символов в диапазоне и подсчета только определенных символов в ячейке или в нескольких ячейках.
В нашем предыдущем уроке была представлена функция Excel LEN, которая позволяет подсчитывать общее количество символов в ячейке.
Формула LEN полезна сама по себе, но в сочетании с другими функциями, такими как СУММ, СУММПРОИЗВ и ПОДСТАВИТЬ, она может решать гораздо более сложные задачи. Далее в этом уроке мы более подробно рассмотрим несколько основных и расширенных формул для подсчета символов в Excel.
Как посчитать все символы в диапазоне
Когда дело доходит до подсчета общего количества символов в нескольких ячейках, первое решение, которое приходит на ум, — получить количество символов для каждой ячейки, а затем сложить эти числа:
=ДЛСТР(A2)+ДЛСТР(A3)+ДЛСТР(A4)
Или же
=СУММ(ДЛСТР(A2),ДЛСТР(A3),ДЛСТР(A4))
Приведенные выше формулы могут хорошо работать для небольшого диапазона. Чтобы подсчитать общее количество символов в большем диапазоне, нам лучше придумать что-то более компактное, например, функцию СУММПРОИЗВ, которая умножает массивы и возвращает сумму произведений.
Вот общая формула Excel для подсчета символов в диапазоне:
=СУММПРОИЗВ(ДЛСТР(диапазон))
И ваша реальная формула может выглядеть примерно так:
=СУММПРОИЗВ(ДЛСТР(A1:A7))
Другой способ подсчета символов в диапазоне — использование функции ДЛСТР в сочетании с СУММ:
=СУММ(ДЛСТР(A1:A7))
В отличие от СУММПРОИЗВ, функция СУММ по умолчанию не вычисляет массивы, и вам нужно нажать Ctrl + Shift + Enter, чтобы превратить ее в формулу массива.
Как показано на следующем снимке экрана, формула СУММ возвращает то же общее количество символов:
Как работает эта формула подсчета символов диапазона
Это одна из самых простых формул для подсчета символов в Excel. Функция ДЛСТР вычисляет длину строки для каждой ячейки в указанном диапазоне и возвращает их в виде массива чисел. А затем СУММПРОИЗВ или СУММ складывают эти числа и возвращают общее количество символов.
В приведенном выше примере суммируется массив из 7 чисел, представляющих длины строк в ячейках от A1 до A7:
Примечание. Обратите внимание, что функция ДЛСТР в Excel считает абсолютно все символы в каждой ячейке, включая буквы, цифры, знаки препинания, специальные символы и все пробелы (начальные, конечные и пробелы между словами).
Как посчитать определенные символы в ячейке
Иногда вместо подсчета всех символов в ячейке может потребоваться подсчет только вхождений определенной буквы, цифры или специального символа.
Чтобы подсчитать, сколько раз данный символ появляется в ячейке, используйте функцию ДЛСТР вместе с ПОДСТАВИТЬ:
=ДЛСТР(клетка)-LEN(ПОДСТАВИТЬ(клетка, персонаж,””))
Чтобы лучше понять формулу, рассмотрим следующий пример.
Предположим, вы ведете базу данных доставленных товаров, где каждый тип товара имеет свой уникальный идентификатор. И каждая ячейка содержит несколько элементов, разделенных запятой, пробелом или любым другим разделителем. Задача состоит в том, чтобы подсчитать, сколько раз заданный уникальный идентификатор встречается в каждой ячейке.
Предполагая, что список доставленных товаров находится в столбце B (начиная с B2), и мы подсчитываем количество вхождений «A», формула выглядит следующим образом:
=ДЛСТР(В2)-ДЛСТР(ПОДСТАВИТЬ(В2,”А”,””))
Как работает эта формула подсчета символов Excel
Чтобы понять логику формулы, давайте разобьем ее на более мелкие части:
- Во-первых, вы подсчитываете общую длину строки в B2:
ДЛИН(B2) - Затем вы используете функцию ПОДСТАВИТЬ, чтобы удалить все вхождения буквы “А” в B2, заменив его пустой строкой (“”):
ЗАМЕНИТЬ(B2,”А”,””) - И затем вы считаете длину строки без “А” персонаж:
ДЛСТР(ПОДСТАВИТЬ(B2;”A”,””)) - Наконец, вы вычитаете длину строки без “А” из общей длины строки.
В результате вы получите количество «удаленных» символов, равное общему количеству вхождений этого символа в ячейку.
Вместо указания символа, который вы хотите подсчитать в формуле, вы можете ввести его в какую-нибудь ячейку, а затем сослаться на эту ячейку в формуле. Таким образом, ваши пользователи смогут подсчитывать вхождения любого другого символа, который они вводят в эту ячейку, не вмешиваясь в вашу формулу:
Примечание. ПОДСТАВИТЬ в Excel — это функция, чувствительная к регистру, поэтому приведенная выше формула также чувствительна к регистру. Например, на приведенном выше снимке экрана ячейка B3 содержит 3 вхождения буквы «А» — два в верхнем регистре и один в нижнем регистре. В формуле учитывались только символы верхнего регистра, потому что мы поставили «A» в функцию ПОДСТАВИТЬ.
Формула Excel без учета регистра для подсчета определенных символов в ячейке
Если вам нужно подсчет символов без учета регистра, вставьте функцию ПРОПИСЬ внутри ПОДСТАВИТЬ, чтобы преобразовать указанный символ в верхний регистр перед запуском подстановки. И обязательно вводите в формулу заглавные буквы.
Например, чтобы подсчитать элементы «A» и «a» в ячейке B2, используйте следующую формулу:
=ДЛСТР(B2)-ДЛСТР(ЗАМЕНИТЬ(ВЕРХНИЙ(В2),”А”,””))
Другой способ — использовать вложенные функции замены:
=LEN(B2)-LEN(ЗАМЕНИТЬ(ЗАМЕНИТЬ(B2,”A”,””)”,”a”,””)
Как вы можете видеть на снимке экрана ниже, обе формулы безупречно подсчитывают вхождения указанного символа в верхнем и нижнем регистре:
В некоторых случаях вам может понадобиться подсчитать много разных символов в таблице, но вы можете не захотеть изменять формулу каждый раз. В этом случае вложите одну функцию замены в другую, введите символ, который вы хотите подсчитать, в какой-либо ячейке (D1 в этом примере) и преобразуйте значение этой ячейки в верхний и нижний регистр с помощью функций ПРОПИСНАЯ и НИЖНЯЯ:
=ДЛСТР(B2)-ДЛСТР(ЗАМЕНИТЬ(ЗАМЕНИТЬ(B2, ВЕРХНИЙ($D$1), “”), НИЖНИЙ($D$1),””))
В качестве альтернативы можно преобразовать как исходную ячейку, так и ячейку, содержащую символ, в верхний или нижний регистр. Например:
=ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСНАЯ(B2), ПРОПИСНАЯ($C$1),””))
Преимущество этого подхода заключается в том, что независимо от того, введен ли символ в верхнем или нижнем регистре в указанной ячейке, ваша формула подсчета символов без учета регистра вернет правильное количество:
Подсчет вхождений определенного текста или подстроки в ячейке
Если вы хотите подсчитать, сколько раз определенная комбинация символов (т. е. определенный текст или подстрока) появляется в данной ячейке, например, «A2» или «SS», разделите количество символов, возвращаемых приведенными выше формулами, на длину подстроки.
Формула с учетом регистра:
=(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(В2, $C$1,””)))/ДЛСТР($C$1)
Формула без учета регистра:
=(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(НИЖНИЙ(B2),НИЖНИЙ($C$1),””)))/ДЛСТР($C$1)
Где B2 — это ячейка, содержащая всю текстовую строку, а C1 — это текст (подстрока), который вы хотите подсчитать.
Подробное объяснение формулы см. в разделе Как подсчитать конкретный текст/слова в ячейке.
Как подсчитать определенные символы в диапазоне
Теперь, когда вы знаете формулу Excel для подсчета символов в ячейке, вы можете улучшить ее, чтобы узнать, сколько раз определенный символ появляется в диапазоне. Для этого мы возьмем формулу Excel LEN для подсчитать определенный символ в ячейке обсуждалось в предыдущем примере, и поместите его в функцию СУММПРОИЗВ, которая может обрабатывать массивы:
СУММПРОИЗВ(ДЛСТР(диапазон)-LEN(ПОДСТАВИТЬ(диапазон, персонаж,””)))
В этом примере формула принимает следующий вид:
=СУММПРОИЗВ(ДЛСН(B2:B8)-ДЛСТР(ПОДСТАВИТЬ(B2:B8, “A”,””)))
А вот еще одна формула для подсчета символов в диапазоне Excel:
=СУММ(ДЛСТР(B2:B8)-ДЛСТР(ПОДСТАВИТЬ(B2:B8, “А”,””)))
По сравнению с первой формулой наиболее очевидным отличием является использование СУММ вместо СУММПРОИЗВ. Еще одно отличие состоит в том, что для этого требуется нажать Ctrl + Shift + Enter, потому что в отличие от СУММПРОИЗВ, который предназначен для обработки массивов, СУММ может обрабатывать массивы только при использовании в формуле массива.
Если вы не хотите жестко кодировать символ в формуле, вы, конечно, можете ввести его в какую-нибудь ячейку, скажем, D1, и сослаться на эту ячейку в формуле подсчета символов:
=СУММПРОИЗВ(ДЛСТР(B2:B8)-ДЛСТР(ПОДСТАВИТЬ(B2:B8, D1,””)))
Примечание. В ситуациях, когда вы подсчитываете количество вхождений определенной подстроки в диапазоне (например, заказы, начинающиеся с «KK» или «AA»), вам необходимо разделить количество символов на длину подстроки, иначе каждый символ в подстроке будет учитываться отдельно. . Например:
=СУММ((ДЛСТР(B2:B8)-ДЛСТР(ПОДСТАВИТЬ(B2:B8, D1, “”))) / ДЛСТР(D1))
Как работает эта формула подсчета символов
Как вы, возможно, помните, функция ПОДСТАВИТЬ используется для замены всех вхождений указанного символа (в данном примере “А”) пустой текстовой строкой (“”).
Затем мы передаем текстовую строку, возвращенную функцией ПОДСТАВИТЬ, функции ДЛСТР Excel, чтобы она вычисляла длину строки без букв А. Затем мы вычитаем это количество символов из общей длины текстовой строки. Результатом этих вычислений является массив счетчиков символов, по одному символу на ячейку.
Наконец, СУММПРОИЗВ суммирует числа в массиве и возвращает общее количество указанных символов в диапазоне.
Формула без учета регистра для подсчета определенных символов в диапазоне
Вы уже знаете, что ПОДСТАВИТЬ — это функция, чувствительная к регистру, что делает нашу формулу Excel для подсчета символов также чувствительной к регистру.
Чтобы формула не учитывала регистр, следуйте подходам, продемонстрированным в предыдущем примере: Формула без учета регистра для подсчета определенных символов в ячейке.
В частности, вы можете использовать одну из следующих формул для подсчета определенных символов в диапазоне без учета регистра:
- Используйте функцию ПРОПИСНЫЕ и введите символ в верхнем регистре:
=СУММПРОИЗВ(ДЛСТР(B2:B8) – ДЛСТР(ПОДСТАВИТЬ(ВЕРХНИЙ(B2:B8),”A”,””)))
- Используйте вложенные функции ПОДСТАВКИ:
=СУММПРОИЗВ(ДЛСН(B2:B8) – ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ((B2:B8),”А”,””),”а”,””)))
- Используйте функции UPPER и LOWER, введите символы верхнего или нижнего регистра в какую-либо ячейку и укажите ссылку на эту ячейку в своей формуле:
=СУММПРОИЗВ(ДЛСТР(B2:B8) – ДЛСТР(ЗАМЕНИТЬ(ЗАМЕНИТЬ((B2:B8), ВЕРХНИЙ($E$1), “”), НИЖНИЙ($E$1),””)))
На приведенном ниже снимке экрана показана последняя формула в действии:
Кончик. Чтобы подсчитать количество вхождений определенного текста (подстроки) в диапазоне, используйте формулу, показанную в разделе Как подсчитать определенный текст/слова в диапазоне.
Вот как вы можете подсчитывать символы в Excel, используя функцию ДЛСТР. Если вы хотите научиться считать слова, а не отдельные символы, вы найдете несколько полезных формул в нашей следующей статье, следите за обновлениями!
Тем временем вы можете скачать образцы рабочих тетрадей с формулой подсчета символов, обсуждаемой в этом руководстве, и ознакомьтесь со списком связанных ресурсов в конце страницы. Я благодарю вас за чтение и надеюсь увидеть вас в ближайшее время!
