給与計算などで、出勤簿をExcelで作成して、時間計算をしている方が多いと思います。時間を四則演算する場合は注意が必要です。例えば、毎日8時間で22日間の出勤簿の時間を加算すると、176時間のはずが、8時間と表示されたり、時間を引き算すると、゛######”と表示されたりします。
時間計算の落とし穴
以下の出勤簿のトータルを見て下さい。26日から31日までの出勤簿を入力しました。
「１日の勤務時間は、9時間×4日と4時間×1日なので、40時間のはずですが、16時間となっています。これは、1日の勤務時間の表示形式が時刻表示となっている為です。
時刻表示
excelは、時間をシリアル値で管理しています。
excelが扱うのは「時間」ではなく「時刻」です。「1:00」というのは「1時間」という意味ではなく「1900年1月0日 1:00」という日時を表します。つまり、1日の勤務時間の計は、朝9:00から9時間経過すれば、18:00となり、さらに9時間経過すれば、03:00となり、次の9時間で12:00、そして残り4時間で、最終的に、16:00となっているのです。(時刻表示となっているのです。)
時間計算
時刻表示ではなく、時間計算をするためには、ユーザー定義により表示形式を[h]:mmに変更します。
[h]:mmと設定すると、時間が表示されます。
引き算の落とし穴
時間を引き算すると、「######」となることがあります。
休憩時間が勤務時間を上回ることは、現実にはありませんが、例えば、何等かの作業時間の比較（前回と今回など）を行う場合は、前回の方が早かったという事はありますので、同じ様に、時間の少ない方から多い方を引き算することはあります。
では、何故？上記の場合、-1:00とならないのでしょうか？
excelでは、日付や時間を「シリアル値」という連続した特別な値で管理していますが、シリアル値ではマイナス表示ができない為、「######」と表示されるのです。前述の如く、Excelが扱うのは「時間」ではなく「時刻」であり、「1:00」というのは「1時間」という意味ではなく「1900年1月0日 1:00」という日時を表します。つまり、「-1:00」はありえないのです。
では、どのように対応するか?対応には2つの方法があります。「オプション設定」による方法と、「関数を使う」方法です。
オプション設定で対応
「1904年から計算する」のチェックボックスをONとする。
ファイル→オプション→詳細設定→数式で、「☑1904年から計算する」のチェックボックスをオンにします。
1日の実働時間が、-1:00と表示されました。
注意：
「1904年から計算する」をONにすると、シリアル値の開始が4年と1日だけ動きますので、日付を入力しているときは、注意が必要です。このオプションは、マッキントッシュ版との互換性を保つために用意されているオプションで、1900年からの4年分のみ、マイナス値を日付及び時間として扱うことができます。それを応用した設定となります。
関数を使う
関数を使う方法は、絶対値で計算して、後でマイナス符号をつけるという方法です。
ＡＢＳ関数で絶対値を求め、TEXT関数でマイナスの時の表示を設定します。
時間計算対策の違い
正しいデータとしてのマイナス計算
時間のマイナス計算をしなければならないケースは、例えば何等かの仕事の完了時間を業務改善として計測するような場合(前回の時間と比較して何分遅いなど)であり、この場合は、マイナスとなることは正しい結果です。よって、「オプション設定」か「関数を使って」対応しなければなりません。
不正データによるマイナス計算
出勤簿の始業・終業時間の逆転や休憩時間は、不正データであり、入力ミスさえなければマイナス計算は発生しません。よって、こちらの場合は、「オプション設定」や「関数を使って」対応するのではなく、不正なデータは無視するか、入力データに入らない様に、入力時チェックを行うことが適切です。
不正データは入力ミスとして、スペースを埋める。
休憩時間の3:00が入力間違えとしてマイナス計算せず、「######」と表示されるのを避ける為、スペースを埋め込みます。(実質、1日の実働時間=0とする。)
データの入力規則の設定
時間計算でエラーとならない様に、時刻を入力時にチェックをすることができます。例えば、1日の勤務時間より大きい休憩時間は、入力時に入力できない様にチェックできれば、「######」が発生することはありません。
休憩時間のセルにカーソルを位置づけて、「データの入力規則」をクリックします。
①条件の設定
データの入力規則の画面が表示されたら、「設定」タグを選択して、以下の様に設定します。
- 入力値の種類・・・・「時刻」を選択
- データ・・・・・・・「次の値より小さい」を選択
- 次の時刻まで・・・・「=E40」(1日の勤務時間)と入力
②エラーメッセージの設定
「エラーメッセージ」のタグを選択して、以下の様に設定します。
- スタイル・・・・・・「停止」を選択
- タイトル・・・・・・ここでは、「休憩時間」と入力
- エラーメッセージ・・「休憩時間は・・・下さい。」と入力
入力チェックの実際
①間違った時間を入力
休憩時間に、「3:00」と入力すると、以下の通りエラーメッセージが表示され、再入力となります。
②正しい時間を入力
休憩時間に「1:30」と入力すると、エラーメッセージは表示されません。
端数処理の落とし穴
原因は端数処理
excelの計算で、電卓と数値があわない場合があります。その原因は、端数処理にあります。次の表を見て下さい。
消費税の合計を電卓でたたくと、3,131円ですが、excelシートでは3,132円となっています。
excelでは、実際には少数がある場合も、表示形式によって整数の様に表示されます。ところがSUM関数は、少数も込みの結果を返しますので、このような差異が生じます。上記の表で、実際に消費税の数値はどのようになっているか？を見てみます。
C2,C3,C4のセルの表示形式を標準に設定してみます。
消費税は、少数点以下切り捨てなのに、四捨五入されています。
ROUND関数で対応
Ｃ列のセルの計算式を、以下の様にROUND関数をもちいて、小数点以下を切り捨てます。
