テンプレート文字列を扱える数式を作成する。
以下の数式をFormatという名前で定義する。
=LAMBDA(template, values,
REDUCE(
template,
SEQUENCE(LEN(template) - LEN(SUBSTITUTE(template, "$", ""))),
LAMBDA(acc, i,
SUBSTITUTE(acc, "$" & i, INDEX(TOCOL(values, 1), i, 1), 1)
)
)
)
使用例
A1 | B1
世界 | 私
=Format("こんにちは! $1 と $2",A1:B1)
結果
"こんにちは! 世界と私"
EXCELで数字のみ半角にする数式
=REDUCE(A1,SEQUENCE(11),LAMBDA(x,y,SUBSTITUTE(x,JIS(y-1),y-1)))
文字列を右から検索できるようになりました｡
EXCEL関数で右から検索するのは大変でしたが､
参考:
新しいSEQUENCE関数を利用することで少し楽になりました｡(簡単とまで行きませんが)
■例■
A1セル: abcde/fghijk/m から最後の"/"を探す
B1セル数式: =LEN(A1)-XMATCH("/",MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1))+1
B1セル値: 13
■解説■
SEQUENCE(LEN(A1),,LEN(A1),-1),1) でA1の文字数から1ずつ減っていく数列を作ります｡
14,13,12,....2,1
これにMID関数を使うことで MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1))
逆順に文字が入った配列ができます｡
m,/,k,j,i,h,g,f,/,e,d,c,b,a
"/"を XMATCHを使って探すと､最後から何文字目かが分かるので､文字列全体の長さから引いて1を足すと､最後の"/"の位置がわかる｡
数値以外の値もランク付け
(対応バージョン:2021,WEB版,365版)
値の順位付けを行う関数にRANK関数がありますが､これは数値同士の比較にしか使えません｡次の式で､任意の順位付けを行うことができます｡
=XMATCH([何番目か知りたい値], SORT( [ランク付けに使う範囲] ))
使用例(結果):
使用例(結果):
解説:
SORT関数で並べ替えた中で何番目かをXMATCH関数で取得しています｡
以上です｡
参考:COUNTIFを使った方法
1セルで九九表(SEQUENCE関数)
どこでもいいので､以下の数式をセル一つに入力すると九九表ができる｡
=SEQUENCE(9,1)*SEQUENCE(1,9)
応用1:アルファベット & 数字
=CHAR(CODE("a")-1+SEQUENCE(9,1))&SEQUENCE(1,9)
応用2: 1セルでカレンダー
=LET(_year,2022,_month,SEQUENCE(1,12),_day,SEQUENCE(31,1),_date,DATE(2022,_month,_day),IF(DAY(_date)<_day,"",_date))
カンマ区切りを分割する数式
私のEXCELにもLAMBDA関数がやってきたので、使ってみました。のメモ。
以下の数式をSPLITCSVという名前で名前の管理から登録します。
=LAMBDA(文字列,FILTERXML(SUBSTITUTE("<r><n>" & 文字列 & "</n></r>",",","</n><n>"),"r/n"))
上記は、"&"や"<"などの比較的使われる文字が入っているとエラーになってしまうので、改善した版が以下です。
=LAMBDA(文字列,SUBSTITUTE(FILTERXML(SUBSTITUTE("<r><n>" & SUBSTITUTE( 文字列,"<","🦁") & "</n></r>",",","</n><n>"),"r/n"),"🦁","<"))
以下のサイトを参考にしました。
（Excel）区切り文字による文字列の分割を関数で行う - いきなり答える備忘録 (officeisyours.com)
時間とニーズがあればもう少しわかりやすく書き直します。
