Excelのマクロ（ExcelVBA）を使って5の倍数毎に数値を合計しその平均値を出力したい

###前提・実現したいこと
Excelのマクロ（ExcelVBA）を使って5の倍数毎に数値を合計しその平均値を出力したい

分かりにくいかもしれませんが上記のような感じで
B2～B6の全ての数値（5行）を足してD2に平均値を出力
B7～B11の全ての数値（5行）を足してD3に平均値を出力
B12～B16の全ての数値（5行）を足してD4に平均値を出力
....以下空欄になるまで

C2～C6の全ての数値（5行）を足してE2に平均値を出力
C7～C11の全ての数値（5行）を足してE3に平均値を出力
C12～C16の全ての数値（5行）を足してE4に平均値を出力
....以下空欄になるまで

AとBの数値は時間を追う毎に出力されていきます
今回は分かりやすく数値は変えてあります

色同士で見て頂くとわかりやすいかもです
とにかくAの列、Bの列の数値を５つづつ足してD、Eの列にその平均値を出力したいです

5行の平均...次の5行の平均...その次の5行の平均...その次の...その次の...とA、Bが空欄になるまでもしくは任意の回数
続きます

今回は説明用に１５行の所までしか書いてありませんがずっと続いているとお考え願えたら

###試したこと

ほぼ初心者に近い者です

最近少しづつですが変数事を理解し始めました
DO　LOOP　　や　IF文　も少しづつですが見ればどのような動きをするか分かるようになってきました

今回の件はfor または　DO LOOP　と if 等を使いセルを指定するところまでは思いついたのですがどう記述したり指定の仕方で躓いています

下記のサイト等を参考にいろいろ調べたりしたのですが
何処をどうしたらいいのか自分なりにあと一ひねりの考えだとはおもうのですが…そこまで至らないのが現状で藁をもすがる思いで質問させていただきました。
何卒ご教授願えたらと思います

http://www.excel.studio-kazu.jp/kw/20100531142829.html
http://www.relief.jp/docs/excel-vba-mod-function.html
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1090697005

###補足情報(言語/FW/ツール等のバージョンなど)

セル自体に記述するのではなく
マクロ（VBA）で製作したいのです

coco_bauer

2017/10/25 06:38

B列は５個ずつ同じ数値が並ぶ、C列は５個ずつ等差数列が並ぶ、などのルールがあるのですか？　そうしたルールがあるなら平均をとる手間を省けます(B列のどれかを使う、C列の３番目の値を使うことで平均が求められますから）

行動規範の内容に同意します

回答1件

ベストアンサー

すみません、全く動かしていないので細かいところはダメかもしれないのですが
こんな感じでできるのかなと思いました。

Sub test()
	'B列から開始
	Dim i As Long: i = 2
	' 列のループ(C列まで)
	Do While i <= 3 
		'合計値
		Dim sum As Long: sum = 0

		'2行目から開始
		Dim j As Long: j = 2
		'行のループ(空欄まで)
		Do While Worksheets("Sheet1").Cells(j, i).Value <> ""
			'合計を出す
			sum = sum + Worksheets("Sheet1").Cells(j, i).Value

			'5の倍数になったら
			If (j - 1) Mod 5 = 0 Then
				'平均をセルに設定
				Worksheets("Sheet1").Cells(j/5+1, i+2).Value = sum / 5
				sum = 0
			End If
			j = j + 1
		Loop
		i = i + 1
	Loop
End Sub

投稿2017/10/25 05:48

編集2017/10/25 08:46

yuki-saito

総合スコア928

techno

2017/10/25 06:31

yuki-saito さんお返事ありがとうございますプログラム拝見させて頂きました僕なんかでは到底考えもつかないプログラムでした拝見した時に純粋に「凄い！！！」って言ってしまいました早速、試してみたのですがコンパイルエラー：　　構文エラーが出てきまして If (j - 1) % 5 == 0 Then の所なのですが何か他に記述の仕方がるのでしょうか？この部分の言わんとしている事は分かるのですが何が構文エラーなのか

yuki-saito

2017/10/25 07:01

よかったです＾＾ありがとうございます。（でも誰かに低評価されてますね苦笑）コンパイルエラーですか！何ででしょうね・・・今は動作環境がないので確認できないのですが ()があることでダメなのでしょうか・・・ちょっと調べてみてまた何か分かったら書きますね！

date

2017/10/25 07:01

VBAだったら If (j - 1) Mod 5 = 0 Then Modで余りを出すのではないですか? また全部Dimで宣言した方がいいですね

yuki-saito

2017/10/25 07:03

確かにそうですね！それでできるかと思います。

yuki-saito

2017/10/25 07:11

回答のコードを直してみました。やっぱり動かしてないとダメですね。（VBAはそれほどやったことがなく・・・）失礼致しました。

date

2017/10/25 07:15

正確にはif文の=は1個でいいのでまだエラーですが ✕== ○=

techno

2017/10/25 07:20

yuki-saito　さんありがとうございます動作環境がないのにそこまでかけるのは凄いです頭の中で構築できる感じなのですかね？どうしたらそんな考えに至るのか...僕自身かなり勉強不足を凄く凄く凄く痛感しました奥が深いです...

yuki-saito

2017/10/25 07:23

> dateさんまたまたご指摘ありがとうございます！修正しました。 > technoさんそうですね。想像力を働かせてやっている感じです笑

techno

2017/10/25 07:24

dateさんお返事ありがとうございます仰るとおりに記述（あってますかね）してみたのですがまた違うコンパイルエラーがでまして「Val = 」の所で代入式の左辺の関数呼び出しは、バリアント型またはオブジェクト型の値を返さなければなりません。何が何やら...今必死に意味を検索中です助言して頂いて書いた記述したプログラム載せておきます Sub test() Dim i As Integer Dim j As Integer 'B列から開始 i = 2 ' 列のループ(C列まで) Do While i <= 3 '合計値 Sum = 0 '2行目から開始 j = 2 '行のループ(空欄まで) Do While Worksheets("Sheet1").Cells(j, i).Value <> "" '合計を出す Sum = Sum + Worksheets("Sheet1").Cells(j, i).Value '5の倍数になったら If (j - 1) Mod 5 = 0 Then '合計を設定 Val = Sum 'A列だったら If i == 2 Then '平均にする Val = Val / 5 End If 'セルに設定 Worksheets("Sheet1").Cells(j / 5 + 1, i + 2).Value = Val Sum = 0 End If j = j + 1 Loop i = i + 1 Loop End Sub

yuki-saito

2017/10/25 07:26 編集

Valという関数があるからですね。先頭大文字でなければ大丈夫なのであれば、最新の僕の回答欄のコードでどうでしょうか？ダメならvでもdataでも良いので名前を変えれば大丈夫かと。

date

2017/10/25 07:26

vbaにはvalという関数があるのでそれと混在してしまっているのでしょう別の名前に変えるとエラーなくできると思います

yuki-saito

2017/10/25 07:30

変数名をdataに変えました。（ネーミングセンスなくてすみません。）

techno

2017/10/25 07:40

お二人の言う通り Val　を　data と言う名前に変えた所エラーがでまして「 j = j + 1」の所でオーバーフローしました出たので Dim ｊ As Integer　の所を　Dim j As Long　に変更したらエラーが出なくなりましたありがとうございます今度はエラーが出ない代わりにエクセルの方はうんともすんとも言わない変化無しの状況になってしまいましたプログラムはエラーは出ていませんのでどこかに結果が出ていないか探してみます

yuki-saito

2017/10/25 07:43

そんなにデータ数（行数）あったんですね苦笑最初からLongにしておけばよかったですね。単純に重いということではなくてですかね？

techno

2017/10/25 07:47

yuki-saitoさん一番最初に画像で示しているそのままの数値でやっているのですがそんなに大きな数になるんでしょうかね？ yuki-saitoさんの仰るとおりに修正してみました Sub test() 'B列から開始 Dim i As Integer: i = 2 ' 列のループ(C列まで) Do While i <= 3 '合計値 Dim sum As Long: sum = 0 '2行目から開始 Dim j As Long: j = 2 '行のループ(空欄まで) Do While Worksheets("Sheet1").Cells(j, i).Value <> "" '合計を出す sum = sum + Worksheets("Sheet1").Cells(j, i).Value '5の倍数になったら If (j - 1) Mod 5 = 0 Then '合計を設定 Dim data As Long: data = sum 'A列だったら If i = 2 Then '平均にする data = data / 5 End If 'セルに設定 Worksheets("Sheet1").Cells(j / 5 + 1, i + 2).Value = data sum = 0 End If j = j + 1 Loop i = i + 1 Loop End Sub

techno

2017/10/25 07:47

エラーにはならないものの数値が現れないのです

techno

2017/10/25 08:33

出来ました！！！ Worksheets("Sheet1")の所が"Sheet1"では無く"Sheet2"でした "Sheet1"は別の計算をしているページでしたすみません、お騒がせ致しましたそのあとなのですが「Aの平均」はちゃんと計算されているのですが「Bの平均」は平均ではなく「５つを足した合計数」になってしまっているようなのです。この後どのように記述したらいいのかご教授願えないでしょうか。

yuki-saito

2017/10/25 08:44

あっ質問を見間違えてましたね。わざわざ合計にするようにしちゃってます。それなら A列だったら・・・とか全然いらないのでもっと簡単になりますね！ If (j - 1) Mod 5 = 0 Then 'セルに設定 Worksheets("Sheet1").Cells(j / 5 + 1, i + 2).Value = sum / 5 sum = 0 End If でOKです！

techno

2017/10/25 09:03

yuki-saitoさん出来ました！！！凄く助かりましたありがとうございます本当に素晴らしいです僕もこういう質問が来たら即答できるように頑張りたいです心からありがとうございました

yuki-saito

2017/10/25 09:08

いえいえ＾＾また何かあれば言ってください。

techno

2017/10/25 09:11

本当にお優しい方です！！ありがとうございます感謝いたします。

行動規範の内容に同意します