No.4ベストアンサー
- 回答日時:
こんばんは。
いくつか考えてみました。
#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
- 回答日時:
いまやってみると、MIN関数は空白、文字列が範囲内にあっても正しく最小値を出すようです(*)。
ですからVLOOKUP関数で該当アイテム・エントリがない時など
空白を返しておけば、思いの結果になりませんか。
ーー
最小値を求めるとき、セルの値でもって、限定したものを対象にする方法
=MIN(IF(B2:B10<>"",B2:B10,""))
SHIFT+CTRL+ENTER
配列数式です。
ただ本件では(*)の性質を使うので、これを持ち出すまでもないのですが、見積もり10以上(下)の平均は?などに使えるででしょうから、上げときます。
No.2
- 回答日時:
一応、参照先のセル(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))
とすると、空欄や0の数値以外を省いた数値の最小値が求められます。
この回答への補足
マクロは既に使用しているので問題ありません。
教えて頂いた方法で「出来た!」と思ったのですが、
特殊な事情による条件に対応できませんでした。
当初の質問の答えがあれば解決できると思っておりましたが、説明が不足していたようです。
*************************************************
"シート1~7"に各支店の扱い業者からの見積比較表があります。この質問で作成する"シート8"のA列には"シート1~7"の全ての品目が抽出されてあり、質問文中の参照セルにはA列の品目を元に参照した各支店の最低価格が入ります。支店によって取り扱いがない品目がある場合、その支店のシートに参照する品目が存在しないため、"シート8"の参照セルは空欄になります。
"シート1~7"の形式は、各支店のシステムから抽出されたCSVを元に加工しているため、大きな変更は困難です。
*************************************************
上記の設定で一部支店に取り扱いがなかった場合の空欄があると、#VALUE!となってしまいます。
回避する手段はないでしょうか?
私の質問に足りない点がありました。
ANo.4にあるように根本的な問題でした。
参照関数に「0」を返す事で解決できましたので、
先に設定してあったham_kamo様の関数を利用させて頂きます。
ありがとうございました。
No.1
- 回答日時:
ちょっと邪道ですが、VLOOKUPで参照したセルにIF文を付け加える方法で、
=IF(VLOOKUP文=0,sum(B2,E2,H2,K2,N2,Q2,T2),VLOOKUP文)
この方法ですと、参照した所が0の場合、参照セルを合計した値を入れ、そうでない場合は
参照セルの値が入ります。
これでセルの値が無かったところには最大の値が入ることになるので、最小値が検出出来るようになります。
いかがでしょうか?
この回答への補足
早速のお返事ありがとうございます。
教えて頂いた方法は比較表として印刷する場合に問題がありそうです。
他の場面で応用できるよう覚えておきます。
補足しますと「VLOOKUPで別シートから参照」と書きましたが、
正確にはエラー表示を回避するため下記の記述がしてあります。
=IF(ISNA(VLOOKUP~
引き続き、対処法がありましたらお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) エクセル関数の質問 5 2022/04/20 09:46
- Excel(エクセル) エクセルの数式について教えて下さい。 8 2023/05/27 12:17
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Excel(エクセル) エクセル 3つの値の中からデータを抽出させる方法 4 2023/08/24 11:00
- 統計学 統計学の問題です よろしくお願いします 代表値 次の15件のデータについて,以下の問いに答えよ。 結 1 2023/01/31 18:53
- Excel(エクセル) 【エクセル】複雑な関数を教えてください 1 2023/06/05 18:09
- 統計学 統計学の問題です よろしくお願いします 代表値 次の15件のデータについて,以下の問いに答えよ。 結 5 2023/01/31 23:35
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
なぜ、最小値がないのかが分か...
-
極大値・極小値 を英語で
-
①とても初歩的なことなのですが...
-
3σと最大値,最小値
-
数値データの規格化
-
複素数平面
-
このフローチャートがわかりま...
-
(2)の問題を解くときに、最初...
-
マルチディスプレイ【2台】に...
-
2次関数の問題です。よろしく...
-
max,minの意味
-
2次関数の最大・最小問題の場...
-
三角形 角度が最大になるときの辺
-
数学 二次関数についてです。 ...
-
二次関数の場合分けでの不等号...
-
(数学II)加法定理の応用
-
数学の表記の表し方で最大値と...
-
範囲の始まりと終わりの値の名称
-
最大の定理、最小の定理の証明
-
ラグランジュの未定乗数法の連...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
極大値・極小値 を英語で
-
小学5年算数。階乗の関数
-
なぜ、最小値がないのかが分か...
-
数学 2時間数に関わる問題につ...
-
aを正の定数とし、f(x)=x²+2(a-...
-
①とても初歩的なことなのですが...
-
Ankerのケーブルについて Anker...
-
マルチディスプレイ【2台】に...
-
数値データの規格化
-
MOS365 Excel Expert / Excel R...
-
範囲の始まりと終わりの値の名称
-
max,minの意味
-
基本情報処理 平成27年春期 ...
-
はめあいの『最大すきま』と『...
-
数学の問題です。
-
x(x-1)(x-2)(x-3)の最大値と最...
-
数II:三角関数の合成です
-
数学 二次関数についてです。 ...
-
(2)の問題を解くときに、最初...
-
数学のことで質問があります。
おすすめ情報