Skip to content
Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если вы хотите посчитать точное количество повторяющихся записей на листе 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 можно множеством различных способов.
Рекомендуем также:
Подсчет количества вхождений значения
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще…Меньше
Предположим, что нужно определить, сколько раз встречается конкретный текст или число в диапазоне ячеек. Например:
-
Если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза.
-
Если столбец содержит поговорки “Климов”, “Наворов”, “Наворов” и “Наворов”, то этот столбец встречается три раза.
Подсчитать количество вхождений значения можно несколькими способами.
Используйте функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз встречается определенное значение в диапазоне ячеек.
Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ.
Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.
Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:
СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
См. пример ниже.
Дополнительные сведения об использовании этой функции для подсчета вхождений в нескольких диапазонах и с несколькими условиями см. в статье Функция СЧЁТЕСЛИМН.
Предположим, вам нужно определить, сколько продавцов продало определенный товар в определенном регионе или сколько продаж было сделано конкретным продавцом. Функции ЕСЛИ и СЧЁТ можно использовать вместе. то есть сначала для проверки условия используется функция ЕСЛИ, а затем, только если функция ЕСЛИ имеет истинное положение, для подсчета ячеек используется функция СЧЁТ.
Примечания:
-
Формулы в этом примере должны быть введены как формулы массива.
-
Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
-
Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и хотите изменить формулу или создать похожую, нажмите F2,а затем нажмите CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
-
-
Чтобы эти примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.
Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ.
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
Примечания: Формулы, приведенные в этом примере, должны быть введены как формулы массива.
-
Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
-
Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и хотите изменить формулу или создать похожую, нажмите F2,а затем нажмите CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
Пример 1
Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для “Шашков” и одну для “Туманов” и отобразит 4.
Пример 2
Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.
Пример 3
Функция выше сообщает, что если в функции D2:D7 есть счета для записи “Иванов” на сумму менее 9 000 рублей, то функция СУММ должна отобразить сумму записей, в которых условие должно быть выполнены. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Вы можете использовать совокупные значения для отображения итогов и подсчета количества уникальных значений. Сводная таблица — это интерактивный способ быстрого суммирования больших объемов данных. Вы можете использовать ее для развертывания и свертывания уровней представления данных, чтобы получить точные сведения о результатах и детализировать итоговые данные по интересующим вопросам. Кроме того, можно перемещать строки в столбцы или столбцы в строки (“сводить” их) для просмотра количества вхождений значения в сводной таблице. Рассмотрим пример электронной таблицы “Продажи”, в которой можно подсчитать количество значений продаж для разделов “Гольф” и “Теннис” за конкретные кварталы.
-
Введите данные в электронную таблицу Excel.
-
Выделите диапазон A2:C8
-
Выберите Вставка > Сводная таблица.
-
В диалоговом окне “Создание сводной таблицы” установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК.
Пустая сводная таблица будет создана на новом листе.
-
В области “Поля сводной таблицы” выполните одно из указанных ниже действий.
-
Перетащите элемент Спорт в область Строки.
-
Перетащите элемент Квартал в область Столбцы.
-
Перетащите элемент Продажи в область Значения.
-
Повторите третье действие.
Имя поля Сумма_продаж_2 отобразится и в области “Сводная таблица”, и в области “Значения”.
На этом этапе область “Поля сводной таблицы” будет выглядеть так:
-
В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
-
В диалоговом окне Параметры поля значений выполните указанные ниже действия.
-
На вкладке Операция выберите пункт Количество.
-
В поле Пользовательское имя измените имя на Количество.
-
Нажмите кнопку ОК.
-
Сводная таблица отобразит количество записей для разделов “Гольф” и “Теннис” за кварталы 3 и 4, а также показатели продаж.
-
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Сочетания клавиш и горячие клавиши в Excel
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Есть полтора миллиона записей, из них нужно подсчитать количество тех, что повторяются
20 октября 2021 · 486,0 K
Есть разные способы, начиная от проверочного столбца и до формул, работающих напрямую с данными.
Допустим у меня 1118 позиций. Надо узнать, сколько из них повторяющихся.
вариант 1. Все данные лежат в колонке А, начиная с первой строки. Тогда в ячейке В1 пишем
=ЕСЛИ(СЧЁТЕСЛИ($A$1:A1;A1)=1;1;0)
протягиваем формулу вниз. Сумма в колонке В и будет количеством повторов.
вариант 2. Те же исходные данные. В В1 пишем единицу, а в В2 – формулу.
=ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A3)=0;B2+1;B2)
Протягиваем вниз и видим внизу количество повторяющихся значений
Вариант 3. вводим формулу массива (сочетание Ctrl Shift Enter) для ввода
{=СУММ(1/СЧЁТЕСЛИ(A2:A1119;A2:A1119))}
2,7 K
Комментировать ответ…Комментировать…
Высокой страсти не имея
Для звуков жизни не щадить,
Не мог он ямба от хорея,
Как мы ни… · 20 окт 2021
Можно попробовать с помощью встроенного фильтра. Во вкладке данные есть символ в виде лейки (воронки). Выделяете столбец с данными, затем щелкаете по значку лейки, появляется маленький квадратик с треугольничком внутри. Щелкаете по нему, выскакивает меню маленькое. В нем выбираете текстовые фильтры. В них выбираете значение равно, Вылезает пользовательский автофильтр… Читать далее
1,8 K
один из вариантов расчета, почему и нет?
Комментировать ответ…Комментировать…
Есть функция =unique на русский УНИК( точно не помню). Выбирает уникальные значения из диапазона и выводит их списком. Ну а как дальше посчитать думайте. Только учтите что в тексте могут быть описки, которые тоже посчитает уникальным текстом
1,5 K
Да, такая функция присуитствует в Excel, Но! Только в версии 365, 2019 или новее
Комментировать ответ…Комментировать…
По образованию физик, работаю программистом · 20 окт 2021
Данные нужно отсортировать по изучаемому значению и добавить колонку, которая будет сравнивать значение с предыдущим. Если значение не равно предыдущему, то колонка должа увеличиваться на единицу, а если равно, то не должна. В самом низу этой колонки получится количество уникальных значений в таблице. Теперь, возможно, вам захочется подсчитать количества повторов в… Читать далее
108,0 K
А не смутило, что данных полтора миллиона строк? )) В то время, как в эксель чуть более миллиона строк..
Комментировать ответ…Комментировать…
Допустим, данные в столбце A. Добавьте в столбец B формулу вида:
=СЧЁТЕСЛИ(A:A;A2)
Потом в любом удобном месте можно посчитать количество повторяющихся формулой вида:
=СЧЁТЕСЛИ(B:B;”>1″)
Можно считать не во всем столбце, а только в нужном диапазоне, но общий принцип такой. Читать далее
634
Комментировать ответ…Комментировать…
Высокой страсти не имея
Для звуков жизни не щадить,
Не мог он ямба от хорея,
Как мы ни… · 20 окт 2021
Можно попробовать с помощью встроенного фильтра. Во вкладке данные есть символ в виде лейки (воронки). Выделяете столбец с данными, затем щелкаете по значку лейки, появляется маленький квадратик с треугольничком внутри. Щелкаете по нему, выскакивает меню маленькое. В нем выбираете текстовые фильтры. В них выбираете значение равно, Вылезает пользовательский автофильтр. 4… Читать далее
742
Комментировать ответ…Комментировать…
стример, девственник, озабоченный программист · 3 мар
Для подсчета количества повторяющихся значений в Excel можно использовать функцию COUNTIF или PivotTable.
1. Использование функции COUNTIF:
– Выберите столбец с данными, в котором необходимо подсчитать повторяющиеся значения
– Введите формулу =COUNTIF(A:A, A1) в первую ячейку рядом с первым значением и нажмите Enter
– Скопируйте формулу вниз по столбцу
– Excel… Читать далее
284
При ответе на этот вопрос лучше применить русскоязычное название функции =СЧЕТЕСЛИ() и инструмент “сводные… Читать дальше
Комментировать ответ…Комментировать…
Создать отдельный столбец на листе скопировав значения, и с помощью функции удалить дубликаты, сделать уникальные значение, а далее с помочью функции ЧАСТОТА, ищем по столбцу уникальных значений, с списке с полуторамиллионном значений, далее на таблице ставим фильтр и убираем нулевые значения
Комментировать ответ…Комментировать…
Функция “счетесли” думаю идеально подойдет для этих целей. Рекомендую почитать справочник со списком функций, много интересного узнаете для себя.
521
Комментировать ответ…Комментировать…
О сообществе
Добрый день!
Постепенно мы переходим к функциям Excel. Они существенно экономят наше время. Написав один раз необходимую функцию, мы будем получать требуемый результат всегда, когда нам нужно. Скажем так, исключаем рутину. Пускай роботы делают за нас скучную работу 🙂
Задача звучит так: необходимо посчитать количество одинаковых значений в столбце MS Excel.
1. Для примера, создадим некое количество строк в разными значениями:
Как видите, значения расставлены в случайном порядке. Всего их 31, в Вашем же случае может быть сколько угодно.
2. Выделяем любую пустую ячейку. В моем случае это B1. Чуть выше находим кнопку с функциями и нажимаем на нее (цифра 2 на картинке ниже):
Появляется окно “Вставка функции“. Выбираем категорию “Статистические” (цифра 3) и листаем список вниз до функции “СчетЕсли” (цифра 4). Нажимаем “ОК“.
3. Открывается окно “Аргументы функции“. Здесь необходимо указать диапазон тех значений, по которым будет производиться поиск:
Как это делается: либо пишем вручную первую и последнюю ячейку (A1:A31), либо кликаем мышкой на ячейку A1 и не отпуская доводим курсор до A31, затем отпускаем (текст появится в поле “Диапазон“).
4. Дальше, необходимо заполнить поле “Критерий” – это то, что мы будем искать. Например, я указал адрес ячейки (A1):
Программа сама написала справа значение ячейки A1 = “белый“. Таким образом, она будет искать все совпадения со словом “белый“. Обратите внимание, чуть ниже написано количество совпадений = 3.
Учтите, что можно в поле “Критерий” написать само слово для поиска:
Теперь, значений стало пять. Нажимаем кнопку “ОК” и смотрим на результат:
Немного поиграем с этой функцией.
1. Чтобы не изменить диапазон значений, перед каждой буквой и цифрой поставим знак доллара:
Слово “фиолетовый” я изменил на ячейку A1, чтобы программа сама искала значение в указанной ячейке. Здесь очень важно понимать, почему знак доллара не ставится в “критерии“. Если мы его поставим, то программа не будет менять значение поля A1. Так что делаем так, как показано на рисунке выше.
2. Ставим курсор на правый нижний угол ячейки B1:
Нажимаем левую кнопку мыши и ведем ее до ячейки B31, затем отпускаем. Смотрим на результат:
Теперь, напротив каждого цвета написано количество его повторений в диапазоне от A1 до A31.
На этом все! Задача выполнена!
Как всегда, надеюсь, что был Вам полезен! Всем добра!
17 авг. 2022 г.
читать 2 мин
Часто вам может понадобиться подсчитать количество повторяющихся значений в столбце Excel.
К счастью, это легко сделать, и следующие примеры демонстрируют, как это сделать.
Пример 1. Подсчет дубликатов для каждого значения
Мы можем использовать следующий синтаксис для подсчета количества дубликатов для каждого значения в столбце в Excel:
=COUNTIF( $A$2:$A$14 , A2 )
Например, на следующем снимке экрана показано, как использовать эту формулу для подсчета количества дубликатов в списке имен команд:
Из вывода мы видим:
- Название команды «Мавс» встречается 2 раза.
- Название команды «Ястребы» встречается 3 раза.
- Название команды «Нетс» встречается 4 раза.
И так далее.
Пример 2. Подсчет неповторяющихся значений
Мы можем использовать следующий синтаксис для подсчета общего количества неповторяющихся значений в столбце:
=SUMPRODUCT(( A2:A14 <>"")/COUNTIF( A2:A14 , A2:A14 &""))
Например, на следующем снимке экрана показано, как использовать эту формулу для подсчета количества неповторяющихся имен в списке команд:
Из вывода мы видим, что существует 6 уникальных названий команд.
Пример 3. Список неповторяющихся значений
Мы можем использовать следующий синтаксис, чтобы перечислить все неповторяющиеся значения в столбце:
=UNIQUE( A2:A14 )
На следующем снимке экрана показано, как использовать эту формулу для перечисления всех уникальных названий команд в столбце:
Мы видим, что есть 6 уникальных названий команд, и каждое из них указано в столбце C.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как рассчитать относительную частоту в Excel
Как подсчитать частоту текста в Excel
Как рассчитать кумулятивную частоту в Excel
Написано
Замечательно! Вы успешно подписались.
Добро пожаловать обратно! Вы успешно вошли
Вы успешно подписались на кодкамп.
Срок действия вашей ссылки истек.
Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.
Успех! Ваша платежная информация обновлена.
Ваша платежная информация не была обновлена.