[[20210712161606]]　『合致する日付又は直近の日付から抽出する関数』（右近）　ページの最後に飛ぶ

『合致する日付又は直近の日付から抽出する関数』（右近）

ご質問させていただきます。
以下のようなシートがあります。
Sheet2のA1に抽出日を手入力します。
Sheet1のA列にある日付を入力した際は、その行のCセルとDセルの値をSheet2のB3セルとB4セルに表示します。
ただし、Sheet1のA列にない日付をSheet2のA1に入力した際は、その直前の日付の行を表示させます。
例えば、2021/4/30ならば、2021/4/1の??2を表示させたいです。
同じ日付はあり得るものとします。
その場合のSheet2のB3とB4の関数の設定について、ご教示願います。
よろしくお願いいたします。

Sheet1

   A         B      C      D
1  日付      ??     数量   区分
2  2021/4/1  1      5	   AA
3  2021/4/1  2      7      AB
4  2021/5/4  1      9      AH
6  2021/6/7  1      13     AL
7  2021/7/1  1      16     BB
8  2021/7/1  2      18     BN
9  2021/7/10 1      23     CV
10 2021/7/12 1      26     DD

Sheet2
A1=2021/4/30

   A     B
3  数量  7
4  区分  AB

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 指定した日付(及びその直前の日付)の一番下の行を返すということだろうか？
（ねむねむ） 2021/07/12(月) 16:31

ありがとうございます。
最も直前の日付、という表現で分かりますでしょうか？
2021/4/30ならば、最も直前というと、2021/4/1ですが、同じ日付が２件ありますので、A3セルの4/1の値を抽出したいというものです。
2021/7/11ならば、最も直前の日付は、2021/7/10です。
もちろん、A列にある日付を指定する場合もあります。その場合は、そのまま抽出しますが、同じ日付が複数ある場合は、おっしゃるように、最も直前の一番下の行になります。
（右近） 2021/07/12(月) 16:39

 ではSheet1のデータが最大100行目までとして
 B3セル:=INDEX(Sheet1!C2:C100,MATCH(1,INDEX(0/((Sheet1!A2:A100<=A1)*(Sheet1!A2:A100<>"")),0),1))
 B4セル:=INDEX(Sheet1!D2:D100,MATCH(1,INDEX(0/((Sheet1!A2:A100<=A1)*(Sheet1!A2:A100<>"")),0),1))
 ではどうだろうか？
（ねむねむ） 2021/07/12(月) 16:45

 Sheet1のA列が昇順になっているのなら
 ↓ でできませんかね？

 B3 =LOOKUP(A1,Sheet1!A2:C100)
 B4 =LOOKUP(A1,Sheet1!A2:D100)

 以上
（笑） 2021/07/12(月) 17:52

ありがとうございます。ご返事が遅くなり、申し訳ありません。

ねむねむさんの関数を設定してみました。見事に抽出できました。
さらに一つお尋ねです。A列の日付は重複はないとした場合(B列が不要)は、修正が必要になるでしょうか？

笑さんもありがとうございます。
必ずしも昇順とは限りません。前後してしまう場合はあります。
（右近） 2021/07/12(月) 22:21

 もともとB列を見ていないため修正は必要ない。
 で、日付が同じでB列が昇順ではない場合もあるのだろうか？
（ねむねむ） 2021/07/13(火) 09:16

Excel2019なら XLOOKUPが使えるんじゃないですか？

=XLOOKUP(A1,Sheet1!A2:A9,Sheet1!C2:C9,"",-1,-1)
=XLOOKUP(A1,Sheet1!A2:A9,Sheet1!D2:D9,"",-1,-1)
（d-q-t-p） 2021/07/13(火) 09:44

 XLOOKUP関数は365でないと使えない。
（ねむねむ） 2021/07/13(火) 09:58

 ＞必ずしも昇順とは限りません。

 では Sheet1 が ↓ の場合

	A	B	C
1	日付	数量	区分
2	7/1	16	BB
3	7/13	23	CV
4	7/7	26	DD

 Sheet2のA1が「2021/7/14」だったらどうなればいいんですか？

 以上、確認だけ
（笑） 2021/07/13(火) 12:17

XLOOKUP関数は365でないと使えない。勘違いでしたか。失礼しました。

=LOOKUP(1,0/(Sheet1!A2:A100<=A1)/(Sheet1!A2:A100<>""),Sheet1!C2:C100)
=LOOKUP(1,0/(Sheet1!A2:A100<=A1)/(Sheet1!A2:A100<>""),Sheet1!D2:D100)
（d-q-t-p） 2021/07/13(火) 12:30

 一応･･･
 ＞最も直前の日付
 ＞必ずしも昇順とは限りません

 2021/07/13(火) 12:17 の例で言うと「23」と「CV」では？

 ＞B列が不要
 B列が数量、C列が区分だとして

 B3 =INDEX(Sheet1!B2:B100,MATCH(MAXIFS(Sheet1!A2:A100,Sheet1!A2:A100,"<="&A1),Sheet1!A2:A100,0))
 B4 =INDEX(Sheet1!C2:C100,MATCH(MAXIFS(Sheet1!A2:A100,Sheet1!A2:A100,"<="&A1),Sheet1!A2:A100,0))

 エラー処理はしてません

 以上
（笑） 2021/07/13(火) 13:07

すみません。すっかりご返事を忘れておりました。申し訳ありません。
ご教授いただいた関数を設定させていただき、思い通りの結果となりました。
ありがとうございました。
（右近） 2021/07/27(火) 11:43

コメント返信：

[ 一覧(最新更新順) ]

[[20210712161606]] 『合致する日付又は直近の日付から抽出する関数』（右近） ページの最後に飛ぶ

[[20210712161606]]　『合致する日付又は直近の日付から抽出する関数』（右近）　ページの最後に飛ぶ