セルの書式設定と定番の関数を覚える

Excelで思い通りに時間を計算できていますか? 特につまずきがちなのが、24時間以上の表示時間の切り上げ・切り捨てです。

エクセル時短】第111回では、たまの操作で悩みがちな時間計算の基本について紹介します。

24時間以上の時間を正しく表示する

複数日の作業時間を足していったとき、明らかに少ない時間が合計として表示されることがあります。これは、Excelが時間の入力されたセルの表示形式を[時刻]と判断するため、「23:59」を超えたら「0:00」に戻ってしまうためです。

ここでは1週間の作業時間を加算していく例で、時間の合計を正しく表示する方法を見てみます。[Ctrl]+[1]キーで[セルの書式設定]ダイアログボックスを呼び出して再設定しましょう。

1作業時間の合計を求める

【エクセル時短】時間計算の基本。24時間を超えるときの時間の書式設定と時間を丸めるときの3つの関数

セルG2に1週間分の作業時間を合計する数式「=SUM(B2:F2)」を入力します。フィルハンドルをドラッグして、セルG7までコピーしておきます。

2[セルの書式設定]ダイアログボックスを表示する

【エクセル時短】時間計算の基本。24時間を超えるときの時間の書式設定と時間を丸めるときの3つの関数

セルG2の関数式がコピーされました。明らかにセルG7の値がおかしいですよね。ぱっと見で40時間は超えそうです。[Ctrl]+[1]キーで[セルの書式設定]ダイアログボックスを呼び出して、セルの書式を設定し直します。

3[ユーザー定義]の書式を設定する

【エクセル時短】時間計算の基本。24時間を超えるときの時間の書式設定と時間を丸めるときの3つの関数

[セルの書式設定]ダイアログボックスが表示されました。[表示形式]タブの[ユーザー定義]をクリックして、[種類]に『[h]:mm』と指定します。「h」を『[]』で括ることで、24時間以上の表示ができるようになります。なお、「06:45」のように、1桁の時間の先頭に「0」を付けたい場合は『[hh]:mm』と指定してください。

4セルの書式が設定された

【エクセル時短】時間計算の基本。24時間を超えるときの時間の書式設定と時間を丸めるときの3つの関数

セルの書式が設定され、時刻の集計値が正しく表示されました。

時刻を切り上げる・切り捨てる

作業時間の合計を求めたあと、半端な時間を切り上げたり、切り捨てたりすることもあるでしょう。こうした時刻の丸めの操作も、慣れないうちは面倒ですよね。ここでは30分単位で時間を丸める方法を見てみましょう。

定番の関数としては、以下で解説するMROUND(ラウンド・トゥ・マルチプル)関数、CEILING(シーリング)関数、FLOOR(フロア)関数の3つがあります。なお、Excel 2013以降では、CEILING関数とFLOOR関数の新しいバージョンであるCEILING.MATH(シーリング・マス)関数、FLOOR.MATH(フロア・マス)関数も利用できます。

MROUND数値, 基準値

引数[数値]を引数[基準値]の倍数になるように丸めた結果を求めます。引数[数値]にもとの数値を指定します。引数[基準値]には丸めの基準となる数値を指定します。

CEILING数値, 基準値

引数[数値]を引数[基準値]の倍数に切り上げます。引数[数値]にもとの数値を指定します。引数[基準値]には切り上げるときの基準となる数値を指定します。

FLOOR数値, 基準値

引数[数値]を引数[基準値]の倍数に切り捨てます。引数[数値]にもとの数値を指定します。引数[基準値]には切り捨てるときの基準となる数値を指定します。

3つの関数とも引数は同じです。基準となる数値の倍数で切り上げ・切り捨ての処理を行います。ここでは引数[数値]には時刻が入力されたセルを指定し、引数[基準値]には30分を表す「"0:30"」を指定しています。

【エクセル時短】時間計算の基本。24時間を超えるときの時間の書式設定と時間を丸めるときの3つの関数

セルG2~G7の時刻を基準にして、30分単位で切り上げ・切り捨ての処理をしています。

HINTExcelにおける日付と時刻の扱い

Excelでは、日付と時刻は「シリアル値」という値で管理されています。日付は、1900/1/1を基準にした経過日数で表されます。時刻は、24時間を「1」として分数で表します。午前1時なら「1/24」(0.0416666...)、正午12時なら「12/24」(0.5)という具合です。

例えば、2019/6/1の正午12時なら、「1900/1/1から43617日後の12/24時」なので、「43617.5」とセルに入力して書式設定を[日付]にすると、「2019/6/1 12:00:00」と表示されることがわかると思います。

関連まとめ記事