Контекст: я работаю над электронной таблицей 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 не дает того же результата?