【和暦変換】スプレッドシート 数式で変換しよう！ -1 API式
今年 2022年も 残りわずか、次は 令和5年ですか。
Excelの和暦変換を令和に対応させるべく 更新プログラムをあててたのが つい最近のような感覚でしたが、月日の流れはあっという間ですね。（中年だからそう感じるのか？）
この和暦ってやつは、なかなか厄介ですよね。
Excelでは普通に 表示形式を（「ggge年」って感じで）切り替えることで 令和・平成 といった 和暦表示に切り替えが可能ですが、残念ながら Googleスプレッドシートは 和暦（ローカルの暦）に対応していません。
各国の暦用にアドオンがあっても良さそうなんですが、そういったものもありません。
そもそも和暦使わなきゃいいじゃんってのが、Google様の考えなんでしょう。
無いものは仕方ないので、どうしても和暦が必要って場合は 変換用に数式を自作するしかないです。今回は 和暦⇔西暦 変換ネタを取り上げてみましょう。
1週ずれましたが、なんで チェックボックスネタの間に 和暦変換ネタを入れたか？
これは連続でチェックボックスネタが飽きてきた、もう一つのExcel新関数の記事との兼ね合い という理由に加え、 タイムラインビューの記事が 週間 500PV越えで mir的にはプチバズったからってのがありますｗ
Twitterで インフルエンサーな人が 拾ってくれた影響が大きいんでしょうが、新しいネタで競合が少ないブルーオーシャンだったのか Google検索の"Googleスプレッドシート タイムライン" での検索結果でも、そこそこ上位表示となりまして、mir的には 初の単記事 週間500PV越えとなったわけです。
ちなみに 2位は 現在は PV100以下ですが、タイムスタンプの記事で、これも この前の週くらいまでは 200いってたんでよね。
どっちもタイム がつくってことは、もしや 時間、日時系のネタがウケるのか？？
「乗るしかない このビッグウェーブに」ってことで、 和暦変換ネタを書くことにしました。
とりあえず 検証記事を書いてみたら、意外と和暦変換は関数や API理解ネタとしても良い題材でした。和暦変換を必要としない人にも、関数の勉強になるんでおススメです。
前回の記事（別ネタです）
※今回は 内容が特殊、かつ検証が多いので QA形式の記事ではありません。
Googleスプレッドシートで、どう和暦対応させるか？
Googleスプレッドシートでの和暦変換ネタは ネット上にも色々なやり方が掲載されていますが、GASを使った自作関数を使う方法の紹介が多い印象。
お馴染みの「いつも隣にITのお仕事」さんも GASによる 自作関数を紹介しています。（コードの練習題材っぽいけど）
でも、いまや GAS使わない 自作関数（名前付き関数）も作れますし、この程度の処理で GAS使うのもなーって思っちゃうんですよね。
というわけで mirのnoteでは GAS でやる方法は割愛して、GASを使わないで 和暦 ⇔ 西暦 変換を実現する 以下の2つの方法を紹介します。
APIを関数と組み合わせる
変換テーブルを自作して名前付き関数と組み合わせる
さすがに 元禄とかの 江戸時代の元号を必要とするケースは少ないと思うので、今回の 和暦 ⇔西暦 変換は 明治以降 が前提です。もちろん、どちらも GASなしで出来ます。
しかも多くのサイトの記事が 西暦 → 和暦変換ですが、和暦 → 西暦変換 かつスピル対応についても 検証しています。
この noteを読んで、Googleスプレッドシートでの 和暦変換をマスターせよ！！です。
1. APIを関数と組み合わせる
西暦、和暦変換用に 自分で変換テーブルを用意してもいいんですが、出来れば楽したいんで今回も 他力本願API をまずはあたってみましょうｗ
これは 1行カレンダーの記事、祝日取得でも使った方法です。
"和暦 API" でググってみると、1件目によさげなサイト HuTime さんが見つかりました。
中に URLのサンプルも記載があるので、これをベースにすればいけそうですね。
Googleスプレッドシートで使う場合は、APIと関数の組み合わせでは お馴染みの IMPORTDATA関数を使えばOK。
これをベースに進めていきましょう。
1a. APIを関数と組み合わせて 西暦 → 和暦 変換
まずは 西暦を 和暦に変換する 式を作ってみましょう。
1a-1. 単体セルを対象とした 和暦変換式
=IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&TEXT(A1,"yyyy-MM-dd")&"&ocal=1001.1")
すんなり成功、和暦に変換されました～！
これは仕方ないんですが、出力される和暦は 文字列となります。Googleスプレッドシートでは 和暦表記は 日付とはみなされません。
あと 式を組む上での注意点ですが、サンプルのURLに そのまま A1のセル参照を組み合わせると シリアル値 扱いで エラーになっちゃいます。
日付のシリアル値は、あくまでも ExcelやGoogleスプレッドシート内での 日付のとらえ方なので、日付っぽいフォーマット(見た目)に成形する為に TEXT関数を組み合わせる必要があります。
ちなみに TO_TEXT関数を使う書き方でもOK。(こっちの方がちょっとだけ短い）
=IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&TO_TEXT(A1)&"&ocal=1001.1")
TO_TEXT関数は GASの getDisplayValue みたいな感じで、シート上で表示されたままをテキストとして取得できます。
これは Excelには無い Googleスプレッドシート独自の関数 です。便利ですね。
TO_TEXTの使い方は 「いきなり答える備忘録」さんが参考になります。
単体セルの和暦変換は成功しましたが、複数セルを対象とした場合はどうか？これも検証してみましょう。
1a-2. APIによる 西暦→和暦 式をスピらせる
A列に複数ある 西暦の日付を全て 和暦に変換したい場合、スピル処理出来たら便利ですよね？
残念ながら import〇〇系の関数は Arrayformula がききません。
でも、今は Arrayformulaではない もう一つのスピル手段、 LAMBDA + ヘルパー関数 があります。こっちで 式を組んでみましょう。
=BYROW(A1:A5,LAMBDA(r,IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&TEXT(r,"yyyy-MM-dd")&"&ocal=1001.1")))
縦方向のデータに対して Arrayformulaが使えないスピル処理をする場合は、Googleスプレッドシート新関数の LAMBDA + BYROWが有効です。
ただし 対象の範囲に空白セルが含まれる場合、
空白セル = 0 = 1899/12/30（0をシリアル値とした日付）
と見なされ、該当する和暦 明治32年12月30日 が返ってしまいます。B5セルがこの状態ですね。
A列が空白の時は空白を返すように、処理を分岐させましょう。分岐なので IFを使います。
=LAMBDA(array,BYROW(array,LAMBDA(r,IF(r="",,IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&TEXT(r,"yyyy-MM-dd")&"&ocal=1001.1")))))(A1:A5)
これで 列まるまる指定にも対応できる 和暦変換スピル式になりました！
※出力のカレンダーID指定を サンプルのまま ocal=1001.1 としていますが、明治以降を扱うという前提なので ocal=1002.1 でも良いです。
1a-3. 元年表記にしたい場合
でもうるさい人がいると、「大正1年8月29日」ではなく、開始年は「大正元年8月29日」と元年表記にすべきだ！なんて意見が出るかもしれません。
うっせーわー！ って歌いたくなりますが、「はい、喜んで～」と笑顔で従いましょうｗ 大人ですから。
式が長くなりますが、importdataで 変換後の和暦 に「〇〇1年」があったら 「〇〇元年」に置き換えるというリプレース処理をする関数をかませましょう。
ここで使うのは、正規表現 指定で リプレースが出来る REGEXREPLACE関数です。
mirの 推しカンの一つで、Excelにはない 文字列操作系 最強クラスの関数 REGEXREPLACE 。（レゲックスリプレースって呼んでるけど、発音が正しいかは不明）
カイジではないですが、悪魔的な圧倒的 検索・置換が出来る関数なんですが、「正規表現」の理解が必要なので初学者には ハードルが高く、使い方を誤ると 沼にはまる、多重債権者に陥るというリスクがある関数ｗ （難しいだけでリスクはないです）
正規表現って言葉がしっくりこないんですよね。イメージしにくいと思いますが、ざっくり言ってしまうと Excelのワイルドカードの超上位互換みたいなもんです。
そのうち正規表現関連も記事を書くつもりですが、とりあえず今回は簡単に解説します。正規表現特有のワードが多く登場するので、わからない人は読み飛ばしてください。
検索対象が 〇〇1年 から始まる場合 〇〇元年 に変換する 処理を 正規表現的に表すと、
■対象ワード
先頭から ・・・ ^
数値以外の文字が2つ(年号) ・・・([^\d]{2})
1年 ・・・ 1年
※年号は再利用するので ()で括り キャプチャグループ としている
↓
■変換後
$1元年
※$1で ()で括った部分 1つ目をそのまま引き継げる
となります。Googleスプレッドシートの 関数で扱える正規表現は、残念ながら 先読み・後読み が使えないので 〇〇1年 を丸ごと変換する処理としています。
その際、元号部分をそのまま使いたいのでキャプチャグループを使っています。
A1を対象とした式にすると
=REGEXREPLACE(A1,"^([^\d]{2})1年","$1元年")
こんな感じになります。慣れないと解読できないですよね。とりあえずは、こういうもんだと流してください。
シンプルなケース（A1に和暦文字列が入っている）でテストしてみると、大正1年が 大正元年に変換されており、それ以外のケースはそのまま出力されているのがわかります。
これを 和暦変換スピル式に組み合わせると以下のようになります。
API利用の 和暦変換式(スピル・元年対応）
=LAMBDA(array,BYROW(array,LAMBDA(r,IF(r="",,REGEXREPLACE(IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&TEXT(r,"yyyy-MM-dd")&"&ocal=1001.1"),"^([^\d]{2})1年","$1元年")))))(A:A)
完成です！
※ この時点では これでよいと思ってましたが続きがあります。
和暦 → 西暦が不要でも必ず最後までお読みください。
1b. APIを関数と組み合わせて 和暦 → 西暦 変換
次に和暦（文字列） を 西暦（日付データ）に変換する式を作ってみましょう。
1b-1. 単体セルを対象とした 西暦変換式
入力 IN と出力 OUTが逆になるので、HuTime の URL のカレンダーIDを逆にすればいいですね。和暦は文字列なんで、そのままセル参照で試してみましょう。
■西暦 → 和暦
http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&TEXT(A1,"yyyy-MM-dd")&"&ocal=1001.1
※ ical=101.1 , ocal=1001.1
↓
■和暦 → 西暦
http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&A1&"&ocal=101.1
※ ical=1001.1 , ocal=101.1
=IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&A1&"&ocal=101.1")
これで試してみましょう。
う・・・。こうきたか。
C.E. は current era （紀元）ですかね。年、月、日は正しいし、元年をちゃんとに1年として拾えてるのは素晴らしいですが。。。英語表記に。。
C.E. 2018 October 05
これを日付データにするのは結構面倒です。暦の英語表記は Googleスプレッドシートの言語設定を 日本語 にしている場合は 日付と認識してくれません。だから、
"C.E. " この部分を削除 （SUBSTITUTE関数）
※明治以降なので B.C. 期限前は考慮不要
↓
英語を日本語に翻訳処理 (GOOGLETRANSLATE関数）
↓
日付化 （DATEVALUE関数）
こんな手順での処理が必要となります。
全てを一つの式にすると以下になります。※セルの表示形式を日付とする必要あり。
=DATEVALUE(GOOGLETRANSLATE(SUBSTITUTE(IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&A1&"&ocal=101.1"),"C.E. ",),"en","ja"))
うーん、完成はしましたけど。。
これでもいいんですが IMPORTDATAした上に GOOGLETRANSLATEって外部処理が2つもあるんで重そうな感じ・・・。
別の方法を考えてみましょう。
1b-2. 単体セルを対象とした 西暦変換式 (書式指定）
もう少しスマートな手段がないかなと APIを使わせてもらっている HuTime さんのページの説明を読み込むと、上記の箇所に 日付文字列 が oform で書式指定可能 ってなってますね。これを試してみましょう。
後ろに &oform=yyyy/MM/dd を追加してみました。
=IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&A1&"&ocal=101.1&oform=yyyy/MM/dd")
一発で 求めていた形式に変換されました！
ちゃんと読み込まなずに式作っちゃダメですね・・・。
というわけで、単体セルを対象とした 西暦変換式は
=IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&A1&"&ocal=101.1&oform=yyyy/MM/dd")
これで完成です。
1b-3. APIによる 和暦 → 西暦 式を スピらせる
西暦 → 和暦と同じように BYROWでいこうかと思いましたが、先ほどの読み込み不足を反省し Hu Timeを再度確認してみましょう。
複数の日付を一括処理できるようです。
改行で区切ればよいって記載がありますね。試しに A1セルに複数の和暦を改行したデータを入れてみましょう。
うーん、一番上の日付しか変換されません。
でも、ここで諦めてはダメです。
「あきらめたらそこで試合終了ですよ」
安西先生 の声が聞こえてきます。
じゃあ URLに渡す際にエンコードしてみたらどうか？
まさに こんな時に使う用の ENCODEURL という関数があります。
※これはExcelにもある関数です。
=IMPORTDATA("http://ap.hutime.org/cal/?
method=conv&ical=1001.1&itype=date&ival="&ENCODEURL(A1)&"&ocal=101.1&oform=yyyy/MM/dd")
欲しかった配列が返ってきました！成功です。
これなら TEXJOIN(CHAR(10),TRUE,範囲) で対象セルの文字列（和暦）を改行で繋げたものを生成し、一旦 ENCODEURL でエンコードしてから 渡せばいけそうです。
=LAMBDA(array,IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&ENCODEURL(TEXTJOIN(CHAR(10),true,array))&"&ocal=101.1&oform=yyyy/MM/dd"))(A:A)
おー 成功です。
後から気づきましたが、年・月・日は 漢数字にも対応してるんですね。これは便利～。
ただしTEXTJOINの 第2引数 （空白の処理）は、参照する和暦行の途中に空白があった場合、TRUE（空白を無視）だと結果が上に詰まってしまいズレが発生します。
FALSE にした場合は 途中に空白があっても空白が返ります。
ただし FALSEにした状態で範囲を A:A といったお尻を指定しない形にすると、 大量の空データが飛んでしまい エラーとなります。
対象とするデータは 途中に空白行はない、最終行を指定したくない
→ TEXJOIN(CHAR(10),TRUE,A:A)
対象とするデータは 途中に空白行がある ※最終行 指定は必須
→ TEXJOIN(CHAR(10),FALSE,A1:A10)
こんな感じの使い分けがよいでしょう。
なんにしても、APIへ一回のリクエストで まとめて処理されるんで、BYROWでの繰り返し処理よりも高速ですし、こっちを使うのが正解ですね。
LAMBDA + BYROWでスピらせて完成としていた 西暦 → 和暦の式も、この方式に修正して 最後にまとめ としましょう。
西暦 ⇔ 和暦 変換 API式 まとめ
■完成版：西暦 → 和暦 変換スピル式 (元年表記対応）
=LAMBDA(array,Arrayformula(REGEXREPLACE(IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=101.1&itype=date&ival="&ENCODEURL(TEXTJOIN(CHAR(10),TRUE,TO_TEXT(array)))&"&ocal=1001.1"),"^([^\d]{2})1年","$1元年")))(B:B)
■完成版：和暦 → 西暦 変換スピル式 (漢数字対応）
=LAMBDA(array,IMPORTDATA("http://ap.hutime.org/cal/?method=conv&ical=1001.1&itype=date&ival="&ENCODEURL(TEXTJOIN(CHAR(10),TRUE,array))&"&ocal=101.1&oform=yyyy/MM/dd"))(A:A)
※ HuTime Web API - Calendar Calculation を使用しております
どちらも 変数を LAMBDAで外に出したので、簡単に名前付き関数化できますね。
今回の式は 試行錯誤トレース型にしてみました
今回の記事は、完成形に至るまでの試行錯誤をそのまま載せて見ましたが、読んでみてどうだったでしょうか？
最初から完成形を解説でも良いのにって思いませんでしたか？ 実際、公開前に読み返して、やっぱりシンプルな形にしようかとも思いましたが、途中の無駄な回り道にも学びがあるかなってことで、今回は記事書きながら検証した mirの思考錯誤そのまま を書いてみました。
このAPI使った式は もともと使ってた式じゃなくて、今回 和暦ネタやってみるかってことで、この記事用に作ったってのもあるんですが、
結構無駄なことや遠回りしてるのがわかりましたかｗ
（自分で mirのスプ値 は 53万です。とか言ってたくせにね。）
でも、これって凄く大事なことなんですよね。
だってGoogleスプレッドシート上は いくら失敗しても やり直しがきくんですすから。
たまに QAサイトで スプレッドシートや GAS関連で 回答した時に
「その式（コード）を使えばうまくいくってことでしょうか？」
って返答されることがあります。
「え？回答した式を試しもせず再度聞いてくるの？」
って思うわけです。
どんだけ失敗という手間をかけたくないんだろ。と。
効率を追求することやタイパ重視は悪いこととは思いません。
でも、「やってみる」＆「失敗したら 原因を検証する」、この トライ＆エラー を積み重ねることは、 何かを身に着けようと思ったら 避けては通れないと思うんですよね。
間違えたり、回り道をすることは 決して無駄じゃないと言いたいのです。
君の目が貫いた～ 僕の胸を真っすぐ～ （まちがいさがし がBGMで流れてきそう ｗ）
と、なんか ギャグ漫画のたまにあるシリアス回みたいになってきたんで、この辺で終了しましょう。
さてなかなか良い出来の APIを使った式が完成したんで、これで和暦ネタ終わりでもよくない？って気もしますが、
APIを関数と組み合わせる
変換テーブルを自作して名前付き関数と組み合わせる
最初にこの2つをやるって言っちゃったし・・・、次週 2番目の方法 変換テーブルを自作する和暦変換の検証をやりたいと思います。
別に言っちゃったからやるというだけではなく、ちゃんと2番目の方法も検証する理由 と利点はあるんです。これは、次の記事の冒頭で触れようと思います。
とりあえず 関数の勉強にはなるし、名前付き関数の理解も深まると思いますので、スプ値（スプレッドシートレベルを数値化したもの）を上げたい人は 来週も要チェックや！
■このシリーズの次の記事
いいなと思ったら応援しよう！
