和暦変換・和暦対応　TEXT関数の拡張　Googleスプレッドの名前付き関数

知らないうちにGoogleスプレッドシートに「名前付き関数」の機能が追加されていました。
メニュー「データ」→「名前付き関数」→「新しい関数の追加」で名前付き関数を追加できます。
GAS使わなくてもできるし便利ですね。

Googleスプレッドシートで使える、和暦表示の関数を作ってみました。
どうぞ自由にお使いください。
よかったら「スキ」を押してもらえると嬉しいです。

名前付き関数 TEXTW

メニュー「データ」→「名前付き関数」→「新しい関数の追加」で
関数名：TEXTW
関数の説明：簡易的な和暦対応TEXT関数
引数のプレースホルダ（2つ追加）：日付、フォーマット

数式のコード

最新版です。更新2024/9/29

=LET(
	 tlist,{"ggge","ge","e"}
	,wlist,LET(
		 y,INT(TEXT(日付,"yyyy"))
		,ymd,INT(TEXT(日付,"yyyymmdd"))
		,wtable,{
			 {18680908,19120730,19261225,19890107,20190501}
			;{"明治","大正","昭和","平成","令和"}
			;{"\M","\T","\S","\H","\R"}
			;{"","","","",""}}
		,REDUCE({y,y,y},SEQUENCE(COLUMNS(wtable)),LAMBDA(res,cur,IF(ymd>=INDEX(wtable,1,cur),{INDEX(wtable,2,cur)&y-ROUNDDOWN(INDEX(wtable,1,cur)/10000-1),INDEX(wtable,3,cur)&y-ROUNDDOWN(INDEX(wtable,1,cur)/10000-1),INDEX(wtable,4,cur)&y-ROUNDDOWN(INDEX(wtable,1,cur)/10000-1)},res)))
	)
	,TEXT(日付,REDUCE(フォーマット,SEQUENCE(COLUMNS(tlist)),LAMBDA(resi,curi,SUBSTITUTE(resi,INDEX(tlist,1,curi),INDEX(wlist,1,curi)))))
)

使い方・仕様

TEXT関数に"ggge"と"ge"と"e"も使えるようにしました。
改良しました。
"勤務管理表ggge年m月　ge.m.d"のような形式も対応できるようになった改良バージョンです。
拡張もしやすくなっていますのでお勧めです。

=TEXTW(DATEVALUE("1989/1/1"),"ggge年m月d日")
→昭和64年1月1日
=TEXTW(DATEVALUE("1989/1/1"),"ge.m.d")
→S64.1.1
=TEXTW(DATEVALUE("1989/1/10"),"e年生まれ")
→1年生まれ

総括

今回の関数のつくり方はいろんなことに応用できると思います。
テンプレートとして用いたいと思います。

学んだポイントは以下の通り

• 2次配列は疑似テーブルとして使用できる。

• 順次処理はLET()の階層を用いて実現する。

• 反復処理はREDUCE()やLAMBDA()を用いて実現する。

• REDUCE()では配列の添え値用にSEQUENCE()で生成すると簡単。

• LAMBDA()で反復処理にした場合、Break（中断）はできないのでIFで分岐して同じ値を上書きする。

仕様覚書メモ

ポイント1：IFSとIFで返値の仕様が異なる。
IFSは{}配列で値を返すことができない。
IFは{}配列で値を返すことができる。

ポイント2：2次配列は要素数を合わせれば「,」と「;」の組み合わせで表記可能。
１次配列をINDEX関数で指定するときは列を使用。
{}配列を受け取るときはINDEX関数で。基準値は1より。
→INDEX({1,2,3,4,5},1,4)→4

2次配列は要素数を合わせれば「,」と「;」の組み合わせで表記可能。

{1,2,3}
→ ROWS=>1 COLUMNS=>3

{{1,2,3};{"a","b","c"};{"d","f","g"};{4,5,6}}
→ ROWS=>4 COLUMNS=>3

=LET(a,{{1,2,3};{2,3,4};{1,1,2};{1,1,2}},rows(a)&"|"&COLUMNS(a))

→ 4|3

ポイント3：REDUCE()やLAMBDA()で使用する変数名は数字で終われない
変数名で数字を使用するときは間で使用する。 i2iなど。

下は旧バージョンですが残しておきます。
簡易的ですので先頭にしか対応していません。"勤務管理表ggge年m月"のようなことはできません。その場合は文字列を分けて使ってください。
旧バージョン2024/9/28

=LET(
	y,INT(TEXT(日付,"yyyy")
	,ymd,INT(TEXT(日付,"yyyymmdd")
	,fmt,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(フォーマット,"ggge",""),"ge",""),"e","")
	,tp,IF(SUBSTITUTE(フォーマット,"ggge","")<>フォーマット,{"明治","大正","昭和","平成","令和"},IF(SUBSTITUTE(フォーマット,"ge","")<>フォーマット,{"M","T","S","H","R"},IF(SUBSTITUTE(フォーマット,"e","")<>フォーマット,{"","","","",""},{})))
	,IF(ROWS(tp)>0, 
		IFS(
		 ymd>20190430,INDEX(tp,1,5)&y-2018&TEXT(日付,fmt)
		,ymd>19890106,INDEX(tp,1,4)&y-1988&TEXT(日付,fmt)
		,ymd>19261224,INDEX(tp,1,3)&y-1925&TEXT(日付,fmt)
		,ymd>19120730,INDEX(tp,1,2)&y-1912&TEXT(日付,fmt)
		,ymd>18680907,INDEX(tp,1,1)&y-1868&TEXT(日付,fmt)
		,true,y&TEXT(日付,fmt))
	,TEXT(日付,フォーマット))
)