COUNTIFS и SUMPRODUCT дают разные результаты

Question

Контекст: я работаю над электронной таблицей Excel 2010, обобщая много данных и получая различные результаты, сравнивая содержимое нескольких столбцов. Все детали не важны; Я приведу упрощенный пример проблемы.

У меня есть формулы, которые работают на основе использования SUMPRODUCT. Я считаю, что смогу достичь тех же результатов, используя формулы, основанные на COUNTIFS. Однако, когда я пытаюсь это сделать, формулы COUNTIFS не дают правильных результатов, и я пытаюсь понять, почему это так. Вот пример:

Исходные данные находятся в столбцах A, B и C. Я суммирую на основе столбца A, помеченного как активные дни.

Столбец E представляет собой список различных чисел активных дней, которые могут варьироваться от 1 до 31. Значения в столбцах с F по H представляют собой сводные данные, связанные с каждым числом активных дней. Пример был извлечен из большого набора данных, поэтому не каждое число активных дней показывает данные.

Столбец F - это просто количество записей с таким количеством активных дней.

Столбцы G и H являются одним из примеров производного результата. Столбец G был рассчитан по моей формуле COUNTIFS, столбец H - по моей формуле SUMPRODUCT. Все значения столбца H верны. Значения столбца G иногда совпадают по совпадению, но формула не дает ожидаемого результата.

Я буду использовать формулу SUMPRODUCT, чтобы объяснить расчет. Формулы для последней строки аннотированы на изображении, поэтому я буду ссылаться на них:

=SUMPRODUCT(($B$3:$B$1001<>"")*($B$3:$B$1001<$C$3:$C$1001)*($A$3:$A$1001=E33))

Значения столбца B фильтруются на непустые значения. Записи выбираются там, где дата столбца B предшествует дате столбца C. И он выбирает только те записи, чье значение в столбце A соответствует последнему значению в столбце E.

В этом случае последние четыре записи данных в столбце A соответствуют 31 активному дню, ни одна из них не является пустой в столбце B, поэтому они не исключаются, и ни одна из них не имеет более ранней даты в столбце B, чем в столбце C. Таким образом, результат равен нулю.

Формула для версии COUNTIFS в G33:

=COUNTIFS($B$3:$B$33,"<"&$C$3:$C$33,$A$3:$A$33,E33)

Это явно не отфильтровывает пробелы в столбце B, но остальное - перевод логики SUMPRODUCT в структуру COUNTIFS. Для записей с 31 активным днем в столбце B нет пробелов, поэтому разница не должна иметь значения. Тем не менее, это один из несоответствующих результатов.

Если кто-то захочет просмотреть электронную таблицу, я сохраню ее по этой ссылке Google Sheets под названием Ошибка. Первая вкладка - это большое извлечение фактического файла. Есть несколько вкладок с именем « Not Working , которые представляют собой небольшие подмножества данных, которые я использовал, чтобы попытаться диагностировать происходящее. Приведенный выше пример был извлечен и уменьшен с одной из вкладок « Not Working и включает только один производный столбец; он находится на вкладке под названием Sheet1 .

У меня вопрос, почему версия формулы COUNTIFS не дает того же результата?

Answer 1

Почему COUNTIFS не дает такой же правильный результат, как SUMPRODUCT?

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

Чтобы сравнить диапазон с диапазоном, вам нужно SUMPRODUCT. Вы можете сделать это полностью с SUMPRODUCT, как вы это сделали, или вы можете использовать SUMPRODUCT в сочетании с COUNTIF, чтобы выполнить некоторые ограниченные сравнения, такие как поиск уникальных значений и дубликатов. На Ablebits.com есть хорошее руководство по этому вопросу, в том числе о том, как справиться с множеством распространенных проблем. Охват использованием COUNTIF с SUMPRODUCT начинается примерно посередине страницы.

Еще одна особенность, о которой следует знать, это то, что COUNTIF и SUMPRODUCT не обрабатывают диапазоны одинаково. SUMPRODUCT рассчитывает построчно. COUNTIF действует больше в совокупности. Таким образом, для такой задачи, как поиск совпадающих значений между столбцами, SUMPRODUCT увидит совпадение, только если совпадают значения в той же строке, а COUNTIF увидит совпадение в любом месте диапазона.

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