Как посчитать количество совпадений в excel в двух столбцах

Обновлено: 03.07.2024

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

Исходные списки для сравнения

Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен - Создать (Formulas - Name Manager - Create) . Также можно превратить таблицы в "умные" с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home - Format as Table) .

Подсчет количества совпадений

Для подсчета количества совпадений в двух списках можно использовать следующую элегантную формулу:

Количество совпадений формулой

В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))

Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек.

Во-первых, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации:

=СЧЁТЕСЛИ( Где_искать ; Что_искать )

Обычно первый аргумент - это диапазон, а второй - ячейка, значение или условие (одно!), совпадения с которым мы ищем в диапазоне. В нашей же формуле второй аргумент - тоже диапазон. На практике это означает, что мы заставляем Excel перебирать по очереди все ячейки из второго списка и подсчитывать количество вхождений каждого из них в первый список. По сути, это равносильно целому столбцу дополнительных вычислений, свернутому в одну формулу:

Подсчет количества совпадений отдельным столбцом

Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции - суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl + Shift + Enter . Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.

Вывод списка совпадений формулой массива

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

Вывод совпадений в двух списках формулой массива

В английской версии это будет, соответственно:

Логика работы этой формулы следующая:

  • фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
  • фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
  • и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент

Вывод списка совпадений с помощью слияния запросов Power Query

На больших таблицах формула массива из предыдущего способа может весьма ощутимо тормозить, поэтому гораздо удобнее будет использовать Power Query. Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать практически любые данные. Мощь и возможности Power Query так велики, что Microsoft включила все ее функции по умолчанию в Excel начиная с 2016 версии.

Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table) :

Загрузка списков в Power Query

Excel превратит нашу таблицу в "умную" и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить - Закрыть и загрузить в. (Close & Load To. ) и выбрать в появившемся окне Только создать подключение (Create only connection) :

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

Затем повторяем то же самое со вторым диапазоном.

И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные - Объединить запросы - Объединить (Get Data - Merge Queries - Merge) :

Объединение запросов в Power Query

В открывшемся окне делаем три вещи:

  1. выбираем наши таблицы из выпадающих списков
  2. выделяем столбцы, по которым идет сравнение
  3. выбираем Тип соединения = Внутреннее (Inner Join)

Слияние для выявления совпадающих строк

После нажатия на ОК на экране останутся только совпадающие строки:

Результат слияния

Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :

Выгрузка результатов на лист

Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 .

Макрос для вывода списка совпадений

Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer) . Если ее не видно, то отобразить ее можно через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .

В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert - Module и затем скопировать туда код нашего макроса:

Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl , оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F8 . Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:

Макрос поиска совпадений в двух списках

Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :

difference1.jpg

Число несовпадений можно посчитать формулой:

или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))

Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

difference2.jpg

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

  • залить цветом или как-то еще визуально отформатировать
  • очистить клавишей Delete
  • заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
  • удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
  • и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):

difference5.jpg

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.

Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

difference4.jpg

Полученный в результате ноль и говорит об отличиях.

И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Например, у меня есть два списка данных в столбце A и столбце C, теперь я хочу сравнить два столбца и подсчитать, найдено ли значение в столбце A в столбце C в той же строке, что и на скриншоте ниже. В этом случае функция СУММПРОИЗВ может быть лучшей функцией для решения этой задачи в Excel.


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

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

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

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


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

= СУММПРОИЗВ (- (A2: A12 = C2: C12)):

  • A2: A12 = C2: C12 : Это выражение сравнивает значения в диапазоне A2: A12 со значениями в диапазоне C2: C12 для каждой строки, если два значения в одной строке равны, будет отображаться ИСТИНА, в противном случае отображается ЛОЖЬ, поэтому вы получите такой массив: .
  • - (A2: A12 = C2: C12) = - (): : - этот двойной отрицательный знак преобразует ИСТИНА и ЛОЖЬ в 1 и 0, возвращает следующий результат: .
  • SUMPRODUCT(--(A2:A12=C2:C12))= SUMPRODUCT(): : Функция СУММПРОИЗВ суммирует все значения в массиве и получает результат: 4.

Советы:

Если вам нужно подсчитать несовпадающие значения между двумя столбцами, примените эту формулу:


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

  • SUMPRODUCT:
  • Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.

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

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

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

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

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


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

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

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

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

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


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

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

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

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

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


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

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


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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

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