Подсчет символов в ячейках
Смотрите также «*+*») считает ячейки, причинам, в ячейках все лишние пробелы.Первый вариантПолучится так. – «с» и с функцией «СУММ». добавить дополнительные числаExcel посчитал иНаходится функция «ДЛСТР»символы в суммируются в (92).нескольких ячейках ячейках в столбце ячейках является использование Например, длина «НаходитсяПримечание: в которых «+» Excel даты написаны Читайте об этом.
В тексте в «о». В ячейке Получится такая формула. , буквы, т.д. кавычки, это, тоже, на закладке «Формулы»Excel — этоРассмотрим,Щелкните ячейку B2. A. функций сумм вместе 98 градусов сегодня,Мы стараемся как может быть как в виде одного статью «Как удалить
В ячейке В86 ячейке Excel можно С66 напишем такую =СУММ(ДЛСТР(A49);ДЛСТР(A50);ДЛСТР(A51)) Этот способ удобен, символы, как и в разделе «Библиотека
и буквы, икак посчитать количество символовКлавиши Ctrl + C,Текстовые строки с ДЛСТР. В поэтому я буду можно оперативнее обеспечивать отдельно так и числа. Как из
лишние пробелы в пишем такую формулу. посчитать не только формулу.Получится так. когда очень много тире, пробелы. функций». Нажимаем на цифры, и запятая, в тексте
чтобы скопировать ячейкуФормулы этом примере функция перейти swimming» (за вас актуальными справочными в тексте. этих чисел сформировать Excel». =ДЛСТР(A86)-ДЛСТР(ПОДСТАВИТЬ(A86;» «;»»))
Попробуйте попрактиковаться
символы, но и=СУММПРОИЗВ(ДЛСТР(A66)-ДЛСТР(ПОДСТАВИТЬ(A66;{«с»;»о»};»»)))Четвертый вариант
таких ячеек. СмотритеВ Excel есть кнопку «Текстовые». В и точка, иExcel B2, а затемВыпей. LEN подсчитывается количество исключением кавычки) — материалами на вашем
|
Через поиск и |
нормальные даты, читайте |
|
Второй вариант |
Копируем формулу по |
|
слова. Какую формулуКопируем формулу по |
|
|
. об этом статью возможность вставить весь |
появившемся списке выбираем др. знак, и
-
,
-
выделите ячейки B3=ДЛСТР(A2) символов в каждой
42 символов — языке. Эта страница замену считаются не в статье «Преобразовать. столбцу. Какими способами
написать для этого, столбцу. Получится так.Как посчитать количество конкретных
«Добавить текст в текст из Word функцию «ДЛСТР».
-
пустое место между
-
количество слов и B4 иЖе еще этих мягких ячейке и функция 31 буквы, числа переведена автоматически, поэтому ячейки, а плюсы. число в датуКак посчитать количество пробелов
быстро скопировать формулу смотрите в статьеЭтой же формулой можно букв в ячейки с текстом в одну ячейку.В ячейке В49 устанавливаем
словами, числами, кавычки,
-
, нажмите клавиши Ctrl
-
французских булок. сумм прибавляет счетчиков: 2, 8 пробелов, ее текст может
В ячейке может Excel». в ячейках по столбцу, смотрите
support.office.com
Как посчитать количество символов в ячейке Excel.
«Посчитать количество слов посчитать количество цифр.Excel Excel». Обычным способом текст функцию «ДЛСТР». Диалоговое т.д. По-простому –букв + V, чтобыСъешь же еще этих= СУММ ((функция LEN запятую и апострофы содержать неточности и
быть несколько плюсов.Сергей анисимовExcel в статье «Копирование в ячейке Excel». Только, в формуле,.Третий вариант из Word вставляется окно функции заполнили сколько места занимает , вставить формулу в
мягких французских булок, ( 2. грамматические ошибки. ДляFelix: СЧЕТЕСЛИ (диапазон; «+»). в Excel» тут.Рассмотрим, вместо букв, вУ нас такая.
в несколько ячеек, так.
текст.цифр ячейки B3: B4. да выпей чаю.
cell1Чтобы использовать функцию, введите
нас важно, чтобы: Никак не могуРостислав титаренкоЧтобы посчитать пробелыПолучится так.как в кавычках пишем нужную таблица.Посчитать количество символов в
что нарушет всюВ строке «Текст» диалоговогоПервый вариант,Эта формула будет скопированаПодсчет символов в одной), функция LEN (
= ДЛСТР ( эта статья была сочинить формулу для: Выделить диапазон, нажать
в нескольких ячейкахExcel в ячейке В87Excel посчитать количество пробелов цифру.Первый способ.
нескольких ячейках таблицу. Как этого окна функции указываем
.конкретных букв в ячейки B3 ячейкеcell2ячейку
вам полезна. Просим
подсчета определенных символов ctrl=f потом сделайте
сразу, нужно установить посчитала 6 пробелов, в ячейкеПятый вариант
Нам нужно посчитатьExcel. избежать, смотрите в адрес ячейки сКак посчитать количество символов, т.д. и B4, аЩелкните ячейку B2.), (функция LEN () вас уделить пару в ячейке. Например, замену символа на
такую формулу. п. ч. перед
. Если между словами. количество буквы «с»Нам нужно посчитать
статье «Как вставить текстом, в котором в ячейке
В Excel нет функция подсчитает символыВведитеcell3в строке формул, секунд и сообщить, вывести в ячейку такой же. ПрогаОбратите внимание! словом «кино» стоит стоит не один,
Нам нужно посчитать в словах. В
количество символов в в ячейку Excel нужно посчитать символы.
Excel специальной кнопки подсчета в каждой ячейкеформулу =ДЛСТР(a2)
)) нажмите клавишу ВВОД
помогла ли она В1 количество символов посчитает количество замен.Формула заключена в два лишних пробела.
а несколько пробелов во всех ячейках
ячейку В66 пишем нескольких ячейках сразу, текст Word».Нажимаем «ОК». Получилась
.
количества символов в (20 27 и
.. на клавиатуре. вам, с помощью
Т в ячейкеАлексей матевосов (alexm)
фигурные скобки. Это
Как найти все (лишние пробелы), то букву «с», т.е. такую формулу. узнать их сумму.
Второй вариант
такая формула вУ нас такая
ячейке, как в 45).Формула подсчитывает символы в
Ниже приведены некоторые примеры
Нескольких ячеек: кнопок внизу страницы. А1.: Формула как у формула массива, п. лишние пробелы, смотрите они, тоже, посчитаются.
сложить букву «с»
=ДЛСТР(A66)-ДЛСТР(ПОДСТАВИТЬ(A66;»с»;»»))Первый способ
. ячейке А49. =ДЛСТР(A49) таблица. Word. Но естьПодсчет общего количества символов ячейке A2, 27
того, как работаетЧтобы применить такую
Для удобства такжеПытался через ДЛСТР. Сергея Анисимова, а э., после ее в статье «КакНапример, у нас во всех ячейках.Копируем формулу по.Посчитать количество цифр в
Копируем формулу поМы будем использовать функцию
функция, которая посчитает
В книге с примерами
«Итого», которая содержит функция ДЛСТР. же формулу в приводим ссылку на Пробелы смог исключить, считает почти как написания в ячейке, найти пробел в такая таблица.
excel-office.ru
Как посчитать количество пробелов в ячейке Excel.
Для этого не столбцу. Получится так. Устанавливаем функцию «ДЛСТР», ячейке столбцу. Какими способами «ДЛСТР». Для тех, количество символов в щелкните ячейку В6. все пробелы и
Скопируйте приведенную ниже таблицу несколько ячеек, введите
оригинал (на английском но не могу у Ростислава Титаренко
нажимаем сочетание клавиш Excel». В ячейке А87 перед нужно считать буквы
Кстати, эта формула поможет как в первом
Excel быстро скопировать формулу кто хочет запомнить
каждой ячейке илиВведите в ячейке формулу период в конце и вставьте ее формулу в первую языке) .
туда прицепить условие.
=СЧЁТЕСЛИ (диапазон; «*+*») «Ctrl» + «Shift»Лишние пробелы мешают словом «кино» стоят во всех ячейках
быстро проверить тесты варианте. Затем, функцией. по столбцу, смотрите эти буквы, можно
сразу во всех=СУММ(ДЛСТР(A2);ДЛСТР(A3);ДЛСТР(A4)) предложения. в ячейку A1 ячейку и затемЕсли вам нужно подсчитатьPavel55Уточнение. + «Enter». не только визуально, лишние пробелы.
отдельно. Достаточно написать учеников. : )
«Автосумма» складываем этиФормулу пишем, как в статье «Копирование их запомнить, как
ячейках. Эти способыи нажмите клавишуПримечание: на листе Excel.
перетащите маркер заполнения
количество символов в: Вот почитайте доконцаФормула =СЧЁТЕСЛИ (диапазон;Подробнее о формулах но и приИтак, одну формулу.Второй способ.
значения отдельных ячеек. в первом варианте. в Excel». сокращенное предложение «ДЛина
подсчета символов в ячейках ВВОД.ДЛСТР подсчитывает пробелы Перетащите формулу из вниз (или через) ячейках, функция LENиспользоватьЮрий М «+») считает ячейки, массива, читайте в работе формул, формулы
excel-office.ru
Как в Excel посчитать количество знаков, например «+» в заданном диапазоне?
как в ячейкеВ ячейке E66
Нам нужно посчитатьВторой способ Получится так.Получилось так. СТРоки». Эта функцияExcel
Так подсчитывается количество символов после последнего символа. ячейки B2 B4, диапазон ячеек. — которого подсчитывает
: Я вот такую
в которых только
статье «Формулы массива не считают. НоExcel посчитать количество пробелов напишем такую формулу.
в словах количество.Можно к имеющемуся тексту,Обратите внимание считает количество символовмы сейчас рассмотрим.
в каждой изПодсчет символов чтобы увидеть длинуЧтобы получить общее количество букв, чисел, знаков
Подсчет определенных символов в ячейке
формулу нашел. Работает «+» Excel». есть способ убрать.=СУММПРОИЗВ(ДЛСТР(A66:A68)-ДЛСТР(ПОДСТАВИТЬ(A66:A68;»с»;»»))) сразу двух буквСовмещаем функцию «ДЛСТР» числам в ячейках
. в ячейке.Сначала уточним, что трех ячеек, а
в текста во всех
символов в нескольких и все пробелы.=СУММПРОИЗВ(ДЛСТР(B1:B10)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(B1:B10);{«Т»;»Ж»};»»)))
Формула =СЧЁТЕСЛИ (диапазон;
planetaexcel.ru
Бывает, по разным
Вы когда-нибудь пытались подсчитать количество пробелов текстовой строки в ячейке? Конечно, вы можете пересчитать их по одной вручную, но, если необходимо подсчитать несколько ячеек, этот способ будет утомительным и трудоемким. Сегодня я могу рассказать о некоторых формулах решения этой проблемы в Excel.
Подсчитайте количество всех пробелов в текстовой строке с помощью формулы
Подсчитайте количество всех пробелов текстовой строки с помощью Kutools for Excel
Подсчитайте количество начальных пробелов текстовой строки с помощью формулы
Чтобы получить количество всех пробелов в текстовой строке, вам может помочь следующая формула, сделайте следующее:
1. Введите эту формулу: = LEN (A2) -LEN (ПОДСТАВИТЬ (A2; «»; «»))(A2 содержит текстовую строку, в которой вы хотите подсчитать пробелы) в пустую ячейку, см. снимок экрана:
Затем перетащите маркер заполнения к ячейкам, к которым вы хотите применить эту формулу, и все пробелы в ячейке будут подсчитаны следующим образом:
Подсчитайте количество всех пробелов текстовой строки с помощью Kutools for Excel
Подсчитайте количество всех пробелов текстовой строки с помощью Kutools for ExcelЕсли у вас есть Kutools for Excel, Его СЧЕТЧИК Функция Kutools Функции может помочь вам быстро подсчитать, сколько раз определенный символ (например, конкретная буква, запятая, пробел и т. д.) появляется в ячейке.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Щелкните ячейку, в которую вы хотите поместить результат, например B2.
2. Затем нажмите Кутулс > Kutools Функции > Статистические и математические > СЧЕТЧИК, см. снимок экрана:
3. В Аргументы функций диалоговое окно, нажмите 
с Внутри_текст , чтобы выбрать ячейку, в которой вы хотите получить количество пробелов, а затем введите пробел один раз в Найти_текст box, см. снимок экрана:
4. Затем нажмите OK , вы получите количество пробелов в текстовой строке, а затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, все пробелы среди текстовых строк были подсчитаны сразу. Смотрите скриншот:
Нажмите, чтобы узнать больше об этой функции COUNTCHAR.
Бесплатная загрузка Kutools for Excel Теперь
Приведенная выше формула может помочь вам подсчитать все пробелы в текстовой строке, но, если вам просто нужно подсчитать ведущие пробелы в текстовой строке, я могу представить вам другую формулу.
1. Введите эту формулу: = НАЙТИ (ЛЕВО (ОБРЕЗАТЬ (A2); 1); A2) -1 (A2 содержит текстовую строку, в которой вы хотите подсчитать пробелы) в пустую ячейку, см. снимок экрана:
2. Затем перетащите дескриптор заполнения в диапазон, который вы хотите содержать эту формулу, а затем все ведущие пробелы перед текстовой строкой будут подсчитаны, см. Снимок экрана:
Статьи по теме:
Как подсчитать, сколько раз символ / слово появляется в ячейке?
Как подсчитать по нескольким критериям в Excel?
Как использовать countif для расчета процента в Excel?
Как подсчитать конкретное значение на нескольких листах?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Подсчет символов в ячейках
Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше
Если необходимо подсчитать символы в ячейках, используйте функцию LEN, которая подсчитывает буквы, цифры, символы и все пробелы. Например, длина фразы «На улице сегодня 25 градусов, я пойду купаться» (не учитывая кавычки) составляет 46 символов: 36 букв, 2 цифры, 7 пробелов и запятая.
Чтобы использовать функцию, введите =LEN(cell) в строке формул, а затем нажмите клавишу ВВОД на клавиатуре.
Несколько ячеек: Чтобы применить одну и ту же формулу к нескольким ячейкам, введите формулу в первую ячейку, а затем перетащите маркер заполнения вниз (или поперек) диапазона ячеек.
Чтобы получить общее количество всех символов в нескольких ячейках, необходимо использовать функции СУММ вместе с LEN. В этом примере функция LEN подсчитывает символы в каждой ячейке, а функция СУММ добавляет счетчики:
=СУММ((LEN(
cell1
),LEN(
cell2
),(LEN(
cell3
))
))).
Попробуйте попрактиковаться
Ниже приведены некоторые примеры использования функции LEN.
Скопируйте приведенную ниже таблицу и вставьте ее в ячейку A1 на листе Excel. Перетащите формулу из B2 в B4, чтобы увидеть длину текста во всех ячейках столбца A.
|
Текстовые строки |
Формулы |
|---|---|
|
Быстрая бурая лиса. |
=ДЛСТР(A2) |
|
Быстрая бурая лиса вскочила. |
|
|
Съешь же еще этих мягких французских булок, да выпей чаю. |
Подсчет символов в одной ячейке
-
Щелкните ячейку B2.
-
Введите =LEN(A2).
Формула подсчитывает символы в ячейке A2, которая составляет 27, включая все пробелы и точку в конце предложения.
ПРИМЕЧАНИЕ: LEN подсчитывает пробелы после последнего символа.
Подсчет символов
в
нескольких ячейках
-
Щелкните ячейку B2.
-
Нажмите клавиши CTRL+C, чтобы скопировать ячейку B2, затем выделите ячейки B3 и B4, а затем нажмите клавиши CTRL+V, чтобы вставить формулу в ячейки B3:B4.
При этом формула копируется в ячейки B3 и B4, а функция подсчитывает символы в каждой ячейке (20, 27 и 45).
Подсчет общего количества символов
-
В книге с примерами щелкните ячейку В6.
-
Введите в ячейке формулу =СУММ(ДЛСТР(A2);ДЛСТР(A3);ДЛСТР(A4)) и нажмите клавишу ВВОД.
Это подсчитывает символы в каждой из трех ячеек и суммирует их (92).
Нужна дополнительная помощь?
Одна из самых полезных задач по работе с текстом в Excel – это автоматический подсчет количества слов или определенных в строке. Существует много разных способов для решения данной задачи, но мы рассмотрим самый простой.
Формула подсчета количества слов в ячейке Excel
Как посчитать количество слов и пробелов в ячейках Excel? Фактически слова в строке разделены символом пробела. Между каждым словом 1 пробел, поэтому пробелов в текстовой строке всегда на единицу меньше чем слов. Простая формула для понимания: количество слов в строке = количество пробелов +1. Другими словами нам нужна формула для подсчета количества пробелов в строке и к результату ее вычисления добавить +1.
Чтобы составить формулу в Excel для подсчета количества слов нам нужно выполнить 3 шага:
- Определить длину исходной строки с пробелами.
- Определить длину исходной строки без пробелов.
- Вычесть от длины строки с пробелами – строку без пробелов.
Выше указанная формула содержит 3 функции. Одна из них функция ПОДСТАВИТЬ, а далее идут две одинаковые ДЛСТР.
Сначала функция ДЛСТР проверяет длину текстовой стирки пока в оригинальном виде в ячейке A1.
=ДЛСТР(A2)
Далее функция ПОДСАВИТЬ удаляет из исходной строки все пробелы:
ПОДСТАВИТЬ(A2;» «;»»)
Помещение функции ПОДСАВИТЬ в качестве аргумента для функции ДЛСТР позволяет нам автоматически определить длину строки без пробелов.
ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»))+1
Обратите внимание! На этом этапе следует добавить +1, ведь последнее слово в тексте не имеет соответствующего ему пробела.
Дальше остались только вычесть от длины оригинального текста длину строки без пробелов и в результате мы автоматически получаем количество слов:
Практическое применения выше описных формул наиболее часто встречается при анализе больших списков ключевых фраз.
Формула подсчета количества символов в ячейке Excel
Как посчитать количество символов в ячейках Excel? Аналогичным способом мы можем подсчитать количество любых символов в слове или целой строке. Для примера посчитаем количество букв «С» в слове Миссисипи:
- Определяем длину исходного слова «Миссисипи» = 9.
- Определяем длину этого же слова, но без буквы «С» — «Мииипи» = 6.
- Вычитаем от исходного слова слово без буквы «С»: 9-6=3.
В результате формула автоматически посчитала количество букв «С» во всех словах:
Примечание. В формуле используется функция ПРОПИСН чтобы сделать все буквы большими в словах так как функция ПОДСТАВИТЬ чувствительна к регистру. А мы ищем большую букву «С» в каждом слове названия городов.
Skip to content
В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках.
В нашем предыдущем руководстве была представлена функция ДЛСТР , которая позволяет посчитать количество символов в ячейке Excel.
Функция ДЛСТР (LEN в английской версии) полезна сама по себе, а в связи с другими функциями, такими как СУММ, СУММПРОИЗВ и ПОДСТАВИТЬ, она может решать и более сложные задачи. Далее в этом руководстве мы более подробно рассмотрим несколько основных и более сложных выражений для подсчета количества знаков в Excel.
- Как посчитать все символы в диапазоне
- Как подсчитать определенные знаки в ячейке
- Подсчет определенных букв в ячейке без учета регистра
- Как посчитать вхождения текста или подстроки в ячейку?
- Сколько раз встречается символ в диапазоне?
- Подсчет определенных букв в диапазоне без учета регистра.
Как посчитать все символы в диапазоне
Когда дело доходит до подсчета общего количества знаков в нескольких клетках таблицы, сразу приходит на ум решение сделать это для каждой из них, а затем просто сложить эти числа:
=ДЛСТР(A2)+ДЛСТР(A3)+ДЛСТР(A4)
или
=СУММ(ДЛСТР(A2);ДЛСТР(A3);ДЛСТР(A4))
Описанное выше может хорошо работать для небольшого диапазона. Но вряд ли вы захотите таким образом складывать даже 20 чисел. Чтобы определить количество символов Excel в большем диапазоне, нам лучше придумать что-нибудь более компактное. Например, функцию СУММПРОИЗВ, которая перемножает массивы и возвращает сумму произведений.
Вот общая формула Эксель для подсчета символов в диапазоне:
=СУММПРОИЗВ(ДЛСТР( диапазон ))
И ваша реальная формула может выглядеть примерно так:
= СУММПРОИЗВ(ДЛСТР(A1:A4))
Другой способ подсчета знаков в диапазоне — использовать функцию ДЛСТР в сочетании с СУММ:
{=СУММ(ДЛСТР(A1:A4))}
В отличие от СУММПРОИЗВ, функция СУММ по умолчанию не вычисляет массивы. Поэтому вам нужно не забыть нажать Ctrl + Shift + Enter, чтобы превратить ее в формулу массива.
Как показано на следующем скриншоте, СУММ возвращает такой же результат:
Как работает эта формула подсчета символов диапазона?
Это один из самых простых вариантов для подсчета знаков в Excel. Перво-наперво функция ДЛСТР вычисляет длину текста для каждого адреса в указанной области и возвращает их в виде массива чисел. Затем СУММПРОИЗВ или СУММ складывают эти числа и возвращают общий итог.
В приведенном выше примере суммируется массив из 4 чисел, которые представляют длины строк в ячейках от A1 до A4:
=СУММПРОИЗВ({23;13;23;17})
Примечание. Обратите внимание, что функция Excel ДЛСТР считает абсолютно все символы в каждой ячейке, включая буквы, числа, знаки препинания, специальные символы и все пробелы (ведущие, конечные и пробелы между словами).
Как подсчитать определенные знаки в ячейке
Иногда вместо подсчета всех символов вам может потребоваться подсчитать только вхождения определенной буквы, числа или специального символа.
Чтобы подсчитать, сколько раз данный символ появляется в выбранной ячейке, используйте функцию ДЛСТР вместе с ПОДСТАВИТЬ:
=ДЛСТР( ячейка ) — ДЛСТР(ПОДСТАВИТЬ( ячейка ; символ ; «»))
Чтобы лучше понять этот расчет, разберём следующий пример.
Предположим, вы ведете базу данных о доставленных товарах, где каждый тип товара имеет свой уникальный идентификатор. И каждая запись содержит несколько элементов, разделенных запятой, пробелом или любым другим разделителем. Задача состоит в том, чтобы подсчитать, сколько раз данный уникальный идентификатор появляется в каждой записи.
Предполагая, что список доставленных товаров находится в столбце B (начиная с B2), и мы считаем число вхождений «A». Выражение выглядит следующим образом:
=ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2,»A»,»»))
Чтобы понять логику расчётов, давайте разделим процесс на отдельные этапы:
- Сначала вы подсчитываете общую длину строки в B2:
ДЛСТР(B2) - Затем вы используете функцию ПОДСТАВИТЬ, чтобы удалить все вхождения буквы «А» в B2, заменив ее пустой строкой («»):
ПОДСТАВИТЬ(B2;»А»;»») - Затем вы подсчитываете длину получившейся строки без буквы «А»:
ДЛСТР(ПОДСТАВИТЬ(B2;»А»;»»)) - Наконец, вы вычитаете длину строки без буквы «А» из первоначальной длины.
В результате вы получаете количество «удаленных» знаков, которое и равно общему числу вхождений этого символа в ячейку.
Вместо того, чтобы указывать букву или цифру, которые вы хотите подсчитать, в формуле, вы можете ввести их, а затем указать на эту ячейку. Таким образом, ваши пользователи смогут подсчитывать вхождения того, что они укажут отдельно, без изменения самой формулы:
Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру, поэтому в приведенном выше выражении также учитывается регистр. Например, B7 содержит 2 вхождения буквы «A» — одно в верхнем регистре и второе в нижнем регистре. Учитываем только символы верхнего регистра, потому что мы передали «A» функции ПОДСТАВИТЬ.
Подсчет определенных букв в ячейке без учета регистра
Если вам нужен счетчик букв без учета регистра, вставьте функцию ПРОПИСН в ПОДСТАВИТЬ, чтобы преобразовать указанную букву в верхний регистр перед выполнением подстановки. И обязательно используйте для поиска заглавные буквы.
Например, чтобы подсчитать буквы «A» и «a» в B2, используйте следующее:
=ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(B2);$C$1;»»))
Другой способ — использовать вложенные функции замены:
=ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;ПРОПИСН($C$1);»»);СТРОЧН($C$1);»»))
Как вы можете видеть на скриншоте ниже, оба варианта безупречно подсчитывают вхождения указанной буквы в верхнем и нижнем регистре:
Другой способ — преобразовать всё в верхний или нижний регистр. Например:
=ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(B2);ПРОПИСН($C$1);»»))
Преимущество этого подхода заключается в том, что независимо от того, используются прописные либо строчные буквы, ваша формула подсчета без учета регистра будет возвращать правильный счет:
Как посчитать вхождения текста или подстроки в ячейку?
Если вы хотите подсчитать, сколько раз определенная комбинация символов (например, определенный текст или подстрока) появляется в данной ячейке (например «A2» или «М5»), разделите количество определенных символов, возвращаемых приведенными выше формулами, на длину подстроки.
С учетом регистра:
=(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2;$C$1;»»)))/ДЛСТР($C$1)
Без учета регистра:
=(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;ПРОПИСН($C$1);»»);СТРОЧН($C$1);»»)) )/ДЛСТР($C$1)
Где B2 — запись, содержащая всю текстовую строку, а C1 – тот текст (подстроку), который вы хотите подсчитать.
Как эта формула подсчитывает количество вхождений определенного текста в ячейку?
- Функция ПОДСТАВИТЬ удаляет указанное слово из исходного текста.
В этом примере мы удаляем слово, введенное в ячейку С1, из исходного текста, расположенного в B2:
ПОДСТАВИТЬ(B2; $C$1;»») - Затем функция ДЛСТР вычисляет длину текстовой строки без указанного слова.
В этом примере ДЛСТР(ПОДСТАВИТЬ(B2; $C$1;»»)) возвращает длину текста в B2 после удаления всех букв и цифр, содержащихся во всех вхождениях «А2». - После этого указанное выше число вычитается из общей длины исходной текстовой строки:
ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2; $C$1;»»))
Результатом этой операции является количество символов, содержащихся во всех вхождениях целевого слова, которое в этом примере равно 4 (2 вхождения «A2», по 2 в каждом).
Наконец, указанное выше число делится на длину искомого текста. Другими словами, вы делите количество символов, содержащихся во всех вхождениях целевого слова, на число знаков, содержащихся в этом слове. В этом примере 4 делится на 2, и в результате мы получаем 2.
Сколько раз встречается символ в диапазоне?
Теперь, когда вы знаете формулу Excel для подсчета символов в одной определённой ячейке, вы можете улучшить ее, чтобы узнать, сколько раз определенный символ появляется в диапазоне. Для этого мы возьмем выражение, описанное в предыдущем примере, и поместим его в функцию СУММПРОИЗВ, которая умеет обрабатывать массивы:
СУММПРОИЗВ(ДЛСТР( диапазон ) -ДЛСТР(ПОДСТАВИТЬ( диапазон , символ , «»)))
В этом примере выражение принимает следующую форму:
=СУММПРОИЗВ(ДЛСТР(B2:B7)-ДЛСТР(ПОДСТАВИТЬ(B2:B7;$C$1;»»)))
А вот еще один способ для подсчета символов в диапазоне Excel:
{=СУММ(ДЛСТР(B2:B7)-ДЛСТР(ПОДСТАВИТЬ(B2:B7;$C$1;»»)))}
По сравнению с описанным ранее наиболее очевидным отличием здесь является использование СУММ вместо СУММПРОИЗВ. Другое отличие в том, что в данном случае требуется нажатие Ctrl + Shift + Enter. Думаю, вы помните, что в отличие от СУММПРОИЗВ, которая умеет работать с массивами, СУММ может обрабатывать массивы только при использовании её в формуле массива.
Разберем, как это работает.
Как вы, возможно, помните, функция ПОДСТАВИТЬ используется для замены всех вхождений указанного символа («A» в этом примере) пустой текстовой строкой («»).
Затем мы передаем текстовую строку, возвращаемую функцией ПОДСТАВИТЬ, в функцию ДЛСТР, чтобы она вычисляла длину строки без «A». Затем мы вычитаем это количество знаков из общей длины текстовой строки. Результатом этих вычислений является массив количества символов. В нем столько чисел, сколько ячеек в диапазоне.
Наконец, СУММПРОИЗВ суммирует числа в массиве и возвращает общее количество указанного символа в диапазоне.
Примечание. В ситуациях, когда вы подсчитываете количество вхождений определенной подстроки в диапазоне (например, заказы, начинающиеся с «A2»), вам необходимо разделить количество символов на длину подстроки. В противном случае каждый знак в подстроке будет учитываться индивидуально. Например:
=СУММПРОИЗВ(ДЛСТР(B2:B7)-ДЛСТР(ПОДСТАВИТЬ(B2:B7;$C$1;»»)))/ДЛСТР($C$1)
Подсчет определенных букв в диапазоне без учета регистра.
Вы уже знаете, что ПОДСТАВИТЬ — функция, чувствительная к регистру. Поэтому наша формула для подсчета также чувствительна к регистру.
Чтобы игнорировать регистр, следуйте подходам, продемонстрированным в предыдущем примере.
Используйте функции ПРОПИСН и СТРОЧН, введите прописную либо заглавную букву и укажите ссылку на нее:
=СУММПРОИЗВ(ДЛСТР(B2:B7) — ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2:B7;ПРОПИСН($C$1);»»);СТРОЧН($C$1);»»)))
На скриншоте ниже показана последняя формула в действии:
Совершенно аналогично, подобный метод можно применить, если мы будем считать число вхождений в диапазон какого- то слова. Вернемся к нашему примеру.
Чтобы подсчитать, сколько раз сочетание «А2» в точном виде встречается в наших данных, запишем выражение:
=СУММПРОИЗВ((ДЛСТР(B2:B7)-ДЛСТР(ПОДСТАВИТЬ(B2:B7;$C$1;»»)))/ДЛСТР($C$1))
Если не нужно учитывать регистр букв, то тогда —
=СУММПРОИЗВ((ДЛСТР(B2:B7)-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2:B7;ПРОПИСН($C$1);»»);СТРОЧН($C$1);»»)))/ДЛСТР($C$1))
То есть, мы берем формулы, использованные нами для отдельной ячейки, меняем в них ссылку на диапазон данных и обрабатываем затем при помощи СУММПРОИЗВ.
Вы можете использовать функцию СУММ, но в формуле массива, как мы также уже рассматривали ранее.
Вот как вы можете подсчитывать символы в Excel с помощью функции ДЛСТР. Если вы хотите знать, как считать слова, а не отдельные знаки, вы найдете несколько полезных формул в нашей следующей статье, следите за обновлениями!
Возможно, вам будут также полезны:
Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел — В статье объясняется на примерах как работают функции Excel ЗАМЕНИТЬ (REPLACE в английской версии) и ПОДСТАВИТЬ (SUBSTITUTE по-английски). Мы покажем, как использовать функцию ЗАМЕНИТЬ с текстом, числами и датами, а также… 
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… 
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… 
Как убрать пробелы в числах в Excel — Представляем 4 быстрых способа удалить лишние пробелы между цифрами в ячейках Excel. Вы можете использовать формулы, инструмент «Найти и заменить» или попробовать изменить формат ячейки. Когда вы вставляете данные из… 
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… 
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… 
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… 
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…