☰

如何對 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, sum = 13
如果A1是 9，則加A2:A4, sum = 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))

我有一個第一部分的公式......

然後我希望公式在達到閾值後在下一個單元格中添加值的百分比：

如果A1是 10，A2:A5則應新增 的儲存格，再加上 的 50% A6；總和 = 16

答案1

這最簡單的做到這一點的方法是在該單元上使用您已經找到它的間接方式 -

=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))

我認為第二部分可能會更好，或者可以比第一部分更好，但我還沒有弄清楚。

相關內容