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

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

 

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

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

1900年1月1日から数えた日数がその数値となっています。
「41972」と入力して、

試しに、そのセルでCtrl＋Shift＋3を押してみましょう。
「2014/11/29」と表示が変わります。

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

【値】の上に、【書式】というカバーがついていると考えることもできます。
そのカバーを通じて、私たちは目にしているのです。

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

 

 

日付の入力

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

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

「11-29」（又は「11/29」）だけでOKです。

ただし、この場合、「年」は、入力したときの「年」になりますので、2014年に「11-29」と入力すれば、2014年11月29日と表されます。
書式は、「○○月○○日」ですが、セルの内容を見ると、入力されたのは、2014年11月29日です。

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でセルの書式設定を開き、「○○○○/○○/○○」や「○○月○○日」などといった書式を選べます。
西暦ではなく、元号（平成）の設定もここでやりましょう。

(3)曜日を表示させる

セルの書式設定のユーザー定義で、aaaと入力すると、今日（2014年11月29日）の場合、「土」と表示されます。

aaaaなら、『土曜日」です。
yyyy/mm/dd（aaa）だと、2014/11/29（土）と表示されます。

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

日付の表示についての関数には、次のようなものがあります。
YEAR　年を取り出す　MONTH　月を取り出す　DAY　　日を取り出す

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

(4)とは逆に、年、月、日のデータから日付を作ります。
Date（年、月、日）

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

Ctrl＋；で今日の日付を入力できます。

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

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

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

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

「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と/と２９に間にスペースが入っているのです。
この場合は、=Date（2014、mid(1,2),mid(6,2)で変換しましたが、年はどうしようもなくひとまず2014を入れて、2013の分は別途処理しました。

 

 

まとめ

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

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

---

---

---

---

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

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

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