エクセル関数で自動的に値を振りたいのですが・・・。

質問

この表はB3セルに現在予算額、B4セルに周期、B5セルに初回実施年度を入力することによって
実施予定年度に現在予算額に上昇率を掛けた値が振られるようにしてあります。
問題となるのが積立額の列にあるようにそれぞれの予算額を周期で按分した値として自動的に振りたいのですが
そのような数式はありますでしょうか？
なお、B列の入力値は見直しによって変動します。

　A 　　B 　 　　C 　 　　D 　　　E　 　　F　 　　G　 　　H　 　　I 　　　J　 　　K　 　　　L　 M ・・・・
1 　　　　　　　　　　　　　2004　 2005　 2006　 2007　 2008　 2009　 2010 　2011 　2012 　2013
2上昇率 0.01 　年度 1年目 2年目 3年目 4年目 5年目 6年目　 7年目　 8年目　 9年目　 10年目・・・・
3予算　500000 予算 _____________________________________530000__________________550000 ・・・・
4周期　　3　年　積立額 ____0 ____0 176700 176700 176700 183400 183400 183400 190000 190000
5初年度　2008　残高 ____0 ____0 176700 353400 _________0 183400 366800 ________0 190000 380000

表が見づらくて大変申し訳ないのですが・・アンダーバーは意味ありませんので・・・。
D2以降の予算列には　IF(D$2＞＄B5-1,IF(MOD((D$2-＄B5),$B$4)=0,INT($B$3*(1+$B$2)^(D$2-1))と数式を入れて
実施予定年度に予算を振るようにしています。

回答No.1

noname#52504 · ベストアンサー

ご質問のデータを再現してみましたが…
D3に質問文の数式を入れて右方にフィルしても、
質問文と同じデータが返らないのは私の勘違いでしょうか？？

それはさておき、第３行目、予算行は解決済みなのですね。
第３行目が正しく入力されているのであれば、

D4セル：
　=ROUNDUP(SUM(OFFSET(D3,0,0,1,$B4))/$B4,-2)

として右方にフィルすれば、基本的には質問文とほぼ同様のデータが返ります。
(Excel2003で動作確認済)
※自セルの直上のセルから"周期"分だけ右に伸ばしたセル範囲の和を取って、
　"周期"で割って、百の位で切り上げ］

ただ、予算額を利用している都合上、
9，10年目の積立額は、11年目の予算が決まるまで返りません。

例えば10年目までで表が切れる場合も9，10年目の積立額を表示したいということであれば、
予算額の決定方法について補足をお願いします。

お礼

大変ありがとうございます。問題の解決になりました。実はOFFSET関数の存在を知って、いろいろ試行錯誤していたのですが・・・こういう使い方をするんですね。大変参考にというよりそのものズバリでした。
D3列に入っている数式ですが、自分で打ち込んだ質問分の作表で自分でセル番号を間違っていました。
正確には
IF(D$１＞＄B5-1,IF(MOD((D$１-＄B5),$B$4)=0,INT($B$3*(1+$B$2)^(D$2-1))
です。
ちなみに9年目、10年目についても表現の仕方が悪かったようで、「このように続くよ」という意味でした。当然将来予算額がない(年度を作表していない）ものは積立額もありません。