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
'----------------------------------------------------------
度重なる質問にレスしていただき、ありがとうございます。
マクロについては、標準モジュール・シートモジュールと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で質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
ロボットの住む世界で流行ってる罰ゲームとは?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
直近の5個の平均を求めたい
Excel(エクセル)
-
空白セルもある”〇”データの直近3回の平均値を出す
Excel(エクセル)
-
日付をさかのぼる形で、データがあるセルのうち直近の3個のセルの平均を求める
Excel(エクセル)
-
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで特定のセルに表示...
-
エクセル 数字をすべて○などの...
-
エクセル シート保護された共...
-
Excelで、図形内の文字をセルに...
-
Excel内での検索結果をシート...
-
クリックすると文章が表示され...
-
セルの値が変ると自動でマクロ...
-
ページ内ハイパーリンクの表示...
-
Excel ハイパーリンクのURLを別...
-
エクセルマクロ 赤色の文字を検...
-
フォントの色を指定して削除出...
-
現在のセルの位置を返す関数は...
-
マクロを実行すると画像がズレ...
-
シート保護とグループ化機能を...
-
太字に設定されているセルの個...
-
セルの内容をテキストボックス...
-
EXCELのセルや文字色の反映
-
VBAでセル内の指定した文字を太...
-
セルがクリックされた回数をカ...
-
エクセルでPDFリンクを大量...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで特定のセルに表示...
-
Excel内での検索結果をシート...
-
エクセル 数字をすべて○などの...
-
クリックすると文章が表示され...
-
太字に設定されているセルの個...
-
Excelで、図形内の文字をセルに...
-
Excelでセルをクリックす...
-
Excel ハイパーリンクのURLを別...
-
ページ内ハイパーリンクの表示...
-
マクロを実行すると画像がズレ...
-
現在のセルの位置を返す関数は...
-
セル背景や文字を点滅させる方法
-
エクセルでPDFリンクを大量...
-
EXCELのセルや文字色の反映
-
エクセル 未入力セルがあると...
-
【EXCEL】先週の月曜日の日付を...
-
エクセルでページ数をあるセル...
-
セルがクリックされた回数をカ...
-
VBA 見つからなかった時の処理
-
フォントの色を指定して削除出...
おすすめ情報