Excel公式技巧：颠倒单元格区域/数组

如下图1所示，我想使用公式：

=SUMPRODUCT(A1:G1,G2:A2)

但是，Excel总是将其修改为从左到右的单元格区域：

=SUMPRODUCT(A1:G1,A2:G2)

图1

如何实现自己的目的呢？

可以使用公式：

=SUMPRODUCT(A1:G1,N(OFFSET(A2:G2,0,7-COLUMN(A2:G2),1,1)))

然而，公式使用了硬编码7，如果区域大小变了，这个数字也要手动更改。可以将上面的公式修改为：

=SUMPRODUCT(A1:G1,N(OFFSET(A2:G2,0,COLUMNS(A2:G2)-COLUMN(A2:G2),1,1)))

为什么OFFSET函数前还要加上N函数呢？这是因为OFFSET函数只返回单元格区域引用，而不返回值。OFFSET函数使用第1个、第2个或第3个参数为数组的调用，返回一组单元格区域引用，当用于算术操作数或大多数函数的参数时，Excel无法处理。幸运的是，N函数是一个例外，它有效地将区域引用数组转换为数字数组（注意，它将不是数字的条目转换为数字零）。T函数对字符串执行相同的操作。N函数仅返回数值，将其他所有内容转换为0；T函数仅返回字符串，将其他所有内容转换为空；如果都想返回，使用CELL(“Contents”,…)。注意，当使用公式求值或按F9键时，Excel不能直接显示OFFSET部分的内容，而是显示为#VALUE!。

例如，我想求单元格A1+A3+A5之和，如果使用公式：

=SUM(OFFSET(A1,{0;2;4},0,1,1))

无论是否以数组公式输入，返回的值都是单元格A1中的值。

然而，如果使用公式：

=SUM(N(OFFSET(A1,{0;2;4},0,1,1)))

则返回正确结果。

还可以使用公式：

=SUM(TRANSPOSE(OFFSET(A1,{0;2;4},0,1,1)))