月を指定すると自動的に計算される実績表を作りたく、関数を教えて下さい。

【教えて頂きたい関数概要】
◎教えて頂きたい関数は全部で3種類です。何卒宜しくお願い致します。


①期間累計合計額
・A1セルで例えば、４月を選択したとします。
・①のセルに、４月までの合計金額が表示される計算式についてご指導ください。①－２は売上予算内で選択した月までの合計が計算される関数をお願いします。


②単月達成率
・A1セルで例えば、４月を選択したとします。
・自動的に４月の実績÷予算(D5/N5)で達成率を割り出す関数をお願いします。A1で５月を選択すれば、同じく５月の実績÷予算(E5/O5)を計算する様にお願いします。

③単月売上原価率
・A1セルで例えば、４月を選択したとします。
・自動的に４月の売上原価実績÷４月の売上実績（D9/D5)、５月を選択すれば、同じく（E9/E5)となる様に関数をお願いします。
・③－２は、売上原価予算の枠内で同じ様な形で計算する関数をお願いします。

No.2 ベストアンサー 回答者： tom04 回答日時： 2015/02/01 11:50

こんにちは！

↓の画像の黄色いセル（N列やO列が記載されている）に関しては画像にありませんし、
そのままの数式で大丈夫だと思いますので、処理していません。

簡単な方法として「1月～6月」と入力されているセルと（A1セルも含む）を文字列ではなく
セルの表示形式を変えて数値のみの入力にしてみてはどうでしょうか？
Ctrlキーを押しながらA1、A4:F4、A8:F8・・・と「○月」となっているセルを選択します。
選択されたセル上で右クリック　→　セルの書式設定　→　表示形式タブで「ユーザー定義」を選択　→　G/標準　と表示されているところを消去　
0月　
と入力しOK
その後、「○月」のセルには数値のみを入力すると　「4月」のような表示になります。

以上の下準備ができた上での一例です。
Excel2007以降のバージョンをお使いだとすると
G5セルに
=IFERROR(SUM(INDIRECT(ADDRESS(ROW(),1)&":"&ADDRESS(ROW(),A$1))),"")
という数式を入れます。
次にG5セルの四辺のどこかへマウスポインタを移動し、小さな上下左右の矢印になったところで
Ctrlキーを押しながら　
G9セルへドラッグ　→　同様に　G13セル・G17セルへとドラッグします。
これで　①　と　①-2　への数式は大丈夫だと思います。

次にI5セルは
=IFERROR(INDEX(A5:F5,,A1)/INDEX(A9:F9,,A1),"")
H13セルに
=IFERROR(INDEX(A13:F13,,A$1)/INDEX(A$5:F$5,,A$1),"")
という数式を入れH13セルの数式を前述と同じ方法で
Ctrlキーを押しながらH17セルへドラッグします。

これで画像のような感じになります。m(_ _)m

この回答へのお礼

有難うございます！

始めて質問投稿したのですが、本当に画像が小さくて、見にくい質問投稿にも関わらず、大変丁寧なご回答、有難うございました。大変助かりました。

朝から頑張って、何とか先ほど終える事が出来ました。

今後も頑張ってレベルアップします！

お礼日時：2015/02/01 17:46

No.3 回答者： WindFaller 回答日時： 2015/02/01 14:36

こんにちは。

表だけみて作りました。この場合、自分で考えたほうが早いと思ったからです。画像も、拡大してもボケていて、はっきり読めません。しかし、それゆえ、考え違いが存在しているかもしれません。数式の考え方自体は間違いはないはずですし、検算もしました。

売上原価率（％）＝（売上原価÷売上高）×100
売上達成率（％）＝(売上高÷予算)×100

※小数点第2位までで、丸めることにしました。
パーセンテージの表示は、書式を用いてください。
-----------------------------
A1の[4月]は、数字でも、文字で、4月と書いてもよいですが、半角の数字をお使いください。

また、年初が4月の場合、[ =MOD(A1+8,12)+1 ] とすれば、列数が出てきますから、どこでもよいので、その数式から、月数を参照するようにしてください。(数式の中の[8]は、[12-4=8]で、+1は、0を出さないためです。また、この表には、売上の前年対比がでていないようですが、同じ要領です。

-----------------------------
売上実績
　　G 　　　　　H　　　　　　　　　I
期間累計　　　期間累計達成率　　　単月達成率
G5:
=IF($A$1<>"",SUM(OFFSET($A5,,,,SUBSTITUTE($A$1,"月",""))),"")
H5:
=ROUND(G5/G9,2)

I5:
=ROUND(OFFSET($A5,0,SUBSTITUTE($A$1,"月","")-1)/OFFSET($A9,0,SUBSTITUTE($A$1,"月","")-1),2)

売上予算
　G9:
=IF($A$1<>"",SUM(OFFSET($A9,,,,SUBSTITUTE($A$1,"月",""))),"")

売上原価実績
期間累計　　　期間累計達成率　　　単月達成率
G13:
=IF($A$1<>"",SUM(OFFSET($A13,,,,SUBSTITUTE($A$1,"月",""))),"")
H13:
=ROUND(OFFSET($A13,0,SUBSTITUTE($A$1,"月","")-1)/OFFSET($A5,0,SUBSTITUTE($A$1,"月","")-1),2)
I13:
=ROUND(G13/G5,2)

売上原価予算
期間累計　　単月売上原価率　　期間累計売上原価率
G17:
=IF($A$1<>"",SUM(OFFSET($A17,,,,SUBSTITUTE($A$1,"月",""))),"")
H17:
=ROUND(OFFSET($A17,0,SUBSTITUTE($A$1,"月","")-1)/OFFSET($A5,0,SUBSTITUTE($A$1,"月","")-1),2)

I17:
=ROUND(G17/G9,2)

なお、数式の考え方はお分かりなると思います。私は、Offset 関数を用いて範囲を出すことも、Offset関数は、単独のセルも参照可能です。

なお、「教えて!goo」がリニューアルしなかったら、私は、今回のような質問には書かなかったと思います。

この回答へのお礼

本当に有難うございました。

今回、朝から何とか頑張って、頂いたアドバイスをシコシコとコピペをして、何とか数値をいじって、表を完成させました！

今週の会議で出せます。本当に有難うございました！

お礼日時：2015/02/01 17:44

No.1 回答者： hallo-2007 回答日時： 2015/02/01 10:49

画像が小さいので　１のみです。

仮に　B1セルに
=MATCH(A1,4:4,FALSE)
と入れると４月が何列目にあるか表示されると思います。
後はINDEX関数で
=INDEX(A5:G5,,B1)
としておけば、A1セルの値に応じて変化するでしょう。
これが理解できたら、２、３も同様に出来ます。

この回答へのお礼

有難うございます。
変数を確定させ、後は得たい内容で、足し算or割り算の違いとなると思うので何とかやってみます。

足し算の方は何とか出来そうなのですが、割り算が難しそうなので、まだ時間がかかりぞうです。。。

お礼日時：2015/02/01 11:22