参照セル間の最小値を求めたい

質問

エクセルで見積比較するため、下記の方法で最小値を求めてみました。
W2=MIN(B2,E2,H2,K2,N2,Q2,T2)
(B2,E2,H2,K2,N2,Q2,T2)はVLOOKUPで別シートから参照した数値です。

比較するセルに空欄があるとW2の最小値が空欄になる場合があります。
空欄があっても1以上の数値の中から最小値を求める対処法を教えて下さい。

よろしくお願いします。

回答No.4

Wendy02 · ベストアンサー

こんばんは。

いくつか考えてみました。

#1 の回答への補足の
>=IF(ISNA(VLOOKUP～
の後は、もしかしたら、"" となっているのではありませんか？

>1以上の数値の中から最小値を求める

=SMALL(INDEX(SUBSTITUTE((MOD(COLUMN(A2:T2),3)=2)*(A2:T2>=1)*(A2:T2),"0",10^15)*1,,),1)

ただし、これは、="" という「長さ0の文字列」を除くことは出来ません。それは、数式の建て方や書式の問題です。絶対条件として、元の数式が変えられないのでないなら、エラーがTRUE の時に、「0」を返し、その行の書式を、「#,###,,」 などとしてくれれば良いと思います。

数式：
=IF(COUNTIF(範囲,検索値)=0,0,VLOOKUP(検索値,範囲,列番号,検索の型))

それがダメなら、

=MIN(IF(ISERROR((MOD(COLUMN(A2:T2),3)=2)*(A2:T2)),10^15,SUBSTITUTE((MOD(COLUMN(A2:T2),3)=2)*(A2:T2>=1)*(A2:T2),"0",10^15)*1))

このようにして、配列の確定（一旦式を入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定）する方法があります。

または、以下のようなユーザー定義関数を使うようにするか、いずれかの選択が必要だと思います。

ただ、元の、数式の建て方の工夫をすればよいはずですが。

'--------------------------------------------------------------
汎用型の空白や長さ0の文字列を数えないユーザー定義関数（試作段階）

=Min_Emit(">=1",B2,E2,H2,K2,N2,Q2,T2)

範囲に、数値を直接入れることも出来ます。

=Min_Emit(">=1",B2,E2,H2,K2,N2,Q2,T2,1)
(最小値,1になるはずです）

これは、配列数式も取り込み可能です。

例：
Min_Emit("",INDEX(SUBSTITUTE((MOD(COLUMN(A2:T2),3)=2)*(A2:T2>=1)*(A2:T2),"0",10^16)*1,,))

なお、条件は省略することが出来ません。（なお、これは汎用型で、この質問のために作ったものではありません）
'----------------------------------------------------------------
Public Function Min_Emit(条件 As Variant, ParamArray 範囲() As Variant)
'条件より最小値を求める関数
'条件：例; ">1" , ">=1", 数字単独の場合は、"1" は、"<>1" になる
'範囲：A1,B1,C1 .... ;または、A1:D1
Dim c As Range
Dim MinVal As Variant
Dim v As Variant
Dim a As Variant
If 条件 Like "*#[<->]" Then Exit Function
If 条件 = "" Then 条件 = "<>"""""
If InStr(条件, "=") = 0 And IsNumeric(条件) And VarType(条件) = vbString Then 条件 = "=" & 条件
If VarType(条件) = vbDouble Then 条件 = "<>" & 条件
For Each v In 範囲
　If TypeName(v) = "Range" Then
　　For Each c In v
　　　If Not IsEmpty(c.Value) And IsNumeric(c.Value) Then
　　　If MinVal = Empty Then MinVal = c.Value
　　　　If Evaluate(c.Value & 条件) And Evaluate(MinVal & 条件) Then
　　　　　If MinVal > c.Value Then
　　　　　　MinVal = c.Value
　　　　　End If
　　　　End If
　　　End If
　　Next c
　　'配列の場合
　ElseIf VarType(v) = vbVariant + vbArray Then
　　For Each a In v
　　　If MinVal = Empty Then
　　　　MinVal = a
　　　End If
　　If IsNumeric(a) Then
　　　If Evaluate(a & 条件) And Evaluate(MinVal & 条件) Then
　　　　If MinVal > a Then
　　　　　MinVal = a
　　　　End If
　　　End If
　　End If
　Next a
Else
　'数値の場合
　If Not IsEmpty(v) And IsNumeric(v) Then
　If MinVal = Empty Then MinVal = v
　　If Evaluate(v & 条件) And Evaluate(MinVal & 条件) Then
　　　If MinVal > v Then
　　　　MinVal = v
　　　End If
　　End If
　End If
End If
　Next v
　Min_Emit = MinVal
End Function

お礼

>#1 の回答への補足の
>>=IF(ISNA(VLOOKUP～
>の後は、もしかしたら、"" となっているのではありませんか

その通りでした。
御指摘の通り「0」を返すことにより、
ANo.2の方法のままで全ての条件をクリアできました。

教えて頂いた以降の例は、現在の私のスキルでは理解に至りませんでした。
急を要する作業のため、今はこの状態で進めますが、今後に活用できるよう、この作業が終わり次第じっくり復習したいと思います。

ありがとうございました。

回答No.3

imogasi

いまやってみると、ＭＩＮ関数は空白、文字列が範囲内にあっても正しく最小値を出すようです（＊）。
ですからＶＬＯＯＫＵＰ関数で該当アイテム・エントリがない時など
空白を返しておけば、思いの結果になりませんか。
ーー
最小値を求めるとき、セルの値でもって、限定したものを対象にする方法
=MIN(IF(B2:B10<>"",B2:B10,""))
ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ
配列数式です。
ただ本件では（＊）の性質を使うので、これを持ち出すまでもないのですが、見積もり１０以上（下）の平均は？などに使えるででしょうから、上げときます。

回答No.2

ham_kamo

一応、参照先のセル（B2とか）はいじらない方法を。

とは言え、関数を駆使していろいろやろうとしたのですが、うまくいかなかったのでユーザ定義関数を作ってしまいました。マクロを使わなくてもできる方法があると思うのですが、参考までに。

Alt+F11でVBAの画面を開き、「挿入」＞「標準モジュール」を選択して、以下のマクロを貼り付けます。

Function L(n As Long) As Long
　　Application.Volatile
　　If n <> 0 Then
　　　　L = n
　　Else
　　　　L = 100000
　　End If
End Function

100000のところは、最小値になり得ないくらいの十分大きな数値を指定してください。

Excelの画面に戻って、
W2=MIN(L(B2),L(E2),L(H2),L(K2),L(N2),L(Q2),L(T2))
とすると、空欄や０の数値以外を省いた数値の最小値が求められます。

お礼

私の質問に足りない点がありました。
ANo.4にあるように根本的な問題でした。
参照関数に「0」を返す事で解決できましたので、
先に設定してあったham_kamo様の関数を利用させて頂きます。
ありがとうございました。

補足

マクロは既に使用しているので問題ありません。
教えて頂いた方法で「出来た！」と思ったのですが、
特殊な事情による条件に対応できませんでした。

当初の質問の答えがあれば解決できると思っておりましたが、説明が不足していたようです。

*************************************************
"シート1～7"に各支店の扱い業者からの見積比較表があります。この質問で作成する"シート8"のA列には"シート1～7"の全ての品目が抽出されてあり、質問文中の参照セルにはA列の品目を元に参照した各支店の最低価格が入ります。支店によって取り扱いがない品目がある場合、その支店のシートに参照する品目が存在しないため、"シート8"の参照セルは空欄になります。
"シート1～7"の形式は、各支店のシステムから抽出されたCSVを元に加工しているため、大きな変更は困難です。
*************************************************

上記の設定で一部支店に取り扱いがなかった場合の空欄があると、#VALUE!となってしまいます。
回避する手段はないでしょうか？

回答No.1

noname#135138

ちょっと邪道ですが、VLOOKUPで参照したセルにIF文を付け加える方法で、
=IF(VLOOKUP文=0,sum(B2,E2,H2,K2,N2,Q2,T2),VLOOKUP文)

　この方法ですと、参照した所が０の場合、参照セルを合計した値を入れ、そうでない場合は
参照セルの値が入ります。
これでセルの値が無かったところには最大の値が入ることになるので、最小値が検出出来るようになります。

いかがでしょうか？

補足

早速のお返事ありがとうございます。
教えて頂いた方法は比較表として印刷する場合に問題がありそうです。
他の場面で応用できるよう覚えておきます。

補足しますと「VLOOKUPで別シートから参照」と書きましたが、
正確にはエラー表示を回避するため下記の記述がしてあります。
=IF(ISNA(VLOOKUP～

引き続き、対処法がありましたらお願い致します。