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

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

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


**はじめに**

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


**条件**

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

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

③　次に、C3からC17の間で、E3の「91.6」以上の値を探します。
　　このとき、探す順番（方向）は、C3から下に向けて探します。

④　まずC3からC9は「91.6」未満なので、不適合です。

⑤　そしてC10の「91.8」でようやく「91.6」以上の値があるので、
　　この値を抽出してH3に表示します。

⑥　G3には、H3の結果に該当する行の時系列を表示します

**その他**

<1>　C15もE3の「91.6」以上ですが、このExcelシートを時系列と考えた場合、
　　　1月1日から下って、“最も近い日付”でE3の「91.6」以上の値を参照します。

<2>　そのため、C15も「91.6」以上ですが、C10（1月8日の行）よりも下の行なので、
　　　C10を優先して参照します。

<3>　この質問は、以前質問させていただいた下記に酷似しています。
　　　https://oshiete.goo.ne.jp/qa/8923684.html
　　　ただし、今回は探す順番（方向）は、C3から下に向けて探します。
　　　前回ご回答いただいた、

　　　=IF(E2="","",IF(COUNTIF(C$1:C1,">"&E2),INDEX(C$1:C1,MAX(IF(C$1:C1>E2,ROW(A$1:A1)))),"該当なし"))

　　　をベースにできたらと思います。

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

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

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

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

• リクエストの年代と性別を指定してしまっていますが、
  これは、私の入力ミスです。
  年代や性別の指定はありません。

    補足日時：2015/02/27 12:05

• ご回答、ありがとうございます。
  
  >ただし、下方向に検索ではなく、全部比較して、一番小さな日付を出しています。
  
  質問の「**その他**」→「<1>」と「<2>」にありますように、
  古い日付から新しい日付に向けて、下へ順番に検索し、
  最も古い日付（行）を選択しなければならないのですが…
  
  なので、この場合、
  G3が「1月8日」、H3が「91.8」という結果にならなければなりません。
  
  もしお分かりでしたら、補足していただければ助かります。

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

• 前回同様、ご回答ありがとうございます。
  
  前回の質問も的確なご回答をいただき、とても助かりました。
  
  さて、今回の質問に対するご回答ですが、
  お教えいただいた内容によりますと、
  C4:C$1000を検索範囲に指定されておりますが、
  C3:C$1000での検索範囲を指定できますでしょうか。
  
  単純に構文の「C4:C$1000をC3:C$1000」に、
  「A4:A$1000をA3:A$1000」変更してみましたところ、
  本来の検索結果になるはずの1行上の値（セル）が選択されてしまったので、
  そういった単純な変更ではないのかもと思いまして、
  補足質問させていただくことにしました。
  
  どうか、よろしくお願いいたします。
  
  また、ご迷惑でなければ、
  https://oshiete.goo.ne.jp/qa/8932879.html
  も一読いただけましたら幸いです。
  
  甘えて申し訳ありません。

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

• ご回答、ありがとうございます。
  
  参照するリストでC3:C1000の範囲から検索しますが、
  C3も検索対象にしたいのです。
  
  つまり、お教えいただいた構文では、
  例えばE3に「91.4」と入力した場合、
  G3が「1月3日」、H3が「91.5」と表示されますが、
  G3を「1月1日」、H3を「91.4」と表示されるようにしたいのです。
  
  別の質問に対するご回答も、ありがとうございました。
  その質問も、C3を検索対象に含めます。
  そちらでは、
  例えばE3に「91.6」と入力した場合、
  G3を「1月1日」、H3を「91.6」と表示されるようにしたいのです。
  
  質問に不備があり、お手数を掛け、申し訳ありません。
  
  何度も恐縮ですが、ご教授のほど宜しくお願いします。

  No.4の回答に寄せられた補足コメントです。 補足日時：2015/02/28 09:29

No.6 ベストアンサー 回答者： tom04 回答日時： 2015/02/28 14:16

続けてお邪魔します。

＞C3を検索対象に含めます。
というコトですので・・・
結論としてA列日付が昇順であればNo.5さんの回答で解決！だと思います。

余計なお世話かもしれませんが、
G3セルに「～以上」の場合は
=IF(E3="","",IF(COUNTIF(C3:C$1000,">="&E3),MIN(IF(C3:C$1000>=E3,A3:A$1000)),"該当なし"))

「～以下」の場合は
=IF(E3="","",IF(COUNTIF(C3:C$1000,"<="&E3),MIN(IF((C3:C$1000<=E3)*(C3:C$1000<>0),A3:A$1000)),"該当なし"))

としてみてください。（どちらも配列数式です）

※　H列の数式はNo.5さんが仰っているように
INDEX関数とMATCH関数で対応できると思います。m(_ _)m

この回答へのお礼

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

「以上」が「以下」に変わったり、
検索方向を「上に」から「下に」と変わるだけで、
関数の中のどの部分に手を加えればよいか分からず、
試行錯誤を繰り返しましたが、
ようやくできるようになりました。

前回の質問もそうですが、
何度も詳しく教えていただいたことに、
心から感謝します。

もっと関数を勉強しなければと、再認識しました。

お教えいただいた幾つかの構文から、
私の作業内容に最も近い、
No.2・3・6さんのご回答を利用させていただくことにしました。

今後とも、ご教授をお願い申し上げます。

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

お礼日時：2015/03/01 10:57

No.5 回答者： CoalTar 回答日時： 2015/02/28 11:05

No.1です

>ただし、下方向に検索ではなく、全部比較して、一番小さな日付を出しています。

>質問の「**その他**」→「<1>」と「<2>」にありますように、
>古い日付から新しい日付に向けて、下へ順番に検索し、
> 最も古い日付（行）を選択しなければならないのですが…

=IF(COUNTIF(C3:C$100,">="&E3)=0,"該当なし",MIN(IF(C3:C$100>=E3,A3:A$100)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
下へ行くほど新しい日付=下方向へ検索 と判断し、一番小さい(古い)日付を返しています。
よって、日付の順番が逆の場合は、下方向へ検索ではなくなります。
また、別の方の回答も同じですが、配列数式なので対象となる範囲、全部の真偽を判断しています。


>なので、この場合、
> G3が「1月8日」、H3が「91.8」という結果にならなければなりません。
まちがって、B列を返してました。
=IF(G3="該当なし","",INDEX(C3:C$100,MATCH(G3,A3:A$100,0)))
でも、この程度は理解してほしいと思う。

この回答へのお礼

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

>下へ行くほど新しい日付=下方向へ検索 と判断し、・・・全部の真偽を判断しています。

お教えいただいた構文がそういう意味なのだと、
ようやく分かりました。

しっかり内容を精査できていなくて、申し訳ありませんでした。

>この程度は理解してほしいと思う。

おっしゃる通りです。

仕事で使う程度の関数は、なんとかこなしていたつもりでしたが、
まだまだ勉強が足りないと、未熟さを痛感しました。

お教えいただいた構文は、今後の作業に大変役に立ちます。

お忙しいなか、質問にご回答いただき本当にありがとうございました。

お礼日時：2015/03/01 10:48

No.4 回答者： tom04 回答日時： 2015/02/28 00:54

No.2・3です。

E列（参照値）はその行のB列の値ではない訳ですね？
どうも失礼しました。

https://oshiete.goo.ne.jp/qa/8932879.html
の方に投稿した方が良いのかもしれませんが、こちらに補足がついていたので
あちらの回答をこちらに載せておきます。

こちらではE列以上の値を求めたいというコトですが、あちらではE列以下になっていますね。
とりあえずE列以下で最初に出現する場合はG3セルに
=IF(D3=1,IF(COUNTIF(C3:C$1000,"<="&E3),INDEX(A$3:A$1000,MIN(IF((A$3:A$1000>A3)*(C$3:C$1000<=E3)*(C$3:C$1000>0),ROW(A$3:A$1000)-2))),"該当なし"),"")
今回も配列数式です。
※　○以下の場合は「0」も含まれてしまいますので、「0」を除外してやる必要があります。

H3セルの数式は前回そのままで大丈夫だと思います。

今度はどうでしょうか？m(_ _)m

No.3 回答者： tom04 回答日時： 2015/02/27 19:55

No.2です。

たびたびごめんなさい。
前回の数式でも大丈夫だと思いますが、

＞・・・をベースにできたらと思います。
というコトですので、少し似たような感じにしてみました。

G3セル（配列数式です）を
=IF(D3=1,IF(COUNTIF(OFFSET(C3,,,1000),">="&E3),INDEX(A$3:A$1000,MIN(IF((A$3:A$1000>A3)*(C$3:C$1000>=E3),ROW(A$3:A$1000)-2))),"該当なし"),"")

としてみてください。
H3セルはそのままでOKです。

※　似たような感じといってもちょっと違いますかね・・・
どうも失礼しました。m(_ _)m

No.2 回答者： tom04 回答日時： 2015/02/27 19:43

こんばんは！

画像の配置だとG3セル（セルの表示形式は「日付」）に
=IF(D3=1,IF(COUNTIF(C4:C$1000,">="&E3),INDEX(A4:A$1000,MIN(IF(C4:C$1000>=E3,ROW(C4:C$1000)-ROW(C3)))),"該当なし"),"")
今回も配列数式ですので、Ctrl＋Shift＋Enterで確定！

H3セル（配列数式ではありません）に
=IFERROR(INDEX(C:C,MATCH(G3,A:A,0)),"")

という数式を入れフィルハンドルで下へコピー！

こんな感じではどうでしょうか？m(_ _)m

No.1 回答者： CoalTar 回答日時： 2015/02/27 15:18

G3セルに

=IF(COUNTIF(C3:C17,">="&E3)=0,"該当なし",MIN(IF(C3:C17>=E3,A3:A17)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
H3セル
=IF(G3="該当なし","",INDEX(B3:B17,MATCH(G3,A3:A17,0)))
ただし、下方向に検索ではなく、全部比較して、一番小さな日付を出しています。

ちなみに I3セル
=IF(COUNTIF(C3:C17,">="&E3)=0,"該当なし",MAX(IF(C3:C17>=E3,A3:A17)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
J3セル
=IF(I3="該当なし","",INDEX(B3:B17,MATCH(I3,A3:A17,0)))