search
LoginSignup
11

More than 3 years have passed since last update.

posted at

updated at

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

作った経緯

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

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

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

※sachiya3さんからのご指摘を受けてVer.2、Ver.3を追加しています。(2020/1/22)

作成手順

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

実装

1.和暦変換用のテーブルの作成

シリアル値 元号開始年月日 元号漢字 元号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」という名前付き範囲を付けます。

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

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

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

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

4.月と日の取得
 月は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)&"日"

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

使い方

・そのまま貼り付けて使用できると思います。あとは適当に加工してください。
・当社の場合、元号管理用のスプレッドシートを作り、各シートにIMPORTRAGEで和暦変換テーブルを取込んで参照する形をとっています。改元時も1カ所の修正で済みますし。もっとも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.2 簡易版

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

1.和暦変換テーブル

シリアル値 元号開始年月日 元号漢字 元号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列目に追加します。

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

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

4.月と日の取得
 MONTH関数とDAY関数を使ってしまうと同じくエラーになるので次のようにします。
 月:INDEX(SPLIT(A1,"/"),2)
 日:INDEX(SPLIT(A1,"/"),3)
 つまり、西暦の年月日をSPLIT関数を使って"/"で分割し、2番目の月、3番目の日を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)&"日"

2.元号アルファベット○.○.○の場合
=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.3 厳密版

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

1.和暦変換テーブル

シリアル値 元号開始年月日 元号漢字 元号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

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

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

3.元号年の取得
 年:VLOOKUP(DATEVALUE(A1),Gengo,5,TRUE)

4.月と日の取得
 月:VLOOKUP(DATEVALUE(A1),Gengo,6,TRUE)
 日:DATEVALUE(A1) - VLOOKUP(DATEVALUE(A1),Gengo,7,TRUE)+1

 年月はお馴染みの近似値検索です。日ですが、シリアル値同士であれば引き算ができるので「対象年月日のシリアル値 - 元号日計算用シリアル値 +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)&"日"

2.元号アルファベット○.○.○の場合
=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関数が使用できます。
・和暦変換テーブルを作りこめばどんな元号でもOKですが、ただ・・・大変です。

最後に

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
11