プロが教える店舗&オフィスのセキュリティ対策術

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

A 回答 (4件)

一覧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),""))))

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

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

お礼日時:2013/01/15 19:57

添付図参照


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),"")
「エクセル関数 時間の一覧で時間範囲を条件」の回答画像3
    • good
    • 1
この回答へのお礼

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

お礼日時:2013/01/15 19:56

 今仮に、一覧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以下に貼り付けて下さい。
 以上です。
「エクセル関数 時間の一覧で時間範囲を条件」の回答画像2
    • good
    • 0
この回答へのお礼

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

お礼日時:2013/01/15 19:55

では添付図の通りに。


A列に8:44や10:34などを縦に並べます。
D1以下に9:00や10:00、11:00、12:00…と縦に並べます。

E1に
=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)),"")
と記入、必ずコントロールキーとシフトキーを押しながらEnterで入力、E2以下にコピー貼り付けます。
E列にも忘れず時刻の表示形式を取り付けます。


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





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

これと

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

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


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

といった具合に思い付きでご相談を作文しているせいか、ご説明がとんだデタラメ状態ですが、まぁその辺は置いておいて出来ることをやってみましょう。
「エクセル関数 時間の一覧で時間範囲を条件」の回答画像1
    • good
    • 0
この回答へのお礼

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

お礼日時:2013/01/15 19:54

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A