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さんに指摘されるまで気付きませんでした。
　今回、バージョン２、バージョン３を公開します。みなさんのデータの性質に応じて関数を使い分けられてください。
　それでは令和も１年１年頑張っていきましょう！