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

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

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

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

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

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

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

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

= СУММПРОИЗВ (СЧЁТЕСЛИ (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.

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

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

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

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

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

= СЧЁТ (ПОИСКПОЗ (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 вы можете попытаться найти совпадения в двух столбцах и посчитать их, используя функции СУММПРОИЗВ, ЕЧИСЛО и ПОИСКПОЗ, общий синтаксис следующий:

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

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

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

= СУММПРОИЗВ (- (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.

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

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