Как подсчитать количество цифр в Excel

Обновлено: 20.11.2024

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

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

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

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

Это руководство охватывает:

Подсчитать все символы в ячейке

Если вы просто хотите получить общее количество всех символов в ячейке, вы можете использовать функцию ДЛСТР.

Функция ДЛСТР принимает один аргумент, которым может быть текст в двойных кавычках или ссылка на ячейку с текстом.

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

Ниже приведена формула, которая это сделает:

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

Примечание. Функция LEN подсчитывает все символы в ячейке, будь то специальный символ, цифры, знаки препинания и пробелы (начальные, конечные и двойные пробелы между словами).

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

Например, в приведенном ниже примере функция ДЛСТР возвращает 25 для текста в ячейке A1, тогда как должно было быть 22. Но, поскольку она также подсчитывает лишние символы пробела, вы получаете неверный результат.

Чтобы избежать подсчета лишних пробелов, вы можете сначала использовать функцию TRIM, чтобы удалить все начальные, конечные и двойные пробелы, а затем использовать функцию LEN, чтобы получить реальное количество слов.

Приведенная ниже формула сделает это:

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

Вы также можете использовать функцию ДЛСТР для подсчета общего количества символов во всем диапазоне.

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

Вы можете сделать это, используя приведенную ниже формулу:

Позвольте мне объяснить, как работает эта формула.

В приведенной выше формуле часть функции LEN принимает весь диапазон ячеек и подсчитывает символы в каждой ячейке.

Результатом функции ДЛСТР будет:

Каждое из этих чисел представляет количество символов в ячейке.

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

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

Однако, если вы все же хотите использовать SUM, вы можете использовать приведенную ниже формулу (но помните, что вам нужно использовать Control + Shift + Enter, чтобы получить результат вместо обычного Enter)

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

Как я уже упоминал, функция ДЛСТР по-настоящему полезна, когда она используется в сочетании с другими формулами.

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

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

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

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

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

Вот как работает эта формула:

Результат LEN(SUBSTITUTE(A2", ",")) затем вычитается из LEN(A2). Это дает нам общее количество пробелов в ячейке.
В формулу добавляется 1, и общее количество слов будет на один больше, чем общее количество пробелов (поскольку два слова разделяются одним символом).

Обратите внимание, что если есть начальные, конечные или двойные пробелы, вы получите неверный результат. В таком случае лучше всего использовать функцию TRIM вместе с функцией LEN.

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

Например, предположим, что у меня есть набор данных, как показано ниже, в котором есть разные пакеты, где каждый пакет имеет алфавит и число для его представления (например, A1, J2 и т. д.)

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

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

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

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

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

Функция ЗАМЕНИТЬ чувствительна к регистру. Это значит, что у вас «А» не равно «а». Вот почему вы получаете неправильный результат в ячейке C5 (результат должен был быть 3).

Итак, как вы можете получить количество символов определенного символа, если оно могло быть в любом случае (ниже или выше).

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

Подсчет символов/цифр до и после десятичного числа

Не знаю почему, но это частый вопрос, который я получаю от своих читателей и встречал на многих форумах, например, на этом

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

Ниже приведены формулы, которые это сделают.

Подсчет символов/цифр до десятичной точки:

Подсчет символов/цифр после запятой:

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

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

Если вам нужно подсчитать количество символов в ячейках, используйте функцию ДЛСТР. Функция считает буквы, цифры, символы и все пробелы. Например, длина фразы "Сегодня 98 градусов, поэтому я пойду купаться" (без кавычек) – 42 символа: 31 буква, 2 цифры, 8 пробелов, запятая и 2 апострофа.

Чтобы использовать эту функцию, введите =ДЛСТР(ячейка) в строке формул и нажмите клавишу ВВОД. В этих примерах cell — это ячейка, которую вы хотите подсчитать, например B1.

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

Чтобы подсчитать общее количество символов в нескольких ячейках, используйте функции СУММ вместе с ДЛСТР. Например, =СУММ((ДЛСТР(ячейка1),ДЛСТР(ячейка2),(ДЛСТР(ячейка3)))). В этом примере функция ДЛСТР подсчитывает количество символов в каждой ячейке, а функция СУММ суммирует количество.

Чтобы подсчитать определенные символы в ячейках, используйте функцию ДЛСТР с функцией ПОДСТАВИТЬ. Например, если вы хотите узнать, сколько строчных букв Z находится в ячейке, вы должны использовать следующую формулу: =ДЛСТР(ячейка1)-ДЛСТР(ПОДСТАВИТЬ(ячейка1, "з",""))

Попробуйте

Введите следующие данные в новый лист Excel. Начните с ячейки A1.

Быстрая коричневая лиса

Быстрая коричневая лиса прыгнула

Быстрая коричневая лиса перепрыгнула через ленивую собаку

Подсчет символов в одной ячейке

В ячейке введите =LEN(A2) и нажмите Enter.

Формула подсчитывает количество символов в ячейке A2, и это число равно 19, включая все пробелы. Если у вас есть пробелы после последней точки, формула также учитывает их.

Подсчет символов в нескольких ячейках

Нажмите CONTROL + C, чтобы скопировать ячейку B2.

Нажмите CONTROL + V, чтобы вставить формулу в ячейки B3:B4

При этом формула копируется в ячейки B3 и B4, и функция подсчитывает символы в каждой ячейке (26 и 44).

Подсчитать общее количество символов

В ячейке введите =СУММ(ДЛСТР(A2),ДЛСТР(A3),ДЛСТР(A4)) и нажмите Enter.

Это подсчитывает символы в каждой из трех ячеек и суммирует их (89).

Чтобы подсчитать определенные символы в ячейках

В ячейке введите =ДЛСТР(A4)-ДЛСТР(ПОДСТАВИТЬ(A4,"z","")) и нажмите Enter.

Есть ли функция, которая позволит мне подсчитать количество цифр в ячейке? Я нашел функцию "len", которая подсчитывает количество символов для текста, но не для числа.

Факты об Excel

ПОИСКПОЗ использует -1, чтобы найти большее значение (таблица поиска должна быть отсортирована ZA). XLOOKUP использует 1, чтобы найти значения больше, и не требует сортировки.

Джей Петрулис

MrExcel MVP

Можно уточнить? Почему LEN(ячейка) не работает? Так и должно быть.

Аладин Акюрек

MrExcel MVP

15 апреля 2002 г., 14:33, izzyq написал:
Есть ли функция, которая позволит мне подсчитать количество цифр в ячейке. Я нашел функцию "len", которая подсчитывает количество символов для текста, но не для числа.

В A1 введите: 567
В B1 введите: =LEN(A1), что даст вам 3.

В поле A2 введите: 56,48
В поле B2 введите: =LEN(A2), что даст вам 5.

Если вы хотите избежать подсчета десятичной точки, используйте:

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

иззик

Новый участник

У меня сложилось впечатление, что вам нужно было ввести текстовую строку в кавычках, чтобы она работала. LEN("1111") = 4. Я больше не могу подсчитать количество цифр в ячейке C:8? Любая идея

Аладин Акюрек

MrExcel MVP

15 апреля 2002 г., 14:48, izzyq написал:
У меня сложилось впечатление, что для работы текст нужно заключать в кавычки. LEN("1111") = 4. Я больше не могу подсчитать количество цифр в ячейке C:8? Любая идея

суммирует длины цифр/чисел из каждой ячейки в A1:A5.

Активный участник

Я так понимаю, Лен не сработает, когда в ячейке есть Персонажи.

В этом случае вставьте следующую UDF (определяемую пользователем функцию) в модуль VB и вызовите ее из Excel, как любую обычную функцию.

Функция HowMany(Rng)
HowMany = 0
If IsNumeric(Rng) Then
HowMany = Len(Rng)
GoTo EndFunc
End Если

Для x = 1 To Len(Rng)
Ext = Mid$(Rng, x, 1)
If InStr(1, "0123456789", Ext, vbTextCompare) > 0 Then
>Сколько = Сколько + 1
Конец, если
Далее

EndFunc:
Завершить функцию

Марк В.

MrExcel MVP

izzyq, возможно, вам нужно познакомиться с концепцией Excel — "принуждением". Некоторые операторы и функции Excel «принуждают» один тип данных к другому. Например, 1111&"" дает "1111", а "1111"+0 дает 1111. Таким образом, когда вы используете =LEN(1111), он преобразует 1111 в "1111" и дает 4. Итак, как предложил Аладин. =LEN(SUBSTITUTE(111.1,".","")) подсчитывает числа.

Неограниченная мощность

Новый участник

Частная функция GetDigitCount(inValue As Double) As Double
GetDigitCount = Int(Log(inValue) / Log(10)) + 1
Конец функции

Частная подпрограмма Command1_Click()
MsgBox GetDigitCount(12345)
End Sub

Эта функция подсчитывает количество цифр в числе.
Вместо 12345 введите свой номер.

Иван Тан

Новый участник

привет, формула len работает для подсчета количества вхождений одной цифры в диапазоне ячеек, но как насчет подсчета вхождений двузначного числа? fficeffice" />

например,

A1: 65306447 (1-й раз)
B2: 65306464 (второй раз)

Смогу ли я по-прежнему использовать формулу len для подсчета, чтобы узнать двузначное число '4 " в этом диапазоне ячеек появляются 2 раза как подчеркнутые. Даже для трехзначного числа "6", и так далее, и тому подобное.

Какая тогда формула? Спасибо.

Иван Тан

Новый участник

Смогу ли я по-прежнему использовать формулу len для подсчета, чтобы узнать, что двузначная цифра «4» в этом диапазоне ячеек появляется 2 раза, как подчеркнуто. Даже для трехзначного числа «6» и т. д. и т. п.

Какая тогда формула? спасибо.

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

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

Щелкните правой кнопкой мыши одну из ячеек и выберите "Формат ячеек", чтобы открыть диалоговое окно "Формат ячеек".

Перейдите на вкладку "Число" и просмотрите форматы в разделе "Категория".

Нажмите на формат "Текст", чтобы выбрать его. Этот формат указывает Excel обрабатывать значения в ячейках как текст. Если вы не видите текстовый формат, прокрутите вниз до раздела «Категория», чтобы найти «Текст».

Нажмите "ОК", чтобы сохранить изменения. Введите большие числа в выбранные ячейки, и Excel отобразит их правильно.

Когда вы вводите в ячейки большие числа, вы можете обнаружить, что они не помещаются в свои ячейки. Если вы хотите, чтобы ячейки Excel автоматически подходили к размеру, выберите диапазон, размер которого вы хотите изменить, нажмите «Главная» и нажмите кнопку «Формат» в разделе «Ячейки» ленты. Нажмите "Автоподбор ширины столбца", чтобы изменить размер ячеек.
Если вам нужно включить десятичные дроби в числа, отформатированные как текст, введите десятичные дроби при добавлении чисел в ячейки.

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

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

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