【Excel VBA】データを集計する数式を入力するマクロ

質問

Excel2003を使用しています。

　　A列　　　B列　　　C列　　　D列　　　E列　　　F列
1　　　　　　9/1　　　　　　　　　　　　　　　　　　　　20000
2　　　　　　9/2　　　　　　　　　　　　　　1000
3　　　　　　9/10　　　　　　　　　　　 　　5000
:
:
10　　　　　　　　　　　9月計　　　　　　 　※１　　　 ※２　　　
11
12
13　　　　　10/1　　　　　　　　　　　　　　　　　　　10000
14　　　　　10/10　　　　　　　　　　　 　2000
15　　　　　10/14　　　　　　　　　　　　　　　　　　　4000
:
:
20　　　　　　　　　　 10月計　　　　　　　※３　　　 ※４

上記のデータでいうと、
※１(E10セル) =SUM(E1:E9)
※２(F10セル) =SUM(F1:F9)
※３(E20セル) =SUM(E13:E19)
※４(F20セル) =SUM(F13:F19)
という内容の数式を入力するマクロを作成したく、数式を入力したいセルをアクティブにして、ボタン等に登録したマクロを実行すると数式が入力されるようにしたいと考えていますが、その際、集計範囲の最下行は、アクティブセルの1行上までになりますが、最上行をどのようにして取得すればよいかがわかりません。
(実際のデータは、もっと行数も入力されているデータ数も多いです)

また、他にもこんな方法があるとか、こうしたほうが簡単とかあれば、教えていただけると勉強にもなりますので助かります。
うまく説明できなくて申し訳ありませんが、よろしくお願いします。

回答No.3

mt2008 · ベストアンサー

簡単に式を代入したいだけでしたらこんなのは如何でしょう。ただし、B列の日付がシリアル値で、集計したい行のC列には必ず「n月計」と入っている場合限定ですが……。

E10に　=SUMPRODUCT((MONTH(B$1:B9)=VALUE(LEFT(C10,LEN(C10)-2)))*(E$1:E9))
F10に　=SUMPRODUCT((MONTH(B$1:B9)=VALUE(LEFT(C10,LEN(C10)-2)))*(F$1:F9))

と、入れてE10:F10をコピーして他の集計したいセル(例：E20）等に貼り付け。

「n月計」の月に一致した合計値を1行目からアクティブセルの一つ上の行までを対象に求めています。

お礼

アドバイスありがとうございます。

>簡単に式を代入したいだけでしたら

セルの位置に関係なく、その月の計を算出できる数式を入力できればいいので、教えていただいた数式をアクティブセルにマクロで入力するようにコードを書いてみたところ、うまくいきました。

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

回答No.5

onlyrom

エラー４００、大当たりだったようで嬉しい限りです。。(^o^)/~

（表のレイアウト）
----------------------------------------------------------
　　A列　　　B列　　　C列　　　D列　　　E列　　　F列
1●1行目は見出し●
2　　　　　　9/2　　　　　　　　　　　　　　1000
3　　　　　　9/10　　　　　　　　　　　 　　5000
:
:
10　　　　　　　　　　　9月計　　　　　　 　※１　　　 ※２　　　
11●空白行
12●空白行
13　　　　　10/1　　　　　　　　　　　　　　　　　　　10000
14　　　　　10/10　　　　　　　　　　　 　2000
15　　　　　10/14　　　　　　　　　　　　　　　　　　　4000
:
:
20　　　　　　　　　　 10月計　　　　　　　※３　　　 ※４
21●空白行
22●空白行
23　　　　　11/1　　　　　　　　　　　　　　　　　　　10000
24　　　　　11/15　　　　　　　　　　　 　2000

　　－－－－－－－－－以下同様ーーーーーー

------------------------------------------------------------------

（前提）
見出し：　１行目
データ：　２行目～～
日＿付：　Ｂ列

月計をセットする行の「Ｃ列」には必ず「Ｘ月計」の文字あり
次の２行は、空白行
続いて、翌月データが始まる
この繰り返し

重要＝＞「Ｘ月計」のあとは必ず２行空白行があること
　　　　（質問のデータではそう取れる）

'--------------------------------------------------　

Sub Test()
　Dim R As Long
　Dim StartRow As Long
　Dim EndRow As Long

　StartRow = 2

　For R = 2 To Cells(Rows.Count, "C").End(xlUp).Row
　　If Cells(R, "C").Value Like "*月計" Then
　　　EndRow = R - 1
　　　Cells(R, "E").Value = "=SUM(E" & StartRow & ":E" & EndRow & ")"
　　　Cells(R, "F").Value = "=SUM(F" & StartRow & ":F" & EndRow & ")"
　　　StartRow = R + 3
　　End If
　Next R

End Sub
'--------------------------------------------------

また、当方なら予め「X月計」を入れておかないで、それも式のセットの時にＣ列に入れてやりますが。。

（その場合の条件）
月と月の変わり目には必ず、「空白行」が３行あること

'----------------------------------------------------
Sub Test555()
　Dim R As Long
　Dim StartRow As Long
　Dim EndRow As Long

　StartRow = 2

　For R = 2 To Cells(Rows.Count, "B").End(xlUp).Row + 1
　　If Cells(R, "B").Value = "" Then
　　　EndRow = R - 1
　　　Cells(R, "C").Value = Month(Cells(EndRow, "B").Value) & "月計"
　　　Cells(R, "E").Value = "=SUM(E" & StartRow & ":E" & EndRow & ")"
　　　Cells(R, "F").Value = "=SUM(F" & StartRow & ":F" & EndRow & ")"
　　　StartRow = R + 3
　　　R = R + 2
　　　End If
　Next R

End Sub
'----------------------------------------------

テストデータでお試しください。
以上。

お礼

回答ありがとうございます。
今回の質問にも目を通していただき、嬉しい限りです♪

まず、質問内容のTestマクロを試してみたところ、うまくいきました。
データ開始行等、実際使用する表と多少異なる部分があるのですが、そのへんはこちらで対処できました。

次に、Test555マクロですが、数式と一緒に「*月計」も入れてしまえば、なお良いですよね。
ただ、実際使用する表では、「*月計」の次行に「累計」もあります。また、1番目の「*月計」の次行には「累計」はなく、2番目の「*月計」以降、次行に「累計」があるので、それらの条件を追加すれば、数式をセットするのと同時に「*月計」も入力できそうですね。

実際、自分でそこまでできるかどうかわかりませんが、ぜひ試してみたいと思います。
ありがとうございました。

回答No.4

pkh4989

今の方法で行くと
合計する「数式を設定」セルをActiveにして、このマクロを実行する

Sub 数式設定()
　　Dim mR　　　　　As Long
　　Dim wR　　　　　As Long
　　Dim fR　　　　　As Long
　　'
　　fR = 1
　　With ActiveSheet
　　　　mR = ActiveCell.Row - 1
　　　　For wR = mR To 1 Step -1
　　　　　　If Left(.Cells(wR, "E").Formula, 1) = "=" Then
　　　　　　　　fR = wR + 1
　　　　　　　　Exit For
　　　　　　End If
　　　　Next
　　　　.Cells(ActiveCell.Row, "E") = "=SUM(E" & fR & ":E" & mR & ")"
　　　　.Cells(ActiveCell.Row, "E").Offset(0, 1) = "=SUM(F" & fR & ":F" & mR & ")"
　　End With
End Sub

お礼

アドバイスありがとうございます。

記載していただいたマクロを試してみたところ、うまくいきました。
今回の質問そのままの数式が入力されますね。
想像していたものより、シンプルなコードで少々驚きました。

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

回答No.2

qualheart

例の表を見ますと、式を入力したい行のC列には必ず「～月計」と入力されてますよね？
例えば、アクティブセル行のC列の値を一旦取得し、集計したい月を割り出した後、B行でその月範囲内の行を特定し、その範囲行内のE列とF列の集計をする、という手順でマクロを組んだらどうでしょうか？
あと特にSUM関数の計算式で入力する必要性がないなら、マクロ上で合計値を計算して値のみを入力してしまっても良いと思います。

お礼

アドバイスありがとうございます。

>例の表を見ますと、式を入力したい行のC列には必ず「～月計」と入力されてますよね？

そのとおりです。数式を入力したい行のC列には必ず「～月計」と入力されていますので、それも集計範囲取得に利用できないかと思い、質問内の表に記載しました。

こんなふうにすればできるのではないかというアイデアは浮かぶものの、それを実際にコードとして記述することがまだまだの状態ですが、他の方からもアドバイスをいただいていますので、頑張ってみます。
ありがとうございました。

回答No.1

hallo-2007

>他にもこんな方法があるとか、こうしたほうが簡単とかあれば、教えていただけると
一般的には
　　A列　　　B列　　　C列　　　D列　　　E列　　　F列
1　　　　　　9/1　　　　　　　　　　　　　　　　　　　　20000
2　　　　　　9/2　　　　　　　　　　　　　　1000
3　　　　　　9/10　　　　　　　　　　　 　　5000
:
13　　　　　10/1　　　　　　　　　　　　　　　　　　　10000
14　　　　　10/10　　　　　　　　　　　 　2000
15　　　　　10/14　　　　　　　　　　　　　　　　　　　4000
:
別シートに
　　A列　　　B列　　　C列　　　D列　　　E列　　　F列
1　9月計　　　　　　 　※１　　　 ※２　　　
2　10月計　　　　　　　※３　　　 ※４
の方が見やすいと思いますが。

補足

アドバイスありがとうございます。

>他にもこんな方法があるとか、こうしたほうが簡単とかあれば、教えていただけると

“データを集計する数式をマクロで入力する方法”に関して、他にもこんな方法があるとかがあれば、教えていただけると…というつもりで、質問文に書かせていただいたので、データが見やすくなる方法とかいうことではないのです。

今回、質問に関係ないセルのデータは特に記載せず、空白にしていますが、実際には入力されているセルもありますし、同様のシートが30数枚ありますので、集計する数式をマクロで入力できればと思い、質問させていただきました。わかりづらかったようで、申し訳ありません。