院长大大丨图文
教程基于Excel 2016
Excel数组,对于刚入门Excel的小伙伴来说,熟悉又陌生。
熟悉是因为经常会听到老司机提起这个词,陌生是因为你根本不会用,看起来也似懂非懂。通过数组计算一步做出九九乘法表,你会吗?
随着Excel函数公式的不断完善,数组的应用越来越弱,但如果不理解数组的含义,你就不能真正学好函数公式!
今天,院长给大家介绍的是Excel数组入门应用,解开你的难题。
1. 数组是什么
数组是指按一行、一列或多行多列排列的一组数据元素的集合。数据元素可以是数值、文本、日期、逻辑值和错误值。
理解不了?没关系。我们来看下面三个例子。
A1:D1区域,输入【={1,2,3,4}】
A1:A4区域,输入【={1;2;3;4}】
A1:B2区域,输入【={1,2;3,4}】
通过【Ctrl】 【Shift】 【Enter】确定输入,效果分别对应下面三个图。
图1
图2
图3
从三个图中,我们可以发现,同一行元素用【,】分开,元素换行时,用【;】分开。
在了解数组基本组成后,我们来看看数组到底有什么用。
2. 数组的应用
以下案例,我们需要对单笔业务成交金额、总利润、销售员销售金额进行计算,并找出产品品类数量,根据销售员姓名查询对应工号。
那么,通过数组的应用,我们如何快速实现上述的要求?
2.1 成交金额计算
如第一笔业务,单元格I2插入公式【=G2*H2】即可得到单笔业务的成交金额,然后填充公式实现全部业务的成交金额计算。
通过数组计算,我们可以更快速地实现。
选中I2:I21,插入公式【=G2:G21*H2:H21】,通过【Ctrl】 【Shift】 【Enter】确定输入,即可得到全部业务的计算。
我们看看具体的计算结果。数组公式计算出20个由G列和H列相乘的结果,依次填充到I2:I21区域,如下图:
2.2 总利润计算
在不计算每笔业务的成交金额的前提下,我们也可以通过每笔业务的单价与数量,使用数组公式,计算出总利润。
单元格L3插入公式【=SUM(G2:G21*H2:H21)*L2】,通过数组计算,即可一步得到总利润。
2.3 销售员销售金额合计
计算各销售员在北京销售金额合计,同样也可以使用数组公式进行计算。条件有两个:城市和销售员,城市固定为北京,销售员随之变化。
单元格L8插入公式【=SUM(($E$2:$E$21=K8)*($C$2:$C$21=L$6)*$I$2:$I$21)】,通过数组计算,则可得出张颖在北京的销售总额。
公式中包含两个条件,【($E$2:$E$21=K8)】代表销售员为张颖,【($C$2:$C$21=L$6)】代表城市为北京,同时满足两个条件,再对I2:I21区域进行求和计算。
2.4 计算产品品类数量
数组公式,还可以配合COUNTIF函数,用1去除的小技巧,计算出产品品类数量。
单元格L17插入公式【=SUM(1/COUNTIF(F2:F21,F2:F21))】,计算出每个产品名称出现的次数,形成数组。
用1去除,产品名称出现1次为1,出现2次为0.5,出现3次为0.33,再把数组相加,即可得到产品品类数量。
2.5 VLOOKUP函数的逆向查找
我们在使用VLOOKUP函数都知道,查找的依据列必须在前,但通过数组公式,可以使VLOOKUP函数实现逆向查找。
案例中,我们需要通过销售人员姓名查找工号,显然,不满足VLOOKUP函数的要求,因为在源数据中,姓名列在工号列后面。
单元格L21插入公式【=VLOOKUP(L20,IF({1,0},E2:E21,D2:D21),2,0)】,通过公式中【IF({1,0},E2:E21,D2:D21)】重构D、E两列,实现VLOOKUP函数的逆向查找。
好了,今天的教程就到这里了。院长祝大家周末愉快!
如果你有更多的方法,可以在文章底部留言哦~么么哒~
联系客服