Вопрос или проблема
Я пытаюсь сочетать функции SUBTOTAL и SUMIF, чтобы получить итог, который учитывает ТОЛЬКО положительные числовые строки в столбце I, и чтобы этот итог разбивался по номеру проводки в столбце F. Функция SUBTOTAL возвращает мне значение ноль, потому что проводки уравновешены, поэтому мне нужна формула, которая позволит мне подсчитать только положительные суммы с помощью SUMIF или чего-то подобного, чтобы затем я мог сортировать строки по значению. У меня есть формулы ниже. Я пытался комбинировать их различными способами, но безуспешно. Есть ли какие-нибудь предложения?
=SUBTOTAL(9,I2:I15)
=SUMIF(I2:I15, “>0”)
Конечная цель – оценить, сколько проводок у меня есть между определёнными величинами. Проводок тысячи, поэтому я не хочу добавлять формулы строка за строкой, так как это займёт вечность. Я хотел бы иметь возможность скрыть все строки, кроме тех, которые имеют “итог” (который я получил из функции SUBTOTAL), добавить формулу в одну строку, которая объединяет итог из SUBTOTAL и SUMIF, а затем скопировать её вниз для остальных проводок. Проводки имеют разное количество строк.
Ответ или решение
Для решения вашей задачи по объединению функций SUBTOTAL и SUMIF в Excel, чтобы подсчитывать только положительные суммы в колонке I, сгруппированные по номеру журнальной записи в колонке F, вам необходимо использовать несколько функций и подходов. Разберём это подробно.
Задача
Вы ищете способ подсчитать положительные численные значения колонки I, причем по каждой записи в колонке F, одновременно используя функции SUBTOTAL и SUMIF. Вы хотите, чтобы итоговое значение было динамическим и работало с отфильтрованными данными (то есть, если вы скрываете строки, итог все равно будет корректно пересчитан).
Решение
- Использование функции SUMIFS:
Чтобы получить сумму только положительных значений по каждой записи в колонке F, вы можете использовать функцию SUMIFS. Эта функция позволяет суммировать значения, основываясь на нескольких критериях.
Вот общий шаблон формулы:
=SUMIFS(I2:I15, I2:I15, ">0", F2:F15, F2)Это означает, что вы суммируете все значения из диапазона I2:I15, которые больше нуля, и соответствуют значению в F2.
- Использование функции SUBTOTAL с фильтрацией:
Допустим, у вас есть значения, которые вы хотите подсчитать по определенной группировке. SUBTOTAL может быть использован для динамического подсчета видимых значений. Чтобы интегрировать это с SUMIFS, вам придется создать отдельный столбец для подсчета и фильтрации.
- Интегрирование обоих решений:
Чтобы собрать все в одну формулу, воспользуйтесь дополнительным критерием фильтрации для SUMIFS, что позволит работать только с видимыми значениями. Для этого вы можете использовать функции AGGREGATE совместно с SUMIFS, чтобы обойти некоторые ограничения.
Пример формулы, которая может помочь:
=SUMPRODUCT(SUBTOTAL(109, OFFSET(I2:I15, ROW(I2:I15)-MIN(ROW(I2:I15)), 0, 1)), --(I2:I15>0), --(F2:F15=F2))Где:
SUBTOTAL(109, ...)— это 109, потому что 9 используется для расчета суммы видимых значений, а 1 — для игнорирования скрытых строк.OFFSET(...)— позволяет обрабатывать каждый элемент в диапазоне I2:I15.--(I2:I15>0)— проверяет на положительные значения.--(F2:F15=F2)— позволяет фильтровать значения по номеру ваших журнальных записей.
Применение формулы
- Скопируйте приведённую формулу в первую ячейку нового столбца.
- Протяните формулу вниз для остальных записей.
- Убедитесь, что ваши данные отфильтрованы или скрыты так, как вам нужно.
Заключение
Таким образом, с помощью объединенных функций SUMIFS и SUBTOTAL, вы сможете динамически подсчитывать положительные значения с учётом определённых группировок в Excel. Это позволяет значительно упростить анализ данных, так как вы избегаете необходимости ручного подсчёта или добавления множества формул. Если у вас возникнут дополнительные вопросы по этому процессу, не стесняйтесь обращаться за помощью.