在Excel查找满足条件的最大值

本文旨在Excel中利用函数查找满足条件的最大值,解决诸如「在张三、李二、王五中,李二的俯卧撑最高记录是多少」之类的问题

最近在思考怎么用Excel分析自己的阅读情况,需要查找满足条件的最大值,例如查找当A列值=X时,B列值中的最大值,因此单纯的 MAX 不够。以下是实例和解决方案:

1 示例

找到下表中每本书已经读到多少页,即每本书的最大页码(例如『开放的智力』读到了180页,『我的一生』读到了83页):

活动类别 页码
开放的智力 38
开放的智力 86
开放的智力 136
开放的智力 180
How to Read a Book 19
一生受用的分类技巧 52
一生受用的分类技巧 109
我的一生 39
一生受用的分类技巧 224
一生受用的分类技巧 262
我的一生 47
我的一生 83

可以直接复制表格内容1,粘贴到Excel工作表A1单元格,进行试验。大体有数组公式和普通公式两种方式。

Ctrl+Shift+Enter 生成数组公式;直接按 Enter 生成普通公式。

2 数组公式

  1. 在 C2 单元格输入公式(以下3种皆可):
    • =MAX(IF(A:A=A2,B:B,0)) 2
    • =MAX(IFERROR((A:A=A2)*B:B,0))

      不用 IFERROR 函数的话会出现 #VALUE! 错误。因为首行为文本格式,不能进行数值计算(A1*B1)。

    • =MAX((A$2:A$13=A5)*B$2:B$13,0) 3

      不将首行包含在求值范围内,则不会出现出现 #VALUE! 错误

  2. Ctrl+Shift+Enter 并向下填充,设置单元格格式

    数组公式,输入结束后必须按住Ctrl+Shift键回车。在编辑栏显示的公式两边会自动添加上大括号,这是使用数组公式必不可少的步骤。

总结:

  • 优点:可以在整列中查找符合条件的最大值,方便以后添加新数据时的公式的自动生成
  • 缺点:运算速度相对较慢,在数据多时尤为吃力

3 普通公式4

  1. 输入公式 =SUMPRODUCT(MAX((A$2:A$13=A2)*B$2:B$13))
  2. Enter 并向下填充,设置单元格格式

总结:

  • 优点:计算相对较慢
  • 缺点:不能针对整列求值, =SUMPRODUCT(MAX((A2:A30=A2)*B2:B30)) 会因首行为文本格式出现 #VALUE! 错误5,要想针对整列(这样以后加入新的记录,就可以直接自动计算),只能引用 A$2:A$1048576 (1,048,576是Excel 2010的最大行数限制6

4 更新历史

  • [2016-05-14 周六] 拟定初稿
  • [2016-05-13 周五] 拟定大纲

Date: [2016-05-13 周五]

Validate

版权声明

本文由宇晨创作,采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。

首发于跬步,转载或引用请注明出处,本文永久链接:在Excel查找满足条件的最大值

知识共享许可协议