質問: excelのif関数について 9セルa1~j1に1~15の数のうちの10個が一つずつ入っており、セルa3~j3にも1~15の数が入っています。またa2~j2には、a~jまでの英字が入っています 1 2 15 9 7 6 5 4 11 3 a b c d e f g h i j 2 3 5 7 9 10 11 12 13 15 のとき b j g e d i c のようにa3列の値と同じ1列の数字の下の値を a5 列に表示したいのですが、何度やってもできません。 良い知恵をお持ちの方、よろしくおねがいします。
回答: 少し解説を =if(sumproduct(($a$1:$j$1=a$3)+0)<1,"",offset($a$2,0,match(true(),index($a$1:$j$1=a$3,,),0)-1,1,1)) ですが、 まずは sumproduct(($a$1:$j$1=a$3)+0)<1 からいきます。 ($a$1:$j$1=a$3) は a1:j1の範囲にa3と同じ物があるか1つ1つ確認させています 回答は同じな場合true、違う場合falseです お示し頂いた例になぞらえると a1はa3と同じではないのでfalse b1はa3と同じなのでtrue c1はa3と同じではないのでfalse d1はa3と同じではないのでfalse ......... ...... ... ... ... j1はa3と同じではないのでfalse となります。 実際の値としては {false,true,false,false,false,false,false,flse,false,false} です。 ところで、 excelでは falseは0に、trueは1に置換される作用があります。 また 0+0は0,1+0は1,0*1は0,1*1は1ですから 従って {false,true,false}+0 は、 {0,1,0} に、置換されます。 ($a$1:$j$1=a$3)+0 は、 {0,1,0,0,0,0,0,0,0,0} と、成るわけです。 本来の使い方ではないですが、 =sumproduct({0,1,0,0,0,0,0,0,0,0}) と、した場合 sumproductは此等の総和を計算してくれます。 回答はこの場合「1」ですね。 総じていえば 「true」が1つ以上あれば sumproductは1以上を返します。 sumproduct(($a$1:$j$1=a$3)+0)<1 とすれば、一致しているものが無いか調べられるわけです。 さて、後半部分の offset($a$2,0,match(true(),index($a$1:$j$1=a$3,,),0)-1,1,1) ですが、 offsetについてはヘルプやネットをご参照ください 単純な物です。 match(true(),index($a$1:$j$1=a$3,,),0) ですが、 キモは $a$1:$j$1=a$3 です。 先程の説明通り、 {false,true,fase,...} excelがよこす答えはこうですね。 所で excelでは「false」より「true」の方が大きい と、いうことになっています。 「true」をmatchに探させているとき matchはtrueが出た時点でそれ以降の検索を止めます。 従って matchにとって =match(true,{false,true,false,false,false,false},0) =match(true,{false,true},0) この双方は 「true」の時点以降を見ないので 同じ扱いとなります。 つまり どちらも昇順に並んでいることになるわけです。 因みにexcelのよこす回答は「2」ですね。 如何でしょうか? お役に立てていたならば幸いです。