Excel 関数による文字列操作のみで西暦を和暦に変換する方法

Excelの関数だけで西暦を和暦に変換するくだりについて、ずっと以前から一度はまとめてみるべきと思っていたのでまとめておきます。これは結構多用するので知っておくと便利です。

1.Excelの西暦和暦変換に関する背景的な話
2.Excelで"YYYYMMDD"文字列を簡単に（関数のみで）和暦にするには

1.Excelの西暦和暦変換に関する背景的な話

ときどき「○○○という条件の人の氏名と生年月日のリストがほしい」などという要望がある。
このようなときはその情報をもつシステムのデータベースから指定条件のレコードを抽出し、依頼主が見れる形に整えて（Excelファイルに貼りつけるなどして）依頼主へ渡すのが一般的だろう。

ここで、データベースの生年月日データの持ち方が「yyyymmdd」形式のテキスト型の場合は、下図のように単純にmid関数で4桁と2桁と2桁にスラッシュで区切るという方法が考えられる。

ただし、これはテキストを整形しただけに過ぎず、日付型になっているわけではない
ことに注意が必要。しかしここでは依頼主の要望には応えていることになるのでこれでもOKである。

では「生年月日は和暦でお願い！」という依頼だった場合にどうするか？

2.Excelで"YYYYMMDD"文字列を簡単に（関数のみで）和暦にするには

西暦/和暦の変換は、シリアル値を介しての変換であれば、表示形式を変更することで容易にできる。このことはExcelを普段から使うような人は誰もが知っていることだろう。詳しくない人向けに念のため解説。

例えばExcelのセルに「1980/10/10」と日付を打ち込んでEnterを押すと、これは自動的に日付型に変換されたものとなる。ここでいう「日付型に変換」というのは、テキスト文字列がシリアル値に変換されてセルへセットされ、さらに表示形式を「日付型」にするということ。

打ち込んだセルの「セルの書式設定」を見ると下記のように表示形式が「日付」になっていることが確認できる。さらに必要に応じて容易に西暦／和暦を切り替えることができることもわかる。

ちなみにこの状態で「標準」をクリックしてみると、「1980/10/10」に対応するシリアル値「29504」が入力されていることも確認できる。これがシリアル値というものである。

シリアル値についてあらためて説明しておくと、1900年1月1日が基準日となりシリアル値は「1」となる。ここから1日経過することにシリアル値は1ずつ増加していく。例えば1900年1月2日のシリアル値は2となる。

日付はこのように1900年1月1日から何日経過したかというシリアル値でのみ管理されており、前述したように日付を表示する時はこのシリアル値を任意の日付フォーマットに変換して画面に表示する。

シリアル値は「value関数」でも取得することができる。

=value(セル指定)

下図はmid関数を使って無理やり作成した文字列「1980/10/10」を、value関数にかけてシリアル値を取得したものである。

シリアル値を取得したセルにおいて「表示形式」を変更すると、下図のように和暦を得ることができる。

しかし、上記の方法だと「セルの書式設定」⇒「表示形式」をいじらなければならず、マウスを使用した作業が発生してしまう。キーボードから出来る限り手を離したくない人にとって、これは意外と大きな問題である。

そこで、Excelに用意された関数だけで和暦表示を実現する方法。

=text(セル指定,"ge年mm月dd日")

以上。これを以前からきちんとまとめておきたかった。

あわせて読みたい

kojikoji75.hatenablog.com
kojikoji75.hatenablog.com
kojikoji75.hatenablog.com

たった1日で即戦力になるExcelの教科書

Excel VBAのプログラミングのツボとコツがゼッタイにわかる本―最初からそう教えてくれればいいのに!Excel2007/2003対応

メキメキ上達! エクセル関数ワザ100(日経ビジネス人文庫)