エクセル実践塾 > 特集「条件式の考え方」

シリアル値の小数部で表される“時間”は、計算過程で誤差を生じやすい

 

無限小数を含む小数の計算では、その過程によっては誤差を生じてしまうので、シリアル値の小数部で行われる時間の計算は、それを加味したうえで行う必要があります

「2012/3/17」などの日付や、「14:43」などの時刻(時間)は、いったんセルに入力すると、シリアル値に変換され、表示形式でその見た目を変えられるようになります。

このシリアル値は、「1900/1/0 0:00」を0とする数値です。

シリアル値の1は1日=24時間に相当するので、24時間未満の時間は1未満の小数で表されます。

小数には無限小数も含まれるため、計算の順序によっては誤差が生じることがあります。そこで、時刻や時間の演算では、支障のない程度で余裕をもたせるのが賢明です。

時刻や時間そのものは文字列なので、条件式で用いる場合には、TIME関数などでシリアル値に変換する必要があります。

 

 

 

 

条件式の中で 時刻や時間の演算を行う場合には、TIME関数などでシリアル値に変換します。

TIME関数では、引数の時分秒を省略することもできます。この例では、分と秒を省略して、20時0分0秒をB列の時刻と比較しています。

ABS関数で計算結果を絶対値にすれば、指定した時刻の前後30分であるか否かを調べることも容易です。

ただし、シリアル値の小数部である時刻や時間の場合には、この例のように誤差が生じることもあります。本来なら、12:00も12:30の30分前後に該当するはずですが、条件式の結果はFALSEとなっています。

このような誤差を回避する簡単な方法は、支障のない程度で時間に余裕を持たせることです。この例のように、30分前後ではなく、31分前後にすれば、意図した結果を得ることができます。B列に入力されている時刻は30分単位なので、1分程度の余裕は結果に支障ありません。
日付はシリアル値の整数部で、時刻は小数部なので、両者を加算(足し算)すると、日付と時刻の両方を表すシリアル値となります。

この例では、そのシリアル値を、表示形式で年月日時分で表示しています。

この例では、囲った日付と時刻が、それぞれ前後1時間で重複しています。

 

この例では、日付と時刻を足したものを、表内のすべての日付+時刻と比較し、それが1時間前後で重複するか否かを調べています。

減算の絶対値と比較する1時間を、TIME(1,10,)と1時間10分にしているのは、小数計算となる時刻演算の誤差を回避するためです。

セル範囲の値との比較を行うので、配列数式を用いています。

【参考】論理値は、加算できる

最後に1を引いているのは、同じ行の日付と時刻も比較対象となるからです。少なくとも1つは条件に合致してしまうので、条件式として用いやすいように1を引き、他と重複しない場合には結果が0になるようにしています。

上の条件式は、SUMPRODUCT関数に置き換えれば、通常の数式 にできます。

条件付き書式では配列数式を条件式として設定できないので、 このSUMPRODUCT関数を使います。

 

 

<< 日数はシリアル値の減算で求める 質問や要望などはこちらへ 【条件付き書式での使用例】
前後1時間で重複するスケジュールをチェックする >>

 

関連する他のページ

曜日でデータをまとめたい
WEEKDAY関数の結果を並べ変えれば、曜日ごとに集計することもできます

スケジュール表の土日を塗りつぶしたい
日付や曜日がシリアル値(日時データ)で入力されていれば、WEEKDAY関数を使った条件付き書式で、土日だけを強調できます

日付の曜日を数値で表したい
WEEKDAY関数を使うと、各曜日を数値で表すことができます

条件付き書式を使って、土曜日と日曜日、祝祭日を色分けする
条件付き書式のルールは、複数設定して適用できます

ピボットテーブルを使って、曜日でデータを分析したい
WEEKDAY関数の結果をピボットテーブルでページフィルタにすれば、曜日でデータを分析できます

WEEKDAY関数を使ってみたい
WEEKDAY関数を使うと、指定した日付の曜日を1〜7の数値で表せます

日付から曜日データを取得したい
WEEKDAY関数を使えば、日付データから、曜日を表す数値データを取得できます

条件付き書式を使って、 日曜日の列だけを塗りつぶす
WEEKDAY関数による条件式を使えば、日曜日のセル範囲だけを塗りつぶせます

月〜金曜日の売上だけを合計したい
WEEKDAY関数による結果を条件とすれば、SUMIF関数で曜日を限定して売上を合計できます

月〜金曜日の売上の平均を求めたい
WEEKDAY関数による結果を条件として、SUMIF関数での集計結果を、COUNTIF関数で調べた個数で割れば、曜日を限定して平均を求めることができます