excel计算矩阵转置函数 看你矩阵基础是不是熟练

我们在坐公交车的时候，常常可以见到像下面这样的价格表，所谓“阶梯票价表”。假如这时候有人问你：小明坐了连续的8站，他可能的最大车费、最小车费是多少？

这就是我今天要说的对角线问题了。

当然，首先要有如下的一张原始表（请忽略奇特的价格数字~）：

然后，我们需要做出的结果表可能会是这个样子的：

显然，这里的处理，每次都是对某条斜对角线上的一串单元格进行了计算。第一反应，大家可能会写出像下面这样的公式，比如求乘坐1~n站的最大车费：

1站：

=MAX(B2,C3,D4,E5,F6,G7,H8,I9,J10,K11,L12,M13,N14,O15)

2站：

=MAX(B3,C4,D5,E6,F7,G8,H9,I10,J11,K12,L13,M14,N15)

3站：

=MAX(B4,C5,D6,E7,F8,G9,H10,I11,J12,K13,L14,M15)

4站：

=MAX(B5,C6,D7,E8,F9,G10,H11,I12,J13,K14,L15)

这个做法当然是对的，但总有些让人不舒服，雅则称枚举，俗则叫强算，轻则不可复用，重则易出差错。那么好的方法是什么样的呢？

这里推荐这样一个公式（数组公式，需要三键，然后下拉）：

最大值：

=MAX(IFERROR(IF(ROW($1:$14)=COLUMN($1:$14) $A18-1,$B$2:$O$15,0),0))

最小值：

=MIN(IFERROR(IF(ROW($1:$14)=COLUMN($1:$14) $A18-1,$B$2:$O$15,999),999))

平均值：

=SUM(IFERROR(IF(ROW($1:$14)=COLUMN($1:$14) $A18-1,$B$2:$O$15,0),0))/SUM(N(ROW($1:$14)=COLUMN($1:$14) $A18-1))

完美搞定对角线数据的最大、最小值、均值处理！

下面我们从内而外，简要解释其含义：

1、最内层，是类似这样的结构：ROW($1:$14)=COLUMN($1:$14) 0；我们知道，row(1:14)是纵向的1~14自然数向量，column(1:14)是横向的1~14自然数向量，那么结合起来呢，如果选中一个14*14的区域输出这一段（注意三键），其实就是如下图这样的（用布尔值TRUE/FALSE标记出了对角位置）：

那么，推而广之，当我们下拉的时候， 0变成了 1，这个部分的结果就会是下移一格的对角线：

OK，我们暂时可以把这一部分称为X。

2、再往外，则是这样的结构：IF(X,$B$2:$O$15,0)。这里很容易理解了，$B$2:$O$15，就是一个14*14的区域，那么这个部分含义很明确，在X中为TRUE的位置得到$B$2:$O$15对应位置的值，其他位置得到0。毋庸置疑，这部分结果如下：

这个部分，我们可以称之为Y，其实，到这里我们已经提取了想要的对角线数据，其他位置全是0。

3、再往外，是IFERROR(Y,0)。显然，这一层仅仅是作了错误值处理。这是因为最里层的ROW($1:$14)=COLUMN($1:$14) 0，其真实意义上的结果不仅仅是个14*14区域，作这样一个处理会更有保障，这里按下不表。

4、然后，就是最外层的MAX了，这个不必多说，直接对前面得到的结果求最大值。

两个扩展：最小值公式，仅仅把MAX改为MIN，同时将IF层和IFERROR层的0，改成一个很大的数（比如999）即可；平均值公式，仅仅把MAX改为SUM，且在末尾除以X层中TRUE的数量，可如此获得：SUM(N(X))

最后，留下一个小问题：这里是处理左上到右下的对角线，如果需要处理左下到右上的对角线，又该如何呢？

编后话：这一篇的内容难度稍高，小伙伴们可以对照示例文件操作试试看，如果一时看不懂也没关系，毕竟这样复杂的问题在工作中是少之又少的。