働き方改革応援団!

「働き方改革」を前進させるため、関連情報をネタにいろいろ考えてみたい。

【スポンサーリンク】

Excelで労働時間を集計する「時間の取り扱い」2/5

  Excel・労働時間の第2回目です。今回は、Excelの「時間の取り扱い」の基本について、サクッと確認します。

 Excelで時間計算をするとき、なんとな~くでも計算できてしまうのですけど、思わぬところでエラーがでたりします。「給与計算」とか「経営資料」とか、結構ミスが許されないケースで集計することが多いので、神経使いますよね。

Excelで「時刻」「時間」の取り扱い

 Excelには、「10進数の数値」と「60進数の数値」を混在して取り扱うための工夫があります。それが「シリアル値」です。

 シリアル値とは、「1900年1月0日からの経過日数(時間)」のことです(WindowsExcelの場合)。時間を表す場合は、たとえば1時間の場合「0.0416666666666667(1の1/24)」になります。

 あえて、ややっこしい書き方をすれば、Excelで「1時間(1:00)」という時間を入力したつもりでも、それは「1900年1月0日 1:00」という時刻を表す「0.0416666666666667」という数値が保存されています。念のため付け加えると、Excelに保存されている時間は、「見た目」と「中身」が異なります。「2022/9/21(日付)」とか「10:32:14(時間)」という見た目は、書式設定によりそう見えているだけで、実際のデータは、「44825(2022/9/21)」とか、「0.439050925925926(10:32:14)」という数値で格納されています。

 私はややっこしいコトが嫌いなので、一箇所にまとめて書いてみました。これ以降は、カンタンな確認になります。

見た目の整え方

 Excelに格納されているデータは、「1900年1月0日からの経過日数(時間)」ということですが、わかりやすく「見た目を整える」ことができます。「セルの書式設定」ですね。

セルの書式設定

 ここで、注意しておきたいのは、Excelに格納されているデータは「1900年1月0日からの経過日数(時間)」になるので、たとえば「10:32(10時32分)」という時間を入力したつもりでも、中に格納されているデータは、「1900/1/0 10:32:00」という時刻を表す数値になるということです。

時間の引き算でエラーがでるとき

 確認です。

 スクリーンショットは、時間の引き算をしています。1行目は「始業」「終業」などのタイトル行です。2行目と3行目に見た目が同じ「時間」が入力されていて、同じ計算式で引き算をしているのですが、3行目にはエラー「#####」が表示されています。

時間の引き算

 スクリーンショットにあるとおり、Excelは「負であるか、大きすぎる日付および時間は #####と表示されます」ってことになっています。実は、A3には「1900/1/10 10:30」、B3には「1900/1/0 13:40」が入力されているので、計算結果がマイナスになるのでした。Excelでは、「1900年1月0日からの経過日数(時間)」で日数・時間を表すため、「負の数字はあつかえません」(表示形式を日付・時間に設定したケースの話です)。

 日常的には「マイナスの時間」というのも扱ったりするので、こういう計算はウッカリやりがちです。特に、年をまたいだりしたときの計算にエラーが出る印象です。時間計算をするときでも、「日時」を正しく設定することで回避できます。仕事では「勤怠管理システム」や「給与計算システム」からの出力データを元に計算することが多いと思うので、最初に確認することをおすすめします。

時間の集計結果が正しくない!!

 たとえば、「1週間の総労働時間を集計する」という例です。

 スクリーンショットは、9時30分から14時30まで休憩ナシで1日5時間・5日労働した人の計算例です。

時間の集計

 sum関数で集計した結果はなんと「1:00」!!。ちなみに、5:00と入っているセルに「5」をかけても、結果は「1:00」です。で、この集計結果の「1:00」と表示されているセルに1,000円(時給)をかけると、「1,041.667」という結果になります。

 どういうこと?これ?www

正しくない原因はコレ

 実際、いそいで集計しないといけない!!とか、報告書を提出しないといけない!!とかという、切羽詰まった状況のときに、上記のようなナゾの結果がでたら・・・と思うとゾッとします。原因と対策はシンプルなので、事前に確認しておくといいですね。

 原因は、Excelでは、日時は「1900年1月0日からの経過日数(時間)」で表されるということと、「見た目と中身が異なる(セルの書式設定)」ということです。

 1つづつ確認します。

 まず、「5:00(時間)×5(日)」の計算結果が「1:00」と表示される件についてです。本来ならば「25:00(25時間)」と表示されなければいけませんね? 実は、セルの中には「1.04166666666667」という数値が入っています。Excelの日時は「1900年1月0日からの経過日数(時間)」ということなので、この数値は「1900年1月1日 1時」を表しています。24時間は1日なので、25時間は「1日と1時間」です。結局、日付が1日進んでいるのですが、セルの書式で時間しか表示しないようにしているので、日付の部分が省かれた「1:00」が表示されているということになります。

 1,000円の時給を掛け算した結果についても、セルに入っている数値が「1.04166666666667」なので、1,000をかけると「1,041.667」という結果になります。

正しく計算するポイント

 原因がわかれば対策はカンタンです。

 まず、「1:00」と表示されているものを「25:00(25時間)」と表示するには、セルの表示形式を「[h]:mm」にすればOKです。余談ですが、分についても、たとえば「100(分)」という表示にしたければ「[m]」のように、分を表すmを大カッコでくくればOKです。

 つぎに計算結果なんですが、Excelに格納されているデータは「1900年1月0日からの経過日数(時間)」なので単位は「日」です。時間は小数点以下の数値で表されます。時給は時間あたりの給料なので、結局「時間の入ったセル×24(1日の時間)×時給」を計算すると正しい金額になります。

 ところで、この部分、会社に務めているときに後輩に説明したのですが、いくら説明しても理解してもらえませんでした。そんなに難しいですか? たとえば、Excelでは12時間(12:00)は「0.5(日)」という数値がセルの中に格納されているので、1,000円の時給の人の給料を計算するには「0.5(日)×24(1日の時間)×1,000(時給)」を計算するということなんですが、どうしても伝わらないので「とりあえず、計算結果が変だったら24を掛けるルールです」と説明したのでした。

 なにか、良い説明方法があったら、教えてください。

時間(60進数)を10進数に変換する

 5時間30分を「5:30」と入力して集計したり計算したりするには、数値を取り扱うときの「Excel特有のクセ」に配慮しないといけません。シンプルに取り扱いたいと思うなら10進数の普通の数値として集計・計算することをおすすめします。

 変換方法はカンタンです。2ステップにわけて説明します。

  1. 5:30(5時間30分)などと入力されているセルに「24」を掛ける。
  2. 24を掛けた結果のセルの書式を「標準」にする。

 ステップ1で24を掛けることで、シリアル値を時間数(10進数)に変換することができます。が!、実際にやってみると「12:00」という時間が表示されてビックリするでしょう。これは、セルの表示形式が時刻を表示する形式のままであることが原因です。

 そこで、ステップ2でセルの書式を変更します。「標準」に変更すると「5.5(5.5時間)」という表示になります(「標準」以外でも、見やすい書式にすればいいですね)。このように数値(10進数)に変換して計算すると、結果がマイナス数値になってもエラーになりません。安心です。

まとめ

 まとめると、

  • 表示形式を「[h]:mm」に設定する。
  • 時間を使って計算するとき(たとえば、時給をかけるとき)は、「×24(時間)」を計算する。

 と、いうことがコツです(ギュッとまとめました)。

 労働時間を集計するときは、元になる数値を勤怠管理システムや給与計算システムからの出力データを使うケースが多いと思います。Excelに読み込ませたときに、意図しないデータとして格納される可能性があるので、とくに初めて集計表を作ったりするときは、検算が欠かせません。

第3回「法律編」予告

 Excelでの時間の基本的な取り扱いを確認したので、次回は法律上での取り扱いを確認します。

 この部分も結構ややっこしいですが、できるだけカンタンにまとめるつもりです。