在excel中，可以从单个单元格中的多个值(行字符串)创建命名范围吗？

Question

我想知道是否可以从单个单元格中的一行字符串(如"1,2,3,6,7“或"1-3，6-7”)生成一个命名范围，而不必在它自己的单元格中键入每个值。

在工作表A的C列中，如果A列的值存在于我的命名范围"B_range“中，则将B列的值相加，否则它将为0。我用的公式是：

=if(count.if(B_range,A2)>0,sumproduct(sum.if(A:A,B_range;B:B)),0)

"B_range“由表B中的C2:E2组成，但如果可能的话，我想从表B的B2中的文本字符串值中定义"B_range”。

​

​

​

​

Answer 1

这可能是一个相当棘手的问题。像'2,3,4‘这样的行不太多，但是'1-3，5，7’更有问题，更不用说两者的结合了。

如果您能够访问LET()和Microsoft365的动态数组，就会变得更容易：

​

​

C2中的公式

=LET(A,TRANSPOSE(MID(FILTERXML("<t><s>'"&SUBSTITUTE(E1,",","</s><s>'")&"</s></t>","//s"),2,LEN(E1))),B,--LEFT(A,FIND("-",A&"-")-1),C,IFERROR(--MID(A,FIND("-",A)+1,99),--A),D,MMULT((A2:A6>=B)*(A2:A6<=C),SEQUENCE(COUNTA(A),,,0)),IF(D,SUM(B2:B6*D),0))

变量解释如下：

• A - TRANSPOSE(MID(FILTERXML("<t><s>'"&SUBSTITUTE(E1,",","</s><s>'")&"</s></t>","//s"),2,LEN(E1)))将在水平数组中拆分初始字符串。在我们的例子中，{2,3,4,6-8}.
• B - --LEFT(A,FIND("-",A&"-")-1)将检索连字符左边的数字。请注意，为此我们将变量'A‘与连字符连接在一起。结果：{2,3,4,6}.
• C - IFERROR(--MID(A,FIND("-",A)+1,99),--A)将检索连字符的数字右。如果没有连字符，则返回与变量A相同的内容：{2,3,4,8}.
• D - MMULT((A2:A6>=B)*(A2:A6<=C),SEQUENCE(COUNTA(A),,,0))将计算在前两个变量'B‘和'C’之间是否存在‘no .’s。返回一个垂直数组：{0;1;1;1;0}.
• IF(D,SUM(B2:B6*D),0) --我们的最后计算是一个简单的IF()语句，它将使用D并在“Sum”.

下将结果溢出。

我可以深入到更多的细节，但是上面的内容会让你对这里发生的事情有一个了解。

对于那些发现如何将字符串“拆分”成数组的人，请按照this链接进行操作。