EXCEL -如果列中的单元格包含列表(特定于每行的列表)中的任何字符串，则为平均值

Question

我想得到一个公式，根据一个列的文本(示例中的“Item”)是否包含在另一列(示例中的“Other items”)中的字符串中，来计算"averageif“。

以A项为例，我想得到B项和C项在"Output“列的平均值。

有什么简单的方法可以做到这一点吗？我考虑过数组搜索，但一直收到错误

​

​

Answer 1

一个选项可以是SUMPRODUCT中的SEARCH

=SUMPRODUCT(ISNUMBER(SEARCH($A$2:$A$5,"," & C2 & ","))*$B$2:$B$5)/SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$5,"," & C2 & ",")))

​

​

Answer 2

可以使用FILTERXML：

=SUMPRODUCT(SUMIFS(B:B,A:A,FILTERXML("<t><s>"&SUBSTITUTE(C2,",","</s><s>")&"</s></t>","//s")))/SUMPRODUCT(COUNTIFS(A:A,FILTERXML("<t><s>"&SUBSTITUTE(C2,",","</s><s>")&"</s></t>","//s")))

​

​