[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『1番近い時間を検索する方法』（ぴぴぴ）
初めて質問させて頂きます。
例えば、セルに
１９：５０
２０：１０
２０：２０
２１：４０
と一貫性のない時間がならんでいるとします。
例えば、２０：００と入力した場合に、１番近い最近の時間
（例で言うと１９：５０）と表示されるような関数はあるのでしょうか。
うまく説明できないのでわかりずらいと思いますが、わかる方が
いらっしゃったら教えて下さい。宜しくお願いします。
こういうこと？
A B
1 19:50 19:50 =TEXT(VLOOKUP(TIMEVALUE("20:00"),A1:A4,1,TRUE),"h:mm")
2 20:10
3 20:20
4 21:40
>２０：００と入力した場合に、１番近い最近の時間
◆「最近の時間」というのが、わかりませんが！？
◆例を満足させるなら！ A B C 1 19:50 20:00 19:50 2 20:10 3 20:20 4 20:40
◆B1に、20：00と手入力して！ C1=LOOKUP(B1,$A$1:$A$4)
◆いかがでしょうか！ （Maron)
>一貫性のない時間がならんでいるとします。 を、順番に並んでいないと考えました。長くなるので、作業列を使っています。 A B C D 1 19:45 0.010416667 20:00 20:10 2 18:30 0.0625 3 20:10 0.006944444 4 19:50 0.006944444 B1セルに =IF(COUNT($A1,$C$1)=2,TEXT(IF($A1-$C$1>=0,$A1-$C$1,$C$1-$A1),"h:mm")-0,"") とし、下方コピー。 D1セルに =IF(COUNT($A$1,$C$1)=2,INDEX($A$1:$A$4,MATCH(SMALL($B$1:$B$4,1),$B$1:$B$4,FALSE)),"") いかがでしょうか？ (sin) やっぱり時間計算は嫌いかも...
>一貫性のない時間がならんでいるとします
◆前回答では、不都合ですので、訂正しました！
A B C 1 19:45 21:00 20:55 2 20:55 3 20:10 4 21:05
C1=INDEX($A$1:$A$4,MATCH(MIN(ABS($A$1:$A$4-$B$1)),ABS($A$1:$A$4-$B$1),0))
★この式は「配列数式」です。式を入力後、Ctrl＋Shift を押しながら、Enter をおしてください！ ★式が確定すると、式の両端に｛ ｝がつきます
◆いかがでしょうか！ （Maron)
過去にも時間計算に関して、種々の話題が出ました。 小数点以下の誤差が問題という共通認識（？）ですが、 『負の日付・時間は######で表示されます。』とサポートコメントがありますが、 実際はどんな値になるのでしょう？？？ という事で、Maronさんの方法でA1:A4を下のデータで 20:00に近いのだと 20:15 になりませんか？ 19:45 18:30 20:15 20:30 私の方法でもダメな場合があるかもしれませんが。。。 (sin) やっぱり、時間計算は嫌い。
◆差が同じ場合、上の行を表示させるには！
=INDEX($A$1:$A$4,MATCH(MIN(ROUND(ABS($A$1:$A$4-$B$1),15)),ROUND(ABS($A$1:$A$4-$B$1),15),0))
◆これで、いかがでしょうか！ご確認ください！ （Maron)
>私の方法でもダメな場合があるかもしれませんが。。。 と書いている通り、全てのパターンに関して検証する時間も意志もありません。 たまたま、今回のケースは試験入力する際に見つけていた組み合わせを申し上げただけです。
ただの+-だから大丈夫だろうと考えた式でＮＧが出た為に、 TEXT関数で文字列にして計算させ、出来るだけ誤差が出ない様にしていますが、それでも万全だとは思っていません。 演算上での有効桁数はあるでしょうし、それ以上になる時間計算もあるかと思います。 時間計算には、どこかで妥協が必要なのだと個人的には思っています。 ですから、妥協点はそれぞれの環境で違ってよろしいのではないでしょうか？
小数点以下15位って想像したこともなかったので、逆に、質問させて下さい。 論理的な知識を持ち合わせない私の為に、15にした根拠を教えてくださいませんか？ (sin) 追記です。 ちょっと遊んでみました。 A1:A1439に、0:01〜23:59 を１分単位で並べて、その文字数を数えた[=LEN(A1)]ところ、 なな何と、0:01が唯一【20】でした。 という事で、小数点以下は18桁ですから、おまけして、【19】の方がいいと思います。 ※これ自体の信憑性も？ですが、Excel上の現実として受け止める事にします。 って事は、秒単位だと？？？ さすがにやめときます。。。
ヘルプの「エクセルの仕様」に次のようにあります。 有効桁数 15 桁 セルに入力できる最大値 9.99999999999999E+307 処理できる正の最大値 1.79769313486231E+308 処理できる負の最小値 -2.2251E-308 処理できる正の最小値 2.229E-308 処理できる負の最大値 -1E-307 「有効桁数」が１５桁であって、「有効な小数点以下の桁数」が１５桁ではない ですよね。また、小数点以下１９桁でROUNDしてもそこには0しかない可能性が高 いので、ROUNDでの誤差処理は厳密解を得られない可能性は大。 分単位での比較として、Maronさんの式でROUNDではなく、TEXT関数で丸めては。 =INDEX($A$1:$A$4,MATCH(MIN(--TEXT(ABS($A$1:$A$4-B1),"h:m")),--TEXT(ABS($A$1:$A$4-B1),"h:m"),0)) としてCtrl+Shift+Enterの配列数式。 (みやほりん)(-_∂)b
◆みやほりんさん、ありがとうございます！ ◆いつも的確な回答を見させていただき、参考にしております！ ◆今後とも、ご指導よろしくお願いします！ （Maron)
>ヘルプの「エクセルの仕様」に次のようにあります。 ヘルプのどこかで見たおぼえがありましたが、ここだったんですね。 みやほりんさん、ありがとうございます。今後『仕様』で呼び出します。
--以下、どうでもいいような独り言--
今日一日仕事もしないで（1時間ぐらいはしましたけど・・・）
色々と試してみました、
時間算出では、シリアル値に誤差は出るものの時間自体の表示への影響は少ない模様です。
ただ、二次利用においては、誤差が誤差を生むので、矯正する必要があります。
そもそも、1日=24時間 >>> =1/24 >>> 割り切れませーん。
ちなみに、0:00〜1:00までの隣り合う同士の1分間の差が、0:01と同じなのは0:01-0:00の一つだけでした。
次に有効桁数15桁に関しては、 有効＝頭とお尻の【0】もしくは【連続する0】を除いてという事らしいですね。 これは、小数点以下も含めた全体の数値のようなので、 1234567890.12345678 → 1234567890.12345 0.001234567890123456 → 0.00123456789012345 の有効数字になりました。 また、 6543210987654321 ←桁数→ 1234567890123456 999999999999999 + 1 は、 1000000000000000 と正しく表示できますが、 1000000000000000 + 1 は、 1000000000000000 と正しく表示出来ません。 1000000000000001で、16桁になるためでしょう。 ただ計算はされています。 1000000000000000+1-1000000000000000=1 となりますから。 有効桁数→表示有効桁数 が正しい表記かも？
同時に確認したもう一つの厄介ものに、書式が有ります。 標準で多桁の数字を入力すると指数に勝手に変ってしまいます。 123456789012 が 1.23457E+11 の様に。 防止策として、数値にしても見た目(セル表示）は、有効数字15桁の数値ですが、 数式バー内では、指数です。 この指数の後ろの[+11]が[+20]になるとMID関数を使っての計算などに影響が出ます。 それまでは、指数になっていても有効桁数15桁で処理してそれ以降を[0]として扱いますが、 [.]、[E]、[+]を入力値のように認識し、エラー値#VALUE! が発生します。 個人的には、有効桁数を15桁も使いませんが、結構邪魔な仕様だと思います。 今回は、色々と勉強できました。ありがとうございます。 知識になったかは別物ですが... ※試行結果は、個人的な勝手なものですので、ご参考の参考程度に。。。
ところで、ぴぴぴさんは、解決されました？ (sin)ちょっと、修正および追加。 2006/1/12 19:05頃
今日もちょっといじってましたが、一筋縄ではいかない。 よって、指数に関する上記の書き込みは、無視して下さい。 ちょっと、真面目に調べる必要がありそうです。。。 (sin) 参考程度にもならないぐらい面倒っちい〜
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.