excel - 递归 LAMBDA 用查找表中的特定替换替换字符

Question

目标是遍历字符表的行并将每个字符替换为它的替代品。
本例中的字符表为 ={"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"} ， 或者:

*(旁注:在这种情况下，"&","&" 必须在列表中的最后一个，否则它将替换先前替换中的其他匹配项，因为我们要从最后到第一个。)
公式:

=LAMBDA(XML,Pos,
LET(
Cls,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},
Row,IF(ISOMITTED(Pos),ROWS(Cls),Pos),
Crf,INDEX(Cls,Row,1),
Crr,INDEX(Cls,Row,2),
Sub,SUBSTITUTE(XML,Crf,Crr),
IF(Row=0,XML,ENCODEXML(Sub,Row-1))
))

=ENCODEXML("sl < dk & jf") 的预期结果将是 sl < dk &amp jf我收到 #VALUE!而是错误。

Answer 1

您需要在递归上有一个导出:

=LAMBDA(XML,Pos,
LET(
Cls,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},
Row,IF(ISOMITTED(Pos),ROWS(Cls),Pos),
Crf,INDEX(Cls,Row,1),
Crr,INDEX(Cls,Row,2),
Sub,SUBSTITUTE(XML,Crf,Crr),
IF(Row>1,ENCODEXML(Sub,Row-1),Sub)
))

您需要添加 ,通话中:

=ENCODEXML("sl < dk & jf",)

或者正如@Filcuk 发现的(我刚刚了解到)如果它是可选的，则需要使用 [] 声明它
IE:

=LAMBDA(XML,[Pos],
LET(
Cls,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},
Row,IF(ISOMITTED(Pos),ROWS(Cls),Pos),
Crf,INDEX(Cls,Row,1),
Crr,INDEX(Cls,Row,2),
Sub,SUBSTITUTE(XML,Crf,Crr),
IF(Row>1,ENCODEXML(Sub,Row-1),Sub)
))

然后 ,不需要:

=ENCODEXML("sl < dk & jf")