65536行6列×6で10シートを連続して計算

質問

1つのブックに為替のデータが時系列で年ごとに10シート（10年分）あります。
各シートには項目が「日付、時間帯、始値、高値、安値、終値」と6列あり、それぞれの時系列データは1年分で65536行以上あって縦にすべてを並べることはできないので65536行ごとに分割しています。（2009年が3分割でそれ以前は6分割になっています。）

このブックをもとに他のブックのシート1枚へ、終値過去5つ分の単純移動平均値を並べて書き出したいと思っているのですが、
データの最後の行とその次の1行目からをあわせた計算、シートとシートをまたいだ計算がうまくできず困っております。
そこで、シート1のセルA1から数値が入っているシート10の最後のセルまでを1列とみなすようなマクロを作りたいのですが、何か良い方法はございますでしょうか。

エクセルは2003を使っています。
宜しくお願い致します。

回答No.3

mitarashi · ベストアンサー

巨大な配列が使えるかどうか分かりませんので、クラスモジュールを使うのはいかがでしょうか。データを一個入れると、ところてん式に古いデータを押し出して、単純移動平均を計算するという、なんのひねりもないコードです。
下記コードは、VBEで挿入／クラスモジュールで生成されるClass1に記述してください。コードは単純明快なので、当方の理解に誤りがあればご訂正下さい。
Private dataNum As Long
Private dataArray(5) As Double

Public Function SMA(newData As Double) As Double
Dim tempSum As Double
Dim i As Long

If dataNum < 5 Then dataNum = dataNum + 1
dataArray(5) = dataArray(4)
dataArray(4) = dataArray(3)
dataArray(3) = dataArray(2)
dataArray(2) = dataArray(1)
dataArray(1) = newData
For i = 1 To dataNum
tempSum = tempSum + dataArray(i)
Next i
SMA = tempSum / dataNum
End Function
使用例です。こちらは標準モジュールに記述してください。列、シートの切り替わりの部分で苦労されているだけで、複数シート、列にまたがるループは既に達成されていると思うので、割愛します。
Sub test()
Dim calcSMAcls As New class1
Dim i As Long

For i = 1 To 1000 Step 100
Debug.Print calcSMAcls.SMA(CDbl(i))
Next i
End Sub

お礼

遅くなってしまいましたが、ご回答有難うございました。
自分の用途にはこの方法が一番あっていたようです。全て計算することができました！

回答No.4

imogasi

非常に矮小な例で考えた。
納得できればコードの定数面を拡張してやってみてください。
For j = 1 To 3
For i = 1 To 5
の３や５の部分や
avg = (st3(1) + st3(2) + st3(3)) / 3
の３の部分です。
＝＝＝＝
A列５行の３シートを対象とする。
シートタブの位置が左から３シート分ということ。シートの名前に関係させてない。
Sub test01()
For j = 1 To 3
For i = 1 To 5
t = t + Sheets(j).Cells(i, "A")
Next i
Next j
MsgBox t
End Sub
これで３シートの合計は取れた。
配列などに溜め込むのは、データ数が多いときはやめたほうが良い。
エクセルのセルが一種の配列構造だ。
＝＝＝
３個データの移動平均を出すなら、データ３つは貯めておくとやりやすいかも。
例
Sub test01()
Dim st3(3)
For j = 1 To 3
For i = 1 To 5
x = Sheets(j).Cells(i, "A")
't = t + x
st3(1) = st3(2): st3(2) = st3(3): st3(3) = x
avg = (st3(1) + st3(2) + st3(3)) / 3
MsgBox avg
Next i
Next j
'MsgBox t
End Sub
ーー
例データ
Sheet1　A1;A5
1
2
3
4
5
Sheet2 A1:A5
1
2
3
4
5
Sheet3　A1:A5
10
11
12
13
14
テスト結果が見やすいように
Sheet1にA６：A15にSheet2,Sheet3のA1:A5データをコピーしておき
A列　　B列
1
2
3 2
4 3
5 4
5 4.666666667
4 4.666666667
3 4
2 3
1 2
10 4.333333333
11 7.333333333
12 11
13 12
14 13
B列は、ｂ３に移動平均式
=(A1+A2+A3)/3を入れて下方向に式を複写したもの。B1,B2の式は小生詳しくない。
上記コードを実行するとSheet1のB列の通りの数がMsgbox で次々出てきて一致した数である。
と言うことは、シート間のつなぎでも、上記のロジックで、旨くいくということと思う。
各シートで数行程度行数が違うときもあろうかと思う。その場合は
各シートの最終行を配列にもつとkか
ループに入る前に最終行を
Sub test02()
j = 2
d = Sheets(j).Range("A65536").End(xlUp).Row
MsgBox d
End Sub
で類推できる方法で捉えて、上記では
For i = 1 To 5
t = t + Sheets(j).Cells(i, "A")
の5をdに置き換えればよいだろう。

お礼

わかりやすい説明で、最後まで計算するのにとても良いヒントとなりました。ご回答有難うございました。

回答No.2

yyr446

Dim target_range As Range
Set target_range = Application.Union( _
Worksheets("Sheet1").Range("A1:A65536"), _
Worksheets("Sheet2").Range("A1:A65536"), _
Worksheets("Sheet3").Range("A1:A65536") _
)
でtarget_rangeを操作すればよいでは

お礼

この方法を基に作ってみたのですがうまく動きませんでした。ご回答有難うございました。

回答No.1

Sinogi

シート名は　１　２　３　のような数値
A列のみデータが複数シートにデータがあって、「表示」シートに10000行ずつの合計を表示するマクロをつくってみました。

全データを配列に格納して処理しています。質問者さんのデータでメモリが耐えられるかどうか不明ですが参考までに

Option Explicit
Sub ss()
Dim Row1, ShName, MySum(), MyData(), SumCount, SHRow, ShNumber, AllRow, PreRow, ModRow, MAN, AMARI

ShNumber = 0
Row1 = 1
PreRow = 0
Do
ShNumber = ShNumber + 1
ShName = CStr(ShNumber)
Worksheets(ShName).Activate
SHRow = Cells(Row1, 1).End(xlDown).Row
AllRow = AllRow + SHRow

For Row1 = 1 To SHRow
ReDim Preserve MyData(PreRow + Row1)
MyData(PreRow + Row1) = Cells(Row1, 1).Value

Next Row1
PreRow = AllRow
Row1 = 1
Loop While SHRow = 65536

SumCount = Int(AllRow / 10000) + 1
ModRow = AllRow - SumCount * 10000
ReDim MySum(SumCount)

Worksheets("表示").Activate
For Row1 = 1 To AllRow
'AMARI = Row1 Mod 10000
MAN = Int((Row1 - 1) / 10000)
MySum(MAN) = MySum(MAN) + MyData(Row1)
Cells(MAN + 1, 1).Value = MySum(MAN)

Next Row1

お礼

今回の自分の用途には合いませんでしたが、VBA覚えたての自分にはとても良い勉強になりました。ご回答有難うございました。