エクセルの関数について教えてください。

質問

エクセルの関数について教えてください。
年度で４月から翌年３月までの各月のセルに目標値を設定してくれるようにしたいのです。
例えば年間の目標契約数を２３７件（延べ）とします。
４月に５件、５月に７件、６月に９件と契約数を増やしていったとした場合（一度結んだ契約は翌月以降も継続するとします。例では５月には４月からの契約５件と新規２件の計７件ということです）、７月以降に目標までの残り２１６件の契約を自動で振り分けてくれるようにしたいのですが、最終3月が最大値、直近の実績6月が最小値となるようにして、等間隔で増加するよう振り分けるようにしたいのです。例では７月に１２件、８月に１５件・・・３月に３６件と計算してくれるようにしたいのです。
以上よろしくお願いいたします。

回答No.2

kagakusuki · ベストアンサー

今仮にB2セルに年間の目標契約数をキーボード入力し、4行目から15行目にかけての、A列には４月～３月の月の名前が入力されていて、B列にその月の新規契約数、C列に解約数をキーボード入力すると、D列に契約実績、E列に月の目標契約数が自動的に表示されるものとします。

　まず最初に、B1セルを枠線で囲って、A1セルに「年間の目標契約数」と入力して下さい。
　次に、3行目の各セルに、その列の項目名（月、新規契約数、解約数、契約実績、目標契約数）を入力し、A4～A15にかけて４月～３月の月の名前を入力して下さい。
　そして、D4セルに

=IF($B4&$C4="","",SUM($B$4:$B4)-SUM($C$4:$C4))

E4セルに

=ROUNDUP(SUM($B3:$B$4)-SUM($C3:$C$4)+(($B$1-SUM($D3:$D$4)-(SUM($B3:$B$4)-SUM($C3:$C$4))*(12-COUNT($D3:$D$4)))/((12-COUNT($D3:$D$4))*(13-COUNT($D3:$D$4))/2))*(ROWS($D$4:$D4)-COUNT($D3:$D$4)),0)

という数式を入力した後、D4～E4の範囲をコピーして、同じ列の5行目～15行目に貼り付けて下さい。

　後は、B1セルに年間の目標契約数を入力し、各月の新規契約数と解約数を入力して行けば、契約実績と月の目標契約数が、自動的に表示されると思います。（小数点以下の端数は切り上げておりますので、月の目標契約数の合計は、年間の目標契約数よりも多くなります）

　尚、間違いがあるといけませんから、年間の目標契約数と各月の新規契約数と解約数の入力欄に、適当な数字を入力されて、正しい数値が表示されているのか否かを確認して頂く様、御願い致します。

お礼

とても丁寧な説明をしていただき、本当に感謝感謝です。
説明通りにシートを作成すれば望んでいる内容のものができました。
せっかくなので式を理解したいのですが、E列の式の残月数をかけたり割ったりしているところがまったくわかりません(-_-;)
これから頑張って意味を理解したいと思いますが、もしお付き合いいただけるのであればご説明いただけるとありがたいです。
この度は本当にありがとうございました。m(__)m

回答No.1

nag0720

質問内容が不明確です。

例では、４、５、６月が決まっていて、７月から３月までを振り分けるとしていますが、そのパターンだけでいいのですか？

それとも、４月から３月までを振り分けたいのでしょうか？
その場合は、４月の件数（初期値）はどうやって決めますか？

補足

説明不足で申し訳ありませんでした。
４月の時点では１２か月すべて空白です。経過月ごとに実績を入力していくのですが、残りの期間で年間目標に達するための目標を計算してほしいのです。ですから、毎月実績入力する毎に、残り期間が短くなり、未達成の月が続けば、先のノルマが厳しくなるわけです。エクセルでの限界もあるかと思いますので、シートへの計算式の入力は、毎月残りの月のセルに手入力することはかまいません。よろしくお願いいたします。