VBAで10行ごとの移動平均を計算する（数式は面倒・・）
Excelでデータ分析や数値の扱う際に、「過去10行の平均を毎行ごとに出したい」というケースがあります。
FXや株式取引でのテクニカル分析で扱う「移動平均線」は有名です。他にも売上やアクセス数、温度や計測値など、一定期間の推移をなめらかに見るためには移動平均がとても有効です。
ただ、Excel関数で1行ずつ手動で数式を入れるのは手間がかかりますし、対象データが増えると面倒です。
そこでこの記事では、VBAを使って、指定した列のデータに対して10行ごとの平均を自動計算して隣の列に出力する方法をご紹介します。コードを編集すれば10日間を20日間などに変更することが可能です。
マクロ概要
対象列のデータを1行ずつ読みながら、その行を含めた直近10行の平均を計算します。
計算結果を隣の列に自動で書き出します。
シートのデータが空になるまで繰り返し実行します。
コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Sub SetMovingAverage() Dim r As Range '// 処理対象セル Dim averageRange As Range '// 移動平均セル範囲 Dim sum As Double '// セル範囲の合計値 Dim cell As Range '// セル範囲のループ中セル Dim numRows As Long '// 移動平均を計算する範囲の行数 Dim targetCol As String '// 計算対象列 Dim outputColOffset As Long '// 計算結果出力列（計算対象列からの何列右か） '// 設定 numRows = 10 '// 行数（10であれば10行分での移動平均を求める） targetCol = "G" '// 列 outputColOffset = 2 '// targetColからどれだけ右の列に出力するか（G列で2列右であればI列に出力する） '// 基準セルを指定 Set r = Range(targetCol & "11") '// Do '// セルに値がなければループを抜ける If r.Value <> "" Then Exit Do End If '// 基準セルからｎ行前までのセル範囲をセット Set averageRange = Range(r, r.Offset(-(numRows - 1), 0)) '// 合計値を初期化 sum = 0 '// ｎ行分のセル値の合計を計算 For Each cell In averageRange sum = sum + cell.Value Next '// 計算結果出力列に移動平均（セル値合計÷行数）を出力 r.Offset(0, outputColOffset).Value = sum / numRows '// 次のセルに対象セルを変更 Set r = r.Offset(1, 0) Loop End Sub |
実行方法
株価のデータを使って実行方法を説明します。
ここではA列の「日付」昇順でE列の「終値」の10日間の移動平均をG列に出したい場合とします。
上のようにシートにデータを入力します。例では罫線を付けていますが罫線は無くても構いません。
あとは、入力したシートを表示（アクティブ）状態で上のコードのSetMovingAverage関数を実行するだけです。
実行後は以下のようにE列の各10日分の平均がG列に出力されます。
注意点
平均の対象となる10行分のデータが不足している場合もそのまま計算されます。
空白セルや非数値が混ざっている場合、エラーになることがあります。