[[20101020100515]]　『近い時刻のデータの抽出』（マル）　ページの最後に飛ぶ

『近い時刻のデータの抽出』（マル）

 Excel2002

 お世話になっております。ご教授、お願いできますでしょうか？

 Book1のC3から（C列が空白の場合は、B3から）、時刻が入力されており、
 Book2のB13からも時刻が入力されています。

 Book1のC3に入力されている時間と、（C列が空白の場合は、B3から）
 Book2のB13から入力されている時間には、若干の誤差があり、
 ぴったり同じ時間はないのですが、
 Book1のC列に入力されている時間に近い、
 Book2のB列の時間の、C13からD13のデータを、
 Book1のO3～とQ3～に抽出したいのです。

 Book1のC3に9:00と入力されていた場合、(空白の場合、B3の時間)
 Book2のB13からの、9:00に近いデータを抽出したいのですが、
 B列に、8：58：01と9：03：01が入力されていた場合、
 8:58：01の行のC列、D列のデータをBook1にO列Q列に抽出したいのです。

 Book2のB列は、6000行程あり、時間分秒が表示されており、
 Book1のC列は、時間分が表示されていて、秒は表示されていません。

 vlookup関数のtureで近似値を含めて検索するのかと思ったりして、
 試行錯誤したのですが、解決できなかったので、
 恐れ入りますが、何か良い方法がありましたら、
 ご伝授頂けないでしょうか？

 よろしくお願いいたします。

 Book1のC3セルに「9:00」とあってBook2のB列に「9:01:00」と「8:59:00」のデータがあった場合、どちらの値に
 対応する値を持ってくるのでしょうか？
 (独覚)

 独覚さま

 説明不足で恐れ入ります。
 全く同じ場合は、過ぎた方の時間に対応する値を持って来たいです。
「9:01:00」でお願いいたします。
 (マル)

 すみません、聞き忘れがありました(汗

 Book2のB列内で同じ時間があってそれが一番近い場合にはどうしますか？
 (独覚)

 独覚さま

 何度も恐縮ですm(__)m
 Book2のB列内で、時間分秒が全く同じ物は、
 今のところ発生しておらず、今後もない予定です。
 ご配慮、ありがとうございました!（マル)

 Book1、Book2両方開いた状態でBook1のO3セルに

=INDEX([Book2.xls]Sheet1!$C$13:$C$100,MATCH(MIN(INDEX(ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1",0)),INDEX(ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1",0),0))

 Q3セルに

=INDEX([Book2.xls]Sheet1!$D$13:$D$100,MATCH(MIN(INDEX(ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1",0)),INDEX(ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1",0),0))

 と入力して下へコピーしてみてください。
 なお、二つの式は頭の「[Book2.xls]Sheet1!$C$13:$C$100」の部分がC列かD列かの違いだけです。

 追記
 　あっ、Book2の方は6000行ほどでしたね(汗
 　式では100行めまでになっているので「$100」の部分を最大行数に変更してください。
 (独覚)

 独覚さま

 検証が遅くなって、恐れ入ります。
 上記、試してみたところ、ばっちりでした！
 有難うございました。

 恐れ入りますが、よろしければ、数式の意味を教えて頂けないでしょうか？
 ABS関数が絶対値と言う事は調べたのですが、
 どのように、導き出されているのか知りたく、
 ご教授頂けないでしょうか？

 せっかく教えて頂いたのに、誠に申し訳ないのですが、
 今度は、
 Book1のA3から日付データが入る事になりました。
 例えば、Book1のA3に、2010/10/18　C3に9：00
　　　　 Book1のA100に、2010/10/19　C100に9:00と
 日付が違うが、時間は一緒の場合に対応する方法は、あるのでしょうか？
 Book2のA13からも、日付が入っており、今後は、
 Book1,2ともに、日付も時間も増えて行く予定です。
 Book2にも、2010/10/18、2010/10/19が存在し、
 B13から、9：00に近い時刻は、日付に対して、日付分存在するので、
 どのように対応すれば良いのでしょうか？

 質問がややこしく、追加になってしまい、申し訳ありませんが、
 よろしくお願いいたします。(マル)

 とりあえず日付追加の対応について。

 これは今まで時間だけの判定だったのを日付も含めるということでいのでしょうか？
 であれば式中で時間を参照している部分を日付+時間に変更すればいいです。

 例
 [Book2.xls]Sheet1!$B$13:$B$100
 　　　　　　↓
 [Book2.xls]Sheet1!$A$13:$A$1006+[Book2.xls]Sheet1!$B$13:$B$100

 式の説明についてはまた後ほど。
 で、説明の前の予習として下記のリンク先に目を通してみてください。
 「“達人”芳坂和行氏に学ぶ、エクセル(Excel)「配列数式」講座」

http://pc.nikkeibp.co.jp/pc21/special/hr/

 (独覚)

 独覚さま
 ありがとうございます!

 今、手動でしているのですが、
 後ほど、上記を参考に勉強させて頂きます。
 数式も修正して試してみます。
 ちょっと、時間がかかるかもしれませんが、
 後ほど、また結果を報告させて頂きます。

 いつもご丁寧に有難うございます。(マル)

 式の簡単な解説です。

 ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))
 まず、ABS関数で検索先の時間と検索元の時間の差額の絶対値を求めているのはわかると思いますがそのままでは
 1分前も1分後も同じ値になってしまいます。

 そこで
 -([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1"
 の部分で検索先の時間が検索元の時間より大きい場合は0.1秒を差額の絶対値からひいています。

 これで1分前は差額の絶対値が1分でそのまま、1分後は差額の絶対値が1分でそこから0.1秒引かれるため59.9秒となり、
 差が同じでも検索元の時間より大きい方が小さくなります。

 MIN(INDEX(ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1",0))
 ではそのようにして求めた差額の絶対値で一番小さいものを求めます。

 で、同じように
 INDEX(ABS([Book2.xls]Sheet1!$B$13:$B$100-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$B$13:$B$100>IF($C3="",$B3,$C3))*"0:0:0.1",0)
 で、求めた範囲内から(前回紹介したリンク先で説明されていますが、INDEX関数でくくることで上記の計算を
 行った作業列を作るのとおなじことになります）
 MATCH関数を使って差額の最小値の位置を求めて、一番外側のINDEX関数でBOOK2のC列・D列で対応する位置を求めています。

 あくまでもおおざっぱな説明なんで疑問点があればどんどん聞いてください。
 (独覚)

 独覚さま
 ご丁寧に有難うございます！

 教えて頂いたH.Pと上記のご説明は、まだ、確認できていないのですが、
 手入力が終わり、とりあえず、手入力データに間違いがないか確認したかったので、
 先に、日付追加の下記の作業をしてみました。
 教えて頂いた件は、後ほど、確認させて頂きます。

=INDEX([Book2.xls]Sheet1!$C$13:$C$5340,MATCH(MIN(INDEX(ABS([Book2.xls]Sheet1!$A$13:$A$5340+[Book2.xls]Sheet1!$B$13:$B$5340-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$A$13:$A$5340+[Book2.xls]Sheet1!$B$13:$B$5340>IF($C3="",$B3,$C3))*"0:0:0.1",0)),INDEX(ABS([Book2.xls]Sheet1!$A$13:$A$5340+[Book2.xls]Sheet1!$B$13:$B$5340-IF($C3="",$B3,$C3))-([Book2.xls]Sheet1!$A$13:$A$5340+[Book2.xls]Sheet1!$B$13:$B$5340>IF($C3="",$B3,$C3))*"0:0:0.1",0),0))

 上記のように、修正して入力してみたのですが、
抽出されたデータは、別のセルのデータになっているようでした。
下にコピーしてみると、同じ数値が入力されました。
上記の数式のどこに誤りがあるのか、教えていただけないでしょうか？よろしくお願いいたします。(マル)

 時間の部分は全て日付+時間の形にしなければなりません。

 Sheet2の方の時間データは日付データと足されていますがBook1の方が時間データのままです。

 IF($C3="",$B3,$C3)
 を
 ($A3+IF($C3="",$B3,$C3))
 としてみてください。

 (独覚)

 独覚さま
 有難うございました！出来ました。

 恐れ入りますが、続けて問題が出てきて、
 教えて頂きたいのですが、よろしいでしょうか？

 ①8:27に近いデータを取りたいのすが、
   8:24:55と8:29:55の時間がある場合、
   本来8:29:55のデータが欲しいのですが、
　 8:24:55のデータが抽出されます。

 同様に、
   15：00のデータ抽出の時、
   14：57：55と15:02:55があった場合、
   15:02:55のデータが欲しいのですが、
   14：57：55のデータが抽出されます。

   このような現象が起きているのは、一部だけで、
   他は、問題ないのですが、何故でしょうか？

 　入力式は、下記です。

　=INDEX([室内1.xls]Sheet1!$C$13:$C$5340,MATCH(MIN(INDEX(ABS([室内1.xls]Sheet1!$A$13:$A$5340+[室内1.xls]Sheet1!$B$13:$B$5340-($A3+IF($C3="",$B3,$C3)))-([室内1.xls]Sheet1!$A$13:$A$5340+[室内1.xls]Sheet1!$B$13:$B$5340>($A3+IF($C3="",$B3,$C3)))*"0:0:0.1",0)),INDEX(ABS([室内1.xls]Sheet1!$A$13:$A$5340+[室内1.xls]Sheet1!$B$13:$B$5340-($A3+IF($C3="",$B3,$C3)))-([室内1.xls]Sheet1!$A$13:$A$5340+[室内1.xls]Sheet1!$B$13:$B$5340>($A3+IF($C3="",$B3,$C3)))*"0:0:0.1",0),0))

 ②室内2Book(前のBook1)の10/6　9：20のデータを抽出したい時に、
   抽出データBook(前のBook2）に、※Bookの名前が変わりました。※
   近いデータはないので、最も近い時刻の、10/6　15:19:36のデータを取得してきます。
   10/14の11：00のデータを抽出したいが、
   抽出データBook、に10/14のデータがない場合、10/13の最終データを抽出しています。
   このように、データ取得時間の誤差が多い場合、室内２Bookのセルは、
   データを抽出せずに、該当がないと言う事で、ブランクにしておきたいのですが、
   このような事は可能なのでしょうか？誤差は、15分以内なら、許容範囲にしたいです。
　
 度々、申し訳ございませんが、再度、教えて頂けないでしょうか？
 よろしくお願いたいします。(マル)

 1番目について。

 8:27との差は
 8:24:55  0:02:05
 8:29:55  0:02:55

 15:00との差は
 14:57:55  0:02:05
 15:02:55  0:02:55
 と、8:24:55、14:57:55の方が小さいようですけれども。

 2番目は考え中です。
 (独覚)

 2番目です。

 本番の計算を行う前に15分以内の値があるかをチェックしてから計算を行うようにします。

 =IF(SUMPRODUCT((ABS([室内1.xls]Sheet1!$A$13:$A$5340+[室内1.xls]Sheet1!$B$13:$B$5340-($A3+IF($C3="",$B3,$C3)))<="0:15"*1)*1),現在の式,"")

 (独覚)

 独覚さま
 ①は、私の勘違いで、分まで見て、近い方と認識していました。
 教えて頂いた関数のおかげで、手入力での間違いが4つも発見されました。
 データが多いので見直すだけでも時間がかかるので、大変で助かりました。
 あとは、式を理解して、使えるように勉強したいです。
 ありがとうございました！

 ②有難うございます。後ほど確認します。

 あと、この作業内で、別作業があり、さらに質問があるのですが、
 この質問内で、追加させて頂いてもよろしいでしょうか？

 先程同様に、室外BookのC3（ブランク場合はB3）から、時間分が入力されており、
 抽出データBookのSheet1に、A10～時間分秒が入力されており、
 抽出データBookのD10～ E10～データが入っています。
 今度は、近い時間のデータの抽出ではなく、ぴったりの時間があるので、
 室外BookのC3～（もしくはB3)の時間に対応する、
 抽出データBookのA10～の時間に対応するD10～とE10～のデータを、
 室外BookのP3～とR3からに入力したいです。
 抽出データBookは、日付ごとにファイルが分割されているので、
 日付の入力はありません。

 P3=VLOOKUP(C3,[抽出データ.xls]Sheet1!$A$10:$A$1449,4,0)
 検討違いの数式なのだと思いますが、上記だと、#REF!となってしまいます。
 こちらも、今、手入力中なのですが、何か良い方法は、ありますでしょうか？

 よろしくお願いいたします。(マル)

 >この質問内で、追加させて頂いてもよろしいでしょうか？

 式も全然別になりますし、新たな質問にした方が見てくれる人も多くなるので新たに質問なされた方がいいかと思いますが、
 すぐに気付いた点を一つ。

 VLOOKUP関数の場合は範囲に検索列、および目的の列も含めなければなりません。
 A列で検索してD列の値を求めたい場合は
 P3=VLOOKUP(C3,[抽出データ.xls]Sheet1!$A$10:$D$1449,4,0)
 とD列も範囲に含めないとなりません。

 上記の修正をした上で他にも疑問が出てきた場合は新規に質問なさってみてください。
 (独覚)

 独覚さま
 ②も出来ました!
 いつも関心するばかりです。
 ご親切に有難うございます。
 大変勉強になり、助かっています。
 今回もお世話になりましたm(__)m

 追加の質問の件は、失礼しました。
 教えて頂いた式で不明は点は、
 改めて質問させて頂きます。

 ありがとうございました!(マル)

コメント返信：

[ 一覧(最新更新順) ]

[[20101020100515]] 『近い時刻のデータの抽出』（マル） ページの最後に飛ぶ

[[20101020100515]]　『近い時刻のデータの抽出』（マル）　ページの最後に飛ぶ