2009年1月アーカイブ

［書式］　FLOOR(＜数値＞,＜基準値＞)

［機能］　＜数値＞ を ＜基準値＞ の倍数に切り捨てます。

●●●　時間を10分ごとに切り捨てる方法　●●●

 

今回は、時間の切り捨てについて考えていきましょう。

 

普通の数値の切り捨てなら ROUNDDOWN関数ですが、時間の場合には、そうはいきませ

ん。　たとえば１時間単位で切り捨てたいなら、１時間のシリアル値である 1/24 、すなわち

0.041666667 の倍数に切り捨てる必要があります。

 

さて、Excelには、FLOOR関数という、数値を任意の数値の倍数に切り捨てる関数があり

ます。　たとえば値を 0.25 ごとに切り捨てたければ、

　　=FLOOR(＜数値＞,0.25)

のように利用します。

 

こうすれば、＜数値＞ が

　　「1.3」　だったら、計算結果は　「1.25」

　　「0.7」　だったら、計算結果は　「0.5」

というように、0.25の倍数に切り捨てることができます。

 

この関数をうまく利用すれば、時間の切り捨てもできそうですね。

 

 

それでは例題として、これまで何度か出てきた次のシートを使って、「勤務時間数」を10分単位

で切り捨てる方法を考えていきましょう。 

 

 

10分単位で切り捨てたいので、まず 「10分」 がシリアル値でいくつになるかを求めなければ

なりません。　時分秒からシリアル値を求めるには、TIME関数が便利でしたね。　この関数

を使って10分をシリアル値に変換するには、

　　=TIME(0,10,0)

という計算式が考えられます。

 

これを FLOOR関数に利用して、 

　　=FLOOR(＜切り捨てたい時間＞,TIME(0,10,0))

とすれば、10分ごとの切り捨てができると考えられます。

 

ところで、Excelには、

　　「時間をあらわす文字列は、適宜、内部で時間のシリアル値に自動変換される」

という仕組みが備わっています。　

 

そこで、同じことを 

　　=FLOOR(＜切り捨てたい時間＞,"0:10:0") 

という計算式でおこなうこともできます。 

 

ということで、さっそく次の計算式を セルE6 に入力し、セルE15 までコピーしてみましょう。

　　=FLOOR(D6-C6,"0:10:0") 

 

 

 

●●●　正しく計算されないのは？？？　桁落ち　●●●

すると、次図のような結果になります。 

一見、計算できたように思えますが、よくみると、正しない部分があります。 

 

 

本来なら 「1:00」 という結果になるところが 「0:50」 となっています。 

考え方は正しいはずなのに、正しく計算されないのです。

 

これは、「桁落ち」という、コンピュータ特有の現象によるものです。

 

ご存じのように、コンピュータの内部での計算は、二進数でおこなわれます。　このことは、

整数を扱う場合には特に問題になりません。

 

ところが、小数はどうでしょうか。　小数を二進数に直すと、多くの数でいつまでも続く無限

小数にります。　そして演算が行われる際に、有効数字が減少して誤差が生じることがあ

るのです。　これを 「桁落ち」 と呼んでいます。

 

この時間の切り捨てでの計算誤差は、この桁落ちによるものと思われます。　つまり、

 

「 "15:00" から "14:00" を引いたら、桁落ちによって "1:00" にわずかばかり足りなくなった。

　それをFLOOR関数で10分単位で切り捨てたので、50分という計算結果になってしまった」

 

と考えられるわけです。

 

 

●●●　時間の切り捨てを正しく計算するには　●●●

コンピュータの性質上やむを得ない現象ですが、でもだからといって、こんな計算違いが起こる

まま使うわけにはいきません。　では、どうしたらいいか。

 

時間の計算を自力でおこなう。　つまり、TEXT関数で文字列に変換して、コロンで分割して

60で掛けたり、割ったり、剰余を求めたり、なんていう方法もありますが、ちょっと面倒です。

 

ということで、

 

　　「足りないんなら、1秒足しちゃいましょう！」

 

なんていうおおざっぱなテクニックを考えるわけです。　このケースでは、計算は分までで、

秒は問題にしていません。そこで、たとえ1秒足したとしても、その後に FLOOR関数で

切り捨ててしまうので、問題は出ないと考えられます。

 

そこで今度は、セルE6の計算式を次のように変更して、セルE15までコピーしてみましょう。

 

　　=FLOOR(D6-C6-"0:0:1","0:10:0") 

 

 

すると、今度は正しい計算結果が得られたはずです。

 

このように、小数の計算には意外な落とし穴がひそんでいます。　状況に応じて、きちんと

桁落ちの現象に対応することも、時には必要です。 

 

　　「精密な計算をするわけではないから、桁落ちなんて関係ない」

 

と決めつけるのは早計です。　時間の計算も、Excelの内部では精密な小数の計算なの

ですから。

    

［書式］　TODAY()

［機能］　今日の日付のシリアル値を返します。

●●●　「今日」を使った動的な日付の計算　●●●

 

TODAY関数は、ワークシート上に、常に 「今日」 の日付を表示しておきたい場合などに

便利です。　また、今日の日付を動的に利用した計算などにも用います。

 

 

たとえば、「今年の残り日数」 をいつもワークシート上に表示しておきたい場合を考えて

みましょう。

 

本日 「2009/1/7」 から 「2009/12/31日」 までの日数を計算したいなら、適当なセルに

 

　　="2009/12/31"-"2009/1/7"

 

と入力すれば求めることができます。　でも、日付は毎日変わっていきます。

そこで、"2008/1/7" の代わりに TODAY() を利用して、

 

　　="2009/12/31"-TODAY()

 

とします。　こうすれば、明日になっても明後日になっても、正しい日数が計算されます。

 

 

次のシートでは、「今年の残りの日数を」 もう少しきちんと計算しています。　上のやり方

では、来年になったら計算式を変えなければなりません。　年が変わっても正しい日数を

求めたいなら、もうひと工夫した、こんな計算式が必要です。

 

セルB2に入力されている計算式は、

　　=DATE(YEAR(TODAY()),12,31)-TODAY()

というものです。　少しだけ長いので、分解して意味を考えていきま

しょう。

 

まず、YEAE関数は、シリアル値に対応する「年」を求める関数です。　したがって

　　YEAR(TODAY())

の部分では、今年の西暦を求めています。

 

さらに

　　DATE(YEAR(TODAY()),12,31)

とすることで、今年の12月31日のシリアル値が計算できます。　このような計算式にして

おけば、来年になったら、来年の12月31日のシリアル値が返されます。

 

その年の12月31日のシリアル値から本日の日付のシリアル値を引いた値、すなわち 

　　=DATE(YEAR(TODAY()),12,31)-TODAY()

という計算式で、残りの日数が計算できるわけです。

 

 

なお、TODAY関数の計算結果が更新されるのは、シートを開いた時と再計算が実行され

た時です。　Excelでは通常、シートに入力や修正が加えられると再計算が実行されます。

ですから、シートをただ開きっぱなしにして翌日まで放置するようなケースでなければ、常

に正しい結果が表示されます。

 

TODAY関数はシートを開いた時にも再計算がおこなわれるため、ちょっとした副作用があ

ります。　（Excelの関数の多くは、シートを開いただけでは再計算はおこなわれません）

それは、シートに何も修正を加えずに閉じようとした時にも、

　　「保存しますか？」

と聞いてくることです。　知らないと、ちょっとびっくりするかもしれませんが、これはTODAY

関数の出力値が書き替えられたことによるものです。　見えないところで自動的に修正が

行われたわけです。　特に修正していないなら、そのまま保存せずに閉じてかまいません。

 

 

余談ですが、請求書のようなシートの日付欄にTODAY関数を使用している例題を見かける

ことがあります。　しかしながら、こうした使い方は、実際には、あまり好ましくありません。

別の日にそのシートを開いたら、日付が変わってしまうからです。　あくまでも説明のための

例題と考えるのがよいと思います。

 

もしその日の日付を、セルに簡単に入力したいだけなら、ショートカットキーをお薦めします。

　　CTRL + ;　（CTRLキーを押しながら ; 「セミコロン」を押す）

とすれば本日の日付（のシリアル値）を素早く入力できます。

このショートカットキーでセルに入力されるのはシリアル値ですから、あらかじめセルに表示

形式を設定しておけば、好きな形式で簡単に日付の入力ができます。