Excel函数公式:含金量超高的常用万能公式,你必须掌握
Excel中,函数公式的威力强大是众所周知的,但是,好多人还是应用不好,今天,我们对常用的万能公式,做一下讲解,希望对大家的工作有所帮助。
一、多条件计数统计——Countifs函数。
目的:计算销往“北京”地区且销量大于30的男销售员人数。
方法:
1、在目标单元格中输入公式:=COUNTIFS(C3:C9,">30",D3:D9,"男",E3:E9,"北京")。
解读:
1、Countifs函数为多条件计数函数。
2、语法结构:=Countifs(条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。
二、多条件求和统计——Sumifs函数。
目的:计算销往“北京”地区且销量大于30的男销售员销量之和。
方法:
1、在目标单元格中输入公式:=SUMIFS(C3:C9,C3:C9,">30",D3:D9,"男",E3:E9,"北京")。
解读:
1、Sumifs函数为多条件求和函数。
2、语法结构:=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。
三、多条件查找引用函数——Lookup函数。
目的:查询销售员在相应地区的销量。
方法:
1、在目标单元格中输入公式:=LOOKUP(1,0/(($B$3:$B$9=$H$3)*($E$3:$E$9=$I$3)),C3:C9)。
解读:
1、此用法为Lookup函数的变异用法。首先比较$B$3:$B$9=$H$3是否成绩,形成一个数组,然后比较$E$3:$E$9=$I$3是否成立,形成一个数组,最后两个数组相乘形成一个新的数组,条件同时成立的返回1,否则返回0。0除以1返回0,0除以0返回错误,查找值1和运算结果比较,之后0相近,所以返回0所在行的值。
2、语法结构:=Lookup(1,0/((条件范围1=条件1)*(条件范围2=条件2)……(条件范围N=条件N)),返回值范围)。
四、一对多查询实用技巧——Index+Small+If+Row函数的组合应用。
目的:查询销售员销往的地区。
方法:
1、在目标单元格中输入公式:=INDEX(E:E,SMALL(IF(B$3:B$9=H$3,ROW($3:$9),4^8),ROW(A1)))&""。
2、Ctrl+Shift+Enter填充。
3、拖动填充柄填充。
解读:
1、首先判断B$3:B$9=H$3是否成立,返回一个由True和False构成的数组,然后由ROW($3:$9)返回3,4,5,6,7,8,9,然后由IF函数判断,如果B$3:B$9=H$3成立,则返回对应位置上的ROW($3:$9),否则返回4^8。
2、用Small函数返回第一个最小的值,以此类推。
3、用Index返回E列上对应位置的值。
4、目前Excel中最大的行数为4^8行,所以在此处用4^8进行辅助运算。
结束语:
上文中的4个万能函数,对于计数、求和、查询以及一对多查询进行了演示和系统的分析,希望对大家今后的工作有所帮助。
学习过程中遇到困难可以在留言区留言讨论哦,同时欢迎大家交流学习经验和心得!
-
Origin(Pro):学习版的窗口限制【数据绘图】 2020-08-07
-
如何卸载Aspen Plus并再重新安装,这篇文章告诉你! 2020-05-29
-
AutoCAD 保存时出现错误:“此图形中的一个或多个对象无法保存为指定格式”怎么办? 2020-08-03
-
OriginPro:学习版申请及过期激活方法【数据绘图】 2020-08-06
-
CAD视口的边框线看不到也选不中是怎么回事,怎么解决? 2020-06-04
-
教程 | Origin从DSC计算焓和比热容 2020-08-31
-
如何评价拟合效果-Origin(Pro)数据拟合系列教程【数据绘图】 2020-08-06
-
Aspen Plus安装过程中RMS License证书安装失败的解决方法,亲测有效! 2021-10-15
-
CAD外部参照无法绑定怎么办? 2020-06-03
-
CAD中如何将布局连带视口中的内容复制到另一张图中? 2020-07-03