Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 Pro : Excel 2013)
範囲が可変の場合の平均値の求め方
投稿日時: 18/07/14 15:12:10
投稿者: tansandaisuki

範囲が可変の場合の平均値を求めるコードをご教示下さい。
範囲は、アクティブセルの一つ上から、またC8のセルの個数と同様という2つの法則を持っています。
 
例えば、アクティブセルがO23でC8の値が4の場合、O19:O22 が範囲になります。
アクティブセル、C8の両方が可変になります。
 
アクティブセルがO21でC8が3の場合、O18:O20のケースなど無限に存在します。

回答
投稿日時: 18/07/14 16:06:41
投稿者: hatena
投稿者のウェブサイトに移動

VBAでということですね。
 
アクティブセル は ActiveCellで取得できますね。
 
それに対して、Offsetメソッド、Resizeメソッドで範囲を設定できます。
 
範囲が取得できれば、WorksheetFunction.Average で平均を求めればいいでしょう。

投稿日時: 18/07/14 16:36:56
投稿者: tansandaisuki

ご回答ありがとうございますm(_ _)m
アクティブセルはoffsetで取得できています。
また、範囲も同様です。
 
例えば、範囲は以下のような感じです。
 
Range(Selection, selection.offset(-Val(Range("C8").value) + 1, 0)).select
 
この範囲の平均値を求める場合のコードがわからず苦戦しております。
ご教示お願いいたします。

回答
投稿日時: 18/07/14 17:26:02
投稿者: hatena
投稿者のウェブサイトに移動


Range(Selection, selection.offset(-Val(Range("C8").value) + 1, 0)).select


selection と ActiveCell は微妙に意味が異なります。
一つのセルを選択している場合は同じ結果になりますが、複数のセルを選択している場合は異なる結果になります。
今回の質問なら、ActiveCell の方が適切に思います。
 
あと、その式だと、アクティブセルから上にC8の数値分の範囲になります。質問だと、アクティブセルの一つ上から上にC8の数値分の範囲にですので、これも微妙に違いますね。
 
まあ、その辺はそちらの判断にお任せするとして、希望の範囲が select できたなら、
下記で、C10 に平均値が表示されます。

Range("C10").value = WorksheetFunction.Average(Selection)


 
ただ、Selectせずに、直接、範囲を指定しても可能です。
 

Dim n As Long
n = Val(Range("C8").value)
Range("C10").value = WorksheetFunction.Average(ActiveCell.Offset(-n).Resize(n))


最初の質問内容に沿ったものです。

投稿日時: 18/07/14 18:49:35
投稿者: tansandaisuki

ご回答ありがとうございます。
当初、アクティブセルに計算結果を返そうと思いましたが、この方法は使えないようですね^^;
計算結果を返したいセルはこの列の一番下になりますが、この場合、どうなりますでしょうか?
vbaの関数の使い方を完全に理解できていないので、拙い説明になってしまい申し訳ございません。

回答
投稿日時: 18/07/14 20:50:32
投稿者: simple

横から失礼します。

引用:
当初、アクティブセルに計算結果を返そうと思いましたが、この方法は使えないようですね^^;
計算結果を返したいセルはこの列の一番下になりますが、この場合、どうなりますでしょうか?
vbaの関数の使い方を完全に理解できていないので、拙い説明になってしまい申し訳ございません。

質問が平均値の計算なので、書込先に話の重点がなかったので、仮ですよ。
アクティブセルは使えないというのはなぜですか?
平均値の計算ではアクティブセルは動かしてないから、問題ないはずです。
ActiveCell.Value = ..... (右辺は平均を算出したもの。変わらず)
とすればアクティブセルに書き込めると思いますよ。
 
>計算結果を返したいセルはこの列の一番下になります
アクティブセルのある列の最終行の次行なら、
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1).Value = ......(同上)
ですね。

回答
投稿日時: 18/07/14 22:24:53
投稿者: mattuwan44

引用:
例えば、アクティブセルがO23でC8の値が4の場合、O19:O22 が範囲になります。
アクティブセル、C8の両方が可変になります。
 
当初、アクティブセルに計算結果を返そうと思いましたが、この方法は使えないようですね^^;
計算結果を返したいセルはこの列の一番下になりますが、この場合、どうなりますでしょうか?
vbaの関数の使い方を完全に理解できていないので、拙い説明になってしまい申し訳ございません。

 
あのですね。
VBAでコード書いて、マクロ作るじゃないですか。
このとき、tansandaisukiは、
エクセル君に、
「ここを、こんな風にして、平均を出してください。」
と頼んでるのと同じことなんですよ。
ということは、
他人に仕事を文章で頼んでいるのと同じなんですよ。
その時にですね、
「ここ」とか、「こんな風に」ではやってほしいことが伝わらないですよね?
そこを「可変」とか「任意」とか言っても同様にわかりませんよね?
 
例えば、
 
C8の値は処理する列数を示す
計算したい表は、I10から、O列のデータ最終行
O列は平均した結果を入力する用の列
という前提条件をまず示す。
そして、やりたいことは、、、、
表の下の新しい行にデータを1行追加したら、
その行のO列にその左隣りから、C8セルの値分の列数を左に拡張したセル範囲の平均を入力する

 
という風な書き方をしたら、伝わりませんかね?
そういう説明というか、作業の手順を書くのがプログラムです。
日本語でそういう説明ができれば、
それを、エクセル君に伝わる言葉(=VBA)に翻訳するだけです。
なので、まずは日本語で作業の流れを説明できるようになりましょう。
その次に、それに対応するVBAの単語なり文法なりを覚えましょう。
VBAの翻訳は回答者が手伝ってくれますが、
tansandaisukiさんがどうしたいのかを決めて、説明できないと、
アドバイスなりサンプルなりの提示が困難なのは理解してください。

回答
投稿日時: 18/07/14 22:29:16
投稿者: mattuwan44

すみません。単にコピペした後、油断しました。
訂正です。失礼しました。
 
>このとき、tansandaisukiは、
このとき、tansandaisukiさんは、
 
(折り畳み式キーボードの配列に中々慣れない今日この頃です><)

回答
投稿日時: 18/07/15 12:04:48
投稿者: simple

へたに省略したので通じていないのでしょうか。

Dim n As Long
n = Val(Range("C8").value)
ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(-n).Resize(n))

投稿日時: 18/07/15 17:43:58
投稿者: tansandaisuki

返信遅れてすみません。
simpleさんのコードで解決しました。
他の方もご意見ありがとうございました!
とても勉強になりました!!