今回は、Excelで時刻データを扱うときのテクニックを紹介する。前回紹介した日付データと同様に、時刻データも「ユーザー定義」の表示形式を使って表示をカスタマイズすることが可能だ。時刻データを自由に扱えるように、基本的な操作と応用テクニックをマスターしておこう。
時刻の表示形式
セルに時刻データを入力するときは、「15:43:00」のように、時、分、秒を「:」(コロン)で区切って記述する。すると、入力した内容がそのまま時刻データとして表示される。秒の表示が必要ない場合は、「15:43」のように「秒」の記述を省略して時刻データを入力しても構わない。
「15:43:00」と入力したときの表示 |
「15:43」と入力したときの表示 |
ただし、「時」の記述を省略することはできないので注意すること。たとえば、6分24秒というデータを入力するときに「6:24」と記述してしまうと、6時24分の時刻データとして扱われてしまう。このことは、データを入力したセルを選択し、数式バーを見ると確認できる。1時間未満の場合も、「0:06:24」のように「時」からデータを入力しなければならないことを覚えておこう。
また、時刻データの入力は、12時間制ではなく、24時間制で入力するのが基本となる。AM/PMを指定して12時間制で時刻データを入力する方法もあるが、通常は24時間制で入力するのが基本となる。
時刻データの表示を「セルの書式設定」でカスタマイズすることも可能だ。この場合は分類に「時刻」を指定し、一覧から好きな表示形式を選択すればよい。
時刻データの場合は「分」と「秒」が必ず2桁で表示されるため、位置が揃わなくて困るケースは少ないと思われる。ただし、「時」の表示は1~2桁に変化するので、データを「中央揃え」で並べると、位置が揃わなくなってしまう場合がある。
時刻データの表示も自由にカスタマイズできるように、「ユーザー定義」の指定方法を覚えておくと役に立つだろう。
「ユーザー定義」を使って時刻の表形形式をカスタマイズ
Excelには、時刻の表示に使用できる書式記号として、以下のような書式記号が用意されている。これらの書式記号を使って「ユーザー定義」の表示形式を指定すると、時刻データの表示を自由にカスタマイズできるようになる。
h | 時を表示(0~23) |
hh | 時を必ず2桁で表示(00~23) |
m | 分を表示(0~59) |
mm | 分を必ず2桁で表示(00~59) |
s | 秒を表示(0~59) |
ss | 秒を必ず2桁で表示(00~59) |
AM/PM | 時刻を12時間制(AM/PM)で表示 |
A/P | 時刻を12時間制(A/P)で表示 |
たとえば、時:分を必ず2桁で表示したい場合は、「hh:mm」と表示形式を指定する。前回の連載で紹介した日付データの場合と同様に、「:」などの記号はそのまま記述すればよい。
もちろん、「時」「分」「秒」といった文字を表示することも可能だ。この場合は、表示する文字をダブルクォーテーション(")で囲って記述する。たとえば「AM/PM hh"時"mm"分"ss"秒"」と表示形式を指定すると、「PM 03時45分00秒」のように時刻データを表示できる。
このように、時刻データも「ユーザー定義」の表示形式を使って表示をカスタマイズすることが可能である。日付データと比べると、カスタマイズが必要になる機会は少ないかもしれないが、覚えておくと役に立つだろう。
経過時間の表示
24時間、もしくは60分、60秒を超える時間を表示したい場合もあるだろう。たとえば、アルバイトの勤務時間をExcelで集計し、一週間の勤務時間を関数SUMで合計すると、以下のような結果になってしまう場合がある。
合計が24時間未満のときは正しい計算結果が表示されるが、24時間を超えると計算結果の表示がおかしくなってしまう。これは、時間を「時刻」として表示していることが原因だ。Excelの時刻は0:00~23:59までしかなく、24:00以降のデータは翌日の時刻として扱われる仕組みになっている。たとえば「25:00」というデータは「翌日の01:00」として処理される。
このように24時間を超えるデータを扱うときは、時刻データを経過時間として表示する必要がある。Excelには、経過時間を表示する書式記号として、以下のような書式記号が用意されている。
[h] | 24時間以上の経過時間を表示する場合(0~∞) |
[m] | 60分以上の経過時間を表示する場合(0~∞) |
[s] | 60秒以上の経過時間を表示する場合(0~∞) |
今回の例の場合、勤務時間の合計を表示するセルに「[h]:mm」と表示形式を指定すると、24時間を超えるデータを正しく表示できるようになる。
もちろん、「分」や「秒」を経過時間として表示することも可能だ。たとえば、「2:15:41」という時刻データを「[m]"分"ss"秒"」の表示形式で表示すると、「135分41秒」という表示になる。データを「時刻」ではなく「時間」として扱うときに必要となるテクニックなので、覚えておくとよいだろう。
1/100秒を表示
100m走のタイムなど、1秒以下の時間をExcelで扱いたい場合もあるだろう。この場合も表示形式のカスタマイズが必要だ。たとえば、「0:00:12.89」(12秒89)とセルに入力すると、その表示は「00:12.9」となり、1/10秒までしか表示されなくなってしまう。
これを1/100秒まで表示するには、「mm:ss.00」のように表示形式を指定しなければならない。同様に、「mm:ss.000」と表示形式を指定すると、1/1000秒まで時間を表示できるようになる。
このように、1秒以下の時刻データをExcelで扱うには若干の手間がかかる。さらに、データを修正しにくくなることも欠点となる。先ほど紹介した例の場合、セルの表示は「00:12.89」(0分12秒89)となっているが、数式バーの表示は「0:00:13」(0時0分13秒)であり、1秒以下の数値は表示されていない。
このデータを修正しようとセルをダブルクリックすると、データが勝手に「0:00:13」に変化してしまい、1秒以下の数値が四捨五入されてしまう。よって、かなり使い勝手の悪い環境になってしまう。
そもそも、「時」や「分」が絡まない「○秒○○」という形式のデータは、60進法で処理する必要がないため、あえて「時刻データ」にする必然性はない。「12.89」のように「通常の数値」としてデータを入力した方が後々の処理が簡単であるし、ミスも少なくなる。状況に応じて使い分けるとよいだろう。
時刻データの内部処理について
少し話が長くなってしまったが、最後にExcelが時刻データをどのように処理しているかを解説していこう。
Excelは時刻データも数値データの一種として扱っている。前回の連載では、日付データの「1日」が数値データの「1」に対応する、と解説した。これと同様に、時刻データは「24時間」が数値の「1」に対応する仕組みになっている。つまり、時刻データは小数点以下の数値として処理されることになる。
具体的な例を挙げると、正午(12:00)は「0.5」、6:00は「0.25」、18:00は「0.75」という数値データに相当する。日付データと組み合わせた場合は、1900年1月1日の正午は「1.5」、2016年9月30日の18:00は「42643.75」という数値データになる(詳しくは前回の連載を参照)。
いずれも内部的な処理の問題なので、特に気にしなくても支障なく作業を進められると思う。ただし、時刻データを含む計算を行う場合は注意が必要となる。たとえば、以下の表において、「給与」=(合計時間)×(時給)をそのまま計算すると、正しくない計算結果が表示されてしまう。
これは、当然と言えば当然の結果である。時刻データは24時間が数値の「1」に相当するため、1時間は「1/24」という数値になってしまう。よって、正しく給与を計算するには、「時刻データを24倍した値」に時給を掛け算しなければいけない。
なお、「出勤時刻」と「退社時刻」から勤務時間を計算で求める場合は、(退社時刻)‐(出勤時刻)‐(休憩時間)の計算を行えばよい。ただし、退社時刻が24:00を超えると不具合が生じる可能性がある。深夜の1:30に退社の場合は、「25:30」と時刻データを入力しなければならない。
このように、時刻データを含む計算を行うときは、「Excelが時刻データをどのように処理しているか?」を十分に理解しておく必要がある。給与計算などを行う方は、よく仕組みを検証しておくとよいだろう。