В Excel есть несколько удивительных текстовых функций, которые могут помочь вам при работе с текстовыми данными.
В некоторых случаях вам может потребоваться вычислить общее количество символов в ячейке / диапазоне или количество раз, когда определенный символ встречается в ячейке.
Хотя есть функция LEN, которая может подсчитывать количество символов в ячейке, вы также можете сделать все остальное с помощью комбинации формул (как мы увидим позже в примерах).
В этом уроке я расскажу о различных примерах, где вы можете подсчитать общее количество или отдельные символы в ячейке / диапазоне в Excel.
Подсчитать все символы в ячейке
Если вы просто хотите получить общее количество всех символов в ячейке, вы можете использовать функцию LEN.
Функция LEN принимает один аргумент, который может быть текстом в двойных кавычках или ссылкой на ячейку с текстом.
Например, предположим, что у вас есть набор данных, как показано ниже, и вы хотите подсчитать общее количество символов в каждой ячейке:
Ниже приведена формула, которая сделает это:
= LEN (A2)
Сама по себе функция LEN может выглядеть не так уж и много, но когда вы комбинируете ее с другими формулами, она может делать некоторые замечательные вещи (например, получать количество слов в ячейке или разделять имя и фамилию).
Примечание. Функция LEN будет подсчитывать все символы в ячейке, будь то специальный символ, числа, знаки препинания и пробелы (ведущие, конечные и двойные пробелы между словами).
Поскольку функция LEN считает каждый символ в ячейке, иногда вы можете получить неверный результат, если у вас есть лишние пробелы в ячейке.
Например, в приведенном ниже случае функция LEN возвращает 25 для текста в ячейке A1, тогда как должно было быть 22. Но поскольку она также считает лишние символы пробела, вы получите неверный результат.
Чтобы избежать подсчета лишних пробелов, вы можете сначала использовать функцию TRIM, чтобы удалить все начальные, конечные и двойные пробелы, а затем использовать для нее функцию LEN, чтобы получить реальное количество слов.
Нижеприведенная формула сделает это:
= ДЛСТР (ОБРЕЗАТЬ (A2))
Подсчитать все символы в диапазоне ячеек
Вы также можете использовать функцию LEN для подсчета общего количества символов во всем диапазоне.
Например, предположим, что у нас есть тот же набор данных, и на этот раз, вместо того, чтобы получать количество символов в каждой ячейке, я хочу знать, сколько их во всем диапазоне.
Вы можете сделать это, используя следующую формулу:
= СУММПРОИЗВ (LEN (A2: A7)))
Позвольте мне объяснить, как работает эта формула.
В приведенной выше формуле часть функции LEN берет весь диапазон ячеек и считает символы в каждой ячейке.
Результатом функции LEN будет:
{22;21;23;23;23;31}
Каждое из этих чисел представляет количество символов в ячейке.
И когда вы используете с ним функцию СУММПРОИЗВ, она просто складывает все эти числа.
Теперь, если вам интересно, почему нельзя использовать SUM вместо SUMPRODUCT, причина в том, что это массив, и SUMPRODUCT может обрабатывать массив, а функция SUM - нет.
Однако, если вы все еще хотите использовать СУММ, вы можете использовать приведенную ниже формулу (но помните, что вам нужно использовать Control + Shift + Enter для получения результата вместо обычного Enter)
= СУММ (ДЛИН (A2: A7))
Подсчет определенных символов в ячейке
Как я уже упоминал, реальная полезность функции LEN заключается в том, что она используется в сочетании с другими формулами.
И если вы хотите подсчитать определенные символы в ячейке (это может быть буква, цифра, специальный символ или пробел), вы можете сделать это с помощью комбинации формул.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите подсчитать общее количество слов в каждой ячейке.
Хотя встроенной формулы для подсчета слов нет, вы можете подсчитать пробелы, а затем использовать их, чтобы узнать общее количество слов в ячейке.
Ниже приведена формула, которая даст вам общее количество пробелов в ячейке:
= LEN (A2) -LEN (ПОДСТАВИТЬ (A2; ""; "")) + 1
В приведенной выше формуле подсчитывается общее количество пробелов, а затем к этому числу прибавляется 1, чтобы получить количество слов.
Вот как работает эта формула:
- Функция ЗАМЕНА используется для замены всех пробелов пробелом. Затем функция LEN используется для подсчета общего количества символов, когда нет пробелов.
- Результат LEN (ПОДСТАВИТЬ (A2, "", "")) затем вычитается из LEN (A2). Это дает нам общее количество пробелов в ячейке.
- В формулу добавляется 1, и общее количество слов будет на единицу больше, чем общее количество пробелов (поскольку два слова разделяются одним символом).
Обратите внимание, что если есть начальные, конечные или двойные пробелы, вы получите неправильный результат. В таком случае лучше всего использовать функцию ОБРЕЗАТЬ вместе с функцией ДЛИН.
Вы также можете использовать ту же логику для поиска определенного символа, слова или фразы в ячейке.
Например, предположим, что у меня есть набор данных, как показано ниже, где у меня есть разные пакеты, где каждый пакет имеет алфавит и номер для его представления (например, A1, J2 и т. Д.)
Ниже приведена формула, которая даст вам общее количество раз, когда партия с алфавитом A создавалась каждый месяц:
= LEN (B2) -LEN (ПОДСТАВИТЬ (B2; "A"; ""))
Вышеупомянутая формула снова использует ту же логику - найдите длину текста в ячейке с символом и без символа, который вы хотите подсчитать, а затем возьмите разницу этих двух.
В приведенной выше формуле я жестко запрограммировал символ, который хочу подсчитать, но вы также можете поместить его в ячейку, а затем использовать ссылку на ячейку. Это делает его более удобным, поскольку формула будет обновляться при следующем изменении текста в ячейке.
Подсчет символов с использованием формулы без учета регистра
Есть одна проблема с формулой, используемой для подсчета определенных символов в ячейке.
Функция ПОДСТАВИТЬ чувствительна к регистру. Это означает, что у вас «А» не равно «А». Вот почему вы получаете неправильный результат в ячейке C5 (результат должен был быть 3).
Итак, как вы можете получить количество символов определенного символа, если оно могло быть в любом случае (нижнем или верхнем).
Вы делаете это, делая вашу формулу нечувствительной к регистру. Хотя вы можете использовать сложную формулу, я просто добавил количество символов в обоих регистрах (строчные и прописные A).
= LEN (B2) -LEN (ПОДСТАВИТЬ (B2; "A"; "")) + LEN (B2) -LEN (ПОДСТАВИТЬ (B2; "a", ""))
Подсчет знаков / цифр до и после десятичной дроби
Я не знаю почему, но это частый вопрос, который я получаю от читателей и встречал на многих форумах, таких как этот.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите посчитать символы до десятичной дроби и после десятичной дроби.
Ниже приведены формулы, которые сделают это.
Считайте символы / числа до десятичной дроби:
= ДЛСТР (ЦЕЛОЕ (A2))
Считайте символы / числа после десятичной дроби:
= ДЛСТР (A2) -НАЙТИ ("."; A2)
Обратите внимание, что эти формулы предназначены только для значащих цифр в ячейке. Если у вас есть начальные или конечные нули или вы использовали настраиваемое форматирование чисел для отображения большего / меньшего количества чисел, приведенные выше формулы все равно будут давать вам значащие цифры до и после десятичной дроби.
Итак, это некоторые из сценариев, в которых вы можете использовать формулы для подсчета символов в ячейке или диапазоне ячеек в Excel.
Надеюсь, этот урок был вам полезен!
