(再)EXCEL検索値と同列の任意の位置のセル

質問

皆さん、いつもお世話になっております。度々申し訳ございません。
昨晩のご質問ですが仕様が変わりましたので改めて皆さんからのお知恵をいただきたく存じます。情報が不足しておりご面倒をお掛け致します。

例1：関数を使い下記のＣ4～H4(950,860,670)の数値の最小値(670)(間の9(セルD4),3(セルF4),5(セルH4)は検索対象外)をあるセル表示させて、別のセルに最小値(670)が表示されている列の１番上のセル値(SUZ001)を表示させたいのですが、良い方法が有りましたら皆様のお知恵をお借りいたしたく お願いいたします。
(B3～H3(350,400,460)ですと、最小値(350))(間の8(セルD3),5(セルF3),10(セルH3)は検索対象外)を表示させて列の１番上のセル値(SEI001)を表示となります。
間のD列F列H列の数字が検索範囲に含まれないようにする方法で(SUZ001)や(SEI001)を表示させたい。

A　 B C D E F G H(列番号)
(行番号)
1　型　番 SEI001 納期1 NIS001 納期2 SUZ001 納期3
2 ASN310 650 3 560 5 600 1
3 HW1S55 350 8 400 5 460 10
4 HW5S87 950 9 860 3 670 5

回答No.4

bunjii · ベストアンサー

>最小値の表示はされるのですが(SUZ001)や(SEI001)が表示されません。
結果の内容を提示しないと原因がわかりません。

>(1)B3～H3(350,400,460)ですと、最小値(350)で(SEI001)の表示
C3～H3の範囲の誤りではありませんか？
貼付画像は次の数式を使いました。
J2の数式
=IF(COUNT(C2,E2,G2),MIN(C2,E2,G2),"")
K2の数式
=IF(COUNT(C2,E2,G2),INDEX(C$1:H$1,1,MATCH(J2,C2:H2,0)),"")
J2:K2を同時に選択して下へ5行目までオートフィルでコピーしました。

お礼

ご回答いただきありがとうございました。
表までお作り頂き感謝いたします。
なんとか理解できました。
お蔭様で表は完成することが出来ました。
ありがとうございました。
今後とも宜しくお願い致します。

回答No.5

tsubu-yuki

関数についてはご自身でも調べるように心掛けないと
いざというときに応用が利かなくて困りますね。
私も小難しい関数が苦手なので困ってます(笑)。

というか、ご自身でどのようにしてどんな結果が返ってきているか、
出来ればソレもご記載くださいね。
ソレによってアドバイスの内容も変わってしまうかもしれませんから。
「表示されない」のが「空白が返る」なのか「エラー値が返る」なのか、
回答者側には全く見えないというのをお忘れなく。

そんなわけで、２行目についての式。
　　最小値：=MIN(C2,E2,G2)
　　検索値：=INDEX($C$1:$H$1,MATCH(J2,C2:H2,0))
こんな感じでどうぞ。
関数の詳細についてはご自身でもお調べください。

とりあえず・・
MIN関数は引数（括弧の中身）をカンマで区切ることによって
連続しない複数の値から最小値を返します。
MATCH関数は第１引数（検索値）が第２引数（配列・範囲）の中の
どのあたりに位置するかを第３引数（照合の種類）に基づいて返します。
今回は第３引数を「０（完全一致）」で指定しています。
MIN関数で参照している値についての調査ですから、
完全一致する数値が「無い可能性は無い」ので完全一致で探します。
なのでエラー処理も不要です。
INDEX関数は第１引数（配列・範囲）から第２引数（指定位置）にある
値を返します。
この場合、１行目の指定範囲の中からMATCH関数の返り値の位置にある
セルの値を返して来るように指定してやります。
表の構成から見て、行方向にデータが増えていくようですので
第１引数（１行目）の指定範囲は絶対参照指定してやると便利です。多分。

余計なお世話を追記しておきますが、エクセルにおいて
> 検索範囲に含まれない
という考え方は（現段階では）若干危険です。
今のところは「この範囲（連続する・しないに関わらず）から検索する」
という考え方に寄せてやる方がおそらく扱いやすいと思いますよ。
大体の関数はそれでクリアできますから。

お礼

tsubu-yuki 様
ご説明いただきありがとうございました。
丁寧でわかりやすく、私みたいな未熟者の立ち位置でご説明いただき感謝申し上げます。
機会がありましたら、また宜しくお願い致します。

回答No.3

imogasi

#1です。
＃１で、とびとび列の最少値を求めるとき、列セル列挙式の式に関して言及した。
飛び飛びの列（ただし、規則的な飛び飛びに限る）のデータの最少値を求める方法の１つ。
（式に個別に列名を記述しない方法）
例えば、C列からＳ列までの第２５行目に数字が、入っているとする（Ｃ、Ｓ、２５とかは任意です、意味なし）。
そのうちＣ、Ｅ，Ｇ・・と、とびとびの列の同行２５行目に、入っている数字で、一番少ない数字を、関数で求める問題です。
ーー
ただし（関数といっても）「配列数式」を使わざるを得ない。 =MIN(IF(MOD(COLUMN(C25:S25),2)=0,99999,C25:S25)) （Ａ）
この式のMOD(COLUMN(C25:S25),2)=0の部分は列番号が偶数の列の場合 は、 （ＨｉｇｈＶａｌｕｅという場合がある、の）９９９９９をいれ、奇数列番号の場合はそれらの列のセルの値を使って最少値を考える 。９９９９９はデータの数字の大きさについて、
それより絶対大きな数字は出現ないという、安全圏の数字を入れる。 ９９９９９を入れるのは、奇数桁の数字が必ずそれらより小さいので全体の最小値に影響しないと思うからです。
MOD(COLUMN(C25:S25),2)=0,の部分は、偶数の場合、奇数の場合（本例）、３の倍数の場合、４の倍数の場合・・・など、も同じ考えでできる。
この式（（Ａ）の式）を最小値をセットしたいセルに入れて数式バー部の最後をマウスで選択しておいて、Ｓｈｉｆｔ＋ＣＴＲＬ＋ＥＮＴＥＲの３つのキーを同時押しする。
（配列数式という仕組み）
ーー
エクセルの関数を使うといっても 、課題によって、大変な背景知識が要るものもある。
多分質問者の程度だと、配列数式は、すぐには 判らないだろう。
日ごろから勉強しておかないと、エクセルなら、関数でやれそうと予想しても、（ＶＢＡを使う、などに比べてとっつきやすいと思うかもしれないが）、関数でやるが故の、むつかしさもあるので、簡単には使えないと思う。

お礼

imogasi 様
お時間をおとりいただき、度々回答をいただき有難うございました。
関数を簡単に考えていました。奥が深いもので難しいものと改めて認識しました。

回答No.2

bunjii

>間のD列F列H列の数字が検索範囲に含まれないようにする方法で(SUZ001)や(SEI001)を表示させたい。
基本的には前回の質問（https://okwave.jp/qa/q9393668.html）と同じですがMIN関数の範囲をカンマ（,）区切りで飛び飛びに設定することで解決します。
INDEX関数とMATCH関数は連続範囲しか設定できませんのでC2:H2のように設定します。
2行目（J2とK2）の数式は次のようにします。
=MIN(C2,E2,G2)
=INDEX(C$1:H$1,1,MATCH(J2,C2:H2,0))

エラー防止にはIF関数を使って次のように変形します。
=IF(COUNT(C2,E2,G2),MIN(C2,E2,G2),"")
=IF(COUNT(C2,E2,G2),INDEX(C$1:H$1,1,MATCH(J2,C2:H2,0)),"")

補足

bunjii 様
丁寧な回答をいただき、有難うございました。
ご教示いただきました方法で試してみましたが
最小値の表示はされるのですが(SUZ001)や(SEI001)が表示されません。

A　 B C D E F G H(列番号)
(行番号)
1　型　番 SEI001 納期1 NIS001 納期2 SUZ001 納期3
2 ASN310 650 3 560 5 600 1
3 HW1S55 350 8 400 5 460 10
4 HW5S87 950 9 860 3 670 5

(1)B3～H3(350,400,460)ですと、最小値(350)で(SEI001)の表示
(2)C4～H4(950,860,670)ですと、最小値(670)で(SUZ001)の表示
をさせたいのですが、私のやり方が間違っているのでしょうか？

何度も申し訳ありません。

回答No.1

imogasi

前質問と同じタイプの質問じゃないか。
少し応用力を付けること。
ーー
例データ　Ｃ、Ｅ、G列（１列とび）に数値データあるとする。
その最少値を求める。
C列　　　　　Ｅ列　　　Ｇ列
データ１ データ２ データ３ 　最小値
650 560 600 　　　560
350 400 460 　　　350
950 860 670 　　　670
最少とした列の式は（たとえばＨ２セルに）
=MIN(C2,E2,G2)
のような式です。
あとは昨日回答した式でできるのではないか。
列のセルの個別列挙が気に食わないなら、個別列挙をしないで始め列と終り列を指定して（１列とびの）最小値を求めるという、そういう趣旨の質問にして、識者の回答を求めたら。
初心者なら、式の（　）内に列の列挙が早い。行は式の複写で変えられる。