[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『検索 比較 関数』(ポンヌフ)
教えてください。
検索対象範囲(行)を右側から検索し、
0より大きな数字が入っていたら、対応年度を返す。
ということに使える関数を教えてください。
例
2013 2014 2015 2016 :対象年度 0 1 2 1 ←この場合は、2016 5 0 3 0 ←この場合は、2015 1 0 0 0 ←この場合は、2013
宜しくお願いします。
< 使用 Excel:Excel2013、使用 OS:Windows8 >
A B C D E 1 2013 2014 2015 2016 対象年度 2 0 1 2 1 2016 3 5 0 3 0 2015 4 1 0 0 0 2013 というレイアウトだとする。
E2セルに =LOOKUP(1,0/(A2:D2>0),A$1:D$1) と入力して下へフィルコピーではどうか。
追記 マイナスが入力されないのであれば =LOOKUP(1,0/A2:D2,A$1:D$1) でも。 (ねむねむ) 2016/06/27(月) 11:30
=LOOKUP(1,0/(A2:D2>0),A$1:D$1) もし、解決策をご存じでしたら、もう一つだけ教えていただきたいのですが、、、 フィルコピーだと、対象年度の行が固定されないので、 検索される行(ex.A2:D2)はコピー毎に変更されても、 対象年度の行(ex.A$1:D$1)は、固定となるようなコピー法をご存じでしたら 教えてください。 お手数で申し訳ありません、宜しくお願いします。 (ポンヌフ) 2016/06/27(月) 12:04
実際に試してみたか? 以下のページをみて「$」の意味を確認してみてくれ。
http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040720.html
(ねむねむ) 2016/06/27(月) 12:59
疑問点
?@検索値が1にも関わらず、なぜ3など1以外の数値も該当データと判断されるのか(false/trueで何か入力があればtrue=1ということですか?)
?Aなぜ一番右がピックアップされるのでしょうか(LOOKUPの性質ですか?)
無知ですみません。
ご回答いただけますと幸いです。
よろしくお願いいたします。
(教えてください) 2016/06/27(月) 14:29
まずLOOKUP関数や、MATCH関数で検索の型に「1」を指定した場合など検索範囲を昇順に並べることとなっている検索関数は 検索範囲内のどの値よりも大きい値を検索すると範囲内の一番最後の値を検索する。 (その場合は検索範囲が昇順に並んでいなくても構わない)
これは教えてくださいさんが書かれているように「LOOKUPの性質」といえる。
ただしマイクロソフトから公式に説明されている機能ではなくある意味経験則になる。
また、 0/(A2:D2>0) だが、「A2>0」のような比較式はTRUE・FALSEという論理値を返すがこの論理値を演算子(/*-+など)を使った計算式で 扱う場合にはTRUEを1、FALSEを0として計算する。
なので 0/(A2:D2>0) はA2セルからD2セルの間で0より大きい場所はTRUE(1)、0の場所はFALSE(0)として扱い TRUE(1)の場所は0(0/1)、FALSE(0)の場所は#DIV/0!(0/0)の配列となる。
LOOKUP関数はエラー値の場所は検索対象外とするので最初の説明により一番左端の0の位置を検索することになる。
上記の説明でまだ疑問があれば再度質問してくれ。
追記
大事なところで間違いを。
>一番左端の0の位置を検索することになる。 ↓ >一番右端の0の位置を検索することになる。
(ねむねむ) 2016/06/27(月) 14:51
ご丁寧にご回答いただきありがとうございます。
頂いたご説明を頭に入れて数式の検証を何度もしてみてやっと理解してきました。
>0/(A2:D2>0) >はA2セルからD2セルの間で0より大きい場所はTRUE(1)、0の場所はFALSE(0)として扱い >TRUE(1)の場所は0(0/1)、FALSE(0)の場所は#DIV/0!(0/0)の配列となる。
繰り返しになってしまうかもしれませんが、つまり、
「0以上の数字が入っていれば「0」を返し、入っていなければ「#DIV/0!」となる(=検索対象とならない)」
上記によって、
A B C D E 1 2013 2014 2015 2016 対象年度 2 0 1 2 1 2016 3 5 0 3 0 2015
の3行目は、左から「0/検索対象外/0/検索対象外」という結果が返され、
検索範囲内の最大値「0」以上である1を検索値にすることで一番最後の値に該当する2015がピックされるということですね。
極端に言えば検索値は2でも10でも100でもよい、ということと理解しました。
お忙しい中、分かりやすくご説明いただきありがとうございました。
(おそらくですが)正しく理解できたと思います。
(教えてください) 2016/06/27(月) 16:06
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.