Excel中MAX、IF与AND函数嵌套求多条件最大值遇阻求助

这种嵌套数组公式踩坑太常见了！核心问题出在AND函数在数组环境下的行为——它不会返回对应每个单元格的布尔数组，而是把整个数组的条件判断合并成一个单一的TRUE/FALSE，导致IF无法正确筛选符合双条件的C列值，最终MAX找不到有效数据就返回0了。

先拆解你的需求，再给解决方案 #

你需要实现的逻辑是：

• 条件1：A列值等于$D$1
• 条件2：B列值属于该分组（A列=$D$1）的前80%（即大于等于该分组B列值的20分位数）
• 最终取满足这两个条件的C列最大值

方案1：兼容旧版Excel的数组公式（需按Ctrl+Shift+Enter） #

用乘法*代替AND实现数组中的逻辑与（Excel里TRUE=1，FALSE=0，相乘后只有两个条件都为TRUE时结果才是1，否则为0）：

=MAX(IF((A:A=$D$1)*(B:B>=PERCENTILE(IF(A:A=$D$1,B:B),0.2)),C:C))

按Ctrl+Shift+Enter确认后，公式会自动加上大括号（无需手动输入）。

公式逻辑拆解：

1. IF(A:A=$D$1,B:B)：先筛选出A列等于$D$1的所有B列值，其他位置返回FALSE
2. PERCENTILE(...,0.2)：计算上述筛选后B列值的20分位数（前80%的数值就是大于等于这个分位数的部分）
3. (A:A=$D$1)*(B:B>=分位数)：生成一个布尔数组，两个条件都满足的位置为1，否则为0
4. IF(...,C:C)：提取布尔数组中为1的位置对应的C列值，其他位置返回FALSE
5. MAX(...)：取提取出的C列值的最大值

方案2：Excel 365/2021的动态数组公式（无需数组快捷键） #

如果用的是新版Excel，直接用MAXIFS结合FILTER和PERCENTILEX.INC，写法更直观：

=MAXIFS(C:C,A:A,$D$1,B:B,">="&PERCENTILEX.INC(FILTER(B:B,A:A=$D$1),0.2))

公式逻辑拆解：

1. FILTER(B:B,A:A=$D$1)：筛选出A列等于$D$1的B列值
2. PERCENTILEX.INC(...,0.2)：计算该筛选结果的20分位数
3. MAXIFS：直接用两个条件（A列匹配、B列大于等于分位数）取C列最大值

额外注意事项 #

• 尽量避免用整列（A:A），换成具体的单元格范围（比如A2:A1000），减少不必要的计算量，提升公式效率
• 关于分位数：PERCENTILE.INC（或PERCENTILEX.INC）包含边界值，如果你需要排除边界可以用PERCENTILE.EXC，根据你的数据场景选择

用你的例子测试：当A列是“foo”，B列前80%为3、4、5、6时，公式会正确定位到这些行对应的C列值，返回最大值7。

内容的提问来源于stack exchange，提问作者45th Percentile