エクセル 直近入力データの平均・合計

質問

セルA5からA6 A7…と下方向にデータを入力しています。A1に直近5つのデータの平均、A2に直近5つのデータの合計を表示させたいです。例えばA1～A100までデータを入力したとすると、直近5つのデータ平均とはA96～A100の平均ということです。どんどん下方向にデータを入力していくのですが、A1に直近5つのデータの平均、A2に直近5つのデータの合計を表示させたいです。これを実行させるマクロ、関数式を知りたいです。関数式でできるのかはわかりませんが・・・。よろしくお願いします。

回答No.6

pauNed · ベストアンサー

（#2コメントへのレスです）

#5：vizzarさんが書いてらっしゃいますね。
『関数で出来るものをわざわざマクロで実現するのはお勧めしません。』
私もそう思います。
不具合があった時に自分でメンテできなければ実用化は難しいと考えてください。

VBAの場合、一般機能でできない事をできるようになるという事ではなく、
一般機能や手作業では煩雑な手続きを自動化できる、程度に
捉えておいたほうが良いと思います。
#知っていれば便利なのは間違いないです。がんばってください^ ^

以下２例。今後の参考になれば幸いです。
'----------------------------------------------------------
いわゆる[ユーザー定義関数]です。標準モジュールに置いて使います。
Application.Volatileを使っているので、
多用すると一般数式より重くなるかもしれません。
'----------------------------------------------------------
Option Explicit

Function SUMAVE(SR As Range, _
　　　　　　　　Optional flg As Boolean = True, _
　　　　　　　　Optional n As Long = 5)
　　'SUM計算は、セルに =SUMAVE(起点セルを指定) と入力。
　　'AVERAGEは =SUMAVE(起点セルを指定,0)と入力。
　　'第3引数で計算範囲を指定できる。指定なしは5

　　Dim TR As Range
　　Dim x

　　Application.Volatile
　　Set TR = Cells(Rows.Count, SR.Column).End(xlUp)
　　If TR.Row < SR.Row Then
　　　　Set TR = SR
　　Else
　　　　If n > TR.Row Then n = TR.Row
　　　　Set TR = Intersect(Range(SR, TR), _
　　　　　　　　　　　　　 Range(TR.Offset(-n + 1), TR))
　　End If
　　If flg Then
　　　　x = Application.Sum(TR)
　　Else
　　　　x = Application.Average(TR)
　　End If
　　SUMAVE = x
　　Set TR = Nothing
End Function
'----------------------------------------------------------
Worksheetの値を変更する度に実行するイベントプロシージャです。
目的シートのシートモジュールに置いて使います。
改造して、任意に実行するようにすれば少し簡単になります。
'----------------------------------------------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
　　'これはWorksheetイベントプロシージャなので _
　　 目的シート右クリック[コードの表示]のコードペインに置く。

　　Const rn As Long = 5 '起点セルの行
　　Const cn As Long = 1 '起点セルの列
　　Const n　As Long = 5 '計算範囲
　　Dim TR As Range
　　Dim nn As Long

　　If Target.Column <> cn Then Exit Sub
　　Set TR = Cells(Rows.Count, cn).End(xlUp)
　　If TR.Row < rn Then
　　　　Set TR = Cells(rn, cn)
　　Else
　　　　nn = IIf(n > TR.Row, TR.Row, n)
　　　　Set TR = Intersect(Range(Cells(rn, cn), TR), _
　　　　　　　　　　　　　 Range(TR.Offset(-nn + 1), TR))
　　End If
　　With Application
　　　　.EnableEvents = False
　　　　.Calculation = xlCalculationManual
　　　　Cells(1, cn).Value = .Average(TR)
　　　　Cells(2, cn).Value = .Sum(TR)
　　　　.Calculation = xlCalculationAutomatic
　　　　.EnableEvents = True
　　End With
　　Set TR = Nothing
End Sub
'----------------------------------------------------------

お礼

度重なる質問にレスしていただき、ありがとうございます。
マクロについては、標準モジュール・シートモジュールと2パターン提示していただき非常に参考になりました。
私もいつかここで回答者になれるよう、マクロの勉強に精進します・・・。

回答No.5

noname#79209

> すいませんができたらマクロでの方法（コード）も教えていただきたいのですが・・・
多分、参考の為にお知りになりたいのだとは思いますが、
関数で出来るものをわざわざマクロで実現するのはお勧めしません。
1.何でも安易にマクロに走るクセがつく。
2.職場などでは、マクロのメンテや同僚からの要望に忙殺されてしまう。
3.マクロ入りファイルを開くと表示される「ウイルス云々」の警告メッセージに過剰反応する人が必ずいる。

余計なお世話かと思いますが、老婆心ながら．．．

お礼

アドバイスありがとうございます。マクロの勉強はしているのですが、なかなか上達しないもので・・。

回答No.4

imogasi

この質問はイメージするのは簡単ですが、関数としては、超難問でしょう。
取り合えず、配列数式を織り込んで
例データ
A2:A９
1
2
3
4
5
6
8
1
最下行は（質問には無いが）
=MAX((A1:A100<>"")*(ROW(A1:A100)))
と入れてSHIFT+CTRL+ENTER（３つのキーを同時に押す）
結果
９
ーー
５セルの合計は
=SUM(OFFSET(A1,MAX((A1:A100<>"")*(ROW(A1:A100)))-5,0,5,1))
といえてSHIFT+CTRL+ENTER（３つのキーを同時に押す）
結果
２４
平均はSUMをAVERAGEに変えるとでる。
入力最下行をA100までを対象とした式です。
ーーーー
他の方法
最下行を求める式が、思いつきにくいが、VBAを使う方なら、下記は
良く使うので
ユーザー関数
Function lr(a)
lr = Range(a).End(xlUp).Row
End Function
を標準モジュールに作り
=SUM(OFFSET(A1,lr("A100")-5,0,5,1))
とするとやや簡単になり、配列数式で無くなる。
平均もSUMをAVERAGEで置き換えると良い。

お礼

関数・マクロと教えていただきありがとうございます。
いろいろなテクニックがあり非常に参考になります。

回答No.3

noname#204879

データ入力範囲が最大で A5:A1000 と仮定すると、
A1: =AVERAGE(OFFSET(INDIRECT("A"&(4+COUNT(A5:A1000))),,,-5))
A2: =SUM(OFFSET(INDIRECT("A"&(4+COUNT(A5:A1000))),,,-5))

お礼

こういう関数式でもできるのですね。
参考になりました、ありがとうございます。

回答No.2

pauNed

ぁ。失礼m(_ _)m
『セルA5から』なので
=AVERAGE(OFFSET(A4,COUNTA(A5:A65536),,-5))
=SUM(OFFSET(A4,COUNTA(A5:A65536),,-5))

補足

レスありがとうございます。
関数でできたのですね。
すいませんができたらマクロでの方法（コード）も教えていただきたいのですが・・・。図々しくてすいませんが、よろしくお願いします。

回答No.1

pauNed

こんにちは。
A1に=AVERAGE(OFFSET(A2,COUNTA(A3:A65536),,-5))
A2に=SUM(OFFSET(A2,COUNTA(A3:A65536),,-5))
とか。
途中未入力セルがない事が前提ですが。
それに、最下行がA7以上でないとだめです^ ^;