Excelで範囲を可変して参照する関数を教えてください

ある条件が非適合の場合、参照範囲を可変して同じ条件で値を抽出する関数を探しています。

うまく説明できないかもしれないので、添付画像に沿って質問させていただきます。


**はじめに**

・列Bと列Cの関連性はありません。
・列Dは、「ある一定の条件（IF)に適合」した場合、「1」と表示させています。


**条件**

①　今、D16がある一定の条件に適合したため、「1」と表示されました。

②　そこで、基準となる値としてB16を参照し、E16に「91.6」と表示されました。

③　次に、C2からC15の間で、E16の「91.6」を超えている値を探します。
　　このとき、探す順番（方向）は、C15から上に向けて探します。

④　まずC15からC12は「0」なので、不適合です。
　　次に、C11が「91.4」ですが、E16の「91.6」を超えていないので、不適合です。
　　C9も同様に不適合となります。

⑤　そしてC6の「91.7」でようやく「91.6」を超える値があるので、
　　この値を抽出してF16に表示します。


**その他**

<1>　C2もE16の「91.6」を超えていますが、このExcelシートを時系列と考えた場合、
　　　今日（1月15日）から遡って、“最も近い日付”でE16の「91.6」を超えた値を参照します。

<2>　そのため、C2も「91.6」を超えていますが、C6（1月5日の行）よりも古い行なので、
　　　C6を優先して参照します。


Excelのバージョンによる互換性から、できればマクロを使わずに関数で作成したいと考えています。

恐れ入りますが、どなたかお分かりの方がおられましたら、
ご教授いただけましたら幸いです。

宜しくお願い申し上げます。

質問者からの補足コメント

• ご回答ありがとうございます。
  
  教えていただいた通りに関数を入力し、Ctrl+Shift+Enterで確定して配列数式にしたのですが、
  F16には「0」と表示されました。
  
  本来は「91.7」と表示されるはずなのですが、
  何度試してもうまくいかず、悩んでいます。
  
  もしよろしければ、補足説明をお願いできましたら大変助かります。

  No.1の回答に寄せられた補足コメントです。 補足日時：2015/02/16 18:31

No.3 ベストアンサー 回答者： tom04 回答日時： 2015/02/16 20:01

No.2です。

前回の数式は「その行」から遡っています。
ご希望は「1行上」から遡って・・・ですよね？

前回の数式は無視してF2セルは↓の数式に変更してください。
=IF(E2="","",IF(COUNTIF(C$1:C1,">"&E2),INDEX(C$1:C1,MAX(IF(C$1:C1>E2,ROW(A$1:A1)))),"該当なし"))

前回同様配列数式です。

どうも失礼しました。m(_ _)m

この回答へのお礼

No.1さんのご回答を試行錯誤して「ROW($C$2:$C15)」の後ろに「-1」を付け加え、
=IF($K16=1,INDEX($J$2:$J15,MAX(($J$2:$J15>$L16)*ROW($J$2:$J15))),"")
を
=IF($K16=1,INDEX($J$2:$J15,MAX(($J$2:$J15>$L16)*ROW($J$2:$J15)-1)),"")
にすることで解決しました。

また、No.2さんのご回答のうち、補足のNo.3の通り入力すると、希望通りの結果が出ました。

「ROW関数」はこのように使うことができるのだと知り、とても勉強になりました。

ありがとうございました。

お礼日時：2015/02/16 20:28

No.2 回答者： tom04 回答日時： 2015/02/16 19:56

こんばんは！

No.1さんと同じく配列数式になりますが・・・
D列に関数が入っていて、その値に基づいてE列にデータが表示されるというコトですので、
↓の画像ではF2セルに
=IF(E2="","",IF(COUNTIF(C$2:C2,">"&E2),INDEX(C$2:C2,MAX(IF(C$2:C2>E2,ROW(A$1:A1)))),"該当なし"))
配列数式なのでCtrl＋Shift＋Enterで確定してください。

この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　F2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定してください。

最後にF2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　余計なお世話かもしれませんが、「参照値」よりも大きな値が以前にない場合は
「該当なし」と表示するようにしています。m(_ _)m

No.1 回答者： cj_mover 回答日時： 2015/02/16 15:51

こんにちは。

F16に
=IF($D16=1,INDEX($C$1:$C15,MAX(($C$2:$C15>$E16)*ROW($C$2:$C15))),"")
この数式を入力して、Ctrl + Shift + Enter で確定して配列（CSE）数式にします。
数式バーの表示が
{=IF($D16=1,INDEX($C$1:$C15,MAX(($C$2:$C15>$E16)*ROW($C$2:$C15))),"")}
に変われば正しく設定出来ています。

あとはこの数式を上下にコピーすればいいです。

この回答へのお礼

ご回答ありがとうございます。

教えていただいた通りに関数を入力し、Ctrl+Shift+Enterで確定して配列数式にしたのですが、
F16には「0」と表示されました。

本来は「91.7」と表示されるはずなのですが、
何度試してもうまくいかず、悩んでいます。

もしよろしければ、補足説明をお願いできましたら大変助かります。

お礼日時：2015/02/16 18:32