- 日付、時間の表示
◆曜日を表示
Excelには表示形式の設定で曜日を表示する機能が用意されています。
| A | B |
1 | 入力値 | 表示 |
2 | 2003/5/5 | Mon |
3 | 2003/5/5 | Monday |
4 | 2003/5/5 | 月 |
5 | 2003/5/5 | 月曜日 |
A列とB列には同じ値(シリアル値)が入力されています。
表示形式の設定だけでB列のように表示するには下記の設定を行います。
「表示形式のユーザー定義」の種類(
T)でそれぞれ
B2:英記省略 |
B3:英記 |
B4:和記省略 |
B5:和記 |
として
OK
和記表示の書式記号はヘルプでも記載されていないので、なかなか気がつきません。
シリアル値の入力や参照だけで曜日の表示ができるので、案外便利な機能です。
▲PAGETOP
Excelが「時間」として認識するシリアル値からは表示形式で
「年、月、日、曜日、時、分、秒」と意外に多くの情報を引き出すことができます。
と言うことは、ひとつのセルにこれらを全て一度に表示することも可能です。
| A | B |
1 | 入力値 | 表示 |
2 | 2001/9/13 9:00 | 2001/09/13(金)09:00 |
3 | | |
1つのセルに日付と時間を入力するだけでセルB2のように曜日まで含めて表示させることができます。
「表示形式のユーザー定義」の種類(
T)で
として
OK
上記のように、ユーザー設定すると表示されます。
ただし、セルへの入力時に、日付と時間の間にスペースを入れないと、認識してくれませんのでご注意を。
▲PAGETOP
和暦表示は組み込み表示形式には一種類だけ用意されていますが、和暦表示はこれだけではありません。
| A | B |
1 | 入力値 | 表示 |
2 | 2001/9/13 | 平成13年9月13日 |
3 | 2001/9/13 | 平13年9月13日 |
4 | 2001/9/13 | H13年9月13日 |
上記のように元号の表示を変更することができます。
「表示形式のユーザー定義」で種類(
T)をそれぞれ
B2:平成13年9月13日 |
※これは組み込みの表示形式で用意されている元号表示と同じものです。 |
B3:平13年9月13日 |
B4:H13年9月13日 |
として
OK
書式記号「g」は和暦元号表示を、また、書式記号「e」は和暦年数表示を指定します。
年数表示の部分で「gee」とすると、例えば「H07」のように常時年数が二桁で表示されます。
▲PAGETOP
例えば、一ヶ月の「勤務時間」や「稼働時間」など24時間を越えるような時間計算をする場面はよくあると思います。
Excelで経過時間の計算をする場合、組み込みの表示形式では24時間以上の時刻の表示ができません。
原因は時間表示の計算がシリアル値で処理されているためで、
24時間を越える部分については自動的に「1日」へ繰り上げてしまいます。
表示形式だけで24時間以上の時刻を表示するためにはそのように設定する必要があります。
| A | B |
1 | 12:00 | |
2 | 13:00 | |
3 | =SUM(A1:A2) | |
上記のような計算を行うと、セルA3には「1:00」と表示されます。
この計算で「25:00」を求めたい場合には以下のように設定します。
「表示形式のユーザー定義」の種類(
T)で
として
OK
上記の設定のように時間の表示を指定する書式記号hを角括弧 ([])で囲むことで
24時間を超える値を表示させることができます。
※ 24時間が切り上げられて余った時間が表示されてしまう現象は時間計算を行った場合に起こります。
例えばセルへ直接「25:00」と入力を行った場合には「25:00:00」のように24時間以上の表示が自動的に選択されます。
【応用1】60分を超える時間を表示させる
【応用2】60秒を超える時間を表示させる
▲PAGETOP
◆年月日の表示桁数をそろえる
「ggge"年"m"月"d"日"」を適用する場合
、例えば以下のように年月日の桁数が異なる場合には桁数をそろえることができません。
| A | B |
1 | 平成13年12月9日 | |
2 | 平成9年9月18日 | |
これでは一覧表として作成した場合、体裁が悪いと言うことでよく相談に取り上げられる問題です。
- 表示形式で対処する
-
|
A |
B |
1 |
平成13年12月09日 |
|
2 |
平成09年09月18日 |
|
上記のように「数値部分を必ず二桁で表示する」ように設定すれば表示を揃えることは可能です。
「表示形式のユーザー定義」の種類(T)で
としてOK
- 関数で対処する
-
「表示形式のユーザー定義」で
「gggee"年"mm"月"dd"日"」とすると、「平成09年09月18日」と表示されます。
年月日の各数値が一桁の場合は「0」を補って必ず二桁数値にそろえられますが、
この「0」も表示しないようなことも要求されるかも知れません。しかしながら、
書式設定で表示する桁数を監視しながらスペースを挿入したりしなかったりという方法は用意されていません。
したがってその場合には関数で対処します。
|
A |
B |
1 |
2001/12/9 |
平成13年12月 9日 |
2 |
1997/9/18 |
平成 9年 9月18日 |
上記のようにA列に入力された日付からB列に目的の日付を求めます。
|
A |
B |
1 |
2001/12/9 |
=TEXT(A1,"ggg"&IF(LEN(TEXT(A1,"e"))=1," e年","e年")&
IF(MONTH(A1)<10," m月","m月")& IF(DAY(A1)<10," d日","d日"))
|
2 |
1997/9/18 |
|
この関数で返される結果は「文字列」になります。
いったん上記関数で文字列としてセルに値を返して、その文字列を該当セルに「値貼り付け」します。
ただし、いったん文字列として貼り付けしてしまうと日付の計算などには使いにくくなってしまうので、
日付のセルはそのまま残しておいて、
表示用のセルにこのような関数を入力すると言うやり方のほうが現実的です。
- 年、月、日表示用のセルに分割し、表示する
-
もともとの西暦表示のセルがA列だとすると、そのあとに3列挿入し(空白のB列、C列、D列が出来ます)、
B列、C列、D列にA列の同じ行のセルへの参照式を入れます。
|
A |
B |
C |
D |
1 |
2001/12/9 |
=A1 |
=A1 |
=A1 |
2 |
1997/9/18 |
=A2 |
=A2 |
=A2 |
書式設定の「表示形式のユーザー定義」で
それぞれ以下のように設定、
さらにそれぞれの列の配置を右揃えにします。
|
A |
B |
C |
D |
1 |
平成 |
13年 |
12月 |
9日 |
2 |
平成 |
9年 |
9月 |
18日 |
このようにセルを分割して表示する場合は次の点が便利です。
- セルの値はシリアル値のままなので、そのまま計算に使用することができる
- フォントの種類による桁のずれが発生しない
▲PAGETOP
◆時刻を午前(午後)○○時○○分と表示
Excel 2000 以前のバージョンでは「午前(午後)○○時○○分」のような日本語時刻表示がサポートされていません。そのため、このような表示を行うには少し工夫が必要です。
【例】
| A | B | C |
1 | 入力値 | 表示 | |
2 | 9:25:30 | 午前09時25分 | |
3 | 15:21:25 | 午後03時21分 | |
表示形式によって上記のような表示を行います。
「表示形式のユーザー定義」を選択し、
書式文字列を下記のように書き換えます。
「正午(12:00)」の表示 | 入力内容 |
午後00時00分 |
午前12時00分 |
正午 |
上記書式記号中の
・の部分で
Ctrl+
Jを押し、
書式記号内で改行コードを挿入して
OK
[<0.5][<=0.5]などでセルの値による表示形式の条件分岐を行い、なおかつ、
改行コードを使うことでAM/PMを次の行へ送ってしまい表示上は見えなくしてしまいます。
しかし、ここまでしても次のような場合には意図したとおりに表示できません。
- セルの高さが一行分以上ある場合
- 改行して隠すつもりのAM/PMの表示がセルの高さがあるために見えてしまう場合があります。
- セルの入力値が日数部分も含む場合
- もとよりこの表示形式の設定は入力されているのが24時間未満のシリアル値であることを
前提にしています。シリアル値は整数部分は1900年1月1日から数えた日数、小数部分は一日未満の
時間を表現している部分です。「0.5という数値と比べてどうか」であるという 条件分岐で
あるために12時間(0.5)以上のシリアル値は全て「午後である」という表示になってしまい
ます。
例えば、セルの入力値が「2003/12/30 09:32:35」であっても、「午後 09時32分」
のような表示になってしまいます。
表示形式で24時間以上のシリアル値を数値的に無視して、なおかつ午前/午後の分岐を
矛盾なく行うことはエクセルの仕様上、不可能です。
このような場合には別のセルで関数により計算を行わせて表示するほうがより確実に午前/午後の分岐を
行うことができます。
【数式例】
=TEXT(IF(HOUR(A1)>12,A1-0.5,A1),IF(HOUR(A1)>12,"午後 ","午前 ")&"hh時mm分")
▲PAGETOP
◆時刻を○○時○○分 午前(午後)と表示(Excel 2002以降)
Excel 2002以降の場合以下のような対処法があります。
- Windowsの設定による方法
Windowsの地域のオプションの設定により「午前/午後」表記の時間表示ができる選択肢が追加されます。
表示形式追加の方法
1 |
Windows の「スタート」>「設定」>「コントロールパネル」>「地域のオプション」で「時刻」>「時刻の形式」の項目を探します。(Windowsのバージョンによって多少差があります)
|
2 |
時刻の形式を「tt h:mm:ss」もしくは「tt hh:mm:ss」に設定します。
|
3 |
上記の設定によってセルの書式設定、表示形式の「日付」の選択肢に以下の項目が追加されます。
「tt h:mm:ss」→「*午後 1:30:55」
「tt hh:mm:ss」→「*午後 01:30:55」
|
これを利用すれば「午前/午後」の表示にできますが、Windows の設定に依存しているため、以下の制限があります。
- 「秒」までの表示
- Excelの「表示形式」でユーザー定義を「tt hh:mm」などのように変更しても、
反映されません。常に「午前 7:00:00」のような秒単位までの固定表示です。
- あくまでWindowsの設定に依存
- Windows の「地域のオプション」の「時刻の形式」が変更された場合には、
(Excel の表示形式を変更していなくても)セルの表示形式が変わります
- 設定したコンピュータでしか有効でない
- 当然のことながら、ほかのコンピュータでそのファイルを開いた場合は、
そのコンピュータのWindowsの「地域のオプション」で設定されている表示形式が適用されます
- ユーザー定義で直接ロケーションを定義
表示形式のユーザー定義でロケーション(地域)定義することで午前/午後の表示設定をすることが出来ます。
「表示形式のユーザー定義」で下記の様な書式記号設定を行います。
[$-411]が日本の時間表記定義になるようです。
表示の際には「am/pm」の部分が「午前/午後」に置き換わります。
上記の設定ですと、「8時30分 午後」のような表示になります。
日時の表記などでも定義があるようですが、体系的に説明された資料を今のところ見つけておりませんので、
発見しだい追加いたします。
▲PAGETOP