Excel 2016标准版：如何设置动态范围以使用SUMPRODUCT函数

一、正确的OFFSET+MATCH动态范围公式写法 #

假设你的产品列是A列（标题行A1，数据从A2开始），数值列是B列（标题行B1，数据从B2开始），要在C2单元格计算当前产品的最大值，可使用以下公式：

=SUMPRODUCT(MAX((OFFSET(A$2,0,0,MATCH(9.99E+307,B:B)-1,1)=A2)*OFFSET(B$2,0,0,MATCH(9.99E+307,B:B)-1,1)))

公式说明：

• MATCH(9.99E+307,B:B)：找到B列最后一个有数值的行号（9.99E+307是Excel能识别的最大数值，专门用来定位数值列的最后一行）
• OFFSET(A$2,0,0,[行高],1)：从A2开始，向下扩展到最后一行，构建产品列的动态范围；数值列同理
• SUMPRODUCT(MAX(...))：通过数组运算筛选出当前产品对应的所有数值，取最大值

二、#VALUE错误的常见原因及解决 #

1. MATCH函数定位错误
   • 如果你用MATCH("*",A:A,2)定位产品列最后一行，若产品是数字类型或列中间有空白，会返回错误。换成MATCH(9.99E+307,B:B)（针对数值列）或COUNTA(A:A)-1（针对非空产品列，需确保标题行唯一）即可。
2. OFFSET参数无效
   • 如果MATCH返回的行号是1（仅标题行），MATCH-1会得到0，OFFSET的高度参数为0会触发#VALUE错误。确保数据区域至少有一行有效数据。
3. 数组运算未正确触发（旧版Excel）
   • 旧版Excel中，SUMPRODUCT结合MAX的数组运算需要按Ctrl+Shift+Enter确认输入，否则会返回错误。新版Excel（365/2021）支持动态数组，无需此操作。

三、更优的替代方案：结构化表格 #

OFFSET是易失函数，每次工作表计算都会重新求值，数据量大时会拖慢速度。更推荐用Excel的结构化表格实现自动扩展：

1. 选中数据区域（包含标题行），按Ctrl+T，勾选"我的表有标题"，将数据转为结构化表格。
2. 在C2单元格输入公式：

=MAXIFS(Table1[数值列],Table1[产品列],[@产品列])

（把Table1[数值列]和Table1[产品列]替换成你的表格实际列名）
3. 新增数据时，表格会自动扩展范围，公式无需任何调整，计算效率更高。

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