エクセル関数 時間の一覧で時間範囲を条件に抽出入力

質問

8:44, 10:34, 12:13...など時間が縦にならぶ一覧Aがあり、もうひとつ、9:50, 10:00, 10:30...など時間が縦に並ぶ一覧B があります。Bの20分以内（20分前～20分後）という条件に合致するAの時間をBの該当する時間の横に入力する、という関数を教えてください。
この例の場合だと、10：34の横に10：30が入力される。
ひとつの関数で無理でしたら、何回かステップを分けてもいいですが、一番簡単なのを教えていただけると助かります。

回答No.4

KURUMITO · ベストアンサー

一覧AのデータがA1セルから下方にあり、一覧BがE1セルから下方にあるとしたらB1セルには次の式を入力して下方にドラッグコピーします。

=IF(A1<MIN(E:E),IF(MIN(E:E)-A1<="0:20"*1,MIN(E:E),""),IF(A1>=MAX(E:E),IF(A1-MAX(E:E)<="0:20"*1,MAX(E:E),""),IF(A1-INDEX(E:E,MATCH(A1,E:E,1))<="0:20"*1,INDEX(E:E,MATCH(A1,E:E,1)),IF(INDEX(E:E,MATCH(A1,E:E,1)+1)-A1<="0:20"*1,INDEX(E:E,MATCH(A1,E:E,1)+1),""))))

配列数式などを使っておりませんのでデータ数が多くなっても計算に負担のかからない方法です。

お礼

見慣れた関数なので、覚えれば使えそうです。ちゃんとできました。ありがとうございました。

回答No.3

noname#204879

添付図参照
B2: =IF(SUMPRODUCT((A2>=D$2:D$5-"0:20")*(A2<=D$2:D$5+"0:20")),INDEX(D$2:D$5,SUMPRODUCT((A2>=D$2:D$5-"0:20")*(A2<=D$2:D$5+"0:20")*ROW(D$2:D$5))-1),"")

お礼

内容は難しくて分かりませんがちゃんとできました。ありがとうございました。

回答No.2

kagakusuki

今仮に、一覧AがA列にあり、一覧BがB列にあるものとします。

　まず、C1セルに次の関数を入力して下さい。

=IF(AND(COUNT($A:$A),ISNUMBER(INDEX($B:$B,ROW()))),IF(MIN(IF(MIN($A:$A)>INDEX($B:$B,ROW()),9E+99,INDEX($B:$B,ROW())-SMALL($A:$A,COUNTIF($A:$A,"<="&INDEX($B:$B,ROW())))),IF(MAX($A:$A)<INDEX($B:$B,ROW()),9E+99,LARGE($A:$A,COUNTIF($A:$A,">="&INDEX($B:$B,ROW())))-INDEX($B:$B,ROW())))>"0:20"+0,"",IF(IF(MIN($A:$A)>INDEX($B:$B,ROW()),9E+99,INDEX($B:$B,ROW())-SMALL($A:$A,COUNTIF($A:$A,"<="&INDEX($B:$B,ROW()))))>IF(MAX($A:$A)<INDEX($B:$B,ROW()),9E+99,LARGE($A:$A,COUNTIF($A:$A,">="&INDEX($B:$B,ROW())))-INDEX($B:$B,ROW())),LARGE($A:$A,COUNTIF($A:$A,">="&INDEX($B:$B,ROW()))),SMALL($A:$A,COUNTIF($A:$A,"<="&INDEX($B:$B,ROW()))))),"")

　そして、C1セルをコピーして、C2以下に貼り付けて下さい。
　以上です。

お礼

すごく長いですけど、ちゃんとできました。ありがとうございました。

回答No.1

keithin

では添付図の通りに。
Ａ列に8:44や10:34などを縦に並べます。
Ｄ１以下に9:00や10:00、11:00、12:00…と縦に並べます。

Ｅ１に
=IF(COUNTIF(A:A,">="&(D1-"0:20"))-COUNTIF(A:A,">"&(D1+"0:20")),INDEX($A$1:$A$10,MATCH(MIN(ABS($A$1:$A$10-D1)),ABS($A$1:$A$10-D1),0)),"")
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、Ｅ２以下にコピー貼り付けます。
Ｅ列にも忘れず時刻の表示形式を取り付けます。

あなたがどこ番地のセルに数字を並べているのか全く不明ですが、「この通り」にやらないと失敗します。
最初はあなたもまっさらの白紙エクセルを用意、練習用に回答通りにサンプルを作成し、教わった通りにやってキチンと出来ることをまず確認してください。
そのあとご自分のエクセルに、正しく応用してください。

＃補足
まず。
＞この例の場合だと、10：34の横に10：30が入力される。

これと

＞条件に合致するAの時間をBの該当する時間の横に入力する

とでは、ヤリタイ事が真逆です。

それから、9:50と10:00など、２０分前後で完全にラップしててふつーに考えてもよろしくありません。
Ａに10:15があってＢに10:00と10:30があったら、いったいどっちにくっつけたいんでしょうか。
逆に１つのゾーンに複数該当があったらどうしたいのかも、また考え無しの様子です。

といった具合に思い付きでご相談を作文しているせいか、ご説明がとんだデタラメ状態ですが、まぁその辺は置いておいて出来ることをやってみましょう。

お礼

#N/Aとなってしまってできませんでした。