☰

как суммировать значения в excel, пока они не достигнут указанной суммы, а затем добавить ячейку, следующую в столбце

tag-icon • tag-icon microsoft-excel worksheet-function
как суммировать значения в excel, пока они не достигнут указанной суммы, а затем добавить ячейку, следующую в столбце

Я хочу иметь пороговое значение в одной ячейке ( A1) и использовать его в качестве ссылки для добавления ячеек.

Предположим, у меня есть

A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6

Я хочу добавить ячейки на основе A1(Порог).

Если A1равно 10, то прибавляется A2:A5, сумма = 13.
Если A1равно 9, то прибавляется A2:A4, сумма = 9.

Формула СУММ:

=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1,1))

У меня есть формула для этой первой части...

Затем я хочу, чтобы формула добавляла процент от значения в следующей ячейке после достижения порогового значения:

Если равно 10, то следует прибавить A1ячейки из , плюс 50% от ; сумма = 16A2:A5A6

решение1

TheпростейшийДля этого можно использовать косвенный метод на этой ячейке, как вы уже нашли -

=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))
<$A$1)*ROW(1:99),,))+1,1),
(0.5)*INDIRECT("A"&MAX(INDEX((SUBTOTAL(9,OFFSET($A$2,0,0,ROW(1:99),1))  
<$A$1)*ROW(1:99),,))+3))

Я думаю, что вторая часть могла бы быть лучше или развиваться на основе первой части, но я пока не придумал.

Связанный контент