Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (66)
Разное (42)
Баги и глюки Excel (4)

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

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

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

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

Но если по каким-то причинам сводная не Ваш вариант - в 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;"<>")

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


Если необходимо подсчитать количество повторений на основании нескольких условий(значений), то начиная с 2007 Excel это легко можно сделать при помощи функции СЧЁТЕСЛИМН(COUNTIFS). Синтаксис функции почти такой же, как у СЧЁТЕСЛИ(COUNTIF), только условий и диапазонов больше:
=СЧЁТЕСЛИМН($A$2:$A$30;A2;$B$2:$B$30;B2)
предполагается, что условия записаны в столбце В

По сути идет просто перечисление:
=СЧЁТЕСЛИМН(Диапазон_условий1;Условие1; Диапазон_условий2;Условие2; Диапазон_условий3;Условие3; и т.д.)


Так же не могу не написать про небольшую особенность функции СЧЁТЕСЛИ(а так же СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН и им подобных) - данные функции всегда стремятся преобразовать все значения аргументов к типам(в отличии от той же ВПР, которая к типам относится очень бережно и ничего не преобразует). Что это значит. Если у нас в ячейке записано число 23 - оно будет воспринято как число. Если тоже число будет записано как текст - "23", то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и "23" у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках расположены некие номер счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
00034889913131323455
0034889913131323455
00034889913131323477

как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго - два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СЧЁТЕСЛИ после преобразования все три этих значения будет считать как число 34889913131323400 и если записать функцию так: =СЧЁТЕСЛИ($A$1:$A$3;A1), то она вернет значение 3. Особо обращаю внимание на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда подсчет будет некорректным.


Еще один вариант подсчета значений. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке($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. Что нам и требовалось.

Но тут есть и более каверзная ситуация - когда у нас диапазон ячеек, в каждой из которых наше слово может встречаться более одного раза. И подсчитать надо ВСЕ повторения. Диапазон для подсчета повторений у нас будет в ячейках A1:A10. Слово для подсчета повторений запишем в ячейку B1(там будет все тоже слово "банан"):
Подсчет внутри ячеек диапазона
Базируясь на формуле выше можно написать такую:
=СУММПРОИЗВ((ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;B1;"")))/ДЛСТР(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, 10 380 скачиваний)

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


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

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Обсуждение: 42 комментария
  1. Аниса:

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

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

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

  2. Максим Кузнецов:

    Спасибо! Очень помогло в работе!

  3. Pabokor:

    Здравствуйте! Спасибо за Вашу работу.
    Нахожу ответы на свои скромные запросы.
    Не получается справиться со следующей задачей.
    Есть массив дат (колонка с датами), который меняется (пополняется, уменьшается).
    Необходимо вывести из него данные в в две колонки (желательно на другой лист)
    "Дата" "Количество повторений"

    • Pabokor, создайте тему на форуме, приложите файл-пример с исходными данными и желаемым результатом. Тогда может и решение найдется.

  4. Евгений:

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

  5. Вадим:

    Добрый день. В таблице список из более 1500 ФИО. Нужно подсчитать сколько раз каждый человек указан в списке. Как это сделать автоматически?

  6. Нина К.:

    Здравствуйте!
    Имеется таблица. В левой графе названия образцов (группы), в шапке месяцы, проводились исследования (всего, не соответствуют нормам и т.п.). Хочу автоматически подсчитать общее количество образцов, сколько не соответствующих и т.п. по каждой группе на текущий момент. Какую формулу нужно использовать? Спасибо.

  7. Борис:

    Здравствуйте!
    Подскажите, пожалуйста, можно ли ограничить подсчет повторяющихся значений только тем, что отфильтровал автофильтр? Т.е. диапазон, по которому считает функция, должен зависеть от фильтров, выбранных в других столбцах.
    Спасибо!

  8. npopova:

    Огромное спасибо! Очень помоги

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти