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

Question

1つのブックに為替のデータが時系列で年ごとに10シート（10年分）あります。
各シートには項目が「日付、時間帯、始値、高値、安値、終値」と6列あり、それぞれの時系列データは1年分で65536行以上あって縦にすべてを並べることはできないので65536行ごとに分割しています。（2009年が3分割でそれ以前は6分割になっています。）

このブックをもとに他のブックのシート1枚へ、終値過去5つ分の単純移動平均値を並べて書き出したいと思っているのですが、
データの最後の行とその次の1行目からをあわせた計算、シートとシートをまたいだ計算がうまくできず困っております。
そこで、シート1のセルA1から数値が入っているシート10の最後のセルまでを1列とみなすようなマクロを作りたいのですが、何か良い方法はございますでしょうか。

エクセルは2003を使っています。
宜しくお願い致します。

mitarashi · Accepted Answer

巨大な配列が使えるかどうか分かりませんので、クラスモジュールを使うのはいかがでしょうか。データを一個入れると、ところてん式に古いデータを押し出して、単純移動平均を計算するという、なんのひねりもないコードです。
下記コードは、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

imogasi · Answer

非常に矮小な例で考えた。
納得できればコードの定数面を拡張してやってみてください。
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
32
43
54
54.666666667
44.666666667
34
23
12
104.333333333
117.333333333
1211
1312
1413
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に置き換えればよいだろう。

yyr446 · Answer

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を操作すればよいでは

Sinogi · Answer

シート名は　１　２　３　のような数値
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

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

巨大な配列が使えるかどうか分かりませんので、クラスモジュールを使うのはいかがでしょうか。

非常に矮小な例で考えた。

Dim target_range As Range

シート名は １ ２ ３ のような数値

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

シート名は　１　２　３　のような数値