Количество символов в ячейке в эксель

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

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

Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .

Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(» «; A2)). А теперь вы вставляете это выражение в ЛЕВСИМВ:

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

Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса («-«), а не пробела:

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

Как фильтровать ячейки по длине текста в Excel?

Иногда вам может потребоваться отфильтровать ячейки по длине текста в Excel. Например, вам нужно отфильтровать текст длиной 6 символов. Эта статья покажет вам подробные инструкции о том, как фильтровать ячейки по заданной длине текста (количеству символов).

Отфильтруйте ячейки по заданной длине текста с помощью команды Filter

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

Шаг 1. Выберите столбец B и щелкните значок Фильтр под Данные меню.

Шаг 2: Щелкните стрелку фильтра

Текстовые фильтрыПользовательский фильтр

Шаг 3. Во всплывающем диалоговом окне Custom AutoFilter укажите критерии как Равнои введите 6 вопросительных знаков, чтобы указать длину.

Если вы хотите отфильтровать ячейки, которые содержат 6 символов, введите 6 вопросительных знаков (?) как показано на следующем снимке экрана.

Шаг 4: нажмите OK.

Затем он фильтрует ячейки с 6 символами в столбце B.

Быстро фильтруйте ячейки по длине текста в Excel:

Освободи Себя Специальный фильтр полезности Kutools for Excel может помочь вам быстро отфильтровать ячейки по определенной длине текста в Excel, как показано в демонстрации ниже.Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)

Фильтровать ячейки по заданной длине текста с помощью функции Len

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

Шаг 1. В столбце справки, в столбце D, введите формулу = ДЛСТР (B2).

Функция Len будет подсчитывать символы в определенной ячейке и возвращает длину символов.

Шаг 2: выберите столбец D, щелкните значок Главная >> Заполнять >> вниз чтобы скопировать эту формулу во весь столбец.

Шаг 3: выберите столбец D и щелкните значок Фильтр под Данные меню.

Шаг 4: Щелкните стрелку фильтра

Выбрать всеOK

Теперь он фильтрует число 6 в столбце B и показывает ячейки в столбце B длиной всего 6 символов. См. Следующий снимок экрана:

Фильтр ячеек по длине текста с помощью Kutools for Excel

В этом разделе будет представлена ​​утилита специального фильтра Kutools for Excel, которая поможет вам легко фильтровать ячейки по длине текста в Excel. Пожалуйста, сделайте следующее.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Kutools Plus > Специальный фильтр чтобы включить функцию. Смотрите скриншот:

2. в Специальный фильтр диалоговое окно, настройте следующим образом:

• 2.1). Диапазон поле выберите столбец, в котором вы будете фильтровать данные по длине;
• 2.2). Фильтр раздел правил, сохраните Текст выбран вариант, выберите Длина текста равна вариант из раскрывающегося списка, а затем введите длину символов в текстовое поле;
• 2.3) Нажмите OK кнопка. Смотрите скриншот:

3. Появится диалоговое окно, в котором сообщается, сколько ячеек найдено. Нажмите OK закрыть его.

Затем ячейки с указанной длиной текста немедленно фильтруются.

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

Методы подсчёта слов в ячейках Excel

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

Способ 1: ручной подсчёт

Этот метод не совсем подходит для MS Excel, вне зависимости от ее версии, т.к. данная программа задействует инструменты автоматизированного вычисления. Однако счёт вручную также целесообразно рассмотреть в рамках статьи. Для его реализации необходимо:

1. Составить исходный табличный массив.
2. Выделить нажатием левой клавишей мышки ячейку, в которой необходимо подсчитать слова.
3. Сосчитать набранные элементы.
4. Чтобы не терять собственное время, можно скопировать содержимое ячейки, которое целиком отображается в строке для ввода формул, и вставить его в рабочее поле специального сайта для быстрого подсчёта количества знаков, слов.

Способ 2: использование Microsoft Office Word

В текстовом редакторе все набранные слова автоматически считаются, и их количество выводится на экране. Чтобы воспользоваться таким методом, пользователю Эксель потребуется:

Выделить ЛКМ слова в ячейке таблички, чтобы в дальнейшем подсчитать их количество.
Переключить клавиатуру на английскую раскладку и одновременно зажать клавиши «Ctrl+С», чтобы скопировать выделенные символы в буфер обмена.
Открыть текстовый редактор MS Word.
Поставить курсор мышки на начало рабочего поля программы и нажать с клавиатуры на кнопки «Ctrl+V».
Проверить результат

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

Способ 3: применение специальной функции

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

1. Выделить любую пустую ячейку на рабочем листе программы. В нее в дальнейшем будет выводиться результат вычислений.
2. Поставить курсор мыши в строку для ввода формул сверху программы и написать с клавиатуры следующее выражение: «=ДЛСТР(СЖПРОБЕЛЫ(аргумент))-ДЛСТР(ПОДСТАВИТЬ(аргумент;” “;””))+1».
3. Вместо слова «Аргумент» указывается адрес ячейки, в которой проводится вычисление.

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

Технические характеристики и ограничения Excel

Технические характеристики и ограничения листа и книги

Количество открытых книг

Ограничено объемом доступной оперативной памяти и ресурсами системы

Общее количество строк и столбцов на листе

1 048 576 строк и 16 384 столбца

1 026 горизонтальных и вертикальных

Общее количество знаков в ячейке

Число знаков в верхнем и нижнем колонтитуле

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

Количество листов в книге

Ограничено объемом доступной оперативной памяти (по умолчанию 1 лист)

Количество цветов в книге

16 миллионов цветов (32-битовый спектр с полным доступом к 24-битовому спектру)

Именованные представления в книге

Ограничено объемом доступной оперативной памяти

Количество уникальных форматов/стилей ячеек

Количество стилей заливки

Количество стилей и толщин линий

Количество уникальных шрифтов

1 024 доступных глобальных шрифта; 512 шрифтов в каждой книге

Количество численных форматов в книге

От 200 до 250, в зависимости от установленной языковой версии Excel

Количество имен в книге

Ограничено объемом доступной оперативной памяти

Количество окон в книге

Ограничено объемом доступной оперативной памяти

Гиперссылки на листе

66 530 гиперссылок

Количество областей в окне

Количество связанных листов

Ограничено объемом доступной оперативной памяти

Ограничено объемом доступной оперативной памяти; в итоговый отчет включается 251 сценарий, начиная с первого

Количество изменяемых ячеек в сценарии

Количество вычисляемых ячеек в надстройке «Поиск решения»

Количество пользовательских функций

Ограничено объемом доступной оперативной памяти

от 10 до 400 процентов

Ограничено объемом доступной оперативной памяти

Количество ссылок для сортировки

64 для однократной сортировки; не ограничено для последовательных сортировок

Количество уровней отмены

Количество полей в форме данных

Количество параметров в книге

255 параметров в одной книге

Количество элементов, которые отображаются в раскрывающихся списках фильтров

Количество независимых ячеек, которые можно выделить

2 147 483 648 ячеек

Максимальные пределы объема памяти и размера файла для книг с моделями данных

В 32-разрядной среде выделяется 2 гигабайта (ГБ) виртуального адресного пространства, которое совместно используется приложением Excel, книгой и надстройками, работающими в рамках одного процесса. Доля адресного пространства, выделяемого для модели данных, может достигать 500–700 мегабайт (МБ), но может быть и меньше, если загружаются другие модели данных и надстройки.

64-разрядная среда не накладывает жестких ограничений на размер файлов. Размер книги ограничен только объемом доступной памяти и ресурсами системы.

Начиная с Excel 2016, функция, поддерживающая большое количество адресов, позволяет приложению 32-bit использовать память дважды, когда пользователи работают в 64-разрядной операционной системе Windows. Дополнительные сведения можно найти в разделе изменение возможностей, использующих большие адреса в Excel.

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

Считаем общее количество дубликатов в столбце.

Самый простой способ подсчета повторений в столбце — это использовать любую из формул, которые мы использовали для идентификации дубликатов в Excel (ссылки смотрите в конце этой статьи). И затем вы можете подсчитать повторы:

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

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

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

Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ввод. 

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

Можно обойтись и без формулы маиисва:

Это работает и с текстом, и с числами, а пустые ячейки игнорируются, что также очень полезно.

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

Здесь также есть два способа. Первый – с использованием вспомогательного столбца В.

В столбце B проставляем соответствующие отметки, как мы это уже не раз делали.

Далее  определяем количество ячеек, содержимое которых встречается не в первый раз:

или формула массива

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

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

Ну а теперь школьная задачка: если у нас всего 16 слов (можно использовать функцию СЧЁТЗ), и из них 10 – уникальных, то сколько будет неуникальных? Правильно – 6!

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

Ниже приведен алгоритм действий для использования функции “ДЛСТР”:

1. Становимся в ту ячейку, куда планируем вставить функцию, после чего щелкаем по кнопке “Вставить функцию” (с левой стороны от строки формул).
2. Откроется окно Мастера функций. Кликнув по текущей категории выбираем пункт “Текстовые”.
3. В предложенном перечне операторов выбираем “ДЛСТР” и жмем кнопку OK.
4. На экране отобразится окно аргументов функции. На самом деле у данного оператора всего одно поле для заполнения, в котором нужно указать координаты ячейки, количество символов в которой нужно посчитать. Причем указать можно только адрес конкретной ячейки, ссылки на несколько ячеек или диапазон элементов оператором не поддерживаются. Прописать координаты в поле аргумента можно вручную. Также выбрать нужную ячейку можно, щелкнув левой кнопкой мыши по ней, предварительно кликнув по области ввода информации. После того, как значение аргумента заполнено, кликаем OK.
5. Результат в виде количества символов в выбранной ячейке (в аргументах функции) сразу же отобразится на экране. А готовая формула функции отобразится, соответственно, в строке формул.

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

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

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

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

=(LEN(text)-LEN(SUBSTITUTE(text,word,»»)))/LEN(word)

1. Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите Enter ключ.

= (LEN (B2) -LEN (ПОДСТАВИТЬ (B2; D2; «»))) / LEN (D2)

Заметки:

1. В формуле B2 — это ячейка с текстовой строкой, внутри которой нужно посчитать определенное слово, D2 содержит определенное слово, которое вы будете считать. Измените ссылки на ячейки в зависимости от ваших ячеек.
2. Эта формула может только подсчитывать количество определенных слов в одной ячейке.
3. Эта формула чувствительна к регистру.

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

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

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите пустую ячейку для вывода результата, затем щелкните Kutools > Формула Помощник > Формула Помощник.

2. в Помощник по формулам диалоговое окно, настройте следующим образом.

2.1). Выберите формулу коробка, найдите и выберите Подсчитайте количество слова;Советы: Вы можете проверить Фильтр введите ключевое слово в текстовое поле, чтобы быстро отфильтровать нужную формулу.
2.2). Текст выберите ячейку или диапазон ячеек, в которых вы хотите подсчитать, сколько раз встречается слово;
2.3). Word поле укажите ячейку, содержащую конкретное слово, которое вы будете считать;Советы: Вы можете напрямую ввести определенное слово в поле Word, как показано на скриншоте ниже.
2.4) Нажмите ОК. 

Тогда вы получите результат в выбранной ячейке. Смотрите скриншот:

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

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

Подсчет знаков

Для подсчета символов в Excel существует специальная функция, которая называется «ДЛСТР». Именно с её помощью можно просуммировать знаки в конкретном элементе листа. Существует несколько способов её использования.

Способ 1: подсчет символов

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

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

Запускается Мастер функций. Ищем в нем наименование ДЛСТР и жмем на кнопку «OK».

Вслед за этим происходит открытие окна аргументов. Данная функция имеет только один аргумент – адрес конкретной ячейки. Причем нужно заметить, что в отличие от большинства других операторов, этот не поддерживает ввод ссылок на несколько ячеек или на массив. В поле «Текст» вводим вручную адрес элемента, в котором нужно подсчитать символы. Можно сделать по-другому, что будет проще для пользователей. Устанавливаем курсор в поле аргумента и просто кликаем по нужной области на листе. После этого её адрес появится в поле. Когда данные внесены, жмем на кнопку «OK».

Как видим, после этого результат расчета количества знаков выводится на экран.

Способ 2: подсчет символов в столбце

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

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

Формула копируется на весь диапазон. Результат сразу же виден на листе.

Урок: Как сделать автозаполнение в Excel

Способ 3: подсчет символов в нескольких ячейках с помощью автосуммы

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

1. Подсчитаем количество знаков для каждой отдельной ячейки, как это было описано в предыдущем варианте.
2. Выделяем диапазон, в котором указано количество символов, и жмем на кнопку «Сумма», расположенную во вкладке «Главная» в блоке настроек «Редактирование».

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

Урок: Как посчитать сумму в Экселе

Способ 4: подсчет символов в нескольких ячейках при помощи функции

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

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

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

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

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

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

Как вы можете видеть на скриншоте выше, столбец A содержит список ошибок. Ваша цель — получить описание ошибки, которое идет после последнего двоеточия. Дополнительная сложность заключается в том, что исходные значения могут содержать разное количество разделителей, например, A3 содержит 3 двоеточия, а A5 — только одно.

Ключом к поиску решения является определение позиции последнего разделителя (последнее вхождение двоеточия в этом примере). Для этого вам нужно будет выполнить несколько несложных операций:

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

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

1. Замените последний разделитель на какой-нибудь уникальный символ. Чтобы извлечь текст, который идет после последнего разделителя, нам нужно каким-то образом «пометить» это последнее вхождение разделителя. Для этого давайте заменим последнее вхождение двоеточия символом, который нигде не встречается в исходных значениях, например, знаком доллара ($).

Если вы знакомы с синтаксисом функции ПОДСТАВИТЬ, вы можете помнить, что у нее есть 4-й необязательный аргумент (номер вхождения), который позволяет заменять только конкретное появление указанного символа. И поскольку мы уже вычислили количество разделителей, просто впишите рассмотренное выше выражение в четвертый аргумент функции ПОДСТАВИТЬ:

Если вы поместите эту формулу в отдельную ячейку, она вернет:          ERROR: 432 $ Connection timed out

1. Определяем позицию последнего разделителя. В зависимости от того, на какой символ вы заменили последний разделитель, используйте ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра), чтобы определить позицию этого символа. Мы заменили последнее двоеточие на знак $, поэтому используем следующую формулу, чтобы узнать его местоположение:

ПОИСК(«$»; ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»)))))

В этом примере формула возвращает 10, что является позицией $ в измененном тексте.

1. Получаем подстроку справа от последнего разделителя. Теперь, когда вы знаете позицию последнего разделителя, все, что вам нужно сделать, это вычесть это число из общей длины строки и использовать ПРАВСИМВ, чтобы вернуть это количество символов из конца исходного текста:

ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(«$»; ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»)))))

Если вы работаете с большим набором данных, где разные ячейки могут содержать различные разделители, вы можете заключить приведенную выше формулу в функцию ЕСЛИОШИБКА, чтобы предотвратить возможные сообщения об ошибках:

Как показано на скриншоте ниже, формула работает отлично:

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

Дополнительная информация

Формула для подсчета количества вхождений текстовой строки в диапазоне

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”text”,””)))/LEN(“text”)

Где диапазон — диапазон ячеек, а “text” заменяется конкретной текстовой строкой, которую вы хотите посчитать.

Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.

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

Пример 1. Подсчет количества вхождений текстовой строки в диапазоне

Запустите Excel, а затем откройте новую книгу

Введите следующие сведения на Листе 1:

Значение ячейки A8 составляет 4, потому что текст “яблоко” появляется четыре раза в диапазоне.

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

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,”a”,””))

Где cell_refссылка на ячейку, и “а” заменяется символом, который вы хотите посчитать.

Эта формула не должна быть введена в качестве массивной формулы.

Пример 2. Подсчет количества появлений символа в одной ячейке.

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа “p” в A7. Введите следующую формулу в ячейку A9:

Значение ячейки A9 составляет 3, потому что символ “p” появляется три раза в A7.

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

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”a”,””)))

Где range — это диапазон нужной ячейки, а “а” заменяется символом, который вы хотите сосчитать.

Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.

Пример 3. Подсчет количества появлений символа в диапазоне

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа “p” в A2:A7. В ячейку А10 введите следующую формулу:

Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.

Значение ячейки A10 составляет 11, потому что символ “p” появляется 11 раз в A2:A7.

Формула для подсчета количества слов, разделенных символом в ячейке

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,””))+1)

Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.

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

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

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

Запустите Excel, а затем откройте новую книгу

Введите следующие сведения на Листе 1:

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

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

Считаем с учетом всех критериев (логика И).

Этот вариант является самым простым, поскольку функция СЧЕТЕСЛИМН предназначена для подсчета только тех ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что логическая функция И работает таким же образом.

а. Для каждого диапазона – свой критерий.

Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).

Задача может быть выполнена таким образом:

или

Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.

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

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

Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:

Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.

Использование упрощенного варианта с одним ограничением выбора, например =СЧЁТЕСЛИМН(B2:D11;0), даст другой результат – общее количество ячеек в B2: D11, содержащих ноль (в данном примере это 5).