返回目录:excel表格制作
1 数组公式与多条件判断and函数
数组公式可以认为是Excel对公式和数组的一种扩充,换一句话说,是Excel公式在以数组为参数时的一种应用。
数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。
如有以下数据:
姓名 | 面试官1 | 面试官2 | 面试官3 | 是否被录取 |
求职人员1 | 合格 | 合格 | 不合格 | FALSE |
求职人员2 | 合格 | 不合格 | 合格 | FALSE |
求职人员3 | 合格 | 不合格 | 不合格 | FALSE |
求职人员4 | 合格 | 合格 | 合格 | TRUE |
求职人员5 | 合格 | 合格 | 合格 | TRUE |
对于求职人员需要由三个面试官判断全部合格,才可以录用,可以应用数组公式和多条件判断。
“是否被录取”一列下面的单元格输入公式如下:
=AND(B2:D2="合格")
在单元格编辑状态下(也就是光标点击进入单元格的情况下(单元格按F2时可进入编辑状态)),按ctrl+Shift+Enter,即可以将上述公式框在{}内,如下所示:
{=AND(B2:D2="合格")}
此时的公式即是数组公式。
数组公式会对数组(或引用的区域)中的每一个元素的值逐一参与计算,如果有n个元素,则会形成n重循环的n个值;
如果是1个数组(或引用的区域),则是对这1个数组(或引用的区域)的每一个元素的值逐一参与计算;
如果是m个数组(或引用的区域),则是对这m个数组(或引用的区域)的每一个元素的值逐一参与计算,每个数组(或引用的区域)先用第一个元素的值参与计算、然后是每个数组(或引用的区域)的第2个、第3个...;
多条件判断的函数除了and()以外,还有or()。
2 逻辑值的数值取值
当单元格逻辑值是"true"时,应用公式时可以转换为数字"1",当单元格逻辑值是"false"时,应用公式时可以转换为数字"0"。
如以下数据:
员工姓名 | 所属部门 | 业绩 | 工龄 | 是否发放奖金 | 发放资金金额 |
员工1 | 销售部 | 14400 | 3 | FALSE | 0 |
员工2 | 人力部 | 18000 | 9 | FALSE | 0 |
员工3 | 人力部 | 252000 | 8 | TRUE | 500 |
员工4 | 销售部 | 324000 | 5 | FALSE | 0 |
员工5 | 工程部 | 324000 | 10 | TRUE | 500 |
员工6 | 工程部 | 36000 | 4 | FALSE | 0 |
员工7 | 人力部 | 372000 | 11 | TRUE | 500 |
员工8 | 销售部 | 432000 | 5 | FALSE | 0 |
员工9 | 销售部 | 14400 | 2 | FALSE | 0 |
员工10 | 人力部 | 16800 | 8 | FALSE | 0 |
员工11 | 工程部 | 36000 | 4 | FALSE | 0 |
同时满足业绩超过30000元以及工龄在5年以上两个条件即可发放500的资金。
“是否发放奖金”一列便可以利用公式:=AND(C2>30000,D2>5)
“发放资金金额”列便可以利用公式:=E2*500
3 数组公式与多条件判断函数、求和函数
如有以下数据:
日期 | 规格 | 金额 |
2017/3/1 | 惠普 | 2654 |
2017/3/6 | 爱普生 | 2780 |
2017/3/3 | 佳能 | 2432 |
2017/2/5 | 爱普生 | 3223 |
2017/2/8 | 爱普生 | 3564 |
2017/2/7 | 佳能 | 1432 |
2017/2/9 | 惠普 | 1987 |
2017/3/5 | 惠普 | 3465 |
2017/3/7 | 爱普生 | 2683 |
2017/3/4 | 佳能 | 2154 |
按时间段统计每种产品销售金额:
时段间 | 惠普 | 爱普生 | 佳能 |
2017/2/10 | 1987 | 6787 | 1432 |
2017/3/10 | 8106 | 12250 | 6018 |
便可在“惠普”列的下一个单元格应用以下公式:
=SUM(($A$2:$A$11<$A15)*($B$2:$B$11=B$14)*($C$2:$C$11))
4 数组公式与sumproduct()函数
sumproduct()函数是指在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。与数组公式有功能相似之处。
如有以下数据:
产品名称 | 销售数量 | 销售单价 | 金额 |
A3打印纸(箱) | 55 | 120 | 6600 |
迷你文件柜 | 68 | 119 | 8092 |
鼠标 | 70 | 99 | 6930 |
A4打印纸(箱) | 45 | 115 | 5175 |
26797 | |||
总销售额 | |||
26797 |
在“总销售额”下的单元格中可以应用以下公式:
=SUMPRODUCT(B2:B5*C2:C5)
如有以下一个月(从1号-31号)的收进、发出的数据,便可以应用SUMPRODUCT()进行一个月的收进、发出的汇总。
整月收进汇总 | 整月发出汇总 | 结存数量 | 1号 | 2号 | …… | ||||
收进 | 发出 | 收进 | 发出 | …… | |||||
58 | 35 | 24 | 55 | 33 | 3 | 2 |
上述“58”的单元格便是应用以下公式:
=SUMPRODUCT((MOD(COLUMN(I5:BR5),2)=1)*I5:BR5)得出的值。
上述(COLUMN(I5:BR5))用于返回某一引用的列号;
(MOD(COLUMN(I5:BR5),2)=1)形成一个逻辑值,逻辑值的"true"对应数值"1",逻辑值的"false"对应数值"0"。
这样与区域I5:BR5的每一个单元格的列号相对应,由(MOD(COLUMN(I5:BR5),2)=1)形成一个逻辑值数组,或一个0、1序列的数组,用这种间接的方式,可以对每天收进的数值或行汇总。