Пронумеруйте одинаковые значения в Excel

Обновлено: 24.11.2024

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

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

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

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

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

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

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

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

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

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

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

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

Подсчет дубликатов с учетом регистра в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Halo, bagaimana jika kita ingin menjumlahkan total dari data duplikat tersebut? Contoh, Banyaknya Apel pada baris satu adalah 734шт, Apel pada baris 5 100шт, Apel pada baris 8 20шт. Bagaimana cara menjumlahkannya dengan otomatis? Терима Касих унтук джавабання

Вы можете объединить несколько выражений СЧЁТЕСЛИ в одну формулу СУММ, в которой выражения СЧЁТЕСЛИ должны быть разделены запятой. Вот пример: =СУММ(СЧЁТЕСЛИ($B$2:$B$8, B2),СЧЁТЕСЛИ($B$2:$B$8, B3),СЧЁТЕСЛИ($B$2:$B$8, B4)).

Здравствуйте, я пытаюсь вычислить повторяющиеся значения как одно значение в Excel. Но я не могу. Вы можете помочь мне?. Например, я ввел в строки как 1001,1002,1003,1004 и снова 1001,1002,1003,1004 ", как это. В этом случае все повторяются, но он должен вывести "1001" как один счет, не два счета. Есть ли какая-то формула для этого?

Привет, Manikandan.K,
Предположим, что данные, которые вы будете подсчитывать, находятся в диапазоне A1:A20, вы можете использовать приведенную ниже формулу со стрелкой для подсчета (набор дубликатов учитывается только один раз):
= СУММ(ЕСЛИ(A1:A20<>"",1/СЧЁТЕСЛИ(A1:A20,A1:A20)))
Обратите внимание, что это формула массива, нажмите Ctrl + Shift + Enter, чтобы получить подсчет результат.

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

Здравствуйте, я пытаюсь запустить функцию "Расширенная комбинированная строка", но при ее запуске каждый раз происходит сбой Excel.

Я ввел в строку как "10,10,10,10,10,10,10,8,11,10,10,10,10,10" вот так, теперь я ввел 14 числовых значений. Как я могу подсчитать разные числа в одной строке? в моем случае результат должен быть "14".

Привет, Sampath Rangan,
Вам нужно подсчитать все числа в строке или подсчитать дубликаты в строке?
Предположим, что числа находятся в диапазоне A1:N1, теперь вы можете подсчитать числа следующим образом:
Подсчитать все числа: =COUNT(A1:N1)
Подсчитать дубликаты, говорит 10: = СЧЁТЕСЛИ($A$1:$N$1,"10")

мне это нужно
Есть 24 студента. сколько групп, чтобы взять 10 студентов в 1 группу
пример:
1,2,3,4,5 взять 1 группу в 3 число

как это сделать Excel создаст этот файл

Должно быть, я глуп, так как решение KuTools у меня не работает. Я скопировал пример со столбцом «Фрукты» (в который я поместил 3 записи Apple), создал рядом с ним пустой столбец, называемый столбцом «Количество». KuTools -> Контент -> Расширенные ряды комбинирования. Выберите столбец Fruit в качестве первичного ключа, столбец Count в качестве Calculate -> Count. Нажмите ОК. Ничего. Лист без изменений. Помогите пожалуйста?

Здравствуйте, БиллБрюстер!
Не могли бы вы сделать мне одолжение и прислать мне снимок экрана с диалоговым окном "Расширенные строки комбинирования"? Так же, как скриншот, как показано ниже. Заранее спасибо!

Уважаемый, я работаю над прикрепленным снимком экрана и файлом Excel. Мне нужно рассчитать значения в столбце «G», т.е. подсчитать уникальные текстовые значения на основе нескольких (двух) критериев, но критерии указаны в ЦИФРАХ. Кроме того, это большой лист, поэтому я хочу использовать ссылку на ячейку в диапазоне и в критериях. Я очень смущен. Я хочу подсчитать столбец C, т.е. степень на основе критериев E и F.То есть мне нужно установить формулу в G2, чтобы посмотреть E2 в столбце A и посмотреть F2 в столбце B и подсчитать уникальные текстовые значения в столбце C. Надеюсь, это понятно.

Я очень благодарен вам, пожалуйста

Подробности:
У меня есть данные более чем 1000 компаний с разным годом. Как и в столбце A, у меня есть компании, а B показывает год, за который у этих компаний есть данные. Например, у первой компании есть данные за 2011, 2012, 2013 и 2015 годы. Для компании 2 у меня есть данные за 2014 и 2011 годы и так далее. Поскольку у большинства компаний есть данные с 2011 по 2015 год, поэтому я выбираю этот диапазон в столбце F для каждой компании. Теперь я хочу подсчитать уникальные градусы для ячейки G2, если в A есть 389841988 (идентификатор компании для первой компании), а в столбце B — 2011. Теперь мне нужно установить формулу в G2 таким образом, если я буду перетащите формулу G2 в ячейку G3, затем она должна дать мне значение, выглядя так, как будто в A есть 389841988 (идентификатор компании для первой компании), а в столбце B есть 2012, на основе этих двух критериев, он должен рассчитывать уникальный текст в C2 и так далее.
Посмотрите на второй лист под названием Пример, я сделал то же самое для функции ИНДЕКС, ПОИСКПОЗ, а также для функции СУММЕСЛИМН; в C2 у меня есть формула массива ИНДЕКС ($ F: $ H, MATCH (1, (A2 = $ F: $ F) * (B2 = $ G: $ G), 0), 3), а в D2 у меня есть СУММЕСЛИМН (И: И, Ж: Ж, А2, З: З, В2). Это помогает мне делать то, что я хочу, с двумя критериями (чтобы соответствовать, а также суммировать), а также я могу перетаскивать их для остальной части ячейки. Я ищу что-то вроде этого или что-нибудь, что я могу перетащить, чтобы подсчитать уникальные степени. Спасибо большое. Я жду.

В Excel существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений:

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

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

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

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

Выполните следующие действия:

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

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

Во всплывающем окне "Расширенный фильтр" выполните одно из следующих действий:

Чтобы отфильтровать диапазон ячеек или таблицу на месте:

Нажмите "Фильтровать список на месте".

Чтобы скопировать результаты фильтра в другое место:

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

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

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

Проверьте только уникальные записи, затем нажмите OK.

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

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

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

Выполните следующие действия:

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

На вкладке "Данные" нажмите "Удалить дубликаты" (в группе "Инструменты данных").

Выполните одно или несколько из следующих действий:

В разделе "Столбцы" выберите один или несколько столбцов.

Чтобы быстро выбрать все столбцы, нажмите "Выбрать все".

Чтобы быстро очистить все столбцы, нажмите "Отменить выбор всех".

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

Примечание. Данные будут удалены из всех столбцов, даже если вы не выберете все столбцы на этом шаге.Например, если вы выберете Column1 и Column2, но не Column3, то «ключ», используемый для поиска дубликатов, будет значением ОБА Column1 и Column2. Если в этих столбцах обнаружен дубликат, вся строка будет удалена, включая другие столбцы в таблице или диапазоне.

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

Отменить изменение, нажав «Отменить» (или нажав Ctrl+Z на клавиатуре).

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

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

Быстрое форматирование

Выполните следующие действия:

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

На вкладке "Главная" в группе "Стиль" нажмите маленькую стрелку "Условное форматирование", затем нажмите "Правила выделения ячеек" и выберите "Дублировать значения".

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

Расширенное форматирование

Выполните следующие действия:

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

На вкладке "Главная" в группе "Стили" щелкните стрелку "Условное форматирование", а затем нажмите "Управление правилами", чтобы открыть всплывающее окно "Диспетчер правил условного форматирования".

Выполните одно из следующих действий:

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

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

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

В списке «Форматировать все» в разделе «Редактировать описание правила» выберите «уникальное» или «дубликат».

Нажмите «Формат», чтобы открыть всплывающее окно «Формат ячеек».

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

В Excel для Интернета можно удалить повторяющиеся значения.

Удалить повторяющиеся значения

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

Выполните следующие действия:

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

На вкладке "Данные" нажмите "Удалить дубликаты" .

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

Примечание. Данные будут удалены из всех столбцов, даже если вы не выберете все столбцы на этом шаге. Например, если вы выберете Column1 и Column2, но не Column3, то «ключ», используемый для поиска дубликатов, будет значением ОБА Column1 и Column2. Если в Column1 и Column2 обнаружен дубликат, то будет удалена вся строка, включая данные из Column3.

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

Примечание. Если вы хотите вернуть свои данные, просто нажмите «Отменить» (или нажмите Ctrl+Z на клавиатуре).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: