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

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

Что будем считать
• Количество повторений каждой ячейки
• Особенность при работе с функцией СЧЁТЕСЛИ
• Количество повторений значения внутри ячейки

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

Самый простой способ - создать сводную таблицу, поместив в область строк и в область значений данные столбца А. Сводная сделает все сама. Подробнее про создание и использование сводных таблиц можно узнать в этой статье с видеоуроком: Общие сведения о сводных таблицах

Но если по каким-то причинам сводная не Ваш вариант - в Excel имеется функция СЧЁТЕСЛИ(COUNTIF), при помощи которой все это можно сделать тоже буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ($A$2:$A$30;A2)
=COUNTIF($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) =(170-ДЛСТР(ПОДСТАВИТЬ($D$1;D3;"")))/ДЛСТР(D3);
• при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом($D$1) все значения Банан(D3) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР(D3);
• вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5.

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

Но тут есть и более каверзная ситуация - когда у нас диапазон ячеек, в каждой из которых наше слово может встречаться более одного раза. И подсчитать надо ВСЕ повторения. Диапазон для подсчета повторений у нас будет в ячейках A1:A10. Слово для подсчета повторений запишем в ячейку B1(там будет все тоже слово "банан"):

Базируясь на формуле выше можно написать такую:
=СУММПРОИЗВ((ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;B1;"")))/ДЛСТР(B1))
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1))

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

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 - текст для подсчета. Может быть символом или словом.

Скачать пример

  Пример Подсчета повторений.xls (70,5 KiB, 13 761 скачиваний)

Так же см.:
Что такое формула?
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

    Видеоуроки

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