|
|
|
数値を「ある値」を単位として『丸め処理』するには、
[CEILING(切り上げ)] [FLOOR(切り捨て)]関数
を使います。時刻データを丸める場合にも、この関数は利用できます。
例えば、出/退社時刻を１５分単位に丸める場合には、
出社時刻なら『切り上げ』､退社時刻なら『切り捨て』ですから
出社時刻⇒ =CEILING(出社時刻, TIMEVALUE("0:15"))
退社時刻⇒ =FLOOR(退社時刻, TIMEVALUE("0:15"))
という風にします。
でも一寸待って下さい。ここで注意しなければならない問題があります。
手入力された時刻データならば気にしなくても良いのですが、計算結果として得られた
勤務時間などの場合には、そのシリアル値に小数誤差が含まれてきますので、上記の式
が期待通りの結果を返してくれない場合がありえます。
どういう事かというと、[1:30]と表示されているデータにCEILING 関数で１５分丸めを施す
場合、そのままの[1:30]という結果を期待しますが、ここにプラスの小数誤差が加わって
いると
『１５分の倍数である[1:30]を僅かに越えている』
と判断されて、ひとつ上の[1:45]が求まってしまうという事です。
FLOOR関数の場合ならば、マイナスの小数誤差が加わっていると
『１５分の倍数である[1:30]に僅かに届かない』
と判断されて、ひとつ下の[1:15]が求まってしまうという事です。
（注）[1:30]という時刻シリアル値だと必ずこういう結果になってしまうという意味
ではないですから勘違いしないでください。只の例ですから‥‥‥
こういう問題は「丸め」単位の倍数となる時刻で出てきます。
時刻シリアル値は
１時間⇒[1/24] １分⇒ [1/(24*60)] １秒⇒ [1/(24*60*60)]
という分数で表される値(無限小数)を単位として刻まれていますが､実際にはそれが更に
２進数として処理されている為にシリアル値同士の演算（加減算など）を繰り返すと、必ず
【小数誤差】という問題が浮上してきます。
[7:30:00]とキー入力したデータや、TIMEVALUE("7:30:00")というデータは小数表示
にしても等しいです。
しかし､【計算結果として】得られたデータが時刻表示した時に[7:30:00]になるからと
いっても､小数表示にすると非常に小さな誤差がある可能性があります。
この誤差の存在によって､15分/30分単位でCEILING/FLOOR関数を使っても結果が
[7:30:00]にならない場合がありえるのです。
（簡単に確認できる例）
A1に[0:00]､A2に[0:01]を入力してからオートフィルで作成した時刻データに対して
=CEILING(Ax,TIMEVALUE("0:05"))
=FLOOR(Ax,TIMEVALUE("0:05"))
を行なってみると､
=CEILING([0:35のセル],TIMEVALUE("0:05")) ⇒ 0:40
=FLOOR([0:30のセル],TIMEVALUE("0:05")) ⇒ 0:25
という風に､丁度「境界時刻」で結果がズレてしまいます。
※直接手入力した時刻データには､このような懸念はありませんから安心して下さい。
出社/退社時刻は普通[手入力]するデータですから、これにCEILING/FLOOR関数
を適用しても問題は起きません。【求めた勤務時間合計を丸める】という場合に『小数
誤差』の影響が出る可能性があるのです。
ここで注意してもらいたいのは、問題があるのはCEILING/FLOORによって丸めを行なう
場合だけです。普通に勤務時間合計に時給を掛けて給与を算出するというような計算では
小数誤差が目に見えるレベルまで影響することはありませんから（小数以下十数桁のレベル
ですから、億単位の時給を掛ける(笑)という事でもしない限りは‥‥）安心してください。
では、CEILING/FLOORによって勤務時間合計を丸めると、どの位影響が出るのか？
CEILING/FLOORで出る影響とは上で説明したように境界時間の時に出てきます。
例えば「１５分単位」といった場合には、
０分 １５分 ３０分 ４５分
で丸められますが、もし[25:30]というデータが[25:15]や[25:45]に丸められたら影響は
大きいですよね（時給千円なら２５０円の差になります）。
それでは、どうやったら、この問題を解消できるのか‥‥‥
２４時間未満(23:59:59まで)のシリアル値なら、TEXT関数とTIMEVALUE関数を組み合わせて
下記のようにする事で小数誤差を取り除き、その時間を手入力した時と全く同じシリアル値に補正
する事ができます（勤務時間を例にしているので[秒]は無視しています⇒ゼロ固定）。
=TIMEVALUE(TEXT(合計時間, "h:mm"))
どういう理屈かというと、『何時何分』という表示にしてしまえば、「小数誤差を持つシリアル値」も
「小数誤差の無いシリアル値」と同じ時刻に編集されます。その時刻文字列をTIMEVALUE関数
でシリアル値に変換すれば『小数誤差の無い綺麗なシリアル値』が得られるという仕組みです。
２４時間以上の場合には
=TIMEVALUE(TEXT(合計時間, "[h]:mm"))
とすれば良いように思えますが、TIMEVALUE関数の方でエラーになります。
TIMEVALUE関数が変換できるのが[00:00:00〜23:59:59]の範囲だからです。
では、２４時間以上のデータから小数誤差を取り除くにはどうしたら良いでしょうか。
ワークシート関数では下記のような式で対処できます（勤務時間を例にしているので[秒]は
無視しています⇒ゼロ固定）。
=INT(TEXT(合計時間, "[h]") / 24) +
TIME(HOUR(合計時間), MINUTE(合計時間), 0)
ちょっと余計な計算していました。
剰余を採らなくても、HOUR関数が元々２４時未満で返しますね(02/03/20)。
=INT(TEXT(合計時間, "[h]") / 24) +
TIME(MOD(TEXT(合計時間, "[h]"), 24), MINUTE(合計時間), 0)
HOUR関数で除かれる『２４時を越える部分』即ち【日数】に当たるデータ（シリアル値の整数部）は、
[h]書式によるTEXT関数で『通算時』を取り出し、それを[24]で割った『商』として求まります。
残りの『２４時未満の時間データ』は、HOUR関数とMINUTE関数により『時(24時未満)・分』が
出てくるので、それをTIME関数でシリアル値（小数部）に直し、先の整数部分（２４時を越えた日数部分）
に足しこみます。
（注：02/3/20追記）
２４時超の日数部分、即ちシリアル値の整数部分を取り出すのを単に『INT(合計時間) 』としない
のには理由があります。
丁度『２４時』という時間データに僅かなマイナスの小数誤差が含まれていると、時間表示として
は『24:00:00』で表示されますが、数値として見れば『0.999999‥‥』になりますから、ＩＮＴ関数
で整数部を取り出すと『ゼロ』が返ります。そして、HOUR/MINUTE関数が返す時/分も『ゼロ』で
す(時間表示としては24:00:00だから)から、これを足し込むと『２４時』ではなく【０時】になってしまい
ます（プラスの小数誤差なら、このような問題は起きません）。４８時・７２時などでも同様です。
※ワークシートでは､VALUE関数を使うと２４時超でも変換できます。
=VALUE(TEXT(合計時間, "[h]:mm")) (2002/9/4 追記)
ＶＢＡでは、Format関数が[h]書式を使えないので、WorksheetFunctionでTEXT関数を使います。
(CInt(WorksheetFunction.TEXT(合計時間, "[h]")) \ 24) +
TimeSerial(Hour(合計時間), Minute(合計時間), 0)
ちょっと余計な計算していました。
剰余を採らなくても、Hour関数が元々２４時未満で返しますね(02/03/20)。
(CInt(WorksheetFunction.TEXT(合計時間, "[h]")) \ 24) +
TimeSerial(CInt(WorksheetFunction.
TEXT(合計時間, "[h]")) MOD 24), Minute(合計時間), 0)
これでは大変なので『kt関数アドイン』では
ktCTime ：上記の小数誤差を取り除く処理を実現します
ktBoundaryTime ：小数誤差を取り除いた上で分単位の丸めを行ないます
という関数を用意しています（ktCTime関数はVer2.40から利用できます）。
[ Home へ戻る ]
 |
 |
|
|
|
||
| 角田 桂一 Mail:addinbox@h4.dion.ne.jp CopyRight(C) 2001 Allrights Reserved. | ||