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

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

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

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

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

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

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

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

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

• COUNTIF(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Эти два диапазона содержат данные, по которым нужно подсчитать все совпадения.

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

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

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

• MATCH(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}): функция СЧЁТ подсчитает числовые значения в этом массиве и вернёт результат — 5.

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

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

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

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

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

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

• MATCH(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)))=--({TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}): двойной минус (--) преобразует значения ИСТИНА в 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.

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

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