Подсчет повторяющихся значений в Excel
Если вы хотите рассчитать точное количество повторяющихся записей в таблице Excel, используйте одну из следующие методы для подсчета дубликатов.
Подсчет количества каждого из дубликатов.
Например, если у вас есть столбец с названиями продуктов, вам часто может потребоваться узнать, сколько дубликатов существует для каждого из них.
Чтобы узнать, сколько раз запись появляется на вашем листе Excel, используйте простую формулу СЧЁТЕСЛИ, где A2 — это первый элемент, а A8 — последний элемент в списке:
Как показано на следующем снимке экрана, программа считает появления каждого элемента: «Фанта» встречается 2 раза, «Спрайт» встречается 3 раза и так далее.= СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; A2)
Если вы хотите указать 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 — использовать более сложную формулу массива. Преимущество такого подхода в том, что он не требует опорной стойки:
Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ввод.{= СТРОКИ ($ A $ 2: $ A $ 17) -SUM (IF (COUNTIF ($ A $ 2: $ A $ 17; $ A $ 2: $ A $ 17) = 1; 1; 0))}
Также обратите внимание, что он учитывает все повторяющиеся записи, включая первые вхождения:
Можно обойтись без формулы майисва:
Он работает как для текста, так и для чисел, а пустые ячейки игнорируются, что также очень полезно.= СУММПРОИЗВ (- (СЧЁТЕСЛИ (A2: A17; A2: A17)> 1))
Теперь посчитаем количество дубликатов без учета их первого появления в таблице.
Здесь также есть два пути. Первый использует вспомогательную колонку B.
В графе Б ставим соответствующие знаки, как мы это делали не раз.
Далее определяем количество ячеек, содержимое которых не определяется впервые:= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)> 1, «Дублировать»;»»)
или матричная формула= СЧЁТЕСЛИ (B2: B17; «Дублировать»)
Что ж, можно пойти и наоборот. Давайте посчитаем количество уникальных записей таким простым и элегантным способом:{= СЧЁТ (A2: A17) -SUM (1 / СЧЁТЕСЛИ (A2: A17; A2: A17))}
Эта формула массива может оказаться полезной для подсчета уникальных значений.{= СУММ (1 / СЧЁТЕСЛИ (A2: A17; A2: A17))}
Что ж, теперь школьная проблема: если у нас есть только 16 слов (вы можете использовать функцию COUNT) и 10 из них уникальны, сколько будет неуникальных? Правильно — 6!
Количество совпадений по части ячейки.
Допустим, у нас в ячейке есть не только название товара, но и другая дополнительная информация: группа товаров, номер счета, единица измерения и т.д. Как в этом случае рассчитывается количество упоминаний того или иного товара?
Мы проверяем часть содержимого с помощью функции СЧЁТЕСЛИ и подстановочных знаков.
Делаем это так:
Мы ищем любое вхождение нужного слова, используя подстановочный знак “*”.= СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; «*» & C2&»*»)
Как посчитать количество дубликатов внутри ячейки.
Бывает, что список находится вовсе не в таблице, а в ячейке ($ A $ 2):
Приемы, которые мы обсуждали выше, здесь определенно не сработают. Но в Excel нет других функций, и все можно сделать так же легко:
С помощью DLSTR подсчитываем количество символов в ячейке со списком товаров ($ A $ 2)= (DLSTR ($ D $ 1) -LSTR (ПОДСТАВИТЬ ($ D $ 1; D3; «»))) / DLSTR (D3)
Затем, используя ЗАМЕНА, замените указанное слово в указанном тексте пробелом «». Заменить все повторы по умолчанию. Фактически мы их устраняем.
Теперь наша задача — узнать, сколько слов мы удалили. С помощью DLSTR узнаем количество символов, оставшихся в списке после этой замены. Вычтите количество оставшихся символов из начального количества символов, т.е узнайте, сколько символов было удалено. Разделите результат на количество букв в поисковом слове.
Результат вы можете увидеть на скриншоте выше.
Подсчет дубликатов строк.
Чтобы найти общее количество повторяющихся строк, введите функцию СЧЁТЕСЛИ () вместо СЧЁТЕСЛИ () и укажите все столбцы, которые вы хотите сопоставить. Например, чтобы подсчитать повторяющиеся строки на основе столбцов A и B, введите следующую матричную формулу в лист Excel:
Как видите, есть много разных способов подсчета повторяющихся значений в таблицах Excel.{= СТРОКИ ($ A $ 2: $ A $ 17) -SUM (IF (COUNTIF ($ A $ 2: $ A $ 17; $ A $ 2: $ A $ 17; $ B $ 2: $ B $ 17 ; $ B $ 2: $ B $ 17) = 1; 1; 0))}