动态行数下基于日期范围的列最大值条件格式公式报错问题

我来帮你梳理下这个问题的核心问题和解决方案，你遇到的情况在动态数据范围的条件格式里很常见，咱们一步步来：

首先，先说说你现有公式的几个问题：

• INDIRECT函数的误用：你用INDIRECT("$a"&MaxRow)只取了最后一行的年份，而不是整个A2到MaxRow范围的年份，这直接导致逻辑错误；而且INDIRECT是易失性函数，计算效率低，还容易出引用问题。
• MaxRow的计算不准确：COUNT(A:A)+1的写法有漏洞——COUNT只统计数字型单元格（日期本质是数字，但如果A列有空白行或者表头，结果会偏差），更可靠的最后一行获取方式是用MATCH(9^9,A:A)，它会找到A列最后一个日期行的行号（因为9^9是远大于常规日期的数字，MATCH会匹配最后一个小于等于它的位置）。
• 数组运算的逻辑偏差：你的公式里年份判断的范围不对，应该对比当前行的年份和整个同范围的年份，而不是只对比最后一行的年份。

正确的解决方案步骤 #

1. 先定义可靠的MaxRow名称
   点击「公式」选项卡→「名称管理器」→「新建」：

   • 名称：MaxRow
   • 引用位置：=MATCH(9^9,$A:$A)（假设A列的表头在A1，日期从A2开始）
     这个公式会自动获取A列最后一个有日期的行号，动态适配数据行数变化。

2. 设置条件格式的公式
   选中你要应用格式的E列范围（比如从E2到E列最后一行，或者直接选E:E，后续公式会自动忽略表头），然后新建条件格式：

   • 选择「使用公式确定要设置格式的单元格」
   • 输入以下公式：

     =$E1=MAX(IF(YEAR($A$2:INDEX($A:$A,MaxRow))=YEAR($A1),$E$2:INDEX($E:$E,MaxRow),""))
     

   • 设置你想要的高亮格式（比如填充色、字体颜色）

公式解释 #

• INDEX($A:$A,MaxRow)：代替INDIRECT动态引用到A列的最后一行，非易失性，更稳定。
• YEAR($A$2:INDEX($A:$A,MaxRow))=YEAR($A1)：判断A2到最后一行的每个日期是否和当前行（$A1）的年份相同，返回一个TRUE/FALSE的数组。
• IF(..., $E$2:INDEX($E:$E,MaxRow), "")：只保留同一年份对应的E列值，其他返回空。
• MAX(...)：取出同一年份E列的最大值，最后判断当前E列值$E1是否等于这个最大值，满足就应用格式。

注意事项 #

• 如果是Excel 365/2021版本，直接输入公式即可，新版支持动态数组运算；
• 如果是旧版Excel（2019及更早），输入公式后需要按Ctrl+Shift+Enter组合键，将公式作为数组公式确认。

备注：内容来源于stack exchange，提问作者TonyK1321