この関数文の意味を教えてもらえないでしょうか？

質問

下記の関数文の意味が分からないので
教えて頂けないでしょうか？

=INDEX(sheet2!F:F,MIN(INDEX(SUBSTITUTE((sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,sheet2!F1:F100)),0,10^5)*ROW(sheet2!F1:F100),)))

尚、(sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,sheet2!F1:F100))の範囲を指定してF9を押すと

=INDEX(sheet2!F:F,MIN(INDEX(SUBSTITUTE({1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;1;0;0;0;0;1;1;1;1;1;1;1;1;1;1},0,10^5)*ROW(sheet2!F1:F100),)))
と表示されます。

・SUBSTITUTEの関数は何の目的で使用しているのでしょうか？

以上、宜しくお願いします。

回答No.3

maron--5 · ベストアンサー

>★{0;0;1;0;1;0;0;0;0;・・・・0;0;0;0}　のようになります
>→なぜ0と1に分かれるのでしょうか？
◆条件式の場合、条件を満たしていれば、TRUE、そうでない場合は、FALSE　を返します
◆それに*1（1をかけますと）をしますと、TRUEは「1」、FALSEは「0」になります
★そこで、もとに戻りますが、「0」は条件を満たさないもの、「1」は条件を満たすものになります

>★0,10^5
>→0を100000に置き換えるという意味で10^4でも問題ないということでしょうか？→10000になる(1はそのままで置き換えない)
◆この問いの前に、SUBSTITUTE関数をヘルプで調べてください
◆それが理解できれば、なぜ10^5（または10^4）に置き換えるかですが、0を除く一番小さい数字を抽出したいからです
◆そのために（０を排除するために）0を式で考えられる一番大きい数字より大きい数字になるように、0を10^5とか10^4に置き換えています

>★*ROW(sheet2!F1:F100)
>→F列の1～100の行数をそれぞれかけているという意味でしょうか？
◆その通りです

◆人に聞いて、教えてもらうのも近道かもしれませんが、自分で回り道をしながらやっとわかると、もっと楽しさを感じ、もっと自分のものになると思いますよ！

お礼

maron--5さん、回答ありがとうございました。
何となく分かってきました。
あとは自分で調べてみます。
ありがとうございました。

回答No.2

maron--5

=INDEX(sheet2!F:F,MIN(INDEX(SUBSTITUTE((sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,sheet2!F1:F100)),0,10^5)*ROW(sheet2!F1:F100),))

◆上の式は、下の式のような「配列数式」と同じです
=INDEX(Sheet2!F:F,MIN(IF((Sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,Sheet2!F1:F100)),ROW(Sheet2!F1:F100))))

★「配列数式」の場合、式を入力後、　Ctrl＋Shift　を押しながら、Enter　を押して式を確定する必要があります
★なお、式が確定すると式の両端に、｛　｝がつきます
★「配列数式」については、下のＵＲＬを見てください

>SUBSTITUTE関数は何の為に使用しているのでしょうか？
◆Ctrl＋Shift　を押しながら、Enter　を押すことを、しなくてもいいようにするために使っています（なお、SUBSTITUTE関数を使用する方法以外にもそれぞれ工夫していろんな方法があります）

★(Sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,Sheet2!F1:F100))の部分を指定して、「F9」を押してください
★{0;0;1;0;1;0;0;0;0;・・・・0;0;0;0}　のようになります
★このままでは、この中のMINは、いつも「0」になり正しい結果にはなりません
★そこで、この「0」をSUBSTITUTE関数で「10^5」の置き換えることによって、{"100000";"100000";"1";"100000";"1";"100000";・・・・;"100000";"100000"}　として、これに　*ROW(sheet2!F1:F100)　をかけて
★{100000;200000;3;400000;5;600000;700000・・・}の中から、一番小さい「3」を抽出しています
★よって、sheet2!F:F　の３行目、すなわち、sheet2!F3　がその求める答えになります

◆今回は下のような例で試してみました
Sheet2
　　　　A　　B　　C　　D　　E　　F
1　　　b　　　　　　　　　　　　　　14
2　　　v　　　　　　　　　　　　　　25
3　　　a　　　　　　　　　　　　　　72
4　　　i　　　　　　　　　　　　　　526
5　　　a　　　　　　　　　　　　　　20
6　　　k　　　　　　　　　　　　　　1
7　　　m　　　　　　　　　　　　　　14
8　　　a　　　　　　　　　　　　　　75
9　　　p　　　　　　　　　　　　　　21　

Sheet1の
E9：a
E10:2

★よって、この場合の式の意味は、「Sheet2のA1:A100が「a」で、Seet2のF1:F100　のデータに「2」の含まれている、一番上の行にあるデータの抽出する式です（この例では、答えは「72」になります）

>ISNUMBER関数は何の為に使用してりるのでしょか？
◆ISNUMBER(FIND(E10,sheet2!F1:F100))　
★上の式は、「Sheet2のF1:F100のうちE10のデータがあるもの」です
★FIND関数は：FIND(検索文字列,対象)=位置
★位置は数字で返されます、ですから、ISNUMBER関数で「対象が数値の時TRUEを返します」

>「0,10^5」は何を意味しているのでしょうか？
◆SUBSTITUTE({0;0;1;0;1;0;0;0;0;・・・・0;0;0;0},0,10^5)*ROW(sheet2!F1:F100)
★上の式で、1*ROW(sheet2!F1:F100)　の一番小さい数値が抽出できればよいので、0を10^5　の置き換えていますが、この例場合10^5でなくとも、10^3　でもいいです（この意味がご理解いただけますか？）

◆これ以上詳しい説明はできません
★なお、ヘルプなどで、関数の意味を調べられることをお勧めします

参考URL：

http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml

補足

maron--5さん、回答ありがとうございます。
すいません、もう少しお聞きしたいことがありますので
お願いします。
★{0;0;1;0;1;0;0;0;0;・・・・0;0;0;0}　のようになります
→なぜ0と1に分かれるのでしょうか？
★0,10^5
→0を100000に置き換えるという意味で10^4でも問題ないということでしょうか？→10000になる
(1はそのままで置き換えない)
★*ROW(sheet2!F1:F100)
→F列の1～100の行数をそれぞれかけているという意味でしょうか？
1*100000
2*100000
3*1

以上、宜しお願いします。

回答No.1

maron--5

http://oshiete1.goo.ne.jp/qa3824021.html
★すでに回答済みですが

補足

maron--5さん、
前回の回答ありがとうございます。
理解力が足らないので、もう少しお付き合いお願いします。
(お礼をせず、新規で質問を出してすみません。)

(概要)
・UNIXのコマンド調査で、コマンドを過去に打ったことがあるかどうかを検索して、
　　ある→今後も打てる
　　ない→今後も打てない
　といった具合に結果を出力させる関数を作成する。

(シート1)→条件指定・結果表示のシート
・過去に打った実績があるかどうかを(3つの条件から)検索して、結果を出力するシート
　(条件)
　　E9→コマンド
　　E10→場所(サーバ名)
　　E11→目的

　(結果)
　　E14→コマンドを打ったらどうなるか
　　E15→コマンドの意味
　　E16→コマンドを打ったことにより監視装置にエラーメッセージが出力するか
　　E17→コマンドが運用に影響があるか
　　D18→コマンドが実際に打てるかどうか

(シート2)→過去に打ったコマンド名などが入力されているDBみたいなモノ
　A列7～88行目→コマンドの一覧
　C列7～88行目→コマンドが実際に打てるかどうか→約2種類(○or×)
　D列7～88行目→コマンドが運用に影響があるか→約2種類(ありorなし)
　F列7～88行目→場所(サーバ名)
　G列7～88行目→コマンドの意味
　H列7～88行目→何の情報
　I列7～88行目→コマンドを打ったことにより監視装置にエラーメッセージが出力するか→2種類(ありorなし)
　J列7～88行目→コマンドを打ったらどうなるか

(条件関数文)
=$E$9 & "/" & F11& "/" & $E$11
=A7 & "/" & F7 & "/" & G7(sheet2)
=MATCH(F9,sheet2!K7:K88,0)
=INDEX(sheet2!F:F,MIN(INDEX(SUBSTITUTE((sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,sheet2!F1:F100)),0,10^5)*ROW(sheet2!F1:F100),))

(結果関数文)
=IF(ISBLANK($E$11),"",IF(ISERROR($F$10),"登録されてません",INDEX(sheet2!$A$7:$J$90,$F$10,10)))
=IF(ISBLANK($E$11),"",IF(ISERROR($F$10),"登録されてません",INDEX(sheet2!$A$7:$J$90,$F$10,8)))
=IF(ISBLANK($E$11),"",IF(ISERROR($F$10),"登録されてません",INDEX(sheet2!$A$7:$J$90,$F$10,9)))
=IF(ISBLANK($E$11),"",IF(ISERROR($F$10),"登録されてません",INDEX(sheet2!$A$7:$J$90,$F$10,4)))
=IF(ISBLANK($E$11),"",IF(ISERROR($F$10),"登録されてません",INDEX(sheet2!$A$7:$J$90,$F$10,3)))

=INDEX(sheet2!F:F,MIN(INDEX(SUBSTITUTE((sheet2!A1:A100=E9)*ISNUMBER(FIND(E10,sheet2!F1:F100)),0,10^5)*ROW(sheet2!F1:F100),))
→この関数文の意味は下記でしょうか？
　「Sheet2のA1:A100のコマンドとSheet1のE9のコマンドが同じ」でかつ「Sheet2のF1:F100のサーバ名とsheet1のE10のサーバ名の文字（または数字）が含まれれているもので
　　Sheet2のF1:F100　のうち一番上の行のデータを表示する

・SUBSTITUTE関数は何の為に使用しているのでしょうか？(A1～A100のコマンドをE9に代入？)
・ISNUMBER関数は何の為に使用してりるのでしょか？
・「0,10^5」は何を意味しているのでしょうか？

以上、宜しくお願いします。