Подсчитать количество символов в ячейке Excel
Обновлено: 31.10.2024
После этого руководства вы сможете подсчитывать общее количество символов в ячейке или диапазоне, количество вхождений определенного символа или комбинации символов в ячейке или диапазоне как с учетом регистра, так и без учета регистра. Знания, полученные в этом руководстве, являются действительно хорошей базой для дальнейшего творческого решения задач в Excel.
Как считать символы в Excel
Если вы хотите научиться считать символы в Excel, вам нужно использовать функцию ДЛСТР , формулу =ДЛСТР(ячейка) для подсчета общего количества символов в ячейке или комбинацию функций СУММПРОИЗВ и ДЛСТР для подсчета общего количества символов в ячейке. диапазон с формулой =СУММПРОИЗВ(ДЛСТР(диапазон)) . Кроме того, будет подробно объяснено подсчет определенного символа в ячейке или диапазоне и конкретной комбинации символов в ячейке или диапазоне.
- Подсчитать общее количество символов в ячейке
- Подсчитать общее количество символов в диапазоне
- Подсчет определенных символов в ячейке
- Подсчет определенных символов в диапазоне
- Подсчет определенных комбинаций символов в ячейке или диапазоне
Подсчитать общее количество символов в ячейке
Общее количество символов в ячейке можно легко найти с помощью функции Excel ДЛСТР. Эта функция имеет только один аргумент, ссылку на ячейку или текст, где общее количество символов должно быть подсчитано:
Эта функция подсчитывает общее количество символов в ячейке, включая пробелы, знаки препинания и символы, независимо от того, сколько раз они встречаются в строке.
Если мы хотим подсчитать общее количество символов в ячейке, исключая пробелы, потребуется комбинация формул ДЛСТР и ПОДСТАВИТЬ. Давайте посмотрим на приведенный ниже пример, формула, исключающая пробелы, выглядит так:
Функция ПОДСТАВИТЬ заменяет в определенной ячейке один символ/текст другим символом/текстом. Если мы хотим исключить пробелы, используя эту функцию, они будут устранены, заменив все вхождения пробелов пустой строкой. После ПОДСТАВИТЬ функциональный текст в ячейке будет выглядеть так:
После этого легко подсчитать общее количество символов с помощью функции ДЛСТР, и конечный результат составит 12 символов.
Подсчитать общее количество символов в диапазоне
Для подсчета общего количества символов в определенном диапазоне необходима комбинация двух функций: СУММПРОИЗВ и ДЛСТР . Использование функции СУММПРОИЗВ — элегантное решение, когда мы имеем дело с несколькими ячейками или массивами. Взгляните на приведенный ниже пример и комбинацию формул:
Функция LEN связана с ячейкой, но для диапазонов ячеек использование суммированных функций LEN (=LEN(B3)+LEN(B4)+LEN(B5)+LEN(B6)) не лучшее решение, поскольку мы могли бы Работа с огромными диапазонами. Вместо этого функция СУММПРОИЗВ суммирует результаты функции ДЛСТР в заданном диапазоне. При вычислении формулы результат будет выглядеть так:
Числа из массива представляют собой результаты функции LEN для каждой ячейки в определенном диапазоне B3:B6, что дает окончательный результат 75 символов.
Альтернативным решением для работы с диапазонами является использование комбинации функций СУММ и ДЛСТР . Синтаксис почти такой же, с той лишь разницей, что используются фигурные скобки для преобразования функции СУММ в формулу массива (сочетание клавиш для создания массива — CTRL+SHIFT+ENTER). В приведенной ниже формуле приведен точный синтаксис комбинации функций СУММ/ДЛСТР:
Подсчет определенных символов в ячейке
Помимо общего количества символов, в Excel также есть возможность подсчета количества вхождений определенных символов. Давайте рассмотрим пример подсчета количества отдельных символов в определенной ячейке. Для этого нужна комбинация функций ДЛСТР и ПОДСТАВИТЬ, как мы это сделали в аналогичном примере подсчета количества символов в ячейке без пробела. Синтаксис формулы будет выглядеть так:
В конкретном примере, если мы хотим подсчитать количество символов s в определенной ячейке B3, формула будет выглядеть так:
Поясним кратко логику комбинации функций. Общее количество символов в ячейке B3 вычитается из числа символов в той же ячейке, но без конкретного символа, который мы хотим подсчитать. Как упоминалось в учебном пункте 1, функция ПОДСТАВИТЬ используется для изменения строки в определенной ячейке в форме без определенного символа, заменяя этот символ пустой строкой.
В конечном результате можно заметить, что функция не считает символы верхнего регистра, поскольку функция LEN чувствительна к регистру. Решением для подсчета символов без учета регистра является использование функции ПРОПИСНЫЕ/НИЖНИЕ, где все символы будут переведены в верхний/нижний регистр, а функция станет нечувствительной к регистру.
В приведенном ниже примере функция LOWER вложена в функцию SUBSTITUTE, переводя всю строку в ячейке B3 в нижний регистр, поскольку критерии определены как строчные буквы «s»:
Еще одно решение для создания функции, не зависящей от регистра, — это использование двойной вложенной функции ПОДСТАВИТЬ в сочетании с функцией ДЛСТР. В дальнейшем примере счетный символ будет в конкретной ячейке, потому что иногда нецелесообразно каждый раз писать считать символ в формуле, особенно если вы имеете дело со сложными диапазонами и формулами.
Формула с двойной вложенной функцией ПОДСТАВИТЬ:
Формула может показаться сложной, но после объяснения все станет ясно. Давайте оценим формулу шаг за шагом.
Во-первых, мы хотим удалить строчные буквы из текста:
=SUBSTITUTE("Солнце светит";LOWER("S");"")
Функция НИЖН ставится для перевода символа счета в нижний регистр, а затем функция +ПОДСТАВИТЬ заменяет символ нижнего регистра на пустую строку. После этого шага результат формулы будет выглядеть так: «Sun i hining».
На следующем шаге формулы цель состоит в том, чтобы исключить из заданного текста/ячейки символ счета в верхнем регистре. Это решается вложенной функцией ПОДСТАВИТЬ:
Мы уже объяснили внутри функции ПОДСТАВИТЬ результат «Солнце» и поместим его в функцию ниже, чтобы прояснить ситуацию. В первой функции ПОДСТАВИТЬ строчный символ был заменен на пустую строку, а во второй функции ПОДСТАВИТЬ прописной счетный символ заменен на пустую строку, в результате чего в тексте не учитываются символы «s» и «S»: «обнуление» .
=SUBSTITUTE("Солнце"; ПРОПИСНАЯ("S");"")
Функция LEN затем просто подсчитывает количество символов в измененном тексте:
На последнем этапе вычисленный результат формулы, текст без букв "s" и "S", вычитается из общего количества символов в определенной ячейке:
=LEN("Солнце светит") - LEN("светит солнце")
Подсчет определенных символов в диапазоне
Всякий раз, когда мы имеем дело с диапазонами ячеек и массивами, функция СУММПРОИЗВ необходима в сочетании с другими функциями. Для подсчета определенного символа в диапазоне, чувствительного к регистру, используется комбинация трех функций: СУММПРОИЗВ, ДЛСТР и ПОДСТАВИТЬ. Для подсчета без учета регистра следует добавить дополнительные формулы: БОЛЬШАЯ/НИЖНЯЯ.
Во-первых, мы объясним подсчет количества вхождений символа «S» в определенном диапазоне (версия без учета регистра). Формула почти аналогична формуле, объясняющей подсчет символов. Отличие состоит в том, что функция ДЛСТР вложена в функцию СУММПРОИЗВ и в счетной области вместо ячейки определяется диапазон ячеек:
Для подсчета символов без учета регистра в определенном диапазоне мы можем использовать подробно объясненную функцию для подсчета символов без учета регистра в определенной ячейке в пункте 3 руководства с двумя изменениями: вложением функции ДЛСТР в функцию СУММПРОИЗВ и заменой ячейка с диапазоном ячеек:
Помните, что всякий раз, когда вы имеете дело с диапазонами ячеек, вам придется использовать функцию СУММПРОИЗВ. Для подсчета символов с учетом регистра в диапазоне необходима комбинация функций: СУММПРОИЗВ, ДЛСТР и ПОДСТАВИТЬ, а для подсчета без учета регистра: функции СУММПРОИЗВ, ДЛСТР, ПОДСТАВИТЬ и БОЛЬШЕ/НИЖНИЙ.
Подсчет определенных комбинаций символов в ячейке или диапазоне
Существует также возможность подсчета определенной комбинации символов в определенной ячейке или диапазоне. В предыдущих пунктах руководства мы рассмотрели подсчет отдельных символов в ячейке или диапазоне с альтернативами, чувствительными к регистру/нечувствительными к регистру. Формула подсчета комбинации символов та же самая, только мы должны разделить ее на количество символов в комбинации символов.
Давайте посмотрим на приведенный ниже пример для подсчета определенной комбинации символов в ячейке (версия без учета регистра):
Логика такая же, как и для подсчета отдельных символов, только нам нужно было разделить обычную формулу на количество конкретных символов, которые мы подсчитываем, легко используя формулу: LEN(“in”). Без деления формулы на LEN("in") результат будет умножен на количество символов в комбинации символов (в нашем примере на 2, так как "in" состоит из двух символов)
Для подсчета определенной комбинации символов в ячейке (версия с учетом регистра) формула будет выглядеть так:
Подсчет комбинаций символов в диапазоне имеет ту же логику, что и подсчет отдельных символов, и в этом случае формула конфиденциальной версии будет выглядеть так:
Обычная формула, описанная в разделе 4 учебника, делится на количество определенных символов, которые мы подсчитываем, используя функцию ДЛСТР.
Если нам нужна версия без учета регистра, то синтаксис формулы будет выглядеть так:
Все еще нужна помощь с форматированием Excel или есть другие вопросы об Excel? Свяжитесь с живым экспертом по Excel здесь, чтобы получить помощь один на один. Первый сеанс всегда бесплатный.
Если вам нужно подсчитать количество символов в ячейках, используйте функцию ДЛСТР, которая подсчитывает буквы, цифры, символы и все пробелы. Например, длина фразы "Сегодня 98 градусов, поэтому я пойду купаться" (без кавычек) – 42 символа: 31 буква, 2 цифры, 8 пробелов, запятая и 2 апострофа.
Чтобы использовать эту функцию, введите =LEN(ячейка) в строке формул, затем нажмите Enter на клавиатуре.
Несколько ячеек. Чтобы применить одну и ту же формулу к нескольким ячейкам, введите формулу в первую ячейку, а затем перетащите маркер заполнения вниз (или поперек) диапазона ячеек.
Чтобы получить общее количество всех символов в нескольких ячейках, используйте функции СУММ вместе с ДЛСТР. В этом примере функция ДЛСТР подсчитывает символы в каждой ячейке, а функция СУММ суммирует количество:
=СУММ((ДЛСТР( ячейка1 ),ДЛСТР( ячейка2 ),(ДЛСТР( ячейка3 )) )).
Попробуйте
Вот несколько примеров, демонстрирующих использование функции ДЛСТР.
Скопируйте приведенную ниже таблицу и вставьте ее в ячейку A1 на листе Excel. Перетащите формулу из B2 в B4, чтобы увидеть длину текста во всех ячейках столбца A.
Текстовые строки
Быстрая коричневая лиса.
Быстрая коричневая лиса прыгнула.
Быстрая коричневая лиса перепрыгнула через ленивую собаку.
Подсчет символов в одной ячейке
Введите =ДЛСТР(A2).
Формула подсчитывает количество символов в ячейке A2, что в сумме составляет 27, включая все пробелы и точку в конце предложения.
ПРИМЕЧАНИЕ: LEN подсчитывает все пробелы после последнего символа.
Подсчет символов в нескольких ячейках
Нажмите Ctrl+C, чтобы скопировать ячейку B2, затем выберите ячейки B3 и B4, а затем нажмите Ctrl+V, чтобы вставить формулу в ячейки B3:B4.
При этом формула копируется в ячейки B3 и B4, и функция подсчитывает символы в каждой ячейке (20, 27 и 45).
Подсчитать общее количество символов
В образце рабочей книги щелкните ячейку B6.
В ячейке введите =СУММ(ДЛСТР(A2),ДЛСТР(A3),ДЛСТР(A4)) и нажмите Enter.
Это подсчитывает символы в каждой из трех ячеек и суммирует их (92).
Давайте узнаем, как подсчитать количество символов в Excel. Подсчет может выполняться в одной ячейке и даже в диапазоне ячеек. Изучая это, давайте также прольем свет на подсчет конкретных символов. Этот учебник научит вас всему, что упомянуто выше, используя функцию ДЛСТР с различными комбинациями функций ПОДСТАВИТЬ, СУММПРОИЗВ и СУММ, работая вместе, чтобы выполнить весь монотонный подсчет за вас.
Подсчет символов или слов максимально полезен с точки зрения дизайна; Сколько символов будет на визитной карточке, сколько слов будет в брошюре, сколько символов поместится в зависимости от размера объекта, шрифта, дизайна. Когда все это подсчитано и отсортировано, открывается хорошая перспектива!
Что касается конкретных встроенных символов, вы можете искать определенные продукты по коду продукта, текстовому символу, обозначающему характеристику, и т. д. Какой бы важной или бессмысленной ни была ваша причина, мы тем не менее узнаем, как считать символы в Excel, так что давайте начните считать!
Оглавление
Подсчитать количество символов
Мы начнем с подсчета количества символов в Excel. В качестве примера мы будем использовать внутренние сообщения в поздравительных открытках. В зависимости от размера и дизайна поздравительной открытки вам нужно будет посмотреть, какое сообщение может поместиться где. Следовательно, будет полезно посмотреть, сколько символов содержит сообщение. Ниже приведен пример листа:
Мы рассмотрим, как подсчитывать символы в ячейке и в диапазоне с помощью функции ДЛСТР, которую вы будете часто видеть сегодня.
Подсчитать количество символов в ячейке
Количество символов в ячейке можно легко подсчитать с помощью функции ДЛСТР. Функция ДЛСТР возвращает количество символов в текстовой строке. Посмотрим, что из этого выйдет:
Вот формула, которую мы будем использовать для подсчета количества символов в ячейке:
А вот как выглядит примененная формула:
Функция ДЛСТР использовалась с одним аргументом; ссылка на ячейку для подсчета символов в тексте ячейки. LEN возвращает 76 как количество символов в C4. Это количество включает пробелы. Если вы хотите, чтобы ваш счет исключал пробелы, мы вам поможем. Прочитайте следующий сегмент.
Подсчет количества символов в ячейке без пробелов
Если вы хотите подсчитать количество символов без пробелов, мы можем подсунуть функцию ПОДСТАВИТЬ в функцию ДЛСТР. Функция ПОДСТАВИТЬ заменяет существующий текст новым текстом в текстовой строке. Символы пробела будут заменены пустым текстом, поэтому функция ДЛСТР не будет учитывать пробелы. Формула будет выглядеть так:
Функция ПОДСТАВИТЬ заменяет символы пробела (" ") в C4 пустым текстом (""). Таким образом, текст, который будет подсчитан функцией ДЛСТР, будет Пусть этот день рождения будет началом года, наполненного счастливыми воспоминаниями. Функция ДЛСТР возвращает подсчитанные символы в качестве результата.
Подсчитать количество символов в диапазоне
Если нам нужно подсчитать количество символов в диапазоне, мы можем следовать чему-то вроде сложения отдельных сумм, например:
Но пока мы на этом, давайте подробнее изучим потенциал функции LEN, не так ли? Теперь мы покажем вам, как подсчитать количество символов в диапазоне, используя функции ДЛСТР и СУММПРОИЗВ, а также функции ДЛСТР и СУММ вместе.
LEN с СУММПРОИЗВ
Здесь мы объединим функции ДЛСТР и СУММПРОИЗВ, чтобы получить общее количество символов в диапазоне. Функция СУММПРОИЗВ вычисляет произведения диапазонов, а затем сумму произведений. Кроме того, мы используем функцию СУММПРОИЗВ для суммирования количества функций ДЛСТР, поскольку ДЛСТР не вычисляет массивы самостоятельно. У нас есть 2 формулы ниже; один считает символы в диапазоне, включая пробелы, другой исключает пробелы:
С пробелами:
Функция СУММПРОИЗВ помогает функции ДЛСТР работать как формула массива. Работа функции ДЛСТР заключается в вычислении общего количества символов в ячейках C4:C6, которое мы суммируем с помощью функции СУММПРОИЗВ.
Без пробелов:
Прежде чем функции ДЛСТР и СУММПРОИЗВ начнут выполнять свою работу, функция ПОДСТАВИТЬ используется для замены пробелов из C4:C6 пустыми символами. Текстовые строки в C4:C6 будут считаться без пробелов.
Затем функция ДЛСТР продолжает считать символы, а функция СУММПРОИЗВ суммирует значения и возвращает 203 в качестве результата.
ДЛСТР с СУММ
Инстинктивно первой функцией, которая приходит на ум для суммирования, является функция СУММ, и она прекрасно работает и с функцией ДЛСТР. На самом деле, она работает точно так же, как функция СУММПРОИЗВ, описанная выше, с небольшим отличием. СУММПРОИЗВ заставляет формулу работать как формулу массива, но с ДЛСТР и СУММ вам нужно заключить формулу в фигурные скобки, чтобы преобразовать формулу в формулу массива. Давайте посмотрим, как это сделать, используя приведенную ниже формулу:
С пробелами:
После добавления формулы нажмите клавиши Ctrl + Shift + Enter, и вы увидите фигурные скобки, заключающие формулу в скобки. Это превращает его в формулу массива, чтобы функция ДЛСТР могла считать символы в C4:C6 как массив с помощью функции СУММ.
Без пробелов:
Чтобы исключить пробелы из подсчета, мы вложили функцию ПОДСТАВИТЬ, чтобы заменить все пробелы пустым текстом. Это оставит только буквы, цифры и символы для подсчета. Функция ДЛСТР подсчитывает символы каждой из ячеек в C4:C6, результаты добавляются функцией СУММ. В конце формула завершается нажатием клавиш Ctrl + Shift + Enter, чтобы работать как формула массива, и вот результаты:
Если вам интересно, что произойдет, если формулу не преобразовать в формулу массива, попробуйте посмотреть результат, не добавляя фигурные скобки. Формула вернет только количество одной ячейки; ячейка, к которой примыкает результат, несмотря на диапазон, присутствующий в формуле. Фигурные скобки превращают его в формулу массива, чтобы он мог принимать указанный диапазон.
Подсчет вхождений определенного символа
Иногда вам может потребоваться отсчитать определенный символ, чтобы отличить одни элементы от других в списке. Вы можете искать определенный текст, числовой код или символ. Мы поможем вам подсчитать количество вхождений определенного символа в ячейке и в диапазоне, и это методы, к которым вы можете обратиться, если сортировка и фильтрация вас не устраивают.
Подсчет вхождений определенного символа в ячейку
Например, мы ищем продукты в наборе данных, которые отмечены звездочкой (*). Отмеченные продукты будут отображаться в результатах как 1, а неотмеченные — как 0. Мы будем использовать функцию ДЛСТР для подсчета и функцию ПОДСТАВИТЬ для замены текста. Давайте поместим это в действие Excel. Формула будет такой:
Используя функцию ПОДСТАВИТЬ, мы заменяем звездочку в C3 пустым текстом и подсчитываем оставшиеся символы с помощью функции ДЛСТР. В результате получается 7 символов.
Все коды в нашем примере состоят из 7 символов, и ячейка, не содержащая звездочки, будет вычисляться как 7-7=0.
Все продукты, которые в результате формулы получают 1, являются отмеченными продуктами, а 0 – неотмеченными продуктами.
Подсчет вхождений определенного символа в диапазоне
Используя формулу, аналогичную той, которая используется для подсчета определенного символа в ячейке, мы включим в работу функцию СУММПРОИЗВ и диапазон, чтобы получить количество определенного символа в диапазоне. Это означает, что мы снова будем использовать функции ДЛСТР и ПОДСТАВИТЬ вместе с функцией СУММПРОИЗВ, чтобы собрать все вместе. Собранная формула:
Мы можем использовать приведенную выше формулу в нашем примере, чтобы узнать, сколько продуктов было отмечено звездочкой. В формуле все звездочки в диапазоне C3:C18 заменены пустым текстом с помощью функции ПОДСТАВИТЬ. Функция LEN подсчитывает все символы в C3:C18 (исключая звездочки), что дает 16 произведений, умноженных на 7-значный код, что равняется 112 символам.
В части функции LEN(C3:C18)-LEN(SUBSTITUTE(C3:C18,"*","")) число 112 вычитается из общего количества символов C3:C18. Если мы быстро проверим общее количество на листе, оно составит 122 символа. 122-112=10 — результат формулы.
Но при чем здесь функция СУММПРОИЗВ? СУММПРОИЗВ — это функция, которая упорядочивает результаты диапазона в формуле, а также работает для того, чтобы функция ДЛСТР работала как формула массива.
Поэтому в нашем наборе данных есть 10 отмеченных продуктов.
Обратный отсчет до характерного закрытия. Сегодня функция LEN стала тем другом, на которого мы можем рассчитывать (фактически считать с ним). Попробуйте использовать некоторые из этих методов в следующий раз, когда столкнетесь с чем-то столь же дотошным, как подсчет символов; никто не должен делать это в одиночку. И чтобы вы никогда не были так одиноки в своих приключениях с Excel, мы вернемся с еще одним аспектом для изучения.
Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!
Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.
Используйте функцию ДЛСТР для подсчета символов в Excel. Используйте ДЛСТР и ПОДСТАВИТЬ для подсчета определенных символов в Excel.
<р>1. Функция ДЛСТР в Excel подсчитывает количество символов в ячейке.
Объяснение: функция ДЛСТР считает 2 цифры, 1 пробел и 6 букв.
<р>2. Используйте SUM и LEN для подсчета количества символов в диапазоне ячеек.
Примечание: как вы понимаете, эта формула может быть довольно длинной.
<р>3.Приведенная ниже формула массива подсчитывает количество символов в диапазоне ячеек.
Примечание. Чтобы закончить формулу массива, нажмите CTRL + SHIFT + ENTER. Excel добавляет фигурные скобки <>. Используете Excel 365 или Excel 2021? Закончите, просто нажав Enter. Вы не увидите фигурных скобок. Константа массива используется в качестве аргумента для функции СУММ, что дает результат 24.
<р>4. Используйте ДЛСТР и ПОДСТАВИТЬ, чтобы подсчитать, сколько раз определенный символ (в данном примере символ а) встречается в ячейке.
Объяснение: функция ПОДСТАВИТЬ заменяет символ a (второй аргумент) пустой строкой (третий аргумент). LEN(SUBSTITUTE(A1,"a","")) равно 8 (длина строки без символа a). Если мы вычтем это число из 9 (общее количество символов в ячейке A1), мы получим количество вхождений символа a в ячейке A1.
<р>5. Приведенная ниже формула массива подсчитывает, сколько раз определенный символ (в данном примере символ a) встречается в диапазоне ячеек.
Объяснение: константа массива используется в качестве аргумента для функции СУММ, что дает результат 5. Функция ПОДСТАВИТЬ чувствительна к регистру. А на Аляске не считается.
<р>6. Приведенная ниже формула массива подсчитывает вхождение определенного символа как в нижнем, так и в верхнем регистре (в данном примере символа a).Читайте также: