[[20160627111717]]　『検索　比較　関数』（ポンヌフ）　ページの最後に飛ぶ

『検索　比較　関数』（ポンヌフ）

教えてください。

検索対象範囲(行)を右側から検索し、
０より大きな数字が入っていたら、対応年度を返す。
ということに使える関数を教えてください。

例

 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

早速にご回答くださいまして、本当にありがとうございました！
お蔭様で、↓の関数を利用して実現することができましたm(__)m素晴らしい！！

 =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

ごめんなさい。
ご教授通りの関数のフィルコピーで対象年度は固定されてました。
※私が$マークを入れ忘れた行をコピーしたものがあった為。
大変失礼しましたm(__)m
また機会がありましたら、是非よろしくお願いします。
この度は、早いご回答に感謝です、ありがとうございました！
（ポンヌフ） 2016/06/27(月) 13:06

＞ねむねむ様
横から失礼します。
LOOKUP関数は普段使わないので、初歩的な質問になってしまうのですが、
ご提示された「=LOOKUP(1,0/(A2:D2>0),A$1:D$1)」という関数についていくつか疑問があります。
よろしければお手数ですがご教示願えませんでしょうか。

疑問点
?@検索値が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

コメント返信：

[ 一覧(最新更新順) ]

[[20160627111717]] 『検索 比較 関数』（ポンヌフ） ページの最後に飛ぶ

[[20160627111717]]　『検索　比較　関数』（ポンヌフ）　ページの最後に飛ぶ