Формула подсчета слов в ячейке excel

У Вас в основном в ячейках текст «1/1», «1/2», «1/3», «1/4»
а в ячейках N47 и N102 — числа (0,5 и 0,333333. ), которые выглядят как дробь

Предварительно установите формат ячеек в столбце N — текстовый
и введите в данные ячейки 1/2 и 1/3 соответственно

У Вас в основном в ячейках текст «1/1», «1/2», «1/3», «1/4»
а в ячейках N47 и N102 — числа (0,5 и 0,333333. ), которые выглядят как дробь

Предварительно установите формат ячеек в столбце N — текстовый
и введите в данные ячейки 1/2 и 1/3 соответственно MCH

У Вас в основном в ячейках текст «1/1», «1/2», «1/3», «1/4»
а в ячейках N47 и N102 — числа (0,5 и 0,333333. ), которые выглядят как дробь

Предварительно установите формат ячеек в столбце N — текстовый
и введите в данные ячейки 1/2 и 1/3 соответственно Автор — MCH
Дата добавления — 26.06.2012 в 17:06

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

В этой статье я хочу рассказать, как можно подсчитать количество повторений какого-либо значения в таблице или в ячейке. Начнем по порядку. Имеется таблица:

И необходимо подсчитать количество повторений каждого наименования:

Как ни странно, но сделать это весьма просто: в Excel имеется функция — СЧЁТЕСЛИ, при помощи которой все это сделать можно буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
Диапазон ( $A$2:$A$30 ) — указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий ( A2 ) — указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ( $A$2:$A$30 ;»Яблоко») . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия «*банан*» можно подсчитать количество ячеек, в которых встречается слово «банан» (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав «банан*» — значения, начинающиеся на «банан» (бананы, банановый сок, банановая роща и т.п.). «?» — заменяет лишь один символ, т.е. указав «бан?н» можно подсчитать строки и со значением «банан» и со значением «банон» и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ( $A$2:$A$30 ;»*») , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и ?) не получится применить к числовым значениям — исключительно к тексту. Т.е. если если указать в качестве критерия «12*», то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>12″)

Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;»<>«)

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

Но. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $D$1 ):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР( $D$1 )-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 )
ДЛСТР — подсчитывает количество символов в указанной ячейке/строке( $D$1 , D3 )
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) — заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан( D3 ) пошаговый разбор формулы:

• при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом( $D$1 ) =(170-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 ) ;
• при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом( $D$1 ) все значения Банан( D3 ) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР( D3 ) ;
• вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

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

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) — Len(Replace(sTxt, sCntWord, «»))) / Len(sCntWord) End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt — текст, в котором подсчитываем кол-во вхождения.
sCntWord — текст для подсчета. Может быть символом или словом.

Tips_All_Count_Duplicate.xls (39,0 KiB, 9 457 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Добрый день.
Можете ли вы мне помочь?

Нужно посчитать количество повторений по столбцу рейс только в одной строке.
Рейс. Объем. Колич
повтор
1аб. 2 2
1ав. 3. 1
1 аб. 0.3. 2 (2нужно посчитать как 0)
2аг. 9. 2
2аг. 3. 2 (2нужно посчитать как 0)
2ав. 0.4. 1

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

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

Оценка статьи:

Загрузка...