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

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

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

В паре 1, диапазон B:B (вводится в полной ссылки на колонке) и критерием является B5. Сама по себе эта пара будет рассчитывать счетчик каждого значения в столбце B.

В паре 2, диапазон C:C, а критерием является C5. Сама по себе эта пара будет возвращать счетчик каждого значения в столбце C.

Поскольку обе пары появляются в той же функции СЧЁТЕСЛИМН, они связывают значения в столбце B с теми, что в колонке C, и СЧЁТЕСЛИМН генерирует подсчет каждой комбинации B / C, которая появляется в таблице.

Предисловие

Это досадно длинное введение, но контекст имеет важное значение, извините!

Если вы пытаетесь подсчитать очень длинные числа (16+ цифр) в диапазоне с СЧЁТЕСЛИ, вы можете увидеть неправильные результаты, из-за ошибки в том, как определенные функции обрабатывают длинные числа, даже если эти числа сохраняются в виде текста. Рассмотрим приведенное ниже изображение. Все счетчики в колонке D являются неправильными - Хотя каждое число в столбце B является уникальным, количество, рассчитанное СЧЁТЕСЛИ предлагает эти цифры, являющиеся дубликатами.

Эта проблема связана с тем, как Excel обрабатывает числа. Excel может обрабатывать только 15 символов в числе, и если вы вводите число с более чем 15 цифр в Excel, вы увидите, что завершающие цифры молча преобразуются в ноль. Проблема подсчета, упомянутая выше, возникает из этого лимита.

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

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

Решение

Формула использует диапазон B5: B9 и генерирует правильное количество для каждого числа с СУММПРОИЗВ.

Во-первых, выражение внутри СУММПРОИЗВ сравнивает все значения в диапазоне со значением из столбца B в текущей строке. Результатом будет массив ИСТИНА/ЛОЖЬ.

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

Формула массива

Вы можете также использовать функцию СУММ вместо СУММПРОИЗВ, но это формула массива и должна быть введена с Ctrl + Shift + Enter:

Другие функции с этой проблемой

Я не проверял это сам, но кажется, что некоторые функции имеют те же проблемы, в том числе СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН.

Если у вас есть список пунктов, и нужно подсчитать, общее количество, сколько продано, сколько осталось и т.д., вы можете использовать функцию СЧЁТЗ. Это может быть полезно, если вы продаете билеты, количество мест, записей или что-нибудь, где вы поддерживаете и отслеживаете перечень проданных товаров.

В примере, формула в F7 является:

Функция СЧЁТЗ подсчитывает непустые ячейки, которые содержат числа или текст. Первый СЧЁТЗ подсчитывает непустые ячейки в диапазоне B5: B11 и рассчитывает число 7:

Вторая функция СЧЁТЗ делает то же самое с диапазоном C5:C11 и рассчитывает 3, так как есть 3 непустые ячейки в этом диапазоне:

Таким образом, вся формула сводится к 7 - 3 и рассчитывает 4.

Обратите внимание, что в этом случае значения, которые появляются в колонке C не имеет значения. Они могут быть кодами из столбца B , словом "да", или просто "х".

Тест совпадений

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