【和暦変換】スプレッドシート 数式で変換しよう！ -2 シート内完結式
Googleスプレッドシートでの 和暦・西暦変換ネタの2回目です。
Excelと違って 和暦の扱いがない Googleスプレッドシートにおいて、GAS無しで 数式で和暦変換しよう！ということで、
APIを関数と組み合わせる
変換テーブルを自作して名前付き関数と組み合わせる
この2つの方法を紹介しています。
前回は 1. APIを関数と組み合わせる を検証しました。
前回の記事
今回は2番目の方法、 自作変換テーブル（シート内完結式）で和暦・西暦変換を 検証してみましょう。
和暦変換 式を 自作でやるメリット
前回の記事で APIを使った式がいい感じに完成形になったのに、わざわざ 変換表を自作で用意して式を作るという 手間のかかる方法をなぜ検証するか？
それは、シート内で完結できる自作式にもメリットがあるからです。
外部APIを使った式に比べ 処理速度が速い
ネットワークが不安定（オフライン）な時でも使える
外部のAPIはサービスが終了するリスクがある
和暦変換は変換表が簡単・長期固定なので手間が少ない
こんなメリットがあります。
一番はやはり速度と安定感でしょうか。
import系の関数は、外部とのやりとりになるので式を多用すると重くなります。スプレッドシートを開いた時や更新をかけた時 に、一気に再計算が走り場合によってはエラーを返すことも。
その点 シート内で完結する式であれば、速度や読込失敗といった不安は ほぼ解消されます。
これは image関数でも同様で、大量の image関数を使うと スプレッドシートを立ち上げた時に 一部しか画像が表示されないエラーが発生することがあります。読込先の画像サイズによっては、image関数が10以下と大量ではないのに発生することも。
image関数を使わず セルに画像を貼付ける（自己完結型）にすると、読み込みエラーは結構改善されたりします。
※画像の量、サイズによっては、この方法でもエラーになります。
シート内で処理出来た方が早くて安定してるってことですね。
また、今回は 実務で使う 明治以降の和暦を対象とする前提なので、
明治、大正、昭和、平成、令和
と 5行分のデータで 表示に小さいテーブルです。祝日と違ってめったに変わる（新しい年号が増える）ものでもないですし、さくっと作れて長く使えるのもメリットです。
もちろん 和暦変換式を作る という過程を通じて、様々な関数や機能を 学ぶことも出来ます。お題としてはなかなか面白いと思います。
それでは 作っていきましょう。
途中にQ（お題）もあるので、いけそうな人は是非チャレンジしてください。
2.変換テーブルを自作して名前付き関数と組み合わせる
※前回の記事が1なので、今回は2から始まります。
まずは前提となる変換テーブルを用意しましょう。西暦を和暦に変換するだけでなく、和暦を西暦に変換する際も使用します。
note 内では tableタグが使えないので、コード記述を使いましょう。
こちらはコードの 右上に表示されるコピーボタンでコピー、そのままスプレッドシートの適当なセルを選択しペーストすれば、セル毎に分割された表になります。
開始日 年号 開始年
1868/09/04 明治 1868
1912/07/30 大正 1912
1926/12/26 昭和 1926
1989/01/08 平成 1989
2019/05/01 令和 2019和暦変換表のポイント
まず、和暦は 開始日が年始ではないって点に注意が必要です。直近だと令和は 2019年の5月1日から開始ですね。2019年の4月30日までは平成31年となります。
年で和暦変換する方法を紹介しているサイトもありますが、2019年だから令和元年という単純変換は厳密には間違いです。
もう1つのポイントはテーブルの一番右、3列目を開始年にしている点。
普通に開始日から YEAR関数で取得すればいいんじゃね？って思うかもしれませんが、明治の開始年はエラーとなってしまいます。
これは、スプレッドシートのシリアル値が 1899/12/30 を 0 としていることに起因します。
※シリアル値と暦については、1行数式カレンダーの1回目に触れています。
Excelだとそもそも 1900/1/1 より前の日付は扱えませんが、実は Googleスプレッドシートではシリアル値0（1989/12/31）以前の日付も マイナスのシリアル値として扱いえます。
明治の開始日 1868/09/04 は -11439 というシリアル値になります。
ただ、残念ながらYEAR関数で引数に使う場合は、シリアル値がマイナスだとエラーとなり 年が取得できません。（以前は出来たような気がするんだが・・・）
計算式を工夫すれば やり用はあるんですが、テーブルで年を用意しちゃった方が早いんで 3列目を開始年としています。
名前付き関数は、ちょっとしたテーブルのラッピングにも使える！
さて、以前は この和暦変換テーブルを 適当なシートに用意して参照させる必要がありましたが、今どきは この表をそのまま 最新機能の名前付き関数にしちゃう方法があります。
もちろん大量データのテーブルを 名前付き関数にするのは厳しいですが、タイトル行入れて 6行3列程度なら 名前付き関数にしちゃうのもアリです。
ただの表を どうやって関数にするの？？
って思うかもしれませんが、イコールで この表が返れば良いので 中カッコを使った配列を返す式 に変換すれば良いわけです。
手作業で配列化してもいいんですが、この部分も数式で生成するとスマートですね。（この程度なら手作業でもいいんですが）
="={"&TEXTJOIN(",",TRUE,BYCOL(A1:C6,LAMBDA(col,"{"&TEXTJOIN(";",true,Arrayformula(IF(ISNUMBER(col),col,""""&col&"""")))&"}")))&"}"
今回の 主題から外れるので式の説明は割愛しますが、先にBYCOLで列毎に ;区切りでまとめるんですが、その際 IFと ISNUMBER で 数値は そのまま、文字列（年号）は ダブルクォートで括るという処理をしています。その後、さらに生成された縦配列をTEXTJOINで ,つなぎで横に連結しています。
ISNUMBERは値が数値ならTRUE,それ以外ならFALSEを返す判別式です。
和暦変換表が A1:C6にあった場合、上記の式を適当なセルに入れると
={{"開始日";-11439;4595;9857;32516;43586},{"年号";"明治";"大正";"昭和";"平成";"令和"},{"開始年";1868;1912;1926;1989;2019}}
このような「配列を生成する式」の文字列が生成できます。日付の部分は シリアル値になってますがこれは問題ないです。
出力された結果をコピーし、適当なセルをダブルクリックしてセル内に貼り付けると、きちんと配列式として 和暦変換表を返しているのがわかります。
これをそのまま 名前付き関数にしちゃえば OK。引数は不要なので、プレースホルダーは空欄で問題ないです。関数名は適当に WAREKI_TABLE としておきましょう。
このように =WAREKI_TABLE() と式を入れただけで結果（和暦変換表）が出力できました～。
これを出力しないで、他の関数と組み合わせて 数式内で使うこともできますし、名前付き関数を組み合わせた式をさらに 名前付き関数にすることも出来ちゃいます。
名前付き関数を使って ちょっとしたテーブル（表）をラッピングする小ネタでした。
これで準備は完了。
2a. 変換表を関数と組み合わせて 西暦 → 和暦 変換
この和暦テーブル式を使って、まずは 西暦を 和暦に変換する 式を作ってみましょう。
あとは既存関数とこのテーブルをどう組み合わせるか？なんで、関数スキルのある人ならいけますよね？
ここから、お題形式でいってみましょう。
Q. 和暦変換式を作ってみよう
■条件
・上で作成した 和暦テーブル式 WAREKI_TABLE() を使う
・ 縦方向に 複数の西暦を和暦にスピる式にする
（1つ式を入れたら A列の全ての西暦を和暦変換）
・年号が変わった年、〇〇1年は 元年表記とする
・空白セルがあったら 空白を返す
A列のこんな感じの西暦の日付を、APIを使わず WAREKI_TABLE()を使った 1つの式を C2に入れて 和暦変換してみよう！ ってお題です。
どうでしょう？ いけそうな人はまずは自分で作ってみましょう。
↓↓
ここから回答
↓↓
A. 自作変換テーブルを使った 和暦変換式
解説しながら作っていきましょう。
2a-1. 変換テーブルからの検索で一工夫
たとえば 2018/12/05 という西暦の日付があった時、自作変換テーブルで これをどう和暦 に変換すればよいでしょうか？
ここで使うべきは LOOKUP系関数の 「近似値一致 小」です。
本当は新関数の XLOOKUPを使いたいところですが、今回のケースだと
・年号と開始年の2つの情報を変換テーブルから取得したい （横スピル）
・複数の西暦日付を一括で 和暦変換させたい（縦スピル）
・キーとなる 自変換テーブルの開始年は 昇順である
・XLOOKUPだと 検索列、結果列 で和暦テーブルを2回記述する必要がある
ってことで、縦横両方にスピれて完結に書ける VLOOKUPを使います。
VLOOKUPは 第2引数の検索テーブルのキー列（一番左の列）が 昇順になっている必要がありますが、今回は年号を古い順に並べているのでそのまま使えますね。あとは 第4引数を FALSE指定（完全一致指定）しなければ、自動で 近似値一致小になります。
XLOOKUPでも近似値一致 小は指定できますが、残念ながら Arrayformulaと組み合わせても 縦横 両方にスピルができません。（片方ならOK）
これは以前の記事で検証しています。
同じく XLOOKUP、VLOOKUPの近似値一致 小 についても過去に検証しているので 参考に。
わかりやすくする為、まずは一度出力した表でVLOOKUPを動かしてみましょう。
=ARRAYFORMULA(VLOOKUP(E5, A1:C6,{2,3},TRUE))
※VLOOKUPは デフォルトが TRUE（近似値一致）なので TRUEは省略可
※2列目、3列目 両方の結果を得る為に Arrayformulaと組み合わせる
2018/12/10 以下で 最大の日付（一番近い日付）は 1989/01/08 ですね。近似値一致 小 だと、これが検索でヒットし 横の2列目、3列目を返します。
これで 「平成」という年号と 1989 という 平成の開始年が テーブルから取得できました。
さらに 2018年は 平成何年か？は
2018 - 1989 + 1 = 30
で計算できます。これを式内で組み立てれば、 2018/12/10 を 平成30年12月10日 に変換できそうですね。
まずは検索範囲を 自作した 変換テーブル式 WAREKI＿TABLE() に置き換えましょう。
第4引数のTRUE（近似値一致）指定も省略しちゃいましょう。
=ARRAYFORMULA(VLOOKUP(E5, WAREKI_TABLE(),{2,3}))
このVLOOKUPの結果を利用し加工していくので、これを先に LAMUBD（ラム）って ｘとおきます。
=ARRAYFORMULA(LAMBDA(x, ここに式を作っていく )(VLOOKUP(E5, WAREKI_TABLE(),{2,3})))
2a-2. 年以下の部分は TEXT関数で一気に組み立てる
この x (VLOOKUPの結果)を使って 和暦表記を以下のように組み立てます。
VLOOKUPの結果 ・・・ x ※左側が 年号、右側が 開始年 の配列
年号 ・・・ INDEX(x,,1) ※xの1列目（左側） 「平成」
開始年 ・・・ INDEX(x,,2) ※xの2列目（右側） 「1989」
検索値の年 ・・・ YEAR(E5) 「2018」
和暦にした時の年部分 ・・・ YEAR(E5)-INDEX(x,,2)+1 「30」
残りの部分 ・・・ TEXT(E5,"年M月d日") 「年12月10日」
今回のVLOOKUPの結果は {"平成",1989} (シート上の配列の書き方です） という横並び 2つの配列なんで、要素を個々に 取得するには INDEXが使えます。
また、年以下の部分はまとめてTEXT関数で処理して簡略化しています。
TEXT(E5,"年M月d日")
これで一気に "年〇月〇日" 部分が生成できます。
月は シート関数上は mでもアリなんですが、GASで日時扱う時は、
M ・・・ Month 月
m ・・・ minutes 分
という違いがあるんで、これに準じた使い分けに慣れておくとよいです。
2a-3. 式を組み立て LAMBDAでまとめる
あとは & で繋げて LAMBDA式内にいれちゃえばいいですね。
=ARRAYFORMULA(LAMBDA(x,INDEX(x,,1)&YEAR(E5)-INDEX(x,,2)+1&TEXT(E5,"年M月d日"))(VLOOKUP(E5, WAREKI_TABLE(),{2,3})))
最後に 変換対象の日付のセル E5の部分も LAMBDAっておきましょう。
=LAMBDA(days,ARRAYFORMULA(LAMBDA(x,INDEX(x,,1)&YEAR(days)+1-INDEX(x,,2)&TEXT(days,"年M月d日"))(VLOOKUP(days,WAREKI_TABLE(),{2,3}))))(A2)
最初から 下にスピル式にしてあるんで、対象セルを A2:A6と入れれば 自動で展開されます。
2a-4. IFで 空白と元年を処理
残っている前提条件、空白の処理と元年表記に対応させましょう。
どちらも IFでの分岐処理です。
=LAMBDA(days,ARRAYFORMULA(LAMBDA(x,IF(days="",,INDEX(x,,1)&IF(YEAR(days)=INDEX(x,,2),"元",YEAR(days)+1-INDEX(x,,2))&TEXT(days,"年M月d日")))(VLOOKUP(days,WAREKI_TABLE(),{2,3}))))(A2:A6)
IFが2つ入ってやや煩雑な式になりましたが、まあ良しとしましょう。
こちらを使うと
このように 空白は 空白を返し、西暦を 和暦に変換、1年は元年表記に変換ができました。
西暦 → 和暦変換式、完成です！
完成版：西暦 → 和暦 変換式 （APIなし）
=LAMBDA(days,ARRAYFORMULA(LAMBDA(x,IF(days="",,INDEX(x,,1)&IF(YEAR(days)=INDEX(x,,2),"元",YEAR(days)+1-INDEX(x,,2))&TEXT(days,"年M月d日")))(VLOOKUP(days,WAREKI_TABLE(),{2,3}))))(A2:A6)
これを名前付き関数にしても良いです。
2b. 変換表を関数と組み合わせて 和暦 → 西暦 変換
次に逆パターン、和暦（文字列） を 西暦（日付データ）に変換する式を作ってみましょう。
こちらは対象とする和暦が 文字列なので、前提条件が必要です。以下のようにして、お題としてみましょう。
Q. 和暦 → 西暦変換式を作ってみよう
■条件
・上で作成した 和暦テーブル式 WAREKI_TABLE() を使う
・ 縦方向にスピる式にする
（1つ式を入れたら A列の全ての和暦を西暦変換）
・年号が変わった年は、1年または 元年 どちらにも対応する
・空白セルがあったら 空白を返す
・結果は 日付データ で返す
・和暦内の 数字は 元年以外は 半角数字のみとする
・和暦として 形式が正しいかの判定は考慮不要とする
どうでしょうか？ WAREKI_TABLE()と 既存関数の組み合わせで作れそうでしょうか？
いけそうな人は、まずは自力で挑戦してみましょう！
↓↓
ここから回答
↓↓
A. 自作変換テーブルを使った 西暦変換式
今回も順に説明しながら、式を作っていきましょう。
2b-1. 和暦（文字列）から必要なデータを取り出す （正規表現利用）
文字列を日付にする方法はいくつかありますが、今回は DATE関数に年、月、日 の数値を入れる手法でいきたいと思います。
必要となるデータは以下ですね。
これを個々に取得できれば処理ができそうです。
例： 平成30年10月5日
平成 ・・・ 変換テーブルの検索キーとして使う 年号
30 ・・・ 西暦の 年に変換する際の演算で使う
10 ・・・ 月。そのまま DATE の第2引数に利用
5 ・・・ 日。そのまま DATE の第3引数に利用
最初に思いつくのが、正規表現を扱える関数 REGEXEXTRACT を使って一気にぶっこ抜く方法です。
=REGEXEXTRACT(A2,"^([^\d]{2})(\d+|元)年(\d+)月(\d+)日")
正規表現は 初めての人にはハードル高いですよね。そのうち掘り下げたいとは思ってますが、とりあえず現時点では上のように記述すると 和暦から年号と年、月、日の数字が取得できるんだと理解しておいてください。
REGEXEXTRACT関数の 記事は 「いきなり答える備忘録」さんが参考になります。キャプチャグループについても解説があります。
Arrayformulaでスピる式だし、これをベースにしてもいいんですが、今回は正規表現を使わない 別の方法で取得してみましょう。
2b-2. 和暦（文字列）から必要なデータを取り出す （SPLIT利用）
もう1つの SPLIT関数を使った方法を採用してみましょう。ただ、残念ながらSPLITでは一発ではいきません。
=SPLIT(A2,"年月日")
GoogleスプレッドシートのSPLITは 第3引数（各文字での分割）で TRUEを指定しない場合は、各 区切り文字で分割されます。今回の場合は 年、月、日で分割されていますね。
でも、これだと先頭部分が 平成30 と年号と和暦年が分割できません。
LEFT、MIDで取り出してもいいんですが、ここは年号は先頭から二文字である という規則性を使って、先にREPLACE関数で年号の後ろに区切り文字を入れちゃいましょう。
シート関数の REPLACEは、ちょっと独特で 変換対象の文字列を指定するのではなく、〇文字目から 〇文字分 という位置指定で 文字をリプレースする仕様です。
実は この 〇文字分（第3引数）の箇所を 0 とすることで、特定の位置に文字を挿入する という使い方ができます。
平成30年10月5日
↓
=REPLACE(A2,3,0,"年") ・・・3文字目に 年を挿入
↓
平成年30年10月5日
これで 差し込んだ "年"という文字で、年号と年の部分もSPLITで区切ることができます。
=SPLIT(REPLACE(A2,3,0,"年"),"年月日")
SPLIT、REPLACE どちらも ARRAYFORMULA と組み合わせてスピる関数だし、REGEXEXTRACT の記述に比べて短いんで 良いですね。
2b-3. 分割した配列の個々の要素は INDEXで取得
SPLIT(REPLACE(A2,3,0,"年"),"年月日") の結果配列 の個々の要素は、先ほどの 和暦への変換の時と同じく INDEX関数で取得できます。
この年号 と 和暦の年、そして変換テーブルを使って、西暦の年への変換部分を組み立てていきましょう。
2b-4. 今回のケースはXLOOKUPを使った方がよい
変換テーブルを使った検索で、先ほどはVLOOKUPを使いましたが今回のケースはXLOOKUPを使った方がよいです。
理由としては以下になります。
・年号を検索キーとする 完全一致検索（VLOOKUPだとFALSE指定が必要）
・検索列が 一番左ではない （2列目が検索列、3列目が結果列）
・横のスピルが不要 （結果は3列目のみ）、縦スピルのみでよい
XLOOKUP部分の式は以下のようになります。
XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))
この式の結果である 年号の開始年と、和暦年の数値を使って 西暦の年を算出します。
2b-5. 元年は IFERROR で回避
上のケースでは 検索キーは 平成 なので、結果は 1989 が返ります。
この平成の開始年 1989と 平成30年の 30 を使って西暦算出するには、
1989 + 30 - 1 = 2018
という計算をすればよいですね。 つまり、
XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))+INDEX(x,,2)-1
このようにすれば良いです。
ただ、元年表記の時は、30 の箇所が "元" という文字になってしまいます。この場合はどうするか？
IFERRORで 回避しちゃいましょう。
XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))+IFERROR(INDEX(x,,2)-1,0)
元年の時は 後ろの部分は 元 - 1 という式になり、当然エラーとなります。IFERRORで エラーだった時には 0としてけば、元年の時は 開始年をそのまま使うという計算になります。
これで 年部分を取得出来ました。
2b-6. LAMBDA式を組み立てる
DATE関数の中に入れて、LAMBDA式を完成させましょう。
=LAMBDA(x,w,DATE(
XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))+IFERROR(INDEX(x,,2)-1,0),
INDEX(x,,3),INDEX(x,,4)))
(SPLIT(REPLACE(A2,3,0,"年"),"年月日"),WAREKI_TABLE())
中身としては、
DATE(年, 月, 日)
年・・・ XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))+IFERROR(INDEX(x,,2)-1,0)
月・・・INDEX(x,,3)
日・・・INDEX(x,,4)
x ・・・ SPLIT(REPLACE(A2,3,0,"年"),"年月日")
w ・・・ WAREKI_TABLE()
こんな感じです。
あとは、IFで空白処理を加え、Arrayformulaでスピらせれば OK。
完成版：和暦 → 西暦 変換式 （APIなし）
=LAMBDA(days,ARRAYFORMULA(LAMBDA(x,w,IF(days="",,
DATE(XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))+IFERROR(INDEX(x,,2)-1,0),
INDEX(x,,3),INDEX(x,,4))))
(SPLIT(REPLACE(days,3,0,"年"),"年月日"),WAREKI_TABLE())))(A2:A7)
APIを使わない 和暦 → 西暦変換式 完成です。
和暦変換 まとめ
まとめです。
和暦変換テーブル式 （名前付き関数で WAREK_TABLE() と登録）
={{"開始日";-11439;4595;9857;32516;43586},{"年号";"明治";"大正";"昭和";"平成";"令和"},{"開始年";1868;1912;1926;1989;2019}}
西暦 → 和暦 変換式
=LAMBDA(days,ARRAYFORMULA(LAMBDA(x,IF(days="",,
INDEX(x,,1)&IF(YEAR(days)=INDEX(x,,2),"元",
YEAR(days)+1-INDEX(x,,2))
&TEXT(days,"年M月d日")))
(VLOOKUP(days,WAREKI_TABLE(),{2,3}))))(A2:A6)
和暦 → 西暦 変換式
=LAMBDA(days,ARRAYFORMULA(LAMBDA(x,w,IF(days="",,
DATE(XLOOKUP(INDEX(x,,1),INDEX(w,,2),INDEX(w,,3))
+IFERROR(INDEX(x,,2)-1,0),
INDEX(x,,3),INDEX(x,,4))))
(SPLIT(REPLACE(days,3,0,"年"),"年月日"),WAREKI_TABLE())))(A2:A7)
2週にわたって GASを使わず 数式で 和暦⇔西暦変換を実現する方法として、
APIを関数と組み合わせる
変換テーブルを自作して名前付き関数と組み合わせる
この2つを紹介してきました。
さすがに 和暦 → 西暦 変換は、漢数字にも対応できる API式にはかないませんが、自作変換テーブルを使った式もなかなか良い出来になったんじゃないでしょうか。（自画自賛ｗ）
APIを使った式の作り方や、LAMBDA関数、名前付き関数、Arrayformula、XLOOKUP、正規表現、SPLITなどなど、様々なシート関数や機能を組みわせる 学びの多い お題 だったかなと思います。
和暦は別に使わないし、興味ないよって方にも 今回のお題はおススメです。是非読んだだけではなく、実践してみてください。
次回は GAS不要！？ チェックボックスでビンゴゲームをつくる
チェックボックスと反復計算による乱数固定の応用、ビンゴゲーム作成の記事を予告してたんで、次回こそはやろうかと思います。（ようやく）