Excel公式技巧91：求对角线单元格中的数值之和

excelperfect

Excel公式与函数非常强大，往往能够实现你认为不可能的需求。例如下图1所示的例子，我只想求单元格区域B5:F9中对角线单元格中的数值之和。

图1

也就是：

B5+C6+D7+E8+F9=10+20+30+40+50=150

可以使用数组公式：

=SUM(B5:F9*(ROW(B5:F9)=COLUMN(B5:F9)+3))

结果如下图2所示。

图2

这个公式很简洁但有点特别，其特别之处是后面的+3。为什么呢？因为我们要求的是对角线上的单元格数值之和，对于矩阵来说，对角线上的行列号相等，在本例中，行号从第5行开始至第9行，列号从第2列开始至第6列，相差3，所以将列号+3，使行列号相等，从而能够获取相应的数值。

公式中，

B5:F9

将解析为一个5行5列的矩阵:

{10,0,0,0,0;

0,20,0,0,0;

0,0,30,0,0;

0,0,0,40,0;

0,0,0,0,50

}

而ROW(B5:F9)将解析为列矩阵：

{5;6;7;8;9}

COLUMN(B5:F9)+3

解析为行矩阵：

{2,3,4,5,6}+3

即：

{5,6,7,8,9}

对于

{5;6;7;8;9}={5,6,7,8,9}

解析为：

{TRUE,FALSE,FALSE,FALSE,FALSE;

FALSE,TRUE,FALSE,FALSE,FALSE;

FALSE,FALSE,TRUE,FALSE,FALSE;

FALSE,FALSE,FALSE,TRUE,FALSE;

FALSE,FALSE,FALSE,FALSE,TRUE

}

上述两个矩阵相乘，得到：

{10, 0,0,0,0;

0,20,0,0,0;

0,0,30,0,0;

0,0,0,40,0;

0,0,0,0,50

}

传递给SUM函数，得到结果：

150

小结：仔细体味Excel是怎么展开并处理矩阵的。