エクセルでマクロを使用しない月別累計

質問

エクセル2010です。

A　B　C　D　E　F　G　H　I　・・・・
計画合計　実績合計　前年実績合計　4月計画　4月実績　前年4月実績　5月計画　5月実績　前年5月実績　・・・・
=D+G　=E+H　=F+I　1000　1500　1000　2000　2500　2000

月が変わるごとにそれぞれの合計欄に【例えば計画合計なら=D+G…】と1ヶ月ずつ追加しています。
これは4月～3月までの累計になります。

これを計画合計欄に9月と入力したら、4～9月計画合計値が出るようにするには
どうしたらいいのでしょうか？

いい案がありましたら教えて下さい。
よろしくお願い致します。

回答No.2

MackyNo1 · ベストアンサー

例示のレイアウトで、Ａ3セルに集計対象の最終月を「5」のように入力してＡ２セルにその月までの計画合計を集計したいなら以下の数式になります。

=SUMPRODUCT(($D$2:$AM$2)*(MOD(COLUMN($D$2:$AM$2),3)=1)*(COLUMN($D$2:$AM$2)<=(($A$3<4)*12+$A$3-2)*3))

ちなみに実績合計の計算式は、「MOD(COLUMN($D$2:$AM$2),3)=1」の「1」を「2」に変更してください。

お礼

上記、試してみたところ希望通りにできました！
ありがとうございます。助かりました。

回答No.5

MarcoRossiItaly

No.4 です。すみません、ケアレスミスしました。

1 月スタートの場合は No.4 の数式でよいのですが、4 月スタートだと、年をまたぐ場合に正しく計算しないのでした。次式に差し替えていただけば、年をまたいでも問題ありません。SUMIFS 関数の他に、SUMIF も併用します。失礼しました。

B15　=if($I1<=$I2,sumifs(b3:b14,$A3:$A14,">="&$I1,$A3:$A14,"<="&$I2),sumif($A3:$A14,">="&$I1,b3:b14)+sumif($A3:$A14,"<="&$I2,b3:b14))

回答No.4

MarcoRossiItaly

様々な表の作りと、それぞれの作りにおいて、様々な手法があると思います。表の構造は、できるだけ人間が見やすく、Excel にとっても計算しやすいものを考えることをまずはお勧めします。以下は、そういう観点からの提案です。

恐らく、「計画」、「実績」、「前年実績」の間で数字を比較するよりも、「4月」、「5月」、「6月」、…で比較する頻度のほうが高い（そういう時間のほうが長い）のではありませんか？基本的に表は、比較したいもの同士を縦に並べたほうが、人間の目にとっては見やすいです。

また、質問文のレイアウトでは、全データが一直線に並べられて極端に横長な表になっています。しかし「4月」、「5月」、「6月」、…を縦に並べ、「計画」、「実績」、「前年実績」を横に並べれば、全体としては長方形になるので、コンパクトで見やすくなります。

さらに、同じ単語を何度も繰り返し記載しないほうが全体の文字数が減るので、見る人にとっては理解しやすい表になります。そのためには、項目の部分を 2 行以上にするなど、工夫します。

A1　　　　　月
A3～14　　　4～12、1～3
A15　　　　 =i1&"～"&i2
A16　　　　 年度合計
B1、E1、F1　当年度、前年度、比較
B～F2　　　 計画、実績、増分、実績、実績
H1～2　　　 開始、終了
I1～2　　　 指定する月の数

D3　　　　　=c3-b3
F3　　　　　=c3-e3
B15　　　　 =sumifs(b3:b14,$A3:$A14,">="&$I1,$A3:$A14,"<="&$I2)
B16　　　　 =sum(b3:b14)

※添付図の場合は、I1:I2 のセル範囲の書式を「0"月"」に設定しているため、セル内に「月」が表示されています。
　この設定は、任意です。
　設定していても、セルに値を記入する際は、「10」といった数値のみを書きます。
　「10月」と書いてはいけません。

お礼

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

補足

この表は月度に計画・実績が紐づけされているのではなく、企業が紐づけされているのでこのような形式になります。

回答No.3

tom04

No.1です！
何度もごめんなさい。

No.2さんの回答を拝見して・・・
前回の方法は無視してください。
各月が3列ずつになっているのを見逃していました。
すでに関数での方法はNo.2さんが的確な回答をしていらっしゃるので、

お詫びのしるしとして、VBAでやってみました。
尚、一旦マクロが走ってしまうとすでに入力済みのA2～C2セルの数式が消えてしまいますので、
別Sheetで試してみてください。

そして今回も1行目のセルの表示形式はユーザー定義から
D1セルは
0月計画
E1セルは
0月実績
F1セルは
前年0月実績
として、D1～F1セルを範囲指定　→　F1セルのフィルハンドルでAM列までオートフィルでコピー（書式）をしておきます。

上記準備ができた上で、画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストし、A2セルに検索したい月の数値のみを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim j As Long, k As Long, endCol As Long, myTarget As Long, vL
If Target.Address = "$A$2" Then
myTarget = Target
Application.EnableEvents = False
endCol = WorksheetFunction.Match(myTarget, Rows(1), False)
For j = 1 To 3
For k = 3 + j To endCol + j - 1 Step 3
vL = vL + Cells(2, k)
Next k
Cells(2, j) = vL
vL = 0
Next j
Application.EnableEvents = True
End If
End Sub 'この行まで

どうも失礼しました。m(_ _)m

お礼

何度もありがとうございました。
教えていただいたマクロでも試してみます！

回答No.1

tom04

こんにちは！

＞計画合計欄に9月と入力したら、4～9月計画合計値が出るようにする・・・

これはA2セルに検索月を入力するとそのセルに合計を表示したい！
という意味でしょうか？
もしそうであれば関数では不可能です（マクロだと可能）

今回は関数での一案です。
↓の画像で説明します。（検索月はA3セルに入力するとします）

下準備として、4月計画・5月計画・・・
のセルの表示形式はユーザー定義から
0月計画
として、入力は単に「4」とか「5」という数値のみとします。（A3セルも数値のみ）

A2そうした上でA2セルに
=IF(A3="","",SUM(INDIRECT("D2:"&ADDRESS(2,MATCH(A3,1:1,0),4))))

という数式をいれると画像のような感じになります。m（_ _）m

お礼

ご丁寧にありがとうございます！