javascript二维数组转置_精通Excel数组公式010：认识数组函数：TRANSPOSE、MODE.MULT和TREND...

学习Excel技术&＃xff0c;关注微信公众号&＃xff1a;

excelperfect

数组函数是可以提供多个值的Excel内置函数。下面列出了8个Excel内置的数组函数&＃xff1a;

TRANSPOSE函数

MODE.MULT函数(在Excel 2010中引入)

TREND函数

FREQUENCY函数

LINEST函数

MMULT函数

MUNIT函数(在Excel 2013中引入)

MINVERSE函数

下面讲解前3个函数。

TRANSPOSE函数

TRANSPOSE函数可以将垂直数组转换成水平数组&＃xff0c;并且可以处理一维或二维数据。

如下图1所示&＃xff0c;在源数据区域A2:D4中&＃xff0c;数据占3行4列&＃xff0c;要想将这些数据转置&＃xff0c;选择一个4行3列的单元格区域&＃xff0c;如图中的A7:C10&＃xff0c;输入公式&＃xff1a;

&＃61;TRANSPOSE(A2:D4)

按Ctrl&＃43;Shift&＃43;Enter键。

图1

注意&＃xff0c;使用TRANSPOSE函数不仅将数据转置&＃xff0c;而且建立了数据之间的链接&＃xff0c;当源数据区域的数据变化时&＃xff0c;被转置的数据随着变化。

有时候&＃xff0c;当源数据区域中有空单元格时&＃xff0c;TRANSPOSE函数将该单元格数据显示为0&＃xff0c;如下图2所示。

图2

注&＃xff1a;有些函数&＃xff0c;例如TRANSPOSE、VLOOKUP、INDEX等&＃xff0c;将空单元格视作0&＃xff1b;有些函数&＃xff0c;例如MIN、MAX、AVERAGE&＃xff0c;不会将空单元格视作0。

使用IF函数和ISBLANK函数来解决这个问题&＃xff0c;让空单元格显示空&＃xff0c;如下图3所示。

图3

MODE.MULT函数

在一组数中获取出现次数最多的数时&＃xff0c;可以使用MODE函数或MODE.SNG函数&＃xff0c;然而&＃xff0c;当一组数中有多个出现次数一样且最多的数时&＃xff0c;MODE函数或MODE.SNG函数只返回找到的第一个数&＃xff0c;此时&＃xff0c;可以使用MODE.MULT函数。

如下图4所示&＃xff0c;选择单元格区域C7:C8&＃xff0c;输入数组公式&＃xff1a;

&＃61;MODE.MULT(A2:A15)

得到结果。(这两个数字都出现了3次且最多)

图4

然而&＃xff0c;我们知道上例中会有2个结果&＃xff0c;因此选择了含有2个单元格的区域。实际上&＃xff0c;数据可能变化&＃xff0c;因此我们事先不知道有几个出现最多的数&＃xff0c;如果选择可能多的单元格区域&＃xff0c;输入上面的公式&＃xff0c;可能在有的单元格中出现错误值&＃xff0c;如下图5所示。

图5

此时&＃xff0c;我们可以灵活运用前面学到的知识&＃xff0c;组合多个函数来适应数据的变化&＃xff0c;如下图6所示&＃xff0c;如果有更多的数&＃xff0c;则可直接向下拉动单元格。

图6

还可以使用IFERROR函数使公式简化&＃xff0c;如下图7所示。

图7

TREND函数

使用TREND函数估计线性模型中的y值。有时候&＃xff0c;我们需要使用数据来计算斜率和截距&＃xff0c;以创建线性模型来辅助预测。线性模型等式为&＃xff1a;

y&＃61;f(x)&＃61;mx&＃43;b

如下图8所示&＃xff0c;使用了3个公式来预测学生的分数。

图8

所有的4个函数(SLOPE、INTERCEPT、FORECAST、TREND)都要求知道y值和x值。如果使用SLOPE函数和INTERCEPT函数&＃xff0c;则创建mx&＃43;b公式&＃xff1b;如果使用FORECAST或TREND函数&＃xff0c;则只需添加x值用于预测&＃xff0c;如下图9所示&＃xff1b;如果要预测多个y值&＃xff0c;则最好使用TREND函数&＃xff0c;如下图10所示。不像FORECAST函数每次都要重复计算&＃xff0c;TREND函数仅计算m和b值一次&＃xff0c;因此当处理大量数据时&＃xff0c;其速度更快。

图9

图10

《Ctrl&＃43;Shift&＃43;Enter&＃xff1a;MasteringExcel Array Formulas》学习笔记

完美Excel

欢迎在下面留言&＃xff0c;完善本文内容&＃xff0c;让更多的人学到更完美的知识。

欢迎到知识星球&＃xff1a;完美Excel社群&＃xff0c;进行技术交流和提问&＃xff0c;获取更多电子资料。