admin 管理员组

文章数量: 887021


2023年12月20日发(作者:jquery中load方法)

Excel数组及扩展,以及if({1,0}...)的深度理解

在用Excel使用过程中,为了满足一些需求,有时会用到数组及数组公式,我们深入学习一下。本次内容稍微有些难度,大家静下心来慢慢理解,理解以后,自己Excel函数的使用水平会有一个质的飞越。

一、什么是数组

数组就是单元的集合或是一组处理的值集合,这些元素按顺序存储在一起,可以通过函数的需要获取指定的函数,例如{2、4、6、8、10}就是一个数组。

在EXCEL里,表示数组的方法一般是用一对大括号将数据或者单元格引用起来,例如{A2:D2}、{A4:F4}

数组的维度包括一维数组和二维数组

1.一维水平数组

一维水平数组是指单元格中的元素是同一行数组,其中包括一维水平常量数组和一维水平区域数组,一维水平常量数组中的元素一般用逗号隔开,例如:

一维水平常量数组:{75,80,90}

一维水平区域数组:{B3:F3}

2.一维垂直数组

一维垂直数组是指单元格中的元素是用一列的数组,一维垂直常量数组中的元素一般用分号隔开,例如:

一维垂直常量数组:{75;80;90}

一维垂直区域数组:{B3:B6}

3.二维数组

二维数组包含工作表中的一个二维区域,二维常量数组使用逗号分隔水平元素,使用分号分隔垂直元素,如:

常量数组:{90,84,75;84,58,69}

区域数组:{B3:D6}

总结:根据数组中元素的多少和排列方式,我们可以分为

横向一维数组(元素之间用 , 分隔)

纵向一维数组(元素之间用 ; 分隔)

二维数组(横排元素用 , 分隔 纵排元素用 ; 分隔)

注意:符号均在英文半角状态下输入。

实际上Excel中一个sheet就是一个二维数组,每一行是一个水平数组,每一列是垂直数组。

二,数组的运算

!!!重要知识点:Excel函数数组规则:在进行计算的时候,数组元素不一致,会进行数组扩展!!!

题目一:一维数组乘法

公式写法:选定C1:C4,输入=A1:A4*B1:B4,按住【Ctrl+Shift+Enter]组合键,计算出结果

题目二:一维数组乘法(数组扩展)

数组元素数量一致时,不填时,默认为0。

数组元素不一致时,会自动填充

由上可见:Excel无法判断应该填充什么数据,所以会填充“#N/A”错误值。

题目三:扩展

①公式写法:=B6:C9*E6:F9

②公式写法:=B6:B8*D6:E9

公式解析:根据上边两个的运算结果,可以看到,在进行数组运算时,时行了水平和垂直的扩展。扩展方式如下图:

在垂直方向填充了#N/A,水平方向将原数据进行了复制。②中的公式等同于下图。

综上,在数组进行计算时,数组的维度和无素要一致,当不一致时,Excel将进行自动扩展,在垂直方向填充了#N/A,水平方向将原数据进行复制。讲到这,不知道大家对数组的运算及扩展有了初步的了解。

三,有了数组的初步基础,我们再深入探讨一下之前文章中提过的if{1,0}的扩展

1,数字1和0在Excel中的其它含义。在Excel中0,1除了是普通的数字外,还能表示逻辑值,False,True。在下图就可以看出它们之间的区别:

通常我们一般用0,1来表示,如果单纯表示真假的话,0是false,非0是true。

在某些地方,true,false可以用1,0来替换。但1,0不能全部用true,false来表示,只有表示逻辑值的时候,才能替换。

①IF({1,0},...)的使用

公式写法:选定B11:C15,输入=IF({1,0},C1:C5,B1:B5)按住【Ctrl+Shift+Enter]组合键,计算出结果

公式解析:

 IF函数的第一个参数:{1,0},是一个一行两列的数组常量,有两个元素;

 IF函数的第二个参数、第三个参数:均是一个五行一列的垂直数组。

上面说过,一行为水平数组,各个元素用逗号隔开,一列为垂直数据用分号隔开。现在看上面的公式{1,0},是水平数组。由于上面是选定了B11:C15,所以这个水平数组对应的列就是B列,C列,1代表true,0代表false,也就是if公式中true的结果显示在B列,false的结果显示在C列。C1:C5和B1:B5则是两个垂直数组,根据{1,0}显示在对应的位置。

根据上面我们讲的数组扩展的内容,上面公式的参数维度不一样,那是如何扩展的呢?

第一个参数{1,0}是一行两列,但第二个参数【C1:C5】是五行一列,那第一个参数就扩展成了如下图所示:

第二个参数如下图所示:

第三个参数是一样的道理,最终三个参数扩展成如下图所示

那运算的结果如下:

②IF({0,1,1},...)

相信大家对应数组公式的输入没什么问题了,我们直接看一公式及效果如下图所示:

公式解析:我们先来看一下公式三个参数扩展后是什么样子的呢?如下图所示:

运算过程:

最终的结果就成了我们展示的效果一样。

③IF({0,1;1,0;...},...),注意:逗号表示列分割,分号表示行分割

这个如果能理解了的话,数组公式问题就不大了。我们来看一下它的效果及计算过程

先看公式及效果

运算过程:

1)第一个参数是二行二列,第二和第三个参数是四行一列,那就需要扩展成一样的,第一个参数需要垂直扩展,第二和第三个参数需要水平扩展,结果如下图所示

大家还记着水平扩展是复制前一列数据,垂直扩展是填充#N/A

2)过程

四,总结

Excel的数组比较难理解一些,需要自己去理解它的扩展过程及计算过程,一旦理解了,必定对Excel公式的理解更上一层楼,可以更快速简便的解决一些实际工作中遇到的问题。


本文标签: 数组 扩展 公式 水平 元素