文章/答案/技术大牛

发布

社区首页 >问答首页 >Excel -计算由另一个工作表定义的单元格的平均值。

问Excel -计算由另一个工作表定义的单元格的平均值。
EN

Stack Overflow用户

提问于 2016-11-15 14:26:10

回答 1查看 49关注 0票数 0

我需要计算属于某一昆虫物种的寄主物种的某一性状的平均值。我这里的昆虫被称为物种，如图1所示。列mean_trait_of_hosts应该计算host_species 1到5的平均性状值。

Sheet1

宿主物种的组织方式如图2所示，并位于单独的单张中，相同的文档中。因此，我认为我需要一个函数，在表1对应行的B:F列中寻找宿主物种，然后使用sheet2值计算这些物种的平均值。不幸的是，我不知道如何在Excel中做到这一点。

Sheet2

有什么想法吗？

excel

mean

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-11-15 14:36:34

使用此数组公式：

=AVERAGE(IF(B2:F2<>"",LOOKUP($B2:$F2,Sheet2!$A$2:INDEX(Sheet2!A:A,MATCH("ZZZ",Sheet2!A:A)),Sheet2!$B$2:INDEX(Sheet2!B:B,MATCH("ZZZ",Sheet2!A:A)))))

作为数组公式，在退出编辑模式时，必须使用Ctrl-shift-Enter而不是Enter来确认。如果操作正确，那么Excel将把{}放在公式周围。

注意事项：

Sheet2上的列表必须按A列的升序排序。
如果查找不在列表中，它将按字母顺序返回前一个值。

为了处理上面的第二个警告，我们可以添加另一个IF来测试它们是否在列表中，然后再执行以下操作：

=IF(SUM(COUNTIFS(Sheet2!A:A,$B2:$F2,Sheet2!A:A,"<>"))<>SUMPRODUCT(--(LEN($B2:$F2)>0)),"Host(s) not in list",AVERAGE(IF(B2:F2<>"",LOOKUP($B2:$F2,Sheet2!$A$2:INDEX(Sheet2!A:A,MATCH("ZZZ",Sheet2!A:A)),Sheet2!$B$2:INDEX(Sheet2!B:B,MATCH("ZZZ",Sheet2!A:A))))))

这仍然是一个数组公式。作为数组公式，在退出编辑模式时，必须使用Ctrl-shift-Enter而不是Enter来确认。如果操作正确，那么Excel将把{}放在公式周围。

Sheet2上的数据仍必须按A列的升序排序。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/40612286

复制

相似问题

问Excel -计算由另一个工作表定义的单元格的平均值。
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel -计算由另一个工作表定义的单元格的平均值。EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel -计算由另一个工作表定义的单元格的平均值。
EN