Excel动态调整求和区域计算工时占比及#REF错误排查

一、#REF错误的原因 #

你使用的公式=(SUM(INDIRECT(Q1&5&":"&P2&5)))/(7.5-AT5)*100%返回#REF，核心是INDIRECT无法解析拼接出的引用字符串，常见诱因有三个：

1. 引用顺序反向：如果Q1对应的列在P2对应的列右侧（比如Q1是"D"，P2是"B"），拼接出的区域是D5:B5，Excel要求区域起始位置必须在结束位置之前，这种反向引用属于非法格式，直接触发#REF。
2. 列标识无效：如果Q1或P2不是合法的列字母（比如是数字、错误值、空白单元格），拼接后的字符串会变成5:XX5这类非法引用，INDIRECT无法识别，返回#REF。
3. INDIRECT的固有局限：它是易失性函数，对引用字符串的格式要求极为严苛，哪怕多一个空格、少一个字符都会触发错误。

二、实现动态求和范围的正确方法 #

建议放弃INDIRECT，改用更稳定的函数组合，同时兼顾首尾周天数不足、周末列不计入的需求：

方法1：用INDEX构建动态区域（兼容所有Excel版本） #

如果Q1和P2存储的是列字母（比如"G"、"K"），先将列字母转为列号，再用INDEX定位第5行的首尾单元格：

=SUM(INDEX(5:5, COLUMN(INDIRECT(Q1&1))):INDEX(5:5, COLUMN(INDIRECT(P2&1)))) / (7.5-AT5)*100%

如果Q1和P2存储的是列号（比如7、11），公式更简洁：

=SUM(INDEX(5:5, Q1):INDEX(5:5, P2)) / (7.5-AT5)*100%

方法2：自动跳过周末的动态求和（推荐） #

假设第1行是日期（比如A1是"2024/1/1"），直接通过日期判断工作日，自动跳过周末（周一到周五计入，周六周日不计），同时匹配Q1和P2的日期范围：

=SUMPRODUCT((WEEKDAY(1:1, 2)<6)*(1:1>=Q1)*(1:1<=P2)*5:5) / (7.5-AT5)*100%

• WEEKDAY(1:1,2)<6：判断第1行的日期是否为工作日（1=周一，5=周五）
• 1:1>=Q1和1:1<=P2：限定求和的日期范围
• 该公式无需依赖列字母，切换月份时会自动适配日期范围，不会出现错位问题。

方法3：Excel 365/2021专属动态数组方案 #

如果使用新版Excel，可直接用FILTER+SUM组合，逻辑更直观：

=SUM(FILTER(5:5, (WEEKDAY(1:1,2)<6)*(1:1>=Q1)*(1:1<=P2))) / (7.5-AT5)*100%

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