Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если вы хотите посчитать точное количество повторяющихся записей на листе 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 можно множеством различных способов.
Рекомендуем также:



здравствуйте, нашел еще в дополнение к вашей =СУММПРОИЗВ(--(СЧЁТЕСЛИ(A2:A17;A2:A17)>1)) эту:
=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0
чем они отличаются?
Ваша формула - это логическое выражение, которое определяет сам факт наличия дубликатов в диапазоне. Но она не считает их количество.