セルの書式設定と定番の関数を覚える
Excelで思い通りに時間を計算できていますか？ 特につまずきがちなのが、24時間以上の表示と時間の切り上げ・切り捨てです。
【エクセル時短】第111回では、たまの操作で悩みがちな時間計算の基本について紹介します。
24時間以上の時間を正しく表示する
複数日の作業時間を足していったとき、明らかに少ない時間が合計として表示されることがあります。これは、Excelが時間の入力されたセルの表示形式を［時刻］と判断するため、「23:59」を超えたら「0:00」に戻ってしまうためです。
ここでは1週間の作業時間を加算していく例で、時間の合計を正しく表示する方法を見てみます。［Ctrl］＋［1］キーで［セルの書式設定］ダイアログボックスを呼び出して再設定しましょう。
1作業時間の合計を求める
セルG2に1週間分の作業時間を合計する数式「=SUM(B2:F2)」を入力します。フィルハンドルをドラッグして、セルG7までコピーしておきます。
2［セルの書式設定］ダイアログボックスを表示する
セルG2の関数式がコピーされました。明らかにセルG7の値がおかしいですよね。ぱっと見で40時間は超えそうです。［Ctrl］＋［1］キーで［セルの書式設定］ダイアログボックスを呼び出して、セルの書式を設定し直します。
3［ユーザー定義］の書式を設定する
［セルの書式設定］ダイアログボックスが表示されました。［表示形式］タブの［ユーザー定義］をクリックして、［種類］に『[h]:mm』と指定します。「h」を『[]』で括ることで、24時間以上の表示ができるようになります。なお、「06:45」のように、1桁の時間の先頭に「0」を付けたい場合は『[hh]:mm』と指定してください。
4セルの書式が設定された
セルの書式が設定され、時刻の集計値が正しく表示されました。
時刻を切り上げる・切り捨てる
作業時間の合計を求めたあと、半端な時間を切り上げたり、切り捨てたりすることもあるでしょう。こうした時刻の丸めの操作も、慣れないうちは面倒ですよね。ここでは30分単位で時間を丸める方法を見てみましょう。
定番の関数としては、以下で解説するMROUND（ラウンド・トゥ・マルチプル）関数、CEILING（シーリング）関数、FLOOR（フロア）関数の3つがあります。なお、Excel 2013以降では、CEILING関数とFLOOR関数の新しいバージョンであるCEILING.MATH（シーリング・マス）関数、FLOOR.MATH（フロア・マス）関数も利用できます。
MROUND（数値, 基準値）
引数［数値］を引数［基準値］の倍数になるように丸めた結果を求めます。引数［数値］にもとの数値を指定します。引数［基準値］には丸めの基準となる数値を指定します。
CEILING（数値, 基準値）
引数［数値］を引数［基準値］の倍数に切り上げます。引数［数値］にもとの数値を指定します。引数［基準値］には切り上げるときの基準となる数値を指定します。
FLOOR（数値, 基準値）
引数［数値］を引数［基準値］の倍数に切り捨てます。引数［数値］にもとの数値を指定します。引数［基準値］には切り捨てるときの基準となる数値を指定します。
3つの関数とも引数は同じです。基準となる数値の倍数で切り上げ・切り捨ての処理を行います。ここでは引数［数値］には時刻が入力されたセルを指定し、引数［基準値］には30分を表す「"0:30"」を指定しています。
セル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」と表示されることがわかると思います。
