Office Hack

エクセルで時間を引き算する方法(######エラー対応も)

  • by kenji
  • Release
  • Update

Excelは標準の数値計算だけでなく、時間や日付の計算も可能です。

この記事では時間の引き算の基本的な方法と、時間のマイナス表示がエラーになったときの対処方法を詳しくご説明します。

時間(時刻)を四則演算で計算する

ユーザー定義に変更

11時から3時を引く計算をします。B2セルに『11:00』、C2セルに『3:00』と入力します。すると、赤枠の表示形式の欄が「標準→ユーザー定義」に自動的に変更されます。

ユーザー定義の表示内容

セルの書式設定を見てみると、デフォルトでユーザー定義の分類の中に「h:mm」にカーソルが当たっております。hは時間、mmは分を表しており、「11:00」はただの数字ではなく、時間として認識をしていますよという意味となります。

時間の引き算

B2からC2を引き算します。

時間の引き算結果

時間同士の計算と認識されておりますので、正しい「8:00」が算出されました。

年/月/日 時:分を引き算する

日付時間の差分

ここでは年/月/日と時:分の引き算方法を行います。すべて半角で入力してください。年/月/日と時:分の間は、半角スペースで空けます。

引き算の参照

B2からC2を引き算します。逆でも可。

シリアル値が表示

結果を見るとシリアル値で表示されました。右上の赤枠を見ると表示形式が「標準」となっているためです。

その他の表示形式の選択

表示形式を変更していきます。【プルダウンメニューをクリック】していただき、一番下の【その他の表示形式】を選択します。

表示形式を時刻に

分類の【ユーザー定義】を選択し、【h:mm】を選択し、【OK】ボタンを押します。

正しい時間の表示

正しい時間表記となりました。#######と表示されてしまう方は、下記の「時間のマイナス表示について」のコンテンツをご確認ください。

時間のマイナス表示について

シリアル値の初期値を変更して表示する

時間のマイナス表記

11時から12時を引いてみます。本来なら「-1:00」と表示されるところ、#######と表示されてしまいました。

これはExcelが裏側で、時間の数値を「シリアル値」という連続した値で持っており、通常の時間の引き算はこのシリアル値同士の差分で計算しております。しかしシリアル値はマイナスの時間を計算することができないので、#######と表示されてしまいます。

これを解消するためにはあるチェックボックスをオンにするだけで解消できます。次からその手順をご説明します。

ファイルを選択

【ファイル】タブを選択します。

オプションを選択

【オプション】を選択します。

詳細設定を選択

「Excelのオプション」ダイアログボックスが開かれますので、【詳細設定】を選択します。

1904年から計算するをチェック

一番下までスクロールして、【1904年から計算するをチェック】にチェックを入れて【OK】ボタンを押します。

1900年1月1日が初期値として定められておりますが、チェックすると1904年1月1日を初期値として変更できます。これはMacでそのような仕様となっており、Mac版のエクセルの互換性を保つために用意されております。

時間のマイナス表示

時計のマイナス表示が可能となりました。

初期値を変更した際に気をつけなければならないのは、別のセルで日付の計算をしているときに4年と1日だけ、ズレが生じてしまいます。その回避策としては、以下の関数を利用すると便利かもしれません。

関数を組み合わせて表示する

TEXT関数とABS関数とIF関数を組み合わせて#######と表示されてないようにします。

TEXT関数
TEXT関数の利用

TEXT関数の書式は、=TEXT(数値,表示形式)となります。第1引数に「時間の引き算の結果」、第2引数に「時間の表示形式"h:mm"」を指定します。

もともとはシリアル値で表示されているため、時間の表示形式に変更する必要があります。

TEXT関数の結果

#VALUE!エラーが出てしまいました。これはまだマイナスの値のまま表示形式を変換しただけですので、エラーとなります。そこでABS関数を利用して、マイナスの値を絶対値に変換します。

ABS関数
ABS関数の利用

ABS関数の書式は、=ABS(数値)となります。数値を絶対値に変換してくれます。

絶対値とは、「+(プラス)や-(マイナス)のつかない数字」のことを言います。0からどれだけ離れているかという意味で認識いただくと分かりやすいかもしれません。

ABS関数の結果

絶対値なので負の値が正の値に置き換わりました。正しい表記は-1:00にしないとならないので、次のIF関数で条件分岐を指定していきます。

IF関数
IF関数の利用

IF関数の書式は、=IF(論理式,[真の場合],[偽の場合])となります。

論理式は「B2セルがC2セルより大きい場合」で、それが真の場合マイナスを付けない、偽の場合マイナスを付けるという式を組みました。

IF関数の結果

結果としては、「B2セル(11:00)がC2セル(12:00)より小さい」ので偽となり、マイナスが付与されます。

正の数で確かめる

真の値も試してみましょう。B2セルを13:00に変更すればB2>C2が成り立ちますので、赤枠の部分はマイナスが外れます。

これらの関数を組み合わせれば、【1904年から計算するをチェック】をオンにしなくとも、時間のマイナス表示が可能となります。

日付をまたぐときの時間の引き算(勤怠表を作る)

勤怠管理表を作っているとき、深夜勤務の場合21時出勤で、翌日の6時退勤の場合、日をまたいだ引き算をすることがあります。

この場合の計算方法を求めます。

勤怠表の準備

21時に出勤して1時間の休憩を取り、翌朝6時に退勤した場合、通常であれば「退勤時間-出勤時間-休憩時間」で成り立ちます。

通常の引き算結果

その結果は上記で示したとおり、マイナスのシリアル値となるため######で表記されてしまいます。

+1を加える

これを回避するために(マイナスにならないように)、退勤時間に『+1』を加えます。これを行うことにより24時間分足されたと認識してください。

日をまたいだ正しい結果

正しい結果「8:00」が表示されました。

他のセルへの反映

他のセルへ反映します。

その他、様々な引き算の編集方法