☰

Excelで指定された合計に達するまで値を合計し、列の次のセルを追加する方法

tag-icon • tag-icon microsoft-excel worksheet-function
Excelで指定された合計に達するまで値を合計し、列の次のセルを追加する方法

1 つのセル ( ) にしきい値を設定しA1、それを基準にしてセルを追加したいと考えています。

私が

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

A1(しきい値)に基づいてセルを追加したい。

A1が10の場合は、 を加算してA2:A5合計は13になります。 が9の
場合は、 を加算して合計は9になります。A1A2:A4

SUM式は

=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、 のセル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))

2 番目の部分はもっと良くなるか、最初の部分を基にして機能すると思いますが、まだわかりません。

関連情報