![見出し画像](https://assets.st-note.com/production/uploads/images/92400540/rectangle_large_type_2_5a8126d590eaeb28efa51e5574a02179.png?width=800)
【和暦変換】スプレッドシート 数式で変換しよう! -1 API式
今年 2022年も 残りわずか、次は 令和5年ですか。
Excelの和暦変換を令和に対応させるべく 更新プログラムをあててたのが つい最近のような感覚でしたが、月日の流れはあっという間ですね。(中年だからそう感じるのか?)
この和暦ってやつは、なかなか厄介ですよね。
Excelでは普通に 表示形式を(「ggge年」って感じで)切り替えることで 令和・平成 といった 和暦表示に切り替えが可能ですが、残念ながら Googleスプレッドシートは 和暦(ローカルの暦)に対応していません。
各国の暦用にアドオンがあっても良さそうなんですが、そういったものもありません。
そもそも和暦使わなきゃいいじゃんってのが、Google様の考えなんでしょう。
無いものは仕方ないので、どうしても和暦が必要って場合は 変換用に数式を自作するしかないです。今回は 和暦⇔西暦 変換ネタを取り上げてみましょう。
1週ずれましたが、なんで チェックボックスネタの間に 和暦変換ネタを入れたか?
これは連続でチェックボックスネタが飽きてきた、もう一つのExcel新関数の記事との兼ね合い という理由に加え、 タイムラインビューの記事が 週間 500PV越えで mir的にはプチバズったからってのがありますw
![](https://assets.st-note.com/img/1671019324656-84NvbM5Bp3.jpg?width=800)
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 をまずはあたってみましょうw
これは 1行カレンダーの記事、祝日取得でも使った方法です。
"和暦 API" でググってみると、1件目によさげなサイト HuTime さんが見つかりました。
![](https://assets.st-note.com/img/1669900183275-hbq9pjA7pH.jpg?width=800)
中に URLのサンプルも記載があるので、これをベースにすればいけそうですね。
![](https://assets.st-note.com/img/1669900572450-QbGjZa82Qn.jpg?width=800)
Googleスプレッドシートで使う場合は、APIと関数の組み合わせでは お馴染みの IMPORTDATA関数を使えばOK。
これをベースに進めていきましょう。
1a. APIを関数と組み合わせて 西暦 → 和暦 変換
まずは 西暦を 和暦に変換する 式を作ってみましょう。
1a-1. 単体セルを対象とした 和暦変換式
![](https://assets.st-note.com/img/1669901166306-stpUIhkdCg.jpg?width=800)
=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 + ヘルパー関数 があります。こっちで 式を組んでみましょう。
![](https://assets.st-note.com/img/1669902399617-nO6yiv6q9a.jpg?width=800)
=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)
![](https://assets.st-note.com/img/1671001035611-eIgnY2JYkZ.jpg?width=800)
これで 列まるまる指定にも対応できる 和暦変換スピル式になりました!
※出力のカレンダーID指定を サンプルのまま ocal=1001.1 としていますが、明治以降を扱うという前提なので ocal=1002.1 でも良いです。
1a-3. 元年表記にしたい場合
でもうるさい人がいると、「大正1年8月29日」ではなく、開始年は「大正元年8月29日」と元年表記にすべきだ!なんて意見が出るかもしれません。
うっせーわー! って歌いたくなりますが、「はい、喜んで~」と笑顔で従いましょうw 大人ですから。
式が長くなりますが、importdataで 変換後の和暦 に「〇〇1年」があったら 「〇〇元年」に置き換えるというリプレース処理をする関数をかませましょう。
ここで使うのは、正規表現 指定で リプレースが出来る REGEXREPLACE関数です。
mirの 推しカンの一つで、Excelにはない 文字列操作系 最強クラスの関数 REGEXREPLACE 。(レゲックスリプレースって呼んでるけど、発音が正しいかは不明)
カイジではないですが、悪魔的な圧倒的 検索・置換が出来る関数なんですが、「正規表現」の理解が必要なので初学者には ハードルが高く、使い方を誤ると 沼にはまる、多重債権者に陥るというリスクがある関数w (難しいだけでリスクはないです)
正規表現って言葉がしっくりこないんですよね。イメージしにくいと思いますが、ざっくり言ってしまうと Excelのワイルドカードの超上位互換みたいなもんです。
そのうち正規表現関連も記事を書くつもりですが、とりあえず今回は簡単に解説します。正規表現特有のワードが多く登場するので、わからない人は読み飛ばしてください。
検索対象が 〇〇1年 から始まる場合 〇〇元年 に変換する 処理を 正規表現的に表すと、
■対象ワード
先頭から ・・・ ^
数値以外の文字が2つ(年号) ・・・([^\d]{2})
1年 ・・・ 1年
※年号は再利用するので ()で括り キャプチャグループ としている
↓
■変換後
$1元年
※$1で ()で括った部分 1つ目をそのまま引き継げる
となります。Googleスプレッドシートの 関数で扱える正規表現は、残念ながら 先読み・後読み が使えないので 〇〇1年 を丸ごと変換する処理としています。
その際、元号部分をそのまま使いたいのでキャプチャグループを使っています。
A1を対象とした式にすると
=REGEXREPLACE(A1,"^([^\d]{2})1年","$1元年")
こんな感じになります。慣れないと解読できないですよね。とりあえずは、こういうもんだと流してください。
![](https://assets.st-note.com/img/1669946671855-vklBd6P2VD.jpg?width=800)
シンプルなケース(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")
これで試してみましょう。
![](https://assets.st-note.com/img/1669948821842-0Lek7wjWWo.jpg?width=800)
う・・・。こうきたか。
C.E. は current era (紀元)ですかね。年、月、日は正しいし、元年をちゃんとに1年として拾えてるのは素晴らしいですが。。。英語表記に。。
C.E. 2018 October 05
これを日付データにするのは結構面倒です。暦の英語表記は Googleスプレッドシートの言語設定を 日本語 にしている場合は 日付と認識してくれません。だから、
"C.E. " この部分を削除 (SUBSTITUTE関数)
※明治以降なので B.C. 期限前は考慮不要
↓
英語を日本語に翻訳処理 (GOOGLETRANSLATE関数)
↓
日付化 (DATEVALUE関数)
こんな手順での処理が必要となります。
![](https://assets.st-note.com/img/1669953705341-mFqOHr89Y4.jpg?width=800)
全てを一つの式にすると以下になります。※セルの表示形式を日付とする必要あり。
=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. 単体セルを対象とした 西暦変換式 (書式指定)
![](https://assets.st-note.com/img/1669954788332-wQjhZq7EUJ.jpg?width=800)
もう少しスマートな手段がないかなと 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")
![](https://assets.st-note.com/img/1669955114150-jPxoLMyj2O.jpg?width=800)
一発で 求めていた形式に変換されました!
ちゃんと読み込まなずに式作っちゃダメですね・・・。
というわけで、単体セルを対象とした 西暦変換式は
=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を再度確認してみましょう。
![](https://assets.st-note.com/img/1669955771505-V3sCHnhRcd.jpg?width=800)
複数の日付を一括処理できるようです。
改行で区切ればよいって記載がありますね。試しに A1セルに複数の和暦を改行したデータを入れてみましょう。
![](https://assets.st-note.com/img/1669955915795-eC1YFA7Dcw.jpg?width=800)
うーん、一番上の日付しか変換されません。
でも、ここで諦めてはダメです。
「あきらめたらそこで試合終了ですよ」
安西先生 の声が聞こえてきます。
じゃあ 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")
![](https://assets.st-note.com/img/1669956464646-zrfGs1ZLB3.jpg?width=800)
欲しかった配列が返ってきました!成功です。
これなら 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)
![](https://assets.st-note.com/img/1669957635759-8MJThUOjgN.jpg?width=800)
おー 成功です。
後から気づきましたが、年・月・日は 漢数字にも対応してるんですね。これは便利~。
ただしTEXTJOINの 第2引数 (空白の処理)は、参照する和暦行の途中に空白があった場合、TRUE(空白を無視)だと結果が上に詰まってしまいズレが発生します。
![](https://assets.st-note.com/img/1669957862557-Vr6DtiOyHs.jpg)
FALSE にした場合は 途中に空白があっても空白が返ります。
![](https://assets.st-note.com/img/1670055717963-1zDsKtFnLo.jpg?width=800)
ただし FALSEにした状態で範囲を A:A といったお尻を指定しない形にすると、 大量の空データが飛んでしまい エラーとなります。
対象とするデータは 途中に空白行はない、最終行を指定したくない
→ TEXJOIN(CHAR(10),TRUE,A:A)
対象とするデータは 途中に空白行がある ※最終行 指定は必須
→ TEXJOIN(CHAR(10),FALSE,A1:A10)
こんな感じの使い分けがよいでしょう。
なんにしても、APIへ一回のリクエストで まとめて処理されるんで、BYROWでの繰り返し処理よりも高速ですし、こっちを使うのが正解ですね。
LAMBDA + BYROWでスピらせて完成としていた 西暦 → 和暦の式も、この方式に修正して 最後にまとめ としましょう。
西暦 ⇔ 和暦 変換 API式 まとめ
![](https://assets.st-note.com/img/1670056103377-edKbjIbQzC.jpg?width=800)
■完成版:西暦 → 和暦 変換スピル式 (元年表記対応)
=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使った式は もともと使ってた式じゃなくて、今回 和暦ネタやってみるかってことで、この記事用に作ったってのもあるんですが、
結構無駄なことや遠回りしてるのがわかりましたかw
(自分で mirのスプ値 は 53万です。とか言ってたくせにね。)
でも、これって凄く大事なことなんですよね。
だってGoogleスプレッドシート上は いくら失敗しても やり直しがきくんですすから。
たまに QAサイトで スプレッドシートや GAS関連で 回答した時に
「その式(コード)を使えばうまくいくってことでしょうか?」
って返答されることがあります。
「え?回答した式を試しもせず再度聞いてくるの?」
って思うわけです。
どんだけ失敗という手間をかけたくないんだろ。と。
効率を追求することやタイパ重視は悪いこととは思いません。
でも、「やってみる」&「失敗したら 原因を検証する」、この トライ&エラー を積み重ねることは、 何かを身に着けようと思ったら 避けては通れないと思うんですよね。
間違えたり、回り道をすることは 決して無駄じゃないと言いたいのです。
君の目が貫いた~ 僕の胸を真っすぐ~ (まちがいさがし がBGMで流れてきそう w)
と、なんか ギャグ漫画のたまにあるシリアス回みたいになってきたんで、この辺で終了しましょう。
さてなかなか良い出来の APIを使った式が完成したんで、これで和暦ネタ終わりでもよくない?って気もしますが、
APIを関数と組み合わせる
変換テーブルを自作して名前付き関数と組み合わせる
最初にこの2つをやるって言っちゃったし・・・、次週 2番目の方法 変換テーブルを自作する和暦変換の検証をやりたいと思います。
別に言っちゃったからやるというだけではなく、ちゃんと2番目の方法も検証する理由 と利点はあるんです。これは、次の記事の冒頭で触れようと思います。
とりあえず 関数の勉強にはなるし、名前付き関数の理解も深まると思いますので、スプ値(スプレッドシートレベルを数値化したもの)を上げたい人は 来週も要チェックや!
■このシリーズの次の記事
この記事が気に入ったらサポートをしてみませんか?