Excel如何计算每类服装对应最高3个售价的平均值

实现方案 #

假设你的表格数据结构如下：

• 服饰描述列为A列，数据范围是A2:A20
• 售价列为B列，数据范围是B2:B20
• 待计算的类别名称存放在D列（如D2为shirt，D3为jumper），计算结果放在E列对应行

如果你使用Excel 365/2021及以上版本

直接在E2单元格输入以下公式，下拉填充即可：

=AVERAGE(TAKE(SORT(FILTER($B$2:$B$20,ISNUMBER(SEARCH(D2,$A$2:$A$20))),,,-1),3))

公式逻辑说明：

• SEARCH(D2,$A$2:$A$20)：模糊匹配A列中包含当前类别名称的行，不区分大小写
• FILTER：筛选出对应类别的所有售价
• SORT(,,,-1)：将筛选出的售价从高到低倒序排列
• TAKE(,3)：提取排序后前3高的售价
• AVERAGE：计算三个数值的平均值

如果你使用2019及更早版本的Excel

需要使用数组公式，在E2单元格输入以下内容后，按下Ctrl+Shift+Enter三键组合确认输入，再下拉填充：

=AVERAGE(LARGE(IF(ISNUMBER(SEARCH(D2,$A$2:$A$20)),$B$2:$B$20),{1,2,3}))

公式逻辑说明：

• IF判断对应行是否为目标类别，是则返回售价，否则返回逻辑值
• LARGE(,{1,2,3})：分别提取第1、2、3高的售价
• AVERAGE计算平均值

之前通配符方案出错的原因 #

直接用$A$2:$A$20="*"&D2&"*"的写法，通配符*仅在COUNTIF、SUMIF等原生支持通配符匹配的函数中生效，普通的等式判断无法识别通配符，所以要用SEARCH/FIND函数做模糊匹配才可以。

内容的提问来源于stack exchange，提问作者Aleksey Sidorov