【連載】

わずか5分でスキルアップ! Excel熟達Tips

8 時刻の表示をカスタマイズする「ユーザー定義」の表示形式

8/21

今回は、Excelで時刻データを扱うときのテクニックを紹介する。前回紹介した日付データと同様に、時刻データも「ユーザー定義」の表示形式を使って表示をカスタマイズすることが可能だ。時刻データを自由に扱えるように、基本的な操作と応用テクニックをマスターしておこう。

時刻の表示形式

セルに時刻データを入力するときは、「15:43:00」のように、時、分、秒を「:」(コロン)で区切って記述する。すると、入力した内容がそのまま時刻データとして表示される。秒の表示が必要ない場合は、「15:43」のように「秒」の記述を省略して時刻データを入力しても構わない。

「15:43:00」と入力したときの表示

「15:43」と入力したときの表示

ただし、「時」の記述を省略することはできないので注意すること。たとえば、6分24秒というデータを入力するときに「6:24」と記述してしまうと、6時24分の時刻データとして扱われてしまう。このことは、データを入力したセルを選択し、数式バーを見ると確認できる。1時間未満の場合も、「0:06:24」のように「時」からデータを入力しなければならないことを覚えておこう。

「6: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」と表示形式を指定する。前回の連載で紹介した日付データの場合と同様に、「:」などの記号はそのまま記述すればよい。

時:分を必ず2桁で表示する「ユーザー定義」の指定

「hh:mm」の形式で表示された時刻データ

もちろん、「時」「分」「秒」といった文字を表示することも可能だ。この場合は、表示する文字をダブルクォーテーション(")で囲って記述する。たとえば「AM/PM hh"時"mm"分"ss"秒"」と表示形式を指定すると、「PM 03時45分00秒」のように時刻データを表示できる。

文字を含む「ユーザー定義」の指定

「AM/PM hh"時"mm"分"ss"秒"」の形式で表示された時刻データ

このように、時刻データも「ユーザー定義」の表示形式を使って表示をカスタマイズすることが可能である。日付データと比べると、カスタマイズが必要になる機会は少ないかもしれないが、覚えておくと役に立つだろう。

経過時間の表示

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秒までしか表示されなくなってしまう。

「0:00:12.89」(12秒89)と入力したときの表示

これを1/100秒まで表示するには、「mm:ss.00」のように表示形式を指定しなければならない。同様に、「mm:ss.000」と表示形式を指定すると、1/1000秒まで時間を表示できるようになる。

1/100秒まで表示する「ユーザー定義」の指定

「mm:ss.00」の形式で表示された時刻データ

このように、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×(時給)の計算を行った場合

なお、「出勤時刻」と「退社時刻」から勤務時間を計算で求める場合は、(退社時刻)‐(出勤時刻)‐(休憩時間)の計算を行えばよい。ただし、退社時刻が24:00を超えると不具合が生じる可能性がある。深夜の1:30に退社の場合は、「25:30」と時刻データを入力しなければならない。

このように、時刻データを含む計算を行うときは、「Excelが時刻データをどのように処理しているか?」を十分に理解しておく必要がある。給与計算などを行う方は、よく仕組みを検証しておくとよいだろう。

<お知らせ>
ITソリューション検討Information

マイナビニュースのIT Search+では、ITソリューションの検討に役立つ情報を掲載しています。
ここでは、IT Search+の人気記事ベスト3をご紹介。ぜひ、ビジネスにお役立てください。

8/21

インデックス

連載目次
第21回 データバーの書式を自由自在に設定する
第20回 条件付き書式を使いこなす
第19回 数式の利用時に覚えておくと便利な機能
第18回 相対参照と絶対参照を使い分ける
第17回 Webに掲載されているデータの有効活用
第16回 データをグループ化して表示/非表示を自由に切り替える
第15回 集計機能を使った合計の自動計算
第14回 サイズの大きい表を印刷する応用テクニック
第13回 サイズの大きい表の印刷
第12回 “見出し”の固定と画面分割の活用
第11回 VLOOKUP関数の使い方と応用テクニック
第10回 データの前後に「〒」や「様」などの文字を自動付加する
第9回 日付データから年齢や期間を算出する関数DATEDIF
第8回 時刻の表示をカスタマイズする「ユーザー定義」の表示形式
第7回 月日を必ず2桁で表示する「ユーザー定義」の表示形式
第6回 「列の幅」と「行の高さ」をcmで指定
第5回 一覧から項目を選んでデータを入力
第4回 「フォントの指定」と「行の高さ」の関係
第3回 データの一括入力と書式指定の繰り返し
第2回 文字数が異なるデータの両端を揃えて配置
第1回 セル範囲を短時間で自由自在に選択する

もっと見る



人気記事

一覧

イチオシ記事

新着記事