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

Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если вы хотите посчитать точное количество повторяющихся записей на листе Excel, используйте один из следующих способов для подсчета дубликатов.

Подсчет количества каждого из дубликатов.

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

Чтобы узнать, сколько раз та или иная запись встречается в вашей рабочей таблице Excel, используйте простую формулу COUNTIF, где A2 - первый, а A8 - последний элемент списка:

=СЧЁТЕСЛИ($A$2:$A$17;A2)

Как показано на следующем снимке экрана, программа подсчитывает вхождения каждого элемента: «Fanta» встречается 2 раза, «Sprite» - 3 раза, и так далее.

Если вы хотите указать на 1- ^е , 2- ^е , 3- ^е и т. д. появление каждого элемента, используйте:

=СЧЁТЕСЛИ($A$2:$A2;A2)

Мы отметили на рисунке первое, второе и третье появление Sprite.

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

=СЧЁТЕСЛИМН($A$2:$A$17;A2;$B$2:$B$17;B2;$C$2:$C$17;C2)

На скриншоте мы отметили одинаковые строки.

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

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

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

=СЧЁТЕСЛИ(диапазон, "Дубликат")

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

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

=СЧЁТЕСЛИ(B2:B17;"Дубликат")

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

{=ЧСТРОК($A$2:$A$17)-СУММ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17;$A$2:$A$17)=1;1;0))}

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

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

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

=СУММПРОИЗВ(--(СЧЁТЕСЛИ(A2:A17;A2:A17)>1))

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

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;"Дубликат";"")

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

=СЧЁТЕСЛИ(B2:B17;"Дубликат")

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

{=СЧЁТЗ(A2:A17)-СУММ(1/СЧЁТЕСЛИ(A2:A17;A2:A17))}

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

{=СУММ(1/СЧЁТЕСЛИ(A2:A17;A2:A17))}

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

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

Количество совпадений по части ячейки.

Предположим, у нас в ячейке записано не только название товара, но и другая дополнительная информация: товарная группа, номер счёта, единицы измерения и т.п. Как в этом случае подсчитать число упоминаний определённого товара?

Будем проверять часть содержимого, используя функцию СЧЕТЕСЛИ и знаки подстановки.

Делаем это так:

=СЧЁТЕСЛИ($A$2:$A$17;"*"&C2&"*")

Ищем любое вхождение искомого слова при помощи знаков подстановки “*”.

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

 Случается, что список находится вовсе не в таблице, а в одной ячейке ($A$2):
Рассмотренные нами выше приемы здесь точно не сработают. Но в Excel хватает других возможностей, и все можно сделать так же достаточно просто:

=(ДЛСТР($D$1)-ДЛСТР(ПОДСТАВИТЬ($D$1;D3;"")))/ДЛСТР(D3)

При помощи ДЛСТР считаем количество символов в ячейке со списком товаров ($A$2)
Затем при помощи ПОДСТАВИТЬ заменяем в указанном тексте заданное слово на пустое «». По умолчанию заменяем все повторы. А фактически – удаляем их.

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

Результат вы видите на скриншоте выше.

Подсчет дубликатов строк.

Чтобы найти общее количество повторяющихся строк, вставьте функцию СЧЁТЕСЛИМН() вместо СЧЕТЕСЛИ() и укажите все столбцы, которые вы хотите проверить на наличие совпадений. Например, чтобы подсчитать повторяющиеся строки на основе столбцов A и B, введите следующую формулу массива в свой лист Excel:

{=ЧСТРОК($A$2:$A$17)-СУММ(ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$17;$A$2:$A$17;$B$2:$B$17;$B$2:$B$17)=1;1;0))}

Как видите, выполнить подсчет повторяющихся значений в таблицах Excel можно множеством различных способов. 

Рекомендуем также: