二维码 购物车
部落窝在线教育欢迎您!

不懂excel中的数组公式,怎么晋升高手?

 

作者:赋春风来源:部落窝教育发布时间:2020-03-30 17:06:59点击:4133

分享到:
0
收藏    收藏人气:1人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!今天要给大家介绍的内容,对于学习excel的小伙伴来说,绝对不陌生,没错,它就是数组。估计大家还从未专门去了解过数组的概念,只知道数组公式要用“Ctrl+Shift+Enter”组合键结束公式,才能得到正确的结果。今天,春风就带大家一起来了解一下excel中的数组。

 

数组,是Excel中一个必不可少的内容,相信很多同学都有接触过,但是由于数组的理论部分较多,且略为枯燥和难懂,相信不少小伙伴对于它都是似懂非懂的,今天小编就尝试用通俗易懂的语言,把数组的知识分享给大家。



一、数组介绍

 


   数组的定义

 

所谓数组,是指按一行一列或多行多列排列的一组数据元素的集合,数据元素包括:数值、文本、日期、逻辑值和错误值。

 

一千个读者就有一千个哈姆雷特,每个人对数组的理解也不会完全相同,而春风的理解是,Excel里的数组是指多个单元格数据元素的组合。假设某班级里有30个学生,如果班级是数组,30个学生就是数组里的30个元素。放到Excel里,学校就相当于sheet表,班级就是数组,而学生就相当于单元格的数值。

 

   数组的表示

 

数组公式用大括号“{}”进行标识,便于区别于普通的Excel公式。

 

 

   数组的维数

 

数组的维度指数组的行列方向,一行多列的数组为横向数组,一列多行的数组为纵向数组,多行多列的数组则同时拥有纵向和横向两个维度。数组的维数是指在数组中不同维度的个数,像一行或一列这种在单一方向上延伸的数组,称为一维数组,多行多列同时拥有两个维度的数组称为二维数组。

 

一维纵向数组的各元素用半角“;”间隔,我们可以简单地看成是一列单元格数据的集合,比如尺寸为4行×1列的数组“={1;2;3;4}”。一维横向数组的各元素用半角“,”间隔,同样,可以简单地看成是一行单元格数据的集合,比如尺寸为1行×4列的数组“={1,2,3,4}”。

 

 

二维数组可以看成是一个多行多列的数据集合,也可以看成是多个一维数组的组合。如单元格A1:C2,就是一个23列的二维数组。我们也可以把它看成是A1:C1A2:C2两个一维横向数组的组合。二维数组里同行的元素间用逗号,分隔,不同行的元素用分号;分隔。

 

 

从上图中可以看出,在二维数组里,不同行的元素间的分隔符是“;”,所以,要判断一个数组是几行几列的数组,只需要看里面的逗号和分号就知道了。

 

 

二、数组公式

 

 

   数组公式

 

什么是公式?个人理解,在Excel里,凡是以半角符号“=”开始的,具有计算功能的单元格内容就是所谓的Excel公式。如“=SUM(B2:D2)”“=B2+C2+D2”这些都是公式。数组公式与普通公式不同,普通公式只占用一个单元格,只返回一个结果。而数组公式可以占用一个单元格,也可以占用多个单元格,且它对一组数或多组数进行多重计算,并返回一个或多个结果。比如,老师把集合在教室外面的同学叫进教室,老师说“柯镇恶同学进教室”,于是柯镇恶走进教室,老师就这样挨个叫学生进入教室,一个座位叫一次,就像一个单元格输入一个公式,这就是普通公式的处理方法。接着老师叫“全真七子进教室”,七位同学一起进入教室,这是数组公式的处理方法。

 

   输入数组公式

 

如果需要把数组输入到单元格区域里,首先得看数组是几行几列,然后再选择相应的单元格区域,输入公式后按“Ctrl+Shift+Enter”组合键结束公式(这个很重要!),Excel会自动给公式最外边加上“{}”用于和普通公式区别开来。比如,选中A1:A4单元格,在编辑栏输入:={1;2;3;4}后,“Ctrl+Shift+Enter”组合键结束公式,这样一来,一个一维数组就被输入到工作表的单元格里了。

 

 

完成公式的输入后,不能单独改变、移动、删除数组公式区域的某一部分单元格,也不能在该区域中插入新的单元格,否则会弹出“无法更改部分数组”的对话框。

 

 

三、数组运算

 

 

在对数组公式有了一个简单的了解之后,我们将通过一个简单的例子来进一步认识数组公式与常规公式。

 

比如,需计算下图中每件商品的销售金额。很简单,在D2单元格输入公式“=B2*C2”,下拉公式即可。我们试着用数组公式来解决这个问题,选中D2:D4输入公式“=B2:B4*C2:C4”,按“Ctrl+Shift+Enter”组合键结束数组公式,即可得到同样的结果。这就是一个多单元格的数组公式,它可以进行批量计算,以节省计算的时间。

 

 

在对数组的计算有了基本的认识后,下面我们进行相同维数和不同维数的数组运算。

 

   相同维数数组运算

 

相同维数的数组运算,要求数组的大小必须一致,否则运算结果的部分数据将返回“#N/A”错误。

 

比如,要查找研发部门杨过的工号,只需要选择H5单元格,在编辑栏中输入“=INDEX(E3:E12,MATCH(H3&H4,B3:B12&C3:C12,0))”,按“Ctrl+Shift+Enter”组合键即可在H5单元格中返回该员工的工号。

 

 

公式中连接了两个一维区域进行引用运算,如“B3:B12&C3:C12”,生成同尺寸的一维数组,再利用MATCH函数进行定位判断,返回查询员工在该区域中的位置序号,即6,然后使用INDEX函数在E3:E12单元格区域中返回第6行的员工工号信息。

 

   不同维数数组运算

 

不同维数的数组运算可以分为一维数组、一维数组和二维数组以及二维数组之间的运算。计算不同维度的一维数组时,如1行×3列的水平数组与4行×1列的垂直数组,它们将生成新的4行×3列的二维数组,如选择A9:C12单元格区域,在编辑栏中输入“=A4:A7&B1:D1”。按“Ctrl+Shift+Enter”组合键即可用两个一维数组生成一个二维数组。

 

 

可见,单列数组与单行数组的计算结果是返回一个多行多列的数组,返回数组的行数同单列数组的行数相同,列数同单行数组的列数相同。如果要返回数组中第R行第C列的元素,就等同于返回单列数组第R个元素和单行数组第C个元素的运算结果。

 

计算一维数组与二维数组时,它们在相同维度上的元素个数必须相等,否则结果将出现“#NA”错误。比如,需要计算下图中各班的综合评分,综合评分为评分标准对应的分数乘各科权重的和。选中E11单元格,在编辑栏中输入公式“=SUM(B$3:D$3*SUMIF(A$10:A$13,B$4:D$7,B$10:B$13)*(A$4:A$7=D11))”,按“Ctrl+Shift+Enter”组合键结束公式,即可计算出六年一班的综合评分,选中E11单元格,下拉填充至E14单元格,即可计算出其他班级的综合评分。

 

 

下面我们以E11单元格的计算过程为例,说明函数的运算过程。本例中使用了两个函数,求和函数sum,条件求和函数sumif。公式中“B$3:D$3”生成了一个由各科目权重值组成的1行×3列的一维数组,“SUMIF(A$10:A$13,B$4:D$7,B$10:B$13)”生成了一个由4个班级中各科目评分标准值组成的4行×3列的二维数组,二者相乘即生成了一个由4个班级中各科目的综合评分组成的4行×3列的二维数组,“A$4:A$7=D11”可以的得到由TRUEFALSE组成的4行×1列的一维数组,其中FALSE代表0TURE代表1,它们与前面的计算结果相乘后生成了一个4行×3列的二维数组,最后对相乘完的二维数组进行求和运算就得到了所需的结果。

 

我们从数组介绍、数组公式、数组运算三大方面介绍了数组,相信大家以后看到等号外面带“{}”的公式就不会再陌生了,关于数组的应用方法就介绍到这,聪明的你有什么别的想法,欢迎留言。

 

本文配套的练习课件请加入QQ群:1003077796下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

SUMPRODUCT数组公式加了*的 SUMPRODUCT函数无所不能

INDEX函数的应用INDEX:函数中的精确制导导弹,最强大的瘸子

求和函数汇总求和,我是认真的(Excel函数教程)

MATCH函数的应用《MATCH:函数哲学家,找巨人做伴。新出道必学!