エクセルとの付き合いは〇十年のパート事務員が僭越ながら開催しています、エクセル塾です。
前回は、日付時刻の関数③文字列や数値を日付データ（シリアル値）に変更する、でした。
【Excel/初心者】エクセル塾：日付時刻の関数③文字列・数値を日付データ（シリアル値）にするには
今回は「日付」ではなく「時刻」に関する関数について、をやります。
１．おさらい：シリアル値における時刻とは
日付時刻の関数①でやりましたが、
エクセルでは日付時刻を「シリアル値」というデータ形式で扱い、日付は1900/1/1を「1」として、一日たつごとに１を足した数字となる。
時刻は一日24時間を「１」として、少数であらわされる。つまりお昼の12時は半分だから「0.5」、朝の6時は「0.25」、夜の6時は「0.75」、夜中の12時で1になる。つまり日付部分の整数値が1繰り上がる。
要は
1/24が1時間（0.04166666…..)
1/24/60が1分(1時間=1/24を60分で割ると1分 1/1440 → 0.000694444….)
1/24/60/60が1秒(1時間=1/24を60分で割ると1分。1分を60で割ると1秒 1/86400→ 0.000011574….)
っていわれてもなんかむずかしい。きっばりと割り切れないので、時刻の計算では誤差が出そうな予感がしますね。
２．NOW：今の日付と時刻を取得する
NOW関数は現在の日付と時刻を返す関数です。引数は取りません。
=NOW()
と入力します。
TODAY関数と似ていますが、TODAY関数は現在の日付を返すのに対し、NOW関数は現在の日付と時刻を返します。
３．HOUR,MINUTE,SECOND関数：時刻から時・分・秒を取り出す
HOUR、MINUTE、SECOND関数は時刻のデータからそれぞれ時・分・秒を取り出すことができる関数です。
例えば 17:47:04 から
HOUR関数は 17 を
MINUTE関数は 47 を
SECOND関数は 4 を
取り出します。
４．TIME関数：時、分、秒から時刻データ（シリアル値）を生成する
３と逆ですね。時、分、秒から時刻データ(シリアル値)を生成するのがTIME関数です。
ちなみに、秒にすべて突っ込んでも、分にすべて突っ込んでも、繰り上がりもちゃんと計算してくれます。
５．時刻から秒への換算
なんか、計算するのに時刻だと訳が分からなくなるから「秒」という整数の形にして計算したい、というときがあります（私だけ？）
秒への換算方法をいくつかご紹介します。
まず、一番わかりやすい方式。
①時刻1:30:30からHOUR関数で「時」を、MINUTE関数で「分」を、SECOND関数で「秒」を取り出します。（B～D列）
②「時」に3600をかけて秒にします（１時間は60分、1分は60秒なので、60*60=3600）
③「分」に60をかけて秒にします（1分は60秒なので、60をかける）
④「秒」はそのまま
⑤ 秒換算した「時」「分」「秒」を足して（②＋③＋④）で1:30:30を秒にしたものが出ます
※図の例ではかっこを付けていますが =B2*3600+C2*60+D2 のようにかっこがなくても同じ結果となります。
４でやったTIME関数で時刻に戻してみると、ちゃんと1:30:30に戻るので、正しく秒換算ができていることがわかります。
「時」「分」「秒」をセルに分けずに一気に一つの式の中でやってしまってもOKです。
もっと簡単な方法は時間のシリアル値に86,400をかける方法です。
シリアル値の時刻は１日を1として少数で表したものです。
1を何で割っているかというと1日24時間の秒数で割れば、よいということかと。
1日は24時間。1時間が24個ある。
1時間は60分
1分は60秒だから、1時間は60秒が60個で60×60=3600。1時間は3600秒。
1時間が24個で1日だから 3600×24=86,400。
つまり1日は 60×60×24=86,400秒。
なので、かどうかよくわからなくなってきたけど、とにかく時刻のシリアル値に86,400をかけると秒に換算した値が得られる、ということらしい。
時刻のシリアル値 × 86,400 で秒換算した結果が得られる
と覚えておきましょう
６．時刻の差を求める
時刻の差は引き算で出せます。
ただし、マイナスになるとエラーとなります。マイナスの時刻なんてねーよ！と怒られます。
（１）時刻のマイナスの対象法：書式で何とかする
書式で何とかする方法。
D列のタイムとC列のタイムの差を出したい場合、
①まず差を求める
②IF関数で差がマイナスの場合と0およびプラスの場合とに分岐する
③マイナスの場合、ABS関数で差の絶対値（マイナスをとった値）を出して、TEXT関数でhh:mm:ssの書式とする。その時に”▲hh:mm:ss”とし頭にマイナスである▲(－でもよい）を付ける。
④マイナスではない場合、TEXT関数で差のタイムをhh:mm:ssの書式にする
=IF(D4-C4<0,TEXT(ABS(D4-C4),”▲hh:mm:ss”),TEXT(D4-C4,”hh:mm:ss”))
ただし、hh:mm:ssの文字列に変換してしまっているため、差の平均を求めたりなどの計算はできません。
差をシリアル値で計算しておき、計算にはそれを使い、見せる用の文字列変換した列を別に持っておくのもよいと思います。
なお、マイナスの時刻を時刻の書式にしてエラーとなっても、値はしっかり持っているので計算には利用することができます。気にならなければ。。。
（２）時刻のマイナスの対処法：秒換算して計算する
時刻の差を計算する際にマイナスだとエラー表記になり、なんかイヤなので、秒に換算して計算するのもおすすめです。
先ほどの市民マラソン大会でやってみましょう。ちょっと差を大きくしたかったのでID02の方の2022年度の時間を変更しています。
①秒換算の差は (2022年の時間－2021年の時間)×86,400 で出しています。
②「秒」なので、足したり引いたり、平均を出したり、計算には問題なく利用できます。
③ちょっとこれではどのくらいの時間なのかわかりにくい、という場合は、４でやったTIME関数を使って、TIME(0,0,秒) と秒のところに差の秒数を突っ込み、時刻のシリアル値に変換します。
このとき秒がマイナスだとエラーになるのでABS関数で差の秒数を絶対値(マイナスをとった値）にして突っ込みます。
（１）でやったように、TEXT関数を利用して差がマイナスの時は”▲hh:mm:ss”にする、という対応も付け加えます。
差（秒換算）の式は
=(D4-C4)*60*60*24
または
=(D4-C4)*86400
TEXT関数とTIME関数を利用して差（秒）を時刻表記にするには
=IF(E4<0,TEXT(TIME(0,0,ABS(E4)),”▲hh:mm:ss”),TEXT(TIME(0,0,E4),”hh:mm:ss”))
ちょっと長くなってしまいますね。
日付・時刻に関する関数シリーズはこれでおしまいです。
お読みいただきありがとうございました。
日付時刻の関数シリーズ
【Excel/初心者】エクセル塾：日付時刻の関数①
【Excel/初心者】エクセル塾：日付時刻の関数②曜日を表示する
【Excel/初心者】エクセル塾：日付時刻の関数③文字列・数値を日付データ（シリアル値）にするには
エクセルをマスターしたい方にお勧めの書籍！
コメント