Excelの関数について教えてください

質問

Excel2007を使用しています。
年間の目標数字が決まっていて、それを月々上昇するよう金額を設定し、右肩上がりのグラフを描きたいと思います。
月々の数量を計算できる関数はあるでしょうか？

例えば年間目標を、2014年度（4月～3月）1000個、2015年度1500個、2016年度2000個とします。
2014年3月は50個で終了していたとし、2014年4月は50個以上から開始するとします。
その場合、年間売上は上記になるようにして、３年間（３６カ月）の数字を関数で導き出すことはできますでしょうか？
ずっと右肩上がりとなるグラフにするために、各年4月の数字は前月3月以上の数字にするようにしたいです。

わかりにくくて申し訳ございません。
よろしくお願い申し上げます。

回答No.6

answer119

月々の数量が一定の割合で増加していくとした場合、その増加の割合の計算は、RATE関数を使えば簡単です。
ご提示のケースの場合、次のようにRATE関数を使います。
=RATE(13,-前期期末,,各年度目標値+前期期末)

具体的には次のようにします。
A列に展開するとします。

セルA1 ： 50
セルA2 ： =A1*(1+RATE(13,-$A$1,,1000+$A$1)) ← この式をセルA3～A13まで数式コピーする
…
セルA14 ： =A13*(1+RATE(13,-$A$13,,1500+$A$13)) ← この式をセルA15～A25まで数式コピーする
…
セルA26 ： =A25*(1+RATE(13,-$A$25,,2000+$A$25)) ← この式をセルA27～A37まで数式コピーする

これだけ。
No.4の回答者さんがおっしゃるとおり、各年度間の目標値に関連性がないので、式は年度ごとに分けなければなりません。
そうすると、グラフは、年度内は連続ですが、年度の変わり目では当然、折れ線になってしまいます。
年度の変わり目で折れ線にしたくないとしたら補間という数値計算の方法を利用しなければなりませんが、マクロ組まないとできません（このケースの場合、特に面倒になります）。
各年度目標値や、RATE関数は、ここでは面倒なので式の中に直接書きましたが、それぞれどこかのセルに設定し、そこを参照するようにした方がいいでしょう。

回答No.5

ryo_ Deathscythe（@Deathscythe）

ああ・・・そうか関数か＾＾；

目標は4行目として・・・
A列は「年間目標」とかって文字が入っているだろうから

1年目
B4=55.12
C4=B4+5.12
でD4以降はC4をコピー、小数点以下が気になるなら「数値」で四捨五入若しくは整数に変更しておけばOK

2年目
・・・13個目だから「N列」かな？
N4=M4+6.41
でO4以降はN4をコピー

回答No.4

MackyNo1

>例えば年間目標を、2014年度（4月～3月）1000個、2015年度1500個、2016年度2000個とします。

2014年度が1000個と2015年度が1500個という条件は全く相関性のない数字なので、当然のことですが1つの式で計算できないことになります（2015年度と2016年度分は別の数式で計算します）。

ちなみに、前月と同じ比率で次第に数値が増える計算はちょうど預金利息と同じように「2014年3月が50円で一定の利率で預金を積み上げていき、開始月を含めて、その後の13か月の合計が1000円プラス初期値になる計算をすることになります。
この時の「利率」を計算で求めればよいことになります。
たとえば、このような財務計算をするにはPV関数が利用できます。
=PV(利率,期間,定期支払額,将来価値,支払期日)

ここで期間は13か月、定期支払額は前年3月の値（マイナスで指定）、将来価格は目標額＋前年3月の数字、ということになります。

この時のPV関数の値が「0」になる（すなわち目標額と一致する）利息を求めるにはゴールシークの機能を利用します。

D2セルに「=B2」、E2セルは空白、F2セルは「=E2+1」、G2セルには以下の式を入力します。

=PV(E2,13,-D2,1000+D2)

これらのセルをコピーして2015年3月の欄に貼り付け、PV関数の数値だけを以下のように修正します（2016年分も同様に修正）。

=PV(E5,13,-D5,1500+D5)

このように設定した状態で、「データ」「What If分析」「ゴールシーク」で数式入力セルをG2セル、目標値を「0」、変化させるセルをE2にしてOKします。

上記の操作で4月から翌年3月までの合計が1000になる利率が計算できますので、B2セル以下に、以下の数式を入力して12か月間の合計値が1000になっていることを確認してください（データ範囲を選択して画面下のステータスバーの合計値を確認してください。

B2セルに初期値50を入力し、B3セルに「=B2*$F$2」と入力し、2015年3月までオートフィルします。
同様に2015年4月のB15セルに「=B14*$F$5」と入力し、2016年3月までオートフィルしておきます（2016年度分も同様）。

回答No.3

ryo_ Deathscythe（@Deathscythe）

ああ・・・やっと意味が判った

じゃあ・・・
50x12だと600で目標の1000に400足りないんで
1年目の1月目は55。で5.12の割合で増やしていく。
5.12ｘ78＝399.36

同様に2年目以降は年間500づつ増やすなら6.41の割合となるよう6もしくは7づつ増やせばいい。
6.41ｘ78＝499.98 年間ほぼ500づつ目標が増えるはず

ちなみに78って数字は1から12までを足した数の合計で「その年の何月目か」で掛けて行けばいい
1月目は6.41ｘ1だから6
2月目は6.41ｘ2＝12.82だから繰り上げて13って具合で。
3・・・6.41ｘ3＝19.23→19
4・・・6.41ｘ4＝25.64→26

お礼

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

回答No.2

qwe2010

関数は思いつきませんが、
全体を選んで、書式、セル、　数値を選んで、小数点の桁数を０にします。

A２列に月　前年分の３月から初めてM2列に２０１４年度の３月で終わるようにします。
A３列に50を書き込み
B３列に　＝A3×１．０７６５として書き込み　ますの右下を右方向にドラッグして、M3列まで計算式をコピーしていきます。
O３に　ΣB3、M3で　合計が１０００となります。

A4には、前年度分の3月分＝M３とします、１２１です。
B4列は　A4×1.005となります。　　　　総合計が１５０１となりますので、どこかで調整してください。

B5列は　A5×１．０３９５になります。　総合計が2002となりますので、同じようにどこかで調整してください。

計算式を入れていると、倍率を探すのはそれほどめんどくさくありません。

お礼

ありがとうございました。
関数で・・・と思っていましたが、こういう計算方法もあったんですね。
倍率をどのように計算していただいたのかがわかりませんが
計算していただきありがとうございました。

回答No.1

ryo_ Deathscythe（@Deathscythe）

要するに「年毎に分けないでずっと累計を出す、年間の目標値は前年+500」って事でいいのかな？

だとしたら

1行目→その月の売り上げ個数
2行目→その月と前月までの累計を足した売り上げ個数

だとして1行目はその月の売り上げを入力するだけ。
2行目はC2としたら「C2=IF(C1="","",C1+B2)」
っていれその関数の入ったセルを2行目にコピペすれば完了

あとは3行目にでも年間目標値をいれて2行目と3行目をグラフ表示すれば完成

補足

申し訳ございません。

今回の質問は、
年間の目標数字が決まっていて、それを「月」にわけたい、その数字は毎月上昇する値にしたい、
それを36か月（３年間）という意味でした。

2014年3月が50個だったとして、ずっと上昇させるために、
2014年4月は50個以上で開始し、3月？個までの12カ月の合計が1000個、
2015年4月？個～3月？個の１２カ月間の合計が1500個（毎月上昇）
2016年4月？個～3月？個の１２カ月間の合計が2000個（毎月上昇）
（上昇させるために、4月は前月3月以上の値）

とする場合の計算方法はありますでしょうか？

わかりにくくてすみませんでした。