Excelで積立額と満期月から、月毎の積立金残高を計算する方法

質問

こんばんは。

例えば、A列に費目、B列に満期月、C列に積立額があります。
A列　　B列　　C列
積立1　　1月　　1,000円
積立2　　5月　　2,000円
積立3　　9月　　3,000円

1～12月の各月ではいくら積立金があるか知りたいのですが、どのような方法があるでしょうか。
満期月は12か月分、その翌月は1か月分とします。

例の場合ですと、
　1月=1,000円×12ヶ月+2,000円×8ヶ月+3,000円×4ヶ月=40,000円
　2月=1,000円×1ヶ月+2,000円×9ヶ月+3,000円×5ヶ月=34,000円
　・・・・
　12月=1,000円×11ヶ月+2,000円×7ヶ月+3,000円×3ヶ月=34,000円

よろしくお願いします。

回答No.2

picklse · ベストアンサー

Ａ列　　Ｂ列　　Ｃ列
１　種別　　満期月　積立額
２　積立１　　　1　　1,000
３　積立２　　　5　　2,000
４　積立３　　　9　　3,000
５
６　計算月　　　2
７　積立計　34,000

上記のように1行の式にすることも可能です。私ならば配列数式を使います。

セルB7には次の式を入力し、ShiftキーとCtrlキーを同時に押しながら、Enterキーで式を確定します。
=SUM(((B2:B4>=B6)*12+B6-B2:B4)*C2:C4)

式の確定後次のように数式が{と}で囲まれた状態になります。
{=SUM(((B2:B4>=B6)*12+B6-B2:B4)*C2:C4)}

配列数式の説明をすると長くなりますので割愛しますが、インターネットで検索すれば情報があると思います。

なお、数式を編集した場合は、必ずShiftキーとCtrlキーを同時に押しながら、Enterキーで式を確定するように気をつけてください。式が{と}で囲まれた状態になっていないと、配列数式での計算とならないので答えが違ってきます。

お礼

picklseさん

こんばんは。ありがとうございます。
配列数式は知りませんでした。
早速インターネットで調べてみました。
便利な機能ですね。
これから色々と活用したいと思います。

ありがとうございました。

回答No.1

picklse

こんな感じでしょうか。

　　Ａ列　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列
１　種別　満期月　積立額
２　積立１　1　　　1,000
３　積立２　5　　　2,000
４　積立３　9　　　3,000
５
６　月　　積立１　積立２　積立３　合計
７　1
８　2
９　3
10　4
：　：　（省略）
18　12

計算しやすくするために、月は数値で入力しておきます。
次の計算式を入力します。
・集計表の積立１の1月分(B7)
　=(($B$2>=$A7)*12+$A7-$B$2)*$C$2
・集計表の積立２の1月分(C7)
　=(($B$3>=$A7)*12+$A7-$B$3)*$C$3
・集計表の積立３の1月分(D7)
　=(($B$4>=$A7)*12+$A7-$B$4)*$C$4
・集計表の合計(E7)
　=SUM(B7:D7)
これらの式をそれぞれ18行までコピーします。
積立額を乗算する部分を削除すると、それぞれの月に何月分積み立てているかが計算されます。

お礼

picklseさん

こんばんは。
早速の回答ありがとうございます。
一つの計算式でやろうと思って、苦戦してました。
教えていただいたように、月毎の計算をした後に、合計すれば
シンプルにできますね。

ありがとうございました。