ExcelVBAにて、検索した数値を計算する方法

質問

こんにちは。ExcelVBAを最近はじめたばかりの初心者です。

とある一列にランダムな値の数値が並んでいるとして、その数値列からの最大値（最小値）を検索し、その検索された値を用いて計算を行う、
ということは出来ないのでしょうか？書籍や検索システム等活用しましたが、なかなかこれという例がないのでこちらに質問させていただきました。

例えばこんな表があるとすると
　1　 　2　　3　. .
A 14　35　25
B 67　46　37
C 32　73　85
.
.
この表の第１列の最大値を検索
　　　　第２列の最大値を検索
　　　　第３列の最大値を検索
した後、最大値同士の平均値を計算するというプログラムを作成したいです。
また、同じプログラムの内容を用い、たとえ表の値の並びが違ってる他のExcelデータを用いても、同様に処理できるようにしたいです。

どうかよろしくお願い致します。

回答No.2

qualheart · ベストアンサー

ほんの一例です。
・数字が入力されている行の間に空白はない
・数字が入力されている列の間に空白はない
上記2点が問題なければ、列数・行数に関係なく動作します。
算出した平均値は、仮に新しいシートを作成してA1セルに入力するようになっています。

Sub Macro1()
i = 1
Do Until Cells(1, i) = ""
i = i + 1
Loop

ReDim varArray(i) As Integer
For clm = 1 To i - 1
varArray(clm) = Cells(1, clm)
n = 2
Do Until Cells(n, clm) = ""
If Cells(n, clm) > varArray(clm) Then
varArray(clm) = Cells(n, clm)
End If
n = n + 1
Loop
Next

For clm = 1 To i - 1
a = a + varArray(clm)
Next

Set NewSheet = Sheets.Add
NewSheet.Cells(1, 1).Value = a / (i - 1)

End Sub

お礼

コードわざわざ作ってくれたのですね。
ありがとうございます。

こちら参考にさせていただきます。

回答No.4

redfox63

ワークシート関数を使っての例

Sub CalcMaxAverage( rSrc as Range, rDest as Range )
　　dim rColumn as Range, n as integer, dRes as Double
　　n = 1
　　for each rColumn in rSrc.Columns
　　　　dRes = WorkSheetFunction.Max( rColumn.value )
　　　　rDest.Cells( 1, n ).value = dRes
　　　　n = n + 1
　　next
　　rDest.Cells(1,n).FormulaR1C1 = "=AVERAGE(rc[-" & n & "]rc[-1])"
end Sub

呼び出し側
Sub Macro1()
　　CalcMaxAverage　Range("A1:C20"), Rage("E1")
End Sub
といった具合です
A1:C20に元データがって E1:G1に各列の最大値、H1にE1:G1の平均
といった具合になります

回答No.3

sirochacha

#1です
コード例は#2の方が記述されていますので
私はワークシート関数を利用した方法を記します
マクロの記録を使って記録しつつどのようなコードになるかを参考にされると良いと思います
なお、ワークシート関数を使って処理を行う場合
自作のロジックよりも処理時間が短縮される事が多いです
処理件数が増えれば増えるほどその結果は見違えるほどの差になりますのでご参考に

※仮にこの最大値を表示（仮保存）させる領域を各列1行目とします
1：1行目の行選択をします
2：選択した行を追加します
3：ワークシート関数　=MAX（範囲）を必要な列の1行目に記述します
（この時範囲は見出しがあるなら見出しを除いた行から65536行目までとすると行数の変化に対応できます
また、A1に記述した関数をそのままB列以降にコピーする事が有効です）
4：出揃った最大値に対して好きなセル位置で関数　=AVERAGE（範囲）で平均を算出します
（5：関数や式を入れることによってファイルが大きくなる、重くなるなどのケースを回避するために
全セルを選択＞コピー＞形式を選択して貼り付け＞値にチェック＞OK）

以上をマクロで記録して、VBAのソースをご覧になるとよいでしょう
理解が深まったところで変数などを使い、必要に応じてソースの加工をされると良いと思います

※1行目を追加して計算するのはデータがどの行・列数まであるか把握できないため
また増減した時に対応するためです

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/itiran.html

回答No.1

sirochacha

例を探すよりもご自分で模索した方が身に付くと思いますが・・・

まずご質問の要件が完全ではないので具体的な回答は控えますが
・列ごとにデータの行数が異なるか否か
・データの途中に空白があるか否か
・仮保存にセルを使うか変数を使うか
・データ処理にエクセル関数を使うか自分でロジックを作成するか
・VBAと言ってもマクロを記録＞保存＞実行なのかソースから作成するのか否か
などの条件によって回答が異なります

ですが、ご自身で書かれた

＞この表の第１列の最大値を検索
　　　　第２列の最大値を検索
　　　　第３列の最大値を検索
した後、最大値同士の平均値を計算するというプログラムを作成したいです

をそのままプログラムにするだけです

1：第一列の先頭から順に終端まで走査する
2：前回または仮保存したデータより大きな値があればその値に書き換える
3：1・2を行いたい列数分繰り返す
4：揃った仮保存データから平均を計算する

これを自作ロジックなりワークシート関数なりで処理していくだけです
頑張ってください

お礼

まずはご回答ありがとうございます。
楽な道で実践しようとしているようで恐縮ですが、
急を要する作業なので、模索している時間なく、わかりやすい例だけ求めざるを得なくなっている状況です。

あと、例を複雑にしすぎたことをお詫びいたします（ほぼワンコマンドでできる方法があると思っていました）

私が一番知りたい事は、
ランダムに数値が並んでいる表のある列の中から、最大値（最小値）を検索し、検索された数値のセルを選択している状態にする
と言う事です。

今まで調べた分かっている事を述べると、
・検索を行うといってもCELL関数を用いてのaddressやcontentsなどは任意のセルに検索された値を返してくれるだけで選択してくれない。
・条件付書式でも、検索したいセルを書式などで目立つようにしてくれるが、その検索したい（し終えた）セルを選択してくれない。
・Ctrl+Fの検索は検索し終えたセルを選択してくれるが、最大値を探すなどの器用な検索は出来ない。
程度の事ですが、他に何か良い方法があれば知りたいと思っています。
それとも何か難解なプログラミングを必要としてしまうでしょうか。

なにかヒント程度でも教えていただければ幸いです。