Excel: попытка вычислить промежуточный итог, а не подсчитать отфильтрованную группу данных.

На чтение 3 мин Опубликовано 09.01.2025

Вопрос или проблема

Я унаследовал электронную таблицу с этой формулой, которая считает отфильтрованный набор данных.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Assignments!$D$4,ROW(AssignmentPosition)-ROW(Assignments!$D$4),0,1)),–(AssignmentPosition=$B19),–(AssignmentEndDate>I$15),–(AssignmentStartDate<=I$15),–(AssignmentProvidingRegion=$E$14))

Я хочу сложить отфильтрованные значения, а не считать их, потому что данные не обязательно являются целыми числами. Когда я меняю часть значения на subtotal(9,Offset…, ничего не возвращается. Есть идеи, что я делаю не так?

Надеюсь, этой информации достаточно, чтобы вы поняли, что я пытаюсь сделать. Заранее спасибо! Брайан

Ответ или решение

Здравствуйте, Брайан!

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

Текущая формула для подсчета:

Этапы изменения формулы для суммирования:

Изменение параметра функции SUBTOTAL: В функции SUBTOTAL номер 3 используется для подсчета количества. Для суммирования необходимо использовать номер 9. Следовательно, в формуле вы корректно начали с изменения SUBTOTAL(3, ... на SUBTOTAL(9, ....
Убедитесь, что OFFSET работает правильно: Функция OFFSET должна быть настроена так, чтобы возвращать правильные диапазоны для ваших данных. Проверьте, что формула OFFSET(Assignments!$D$4,ROW(AssignmentPosition)-ROW(Assignments!$D$4),0,1) корректно выбирает ячейки, которые содержат данные, которые вы хотите сложить.
Попробуйте использовать умножение в SUMPRODUCT: После замены кода в SUBTOTAL формула может не возвращать результат, если части --(...) (двойное отрицание логических проверок) не работают как ожидается. Убедитесь, что логические переменные возвращают нужные булевы значения. Убедитесь, что данные действительно попадают в диапазоны, определенные для AssignmentPosition и других условий.
Диагностика с помощью простого массива: Попробуйте применить функцию суммирования для простого диапазона, чтобы проверить, работает ли SUBTOTAL(9,...) должным образом вне сложной формулы. Это поможет локализовать возможную ошибку, если итоги по отдельным условиям не складываются.
Оптимизация формулы: Если у вас по-прежнему ничего не происходит, попробуйте поработать с EVALUATE FORMULA (Оценить формулу) в Excel, чтобы поэтапно просмотреть выполнение формулы и найти, где именно идет сбой.

Соблюдая данные шаги, вы сможете наладить нужное поведение вашей формулы. Убедитесь, что все ссылки на ячейки корректны и охватывают нужные записи. Это должно помочь устранить проблему несуммирования. Если у вас есть еще вопросы, пожалуйста, дайте знать, и мы рады помочь.

С уважением,

Ваш эксперт по IT