Google スプレッドシートの関数だけで和暦変換

作った経緯

　平成が終わり令和が始まりました。
　いま社内でExcelからGoogleスプレッドシートへの移行を進めています。この中、困ったのがスプレッドシートでは和暦表示をしてくれないこと。
　他の記事を参考に、最初はスクリプトによるユーザー関数を作り和暦表示させていたのですが、そのままだとこうなっちゃいます。

　1989年1月7日　→　平成1年1月7日　
　※ほんとは　昭和64年1月7日(昭和最後の日）
　
　また、ユーザー関数ではスプレッドシートの強力な関数ARRAYFORMULAが使えず、各セルにユーザー関数を貼っていくことになります。表示も遅くなる。

　そこで、スプレッドシートに和暦変換用のテーブルを作り、それを参照する形にしてスプレッドシートの関数のみで和暦変換を実現してみました。

※sachiya3さんからのご指摘を受けてVer.２、Ver.３を追加しています。（2020/1/22）

作成手順

１．各元号開始日のDATEVALUE(シリアル値)をキーにした和暦変換用テーブルを作る
２．元号名はVLOOKUPで和暦変換用テーブルを検索して取得する。この時、近似値検索をかける（ここがミソ！）
３．元号年は対象年月日をDATEVALUEに変換させた上で元号開始年を求め、年差で求める。
４．月日はMONTH関数とDAY関数を使って取得する。

実装

１．和暦変換用のテーブルの作成

シリアル値	元号開始年月日	元号漢字	元号A
-11662	1868/1/25	明治	M
4595	1912/7/30	大正	T
9856	1926/12/25	昭和	S
32516	1989/1/8	平成	H
43586	2019/5/1	令和	R

このテーブルに｢GENGO｣という名前付き範囲を付けます。

２．元号名の取得
変換対象の年月日(西暦）がA1セルに入っているとします。

元号漢字の場合　:
VLOOKUP(DATEVALUE(A1）,GENGO,3,TRUE)
元号Ａ(アルファベット)の場合　:
VLOOKUP(DATEVALUE(A1),GENGO,4,TRUE)

第4パラメータ（並べ替え）はTRUEとし近似値検索で元号名を取得します。

３．元号年の取得
対象年月日をシリアル値に変換し、同じく近似値検索で元号開始年月日を取得します。
VLOOKUP(DATEVALUE(A1),GENGO,2,TRUE)
対象年との差を求めて元号年を求めます。
(YEAR(A1)-YEAR(VLOOKUP(DATEVALUE(A1),GENGO,2,TRUE))+1

４．月と日の取得
　月はMONTH(A1)、日はDAY(A1)で取得します。

最終形

1．元号漢字○年○月○日の場合
=VLOOKUP(DATEVALUE(A1),GENGO,3,TRUE)&(YEAR(A1)-YEAR(VLOOKUP(DATEVALUE(A1),GENGO,2,TRUE))+1)&"年"&MONTH(A1)&"月"&DAY(A1)&"日"

２．元号アルファベット○.○.○の場合
=VLOOKUP(DATEVALUE(A1),GENGO,4,TRUE)&(YEAR(A1)-YEAR(VLOOKUP(DATEVALUE(A1),GENGO,2,TRUE))+1)&"."&MONTH(A1)&"."&DAY(A1)

使い方

・そのまま貼り付けて使用できると思います。あとは適当に加工してください。
・当社の場合、元号管理用のスプレッドシートを作り、各シートにIMPORTRAGEで和暦変換テーブルを取込んで参照する形をとっています。改元時も１カ所の修正で済みますし。もっともGOOGLEさんが和暦対応してくれたら一番なんですが。
・西暦がA1セル～A10セルに入っている場合、関数ARRAYFORMULAを使って以下の関数式をB1セルに入れてみてください。この関数式ひとつでB1セル～B10セルに和暦が表示されます。
=ARRAYFORMULA(VLOOKUP(DATEVALUE(A1:A10),GENGO,3,TRUE)&(YEAR(A1:A10)-YEAR(VLOOKUP(DATEVALUE(A1:A10),GENGO,2,TRUE))+1)&"年"&MONTH(A1:A10)&"月"&DAY(A1:A10)&"日")

注意点

・1912/7/30（大正最初の日）以降の対象年月日で使用できます。私を含めて大半の方はこれに該当すると思われます。
・1912/7/29（明治最後の日）以前の対象年月日がある場合、関数エラーになり使えません。sachiya3さんのご指摘通りです。すいません・・・。

　それで以下を追加します。

和暦変換 Ver.２ 簡易版

　1912/7/29以前の対象データがある場合については、以下のようにすれば実装できます。

１．和暦変換テーブル

シリアル値	元号開始年月日	元号漢字	元号A	元号開始西暦年
-12661	1865/5/1	慶応	K	1865
-11662	1868/1/25	明治	M	1868
4595	1912/7/30	大正	T	1912
9856	1926/12/25	昭和	S	1926
32516	1989/1/8	平成	H	1989
43586	2019/5/1	令和	R	2019

テーブルに元号開始西暦年を5列目に追加します。

２．元号の取得
　近似値検索を使用する点は同じ。

３．元号年の取得
　年差を「対象年月日の西暦年　－　元号開始西暦年　＋　１」で求めますが、ここにYEAR関数を使うとエラーになるので次のようにします。
LEFT(A1,4)-VLOOKUP(DATEVALUE(A1),GENGO,5,TRUE)+1
対象年月日の方はLEFT関数で先頭4文字を取り出し、元号開始西暦年は近似値検索のVLOOKUP関数で取得します。

４．月と日の取得
　MONTH関数とDAY関数を使ってしまうと同じくエラーになるので次のようにします。
　月：INDEX(SPLIT(A1,"/"),2)
　日：INDEX(SPLIT(A1,"/"),3)
　つまり、西暦の年月日をSPLIT関数を使って"/"で分割し、２番目の月、３番目の日をINDEX関数で取り出します。　

最終形（Ver.2）

1．元号漢字○年○月○日の場合
=VLOOKUP(DATEVALUE(A1),GENGO,3,TRUE)&(LEFT(A1,4)-VLOOKUP(DATEVALUE(A1),GENGO,5,TRUE)+1)&"年"&INDEX(SPLIT(A1,"/"),2)&"月"&INDEX(SPLIT(A1,"/"),3)&"日"

２．元号アルファベット○.○.○の場合
=VLOOKUP(DATEVALUE(A1),GENGO,4,TRUE)&(LEFT(A1,4)-VLOOKUP(DATEVALUE(A1),GENGO,5,TRUE)+1)&"."&INDEX(SPLIT(A1,"/"),2)&"."&INDEX(SPLIT(A1,"/"),3)

注意点（Ver.2)

・年月の取得にSPLIT関数を使用しているため、西暦の対象年月日が"/"（スラッシュ区切り）でないと使えません。"/"以外の区切り文字であれば、その区切り文字に置換してください。
・ARRAYFORMULA関数が使えません（INDEX関数がうまく評価されない）。個々に関数を貼ってください。
・西暦1873年1月1日（明治6年）以降の対象データは年月日まで正確に変換できます。
・西暦1872年12月31日以前の和暦変換（・・、慶応、～明治5年）には厳密な意味では使えません（理由はコメントで後述します）。

　それでもう一つ追加します。

和暦変換 Ver.３ 厳密版

　1872/12/31（明治5年）以前の対象データがある場合については、以下のようにすれば実装できます。
　西暦1865年（元治2年、慶応元年）を例に説明します。ちょうど改元、閏月もありますので・・。

１．和暦変換テーブル

シリアル値	元号開始年月日	元号漢字	元号A	元号年	元号月	元号日計算用
-12755	1865/1/27	元治	G	2	1	-12755
-12725	1865/2/26	元治	G	2	2	-12725
-12696	1865/3/27	元治	G	2	3	-12696
-12667	1865/4/25	元治	G	2	4	-12667
-12661	1865/5/1	慶応	K	1	4	-12667
-12637	1865/5/25	慶応	K	1	5	-12637
-12608	1865/6/23	慶応	K	1	閏5	-12608
-12578	1865/7/23	慶応	K	1	6	-12578
-12549	1865/8/21	慶応	K	1	7	-12549
-12519	1865/9/20	慶応	K	1	8	-12519
-12489	1865/10/20	慶応	K	1	9	-12489
-12460	1865/11/18	慶応	K	1	10	-12460
-12430	1865/12/18	慶応	K	1	11	-12430
-12400	1866/1/17	慶応	K	1	12	-12400

元号年月単位のテーブルを作成します。月途中で改元が行われるケースがあるため、元号日計算用シリアル値の列を別途持たせます。

２．元号の取得
　Ver.2と同じです。

３．元号年の取得
　年：VLOOKUP(DATEVALUE(A1),Gengo,5,TRUE)

４．月と日の取得
　月：VLOOKUP(DATEVALUE(A1),Gengo,6,TRUE)
　日：DATEVALUE(A1) - VLOOKUP(DATEVALUE(A1),Gengo,7,TRUE)+1

　年月はお馴染みの近似値検索です。日ですが、シリアル値同士であれば引き算ができるので「対象年月日のシリアル値　－　元号日計算用シリアル値　＋１」で求めます。　

最終形（Ver.3）

1．元号漢字○年○月○日の場合
=VLOOKUP(DATEVALUE(A1),GENGO,3,TRUE)&VLOOKUP(DATEVALUE(A1),GENGO,5,TRUE)&"年"&VLOOKUP(DATEVALUE(A1),Gengo,6,TRUE)&"月"&(DATEVALUE(A1) - VLOOKUP(DATEVALUE(A1),Gengo,7,TRUE)+1)&"日"

２．元号アルファベット○.○.○の場合
=VLOOKUP(DATEVALUE(A1),GENGO,4,TRUE)&VLOOKUP(DATEVALUE(A1),GENGO,5,TRUE)&"."&VLOOKUP(DATEVALUE(A1),Gengo,6,TRUE)&"."&(DATEVALUE(A1) - VLOOKUP(DATEVALUE(A1),Gengo,7,TRUE)+1)

注意点（Ver.3)

・ARRAYFORMULA関数が使用できます。
・和暦変換テーブルを作りこめばどんな元号でもＯＫですが、ただ・・・大変です。

最後に

　私の会社で扱っているデータは大正7年(1918)が最古でしてバージョン１で十分に要件を満たすので、sachiya3さんに指摘されるまで気付きませんでした。
　今回、バージョン２、バージョン３を公開します。みなさんのデータの性質に応じて関数を使い分けられてください。
　それでは令和も１年１年頑張っていきましょう！