SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

最新イベントはこちら!

Data Tech 2024

2024年11月21日(木)オンライン開催

EnterpriseZine Day Special

2024年10月16日(火)オンライン開催

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

60秒でできる!Microsoft Excel Tips集

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

Excelのデータ加工Tips(2)

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))

この記事は参考になりましたか?

  • Facebook
  • X
  • Pocket
  • note
60秒でできる!Microsoft Excel Tips集連載記事一覧

もっと読む

この記事の著者

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

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

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/627 2008/09/24 11:00

Job Board

AD

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング