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

Обновлено: 21.11.2024

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

Рисунок 1. Результат функции СУММПРОИЗВ

Синтаксис формулы СУММПРОИЗВ

Общая формула для функции СУММПРОИЗВ:

=СУММПРОИЗВ(--(массив1 = массив2))

массив1 и массив2 — диапазон ячеек, которые мы хотим сравнить. Массивы должны быть одного размера.

Эта функция сравнивает ячейки одну за другой и возвращает логические значения TRUE или FALSE. Используя двойное отрицание «–» перед логическим тестом, логические значения преобразуются в 1 или 0. При этом функция суммирует все и возвращает результат.

Настройка наших данных для функции СУММПРОИЗВ

Рисунок 2. Данные, которые мы будем использовать в примере СУММПРОИЗВ

В столбцах B и C у нас есть два списка городов, которые мы хотим сравнить. В ячейке F2 мы хотим вернуть общее количество совпадений между двумя столбцами.

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

В нашем примере мы хотим проверить, совпадают ли ячейки в столбцах B и C, и подсчитать совпадения в F2.

Формула выглядит следующим образом:

=СУММПРОИЗВ(--(B3:B12 = C3:C12))

Массив1 — это B3:B12, а массив2 — это C3:C12. Давайте оценим формулу и посмотрим на результат:

Рисунок 3. Оценка функции СУММПРОИЗВ

Как видите, функция имеет значение TRUE, если ячейки равны, или FALSE, если нет. Строки 1, 6 и 7 одинаковые, поэтому в функции у нас ИСТИНА для этих ячеек. Наконец, эти 3 значения суммируются, и мы получаем результат 3 в ячейке F3.

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

Выберите ячейку F2 и нажмите на нее.
Вставьте формулу: =СУММПРОИЗВ(--(B3:B12 = C3:C12))
Нажмите клавишу ввода.

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

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

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

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

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

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

диапазон1, диапазон2: два диапазона содержат данные, которые вы хотите подсчитать для всех совпадений.

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

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

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

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

Подсчитать все совпадения между двумя столбцами с помощью функций COUNT и MATCH

Комбинируя функции COUNT и MATCH, вы также можете получить количество совпадений между двумя столбцами. Общий синтаксис:

диапазон1, диапазон2: два диапазона содержат данные, которые вы хотите подсчитать для всех совпадений.

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

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

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

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

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

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

диапазон1, диапазон2: два диапазона содержат данные, которые вы хотите подсчитать для всех совпадений.

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

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

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

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

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

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

СЧЁТЕСЛИ:
Функция СЧЕТЕСЛИ – это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.

СЧЕТЧИК:
Функция COUNT используется для подсчета количества ячеек, содержащих числа, или для подсчета чисел в списке аргументов.

СООТВЕТСТВИЕ:
Функция ПОИСКПОЗ Microsoft Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.

ISNUMBER:
Функция ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.

Еще статьи:

Лучшие инструменты для повышения производительности в офисе

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Предположим, вы хотите узнать, сколько раз определенный текст или числовое значение встречается в диапазоне ячеек. Например:

Если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза.

Если столбец содержит "Бьюкенен", "Додсворт", "Додсворт" и "Додсворт", то "Додсворт" встречается три раза.

Существует несколько способов подсчета частоты появления значения.

Используйте функцию СЧЁТЕСЛИ, чтобы подсчитать, сколько раз определенное значение появляется в диапазоне ячеек.

Для получения дополнительной информации см. функцию СЧЁТЕСЛИ.

Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ за одним важным исключением: СЧЁТЕСЛИМН позволяет применять критерии к ячейкам в нескольких диапазонах и подсчитывает количество совпадений всех критериев. Вы можете использовать до 127 пар диапазон/критерий с СЧЁТЕСЛИМН.

Синтаксис для СЧЁТЕСЛИМН:

COUNTIFS(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерий2],…)

См. следующий пример:

Чтобы узнать больше об использовании этой функции для подсчета с несколькими диапазонами и критериями, см. раздел Функция СЧЁТЕСЛИМН.

Допустим, вам нужно определить, сколько продавцов продали конкретный товар в определенном регионе, или вы хотите узнать, сколько продаж на определенную сумму было совершено конкретным продавцом. Вы можете использовать функции ЕСЛИ и СЧЕТ вместе; то есть сначала вы используете функцию ЕСЛИ для проверки условия, а затем, только если результат функции ЕСЛИ равен True, вы используете функцию СЧЁТ для подсчета ячеек.

Формулы в этом примере должны быть введены как формулы массива.

Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива.

Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и более поздних версиях и хотите изменить формулу или создать аналогичную формулу, нажмите F2, а затем нажмите Ctrl+Shift+Enter, чтобы формула вернулась результаты, которые вы ожидаете. В более ранних версиях Excel для Mac используйте +Shift+Enter.

Чтобы примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.

Подробнее об этих функциях см. в разделах Функция СЧЁТ и ЕСЛИ.

В следующих примерах мы используем функции ЕСЛИ и СУММ вместе. Функция ЕСЛИ сначала проверяет значения в некоторых ячейках, а затем, если результат проверки истинен, СУММ суммирует значения, прошедшие проверку.

Примечания. Формулы в этом примере необходимо вводить как формулы массива.

Приведенная выше функция сообщает, что если C2:C7 содержит значения Buchanan и Dodsworth, то функция СУММ должна отображать сумму записей, в которых выполняется условие. Формула находит три записи для Бьюкенена и одну для Додсворта в заданном диапазоне и отображает 4.

Приведенная выше функция говорит, что если D2:D7 содержит значения меньше 9000 долларов США или больше 19 000 долларов США, то SUM должна отображать сумму всех тех записей, в которых выполняется условие. Формула находит две записи D3 и D5 со значениями меньше 9000 долларов США, а затем D4 и D6 со значениями больше 19000 долларов США и отображает 4.

Приведенная выше функция говорит, что если у D2:D7 есть счета для Бьюкенена на сумму менее 9000 долл. США, то функция SUM должна отображать сумму записей, в которых выполняется условие. Формула обнаруживает, что C6 соответствует условию, и отображает 1.

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

Введите следующие данные в электронную таблицу Excel.

Нажмите «Вставка» > «Сводная таблица».

В диалоговом окне "Создать сводную таблицу" нажмите "Выбрать таблицу или диапазон", затем нажмите "Новый лист" и нажмите "ОК".

Пустая сводная таблица создается на новом листе.

В области полей сводной таблицы выполните следующие действия:

Перетащите "Спорт" в область "Строки".

Перетащите квартал в область столбцов.

Перетащите Sales в область значений.

Имя поля отображается как SumofSales2 как в сводной таблице, так и в области значений.

На данный момент панель полей сводной таблицы выглядит следующим образом:

В области "Значения" щелкните раскрывающийся список рядом с SumofSales2 и выберите "Настройки поля значений".

В диалоговом окне "Параметры поля значения" выполните следующие действия:

В поле Суммировать значение по разделам выберите Количество.

В поле «Пользовательское имя» измените имя на «Количество».

В сводной таблице отображается количество записей о гольфе и теннисе в кварталах 3 и 4, а также данные о продажах.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В этом посте рассказывается, как подсчитать количество совпадений между двумя столбцами с помощью формулы в Excel 2013/2016 или Excel office 365. Как сравнить два столбца в двух разных диапазонах или столбцах и подсчитать, если значение в столбце1 находится в столбце 2 в Excel. Вы можете использовать функцию СУММПРОИЗВ для сравнения двух столбцов, она используется для умножения соответствующих компонентов в заданных двух диапазонах или массивах и возвращает сумму этих произведений.

Синтаксис SUMPRODUCS следующий:

=СУММПРОИЗВ(массив1,[массив2]…)

Например, у вас есть данные списка в форматах A2:A6 и B2:B6, и вы хотите подсчитать любые различия между этими двумя диапазонами, и вы можете использовать приведенную ниже функцию СУММПРОИЗВ:

=СУММПРОИЗВ(–(A2:A6=B2:B6))

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

Давайте посмотрим, как работает эта формула:

В приведенном выше примере выражение A2:A6=B2:B6 сравнивает значения этих двух диапазонов и генерирует результат массива с TRUE и FALSE, например:

Если значения ячеек равны и возвращает TRUE, в противном случае возвращает FALSE.

Операция двойного отрицания преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 и возвращает другой результат массива, подобный этому:

Функция СУММПРОИЗВ просто суммирует значения в массиве и возвращает окончательный результат.

Связанные функции

Если вы часто пользуетесь Microsoft Excel, возможно, вы сталкивались с ситуациями, когда вам нужно было добавить или подсчитать ячейки с учетом регистра. Если вы выполняли эту задачу вручную, я хотел бы отметить .

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

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

Обычно мы применяем функцию СРЗНАЧ или соответствующие функции, чтобы получить среднее значение непосредственно на листе Excel. Но в некоторых ситуациях применение только средних релевантных функций не может решить нашу проблему. Иногда мы можем создать формулу с функциями и математическими операциями.

Если мы хотим суммировать числа из диапазона с критериями, мы часто выбираем функции СУММЕСЛИ или СУММЕСЛИМН в качестве первого выбора для создания формулы. Критерии могут быть числом, массивом или набором некоторых .

В повседневной работе, если мы хотим суммировать числа из диапазона и суммировать только числа, равные X или Y в диапазоне, мы можем создать формулу со встроенными функциями Excel для получения результата. .

Иногда нам может понадобиться просуммировать N самых больших чисел или первых N чисел в диапазоне. В этой статье мы покажем вам метод «СУММ самых больших N чисел» с помощью простой формулы, состоящей из СУММПРОИЗВ.

Иногда значения создаются формулами в ячейках. Если мы хотим суммировать значения, созданные формулами из диапазона, но некоторые значения, которые жестко закодированы, также перечислены в том же диапазоне, как мы можем отфильтровать совпадающие .

Иногда нам может понадобиться суммировать первые наименьшие N чисел в диапазоне в Excel. В этой статье мы покажем вам метод «СУММ наименьших N чисел» с помощью простой формулы, состоящей из СУММПРОИЗВ и .

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

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