Excel - 在 FilterXML 中添加 Excel 公式 [英] Excel - Add an excel formula within FilterXML
问题描述
我有一个 Excel 电子表格,其中包含一列中的字符串列表.字符串列表由几个不同长度的数字组成,用/"和;"分隔字符串的第一个条目是一个代码 id(其长度始终为 3)(示例中为红色)后跟一个/",然后是一个金额(长度不同)(示例中为绿色)后跟一个;"如果字符串继续.
I have an excel spreadsheet containing a list of strings in one column. The list of strings is made up of several numbers from varying lengths, separated by "/" and ";" The first entry of the string is a code id (which always has a length of 3)(red in example) followed by an "/" then an amount (which varies in length)(green in example) followed by an ";" if the string continues.
在成员的帮助下,我现在可以使用以下公式分离绿色数字:
With the help of a member, I can now isolate the green number with the following formula:
=IF(ISBLANK(A4);"";TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A4;"/";";");";";"</s><s>")&"</s></t>";"//s[position() mod 2 = 0]")))
但是,如果满足条件,我仍然需要一个将绿色数字与变量相乘的其他公式.
However, I still need an other formula that multiplies the green number with a variable, if a condintion is met.
示例函数:
=IFS(B2<=10;B2*1,25;B2<=20;B2*1,18;B2<=100;B2*1,05;B2<=250;B2*1,01;B2>250;B2)
有没有办法将这两种功能结合起来?
推荐答案
处理这个问题的一个很好的方法是通过 LET()
将完整数组分配给一个名称,比如说一个变量.所以你在 B2
中的公式会变成:
A very nice way of dealing with this is assigning the full array to a name, let's say a variable, through LET()
. So your formula in B2
would become:
=IF(A2<>"",LET(MNT,TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,"/",";"),";","</s><s>")&"</s></t>","//s[position() mod 2 = 0]")),IFS(MNT<=10,MNT*1.25,MNT<=20,MNT*1.18,MNT<=100,MNT*1.05,MNT<=250,MNT*1.01,MNT>250,MNT)),"")
但是,它确实需要 Excel O365.但是由于您正在转置数组,因此您确实拥有该数组.
It, however, does require Excel O365. But since you are transposing the array it appears you do have that.
这篇关于Excel - 在 FilterXML 中添加 Excel 公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!