excel数组的学习和应用，笔记3，权亮

1 两列数据之差的和计算
计算下列两列数据相减后的和.
解答:我们一般计算这样的方法是b2-a2,得出来的结果,向下复制,就得到了各列之差,最后我们在下面输入sum函数即可求出来.
我们用数组的方法,可以直接b2:b11-a2:a11,求出来对应的差.但是方法有个前提条件,就是提前选中要被求差结果的单元格,如果只选择b-a列的第二个单元格,他不会全部都求出,必须全部选出来,另外一个条件是数组的计算必须按ctrl+shift+enter才可以出结果.


我b-a列选中了所有等待求值的单元格,然后我只输入了一个等式,然后点击ctrl+shift+enter就得到下面图的结果了.
最后我们使用sum函数就能求出这一列的值.

既然数组这么方便,那我们是不是可以直接求出来这个求和运算.也就是在数据运算前面直接价格sum函数.

数据运算大大方便了我们的运算速度,过去我们需要求出每一行,再求和.现在可以直接使用数组相减求和/

2 判断身份证长度

提示:上面的身份证有位数长度正确的,不正确的,我们要找出来.
身份证长度18,过去有的人身份证长度15位.
Or,and函数无法对数组进行使用,因为这两个函数只能返回一个值,true或者false.可以用+*来代替.
+代表or,*代表and.这是我反复试验无数次,反应过来,然后百度后知道的结论,希望大家不要走进这儿误区.也就是说+*和and,or不完全相当.
解答:
过去我们的方法我们可以使用len函数,or,and函数判断这个身份证是不是正确的长度.然后再求出所有的结果
现在我们用if函数,数组直接求出来.
公式解释一下,b2:b11的值如果等于15或者等于18其中一个,则返回true,并且显示true,否则返回false,并且显示false.

3 多条件求和
我们要求一车间男性的工资的和.前提是两个条件,一车间和男性.

解答:
使用sum函数,如下图.,我们可以看到是三个数组相乘,这三个数组第一个数组是某一个数组等于一车间,第二个数组是等于男,第三个数组是工资数.有人会说,前两个数组相当于筛选,可是这三个数组的相乘怎么理解呢?
第一个数组相当于返回的是一个{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}的数组.第二个数组相当于返回的是{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE},前两个数组相乘结果是{1;0;1;0;0;0;0;0;0},再相乘第三个数组,相乘我们就得到. {2400;0;2000;0;0;0;0;0;0},最后求和.得到我们想要的函数.

4 判断是否退休, 男士：60岁及以上；女式：55岁及以上。

解答:
提示这个题我们要求的是,男性大于60,女性的话大于55的人.
我们使用数组直接进行求.

解释一下,这个公式,性别列等于男的,且年龄列大于等于60的人或者性别列等于女的,且年龄大于等于55的人,只要满足一个条件,就返回1,否则返回0.
为了显示准确,我们可以用if来标识是否退休.

5 使用sumproduct函数求三个品牌的销售数量

提示:sumproduct函数格式为SUMPRODUCT(array1, [array2], [array3], …).这个函数是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和.
这个函数的功能及其强大.
解答:我们了解到这个函数是对不用列的乘积求和,如果我们只有一列,那就意味着只是求和.
如图

先对于品牌进行筛选,($A$2:$A$12=F2)结果是TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE.然后和$B$2:$D$12进行相乘,结果为{39,75,38;0,0,0;10,71,80;0,0,0;0,0,0;13,87,60;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}.最后进行求和,得到我们想要的结果.

我们观察到,sumproduct函数在只有一列的时候,其实和sum函数几乎一样作用.

6 利用large函数进行从大到小”行排序”.
提示:large函数:格式为 LARGE(array,k),LARGE 函数语法具有下列参数：Array必需。 需要确定第 k 个最大值的数组或数据区域。K 必需。 返回值在数组或数据单元格区域中的位置（从大到小排）
如果进行排序,我们可以利用k从大到小进行显示.

我们观察第一行数据,如果希望从大到小的排序,我们就要将第一大的数字放在m列,第二大的数字放在n列,以此类推,最小的放在s列.
Column函数:格式为column（reference) ,如果省略reference，则默认返回函数column所在单元格的列数.
解答:

使用large函数,首先我们是以行进行排序,那我们需要固定单元格的列名,之后需要确定排名,m列的是第一,n列第二,直到最小的放在s列.我们使用column函数,column()确定m列的是13,那么我们-12,就是1,n列是14,同理是2,以此类推

7 利用frequency函数计算频率.

提示:frequency函数:格式为FREQUENCY(data_array, bins_array)
data_array 必需。 要对其频率进行计数的一组数值或对这组数值的引用。 如果 data_array 中不包含任何数值，则 FREQUENCY 返回一个零数组。
bins_array 必需。 要将 data_array 中的值插入到的间隔数组或对间隔的引用。 如果 bins_array 中不包含任何数值，则 FREQUENCY 返回 data_array 中的元素个数
解答:

输入参数后,结果如.

8 Sumproduct和sumifs的进阶使用

提示:sumifs函数中的各个条件之间是且,and的关系.如果想表达or,或者的关系,就要使用加法.
两个数组相乘等于两个数组想对应的参数相乘.
结果如下

公式如下: