Excel の LAMBDA 関数でDECODEURL作成

はじめに

　既に至るところで指摘されていますが、EXCELにはENCODEURL関数はありますが、なぜかDECODEURL関数はありません。
　しかし、Teams や Sharepoint でファイル情報を共有する際、パーセントエンコーディングされた文字列をデコードしたいと思いこそすれ、エンコードしたいとは思いません。だって、ブラウザのアドレスバーに入力すれば勝手にエンコードしてくれますもん。

　当ページでは、ENCODEURL関数の対になるDECODEURL関数の作り方を紹介します。
　使い方は簡単で、以下のLAMBDA関数をコピーし、Excelのメニューバー「数式」⇒「名前の管理」⇒名前の管理ダイアログで『新規作成』ボタンをクリックし、名前にDECODEURLと設定して「参照範囲」に数式を貼り付けて登録するだけでOKです。

DECODEURL

=LAMBDA(txt, 
  LET(
    Z, LAMBDA(f, LET(g, LAMBDA(x, f(LAMBDA(v, u, LET(xx, x(x), xx(v, u) )))), g(g))),
    dcdUrl, Z( LAMBDA(dcdUrl, LAMBDA(tmpTxt, idx,
      LET(nextIdx, IFERROR(FIND("%", tmpTxt, idx+1), -1), 
        IF(nextIdx < 0, tmpTxt,
          LET(str, IFERROR(MID(tmpTxt, nextIdx+1, 2), ""),
            isHexErr, OR(ISERROR(HEX2BIN(str, 8)), LEN(str) < 2),
            IF(isHexErr, dcdUrl(tmpTxt, nextIdx+1),
               LET(
                 val_bin, HEX2BIN(str, 8),
                 byt, IFS(LEFT(val_bin, 7)="1111110", 6,
                          LEFT(val_bin, 6)="111110", 5,
                          LEFT(val_bin, 5)="11110", 4,
                          LEFT(val_bin, 4)="1110", 3,
                          LEFT(val_bin, 3)="110", 2,
                          LEFT(val_bin, 1)="0", 1,
                          TRUE, 0
                      ),
                 rmng_chr, SWITCH(byt, 2,{3}, 3,{3,4}, 4,{3,4,5}, 5,{3,4,5,6}, 6,{3,4,5,6,7}, 7,{3,4,5,6,7,8}, FALSE),
                 chr_bin, MID(val_bin, IF(byt=1, 1, byt+2), 8) & IF(byt = 1, "", TEXTJOIN("",TRUE, MID(HEX2BIN(LEFT(CHOOSECOLS(TEXTSPLIT(MID(tmpTxt, idx+1, LEN(tmpTxt)), "%"), rmng_chr), 2), 8), 3, 6) )),
                 adj_bin, IFS(byt = 2, REPT("0", 16 - LEN(chr_bin)), byt = 4, REPT("0", 24 - LEN(chr_bin)), TRUE, "") & chr_bin,
                 uni_chr, UNICHAR(HEX2DEC(BIN2HEX(LEFT(adj_bin, 8), 2) & IF(byt >= 2, BIN2HEX(MID(adj_bin, 9, 8), 2), "") & IF(byt >= 4, BIN2HEX(MID(adj_bin, 17, 8), 2), ""))),
                 rep_tgt, MID(tmpTxt, FIND("%" & str, tmpTxt), 3 * byt),
                 dcd_txt, SUBSTITUTE(tmpTxt, rep_tgt, uni_chr),
                 dcdUrl(dcd_txt, nextIdx)
               )
            )
          )
        )
      )
    ))),
    dcdUrl(txt, 0)
  )
)

　以下の「名前の管理」アイコンをクリックすることで、「新しい名前」ダイアログが起動します。

　新しい名前ダイアログに以下のように、名前と数式をコピーしてください。

　登録が完了すると、Excelシート上で関数として認識されます。

処理概要

　パーセントエンコーディングされた文字列を探してUTF8からUNICODEへ変換する処理を再帰的に行います。
　実装を試行錯誤する中で、不動点コンビネータ（Zコンビネータ）を使用した実装としましたが、自分自身を呼び出す普通の再帰処理でも実現できたと思います。無駄に複雑なコードとなっている点は反省ですね…。

利用シーン

　TeamsやSharepointから取得したファイルパスをメモしている場合に、特に必要とするのかな、と考えます（MSアプリケーションがパーセントエンコーディングしなきゃいいだけなんですが‥）。
　私も時々作成するのですが、設計書等のファイルパスを以下のようにエクセルに張り付けてメモされているのを良く見かけます。

　それを人間が見て理解できる文字列に置き換えられるので、ファイル名だけでなく、ディレクトリ名に対してフィルタすることも可能になります。

最後に

　LAMBDA関数を使って、そこそこ実用的な関数が作れることを紹介しましたが、いかがでしたでしょうか。
　今回はLAMBDA関数コードのコピー&ペーストで、簡単にオリジナルの関数を利用できることを共有したいと思い執筆しました。当ページで紹介しているDECODEURL関数を使ってみて、LAMBDA関数に興味を持っていただければ嬉しいです。

　デコード処理は色んなサイトで既に提供されているのですが、会社によってはローカル端末の情報を外部に漏洩させないために、クリップボードの情報をブラウザにペーストさせない制限を掛けている所もあり、全てのユーザーがデコードできるワケではないんですね。
　しかし、だからと言ってパーセントエンコーディングしたリンクを共有してくるのは人としてどうなのか…と不満に感じたのが、DECODEURLを作ろうと思った動機です（笑）

　最後まで見ていただき、ありがとうございました！

【2024/04/27 0:18 更新】
・エンコードされた文字をデコードする際、"%" の後ろの2文字が16進数の値として有効な値かどうかチェックしていたのですが、取得した値が2文字未満の場合もデコード対象外としてスキップするよう修正しました。