こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excelで最小値のアドレスを求める

 A   B     C    D
1            77580
2        77850
3     74610
4   74880
上記Excelの表で
A1:D4 の範囲で最小値を求めそのアドレスを求めたい
最小値はMIN(A1:D4 )で求めることができるのですが
ここからアドレスを求める方法を教えてください
よろしくお願いいたします。

投稿日時 - 2019-10-18 20:37:41

QNo.9668495

すぐに回答ほしいです

質問者が選んだベストアンサー

>VB初心者なのでよろしくお願いします
VBE画面で、ツール>オプション>編集タブ で「変数の宣言を強制する」にチェックを入れます。VBAの研修で最初に行う作業がこれです。コードウインドウの1行目に「Option Explicit」が表示されます。これで、初心者でも(良い)経験者と同じ環境での開発になります。逆に強制していない(悪い)経験者の作成物はすぐに破綻します。変数の宣言を行うことで、自分がどのようなプログラミングをしようとしてるかVBEに教え、VBAの中身に触れ、VBEはある程度の検証をしてくれます。

VBA初心者の研修では、
 ・変数の宣言を強制する
 ・自動インデントを使う
 ・フォントに「Pの付いたプロポーショナルフォントを使わない」
 ・コーディングは「自分の行いたいことを素直に書けるようにしよう」
 ・動くみにくいプログラムより、動かない見やすいプログラムを作ろう
 ・プログラム構造は、順次、選択、反復の3つ
 ・自分のプログラムでエラーが出る場合を考えよう
 ・実行する前に必ず保存しよう
などを10分程度で説明し、「これで研修終わり」とか冗談を言っていました。

質問の件ですが、
 ・未入力セルは最小値判定から除外
   (ワークシート関数Minは除外している)
 ・複数の最小値セルが存在する可能性がある(個数は不明!)
 ・出力方法は指定がないので、指定列に縦(行方向)に表示します。
   (出力方法についてはいろいろあると思います。ご参考に)

このような条件でコードを書いてみました。標準モジュールに貼り付けます。「Option Explicit」が2行になったら、片方を消してください。行ごとにコメントを書いているので、長くなっています。
調べたいセルを選択した状態で、「searchMin」を実行します。当方、Win10、Excel2010です。ご参考に。


Option Explicit

Sub searchMin()
 Dim rg As Range     '// 調べるセル
 Dim workMin As Double  '// 最小値を保持
 Dim minAdr As String   '// 最小値セル番地
 
 '// 仮に最大値を最小値にする
 workMin = Application.WorksheetFunction.Max(Selection)
 '// 各セルを調べる
 For Each rg In Selection
  '// 未入力セルは調べないことにする(ゼロになるので)
  If rg <> "" Then
   Select Case True
    Case rg.Value < workMin '// 新たな最小値
     '// 新たなアドレス
     minAdr = rg.Address(0, 0)
     '// 新たな最小値にする
     workMin = rg.Value
    Case rg.Value = workMin '// 同じ最小値
     '// アドレスを「,」で結合する
     minAdr = minAdr & "," & rg.Address(0, 0)
    Case Else
     '// 最小値でなければ何もしない
   End Select
  End If
 Next
 
 '// 最小値の出力と複数セル座標を指定列に
 '// 縦に表示しようとしています
 '// 質問の主旨とは違うかもしれません。ご参考に。
 Dim ary() As String  '// 出力を配列にする。配列は0から
 Const oCol = "F"   '// 出力列指定
 
 Range(oCol & ":" & oCol).ClearContents '// 出力列をクリア
 ary = Split(minAdr, ",") '// 出力を配列にする
 Range(oCol & "1") = "最小値:" & workMin '// 最小値を表示
 Range(oCol & "2:" & oCol & UBound(ary) + 2) = _
   WorksheetFunction.Transpose(ary) '// 出力列から縦に出力
End Sub

投稿日時 - 2019-10-20 07:18:00

ANo.6

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

#3です。質問者の補足に関して、
参考にしてください。
私の回答は、
https://kokodane.com/2013_macro_42.htm
の中程以下にある
「ユーザー定義関数は、ワークシート上で使うこともできます」
の使い方に当たります。
ユーザー定義関数は、
Functuin
値を求める処理
End Function
は、Sub プロシージュアーの中でもよく使います。質問者はそちらの方に、頭が行ってませんか。
まず、小生が書いたように、そのままやってみてください。
ーー
付言
なお回答のApplication.Minの部分は、回答の通りでも動いたのですが、
マイクロソフトが作ったエクセルの既製の関数ですので、
Application.WorksheetFunction.Min(・・・)
とする方が正式です。
(既製のSUM関数をVBAで使うときは、例えば、Sum関数を使うときは、Application.WorksheetFunction.Sum(Range(Cells(i, 2), Cells(i, 3)))
のように)
===
複数の該当がある場合の質問は、別質問をたてて、改めて質問してください。
VBAの回答が出た場合のそれを理解するなどは、生易しい理解・背景知識では、判らないと思う。
関数では複数の値を返すのは、配列数式だが、その場合使えるかどうか。
==
セル番地を求める、という「求め」(の質問)自体が、求まったらそれでその後「どうするねん」と思ってしまう。

投稿日時 - 2019-10-20 13:51:14

お礼

ありがとうございます。やってみます。

投稿日時 - 2019-10-20 18:51:45

ANo.5

=ADDRESS(SUM((A1:D4=MIN(A1:D4))*ROW(A1:D4)),SUM((A1:D4=MIN(A1:D4))*COLUMN(A1:D4)))
【お断り】上式は必ず配列数式として入力のこと

投稿日時 - 2019-10-19 00:23:09

お礼

回答ありがとうございます。やってみます。

投稿日時 - 2019-10-19 20:25:32

ANo.4

=ADDRESS(SUMPRODUCT((A1:D4=MIN(A1:D4))*(ROW(A1:D4))),SUMPRODUCT((A1:D4=MIN(A1:D4))*(COLUMN(A1:D4))))
【独白】式とスカートの丈は少しでも短い方が好き、かな?(*^_^*)

投稿日時 - 2019-10-19 00:09:52

お礼

回答ありがとうございます。やってみます。

投稿日時 - 2019-10-19 20:25:58

ANo.3

Excelのワークシート関数での、やり方は、既に出ているが、理解するのもむつかしい。(どういうものであれ、関数でできて結果が正しければよいのなら、
下記は飛ばしてもらっても良い。)
それでVBAの関数でやってみた。実質はコード3行。
最小値をもとめて、その番地を番地の記号の形で、求める。
標準モジュールに
Function minadd(rng As Range)
m = WorksheetFunction.Min(rng)
Set r = rng.Find(m)
minadd = r.Address
End Function
と入れる。
シートに戻って
A1:D4が
12354472
13101112
42651018
10724329
とすると、上記以外の1セルに
=minadd(A1:D4)
と入れると
そのセルは、「$B$2」とでる。
Z状にセルを探索して行って、最小値に初めて出くわしたセル番地を返す。
1つしか返さない。
関数での回答の結果も1つしか返さないだろう。
複数ある場合には複数返すコードはややむつかしくなる。
ーー
該当セルが2つ以上あるのかないのか質問者は言及してない(例では1つだが全貌は分からないと思っている。注意書きを描いてないのが、経験が少ないためだろう)
余り出ない質問だと思うが、この質問は、難しい点を含んでいると思う。
ーー
なお、MATCH関数は
=MATCH(MIN(A1:D4),B1:B4,0)のように1列や1行しか探索しない。
また1つしか、結果を返さない。

投稿日時 - 2019-10-18 22:10:57

補足

回答ありがとうございます
Functionプロシージャは使い方がよくわからないので以下のようにしてみました
rng As Range  ’変数定義
mg=”A1:D4" 'データーの範囲指定
m = WorksheetFunction.Min(rng)   ’VBでMinが使えるように定義。最小値の検索
Set r = rng.Find(m)          ’変数rにセル位置をセット
minadd = r.Address          ’変数minaddに$B$2形式で変換し保存
print minadd             ’表示
上記のようになしても動きますか?

Functionプロシージャを使った場合
戻り値が minaddだと思うのですが?

”上記以外の1セルに=minadd(A1:D4)と入れるとそのセルは、「$B$2」とでる”とあるのです
質問
1)”上記以外の1セルに=minadd(A1:D4)と入れる” とは、プログラム上参照するセルの指定がないのですが?
2)「”$B$2」とでる”あるのですが どこに表示されるのでしょうか?
が意味がよく分かりません。
3)変数mgへの範囲の入力はコード上どのように入力するのでしょうか?

”複数ある場合には複数返すコードはややむつかしくなる”
問題を単純化している為複数ある場合もあると思います。
できれば複数の場合も教えてください。

VB初心者なのでよろしくお願いします?

投稿日時 - 2019-10-19 14:59:01

ANo.2

提示のパターンのように各行に数値のセルが1つで、然も、各列に数値のセルが1つでしょうか?
各行の数値のセルが複数で、然も、各列の数値のセルも複数の場合も対応できるようにする場合は次の数式で試してください。
=ADDRESS(MAX(INDEX((A1:D4=MIN(INDEX((A1:D4="")*MAX(A1:D4)+(A1:D4<>"")*A1:D4,0)))*ROW(A1:D4),0)),MAX(INDEX((A1:D4=MIN(INDEX((A1:D4="")*MAX(A1:D4)+(A1:D4<>"")*A1:D4,0)))*COLUMN(A1:D4),0)),4)

投稿日時 - 2019-10-18 21:31:36

お礼

回答ありがとうございます。やってみます。

投稿日時 - 2019-10-19 20:26:29

ANo.1

=ADDRESS(SUMPRODUCT((A1:D4=MIN(A1:D4 ))*(ROW(A1:D4))),SUMPRODUCT((A1:D4=MIN(A1:D4 ))*(COLUMN(A1:D4))))

でいかがですか。

投稿日時 - 2019-10-18 20:57:50

お礼

回答ありがとうございます。やってみます。

投稿日時 - 2019-10-19 20:26:46

あなたにオススメの質問