Excelで時間の合計がおかしい？ 〜Excelでの時間計算のコツは表示形式
「この計算結果は違う。少なすぎる」
こんな経験をした人はいないだろうか。
Excelで時間を計算するときには、ちょっとしたコツがあるのだ。
これは計算式に問題があるわけではない。
セルの表示形式に問題があるのだ。
●時間の合計を出す
時間の合計は簡単に求められる。
下図は、セルE10を選択し、［オートSUM］ボタンをクリックしたところだ。
セルE2からE9までが計算範囲となっていることを確認し、［Enter］キーを押す。
すると、計算結果として「10:42」が表示された。
しかし、セルE2とE3を足しただけでも15時間46分。
セルE2からE9までのこの合計値は、明らかにおかしい。
ちなみに、セルE2からE9までをドラッグしてタスクバーを見ると、合計値はきちんと「58:42:00」と表示されている。
つまり、計算式は正しいが、セルの表示形式が適切ではないのだ。
Excelでは表示形式を変えることにより、1つの値が、さまざまな姿で表示される。
●セルの表示形式を変更する
セルの表示形式は、デフォルトでは［標準］となっている。
文字や数字など、入力したものがそのまま表示される形式だ。
しかし、「1/1」や「1-1」などと入力したときは、「1月1日」と表示される。
数字をスラッシュやハイフンで区切ると日付と判断されて、自動的に「m"月"d"日"」の表示形式が適用されるからだ。
同様に、「7:40」のように数字をコロンで区切って入力すれば、時刻の表示形式「h:mm」が適用される。
「h:mm」のセルを計算すると、計算結果のセルも同じく時刻を示す「h:mm」となる。
そして、この表示形式での合計値は、24時間毎に繰り上がる。
合計値が「58:42」なら、「24」時間が2回繰り上がり、残りの「10:42」が表示されるのだ。
つまり、これは2日後の10時42分という「時刻」を示している。
だから「58:42」という合計値を表示したいときは、時刻ではなく、経過時間を表す表示形式に変更しなければならない。
前置きは長くなったが、表示形式の変更はすぐにできる。
表示形式を変更したいセル（ここではセルE10）をクリックし、［Ctrl］＋［1］キーを押そう。
あるいは、［ホーム］タブの［数値］グループにある小さな矢印をクリックしてもよい。
すると、［セルの書式設定］の［表示形式］タブが開く。
合計値を出したあとなら、表示形式は［ユーザー定義］の「h:mm」となっているはずだ。
hの左右を" [ ] "で囲み、次のように設定しよう。
[h]:mm
これが、時間の合計が表示できる表示形式だ。
すぐ上の［サンプル］が「58:42」となっていることも確認しておこう。
［OK］をクリックすると表示形式が変わり、セルに正しく「58:42」と表示される。
●30分未満を切り捨てる
就業時間を申告するときには、合計就業時間から15分未満や30分未満など、決められた時間を切り捨てることも多い。
30分未満を切り捨てるなら、合計時間が「58:42」の場合、申告時間は「58:30」になる。
毎月申告するなら、FLOOR.MATH関数を設定しておこう。関数を使えば入力ミスの心配もない。
FLOOR.MATH関数は、「数値を最も近い基準値の倍数に切り下げる」と説明されている。
ざっくり言えば、合計時間を30分や15分といった基準値で割り、その余りを切り捨てることができる、ということだ。
ここでもポイントは表示形式だ。
関数を入力するセル（下図ではセルE11）は「[h]:mm」にしておこう。
もちろん、関数を設定後に変更してもよい。
関数は、次のように挿入する。
・［関数の挿入］（数式バーの隣の［fx］）をクリック。
・［関数の分類］の［数学/三角］でFLOOR.MATH関数を選択。
・［数値］に、切り捨てをする数値を指定。ここではセルE10を指定した。
・［基準値］を指定。ここでは30分未満を切り捨てるため「"0:30"」とした。
ちなみに15分未満なら"0:15"、1時間未満なら"1:00"などとする。
なお、「"」は省略せずに、必ず入力しておこう。
［モード］は負の数に関わることなので、ここでは省略する。
このように設定すると、数式バーには、「=FLOOR.MATH(E10,"0:30")」と表示されているはずだ。
［OK］をクリックすると、セルE10の「58:42」に対する値「58:30」が表示される。
●時間を小数表示に変更する
給与計算時などに計算しやすいよう、「58:30」を「58.5」のような小数表示にしたいこともある。
そんなときには、「58:30」に24をかける。
計算結果を出したいセルの表示形式は、この場合は必ず［標準］にしておくこと。
これも大切なポイントだ。
何故、24をかけるのかについて、少々補足する。
Excelでは時間を、0時を「0」、24時を「1」とした連続した値、つまりシリアル値として管理している。
その中間の12時ならシリアル値は「0.5」。
12を24で割れば0.5になる。
午前9時なら「0.375」。9を24で割った数値だ。
これは、［表示形式］タブの［標準］をクリックして確認することもできる。
そのため、「9:00」と表示されたセル（シリアル値「0.375」）に24をかければ、「9」となる。
つまり「58:30」に24をかければ、そしてそのセルの表示形式を［標準］にすれば、「58.5」と表示されるのだ。
このように、時間の計算自体は簡単だ。
ポイントは、表示形式。
表示形式が重要だと覚えておけば、時間計算はすぐにマスターできるだろう。
執筆 中野 久美子
