時間の合計 表示形式に注意が必要 1【Office シリーズ】時間・時刻の関数の使い

時間の合計 表示形式に注意が必要です。　　　　topへ

　　　標準では、24時表示(時刻表示)になっているため、24時を超える値は正しく表示されません。

　　　下図のように、合計のセルの表示形式を[h]:mmとします。

B7セルには期待する(正しい)合計値が表示されていません。

B9セルを選択します。


 

[ホーム]タブの数値グループにあるダイアログボックスをクリックします。

ショートカットキーは[Ctrl]+[1]です。


 

Excel2007以降は[ホーム]タブの[表示形式]→[その他の表示形式]を実行してもOKです。


 

Excel2003以前は、メニューバーの【書式】→【セル】を選択して、セルの書式設定を表示します。

【表示形式】タブで「ユーザー定義」を選択し、種類に　[h]:mm　と入力します。





B7セルの表示形式は　h:mm　、B9セルの表示形式は　[h]:mm　とした例です。

B7セルには24時を超えた分しか表示されず、期待した表示になりません。

B9セルは表示形式を　[h]:mm　として、期待した表示になっています。

 

時刻に対応する小数(シリアル値)を返す 解答例【Office シリーズ】時間・時刻の関数の使い

【問題】

時刻の時、分、秒が別々のセルに表示してあります。

E3セルには『時：分：秒』　E4セルには『時：分』と表示しなさい。

　 

 

【解答例】



E3:E4セルは次のように表示されました。



Excel2007以降は[ホーム]タブの[表示形式]→[その他の表示形式]を実行します。

ショートカットキーは[Ctrl]+[1]です。

Excel2003以前は【書式】→【セル】で表示形式を変更します。


 

E3セルは秒単位まで表示するので『13:30:55』 、E4セルは分単位まで表示するので『13:30』と、それぞれ設定します。


 

セルの表示が変わりました。

時刻に対応する小数(シリアル値)を返します 問題【Office シリーズ】時間・時刻の関数の使い

時刻に対応する小数(シリアル値)を返します　　　topへ

　タイム

=TIME(時,分,秒)

　　時：0 ～ 32767 の範囲で指定します。23 を超える値は 24 で除算され、剰余が時間として計算されます。

　　分：0 ～ 32767 の範囲で指定します。59 を超える値は時と分に変換されます。

　　秒：0 ～ 32767 の範囲で指定します。59 を超える値は時、分、秒に変換されます。

【問題】

時刻の時、分、秒が別々のセルに表示してあります。

E3セルには『時：分：秒』　E4セルには『時：分』と表示しなさい。

　   

日給の計算 時間単位での計算 時給の四捨五入【Office シリーズ】時間・時刻の関数の使い

時給の四捨五入

上記の結果から分かると思いますが、秒単位まで表示していた場合シリアル値での計算では秒単位の日給が計算され、実情と異なってしまいます。(通常は分単位までと思います)

また、日給計算結果には小数点以下の金額が計算されていますので、日々の日給は整数の単位に四捨五入、切り上げなどして丸めておく必要があります。

四捨五入の例です（9333となります）　　　　　　　　　

=ROUND(D5*24*C3,0)では秒単位まで計算され「9336」となるので、時と分だけ取出して計算します。

=ROUND(HOUR(D5)*C3+MINUTE(D5)/60*C3,0）

30分単位で勤務時間を切り上げて計算する場合(9500となります)

"9:20:10"　を30分単位で切り上げて　"9:30"　として計算します。

=CEILING(D5,"0:30")*24*C3

30分単位で勤務時間を切り捨てて計算する場合（9000となります）　　　　　　　　　

"9:20:10"　を30分単位で切り下げて　"9:00"　として計算します。

=FLOOR(D5,"0:30")*24*C3

日給の計算 時間単位での計算【Office シリーズ】時間・時刻の関数の使い

日給の計算

 

時間単位での計算

時間と分を取り出してから計算することもできます。

この場合はHOUR、MINUTE関数を使って時間、分を取り出して計算します。　

時間×時給＋（分÷60）×時給　=D7*C3+D8/60*C3　とします。(秒は無視しています)

=HOUR(D5)*C3+MINUTE(D5)/60*C3　と1セルでまとめることもできます。

日給の計算 シリアル値での計算【Office シリーズ】時間・時刻の関数の使い

日給の計算

シリアル値での計算

勤務時間（D5)×時給(C3)　では求めることができません。

時間データにシリアル値が使われているためです。

この問題の例では勤務した時間は　=C5-B5="9:20:10"　です。

この　"9:20:10"はシリアル値で数値に直すと、0.38900463　となります。

シリアル値 "9:20:10"*24=9.336111111　となります。これが勤務時間 9.3時間となります。

シリアル値*24　で何時間か？の数値を求めることができます。

これに時間給を掛けるとその日の勤務時間に対する日給を求めることができます。

勤務時間*24*時給　つまり、=D5*24*C3　とします。

【Office シリーズ】時間・時刻の関数の使い 問題 解答例

【問題】 出社時間、退社時間、時給からその日の日給を計算しましょう。

セルD5,D7,D8,D9,D10,D11に数式を入れましょう。


【解答例】


D5セルの　勤務時間=退社時間-出社時間　=C5-B5　で求められます。

D7セル　勤務時間から時間を取り出すには　=HOUR(D5)　とします。

D8セル　勤務時間から分を取り出すには　=MINUTE(D5)　とします。

D9セル　勤務時間から分を取り出すには　=SECOND(D5)　とします。

【Office シリーズ】時間・時刻の関数の使い シリアル値

【Office シリーズ】時間・時刻の関数の使い

シリアル値：Excelでは日付や時刻はシリアル値で処理されます。

Windows版のExcelでは 日付は1900年1月1日を「1」、1900年1月2日を「2」・・・と、数値で表します。

時間・時刻は「1日(24時間)」を「1」として、12:00は「0.5」、6:00は「0.25」と小数点以下の数値で表します。

この数値のことをシリアル値と呼びます。

日付が入力されているセルの表示形式を「標準」とすると、日付データが数値で表示されます。この数値がシリアル値と呼ばれるものです。

なお、Windows版は1900日付システム、Macintosh版は190４日付システムとシリアル値の基準が異なっていました。

Excel 2016 for Mac および Excel for Mac 2011 では、1900日付システムが使用されました。

このバージョン以降では、Windows 版 Excel との日付の互換性が保証されています。

Macintosh版では「1904年1月2日」が「１」となっています。 「1904年1月1日」が「0」から始まっています。1904日付システムと呼ばれます。

Excel版では「1900年1月1日」が「1」となっています。1900日付システムと呼ばれています。

よって、Windows版とMacintosh版でExcelファイルをやり取りすると、日付がずれる場合があります。

Excel2007以降ではExcelのオプションで設定します。[ファイル]タブをクリックして、[オプション]を選択します。

[詳細設定]を選択して、「次のブックで計算するとき」で「1904年から計算する」にチェックを入れます。


Excel2003以前では【ツール】→【オプション】で【計算方法】タブの『1904年から計算する』にチェックを入れて、シリアル値の起点を変更します。

時刻データから「時」「分」「秒」を取り出す(シリアル値から数値への変換)　　topへ

　アワー

=HOUR(時刻のシリアル値)　　　：時間を取り出します。0～23(時)の範囲の整数になります。

　ミニット

=MINUTE(時刻のシリアル値)　：分を取り出します。　0～59(分)の範囲の整数になります。

　セカンド

=SECOND(時刻のシリアル値)　：秒を取り出します。　0～59(秒)の範囲の整数になります。

　　　(注)　時間にはシリアル値を使用します。