![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
No.6ベストアンサー
- 回答日時:
(#2コメントへのレスです)
#5:vizzarさんが書いてらっしゃいますね。
『関数で出来るものをわざわざマクロで実現するのはお勧めしません。』
私もそう思います。
不具合があった時に自分でメンテできなければ実用化は難しいと考えてください。
VBAの場合、一般機能でできない事をできるようになるという事ではなく、
一般機能や手作業では煩雑な手続きを自動化できる、程度に
捉えておいたほうが良いと思います。
#知っていれば便利なのは間違いないです。がんばってください^ ^
以下2例。今後の参考になれば幸いです。
'----------------------------------------------------------
いわゆる[ユーザー定義関数]です。標準モジュールに置いて使います。
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
'----------------------------------------------------------
この回答へのお礼
お礼日時:2007/08/09 22:56
度重なる質問にレスしていただき、ありがとうございます。
マクロについては、標準モジュール・シートモジュールと2パターン提示していただき非常に参考になりました。
私もいつかここで回答者になれるよう、マクロの勉強に精進します・・・。
No.5
- 回答日時:
> すいませんができたらマクロでの方法(コード)も教えていただきたいのですが・・・
多分、参考の為にお知りになりたいのだとは思いますが、
関数で出来るものをわざわざマクロで実現するのはお勧めしません。
1.何でも安易にマクロに走るクセがつく。
2.職場などでは、マクロのメンテや同僚からの要望に忙殺されてしまう。
3.マクロ入りファイルを開くと表示される「ウイルス云々」の警告メッセージに過剰反応する人が必ずいる。
余計なお世話かと思いますが、老婆心ながら...
No.4
- 回答日時:
この質問はイメージするのは簡単ですが、関数としては、超難問でしょう。
取り合えず、配列数式を織り込んで
例データ
A2:A9
1
2
3
4
5
6
8
1
最下行は(質問には無いが)
=MAX((A1:A100<>"")*(ROW(A1:A100)))
と入れてSHIFT+CTRL+ENTER(3つのキーを同時に押す)
結果
9
ーー
5セルの合計は
=SUM(OFFSET(A1,MAX((A1:A100<>"")*(ROW(A1:A100)))-5,0,5,1))
といえてSHIFT+CTRL+ENTER(3つのキーを同時に押す)
結果
24
平均は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.1
- 回答日時:
こんにちは。
A1に=AVERAGE(OFFSET(A2,COUNTA(A3:A65536),,-5))
A2に=SUM(OFFSET(A2,COUNTA(A3:A65536),,-5))
とか。
途中未入力セルがない事が前提ですが。
それに、最下行がA7以上でないとだめです^ ^;
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- C言語・C++・C# このプログラミングの問題を教えてほしいです。 キーボードからデータ数nとn個のデータを入力し、平均値 3 2022/12/19 22:51
- Excel(エクセル) Excelで全クラスのランキング表を作成したい 4 2022/05/24 15:28
- Excel(エクセル) エクセル表作成について 5 2023/03/12 13:25
- 数学 数学 2変数データで、「相関係数=−1」の散布図を書く際 写真に これら5組のデータの散布図を描くと 4 2023/02/15 10:46
- 統計学 t値の計算方法 1 2022/11/29 18:37
- Excel(エクセル) VBAのoffsetの動き方について教えてください 3 2022/11/25 23:36
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Excel(エクセル) エクセル 関数について 2 2022/10/10 07:56
- Excel(エクセル) エクセルのセルの書式設定・ユーザー定義の条件設定について 1 2022/08/17 21:56
- Excel(エクセル) Excelの複数人での参照について 2 2022/06/01 13:38
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで特定のセルに表示...
-
Excel内での検索結果をシート...
-
エクセル 数字をすべて○などの...
-
太字に設定されているセルの個...
-
Excelでセルをクリックす...
-
クリックすると文章が表示され...
-
Excelで挿入した図をセルの中央...
-
現在のセルの位置を返す関数は...
-
マクロを実行すると画像がズレ...
-
excelで セルの移動時に...
-
フォントの色を指定して削除出...
-
【EXCEL】先週の月曜日の日付を...
-
セルがクリックされた回数をカ...
-
Excelで、図形内の文字をセルに...
-
エクセルマクロ 赤色の文字を検...
-
EXCELのセルや文字色の反映
-
excelのソルバーをVBAで複数行...
-
シート保護とグループ化機能を...
-
エクセルでPDFリンクを大量...
-
VBAで特定の文字以降の文字列の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで特定のセルに表示...
-
エクセル 数字をすべて○などの...
-
Excel内での検索結果をシート...
-
太字に設定されているセルの個...
-
クリックすると文章が表示され...
-
Excelで挿入した図をセルの中央...
-
【マクロ】ファイル名の変更に...
-
Excelで、図形内の文字をセルに...
-
マクロを実行すると画像がズレ...
-
Excel ハイパーリンクのURLを別...
-
フォントの色を指定して削除出...
-
Excelでセルをクリックす...
-
Excel2007 色のカウント (VBA)
-
エクセルでの検索ボックスの作...
-
現在のセルの位置を返す関数は...
-
エクセル マクロ チェックボックス
-
VBA 見つからなかった時の処理
-
アポストロフィーの一括挿入 ...
-
エクセルでPDFリンクを大量...
-
セルの値が変ると自動でマクロ...
おすすめ情報