Excel подсчитывает количество одинаковых значений в столбце

Обновлено: 21.11.2024

Вы можете использовать функцию СЧЕТЕСЛИ для подсчета дубликатов в Excel:

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

Шаги по подсчету дубликатов в Excel с помощью СЧЁТЕСЛИ

Шаг 1. Подготовьте данные, содержащие дубликаты

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

Имя

Джон

Мария

Билл

Эмма

Джон

Билл

Счет

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

Шаг 2. Скопируйте данные в Excel

Для простоты скопируйте приведенную выше таблицу в Excel в диапазоне ячеек от A1 до A11. Вы также можете добавить новый столбец под названием «Счетчик» в ячейку B1:

Имя	Count
Джон< /td>
Джон
Мария
Мария
Билл
Эмма< /td>
Джон
Счет
Счет
Счет

Затем вы можете применить функцию СЧЁТЕСЛИ в столбце "Количество", чтобы получить количество дубликатов.

Шаг 3. Подсчитайте дубликаты в Excel с помощью функции СЧЁТЕСЛИ

Вспомните, что функция СЧЁТЕСЛИ имеет следующую структуру:

В контексте нашего примера вам нужно применить функцию СЧЁТЕСЛИ в ячейке B2:

A:A — это столбец, в котором хранятся все наши значения (в нашем случае это столбец A)
A2 — это ячейка, в которой у нас есть первое значение (в нашем случае это «Джон»)

Затем функция СЧЁТЕСЛИ подсчитает, сколько раз слово «Джон» появляется в столбце А. В нашем примере имя «Джон» появляется 3 раза:

Имя	Count
Джон	3

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

Имя	Count
Джон	3
Джон	3
Мария	2
Мария	2
Счет	4
Эмма	1
Джон	3
Счет	4
Счет	4
Счет	4

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

При работе с большими наборами данных часто требуется подсчитывать дубликаты в 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 здесь, чтобы получить помощь один на один. Первый сеанс всегда бесплатный.

Таблицы — это один из самых мощных и простых в использовании способов организации, просмотра и управления всеми видами данных. Одной из наиболее распространенных задач, которую люди выполняют с помощью электронных таблиц, таких как 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, чтобы увидеть функцию СЧЁТЕСЛИ в действии.

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

Загрузить практическую рабочую тетрадь

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

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

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

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

Шаг 1:

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

Шаг 2:

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

Шаг 3:

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

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

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

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

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

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

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

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

Шаг 1:

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

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

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

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

Шаг 2:

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

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

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

Шаг 1:

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

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

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

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

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

Похожие чтения:

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

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

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

Шаг 1:

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

Шаг 2:

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

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

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

Шаг 3:

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

ii. Исключение первого вхождения

Шаг 1:

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

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

Шаг 2:

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

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

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

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

Шаг 1:

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

Шаг 2:

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

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

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

Шаг 3:

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

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

ii. Исключение первого вхождения

Шаг 1:

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

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

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

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

Шаг 2:

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

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

Что нужно помнить

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

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

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

Заключение

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

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