数组函数Word格式.docx

数组函数Word格式.docx

《数组函数Word格式.docx》由会员分享，可在线阅读，更多相关《数组函数Word格式.docx（8页珍藏版）》请在冰豆网上搜索。

数组作为数据的组织形式本身可以是多维的，而且众多编程都支持多维数组，但是Excel的公式中最高只支持2维数组（也就是行列构成的数组）。

关于这个结论，在网络搜索不到明确的答案，做这个结论基于以下两点：

Excel不支持形如={{…},{…}}或={{…};

{…}}的书写格式，所以无法表示3维或多维数组。

在Excel中提供对数组引用的函数为index（），它的格式为index（array,row_num,column_num），它只有行列两个参数，而没有引用高于2维数组的参数。

index函数也有这样的格式index（reference,row_num,column_num,area_num），它提供了第三个引用参数，是不是说明Excel提供了对3维数组的引用呢？

答案是否定的，reference可以使用多个区域（区域之间有逗号隔开）构成的参数，第三个参数area_num是用于指定reference中第几个区域的，如INDEX（（A1:

C6,A8:

C11）,1,1,2）中第三个参数只是指定区域A8:

C11而已。

Excel　VBA支持多维数组。

1.4.数组的引用

在运算过程中，可能需要引用数组中的某一个元素、某一行或某一列。

下面介绍元素、行、列的引用方法。

元素的引用：

对于1维数组index（array,c），对于2维数组index（array,r,c）。

其中r表示第几行，c表示第几列，一般为正整数。

当r为小数时，会舍弃小数点后的部公（非四舍五入），如index（array,1.2,2.9）等同于index（array,1,2）。

编程语言一般默认以截取的方式处理小数转整数的操作，这样可以提高效率，Excel做这样的处理也是在意料之内的。

行引用

index（array,r,0）返回第r行数据构成的数组。

列引用

Index（array,0,c）返回第c列数据构成的数组。

特殊的，index（array,0,0）将返回整个2维数组。

而index（index（array,r,0）,c）、index（index（array,0,c）,r）和index（array,r,c）相同，都可以返回第r行第c列的元素。

1.5.数组的算术运算和数组的扩展

当数组进行运算（加、减、乘、除、幂）时，要求两个数组具有相同的维数，如果是2维数组还要求两数组的行数相同，列数也相同。

满足这个条件的两个数组的算术运算（加、减、乘、除、幂）等于对应元素之间进行数学运算。

如两个具有3行3列的2维数组之间的加法运算为：

如果参与运算的两个数据的维数不同、行数不同或列数不同，Excel会对数据的维数和行列进行扩展，以满足上述的要求而进行运算。

扩展的规则如下：

维数扩展：

如果两数据的维数不同时，维数低的数据会被扩展为和维数高的数据的维数相同。

维数的扩展有两种类型，常数向1维数组或2维数组的扩展、1维数组向2维数组扩展。

行列扩展：

如果两数据的行数不同，行数低的数据会扩展为和行数高的数据的行数相同。

如果列数不同，列扩展的规则和行扩展相同。

如果行列均不相同，那么行列都需要扩展。

根据这些原则，一个M1行、N1列的数组和一个M2行、N2列的数组运算后，得到的是max（M1,M2）行、max（N1,N2）列的数组。

这个结论包含了所有类型数据的运算。

例如{1,2,3;

4,5,6}和{7,8,9,10}运算，前者M1=2,N1=3，后者M1=1,N2=4，结果为2行4列的数组。

数据扩展后，出现了一此数据空位，如计算下列数组时

首先要把（12）扩展为

这时出现了一些数据空位，那么Excel如何填充这些空位的呢？

这里也有规则可循，个人用实验的方法总结如下：

对常数所有的扩展，空位都填写该常数。

当行数组进行行扩展时，扩展出来的每一行的数据和首行相同；

当行数组进行列扩展时，扩展列的数据填写错误值#N/A。

当列数组进行列扩展时，扩展出来的每一列的数据和首列相同；

当列数组进行行扩展时，扩展行的数据填写错误值#N/A。

当2维的数组进行行扩展或列扩展时，扩展出的数据都填写错误值#N/A。

因此，上边那个例子，（12）应该扩展为：

因此计算的结果为：

有了这些规则，就可以进行任意数组、常数间的算术运算了。

2.数组公式

2.1.什么是数组公式

数组公式是指可以在数组的一项或多项上执行多个计算的公式。

数组公式可以返回多个结果，也可返回一个结果。

2.2.数组公式的输入

输入数组公式后，同时按下Ctrl+Shift+Enter，数组公式的外面会自动加上大括号{}以和普通公式区分。

{}不是手工输入的，那样Excel会认为您输入的是文本格式。

数组公式每次编辑过后，都要同时按下Ctrl+Shift+Enter以完成输入。

2.3.多单元格公式和单元格公式

如果把数组公式的返回值放入到一个单元格区域内可以呈现返回数组的各个元素的值，这样的公式称为多单元格公式。

而位于单元格中的数组公式称为单元格公式。

多单格公式在使用时，需遵循以下原则：

必须在输入公式之前选择用于保存结果的单元格区域，这个区域最好和返回的数组尺寸相同，否则在超出数组尺寸范围外的单元格填写#N/A。

不能更改多单元格数组公式中部分单元格的内容，但可以移动或删除整个数组公式。

换言之，要编辑数组公式，需先删除现有公式再重新开始。

不能向多单元格数组公式中插入空白单元格（包括插入新行、列、单元格）或删除其中的单元格。

这和“不能更改多单元格数组公式中部分单元格的内容”是一致的。

2.4.数组公式的优点

简洁性：

数组公式可以同对多个数据执行多个运算。

解决一个复杂的问题可以只需要一个公式，而用普通公式可能需要多步运算，甚至要填加辅助列。

不过，数组公式可以用几个一般公式和一些过渡数据来代替，从而达到同样的目的，所以千万不要迷信数组公式，以免走火入魔！

一致性：

多单元格数组公式中，点击任一个单元格，看到的公式内容都是相同的。

这种一致性可以保证更高的准确性。

安全性：

不能覆盖多单元格数组公式的组成部分，可以防止误操作。

文件小：

通常可以使用单个数组公式，而不必用多个中间公式。

这在数据量很大的时候效果才会明显。

2.5.数组公式的语法

数组公式主要使用标准公式语法。

它们都以等号开始，可以在数组公式中使用任何内置Excel函数。

使用数组公式的主要不同之处在于:

必须按Ctrl+Shift+Enter输入公式

输入多单元格公式时，必须首先选择用于保存结果的单元格区域

2.6.数组函数示例

1.计算一个区域内，不重复的数据的个数。

（个人认为这是数组函数最精彩的例子，正是这个例子激发了我学习数组公式的兴趣）

{=SUM（1/COUNTIF（A1:

A100,A1:

A100）}，这里假设区域为A1：

A100。

分析：

COUNTIF（range,criteria）在range范围内查找criteria，并返回它的个数。

假设A1单元格内的数据为X在A1:

A100范围内共有N个数据X。

那么数组公式对这100个单元格逐个计算1/COUNTIF（A1:

A100,某一单元格），当对A1计算时，返回结果1/N，在这100次计算里，每遇到数据X就回返回一个1/N，共返回N个1/N，求和为1。

也就是说，有多少个不同的数据就返回多少个1，恰好就是不重复的数据的个数。

这个公式是多么简洁优美！

不过再美好的事物也是有缺点的，统计区域内不得有空单元格，否则返回#DIV/0!

错误。

可以用下面的数组公式解决这个问题：

{=SUM（IF（A1:

A100&

lt;

&

gt;

"

1/COUNTIF（A1:

A100,A1:

A100））}

当然这个问题也可以用sumproduct（）、frequency（）或match（）实现。

2.計算1+2+3+…..+100的和。

（假設不知道等差求和公式）

{=sum（row（A1:

A100））}或{=sum（row（1:

100））}

注意：

row（）的运算结果可以做很多函数的参数。

3.條件求和

A

B

C

D

1

产品编号

生产日期

产品单价

产品数量

2

AA

2009/6/15

100

3

2009/6/20

125

4

BB

2009/6/30

150

5

2009/7/10

175

6

CC

200

7

2009/7/20

225

8

2009/7/30

250

9

2009/8/10

275

10

2009/8/15

300

11

2009/8/20

325

12

2009/10/10

350

13

2009/10/15

400

14

DD

2009/10/20

425

15

2009/10/30

500

求產品BB的8月份產量

{=SUM（IF（（B2:

B15="

BB"

）*（MONTH（C2:

C15）=8）,E2:

E15,0））}

这里if用的判断条件为（B2:

C15）=8），如果把这部分改为and（B2:

MONTH（C2:

C15）=8），则不能工作。

起初这另我很不解，但仔细研究and（）发现，and（）处理常量数组和单元格区域数组的方式是不同的。

当处理常量数组时，运算结果还是数组。

如=index（{TRUE,TRUE},{TURE,FALSE},2）结果为FALSE。

当处理受单元格区域数组时，会把数组内的所有的数据都取出来作为多个参数，运算最终结果不是一个数组，而仅仅是一个逻辑值。

如=index（and（B2:

C15）=8）,1）会得到#VALUE!

。

因此，{=SUM（IF（and（B2:

C15）=8）,（E2:

E15）,0））}这个公式在运算时，and（B2:

C15）=8）的计算值为FALSE，最终计算结果为零。

另外，逻辑值是可以直接参与算术运算的，这时TRUE=1,FALSE=0，因此下面这个公式可以简化为：

{=SUM（（B2:

C15）=8）*E2:

E15）}

求产品BB和CC的总产值

）+（B2:

CC"

）,E2:

E15*D2:

D15））}

这个公式可以简化为：

{=SUM（（（B2:

））*E2:

D15）}