関数

エクセルで時間の計算をする時に使える関数は?!表示がうまくいかない方も必見!

タイトル
エクセル初心者
エクセル初心者
出勤簿のフォーマットを作成しないといけないんだけど、時間も関数で計算できるのかな?
エクセル初心者
エクセル初心者
24時間を超える『26:00』をエクセルで表示させたいのに、なぜか『2:00』って表示されてしまう。どうして?

エクセルで、出勤簿の作成をしていて、よく聞かれる質問ですね!

まず、時間の計算もエクセルなら簡単にできます。

演算子(+や-など)を使っても計算できますし、SUM関数も大丈夫です。

そして、勤務時間の合計を表示すると、なぜか少ない時間で表示されてしまう状態も簡単に解決できます。

時刻の表示は24時を超えると0時に戻るようになっています。

なので、表示形式で[h]:mmを選ぶだけで設定できますよ。

簡単ステップ

①時間の計算は、演算子でもSUM関数でも使える

②24時を超える時間の表示は『表示形式』を変更する

本文では『出勤簿』を参考にしながら、上記2点を詳しく、解説していきますね。

エクセルで時間の計算をする時に使える関数は?!

エクセルでは、時間の計算も簡単に求めることが出来ます。

出勤簿を使って説明しますね。

勤務時間の求め方勤務時間の求め方

↑F3の勤務時間のセルには

=D3C3E3

の式が入っています。

退社時間から出社時間と休憩時間を演算子の-(マイナス)で引き算しています。

勤務時間は『8時間』の『8:00』と表示されます。

合計時間はSUM関数合計時間はSUM関数

↑そして勤務時間の合計はSUM関数で求めることが出来ます。

SUM関数で合計を出したのに『8:00』と表示されています。

明らかにおかしいですね!

表示の設定については、後程解説します。

休憩時間の欄を省いた出勤簿休憩時間の欄を省いた出勤簿

↑こちらの出勤簿は、休憩時間の欄がありません。

確実に1時間の休憩がある会社の設定です。

そのような場合はTIME(タイム)関数を使うと大変便利です。

『1:00』という入力を省くことが出来ますのでスッキリと見やすい出勤簿になりますよ。

TIME関数とは

=TIME(時,分,秒)

意味:時分秒の時間を表示することが出来ます。時刻の計算に使うことが出来ます。

では、上記の演算子や関数を使い、出勤簿を作成してみましょう!

エクセルで出勤簿作成!時間の計算をしてみよう!

ベースとなる形を作成ベースとなる形を作成

↑出勤簿を作成しましょう。

日付や曜日などまで準備されたところからスタートします。

日付や曜日、土日祝の色付けの方法についてはこちらの≫エクセルでカレンダー作成!祝日や土日に色付けして見やすくしようを参考にしてみてくださいね。

タイトル
エクセルでカレンダー作成!祝日や土日に色付けして見やすくしよう日々のスケジュール管理は、とても大切ですね。エクセルでカレンダーを作成し、スケジュール管理表を作成するととても便利です。エクセルでカレンダーを作る際に祝日や土日に色付けも出来ます。予定を表示させることも出来るので、手作業で入力しなくても時短です。効率よく入力ミスを減らし、便利なカレンダーの作成を解説します。...
時間の入力時間の入力

↑出社時間に『9:00』そして退社時間は『18:00』休憩時間は『1:00』と入力します。

では、勤務時間の計算をしましょう!

退社時間-出社時間-休憩時間退社時間-出社時間-休憩時間

↑F3のセルに【退社時間-出社時間-休憩時間】を入力します。

上記で説明した【=D3-C3-E3】ですね!

勤務時間が求められる勤務時間が求められる

↑勤務時間は『8時間』の『8:00』と表示されています。

平日に時間を入力平日に時間を入力することで表示される『0:00』

↑簡単に勤務時間を求めることが出来たので、土日祝以外にも同じように入力します。

Dr.オフィス
Dr.オフィス
お休みの日の勤務時間『0:00』が気になるのでIF関数を使って『0時間』を表示しないようにできるよ!
『0:00』を表示しないIF関数『0:00』を表示しないIF関数

↑『0:00』を表示しないようにするために

=IF(D3C3E3=0,””,D3C3E3)

に書き換えました。

IF関数についてはこちら≫エクセルの【IF関数】を使い「#N/A」や「0」などを表示させず空白セルにする!を参考にしてみてください。

タイトル
エクセルの【IF関数】を使い「#N/A」や「0」などを表示させず空白セルにする!エクセルではさまざまなエラー値が表示されることがあります。【IF関数】や【IFERROR関数】を使い『0』や『#N/A』などを非表示にする方法を解説します。不要な数値やエラー値は表示させないようにしてみましょう。ちょっとした関数を使うことで、すっきりとした見栄えの良い資料などを作成できます。...
IF関数で0が非表示になり見栄えが良くなったIF関数で0が非表示になり見栄えが良くなった

↑勤務時間に『0:00』の表示もなくなりスッキリしましたね!

勤務時間の合計を求めるSUM関数勤務時間の合計を求めるSUM関数

↑F31に勤務時間の合計を表示させましょう。

F31を選択し、オートSUMボタンを使い合計時間を計算します。

SUM関数について詳しい解説はこちら≫エクセルで足し算が簡単にできる【SUM関数】で合計値を求めよう!を参考にしてみてください。

タイトル
エクセルで足し算が簡単にできる【SUM関数】で合計値を求めよう!エクセルでは、合計を求める表計算を作成することが多いです。合計を求める関数【SUM(サム)関数】について解説します。SUM関数はもっとも使用頻度の高い関数です。足し算がとても簡単にできます。エクセルではオートSUMボタンも準備されていて、マウスから簡単に操作できます。fxボタン、注意点なども解説します。...
明らかおかしい勤務時間の合計明らかおかしい勤務時間の合計

↑勤務合計時間がなぜか『8:00』と表示されてしまいました。

エクセルでは、24時を過ぎると0時に戻るように設定されていますので、この対処法は下の項目で解説します。

TIME関数を使ってスッキリとした出勤簿も作成

休憩時間の欄が省かれた出勤簿休憩時間の欄が省かれた出勤簿

↑先ほどまで紹介していた出勤簿と少し異なります。

休憩時間の欄を省いています。

Dr.オフィス
Dr.オフィス
1列減るだけでずいぶん印象が変わってくるね!
TIME関数で勤務時間を求めるTIME関数で勤務時間を求める

↑E3に勤務時間を計算して求めてみましょう。

=D3C3-TIME(1,0,0)

を入力します。

退社時間から出社時間と、TIME関数で1時間も引いています。

エラー『########』エラー『########』

↑オートフィルでコピーするとお休みの日に『######』のエラーが表示されています。

エラー回避のIF関数エラー回避のIF関数

↑見た目がよくないのでIF関数を使い、エラー表示を回避しましょう。

=IF(D3C3-TIME(1,0,0)=(-1/24),””,D3C3-TIME(1,0,0))

オートフィルでコピーします。

IF関数でスッキリIF関数でスッキリ

↑『######』が表示されなくなりスッキリします。

勤務合計時間を表示形式で設定すると完成です。

時間の表示がうまくいかない!?24時を超えると0になる時の対処法!

勤務時間の合計が変!?勤務時間の合計が変!?

↑勤務合計時間が明らかにおかしい『8:00』と表示されています。

前述したように『エクセルは24時を過ぎると0時に戻る』となっています。

それでは正しい時間も分からず困りますね。

このような場合は表示形式から設定します。

セルの書式設定ダイアログボックスを出すセルの書式設定ダイアログボックスを出す

↑勤務合計時間のF31を右クリックし、セルの書式設定ダイアログボックスを表示します。

表示形式の設定表示形式の設定

↑表示形式タブの『ユーザー定義』から種類に『[h]:mm』を入力し、OKを押しましょう。

正しい勤務合計時間が表示された正しい勤務合計時間が表示された

↑勤務合計時間が正しく表示されました。

セルの書式設定ダイアログボックスの表示方法はショートカットキーでもできます。

ショートカットキーの方法はこちら≫【エクセルのショートカットキー】セルの書式設定を表示する「Ctrl+1」を参考にしてくださいね。

ホームタブからの表示形式ホームタブからの表示形式

↑ホームタブの『数値の➘』からもセルの書式設定ダイアログボックスを表示できますので、使いやすい方法を試してみてください。

まとめ

エクセルで、出勤簿を作成する際によく聞かれる

『時間の計算』や『時間の表示方法』について解説しました。

エクセルでは、時間の計算も簡単です。

効率良くフォーマットを作成し、毎月の時間の計算がスムーズに作業できると良いですね。

ABOUT ME
Dr.オフィス
Dr.オフィス
エクセルの使い方や応用法を日々研究しています。 初心者から上級者までわかりやすく解説していきます。 エクセルについてわからないことや気になることなどございましたら是非、コメントやお問い合わせからご質問ください。