ＥＸＣＥＬでの期待値までの集計方法について

質問

Ａ１～Ａ１００のセルには整数値が入っています。
Ａ１、Ａ２と順に加算し、その合計値がＢ１の値を超えたとき、Ａ列のセル（アドレス）と合計値を求めたいのです。
マクロ等の知識がありませんので、ご指導のほどよろしくお願いします。
（例）
Ａ１：１、Ａ２：２、Ａ３：３、Ａ４：４　Ｂ１：５
求めたい結果：Ａ３、６

回答No.1

arukamun · ベストアンサー

B2セルに

=IF(AND($B$1>SUM($A$1:$A1),$B$1<=SUM($A$1:$A2)),"A"&ROW()&" "&SUM($A$1:$A2),"")

をコピペして、選択したセルの右下の■をB5までドラックすると、B3セルに

A3 6

と表示される様にしましたが、こんな感じではダメですか？

それとも、特定のセルに表示させたいのであれば、もっと別な方法を考えますが。

お礼

お礼が遅くなり申し訳ありません。
シンプルで明快な方法を、ありがとうございました。
早速、活用させていただきます。

回答No.3

imogasi

Vlookup関数（TRUE型）の応用で解いてみました。
範囲を検索するのが得意な関数だからです。少数点付き数だと思うようにならないかも知れませんが（下記注参照）、金額、個数とうならOKです。
式の複写が累積和の列だけと言う良い点があります。
（１）A列にデータがあるとして、B列に移します。
移す理由は、VLOOKUP関数の検索する表の値は、最左列にないといけないので、累積和(下記（２）の数）をA列に持ってくるため、明渡します。
（２）B1からの累積和が一定値を超えたかを問題にしているので、=SUM($B$1:B1)　をA1に入れ、データ最下行まで複写します。
これでB1からその行までの累積和の値がA列に表示され（求まり）ます。
（３）C行を１列使います。C1に１を入れ、C2に２といれ、C1:C2を範囲指定して、オートフィル機能で連番が振れます。これでC1以下の行に行数の数を入れます。
この列を作る理由は、VLOOKUP関数ではROW(・)の値をを直接返させることは出来ず、具体的な行数の値を表の中に持っていて、その列を指定して、採ってくるよりほか無いからです。
（４）検索値はE1に置くものとします。本件では検索はこの一つの値だけです。E1はどこでも良く、下記（５）の関数式が変るだけです。
（５）求める結果の行数（第何行目でE1を越えるか）をセットするセルを
仮にF1とします。F1もデータのある場所以外ならどこでも良い。
F1セルの関数式は=VLOOKUP(E1,$A$1:$C$5,3,TRUE)+1
越えると言う条件のため＋１した。
この例では、今はC5（5行）までしかデータが無い場合の式ですが、$c$5
の５を最終行の行数に直してください。
（６）後は、A列において、F1セルの示す行の値（その行までの和）を取れば良いので、=OFFSET($A$1,F1-1,0)とG1に入れれば良い。－１するのはOFFSETがズレ行数を指定する約束なので、例えば３行目は、ズレは２になります。
(注）
VLOOKUP関数は、第４引数を省略すると、TRUE型と見なされる。
検索値が実数（小数点以下付き）の時は、切り捨てられる。
VLOOKUP関数のTRUE型は
　　　100 x
200 y
300 z
・・・・・
と表があるとき、検索値を１００とすると、「検索値１００があればその行のｘを選ぶ」。検索値を２１１とすれば、表にズバリ２１１は無いが、無ければ検索値２１１を越えない表の値の１００、２００のうち最大値２００のｙを選ぶ」が採られると言うクセがあるので注意が必要です。

お礼

お礼が遅くなって申し訳ありません。
懇切丁寧なご説明をいただき、よく理解できました。
ありがとうございました。

回答No.2

nishi6

Ａ　　Ｂ　　　　Ｃ　　　　　　　　　Ｄ　　　　　Ｅ　　Ｆ　　　Ｇ
__1　　　１　　５　最初に越えたセル　最初に越えた時の計　　　　式１ 　式２
__2　　　２　　　　　　式３　　　　　　　　　式４　　　　　　　↓　　　↓
__3　　　３　　　　　　　　　　　　　　　　　　　　　　　　　コピー　コピー　　　　
__4　　　４
　：　　　： 　　　　　　　　　　　　　　　　　　　　　　　　　↓　　　↓
　：　　　： 　　　　　　　　　　　　　　　　　　　　　　　　　↓　　　↓
100　１００

●上のような表で、
　(式1)F1は = SUM($A$1:A1)
　(式2)G1は =IF($B$1<F1,ROW(),"")
　 この式をF2:F100、G2:G100にコピーします。
　(式3)C2は ="A"&MIN(G1:G100)
　(式4)D2は =INDIRECT("F"&MIN(G1:G100))

　A列の総計を超えてB1に入力する場合は、下の式に変えてください。
　　C2は =IF(MIN(G1:G100)<>0,"A"&MIN(G1:G100),"なし")
　　D2は =IF(MIN(G1:G100)<>0,INDIRECT("F"&MIN(G1:G100)),"A列の計は"&SUM(A1:A100))
　F、G列を使ってみました。不要なら非表示にして下さい。

●ユーザファンクションの例です。

C2に =CyoukaCell(B1)
D2に =CyoukaTotal(B1)　のようにして使います。他の設定（F、G列）はいりません。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

ここから
↓
'// セル位置
Function CyoukaCell(supDt As Double)
　　Dim rw As Integer　　　'// 行カウンタ
　　Dim TTL As Double　　　'// 合計

　　CyoukaCell = "なし"
　　For rw = 1 To 100
　　　　TTL = TTL + Cells(rw, 1)
　　　　If supDt < TTL Then
　　　　　　CyoukaCell = "A" & rw: Exit Function
　　　　End If
　　Next
End Function

'// 合計
Function CyoukaTotal(supDt As Double)
　　Dim rw As Integer　　　'// 行カウンタ
　　Dim TTL As Double　　　'// 合計

　　CyoukaTotal = "A列の計は" & Application.Sum(Range("A1:A100"))
　　For rw = 1 To 100
　　　　TTL = TTL + Cells(rw, 1)
　　　　If supDt < TTL Then
　　　　　　CyoukaTotal = TTL: Exit Function
　　　　End If
　　Next
End Function

お礼

お礼が遅くなり申し訳ありません。
ご親切に説明いただき、ありがとうございました。
今後のレベルアップの糧にさせていただきます。