<時刻の計算>

　シリアル値に強くなれば時刻の計算は楽々

エクセルでの時刻の計算は、通常と同じ計算式を設定しても正しい答えを求める事ができません。これはシリアル値が原因なのです。シリアル値とは、日付と時刻を数値で表したもので、エクセルでは日付や時刻をシリアル値という数値として扱い計算を行うのです。

シリアル値は、整数部と小数部に分かれ、整数部は日付を表し、1900年１月１日〜9999年１２月３１日の日付を、1〜2958465の整数で表します。そして、小数部は時刻を表し、１日の０時０分０秒〜翌日の０時０分０秒までを0.0〜1.0として表します。また、エクセルでは、日付の場合、／や−で区切って数値を入力すると、自動的に日付データとして認識され、「：」で区切ったデータを入力すると自動的に時刻のデータとして認識されます。ですから、シリアル値同士ならそのまま数式を設定しても正しく答えを算出する事ができますが、シリアル値と数値が混在したデータでは数値をシリアル値に変換する必要があるのです。

そんな時のために、数値をシリアル値に変換してくれるのが「TIME」関数なのです。更に「TIMEVALUE」関数は時刻を表す文字列をシリアル値に変換してくれます。
また、このような事から、時刻に時間をかけても正しい答えを求められないのは、時刻データのシリアル値に数値をかけているからだという事も理解できるでしょう。つまり、このような場合は、前述とは反対に、時刻データを１時間を表すシリアル値「"1：0：0"」で割る事により、数値に変換すれば、数値をかけても正しく答えを求める事ができるというわけなのです。

それでは、今回は、前回ご紹介した、「FLOOR」「CEILING」関数を利用し、時刻を一定の単位で揃え、「TIME」関数で勤務時間を求めていきましょう。さらに、時刻データをシリアル値から数値に変換して、時給をかけて給与支給額を求めていきましょう。

　

　
今回の関数

日付／時刻関数
 　タイム
　指定した時刻に対応したシリアル値を出す
　
書　式 ＝TIME（時、分、秒）
　エクセルでは日付や時刻はそれぞれ「連続する数値」として扱われている。これをシリアル値という。
　TIME関数は時刻をシリアル値に変更する関数。ここでは休憩時間だけが「30分」といった表記のため、この分数をいったんシリアル値に変換して総勤務時間を求めている。


日付／時刻関数
 　アワー
　シリアル値を「時」に変換する
　
書　式 ＝HOUR（時、分、秒）
　「8:35」といった時刻表示は、エクセルでは連続する数値（シリアル値）として管理されている。このシリアル値から１時間単位の「時間数」だけを抽出するのがHOURという関数。ここでは残業時間の合計の「8:35」から時間部分の「8」時間だけを抽出している


日付／時刻関数
 　ミニッツ
　シリアル値を「分」に変換する
　
書　式 ＝MINUTE（時、分、秒）

　「8:35」といった時刻表示のシリアル値から、「分」数の部分だけを抽出するのがMINUTE関数。ここでは「8:35」という時間からMINUTE関数を使い「35」分という部分だけを抽出している。
また時間を示すシリアル値から「秒」数部分だけを抽出する場合はSECOND関数を使う。


　数値を時刻のシリアル値に変換して勤務時間を求める　　

エクセルでは、数値をコロンで区切って入力すると、自動的に時刻データとして認識される。つまり、8：30と入力すると、時刻データとして扱われるが、30だけでは数値データとしてしか扱われない。その為、数値を利用して時刻の計算を行う時には、時刻データ、つまり、時刻のシリアル値に変換して計算を行わなければ正しい答えを求める事ができない。数値を時刻のシリアル値に変換する関数をTIME関数という。
では、退社−出社−休憩で勤務時間を求めてみよう。

　

「退社−出社−休憩」で勤務時間を求める

@勤務時間を求めるセルFの１１番地を選択、数式の編集ボタンをクリック、A退社のセルEの１１番地をクリック、[−]を入力、出社のセルCの１１番地をクリック、[−]をクリック、関数ボックスからB「TIME」関数を選択する。（一覧に無い場合は、その他の関数を選択、日付/時刻関数からTIME関数を選択）

　

　

C分のシリアル値に変換したいので、時、秒には、「0」と入力、分のボックスをクリック、数値の入った休憩のセルDの１１番地を選択、「OK」ボタンをクリックすると、勤務時間を求める事ができる。
※時刻のシリアル値に変換しない引数には0と入力するのがポイント

　

F11のセルには
　　以下の式が入力されます。
　=E11-C11-TIME(0,D11,0)


分のみ表示されたセルで時刻の計算を行なう


「30分」と表示されたセルの場合は..

セルに８時３０分と入力しても、セルの値を確認すると、自動的に8：30となっているので確認してみよう。しかし、30分と入力しても、文字列の３０分と入力されてしまう。その為、このまま勤務時間の計算式をたてても、文字列を含んでしまう為@エラーが出て正しく答えを求める事ができない。このような時は、表示上は文字列が表示されるようにして実際は数値のみがセルの値となるように表示形式を変更すればよい。


A休憩時間のセルには数値だけを入力。全てを範囲選択して、右クリック、「セルの書式設定」を選択、「表示形式」タブのユーザー定義で、BG/標準の後ろに分と入力。

これでD11のセルに「30」と入力すると自動的に「30分」と表示され、エラー値は表示されなくなる。



時刻を一定単位にそろえて計算する


数値を一定の単位で揃えたいという場合は、前回でもご紹介したように、CEILING関数、FLOOR関数、MROUND関数を利用する。つまり、FLOOR関数を利用すると、定めた一定の単位で時間を切り捨てる事ができ、CEILING関数だと、一定の単位で時間を切り上げる事ができ、時間の単位を揃える事ができる。
ここでは、退社時間を１５分単位で切り捨て揃え、出社時間を１５分単位で切り上げ揃えて、勤務時間を求める方法をご紹介しよう。

　退社時間を１５分単位で切り捨てる



勤務時間を求めるセルHの１１番地を選択、関数貼り付けボタンをクリック、「数学/三角」関数から「FLOOR」関数を選択する。

@数値ボックスには、退社時間のセルEの１１番地を選択、基準値ボックスをクリック。

A関数ボックスから「TIME」関数を選択。

　

　


B１５分単位で揃えたいので、時、秒には0と入力、分のボックスに15と入力。
これで、退社時間を１５分単位で揃える事ができる。

※揃える単位のボックスに揃えたい数値を入力するのがポイント
H11のセルには以下のような式が入力されましたか？
=FLOOR(E11,TIME(0,15,0))

　

　出社が9時以前なら9時に9時を過ぎたら15分単位で切り上げる

　次に出社時間を１５分単位で切り上げたいので、CEILING関数を利用する。しかし、出社時間は９時と決まっているので、それ以前に出社しても通常は９時からしか勤務時間は計算されない。そこで、９時以前は９時と表示させ、９時を過ぎたら１５分単位で切り上げる数式にする為に、条件を付けて算出できるIF関数も利用して数式を設定する。


@休憩時間を引くので数式バーをクリックして「−」を入力、関数ボックスから「TIME」関数を選択。

A分のボックスをクリック、休憩時間のセルDの１１番地を選択、時、秒には0と入力。


B次に出社時間をひくので、数式バーをクリック、「−」を入力、関数の貼り付けダイアログから、「IF」関数を選択する。

C論理式には、出社時間が９時以前の場合という条件を設定するので、出社時間＜＝9：00と入力。9：00は、出社のセルHの７番地を選択し、コピーすることを考えてF4キーで絶対指定をかけておく。
真の場合には、そうであった場合、９：00と入力するので、Hの７番地を絶対指定をかけて選択。偽の場合には、そうでなかった場合、１５分単位で切り上げたいので、関数の貼り付けダイアログから「CEILING」関数をネストする。

D数値ボックスには、出社時間のセルCの１１番地を選択、基準値のボックスをクリック、「TIME」関数をネストする。


E１５分単位で切り上げたいので、分のボックスに15と入力、時、分には0と入力。「OK」ボタンをクリックすると、勤務時間が求められる。

　

H11のセルには以下のような式が入力されましたか？
=FLOOR(E11,TIME(0,15,0))-TIME(0,D11,0)-IF(C11&lt;=$H$7,$H$7,CEILING(C11,TIME(0,15,0)))

　

知ッ得
シリアル値から時刻を取り出す関数は、それぞれ「時」「分」「秒」を取り出すHOUR、MINUTE、SECOUNDがある。またシリアル値から年月日に変換する関数としては、それぞれ「年」「月」「日」部分を出すYEAR、MONTH、DAYがあり、「曜日」を出すにはWEEKDAYを使う。


　残業、通常勤務時間を求める

残業時間は、実際の退社時間から、退社指定時間を引いて求める。また、通常勤務時間は、総勤務時間から、残業時間を引いて求める。

　

@残業のセルFの１１番地を選択、数式の編集ボタンをクリック。

A退社のセルEの１１番地を選択、「−」を入力。

BHの８番地を選択、絶対指定をかけて、確定する。

　


C通常勤務のセルGの１１番地を選択、数式の編集ボタンをクリック。

D総勤務のセルHの１１番地を選択、「−」を入力。

E残業のセルFの１１番地を選択して確定する。
　深夜時間の勤務時間を求める


退社時間が次の日になると、出社時間より値が少なくなる為、引く事ができず、答えを求める事ができない。こんな時もシリアル値を利用して数式を設定すれば、正しく答えを求める事ができる。つまり、２４時間を越えた場合は、２４時間を表すシリアル値である「1」を退社時間にたして、数式を立てればよい。


@勤務時間を求めるセルを選択、IF関数を選択する。

A論理式には、退社時間が出社時間より早かった場合という条件を設定するために、「E4＜C4」と設定、真の場合には、早かった場合、24時間をたすので、24時間を表すシリアル値である「1」を入力、そうでなかった場合、そのままなので、「0」と入力。


B数式バーに「+」と入力、退社時間をたすのでEの4番地をクリック、休憩時間をひくので、「−」を入力、その後、関数ボックスから「TIME」関数をネストする。

C休憩の数値を時刻のシリアル値に変換するため、分に休憩のセルDの4番地を選択、「OK」ボタンをクリックする。

勤務時間が正しく求められる。

　

時刻データに時給をかけて給与を求める

時刻データはシリアル値であるため、そのまま、時刻データに時給をかけても、正しい答えを求める事ができない。正しく答えを求めるには、時刻データを1時間単位の時間数に変換する事が必要。変換するには、1時間を表すシリアル値"1：0：0"で時間データを割る事でできる。
※時給をかけて計算する方法は、この操作のほかに、シリアル値で割らずに、普通に計算を行い、最後に24をかける事でも算出できる。つまり、1時間は1日を24時間で割ったシリアル値として表示されるので、24をかける事により、通常の数値に戻すことにより、正しく求める事ができるというわけである。


@支給額のセルEの5番地を選択。

A勤務時間のセルCの5番地を選択、「"1：0：0"」で割る式をたて、最後に時給のセルDの5番地をかける数式を立てる。「OK」をクリックすると、支給額が正しく求められる。


知ッ得
日付/時刻関数には、大きく２つのタイプがある。日付や時刻をシリアル値に変換するものと、シリアル値から日付や時刻を取り出すものだ。前者としては、日付指定のシリアル値を出すDATEのほか、現在、本日、指定の時刻のシリアル値を出すNOW、TODAY、TIMEがある。


　シリアル値を利用して時間単位で表示させる

前述したように、時刻データを1時間を表すシリアル値で割ることにより、1時間単位の時間数に変換できるということは、つまり、8：30を8.5に変換するという事なので
下記のように、最初から、時刻データを変換しておけば、最後の合計値に時給をかけても正しく、支給額が求められるという事である。

　

@時刻データをそれぞれ、「"1：0：0"」で割る。


A表示形式を標準に変更する

8:30を8.5のように表示できるようになる。
　時、分、秒のデータから時、分、秒を取り出す


1つのセルに入力された時、分、秒を、それぞれ、時のみ取り出すには、「HOUR」関数、分のみは「MINUTE」関数、秒のみは、「SECOND」関数を利用する。


@時間を取り出したいセルGの37番地を選択。

A日付/時刻関数から「HOUR」関数を選択する。シリアル値ボックスに時間だけを取りだしたいセルFの35番地をクリック、「OK」ボタンをクリックすると、時間のみを取り出す事ができる。


B分のみを取り出したい場合は、「MINUTE」関数を選択、同様の操作を行うと、分のみを取り出す事ができる。

　

今回の表示形式

時刻データの合計をSUM関数で求めても、24時間を越えると自動的に24時間を省いたデータで表示されてしまう。正しい数値で合計を求めるには、表示形式を変更しよう。

　

24時間を超えた計算を正しく行うには？

@合計のセルを範囲選択、右クリック、セルの書式設定ダイアログを選択、「表示形式」タブのユーザー定義で時間を表す、「h」を[　]でくくる。これにより、２４時間を越えても２４時間が省かれずに正しく表示される。


20：00を8：00PMと表示させるには？

午前のデータはAM、午後のデータはPMを表示させるようにするには、時刻の表示形式を（@部分）変更する。