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

Между двумя столбцами

=СУММПРОИЗВ(—(диапазон1=диапазон2))

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

=СУММПРОИЗВ(—(B5:B11=D5:D11))

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

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

{ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

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

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

—(B5:B11=D5:D11)

После превращения, мы имеем:

{1; 1; 0; 1; 0; 0; 0}

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

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

=СУММПРОИЗВ(—(B5:B11<>D5:D11))

Во всей книге

=СУММПРОИЗВ(СЧЁТЕСЛИ(ДВССЫЛ («‘»&листы&»‘!»&rng);критерии))

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

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(Лист1!B4:AL34; B5))

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон1;диапазон2))

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

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

Контекст

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

Решение

Мы используем в ячейке G4 формулу:

= СУММПРОИЗВ(СЧЁТЕСЛИ (B5:B11; D5:D9))

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

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

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

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

{1; 1; 0; 1; 1; 0; 0}

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

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

Совпадения по строкам

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

Оцените статью
Добавить комментарий