Перейти к основному содержанию

Подсчитайте все совпадения / дубликаты между двумя столбцами в Excel

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


Подсчитайте все совпадения между двумя столбцами с помощью функций СУММПРОИЗВ и СЧЁТЕСЛИ

Чтобы подсчитать все совпадения между двумя столбцами, вам может помочь комбинация функций СУММПРОИЗВ и СЧЁТЕСЛИ, общий синтаксис:

=SUMPRODUCT(COUNTIF(range1,range2))
  • range1, range2: Два диапазона содержат данные, по которым вы хотите подсчитать все совпадения.

Теперь введите или скопируйте приведенную ниже формулу в пустую ячейку и нажмите Enter ключ для получения результата:

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))


Пояснение к формуле:

= СУММПРОИЗВ (СЧЁТЕСЛИ (A2: A12; C2: C12))

  • СЧЁТЕСЛИ (A2: A12; C2: C12): Эта функция СЧЁТЕСЛИ проверяет, существует ли каждое имя из столбца C в столбце A. Если имя существует, отображается число 1, в противном случае отображается число 0. Функция вернет следующий результат: {1; 1; 0; 0; 0; 1; 0; 0; 1; 0; 1}.
  • SUMPRODUCT(COUNTIF(A2:A12,C2:C12))=SUMPRODUCT({1;1;0;0;0;1;0;0;1;0;1}): Функция СУММПРОИЗВ суммирует все элементы в этом массиве и получает результат: 5.

Подсчитайте все совпадения между двумя столбцами с помощью функций СЧЁТ и ПОИСКПОЗ

С помощью комбинации функций СЧЁТ и ПОИСКПОЗ вы также можете получить количество совпадений между двумя столбцами, общий синтаксис:

{=COUNT(MATCH(range1,range2,0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • range1, range2: Два диапазона содержат данные, по которым вы хотите подсчитать все совпадения.

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

=COUNT(MATCH(A2:A12,C2:C12,0))


Пояснение к формуле:

= СЧЁТ (ПОИСКПОЗ (A2: A12; C2: C12,0))

  • ПОИСКПОЗ (A2: A12; C2: C12,0): Эта функция ПОИСКПОЗ будет искать имена из столбца A в столбце C и возвращать положение каждого совпадающего значения. Если значение не найдено, отображается значение ошибки. Итак, вы получите следующий список массивов: {11; 2; # N / A; # N / A; # N / A; 6; 1; # N / A; # N / A; # N / A; 9}.
  • COUNT(MATCH(A2:A12,C2:C12,0))= COUNT({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): Функция COUNT подсчитает числа в списке массивов, чтобы получить результат: 5.

Подсчитайте все совпадения между двумя столбцами с помощью функций СУММПРОИЗВ, ЕЧИСЛО и ПОИСКПОЗ.

В Excel вы можете попытаться найти совпадения в двух столбцах и посчитать их, используя функции СУММПРОИЗВ, ЕЧИСЛО и ПОИСКПОЗ, общий синтаксис следующий:

=SUMPRODUCT(--(ISNUMBER(MATCH(range1,range2,0))))
  • range1, range2: Два диапазона содержат данные, по которым вы хотите подсчитать все совпадения.

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

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))


Пояснение к формуле:

= СУММПРОИЗВ (- (ISNUMBER (MATCH (A2: A12; C2: C12,0))))

  • ПОИСКПОЗ (A2: A12; C2: C12,0): Эта функция ПОИСКПОЗ будет искать имена из столбца A в столбце C и возвращать положение каждого совпадающего значения. Если значение не найдено, отображается значение ошибки. Итак, вы получите следующий список массивов: {11; 2; # N / A; # N / A; # N / A; 6; 1; # N / A; # N / A; # N / A; 9}.
  • ISNUMBER(MATCH(A2:A12,C2:C12,0))= ISNUMBER({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): Здесь функция ЕЧИСЛО преобразует числа в ИСТИНА, а другие значения в ЛОЖЬ в массиве. Итак, вы получите такой массив: {ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА}.
  • - (ISNUMBER (MATCH (A2: A12, C2: C12,0))) = - ({ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА}): - этот двойной отрицательный знак используется для преобразования значения ИСТИНА в 1 и значения Ложь в 0 и возвращает следующий результат: {1; 1; 0; 0; 0; 1; 1; 0; 0; 0; 1}.
  • SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))=SUMPRODUCT({1;1;0;0;0;1;1;0;0;0;1}): Наконец, функция СУММПРОИЗВ просуммирует все элементы в этом массиве и получит результат: 5.

Используемая относительная функция:

  • SUMPRODUCT:
  • Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
  • COUNTIF:
  • Функция СЧЁТЕСЛИ - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.
  • СЧИТАТЬ:
  • Функция COUNT используется для подсчета количества ячеек, содержащих числа, или для подсчета чисел в списке аргументов.
  • МАТЧ:
  • Функция ПОИСКПОЗ в Microsoft Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.
  • НОМЕР:
  • Функция ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.

Другие статьи:

  • Подсчет совпадений между двумя столбцами
  • Например, у меня есть два списка данных в столбце A и столбце C, теперь я хочу сравнить два столбца и подсчитать, найдено ли значение в столбце A в столбце C в той же строке, что и на скриншоте ниже. В этом случае функция СУММПРОИЗВ может быть лучшей функцией для решения этой задачи в Excel.
  • Подсчитать количество ячеек, содержащих определенный текст в Excel
  • Предположим, у вас есть список текстовых строк, и вы можете захотеть найти количество ячеек, которые содержат определенный текст как часть своего содержимого. В этом случае вы можете использовать подстановочные знаки (*), которые представляют любые тексты или символы в ваших критериях при применении функции СЧЁТЕСЛИ. В этой статье я расскажу, как использовать формулы для решения этой задачи в Excel.
  • Подсчитайте количество ячеек, не равное множеству значений в Excel
  • В Excel вы можете легко получить количество ячеек, не равное определенному значению, используя функцию СЧЁТЕСЛИ, но пробовали ли вы когда-нибудь подсчитать количество ячеек, которые не равны множеству значений? Например, я хочу получить общее количество продуктов в столбце A, но исключить конкретные элементы в C4: C6, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...

Описание


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (1)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Great tip, saved me a lot of eye strain!
Rated 5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations