Shoeisha Technology Media

EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

Excelのデータ加工Tips(2) 【Excel Tips】Accessなどの日付データをExcelの日付に変換するには?

  2008/09/24 11:00

Accessやその他のデータベースの日付データを、Excelのシリアル値に変換する方法を紹介します。

概要

 Accessやデータベースの日付データは、Excelで扱いたい表示形式と違う場合も多いですよね。例えば「2008年8月2日」の場合、「20080802」または「080802」と入力されている場合が多いです。このデータを一つ一つについて、年月日を区切るためにスラッシュ(/)を挿入するのは大変な作業になります。

 そこで、セルの書式設定で表示形式を「日付」にしてみるとどうなるでしょうか。残念ながら、この場合は表示が「########」となってしまい、思うような結果は得られません。

[書式]-[セル]を選択
[書式]-[セル]を選択
↓
[セルの書式設定]ダイアログボックスで、表示形式を「日付」に変更すると...
[セルの書式設定]ダイアログボックスで、表示形式を「日付」に変更すると...
↓
「########」と表示される
「########」と表示される

 では、20080802と入力された値の表示形式を変更しても思うような表示が得られないのはなぜでしょうか。これは、Excelでの日付は1900年1月1日が「1」ではじまるシリアル値で管理されていることによります。

 2008年8月2日をExcelのシリアル値で表すと39662となります。よって、数値の指定によって2008年8月2日という表示を得たい場合は、39662を入力しなければなりません。

 ところで、20080802をExcelの日付のシリアル値として想定すると、1年を365.2422日として計算した場合、西暦56879年4月28日になります。Excelでの日付は9999年12月31日までしか扱うことができないので、その数値を日付に表示形式を変更しても「######」表示になるのです。

 この問題を解決するには、文字列操作関数と日付関数を使って、正しいシリアル値を取得する必要があります。ここでは、文字列操作関数であるLEFT、MID、RIGHTを利用して年、月、日の値を取得し、DATE関数を使って取得した対応するシリアル値を取得します。

 20080802の場合、最初の4桁は年、次の2桁が月、最後の2桁を日として年月日を抜き出します。年はLEFT関数、月はMID関数、日はRIGHT関数を利用し、それぞれDATE関数の「年」「月」「日」に代入します。

それぞれの関数の数式と説明
DATE関数 =DATE(年,月,日) 指定した日付を表すシリアル値を返します。
LEFT関数 =LEFT(文字列,文字数) 文字列の先頭から指定した数の文字を返します。全角、半角問わず、1文字を1として処理します。
MID関数 =MID(文字列,開始位置,文字数) 文字列の指定した位置から、指定された数の文字を返します。全角、半角問わず、1文字を1として処理します。
RIGHT関数 =RIGHT(文字列,文字数) 文字列の末尾から指定した数の文字を返します。全角、半角問わず、1文字を1として処理します。

操作手順

 A1からA3までの3パターンの日付を、それぞれ右のセルに求めます。

  A B
1 20080802  
2 080802  
3 80802  

B1セルの場合

 元の日付が「20080802(YYYYMMDD)」の場合の、Excelの日付データへの変換方法を説明します。

 DATE関数の「年」「月」「日」の引数は、以下のように指定します。

  • 年:A1の最初の4文字を抜き出すので LEFT(A1,4)
  • 月:A1の5文字目から2文字を抜き出すので MID(A1,5,2)
  • 日:A1の最後の2文字を抜き出すので RIGHT(A1,2)
[関数の引数]ダイアログボックス:B1セルの場合
[関数の引数]ダイアログボックス:B1セルの場合

 B1セルの数式は以下の通りです。

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

B2セルの場合

 元の日付データが「080802(YYMMDD)」の場合の、Excelの日付データへの変換方法を説明します。

 DATE関数の「年」「月」「日」の引数には、以下のように指定します。

  • 年:A2の最初の2文字を抜き出し、2000を加算するので LEFT(A2,2)+2000
  • 月:A2の3文字目から2文字を抜き出すので MID(A2,3,2)
  • 日:A2の最後の2文字を抜き出すので RIGHT(A2,2)
[関数の引数]ダイアログボックス:B2セルの場合
[関数の引数]ダイアログボックス:B2セルの場合

 B2セルの数式は以下の通りです。

=DATE(LEFT(A2,2)+2000,MID(A2,3,2),RIGHT(A2,2))

B3セルの場合

 元の日付データが「080802」であるものの、文字列ではなく数値として「80802」と処理されてしまっている場合の、Excelの日付への変換方法は以下の通りです。

 DATE関数の「年」「月」「日」の引数には、以下のように代入します。

  • 年:A3の最初の1文字を抜き出し、2000を加算するので LEFT(A3,1)+2000
  • 月:A3の2文字目から2文字を抜き出すので MID(A3,2,2)
  • 日:A3の最後の2文字を抜き出すので RIGHT(A3,2)
[関数の引数]ダイアログボックス:B3セルの場合
[関数の引数]ダイアログボックス:B3セルの場合

 B3セルの数式は以下の通りです。

=DATE(LEFT(A3,1)+2000,MID(A3,2,2),RIGHT(A3,2))


著者プロフィール

  • 小濱 良恵(コハマ ヨシエ)

    Microsoft MVP for Expression (April 2008 - March 2009) Microsoft Office や Expression Web の書籍やWeb記事を執筆するテクニカルライター。 当初は派遣の仕事が中心で、2002年にMOUS(...

バックナンバー

連載:60秒でできる!Microsoft Excel Tips集

もっと読む

All contents copyright © 2007-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5