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