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

Подсчитать числа, которые больше нуля:=СЧЁТЕСЛИ($A$2:$A$30;»>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ($A$2:$A$30;»»)

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

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

По сути идет просто перечисление:

(а так же

,

,

и им подобных) — данные функции всегда стремятся преобразовать все значения аргументов к типам(в отличии от той же

, которая к типам относится очень бережно и ничего не преобразует). Что это значит. Если у нас в ячейке записано число

— оно будет воспринято как число. Если тоже число будет записано как текст —

, то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и «23» у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках расположены некие номера счетов, длина которых более 15-ти символов и могут иметь ведущие нули:

как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго — два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СЧЁТЕСЛИ после преобразования все три этих значения будет считать как число

и если записать функцию так:

, то она вернет значение 3. Особо обращаю внимание на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда подсчет будет некорректным.

Еще один вариант подсчета значений. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке($D$1):

• при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом($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, 13 020 скачиваний)

Так же см.:

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

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

Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

Подсчет количества уникальных значений среди повторяющихся

Excel

Формулы и функции

Ошибки

Ошибки

Подсчет количества уникальных значений среди повторяющихся

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще.

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

• Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.

• Значения «Брэнли», «Дойл», «Дойл», «Дойл» — это два уникальных значения: «Андрей» и «Дойл».

Существует несколько способов подсчета уникальных значений среди дубликатов.

В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции

org/ItemList»>

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

   Убедитесь, что диапазон ячеек имеет заголовок столбца.

2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

   Появится диалоговое окно Расширенный фильтр.

3. Нажмите кнопку Копировать в другое место.

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

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

5. Выберите поле Уникальные записи и нажмите кнопку ОК.

   Уникальные значения из выбранного диапазона копируется в новое место, начиная с ячейки, указанной в поле Копировать в.

6. В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

• org/ListItem»>

  Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

• Сложить итог с помощью функции СУММ.

• Подсчет количества уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству его вхождений. Для каждого вхождения с одинаковым значением после первого функция возвращает ноль.

• Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.

• Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

Примечания: 

• Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

• Чтобы оценить функцию пошаговую проверку, выйдите из ячейки, содержащей формулу, а затем на вкладке Формулы в группе Зависимости формул нажмите кнопку Вы оцениваете формулу.

• Функция ЧАСТОТА вычисляет частоту ветвей значений в диапазоне значений и возвращает вертикальный массив чисел. Например, с помощью частоты можно подсчитать количество результатов тестирования, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.

• Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.

• Функция LEN возвращает количество символов в текстовой строке.

• Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.

• Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Фильтр уникальных значений или удаление повторяющихся значений

Как посчитать количество одинаковых значений в excel

Как посчитать количество одинаковых значений в excel

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

Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.

Значения «Брэнли», «Дойл», «Дойл», «Дойл» — это два уникальных значения: «Андрей» и «Дойл».

Существует несколько способов подсчета уникальных значений среди дубликатов.

В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

Убедитесь, что диапазон ячеек имеет заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Появится диалоговое окно Расширенный фильтр.

Нажмите кнопку Копировать в другое место.

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

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

Выберите поле Уникальные записи и нажмите кнопку ОК.

Уникальные значения из выбранного диапазона копируется в новое место, начиная с ячейки, указанной в поле Копировать в.

В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Сложить итог с помощью функции СУММ.

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

Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.

Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Чтобы оценить функцию пошаговую проверку, выйдите из ячейки, содержащей формулу, а затем на вкладке Формулы в группе Зависимости формул нажмите кнопку Вы оцениваете формулу.

Функция ЧАСТОТА вычисляет частоту ветвей значений в диапазоне значений и возвращает вертикальный массив чисел. Например, с помощью частоты можно подсчитать количество результатов тестирования, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.

Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.

Функция LEN возвращает количество символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.

Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Подсчет повторяющихся значений (Дубликатов) в EXCEL

Если исходный список содержит: < 1 , 1 , 2 , 3, 1 , 2 >. Тогда количество значений, которые повторяются будет равно 5, т.е. <1, 1, 2, 1, 2>(значения в исходном массиве выделены жирным ).

Если исходный список значений находится в диапазоне А7:А16 (см. файл примера ), то число повторяющихся значений можно вычислить с помощью формулы =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A7:A16;A7:A16)>1))

Формула хороша тем, что производит подсчет и текстовых и числовых значений, кроме того, она игнорирует пустые ячейки.

Повторяющиеся значения на рисунке выделены с помощью Условного форматирования (см. статью Выделение повторяющихся значений в MS EXCEL ).

СОВЕТ: Решение обратной задачи по подсчету неповторяющихся значений можно найти здесь: Подсчет неповторяющихся значений . Вообще, если от общего количества значений отнять число повторяющихся, то получим количество неповторяющихся значений.

Как подсчитывать повторения в Excel?

Добрый день, уважаемый читатель!

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

Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:

1. С помощью функции СЧЁТЕСЛИ;
2. С помощью функции СЧЁТЕСЛИМН;
3. С помощью функции ДЛСТР;
4. С помощью функций VBA.

В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать функцию СЧЁТЕСЛИ и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:

=СЧЁТЕСЛИ($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11, указываем тот диапазон ячеек, в котором и будет производиться подсчёт повторяющихся данных. Важно! Указывать случайный диапазон данных недопустимо. Его особенностью является то, что он может быть только диапазоном ячеек или ссылкой на определённую ячейку.

Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.

Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*», применять их возможно только при работе с текстовыми символами. Знак «*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.

Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «<», «<>» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «<>».

Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.

В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:

=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14) Замечу, что орфография функции абсолютно аналогична предыдущей функции СЧЁТЕСЛИ, разница состоит только в их количестве. В нашем примере их два, но функция умеет работать и со 127 диапазонами.

Теперь давайте рассмотрим ситуацию, когда не всё так просто и упорядоченно, когда информация сбита в одну ячейку, к примеру, «Нагаев Гаврош Каропачёв Козубенко Нагаев Гаврош Козубенко Нагаев Нагаев». В этом случае статистические функции нам не помогут, необходимо считать символы и сверять повторы значений с указанным эталоном. Для этих целей есть много иных полезных функций, используя которые это можно провернуть достаточно просто:

=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;»»)))/ДЛСТР(B5) Итак, используя функцию ДЛСТР, мы считаем, сколько же символов содержится в ячейке «$B$2» и «B5», результат будет «71». А потом с помощью функции ПОДСТАВИТЬ производим замену текущего значения на «пусто», получаем результат «47». Следующим действием отнимаем от общего количества символов наш остаток «71-47=24» и делим на количество символов в одном значении «24/6=4», как результат получаем сколько раз в строке, встречается необходимый результат… Ответ: 4. (Это результат рассматривая только первую строку поиска).

Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.

Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module». В созданное окно модуля вы вставляете код функции:

Посчитать количество значений в ячейке Excel — Как посчитать количество заполненных ячеек в Экселе

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

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

1. С помощью функции СЧЁТЕСЛИ;
2. С помощью функции СЧЁТЕСЛИМН;
3. С помощью функции ДЛСТР;
4. С помощью функций VBA.

В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать функцию СЧЁТЕСЛИ и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:

=СЧЁТЕСЛИ($B$2:$B$11;B15)

Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.

Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*„, применять их возможно только при работе с текстовыми символами. Знак “*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.

Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «<», «<>» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «<>».

Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.

В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:

=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14)

Теперь давайте рассмотрим ситуацию, когда не всё так просто и упорядоченно, когда информация сбита в одну ячейку, к примеру, «Нагаев Гаврош Каропачёв Козубенко Нагаев Гаврош Козубенко Нагаев Нагаев». В этом случае статистические функции нам не помогут, необходимо считать символы и сверять повторы значений с указанным эталоном. Для этих целей есть много иных полезных функций, используя которые это можно провернуть достаточно просто:

=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;«»)))/ДЛСТР(B5)

Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.

Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module».

После всего этого вызываете «Диспетчер функций» в панели управления или с помощью горячих клавиш Ctrl+F3 и в категории «Определённые пользователем» у вас появляется новая необходимая функция.

=GetRepeat ($B$2;B8), где:

Подсчет количества уникальных значений среди повторяющихся.

Добрый день, уважаемый читатель!

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

Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:

В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:

=СЧЁТЕСЛИ($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11, указываем тот , в котором и будет производиться подсчёт повторяющихся данных. Важно! Указывать случайный диапазон данных недопустимо. Его особенностью является то, что он может быть только диапазоном ячеек или ссылкой на определённую ячейку.

Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.

Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*», применять их возможно только при работе с символами. Знак «*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.

Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «».

Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.

В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:

=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14)
Замечу, что орфография функции абсолютно аналогична предыдущей функции СЧЁТЕСЛИ, разница состоит только в их количестве. В нашем примере их два, но функция умеет работать и со 127 диапазонами.

Теперь давайте рассмотрим ситуацию, когда не всё так просто и упорядоченно, когда информация сбита в одну ячейку, к примеру, «Нагаев Гаврош Каропачёв Козубенко Нагаев Гаврош Козубенко Нагаев Нагаев» . В этом случае статистические функции нам не помогут, необходимо считать символы и сверять повторы значений с указанным эталоном. Для этих целей есть много иных полезных функций, используя которые это можно провернуть достаточно просто:

=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;»»)))/ДЛСТР(B5)
Итак, используя , мы считаем, сколько же символов содержится в ячейке «$B$2» и «B5», результат будет «71». А потом с помощью производим замену текущего значения на «пусто», получаем результат «47». Следующим действием отнимаем от общего количества символов наш остаток «71-47=24» и делим на количество символов в одном значении «24/6=4», как результат получаем сколько раз в строке, встречается необходимый результат… Ответ: 4. (Это результат рассматривая только первую строку поиска).

Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.

Для начала вам нужно запустить VBA и вставить новый модуль с помощью команд «Insert» — «Module» .
В созданное окно модуля вы вставляете код функции:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) — Len(Replace(sTxt, sCntWord, «»))) / Len(sCntWord) End Function

После всего этого вызываете «Диспетчер функций» в панели управления или с помощью Ctrl+F3 и в категории «Определённые пользователем» у вас появляется новая необходимая функция.
Используем функцию на ячейке стандартным образом с помощью формулы:

=GetRepeat ($B$2;B8) , где:

А на этом у меня всё! Был очень рад помочь и поделится информацией возможностью подсчитывать повторения в Excel. Если есть чем дополнить статью, напишите это в комментариях. Жду ваши лайки, это лучший стимул видеть пользу моих статей!

Каждый наш день — это счет в банке, а деньги на нем — это наше время. Здесь нет бедных и богатых, у каждого есть по 24 часа.
Кристофер Райс

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

Значения 5, 6, 7 и 6 выводятся в виде трех уникальных значений: 5, 6 и 7.

Значения «Кирилл», «Сергей», «Сергей», «Сергей», результат — это два уникальных значения — «Кирилл» и «Сергей».

Существует несколько способов подсчета уникальных значений среди повторяющихся.

Подсчет количества уникальных значений с помощью фильтра

С помощью диалогового окна » Расширенный фильтр » можно извлекать уникальные значения из столбца данных и вставлять их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

Подсчет количества уникальных значений с помощью функций

Для выполнения этой задачи используйте сочетание функций Если , сумм , Частота , ПОИСКПОЗ и ДЛСТР .

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ .

Добавьте итог с помощью функции сумм .

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

Возвращает расположение текстового значения в диапазоне с помощью функции ПОИСКПОЗ . Возвращаемое значение затем используется в качестве аргумента функции FREQUENCY , чтобы можно было оценивать соответствующие текстовые значения.

Поиск пустых ячеек с помощью функции Len . Длина пустых ячеек равна 0.

Примечания:

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Office 365 , можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД , чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры .

Чтобы просмотреть Пошаговое вычисление функции, выделите ячейку с формулой, а затем на вкладке формулы в группе Зависимости формул нажмите кнопку Вычисление формулы .

Описание функций

Функция Частота Вычисляет частоту возникновения значений в диапазоне значений и возвращает вертикальный массив чисел. Например, используйте функцию Частота для подсчета количества результатов теста, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.

Функция ПОИСКПОЗ осуществляет поиск определенного элемента в диапазоне ячеек и возвращает относительное расположение этого элемента в диапазоне. Например, если в диапазоне a1: A3 содержатся значения 5, 25 и 38, функция формула = Match (25; a1: A3; 0) возвращает число 2, так как значение 25 является вторым элементом диапазона.

Функция ДЛСТР возвращает число символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, функция сумм (a1: A5) складывает все числа, содержащиеся в ячейкАх от a1 до A5.

Функция Если возвращает одно значение, если указанное условие имеет значение true, и другое, если условие имеет значение false.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

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

Способ 1: указатель в строке состояния

Самый простой способ решить поставленную задачу в выделенном диапазоне – это посмотреть количество в строке состояния. Для этого просто выделяем нужный диапазон. При этом важно учесть, что система считает каждую ячейку с данными за отдельную единицу. Поэтому, чтобы не произошло двойного подсчета, так как нам нужно узнать количество именно строк, выделяем только один столбец в исследуемой области. В строке состояния после слова «Количество» слева от кнопок переключения режимов отображения появится указание фактического количества заполненных элементов в выделенном диапазоне.

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

Но бывают и ситуации, когда вы выделяете заполненные ячейки в строках, а отображение количества на панели состояния так и не появляется. Это означает, что данная функция просто отключена. Для её включения кликаем правой кнопкой мыши по панели состояния и в появившемся меню устанавливаем галочку напротив значения «Количество» . Теперь численность выделенных строк будет отображаться.

Способ 2: использование функции

Но, вышеуказанный способ не позволяет зафиксировать результаты подсчета в конкретной области на листе. К тому же, он предоставляет возможность посчитать только те строки, в которых присутствуют значения, а в некоторых случаях нужно произвести подсчет всех элементов в совокупности, включая и пустые. В этом случае на помощь придет функция ЧСТРОК . Её синтаксис выглядит следующим образом:

ЧСТРОК(массив)

Её можно вбить в любую пустую ячейку на листе, а в качестве аргумента «Массив» подставить координаты диапазона, в котором нужно произвести подсчет.

Для вывода результата на экран достаточно будет нажать кнопку Enter .

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

Пользователям, у которых небольшой опыт работы с формулами в Экселе, проще работать с данным оператором через Мастер функций .

Способ 3: применение фильтра и условного форматирования

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

SEO-анализ текста от сайт — это уникальный сервис, не имеющий аналогов. Возможность подсветки «воды», заспамленности и ключей в тексте позволяет сделать анализ текста интерактивным и легким для восприятия.

SEO-анализ текста включает в себя:

С помощью данного онлайн-сервиса можно определить число слов в тексте, а также количество символов с пробелами и без них.

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

Данный параметр отображает процент наличия в тексте стоп-слов, фразеологизмов, а также словесных оборотов, фраз, соединительных слов, являющихся не значимыми и не несущими смысловой нагрузки. Небольшое содержание «воды» в тексте является естественным показателем, при этом:

• до 15% — естественное содержание «воды» в тексте;
• от 15% до 30% — превышенное содержание «воды» в тексте;
• от 30% — высокое содержание «воды» в тексте.

Процент заспамленности текста отражает количество поисковых ключевых слов в тексте. Чем больше в тексте ключевых слов, тем выше его заспамленность:

• до 30% — отсутствие или естественное содержание ключевых слов в тексте;
• от 30% до 60% — SEO-оптимизированный текст. В большинстве случаев поисковые системы считают данный текст релевантным ключевым словам, которые указаны в тексте.
• от 60% — сильно оптимизированный или заспамленный ключевыми словами текст.

Данный параметр показывает количество слов, состоящих из букв различных алфавитов. Часто это буквы русского и английского языка, например, слово «стол», где «о» — буква английского алфавита. Некоторые копирайтеры заменяют в русских словах часть букв на английские, чтобы обманным путем повысить уникальность текста..

Пример 1: данные из панели состояния

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

Бывают более сложные ситуации. Например, если столбцы заполнены не полностью, т.е. в столбце есть пустые позиции. В случае выбора исключительно столбца, пустые строки подсчитаны не будут. Рассмотрим пример ниже. Пустым ячейкам из первого столбца соответствуют значения ячеек второго. Выберем полностью первый столбец и при нажатой клавише «Ctrl» нажимаем по соответствующим ячейкам со значениями из второго столбца. В нижней части окна отобразиться все количество строк выбранного диапазона, где ячейки не имеют значений.

Если вы сделали все, как описано выше, а количество не отображается, то это значит, что необходимо включить соответствующую опцию. Для этого выполняем клик правой кнопкой мыши по панели состояния и выбираем «Количество».

Пример 2: вычисление с помощью формул

Рассмотренный выше пример не дает возможности сохранить результаты подсчета и отображать их в ячейке на листе. Более того, часто необходимо учитывать и пустые позиции. Для этого удобно пользоваться формулой ЧСТРОК.

Формула имеет вид: =ЧСТРОК(массив значений).

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

Затем нажать Enter.

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

Для некоторых пользователей будет удобнее пользоваться этой формулой через Конструктор формул.

1) Устанавливаем курсор на позицию, в которую будем вставлять формулу, т. е. туда где будет выводиться результат и нажимаем на иконку «Вставить формулу».

2) Откроется окно Конструктора формул. В значении «Категория» установите «Ссылки и массивы», также можно выбрать «Полный алфавитный перечень». Находим функцию «ЧСТРОК» и нажимаем «ОК»

3) В окне выбора аргументов формулы установите курсор на после массива и выделите область с нужным диапазоном значений. После появления ячеек диапазона в поле аргументов функции нажмите «ОК»

4) После выполнения, приведенных выше действий в указанной позиции Excel автоматически вычислит количество строк в диапазоне. Вычисления будут выполняться до тех пор пока вы сами не уберете формулу из ячейки.

Пример 3: использование форматирования и фильтрации

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

1) Выделим диапазон значений, для которого необходимо выполнить соответствие условиям.

2) Перейдите на главную панель и выберите «Стили» далее нажимаем на пиктограмму «Условное форматирование». В условном форматировании выберите «Правила выделения ячеек» и для нашего случая выбираем правило «Большее…»

3) Появляется диалог задания условий. Слева указывается значение больше которого, ячейки окрасятся заданным цветом. Справа части задается цвет. После выбора нажимаем «ОК»

4) Как видно ниже, позиции, которые соответствуют условиям окрашены заданным цветом. Находясь на главной панели выделите весь диапазон и нажимаем пункт меню «Сортировка и фильтр» и выбираем «Фильтр»

Всё о работе с дубликатами в Excel

Очень часто при работе c таблицами Excel возникают ситуации, в которых необходимо сравнить несколько списков, найти в них повторяющиеся значения и что-то с ними сделать.  Оптимальный способ поиска и обработки дубликатов должен быть выбран в зависимости от типа исходных данных и желаемого результата. Цель данной статьи — разобрать все возможные варианты обработки дубликатов в Excel в одной статье для того, чтобы читатель мог выбрать оптимальный вариант для любой ситуации.

Оглавление:

• Выделение
  • Поиск и выделение повторяющихся значений ячеек в одном списке — условное форматирование
  • Поиск и выделение повторяющихся значений ячеек в нескольких списках — условное форматирование
  • Поиск и выделение повторяющихся значений ячеек — макрос Excel-VBA
  • Поиск и выделение повторяющегося текста внутри ячеек — макрос Excel VBA
• Замена
  • Замена дублирующихся значений ячеек с помощью макроса Excel-VBA
  • Подстановка в другие таблицы
  • Функция ВПР (VLOOKUP)
  • Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()
• Подсчёт
  • Посчитать количество повторений в одном списке
  • Сравнение двух списков используя формулу подсчёта повторений
  • Подсчёт количества повторений значений в строках с помощью макросов Excel-VBA
  • Функция СЧЁТЕСЛИ (COUNTIF)
• Поиск
  • Поиск повторений значений в ячейках с помощью макроса Excel-VBA
  • Скрытие
  • Сортировка и фильтр
  • Скрытие строк с помощью макроса Excel-VBA
• Удаление
  • Данные -> удалить дубликаты
  • Умные таблицы. Форматировать как таблицу -> удалить дубликаты

Функции в каждом разделе описаны в порядке возрастания их сложности и трудоемкости использования.

Выделение

Поиск и выделение повторяющихся значений ячеек в одном списке — условное форматирование

1. Выделить все значения в списке 

2. Вкладка «Главная» -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения

3. Выбрать необходимый формат (в данном случае выбран красный шрифт на светло-красном фоне)

Результат:

Если применить данное условное форматирование ко всему столбцу A, то все новые дубликаты, добавленные после строки 10 также будут отформатированы по заданному правилу.

Поиск и выделение повторяющихся значений ячеек в нескольких списках — условное форматирование

Сначала необходимо выделить столбцы (диапазоны ячеек) с дубликатами. Далее необходимо проделать действия, описанные в предидушем разделе начиная с шага 2.

Недостаток данного способа выделения дубликатов — визуально не определить, продублированы ли значения внутри каждого из списков, или между списками. В данном примере «малина» дублируется внутри списка 1, а «банан» и «груша» выделены потому что они продублированы между списками.

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

1. Выделяем первый столбец:

2. Вкладка «Главная» -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения

3. Пользовательский формат

4. Выбираем, например, одинарное подчеркивание, жирный шрифт и фиолетовый цвет.

5. Повторяем операцию с шага 2 для столбца B и получаем:

Поиск и выделение повторяющихся значений ячеек — макрос Excel-VBA

Скачать пример в Excel

Sub search_highlight_duplicates()

Dim Arr(16, 1) As String 'сравниваем значения как текст
'массив двухмерный
'16 на 2
'элементы 1-16,0 содержат значения ячеек
'элементы 1-16,1 - является ли соответсвующее значение дубликатом


For i = 1 To 16
Arr(i, 0) = ThisWorkbook. Sheets("Sheet1").Cells(i, 1).Value 'записываем в массив
Next i


For k = 1 To UBound(Arr, 1)
CurrentItem = Arr(k, 0) 'достаём по одному элементы из массива 1-16,0
    For i = 1 To UBound(Arr, 1)
    If CurrentItem = Arr(i, 0) And i <> k Then Arr(i, 1) = "COPY"
    'сравниваем с другими элементами массива (за исключением себя самого)
    'для копий записываем в 1-16,0 "COPY"
    Next i
Next k


For i = 1 To UBound(Arr, 1)
    ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value = Arr(i, 1)
    'запишем результат обратно в таблицу в колонку 2
    'либо здесь можно прописать особенное форматирование для каждого элемента исходного массива
Next i

End Sub

То же самое, но через форматирование ячеек:

Скачать пример в Excel

Sub search_highlight_duplicates()

Dim Arr(16, 1) As String

For i = 1 To 16
Arr(i, 0) = ThisWorkbook.Sheets("Sheet1").Cells(i, 1). Value
Next i

For k = 1 To UBound(Arr, 1)
CurrentItem = Arr(k, 0)
    For i = 1 To UBound(Arr, 1)
    If CurrentItem = Arr(i, 0) And i <> k Then Arr(i, 1) = "COPY"
    Next i
Next k


For i = 1 To UBound(Arr, 1)
If Arr(i, 1) = "COPY" Then
    With ThisWorkbook.Sheets("Sheet1").Cells(i, 1)
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
End If
    
Next i
End Sub

Поиск и выделение повторяющегося текста внутри ячеек — макрос Excel VBA

 Возможно существуют и другие более эффективные способы выделения копий слов внутри ячеек, но здесь будет описан только простейший способ, на который натолкнёт запись действий макрорекордером. Если мы напишем длинный текст в ячейке, включим запись макроса и отформатируем часть текста в ячейке, то получим примерно следующее: 

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

Замена

Замена дублирующихся значений ячеек с помощью макроса Excel-VBA

Подстановка в другие таблицы

Функция ВПР (VLOOKUP)

Об использовании функции ВПР пошагово.

Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()

Если таблица, из которой мы хотим доставать значения для подстановки содержит дубликаты, то использование функции ВПР может не дать нужного результата, так как ВПР использует первое попавшееся совпадение и результат будет зависит от сортировки.

Подсчёт

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

Сравнение двух списков используя формулу подсчёта повторений

Подсчёт количества повторений значений в строках с помощью макросов Excel-VBA

Функция СЧЁТЕСЛИ (COUNTIF)

Поиск

Поиск повторений значений в ячейках с помощью макроса Excel-VBA

Скрытие

Сортировка и фильтр

Скрытие строк с помощью макроса Excel-VBA

Удаление

Данные -> удалить дубликаты

Умные таблицы.

Как подсчитать повторяющиеся значения в Excel

Работа с большими наборами данных часто требует подсчета повторяющихся значений в Excel. Вы можете подсчитать повторяющиеся значения, используя функцию СЧЁТЕСЛИ. В этом уроке вы узнаете, как подсчитывать дубликаты с помощью этой функции.

Вы можете подсчитать дубликаты, используя формулу СЧЁТЕСЛИ в Excel. Есть несколько подходов к подсчету дубликатов. Возможно, вы захотите включить или исключить первый экземпляр при подсчете дубликатов. В следующих разделах вы увидите несколько примеров, связанных с подсчетом дубликатов.

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

Чтобы найти количество повторяющихся оценок, включая первое вхождение:

• Перейти к ячейке F2 .
• Назначение формулы =СЧЁТЕСЛИ($C$2:$C$8,E2) .
• Нажмите Введите .
• Перетащите формулу из F2 в F4.

Теперь у вас есть количество повторяющихся оценок в столбце E.

Часто может потребоваться подсчитать количество дубликатов в данных без первого вхождения. Вы можете подсчитать количество дубликатов, исключая первую запись, так же, как и в предыдущем примере. Чтобы подсчитать повторяющиеся примеры из последнего примера без первого вхождения:

• Выберите ячейку F2 .
• Назначение формулы =СЧЁТЕСЛИ($C$2:$C$8,E2)-1 .
• Нажмите Enter, чтобы применить формулу.

Это покажет количество повторяющихся значений без первого экземпляра в столбце E.

Функция СЧЁТЕСЛИ в Excel нечувствительна к регистру. Вы не получите фактического количества, если будете использовать его для подсчета дубликатов с учетом регистра. Но вы можете использовать комбинацию функций SUM и EXACT, чтобы получить количество повторяющихся экземпляров с учетом регистра. Чтобы найти количество повторяющихся значений с учетом регистра:

• Перейти к ячейке F2 .
• Назначение формулы =СУММ(--EXACT($C$2:$C$8,E2)) .
• Нажмите Ctrl + Shift + Enter , чтобы применить формулу как формулу массива.

Формула EXACT выполняет сравнение с учетом регистра значений в столбце D с оценками от C2 до C8. В результате получается массив логических значений ИСТИНА и ЛОЖЬ. Унарный оператор (-) преобразует значения в массив из 0 и 1. Затем функция СУММ суммирует эти записи, чтобы найти количество повторяющихся значений.

Вы можете подсчитывать повторяющиеся строки с одинаковыми значениями в каждой ячейке. Это очень удобно, если у вас есть большой набор данных и вы хотите идентифицировать повторяющиеся строки для будущих изменений. Функция СЧЁТЕСЛИМН позволяет считать на основе нескольких условий. Вы будете использовать функцию COUNTIFS для подсчета повторяющихся строк.

В следующем примере вы будете использовать информацию об ученике. В данных есть столбцы для имен учащихся, возраста и пола. Столбец E содержит все уникальные имена, для которых вы будете подсчитывать повторяющиеся строки.

• Выберите ячейку F2 , нажав на нее.
• Назначьте формулу =СЧЁТЕСЛИМН($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2) для F2.
• Нажмите Введите .

Перетащите формулу в ячейки ниже с помощью мыши.

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

• Перейдите в ячейку B2 , нажав на нее.
• Назначьте формулу =ЕСЛИ(СЧЕТЕСЛИ($A$2:A2,A2)>1,"Да","") ячейке B2.
• Нажмите Введите . Это покажет значение Да, если запись в A2 является повторяющейся записью.
• Перетащите формулу вниз от B2 до B8 .
• Выберите ячейку B9.
• Назначьте формулу =СЧЁТЕСЛИ(B2:B8;"Да") ячейке B9.
• Нажмите Введите .

Это покажет общее количество повторяющихся значений в столбце A без первого вхождения.

Чтобы подсчитать повторяющиеся значения, включая первое вхождение:

• Выберите ячейку D4.
• Назначение формулы =СТРОКИ($A$2:$A$8)-СУММ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$8,$A$2:$A$8) =1,1,0)) в ряд В2.
• Нажмите Ctrl + Shift + Enter .

Здесь мы использовали функции СТРОКИ, СУММ и ЕСЛИ вместе с функцией СЧЕТЕСЛИ и применили ее как функцию массива. Эту формулу можно разбить на две части. На первом этапе функция СТРОКИ подсчитывает количество строк между A2:A8, что равно 7. Во второй части формулы функция СЧЁТЕСЛИ используется для подсчета общего количества совпадений в диапазоне от A2 до A8 с самой собой, и мы вкладываем все это в функцию ЕСЛИ.

Используемое здесь условие состоит в том, чтобы вернуть 1, если это совпадение, и 0 в противном случае. Полученные 1 затем суммируются, и в результате получается 2, что является количеством различных совпадений между A2 и A8 с самим собой. Разница между этими двумя шагами возвращает 5, общее количество дубликатов, включая первые экземпляры.

Подсчет дубликатов в Excel — очень удобный прием при работе с большими наборами данных. Функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН удобны для подсчёта повторяющихся значений в Excel и получения полезной информации о данных.

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

Как подсчитывать дубликаты в электронных таблицах Excel

Электронные таблицы — это один из самых мощных и простых в использовании способов организации, просмотра и управления всеми видами данных. Одной из наиболее распространенных задач, которую люди выполняют с помощью электронных таблиц, таких как Microsoft Excel, является анализ наборов данных. Часто электронная таблица может содержать повторяющиеся данные, то есть строку или ячейку, которые дублируют другую строку или ячейку. Иногда мы хотим удалить эти дубликаты, и мы создали статью об удалении дубликатов в Excel, чтобы научить, как это сделать. Однако иногда мы не хотим изменять данные, но хотим знать, например, сколько раз конкретное значение дублируется в нашем наборе данных. Эта статья научит вас нескольким различным способам подсчета дубликатов в электронных таблицах Excel.

Функция СЧЁТЕСЛИ

СЧЁТЕСЛИ, несомненно, является одной из самых мощных и удобных статистических функций Excel. COUNTIF работает, находя общее количество ячеек в выбранном диапазоне, которые соответствуют заданным критериям. Например, вы можете попросить СЧЁТЕСЛИ сообщить вам, сколько ячеек в столбце D содержат фразу «Excel — это круто». Синтаксис этой функции Excel: =СЧЁТЕСЛИ (диапазон, критерий) . Диапазон — это ячейки, в которых вы хотите искать критерии, критерии — это то, что вы хотите, чтобы функция подсчитывала. Итак, как мы можем использовать COUNTIF для подсчета повторяющихся значений?

Сначала введите фиктивные данные в пустую электронную таблицу Excel. Введите значения «45», «252», «52», «45», «252» и «45» в ячейки A2: A7. Тогда ваша электронная таблица должна соответствовать той, что показана непосредственно ниже.

Теперь давайте предположим, что вам нужно найти, сколько ячеек содержит повторяющееся значение 45. Функция СЧЁТЕСЛИ сообщит вам это в один миг!

1. Выберите ячейку A9 и нажмите кнопку fx .
2. Выберите СЧЁТЕСЛИ и нажмите OK , чтобы открыть окно, показанное непосредственно ниже. (Если вам удобно работать с формулами Excel, вы можете просто ввести формулу непосредственно в ячейку, не используя диалоговые окна)
   
3. Нажмите кнопку Range и выберите диапазон ячеек А2:А7.
4. Затем введите 45 в текстовое поле Criteria и нажмите OK , чтобы закрыть окно.
   

A9 теперь будет возвращать результат формулы 3. Таким образом, в выбранном диапазоне есть три ячейки, содержащие значение 45.

Аналогично работает с текстом. Например, введите «груша», «яблоко», «яблоко» и «апельсин» в ячейки A11:14. Затем электронная таблица должна включать краткий список фруктов, как показано непосредственно ниже.

1. Выберите, чтобы добавить функцию СЧЁТЕСЛИ в ячейку A16.
2. Нажмите кнопку fx , выберите СЧЁТЕСЛИ и нажмите OK .

Теперь, когда вы нажмете OK , A16 должна вернуть значение 2. Таким образом, есть две ячейки, содержащие дубликаты яблок. Обратите внимание, что ячейки в выбранном диапазоне не должны содержать пробелов. Если это так, Excel не будет считать их дубликатами (если только введенные критерии не содержат точно такие же пустые места). Это руководство Tech Junkie расскажет вам, как удалить пустые места из ячеек электронной таблицы Excel.

Подсчет повторяющихся значений

Но что, если вам нужно найти общее количество дубликатов для двух, трех или более значений? Например, может потребоваться узнать, сколько раз три набора значений дублируются в диапазоне ячеек. В этом случае вы можете расширить функцию СЧЁТЕСЛИ, чтобы она включала несколько критериев.

1. Выберите A9 в электронной таблице Excel.
2. Затем щелкните на панели fx , чтобы отредактировать исходную функцию. Добавить +СЧЁТЕСЛИ(A2:A7,252) для функции и нажмите Введите .

После этого полная функция станет =СЧЁТЕСЛИ(A2:A7,45)+СЧЁТЕСЛИ(A2:A7,252), как показано на снимке ниже. Затем A9 вернет значение 5. Таким образом, функция суммирует как 45, так и 252 дубликата в нашем диапазоне ячеек, что составляет 5.

Функция также может подсчитывать значения в диапазонах ячеек в нескольких электронных таблицах Excel. Для этого потребуется изменить требуемые диапазоны ячеек, чтобы они включали ссылку на лист, например Лист2! Или Sheet3!, в ссылке на ячейку. Например, чтобы включить диапазон ячеек на листе 3, функция будет выглядеть примерно так: =СЧЁТЕСЛИ(A2:A7,45)+СЧЁТЕСЛИ(Лист3!C3:C8,252).

Подсчитать все повторяющиеся значения в столбце или строке

Некоторым пользователям Excel может потребоваться подсчитать все повторяющиеся значения или элементы в столбце электронной таблицы. Вы также можете сделать это с помощью функции СЧЁТЕСЛИ. Однако для этой функции требуется абсолютная ссылка на ячейку для всего столбца, в котором нужно подсчитать все дубликаты.

1. Щелкните ячейку B2 в своей электронной таблице Excel.
2. Нажмите кнопку fx и выберите, чтобы открыть COUNTIF Окно аргументов функции.
   
3. Введите $A$2:$A$7 в поле Range .
4. Введите $A2 в поле Criteria и нажмите OK , чтобы добавить функцию в электронную таблицу.
   
5. Ячейка B2 вернет значение 3 , как показано ниже.
6. Теперь нужно скопировать функцию во все ячейки под ней до B7 . Выберите B2 , затем щелкните левой кнопкой мыши в правом нижнем углу и перетащите его вниз до 9.0007 В7 .
   

Это копирует функцию во все остальные ячейки, как показано непосредственно ниже.

Столбец B на приведенном выше снимке теперь эффективно подсчитывает все значения в диапазоне A2:A7. Он подчеркивает, что 45 дублируется три раза, а 252 дублируется дважды в пределах выбранного диапазона. Итак, теперь вы можете быстро найти все повторяющиеся значения в столбцах или строках электронной таблицы, включив абсолютные ссылки на ячейки в функции СЧЁТЕСЛИ.

Теперь вы можете подсчитать любое количество повторяющихся значений или элементов в ваших электронных таблицах Excel с помощью СЧЁТЕСЛИ. Откройте эту страницу YouTube, чтобы увидеть функцию СЧЁТЕСЛИ в действии.

Знаете другие полезные советы и приемы работы с Excel? Поделитесь ими с нами ниже!

Подсчет уникальных значений среди дубликатов

Excel

Формулы и функции

Ошибки

Ошибки

Подсчет уникальных значений среди дубликатов

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Больше. ..Меньше

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

• Значения 5, 6, 7 и 6, результатом являются три уникальных значения — 5 , 6 и 7.

• Значения «Брэдли», «Дойл», «Дойл», «Дойл», результатом являются два уникальных значения — «Брэдли» и «Дойл».

Существует несколько способов подсчета уникальных значений среди дубликатов.

Можно использовать диалоговое окно Расширенный фильтр , чтобы извлечь уникальные значения из столбца данных и вставить их в новое место. Затем вы можете использовать функцию ROWS для подсчета количества элементов в новом диапазоне.

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.

   Убедитесь, что диапазон ячеек имеет заголовок столбца.

2. На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

   Появится диалоговое окно Расширенный фильтр .

3. Нажмите Скопируйте в другое место .

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

   Либо щелкните Свернуть диалоговое окно , чтобы временно скрыть диалоговое окно, выберите ячейку на листе и нажмите Развернуть диалоговое окно .

5. Установите флажок Только уникальные записи и нажмите OK .

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

6. В пустой ячейке под последней ячейкой диапазона введите функцию ROWS . Используйте только что скопированный диапазон уникальных значений в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =ROWS(B2:B45) .

Используйте комбинацию из ЕСЛИ , СУММА , ЧАСТОТА , ПОИСКПОЗ и ДЛИНА функций для выполнения этой задачи:

• org/ListItem»>

  Присвойте значение 1 каждому истинному условию с помощью функции IF .

• Добавьте итог, используя функцию СУММ .

• Подсчитайте количество уникальных значений с помощью ЧАСТОТА функция. Функция FREQUENCY игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству вхождений этого значения. Для каждого вхождения одного и того же значения после первого эта функция возвращает ноль.

• Возвращает позицию текстового значения в диапазоне с помощью функции ПОИСКПОЗ . Это возвращенное значение затем используется в качестве аргумента для ЧАСТОТА функция, позволяющая оценить соответствующие текстовые значения.

• Найдите пустые ячейки с помощью функции LEN . Пустые ячейки имеют длину 0,

Примечания:

• Формулы в этом примере необходимо вводить как формулы массива. Если у вас есть текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать ВВЕДИТЕ , чтобы подтвердить, что формула является формулой динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массива см. в разделе Рекомендации и примеры формул массива.

903:50

Чтобы просмотреть пошаговую оценку функции, выберите ячейку, содержащую формулу, а затем на вкладке Формулы в группе Аудит формул щелкните Вычислить формулу .

• Функция FREQUENCY вычисляет, как часто значения встречаются в диапазоне значений, а затем возвращает вертикальный массив чисел. Например, используйте FREQUENCY для подсчета количества результатов тестов, попадающих в диапазоны значений. Поскольку эта функция возвращает массив, ее необходимо вводить как формулу массива.

• org/ListItem»>

  Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек, а затем возвращает относительное положение этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =ПОИСКПОЗ(25;A1:A3,0) возвращает число 2, поскольку 25 — это второй элемент в диапазоне.

• Функция LEN возвращает количество символов в текстовой строке.

• Функция СУММ складывает все числа, указанные вами в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках с A1 по A5.

• Функция IF возвращает одно значение, если указанное вами условие оценивается как ИСТИНА, и другое значение, если это условие оценивается как ЛОЖЬ.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

Отфильтровать уникальные значения или удалить повторяющиеся значения

Как подсчитать дубликаты в Excel (5 методов)

При работе с большим объемом данных или при компиляции нескольких рабочих листов в один больший могут появиться повторяющиеся значения, столбцы или строки. Иногда нам нужно посчитать их, чтобы получить четкое представление. Вы можете сделать это легко, используя функцию «СЧЁТЕСЛИ» и другие функции. В этой статье мы узнаем, как считать дубликаты в Excel, используя пять различных методов.

Скачать практическую рабочую тетрадь

Пять способов подсчета дубликатов в Excel

1. Как подсчитать повторяющиеся значения, включая первое вхождение

2. Как подсчитать повторяющиеся значения, исключая первое появление

3. Как подсчитать регистрозависимые дубликаты в Excel

4. Подсчет повторяющихся строк в Excel

я. Включая первое появление

II. Исключение первого появления

5. Как подсчитать общее количество дубликатов в столбце

я. Включая первое появление

II. Исключение первого появления

То, что нужно запомнить

Вывод

Дальнейшие чтения

Загрузите этот практический лист, чтобы практиковаться, пока вы читаете эту статью.

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

Шаг 1:

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

Шаг 2:

Создайте таблицу в любом месте рабочего листа, где мы будем подсчитывать дубликаты.

Шаг 3:

Теперь мы посчитаем это с помощью функции «СЧЁТЕСЛИ» .

Формат этой функции

=СЧЁТЕСЛИ( Диапазон, Критерии)

Где,

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

Применить функцию «СЧЁТЕСЛИ» .

Здесь,

• Диапазон: $D$4:$D$12.
• Критерий F4.

Нажмите «Ввод» .

У нас есть количество дубликатов для «A» . Теперь примените одну и ту же функцию для всех критериев, выбрав и перетащив курсор.

Шаг-1:

Здесь мы будем подсчитывать повторяющиеся значения без учета первого появления.

Примените формулу «СЧЁТЕСЛИ» . Окончательная формула:

=СЧЁТЕСЛИ($D$4:$D$12;F4)-1

Нажмите «Ввод» . Повторяющиеся значения отображаются без первого вхождения.

Шаг 2:

Выберите ячейку и перетащите курсор, чтобы получить все результаты.

3.

Функция «СЧЕТЕСЛИ» в Excel нечувствительна к регистру. Поэтому нам нужно применить разные функции, чтобы получить дубликаты с учетом регистра. В этом случае мы будем использовать комбинацию функций «EXACT» и «SUM» .

Шаг 1:

Рассмотрим таблицу со значениями с учетом регистра, как показано на рисунке.

Шаг 2:
Примените эту формулу в ячейке, где вы хотите получить повторяющиеся значения

=СУММ(–ТОЧНО($D$4:$D$12,F4))

Где,

• Текст1 равно $D$4:$D$12
• Текст2 это F4

Для преобразования значений в массив из 0 и 1 используется унарный оператор (- -) .

Нажмите «CTRL + SHIFT + ENTER» , чтобы преобразовать формулу в формулу массива.

Здесь наш счетчик дубликатов с учетом регистра. Теперь мы получим остальные счетчики дубликатов.

Аналогичные показания:

• Как объединить дубликаты в Excel (6 способов)
• Поиск дубликатов в книге Excel (4 метода)

Этот метод можно выполнить двумя способами. Давайте обсудим.

Шаг 1:

В следующем примере у нас есть несколько повторяющихся строк, содержащих одинаковую информацию. Теперь мы узнаем, как считать эти строки, включая первое вхождение.

Шаг 2:

Для подсчета повторяющихся значений мы будем использовать функцию «СЧЁТЕСЛИМН» .

Окончательная форма функции

=СЧЁТЕСЛИМН($B$4:$B$12,B4,$C$4:$C$12,C4,$D$4:$D$12,D4)

Получить результат, нажав «Enter» .

Шаг 3:

Проделайте то же самое с остальными повторяющимися строками.

Шаг 1:

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

=СЧЁТЕСЛИМН($B$4:$B$12,B4,$C$4:$C$12,C4,$D$4:$D$12,D4)-1

Нажмите «Ввод» , чтобы получить значение.

Шаг 2:

Примените ту же формулу, щелкнув и перетащив мышь.

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

Шаг 1:

Рассмотрим таблицу, в которой у нас есть повторяющиеся значения в столбце. Мы должны подсчитать эти повторяющиеся значения.

Шаг 2:

Примените функцию «ЕСЛИ» и функцию «СЧЁТЕСЛИ» .

Конечная форма формулы

=ЕСЛИ(СЧЁТЕСЛИ($B$4:$B$4:$B$12,B4)>1,»ДУБЛИКАЦИЯ»,»»)

Нажмите «Ввод» и перетащите формулу в другие ячейки, чтобы получить повторяющиеся значения в столбце.

Шаг 3:

Чтобы подсчитать общее количество дубликатов, примените эту формулу.

Нажмите «Ввод» . Мы получили значения общего количества дубликатов.

Шаг 1:

Чтобы подсчитать дубликаты в столбце, исключая первое вхождение, примените эту формулу

=ЕСЛИ(СЧЁТЕСЛИ($B$4:B4,B4)>1,»ДА»,»»)

Где мы изменили ссылки на ячейки с $B$4:$B$4:$B$12 на $B$4:B4

Нажмите «Ввод» .

Итак, теперь мы получили только повторяющиеся значения, исключая первое вхождение.

Шаг 2:

Теперь посчитаем дубликаты.

Нажмите «Ввод» . Результат выдается в ячейке.

⏩Всегда используйте «Абсолютная ссылка на ячейку ($)» от до «Блокировать» диапазон

⏩ При подсчете дубликатов с учетом регистра обязательно примените формулу как «Формула массива» , нажав одновременно «CTRL+SHIFT+ENTER» .

⏩ Используйте унарный оператор (- -) для преобразования результата функции «EXACT» в массив из 0 и 1.

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

Дополнительная литература

• Как скрыть дубликаты в Excel (4 способа)
• VBA для удаления дубликатов в Excel (6 примеров)
• Как сгруппировать дубликаты в Excel (3 метода)

поиск, выделение, подсчет, фильтрация и т.

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

При работе с большим рабочим листом Excel или при объединении нескольких небольших электронных таблиц в одну большую вы можете обнаружить в нем много повторяющихся строк. В одном из наших предыдущих руководств мы обсуждали различные способы сравнения двух таблиц или столбцов на наличие дубликатов.

И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 365, Excel 2019, Excel 2016, Excel 2013 и более ранних версиях.

• Как определить дубликаты в Excel
  • Проверка на дубликаты с 1 ^st вхождениями
  • Поиск дубликатов без 1 ^st вхождений
  • Как найти дубликаты с учетом регистра в Excel
• Как найти повторяющиеся строки в Excel
• Как подсчитать дубликаты в Excel
• Как отфильтровать дубликаты в Excel
• Другие способы работы с дубликатами (выделить, удалить, выбрать, скопировать или переместить)
• Duplicate Remover для Excel — быстрый и простой способ поиска дубликатов

Как идентифицировать дубликаты в Excel

Самый простой способ обнаружения дубликатов в Excel — использование функции СЧЁТЕСЛИ. В зависимости от того, хотите ли вы найти повторяющиеся значения с первыми вхождениями или без них, в формуле будут небольшие изменения, как показано в следующих примерах.

Как найти повторяющиеся записи, включая 1

Предположим, у вас есть список элементов в столбце A, которые вы хотите проверить на наличие дубликатов. Это могут быть счета-фактуры, идентификаторы продуктов, имена или любые другие данные.

Вот формула для поиска дубликатов в Excel, включая первые вхождения (где A2 — самая верхняя ячейка):

=СЧЁТЕСЛИ(A:A, A2)>1

Введите приведенную выше формулу в B2, затем выберите B2 и перетащите маркер заполнения, чтобы скопировать формулу вниз в другие ячейки:

Как вы можете видеть на снимке экрана выше, формула возвращает ИСТИНА для повторяющихся значений и ЛОЖЬ для уникальных значений.

Примечание. Если вам нужно найти дубликаты в диапазоне ячеек , а не во всем столбце, не забудьте заблокировать этот диапазон знаком $. Например, для поиска дубликатов в ячейках A2:A8 используйте следующую формулу:
=СЧЁТЕСЛИ( $A$2:$A$8 , A2)>1

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$8, $A2)>1, "Duplicate", " Уникальный")

Если вы хотите, чтобы формула Excel находила только дубликаты, замените «Уникальный» пустой строкой («») следующим образом:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$8, $A2)> 1, «Дублировать», «»)

Формула вернет «Дубликаты» для повторяющихся записей и пустую ячейку для уникальных записей:

Как искать дубликаты в Excel без 1

Если вы планируете отфильтровать или удалить дубликаты после их обнаружения, использование приведенной выше формулы небезопасно, поскольку она помечает все идентичные записи как дубликаты. И если вы хотите сохранить уникальные значения в своем списке, вы не можете удалить все повторяющиеся записи, вам нужно удалить только 2 ^-й -й и все последующие экземпляры.

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2, $A2)>1, "Дублировать", "")

Как видно на следующем снимке экрана, эта формула не идентифицирует первое вхождение « Apples » как дубликат:

Как найти дубликаты с учетом регистра в Excel

В ситуациях, когда вам нужно определить точные дубликаты включая текстовый регистр, используйте эту общую формулу массива (вводится нажатием Ctrl + Shift + Enter):

ЕСЛИ( СУММ(( —EXACT( диапазон , самая верхняя _ячейка )))<=1, "", "Duplicate")

В основе формулы вы используете функцию EXACT для сравнения целевая ячейка с каждой ячейкой в ​​указанном диапазоне точно. Результатом этой операции является массив TRUE (совпадение) и FALSE (не совпадение), который приводится к массиву 1 и 0 с помощью унарного оператора (—). После этого функция СУММ суммирует числа, и если сумма больше 1, функция ЕСЛИ сообщает «Дубликат».

Для нашего примера набора данных формула выглядит следующим образом:

=ЕСЛИ(СУММ((--EXACT($A$2:$A$8,A2)))<=1,"","Дубликат")

Как показано на снимке экрана ниже, строчные и прописные буквы интерпретируются как разные символы (ЯБЛОКИ не идентифицируются как дубликаты):

Как найти повторяющиеся строки в Excel

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

Рассмотрим следующий пример. Предположим, у вас есть номера заказов в столбце A, даты в столбце B и заказанные товары в столбце C, и вы хотите найти повторяющиеся строки с тем же номером заказа, датой и товаром. Для этого мы создадим повторяющуюся формулу на основе функции СЧЁТЕСЛИМН, которая позволяет одновременно проверять несколько критериев:

К поиск повторяющихся строк с 1 ^st вхождений , используйте эту формулу:

=ЕСЛИ(СЧЁТЕСЧЁМ($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$2:$C$8,$C2)>1, «Повторяющаяся строка», «»)

На следующем снимке экрана показано, что формула действительно находит только те строки, которые имеют одинаковые значения во всех трех столбцах. Например, строка 8 имеет тот же номер заказа и дату, что и строки 2 и 5, но другой элемент в столбце C, поэтому она не помечена как повторяющаяся строка:

Чтобы показать повторяющихся строк без 1 ^st вхождений , немного подкорректируйте приведенную выше формулу:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2,$A2,$B$2:$B2,$B2,$B$2:$B2,$B2 ,$C$2:$C2,$C2,) >1, "Повторяющаяся строка", "")

Как подсчитать дубликаты в Excel

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

Подсчет экземпляров каждой повторяющейся записи по отдельности

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

Чтобы узнать, сколько раз та или иная запись встречается на вашем листе Excel, используйте простую формулу СЧЁТЕСЛИ, где A2 — первый, а A8 — последний элемент списка:

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

Как показано на следующем снимке экрана, формула подсчитывает количество вхождений каждого элемента: « Яблоки » встречается 3 раза, « Зеленые бананы » — 2 раза, « Бананы » и " Апельсины " только один раз.

Если вы хотите идентифицировать 1 ^st , 2 ^nd , 3 ^rd и т. д. вхождений каждого элемента, используйте следующую формулу: Таким образом, вы можете подсчитать вхождений дублированных строк . Единственное отличие состоит в том, что вам нужно будет использовать функцию СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ. Например:

=СЧЕТЧИСЛ($A$2:$A$8, $A2, $B$2:$B$8, $B2)

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

Подсчет общего количества дубликатов в столбце (столбцах)

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

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

Где « дубликат » — это метка, которую вы использовали в формуле для поиска дубликатов.

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

=СЧЁТЕСЛИ(B2:B8, "дубликат")

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

=СТРОКИ($A$2:$A$8)-СУММ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$8,$A$2:$A $8)=1,1,0))

Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ее. Кроме того, имейте в виду, что эта формула учитывает все повторяющиеся записи, включая первые вхождения :

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

=СТРОКИ($A$2:$A$8)-СУММ(ЕСЛИ(СЧЁТЕСЧИСЛ($A$2:$A $8,$A$2:$A$8, $B$2:$B$8,$B$2:$B$8)=1,1,0))

Как фильтровать дубликаты в Excel

Для упрощения анализа данных вы можете отфильтровать данные, чтобы отображались только дубликаты. В других ситуациях может понадобиться наоборот — скрыть дубликаты и просмотреть уникальные записи. Ниже вы найдете решения для обоих сценариев.

Как отобразить и скрыть дубликаты в Excel

Если вы хотите сразу увидеть все дубликаты, используйте одну из формул для поиска дубликатов в Excel, которая лучше соответствует вашим потребностям. Затем выберите свой стол, переключитесь на Данные и нажмите кнопку Фильтр . Кроме того, вы можете нажать Сортировка и фильтр > Фильтр на вкладке Главная в группе Редактирование .

Совет. Чтобы включить автоматическую фильтрацию, преобразуйте данные в полнофункциональную таблицу Excel. Просто выберите все данные и нажмите сочетание клавиш Ctrl + T.

После этого нажмите на стрелку в заголовке столбца Duplicate и отметьте « Duplicate row " поле показать дубликаты . Если вы хотите отфильтровать, т.е. скрыть дубликаты , выберите " Unique " для просмотра только уникальных записей:

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

Как отфильтровать дубликаты по их вхождению

1041 rd или N-е вхождение повторяющихся значений, используйте формулу для подсчета повторяющихся экземпляров, которую мы обсуждали ранее:

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

Затем примените фильтрацию к таблице и выберите только события, которые вы хотите просмотреть. Например, вы можете отфильтровать 2 ^и вхождения, как показано на следующем снимке экрана: