EXCEL数组运用

EXCEL数组运算是对我们常用EXCEL公式的扩展，经常用在一些比较高阶的函数嵌套计算中。理解数组，对于EXCEL函数编程能力的进阶有至关重要的作用。在EXCEL公式编辑栏中，数组的输入与一般函数不同，必须先选择区域然后进行公式的输入，输入完成后按【CTRL+SHIFT+Enter】三键退出。

1、数组工作原理

简单来说，EXCEL数组就是单元格区域，而单元格区域有时为行、有时为列、有时为块、有时为一个单元格。

I、数组生成

例1：生成5×5单位矩阵（数组）

第一步：在EXCEL表格中用鼠标选择区域A1:E5

第二步：在EXCEL公式编辑框中输入公式【=IF(ROW()=COLUMN(),1,0)】

第三步：公式输入完成后按【CTRL+SHIFT+Enter】组合键退出

生成5×5单位矩阵工作原理为：所选区域范围内，每个单元格都执行公式【=IF(ROW()=COLUMN(),1,0)】，即在所选区域内每个单元格都判断该单元格所属行、列是否相等。相等为1、否则为0。

例2：生成9×9乘法表

第一步：在EXCEL表格中用鼠标选择区域A1:J10

第二步：在EXCEL公式编辑框中输入公式【=A2:A10*B1:J1】

第三步：公式输入完成后按【CTRL+SHIFT+Enter】组合键退出

生成9×9乘法表原理为：所选区域范围内，每个单元格都执行公式【=A2:A10*B1:J1】。即在所选区域范围内每个单元格的值A2:A10中在对应行标题乘B1:J1中对应列标题。例如在所选区域第2行、第3列（单元格D3），值为A3*D1=2*3=6。

II、计算不重复项总数

在EXCEL表格中有50个数据如下：

数据不重复项总数公式为【=SUM(1/COUNTIF(A1:E10,A1:E10))】，公式输入完成后按【CTRL+SHIFT+Enter】组合键退出。

计算原理：

• 条件计数公式【=COUNTIF(A1:E10,52)】计算单元格区域A1:E10内有多少个52；
• 数组公式【=COUNTIF(A1:E10,A1:E10)】计数所选范围内每个单元格中数字发生次数；

• 公式【=1/COUNTIF(A1:E10,A1:E10)】计数所选范围内每个单元格中数字发生权数。

2、统计汇总

案例一：

单元格B10公式【=SUM(IF(A2:A7=A10,B2:B7*C2:C7,0))】，公式输入完成后按【CTRL+SHIFT+Enter】组合键退出

案例二：

• 总产值：【=SUM(D2:D15*E2:E15)】
• 10月份产值：【=SUM(IF(MONTH(C2:C15)=10,D2:D15*E2:E15,0))】
• 10月份产品CC产值：【=SUM(IF((MONTH(C2:C15)=10)*(B2:B15="CC"),D2:D15*E2:E15,0))】
• 产品BB和CC的总产值：【SUM(IF((B2:B15="BB")+(B2:B15="CC"),D2:D15*E2:E15,0))】
• 产品BB和CC的第2季度总产值：【=SUM(IF(((B2:B15="BB")+(B2:B15="CC"))*((MONTH(C2:C15)=7)+(MONTH(C2:C15)=8)+(MONTH(C2:C15)=9)),D2:D15*E2:E15,0))】
  或者【=SUM(IF(((B2:B15="BB")+(B2:B15="CC"))*(LEN(2^MONTH(C2:C15))=3),D2:D15*E2:E15,0))】

其中，【LEN(2^MONTH(C2:C15))】根据月份返回季度（1、2、3、4）。数组公式不支持AND和OR函数。在数组公式中，公式(MONTH(C2:C15)=10)*(B2:B15="CC")中“*”类似AND，(B2:B15="BB")+(B2:B15="CC")中“+”类似OR。注意数组公式输入完成后按【CTRL+SHIFT+Enter】组合键退出。

3、统计公式计算

案例一：计算标准差

标准差统计公式：

标准差EXCEL数组公式：【=SUM((A1:E20-AVERAGE(A1:E20))^2)/COUNT(A1:E20)】

标准差EXCEL自带函数：【=VAR.P(A1:E20)】

案例二：计算相关系数

相关系数统计公式：

相关系数EXCEL数组公式：【=SUM((A2:A10-AVERAGE(A2:A10))*(B2:B10-AVERAGE(B2:B10)))/(SUM((A2:A10-AVERAGE(A2:A10))^2)*SUM((B2:B10-AVERAGE(B2:B10))^2))^0.5】

相关系数EXCEL自带函数：【=CORREL(A2:A10,B2:B10)】

4、矩阵运算

I、矩阵转置

转置公式【=TRANSPOSE(A1:D3)】，表中单元格区域A1:D3为3×4矩阵，首先用鼠标选择区域F1:H4（4×3矩阵），输入转置公式后按【CTRL+SHIFT+Enter】组合键退出。

II、矩阵乘

矩阵乘公式【=MMULT(A1:D3,A5:B8)】，表中单元格区域A1:D3为3×4矩阵A3×4，单元格区域A5:B8为4×2矩阵B4×2，两矩阵乘积A3×4·B4×2=C3×2 。用鼠标选择区域F6:G8（3×2矩阵），输入转置公式后按【CTRL+SHIFT+Enter】组合键退出。

III、逆矩阵

逆矩阵公式【=MINVERSE(A10:D13)】，表中单元格区域A10:D13为4×4方阵，用鼠标选择区域F10:H13（4×4方阵），输入逆矩阵公式后按【CTRL+SHIFT+Enter】组合键退出。

IV、行列式

行列式公式【=MDETERM(A15:C17)】，表中单元格区域A15:C17为3×3方阵，用鼠标选择单元格E15，输入行列式公式后正常按【Enter】键退出（不用组合键）。

5、多元线性方程组求解

I、多元线性方程组

多元线性方程组在线性代数中被大量运用，一般形式为：

其中 m 和 n 都是正整数， aij 和 bi 都是系数(1≤i≤m, 1≤j≤n )，x1,⋯,xn 是未知数。如果取，

多元线性方程组可以写成 Ax = b 。如果方程组有唯一一组解，则有，

例如，设有方程组如下：

设，

将方程组的解用矩阵表示为，

II、运用EXCEL解多元线性方程组

多元线性方程组解公式【=MMULT(MINVERSE(A1:C3),E1:E3)】，表中单元格区域A1:C3为3×3方阵A、单元格区域E1:E3为3×1列矩阵b，用鼠标选择单元格G1:G3，输入多元线性方程组解公式后按【CTRL+SHIFT+Enter】组合键退出。

6、多元线性回归模型求解

I、多元线性回归模型

模型中，a0、a1、...、am为待定系数。

II、模型求导

根据最新二乘法，多元线性回归模型待定系数解可用矩阵形式表示为，

式中，

例如，现有某区十户居民分持久和临时的月收入与消费调查样本数据如下：

如果持久收入X1及临时收入X2和总消费Y显著线性相关，回归模型如下：

式中，

运用EXCEL矩阵函数可得计算过程如下：

第一步：计算X'X

X'X公式【=MMULT(TRANSPOSE(A2:C11),A2:C11)】

第二步：计算X'Y

X'Y公式【=MMULT(TRANSPOSE(A2:C11),D2:D11)】

第三步：计算系数A

系数A公式【=MMULT(MINVERSE(F2:H4),F6:F8)】

关于EXCEL数组进一步运用参见文章：

• 样本距离 - 数据挖掘方法
• KNN算法 - 数据挖掘方法
• k-means算法EXCEL模板设计