Excelの日付表示の謎&日付の処理で役立つ8つのテクニック。値と書式という2層構造を理解しておこう

Excelで日付を扱うときには、いろいろと注意しなければいけないことがあります。
Excelでの日付の取り扱いについてまとめてみました
IMG 0741

Excelで日付が数字5桁で表示される・・・

Excelで日付を入力しているつもりが、数字5桁、たとえば、41972と表示されている場合はないでしょうか?
これはExcelでは、日付を数値として処理しているからです。

1900年1月1日から数えた日数がその数値となっています。
「41972」と入力して、
スクリーンショット 2014 11 29 8 01 19
試しに、そのセルでCtrl+Shift+3を押してみましょう。
「2014/11/29」と表示が変わります。
スクリーンショット 2014 11 29 8 01 36

これは、Excelが2層構造になっているからです。
表面に見えているのは、【書式】。
その下にあるのが【値】です。

【値】の上に、【書式】というカバーがついていると考えることもできます。
そのカバーを通じて、私たちは目にしているのです。
スクリーンショット 2014 11 29 8 04 42

41972という【値】に、
・日付(○○○○/○○/○○)というカバー(【書式】)をかぶせると、2014/1129
・桁区切りというカバー(【書式】)をかぶせると、41,972
・何もカバーしないと、41972
・%というカバーをかぶせると、417200%
・¥というカバーをかぶせると、¥41,972
・○○○○年○○月○○日というカバーをかぶせると、2014年11月29日
・○○○○年○○月というカバーをかぶせると、2014年11月
・○○月○○日というカバーをかぶせると、11月29日
と同じ【値】でも様々な見せ方ができます。

スクリーンショット 2014 11 29 8 06 19

日付の入力

Excelでは、【書式】ではなく、【値】で入力するのが基本です。
しかし、2014年11月29日が、41972とさっと思い浮かべることは難しいでしょう。

そこで、日付は簡易的に入力できるようになっています。

「11-29」(又は「11/29」)だけでOKです。

ただし、この場合、「年」は、入力したときの「年」になりますので、2014年に「11-29」と入力すれば、2014年11月29日と表されます。
書式は、「○○月○○日」ですが、セルの内容を見ると、入力されたのは、2014年11月29日です。
スクリーンショット 2014 11 29 21 26 28

2015年に「11-29」と入力すれば、2015年11月29日となります。

今日(2014年11月29日)、2013年11月29日と入力したいときは、「2013-11-29」と入力しなければいけません。

日付の処理で役立つExcelテクニック

日付の処理で役立つExcelテクニックで代表的なものをまとめてみました。

(1)日付の書式にするショートカットキー

Ctrl+Shift+3を押すと、書式が日付(○○年○○月○○日)となります。
なお、Ctrl+Shift+1が桁区切り、Ctrl+Shift+^が標準(何も設定されていない)書式です。

(2)ショートカットキー以外の日付書式

Ctrl+1でセルの書式設定を開き、「○○○○/○○/○○」や「○○月○○日」などといった書式を選べます。
西暦ではなく、元号(平成)の設定もここでやりましょう。

スクリーンショット 2014 11 29 21 29 59

(3)曜日を表示させる

セルの書式設定のユーザー定義で、aaaと入力すると、今日(2014年11月29日)の場合、「土」と表示されます。
スクリーンショット 2014 11 29 21 34 17
aaaaなら、『土曜日」です。
yyyy/mm/dd(aaa)だと、2014/11/29(土)と表示されます。

スクリーンショット 2014 11 29 21 34 42

(4)日付から年、月、日を取り出す関数

日付の表示についての関数には、次のようなものがあります。
YEAR 年を取り出す MONTH 月を取り出す DAY  日を取り出す
スクリーンショット 2014 11 29 21 38 15

(5)年、月、日を日付に結合する関数

(4)とは逆に、年、月、日のデータから日付を作ります。
Date(年、月、日)
スクリーンショット 2014 11 29 21 39 40

(6)今日を入力するショートカットキー

Ctrl+;で今日の日付を入力できます。

(7)今日を表示する関数

todayでファイルを操作している日、つまり今日を常に表示します。
ファイルを開けるたびに日付がかわり、かえってミスになる可能性もあるので、注意しましょう。

(8)日付のようで【値】が日付じゃないものを修正する関数

たとえば、「261129」や「20141129」は見た目が日付でも、Excelにとっては日付ではありません。
日付の形式に修正する必要があります。

スクリーンショット 2014 11 29 21 58 51
「20141129」は、MID関数で年、月、日を抜き出し、Dateで組み合わせます。
LEFT、RIGHTを組み合わせる方法もありますが、すべてMIDでやった方がわかりやすいです。
年は20141129の1文字目から4文字、月は20141129の5文字目から2文字、日は、20141129の7文字目から2文字を取り出しています。

「261129」はちょっとやっかいです。
26というのは平成なのですが、26だけではExcelにはわかりません。26に1988を足すと2014、つまり西暦になるのでこれを利用します。

「h261119」は、hを無視してMID関数で取り出しています。

「2014.11.29」もExcel上では日付のようで日付ではありません。
SUBSTITUTEで、「.」を「/]に変えて、念のため、VALUEで数値に変換しています。
VALUEは文字を数値に変える関数です。

「14.11.29」も同様に処理します。

「11 / 29」というのもありました。
11と/と29に間にスペースが入っているのです。
この場合は、=Date(2014、mid(1,2),mid(6,2)で変換しましたが、年はどうしようもなくひとまず2014を入れて、2013の分は別途処理しました。

まとめ

日付の処理は意外と手間がかかることがあるので、システム会社、会計ソフトなどは、きちんとExcelにあわせてエクスポート(変換)できるようにして欲しいです。。
単にExcelに出しただけではその処理に困ります。

【値】と【書式】という考え方は、他の部分でも重要ですので、確認しておきましょう。





【編集後記】
ジュースクレンズ後、1.6kg減った体重。
空けて2日目の今日、78.2kgに戻っていました・・・
やっぱり一時的ですね。。
体重はともかく体の調子はいいので、デトックス効果はあります。

【昨日の1日1新】
※詳細は→「1日1新」

■スポンサードリンク

近くのそば屋
動画の書き起こし
床暖房手続き