arrays - 将逗号分隔的数组作为函数参数传递

Question

我有一列包含唯一标识符，另一列包含价格:

A | B
--+---
1 | $2
2 | $4
3 | $1
s | $6
7 | $5
x | $4
5 | $4

在工作簿的其他地方，我还有一个包含逗号分隔数组的列:

    C
---------
2,3,s
3,1
x
7,2,3,s,5

在 D 列中，我想要各个价格的总和，例如C1 中的 2,3,s 在 D1 中将给出 $11。 (4 美元 + 1 美元 + 6 美元 = 11 美元)

我尝试使用此公式搜索数组 {2;3;"s"}:

=SUM(SUMIF($A$1:$A$7;{2;3;"s"};$B$1:$B$7))

但它仅适用于硬编码数组。这不起作用:

=SUM(SUMIF($A$1:$A$7;C1       ;$B$1:$B$7))

我应该对此函数进行哪些更改，以便它能够正确读取文本逗号分隔数组？

(快速完成此任务的用户定义函数也将受到赞赏。)

Answer 1

将 SUMPRODUCT 与 SEARCH() 一起使用:

=SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$7,C1))*$B$1:$B$7)

这将遍历 A 列并查看该值是否包含在 C1 的字符串中。 SUMPRODUCT 对 B 中 SEARCH 返回 true 的值求和。

<小时/>

如果您的 A 列包含 a 和 aa 或 1 和 11 等内容，两者都会被选中向上。为了确保选择正确的，我们可以稍微改变一下公式并强制进行特定查找:

=SUMPRODUCT(ISNUMBER(SEARCH("," & $A$1:$A$7 & ",","," & C1 & ","))*$B$1:$B$7)