Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Подсчитайте все совпадения / дубликаты между двумя столбцами в 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 for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее ...) и экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.