東京本社 TEL : 03-5728-5580

支社連絡先一覧

mypage

聞くは一時、聞かぬは一生

Q238 Excelで時間の合計がうまくいきません。設定や計算の方法を教えてください。

作業時間をエクセルで管理することになりました。とりあえず、データを入れてみたのですが、合計や計算がうまくいきません。どうしたらよいでしょう。

A238 合計はSUMでできますが、表示形式には注意が必要です!見た目に惑わされないで!

Excelでは、時間はシリアル値というデータで管理されています。 普通、時刻を入力する際に「●●:○○」というデータを入力しますが、エクセルではこの入力文字列をまず「時刻を表しているな」と判断し、それを裏でシリアル値に変換して保持します。 ただ、シリアル値そのもので表示してしまうと、何がなんだか分かりませんので、エクセル側でそれをもう一度時刻の形にして表示しているのです。 これを実現する機能=セルで表示する値の設定を、「表示形式」と呼びます。 セルの表示形式の確認・変更の仕方 確認したいセル(もしくは範囲)を選択する 右クリックし「セルの書式設定」をクリック 開いたウィンドウの一番左手前のタブが「表示形式」 聞くは一時238-1 下図は、同じ表ですが、左が「時刻形式」右が「シリアル値」で表示した状態です。 聞くは一時238-2              聞くは一時238-3 シリアル値は、1日を”1”に置き換えた値です。 1日=24h=1 1時間=1/24=0.041666… 1分=1/(24*60)=0.0006944… 1秒=1/(24*60*60)=0.00001157… です。 上の右図(シリアル値表示)から、おおよその時間を想像するのは、なかなか難しいですよね。 さて、ご質問の「時間の合計」についてです。 上の図(左側)をSUMで縦に集計すると、下記のようになります。 聞くは一時238-4 おかしいですね。 正解は”51時間30分”のはずです。 実は、このセルの表示形式が「時刻」になっているため、24時間表示を超えた”○日”の部分が、表示されなくなってしまっているのです。 これを回避し、24時間を越えた時間を表示するためには、先ほど紹介した「表示形式の変更」を使って、下記のように[h]:mmと設定する必要があります。 聞くは一時238-5 これで、時間の表示はOKですね。 聞くは一時238-6 それでは、下の表を見てください。 先ほど出した時間の合計に、時給1000円をかけてみたところです。 普通に考えれば、 5万1500円となるはずですが、、、。 聞くは一時238-7 2145円?!。 そうです。ここで冒頭でご紹介した「シリアル値」が関わってくるのです。 51:30はシリアル値では、2.145833333…。 つまり1000円×2.145833333…の結果が表示されてしまっています。 これを回避するには、給料の欄に 合計のセル × 時給のセル × 24 と、入れればいいのです。 聞くは一時238-8   時間の表示や、シリアル値のことを知らないと「???」となってしまうことばかりですが、理屈が分かれば時間計算も怖くありません。是非チャレンジしてみてください。