【連載】

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

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

8/34

今回は、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が時刻データをどのように処理しているか?」を十分に理解しておく必要がある。給与計算などを行う方は、よく仕組みを検証しておくとよいだろう。

8/34

インデックス

連載目次
第34回 ピボットグラフを活用したデータ分析
第33回 ピボットテーブルの基本的な使い方(2)
第32回 ピボットテーブルの基本的な使い方(1)
第31回 行と列を入れ替えた表の作成
第30回 グラフシート、近似曲線など、覚えておくと役立つグラフ関連機能
第29回 「縦棒」と「折れ線」を組み合わせた複合グラフの作成
第28回 横軸のカスタマイズと「軸の交点」を変更したグラフ
第27回 グラフの縦軸の書式を詳しく指定する
第26回 グラフを自在に編集するための基本テクニック
第25回 コンボボックスを使って選択肢からデータを入力
第24回 上下ボタンを使って数値を手軽に増減させる
第23回 フォームを使ってカード型データベースのようにExcelを使う
第22回 並べ替えに必須の「ふりがな」を自動入力する
第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回 セル範囲を短時間で自由自在に選択する

もっと見る



人気記事

一覧

イチオシ記事

新着記事