15分単位の駐車料金や時間外手当を計算する（CEILING関数）

駐車料金や時間外手当を15分単位で計算した場合「誤差」が出ます

　コンピューターはその仕組み上「少数の計算が正しく行われない」ので計算結果に「誤差」が出ることがあります。

　CEILING関数を使って端数処理をすることで、誤差の発生を防ぐことができます。

　　　　　

 ※ CEILING関数は指定した間隔で切り上げする関数で、指定した間隔を「15」とすれば、「15の倍数」で端数処理し切り上げします（※ 切り捨ては「FLOOR関数」）

15分単位で料金が発生する駐車料金で誤差が出ないようにする

　エクセル上では「時刻」をシリアル値で管理しています。

　時刻データは、シリアル値で管理されています。

• 1日（24時間）　 ➡   「 0.1 」
• 正午（12時）　   ➡   「 0.5 」
• 午後6時（18時） ➡   「0.75」

　※ 割り切れない場合は、有効桁数（15桁）までの近似値で表す

　時刻のシリアル値は通常自動的に「時刻データ」として表示されるが、表示形式を　「標準」とすることで、少数の値として確認することができます。

15分単位の駐車料金を計算します

　「利用時間」は「出庫時間」から「入庫時間」を引くことで算出します。

　　　

「利用時間」の表示形式を「標準」にします

　表示形式を「標準」にすると、利用時間の計算結果が割り切れずに小数の近似値になります。　

　　　

 近似値のままで「利用時間」を計算します

料金計算のために、「利用時間」を15分単位で切り上げて「料金計算用」のフィールド（列）を作ります

• CEILING関数（端数処理関数）を使って、15分間隔で切り上げます
• 「利用時間」と「利用料金計算用」を比較すると、15分単位で切り上げられています
• 数式「   =CEILING( [@利用時間] , "0:15" )   」
• 数式に「 "0:15” 」と入力することで「15分」と認識されます
• ※ 「表」をテーブル化してあるので「利用時間」のセルが「[@利用時間]」になっています

　　　

単価（100円）を掛けて「利用料金」を計算します

• 数式「  = [@利用料金計算用]  /  "0:15" ＊ 100  」

　　　

利用時間が「15分の倍数」になると誤差が発生してしまいます

• 利用時間が「1:15」なので、利用料金計算用も「1:15」でなければならないものが「1:30」になってしまっている
• 利用時間が、基準となる15分の倍数になったときに端数が切り上げ処理されるのでこのような誤差が生じる

　　　

誤差を回避するために「利用時間」を分単位に換算する

　「時刻データ（1:15）」のままでは端数が15分単位で切り上げられてしまい誤差が発生するので、「分単位」に換算した上で小数点以下を四捨五入します。

• 「利用時間」を「 "0:01 " 」で割ることで「分単位」に換算して、ROUND関数で小数点以下を四捨五入する
• 数式「  = ROUND ( [@利用時間] / "0:01" , 0 )  」
• 「利用料金」は分単位の整数を「15」で割り、ROUDUP関数で小数点以下を切り上げる。
• 数式「 = ROUNDUP ( [@分単位] / 15 , 0 ) ＊ 100  」

※ 「15」で割ることで端数が発生し小数点以下の誤差が発生しますがここでは影響ありません　

　　　

小数点以下の誤差を発生させないようにする

　「分単位」をCEILING関数を使って15の倍数に換算し、「15で割っても端数が発生しない」ようにします。

• CEILING関数で「分単位」を一度「15」単位で切り上げてから「15」で割ることによって、小数点以下の誤差が発生しないようにする
• 数式「 = CEILING ( [@分単位] ,15 ) / 15 ＊ 100 」　　

　　　

　このように端数処理関数のCEILING関数を使うことによって、15分単位の計算が正確になります。

エクセルで「時間」を扱う際の注意点

• 時刻のデータは「シリアル値」で管理されている
• 2つの時刻の感覚を求めるためには「単純に引き算」をすれば良い
• 数式の中で直接「時刻」を指定するには、時間を表すデータを「 " " 」で囲むことで、文字列で指定するのが簡単な方法
• 時刻データの場合、「割り切れない割り算の近似値」として表示しているので誤差が蓄積されてしまう
• 誤差の蓄積を防ぐためには基準となる単位（今回の場合は「分」に換算）してから計算することで誤差の発生を回避できる
• エクセルでは「時刻」と判断される文字列が含まれている場合は自動的にその形式のデータに内部変換される
• 「 0時15分 」は時刻と判断される
• 「 15分 」は時刻と判断されない

　　　

　今回のように複数のフィールド（項目）を使って数式を作成する場合は、テーブル化することによって数式に「フィールド（項目）名」が表示されるようになるので分かり易い数式を作ることができます。

　その他にも「テーブル化」することによって、「行」や「列」にデータやフィールドを追加しても自動的に「表が広がる」ことにより、数式や書式がコピーされるので作業効率が上がります。

　このように表を扱う場合は、必ず「テーブル化」するようにしてください。

※ テーブル化の方法

　「表」をテーブル化することにより、数式が分かり易くなるなど色々メリットがあります。

1. 「表」のどこか１カ所を選択
2. 「挿入」タブを選択
3. 「テーブル」をクリック
4. 「表」全体が選択されていることを確認
5. 「先頭行をテーブルの見出し･･･」にチェックを入れる
6. 「OK」をクリック

　　　

　「時間」の計算は給与計算等でたびたび発生すると思います。

　コンピューター上では見ることのできない「端数」が発生するために計算結果が意図するものと異なることがあると思います。

　そのような場合はCEILING関数等を使うことにより「誤差」の発生を防ぐことができます。

k-ohmori9616.hatenablog.com