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

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

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

=ЛИН(A2)+ЛИН(A3)+ЛИН(A4)

или

=СУММ(ЛИН(A2),ЛИН(A3),ЛИН(A4))

Вышеупомянутое может хорошо работать для небольшого выбора. Но вы вряд ли добавите 20 номеров таким образом. Чтобы определить количество символов Excel в большей области, мы должны придумать что-то более компактное. Например, функция СУММПРОИЗВ, которая умножает матрицы и возвращает сумму произведений.

Вот общая формула Excel для подсчета символов в диапазоне:

=СУММПРОИЗВ(DLSTR(диапазон))

И ваша фактическая формула может выглядеть примерно так:

= СУММПРОИЗВ(ДСТР(A1:A4))

Другой способ подсчета символов в диапазоне — использование функции ДЛСТР в сочетании с СУММ:

{=СУММ(ДЛСТР(A1:A4))}

В отличие от СУММПРОИЗВ, функция СУММ по умолчанию не вычисляет массивы. Поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы превратить его в формулу массива.

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

Как работает эта формула подсчета символов диапазона?

Это один из самых простых вариантов подсчета символов в Excel. Прежде всего, функция DLSTR вычисляет длину текста для каждого адреса в указанном диапазоне и возвращает их в виде массива чисел. Затем СУММПРОИЗВ или СУММ складывают эти числа вместе и возвращают общую сумму.

В приведенном выше примере суммируется массив из 4 чисел, представляющих длины строк в ячейках с A1 по A4:

=СУММПРОИЗВ({23;13;23;17})

Примечание. Обратите внимание, что функция Excel ДЛСТР считает абсолютно все символы в каждой ячейке, включая буквы, цифры, знаки препинания, специальные символы и все пробелы (начальные, конечные и пробелы между словами).

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

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

Чтобы подсчитать, сколько раз данный символ появляется в выбранной ячейке, используйте функцию ДЛИННОЕ с ЗАМЕНИТЬ:

=DLSTR(ячейка) — DLSTR(ЗАМЕНИТЬ(ячейка; символ ; «»))

Чтобы лучше понять этот расчет, рассмотрим следующий пример.

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

Предположим, что список доставленных товаров находится в столбце B (начиная с B2), и мы подсчитываем количество вхождений «A». Выражение выглядит так:

=ДЛИННЫЙ(B2)-ДЛИННЫЙ(ЗАМЕНИТЬ(B2,»A»,»»))

Для понимания логики расчетов разобьем процесс на отдельные этапы:

• Сначала подсчитайте общую длину строки в ячейке B2:
  ДЛИННЫЙ(B2)
• Затем вы используете функцию REPLACE, чтобы удалить все вхождения буквы «A» в B2 и заменить ее пустой строкой («»):
  ЗАМЕНИТЬ(B2,»А»;»»)
• Затем вычислите длину полученной строки без «A»:
  ДЛСТР(ЗАМЕНИТЬ(B2;»A»;»»))
• Наконец, вычтите длину строки без буквы «А» из исходной длины.

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

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

Примечание. Функция ЗАМЕНИТЬ в Excel чувствительна к регистру, поэтому приведенное выше выражение также чувствительно к регистру. Например, B7 содержит 2 вхождения буквы «А» — одно в верхнем регистре и одно в нижнем регистре. Учитываются только прописные буквы, потому что мы передали «A» функции REPLACE.

Читайте также: Как удалить пустые строки в Excel быстрыми способами

Количество знаков в столбце или строке

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

1. Чтобы растянуть результат на другие строки в столбце, подведите указатель мыши к правому нижнему углу ячейки с функцией «ДЛСТР», как только он изменит свой вид на маленький плюсик, удерживая левую кнопку мыши, перетащите до строки, для которой мы хотим получить аналогичный результат.
2. Как мы видим, благодаря этому простому действию мы смогли получить данные о количестве символов для каждой ячейки в выбранном столбце за считанные секунды.

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

Например, воспользуемся одним из следующих:

1. Выберите ячейки в столбце. Затем нажмите кнопку «Сумма» в разделе инструментов «Редактирование» (вкладка «Главная”).
2. Результат в виде общего количества символов во всех ячейках столбца отобразится в ячейке непосредственно под выбранным диапазоном.

Подсчет определенных букв в ячейке без учета регистра

Если вам нужно количество букв без учета регистра, вставьте функцию CAPS в REPLACE, чтобы преобразовать указанную букву в верхний регистр перед выполнением замены. И обязательно ищите заглавными буквами.

Например, чтобы посчитать буквы «А» и «а» в B2, используйте следующее:

=LIN(B2)-LIN(ЗАМЕНИТЬ(ВЕРХНИЙ(B2),$C$1;»»))

Другой способ — использовать вложенные функции замены:

=ДЛИННЫЙ(B2)-ДЛИННЫЙ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(B2,ВЕРХНИЙ($C$1),»»);НИЖНИЙ($C$1);»»))

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

Другой способ — преобразовать все в верхний или нижний регистр. Например:

=ДЛИННЫЙ(B2)-ДЛИННЫЙ(ЗАМЕНИТЬ(ВЕРХНИЙ(B2),ВЕРХНИЙ($C$1);»»))

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

Как посчитать вхождения текста или подстроки в ячейку?

Чтобы подсчитать, сколько раз определенная комбинация символов (например, конкретный текст или подстрока) появляется в данной ячейке (например, «A2» или «M5»), разделите количество конкретных символов, возвращаемых приведенными выше формулами, на длина подстроки.

Различает прописные и строчные буквы:

=(LON(B2)-LONG(SUBSTITUTE(B2,$C$1,»»)))/LON($C$1)

Нечувствительный к регистру:

=(ДЛИННЫЙ(B2)-ДЛИННЫЙ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(B2,ВЕРХНИЙ($C$1),»»);НИЖНИЙ($C$1),»»)))/ДЛИННЫЙ($C$1)

Где B2 — это запись, содержащая всю текстовую строку, а C1 — это текст (подстрока), который вы хотите подсчитать.

Как эта формула подсчитывает количество вхождений определенного текста в ячейку?

1. Функция REPLACE удаляет указанное слово из исходного текста.
   В этом примере мы удаляем слово, введенное в ячейку C1, из исходного текста, расположенного в ячейке B2:
   ЗАМЕНИТЬ(B2; $C$1;»»)
2. Затем функция DLSTR вычисляет длину текстовой строки без указанного слова.
   В этом примере DLSTR(SUBSTITUTE(B2, $C$1,»»)) возвращает длину текста в B2 после удаления всех букв и цифр во всех вхождениях «A2».
3. После этого указанное выше число вычитается из общей длины исходной текстовой строки:
   DLSTR(B2) — DLSTR(ЗАМЕНИТЬ(B2, $C$1;»»))

Результатом этой операции является количество символов, найденных во всех вхождениях целевого слова, которое в этом примере равно 4 (2 вхождения «A2», по 2 в каждом).

Наконец, приведенное выше число делится на длину искомого текста. Другими словами, вы делите количество символов во всех вхождениях целевого слова на количество символов в этом слове. В этом примере 4 делится на 2, и результат равен 2.

Сколько раз встречается символ в диапазоне?

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

СУММПРОИЗВ(ДСТР(диапазон) -ДСТР(ПОДСТАВИТЬ(диапазон; символ , «»)))

В этом примере выражение имеет следующий вид:

=СУММПРОИЗВ(DLSTR(B2:B7)-DLSTR(ЗАМЕНИТЬ(B2:B7,$C$1;»»)))

А вот еще один способ подсчета символов в диапазоне Excel:

{=СУММ(ДЛИННОЕ(B2:B7)-ДЛИННОЕ(ЗАМЕНИТЬ(B2:B7,$C$1;»»)))}

По сравнению с ранее описанным наиболее очевидным отличием здесь является использование СУММ вместо СУММПРОИЗВ. Еще одно отличие состоит в том, что в этом случае необходимо нажать Ctrl+Shift+Enter. Я думаю, вы помните, что в отличие от СУММПРОИЗВ, который может работать с массивами, СУММ может обрабатывать массивы только при использовании в формуле массива.

Давайте посмотрим, как это работает.

Как вы, возможно, помните, функция REPLACE используется для замены всех вхождений указанного символа («A» в этом примере) пустой текстовой строкой («»).

Затем мы передаем текстовую строку, возвращенную функцией REPLACE, в функцию DLSTR, чтобы она вычислила длину строки без «A». Затем мы вычитаем это количество символов из общей длины текстовой строки. Результатом этих вычислений является матрица с количеством символов. Он содержит столько чисел, сколько ячеек в диапазоне.

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

Примечание. В ситуациях, когда вы подсчитываете количество вхождений определенной подстроки в диапазоне (например, заказы, начинающиеся с «A2»), вы должны разделить количество символов на длину подстроки. В противном случае каждый символ в подстроке будет учитываться отдельно. Например:

=СУММПРОИЗВ(ДСТР(B2:B7)-ДСТР(ПОДСТАВИТЬ(B2:B7,$C$1,»»)))/ДСТР($C$1)

Подсчет определенных букв в диапазоне без учета регистра.

Вы уже знаете, что ПОДСТАВИТЬ — это функция, чувствительная к регистру. Поэтому наша формула подсчета также чувствительна к регистру.

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

Используйте функции UPPER и LOWER, введите заглавную или заглавную букву и сделайте ссылку на нее:

=СУММПРОИЗВ(DLSTR(B2:B7) — DLSTR(ЗАМЕНИТЬ(ЗАМЕНИТЬ(B2:B7,ВЕРХНИЙ($C$1),»»); НИЖНИЙ($C$1);»»)))

На скриншоте ниже показана окончательная формула в действии:

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

Чтобы посчитать, сколько раз именно в наших данных встречается комбинация «А2», напишем выражение:

=СУММПРОИЗВ((LIN(B2:B7)-LIN(ЗАМЕНИТЬ(B2:B7,$C$1,»»)))/LIN($C$1))

Если вам не нужно рассматривать регистр букв, то —

=СУММПРОИЗВ((LIN(B2:B7)-LIN(ЗАМЕНИТЬ(ЗАМЕНИТЬ(B2:B7,ВЕРХНИЙ($C$1),»»);НИЖНИЙ($C$1),»»)))/LIN($C$1))

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

Вы можете использовать функцию СУММ, но в формуле массива, которую мы также рассмотрели ранее.

Определение количества знаков в разбросанных ячейках

Рассмотренный выше метод практичен и применим только в случае ячеек в одном столбце или строке. Но что делать, когда нужно определить количество символов в ячейках, разбросанных по всему документу и размещенных в разных столбцах и ячейках.

В этом случае нам снова поможет суммирование значений ячеек. Вот что нужно сделать:

1. 1. Отмечаем ячейку, куда планируем вставить формулу. Затем ставим знак «равно» («=») и через плюсик добавляем функции «ДЛСТР» для каждой ячейки, которые будут участвовать в итоговом расчете. Например, нам нужно определить общее количество символов в ячейках A2, A5, A10, B3, B10, C8 и C12.
      Таким образом, итоговая формула должна выглядеть так:
      = ДЛИННЫЙ (A2) + ДЛИННЫЙ (A5) + ДЛИННЫЙ (A10) + ДЛИННЫЙ (B3) + ДЛИННЫЙ (B10) + ДЛИННЫЙ (C8) + ДЛИННЫЙ (C12).
   2. После того, как формула написана, нажмите клавишу Enter и получите готовый результат в выбранной ячейке.

Альтернатива данной функции

Excel позволяет пользователям найти правильное решение несколькими способами. Эта формула не исключение. Вы можете заменить эту функцию двумя другими: DLSTR и REPLACE. Чтобы определить номер заданного символа, сначала определите длину всего текста с помощью функции ДЛСТР, а затем вычтите из этого значения длину того же текста, только с ранее удаленными символами (которые вы ищете): «)). Загрузка содержит конкретные примеры.

Формула подсчета количества слов в ячейке Excel

Как подсчитать количество слов и пробелов в ячейках Excel? На самом деле слова в строке разделяются пробелом. Между каждым словом есть 1 пробел, поэтому пробелов в текстовой строке всегда на один меньше, чем слов. Простая формула для понимания: количество слов в строке = количество пробелов +1. Другими словами, нам нужна формула для подсчета количества пробелов в строке и добавления +1 к результату вычисления.

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

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

Формула выше содержит 3 функции. Одна из них — функция REPLACE, а затем два одинаковых DLSTR.

Во-первых, функция DLSTR проверяет длину промывки текста, пока она находится в исходном виде в ячейке A1.

=LSTR(A2)

Затем функция REPLACE удаляет все пробелы из исходной строки:

ЗАМЕНИТЬ(A2;» «;»»)

Установив функцию ПОДСТАВИТЬ в качестве аргумента функции ДЛСТР, мы можем автоматически определить длину строки без пробелов.

ДЛСТР(ЗАМЕНИТЬ(A2;» «;»»))+1

Примечание! В этот момент следует добавить +1, потому что последнее слово в тексте не имеет соответствующего пробела.

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

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

Формула подсчета количества символов в ячейке Excel

Как подсчитать количество символов в ячейках Excel? Точно так же мы можем подсчитать количество символов в слове или всей строке. Например, посчитаем количество букв «С» в слове «Миссисипи:

1. Определите длину исходного слова «Миссисипи» = 9.
2. Определяем длину того же слова, но без буквы «С» — «Миииипи» = 6.
3. Вычесть из исходного слова слово без буквы «С»: 9-6=3.

В результате формула автоматически подсчитала количество букв «С» во всех словах:

Примечание. В формуле используется функция ЗАГЛАВНЫЕ буквы для написания слов с заглавной буквы, поскольку функция ЗАМЕНИТЬ чувствительна к регистру. И мы ищем заглавную «С» в каждом слове в названии города.