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

Если вы хотите сравнить два столбца и подсчитать совпадения в соответствующих строках, вы можете использовать функцию СУММПРОИЗВ с помощью простого сравнения двух диапазонов. Например, если у вас есть значения в B5:B11 и D5:D11, и вы хотите рассчитать любые различия, вы можете использовать эту формулу:

Функция СУММПРОИЗВ является универсальной функцией, которая обрабатывает массивы изначально без какого-либо специального синтаксиса массива. Ее поведение просто: она размножается, а затем подводит произведение массивов. В показанном примере выражение B5: B11 = D5: D11 будет генерировать массив, который содержит истинные и ложные ценности:

Обратите внимание, что у нас есть 3 Истинных значения, потому что есть 3 совпадения.

В этом состоянии, СУММПРОИЗВ фактически рассчитывает нулевое значение, так как истинные и ложные значения не учитываются как числа Excel по умолчанию. Чтобы получить СУММПРОИЗВ для представления ИСТИНА, как 1 и ЛОЖЬ как ноль, нам нужно «превратить» их в числа. Двойное отрицание является простым способом сделать это:

При отсутствии других массивов для умножения, СУММПРОИЗВ просто суммирует значения и рассчитывает 3.

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

Во всей книге

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

Количество всех совпадений в двух диапазонах

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

Предположим, что у вас есть какой-то список, а также есть еще один список, содержащий некоторые из элементов первого списка. Вы нужна формула, которая анализирует значения во 2м списке, чтобы узнать, сколько из них появляются в первом списке. Вам не интересен порядок элементов — вы просто хотите знать, сколько элементов в списке 2 появится в списке 1.

Обратите внимание, что эта формула не заботится о местонахождении или порядке элементов в каждом диапазоне.

Функция СЧЁТЕСЛИ будет считать значения в диапазоне, которые соответствуют вашим критериям. Как правило, вы бы использовали СЧЁТ для диапазона A1: A10 и простой критерий, как «> 10». СЧЁТЕСЛИ затем рассчитывает количество ячеек в диапазоне A1: A10, которые больше, чем 10.

Однако, в этом случае, мы используем СЧЁТЕСЛИ для диапазона критериев. Мы не используем никаких логических операторов, а это значит, СЧЁТЕСЛИ будет проверять эквивалентности (т.е. он ведет себя так, как если бы мы использовали равенства (=) оператора).

Потому что мы даем СЧЁТЕСЛИ диапазон (также называемый «массив»), который содержит 7 пунктов, СЧЁТЕСЛИ рассчитывает массив из 7 элементов в качестве результата. Каждый элемент в массиве результатов представляет собой подсчет. В примере, это массив, который рассчитывает СЧЁТЕСЛИ выглядит следующим образом:

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

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

Если вы хотите сравнить два диапазона или столбца, и хотите считать совпадения на уровне строк (то есть рассчитывать только совпадения, когда тот же пункт появляется в том же положении), вы будет нужна другая формула.

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

Как посчитать количество уникальных значений в Excel

Когда таблицы большие и многое происходит автоматически, легко пропустить что-то важное. От этого спасает функция выделения цветом. Для ее активации на главной вкладке выберите «Условное форматирование» и задайте условия и цвет выделения.