【初心者向き】エクセルで日付のデータ・形式を変換する方法まとめ
エクセルを使っていると、次のように、日付データを自分好みの形に整えたい場面が出てきます。
- 日付を「2017/4/1」ではなく「2017年04月01日」、「17/4」と表記したい
- 「20170401」というデータを「2017/4/1」という形に変換したい
- 「2017 4 1」というデータが別々のセルに入っているのを「2017/4/1」という形に変換したい
- インターネットバンキングでダウンロードしたCSVデータ内の日付を使いやすい形に変換したい
- 文字列データをシリアル値に変換したい
- シリアル値を文字列データに変換したい
そこで、このページでは、上記のような変換をするために必要な知識を解説していきます。
この記事の目次
日付は、エクセルでどのように扱われているか？
まず、最初に知っておかないといけないのが、日付データが、エクセルでどのように扱われているか？です。
日付データは、エクセルでは（大きく分けて）次の３つの形式で取り扱われています。
- パターン１：シリアル値
-
たとえば、エクセルを開いた直後の状態で、Ａ１セルに「2017/4/1」と入れてみてください。
「2017/4/1」と表示されます。
ここで、A1セルの表示形式を「標準」に変えてみてください。
表示が「42826」に変わってしまいました。
実は、エクセルは日付が入力されると、勝手に、日付に対応した「数値」に変換して管理をしています。
この数値のことを「シリアル値」と言います。
※詳細はこちら → エクセルのシリアル値とは？それを使うメリットは？
- パターン２：シリアル値以外のデータ
-
今度は、別のシートに、あらかじめ、A1セルの表示形式を「文字列」にしたうえで、「2017/4/1」と入れてみてください。
「パターン１」と同様に「2017/4/1」と表示されます。
ここまでは、一見同じ結果のように見えますが、A1セルの表示形式を「標準」に変えてみてもデータは変わりません。
今回のように「あらかじめ表示形式を文字列にして」日付データを入れることで、エクセルは入力された文字をそのまま記録してくれます。
- パターン３：複数セルを使ったデータ
-
「複数」のセルで、日付データを管理する方法もあります。
例えば、「2017/4/1」というデータを表現するために、
- A1セル：2017
- B1セル：4
- C1セル：1
と入力するイメージです。
パターン１～パターン３までの関係を図解すると次のようになります。
| データの形式 | |||
|---|---|---|---|
| シリアル値 | その他 | ||
| 入力に使うセル | 単一セル | パターン１ | パターン２ |
| 複数セル | （該当なし） | パターン３ | |
あなたが日付データの「変換」をしようとするときには、どのパターンからどのパターンへの変換かを意識することで、適切な方法を選択できるようになります。
かなり大ざっぱに分けると、
- 「パターン１」から「パターン１」へは、セルの表示形式を変えることで変換する
- それ以外の場合は、様々な関数を駆使して変換する
ことになります。
日付変換の具体例
それでは、実際に日付変換の具体例を見ていきます。
西暦を和暦に変換したい
A1セルに入っている「2017/4/1」というデータを「平成29年4月1日」というデータに変換します。
- １．元データの形式の確認
-
まず、元々のデータの形式を確認します。
A1セルの書式設定画面から、表示形式を「標準」にします。その結果、表示が「42826」に変われば、A1セルに入っているデータは「パターン１：シリアル値」であることがわかります。
↓セルの表示形式を「標準」に変更
- ２．変換後のデータ形式を決める
-
次に、変換後のデータ形式を決めます。
特に、こだわりがなければ「パターン１：シリアル値」を使うといいでしょう。
- ３．変換をする
-
今回のような「パターン１」から「パターン１」への変換の場合には、セルの書式設定の「表示形式」を変更するだけで、日付形式の変換をすることができます。
A1セルの上で右クリックし「セルの書式設定」→「表示形式」→「日付」から、好きな書式を選びます。
↓
表示形式「日付」にない表示をさせたい
表示形式の「日付」の欄には、「2017/4」というような表示形式は存在していません。
このような表示のさせ方をしたい場合には、どうすればいいでしょうか？
- １．元データの形式の確認
-
まず、元々のデータの形式を確認します。
A1セルの書式設定画面から、表示形式を「標準」にします。その結果、表示が「42826」に変われば、A1セルに入っているデータは「パターン１：シリアル値」であることがわかります。
↓セルの表示形式を「標準」に変更
- ２．変換後のデータ形式を決める
-
次に、変換後のデータ形式を決めます。
今回も、「パターン１：シリアル値」を使うといいでしょう。
- ３．変換をする
-
「ユーザ定義書式」を使って変換を行います。
A1セルの上で右クリックし「セルの書式設定」→「表示形式」→「ユーザー定義」を選択します。
種類欄に「yyyy/m」と入力します。
↓
※「yyyy」＝年を４桁の西暦で表示、「m」＝月を表示 という意味になります。
これで、「年/月」という形式で、日付を表示することができました。
文字列の日付をシリアル値に変換したい
先ほどの２つの例は、元々のデータが「シリアル値」でした。
元々のデータが「文字列」の場合、シリアル値に変換すると、エクセルで便利に扱えることが多いので、シリアル値に変換してみます。
これは、「パターン２」から「パターン１」に変換に該当します。
- １．元データの形式の確認
-
元々のデータが文字列かどうかは、表示形式を「標準」にしても、表示される値が変わりません。
※セルの表示形式を「標準」に変更しても表示が変わらない - ２．変換を行う
-
それでは、先ほどのデータを「シリアル値」を使った日付表記に変換をします。
今回のように、元データの見た目が「日付データとして認識できる形」になっている場合には、value関数を使うだけで簡単にパターン１に変換ができます。
なお、value関数を入れた結果、よくわからない数値が表示される場合には、セルの表示形式を「日付」に変更すると、目的の日付が表示されます。
=value(A1)
年月日が個別に入力されているデータを、通常の日付に変換したい
ジャパンネット銀行のCSVデータのような「年・月・日」が別々のセルに入っているデータを、１つのセルにまとめます。
要は、「パターン３」→「パターン１」に変換したいということです。
この場合は、次のE1セルのように「date関数」を使って変換をかけます。
８桁の日付を、通常の日付に変換したい
「20170401」というような、
- 最初の４桁：年
- 次の２桁：月
- 最後の２桁：日
というデータ（＝「パターン２」のデータ）を、「2017/4/1」という「パターン１」のデータに変換します。
「パターン２」→「パターン１」の変換は、元データの形に応じて、変換する方法は大きく変わりますが、大抵の場合、次の関数を使うと、うまくいきます。
- value関数
- date関数
- left関数
- mid関数
- right関数
- find関数
- concatenate関数
今回の場合であれば、次のように、date関数・left関数・mid関数・right関数の４つを使うとうまくいきます。
date関数は、「年」「月」「日」を指定すると、それに対応する「シリアル値」を計算する関数です。
そこで、date関数に、
| 「年」： | A1セルの左４文字（＝2017） | ← | left(A1,4) |
| 「月」： | A1セルの５文字目から２文字（＝04） | ← | mid(A1,5,2) |
| 「日」： | A1セルの右２文字（＝01） | ← | right(a1,2) |
を入れることで、2017/4/1に対応する「シリアル値」を計算しています。
シリアル値を文字列データに変換したい
エクセルでデータ処理をする場合、日付をシリアル値ではなく、文字列のデータとして管理したい場合があります。
要は「パターン１」から「パターン２」に変換をしたいという場合です。
このような場合には、text関数を使います。
text関数の使い方
text関数は、２つの引数（※）から構成されています。
※引数というのは、関数中のカンマで区切られている部分で、今回の関数だと、１つめの引数が「A1」、２つめの引数が「"YYYY/M/D"」です。
- １つめの引数：文字列形式に変換する「元」のセルを指定します
- ２つめの引数：どのような形式で日付を表示させるかを決めます
２つめの引数の「形式」をどう書くかは、次の方法で簡単にわかります。
たとえば、A1セルと同じような形式を指定したい場合には、A1セルの上で右クリックをして「セルの書式設定」→「表示形式」→「ユーザ定義」を選択してください。
そして、種類欄に表示されている「文字」をそのまま、text関数の２つめの引数として指定することで、A1セルと「同じ」見た目の文字列データを生成することができます。
まとめ
エクセルで日付のデータ・形式を変換したい場合には、変換前・変換後のデータが「パターン１」～「パターン３」までのどれに該当するかを考えましょう。
どのパターンに該当するかがわかると、どのような手法で変換をかければいいか、だいたい、見当が付くようになります。