エクセルやGoogleスプレッドシートで日付をシリアル化せずに変換するには?

更新情報をお届けします!

無料会員登録していただくと、
会員限定の特別コンテンツ記事を最後まで
読むことができます!
その他、更新情報・イベント情報を
お届けいたします。

シェア

ツイート

LINEで送る

Pocket

ブックマーク

エクセルやGoogleスプレッドシートで日付をシリアル化せずに変換するには?

エクセルやGoogleスプレッドシートで「20160101」などと入力し表示形式を日付に変更すると「57096/06/23」と表示される経験はありませんか?

そこで本記事ではシリアル化してしまう日付を正しく表示する方法について紹介します。

エクセルのシリアル値とは?

シリアル値とは、日付を数値に置き換えたものです。エクセルの日付データは1900円1月1日を『1』としたシリアル値で管理されています。

例えば、

1900年1月1日 → 1(シリアル値)

1900年1月2日 → 2(シリアル値)

1900年1月3日 → 3(シリアル値)

というように数字で管理しています。

 

最近の日付ですと、

2016年6月1日 → 42522(シリアル値)

2016年6月2日 → 42523(シリアル値)

2016年6月3日 → 42524(シリアル値)

2016年6月4日 → 42525(シリアル値)

2016年6月5日 → 42526(シリアル値)

というようになります。

シリアル値のメリット

シリアル値の最大のメリットは、日付の1日前と1日後の計算がとても簡単なところです。シリアル値がない状態で1日前の日付と1日後の日付を計算することは自力で複雑な関数を組む必要があり、とても大変です。エクセルのシリアル値の仕組みを使うことで、エクセルが勝手に全部計算してくれます。

大変な作業は、シリアル値を使って全部エクセルやスプレッドシートに行ってもらいましょう。

エクセルの表示形式は日付を選択しているのにシリアル化する理由

それでは本題に入り、日付がシリアル化していまう時の改善方法についてご説明していきたいと思います。上記で説明した通り、エクセルやGoogleスプレッドシートでは日付に「シリアル値」という番号をつけて計算しています。1990年1月1日を「1」としてそこから番号を振り分けているため…1990年1月2日は「2」というよう表示されます。

なので1990年1月1日から「20160101」たった日付⇒「57096/06/23」といった表示になってしまうのです。

関数を使用してまずは日付にしてみる

関数を利用して日付にする方法を試してみましょう。

代表的な関数は「DATE」

=DATE(2016,1,1)とセルに入力すれば表示は「2016/01/01」となります。連番にするのであれば、先ほどの=DATE(2016,1,1)が入力されているセルの番号に「+1」としていくと次のセルには「2016/01/02」と表示されます。

例えば・・・

=A1+1 のような数式です。もしくはセルに「2016/01/01」と入力していただければ大丈夫なのですが…レポートをとってきた場合に「20160101」となってしまっているところは使えません。

「TEXT」関数が使える!

レポートなどからデータをコピーして貼り付けた場合に、「20160101」となっている場合はありませんか?それを日付にしたい!そんなときに使用できる関数が「TEXT」です。

=TEXT(A1,”0000!/00!/00”)*1と入力して、表示形式を日付にすることで「2016/01/01」と表示させることができます。

ポイントは「!」や「*1」を使用することで、エクセルやGoogleスプレッドシートが日付をシリアル値ではなく数値データとして扱ってくれることです。数値データとして扱うので「0000/00/00」だけだと割り算になってしまうので、「!」や「-」「”」を使用することで割り算をするのを防いでくれます。

ちなみに、TEXT関数を使用するとシリアル値を日付の文字列に変換することができます。セルの書式設定で日付を指定します。

Googleスプレッドシートの場合

エクセルの場合だと上記の

=TEXT(A1,”0000!/00!/00”)*1で大丈夫だったのですが、Googleスプレッドシートの場合はこれでは上手くいきません。

Googleスプレッドシートでは

=TEXT(A1,”0000””/””00””/””0”)*1

もしくは、

=TEXT(A1,”0000-00-00”)*1

とすることで上手くいきます。

上手くいかない場合は…

無理やりですが、文字を結合するといった関数で「MID」関数を使用するやり方もあります。

=(MID(A1,1,4)&”/”&MID(A1,5,2)&”/”&MID(A1,7,2))*1

このようにA1に入力されている「20160101」の1文字目から4つ数字をとってきなさい。といった関数になります。それを&で”/”をくっつけて日付に見せるといったやり方になります。または、一度テキストエディタなどで編集するといったやり方も…少し手間ですが、どうしても変換したい場合には一度試してみて下さい。

シリアル値を理解して便利にエクセル・スプレッドシートを活用しましょう

Excelでは使えますが、Googleスプレッドシートでは使えない関数などもあります。表計算ソフト別の使い方を十分に理解し、参考にしていただければ幸いです。各地でエクセルの使い方のセミナーも開催しているみたいなので、参加してみるのも良いでしょう。

更新情報をお届けします!

無料会員登録していただくと、
会員限定の特別コンテンツ記事を最後まで
読むことができます!
その他、更新情報・イベント情報を
お届けいたします。

シェア

ツイート

LINEで送る

Pocket

ブックマーク

CATEGORY :
ライフハック
tree