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