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屋！