Excel函数多,公式有时又比较复杂、难懂,看到别人用得娴熟,快速解决了一个又一个问题,心里往往十分羡慕。其实,想要成为EXCEL函数和公式的应用高手,是有一个捷径可以走的。有不少公式不需要理解,直接套用就行,你只要花点功夫记住这些通用公式,到时,不但可以高效完成工作,还可以在同事、朋友面前装逼、得瑟一番。
今天,我总结了第1组万能的通用公式分享给大家。
1、多条件计数公式
EXCEL函数公式模板:=Sumproduct(条件1*条件2*条件3...)
示例:统计各班级“上海籍”“女生”人数。在下表,H3单元格输入公式:
=SUMPRODUCT(($A$2:$A$18=G3)*($C$2:$C$18="女")*($D$2:$D$18="上海")),向下复制即可。
2、多条件求和公式
EXCEL函数公式模板:=Sumproduct(条件1*条件2*条件3... , 数据区域)
示例:统计“各班级”“上海籍”“女生”三门总分之和,三门平均值。在下表,K3单元格输入公式:
=SUMPRODUCT(($A$2:$A$18=J3)*($C$2:$C$18="女")*($D$2:$D$18="上海"),$E$2:$E$18)
注:条件求和也可用sumifs函数,但Sumproduct可以对数组处理后再设置条件,同时也可以对文本型数字进行计算,而Sumifs函数则不可以。
3、多条件判断公式
EXCEL函数公式模板:=IF(AND(条件1,条件2...条件n),同时满足条件返回值,不满足条件返回值)
公式模板:=IF(OR(条件1,条件2...条件n),满足任一条件返回值,不满足条件返回值)
示例:三门总分低于300分,数学低于100分,标注“要补习”。在下表,G2单元格输入公式:
=IF(AND(F2<300,E2<100),"要补习","")
4、多条件查询公式
EXCEL函数公式模板:Lookup(1 , 0/((条件1*条件2*条件3...)) , 返回值区域)
示例:根据班级、姓名,查学生数学成绩。在下表,S3单元格输入公式:
=LOOKUP(1,0/(($A$2:$A$18=Q3)*($B$2:$B$18=R3)),$E$2:$E$18)
5、多条件排序公式
EXCEL函数公式模板:=SUMPRODUCT((条件1*条件2*条件3...)*(参照区域>=拟排序数值))
示例:计算三门总分在本班中的排名。在下表,H2单元格输入公式:=SUMPRODUCT(($A$2:$A$18=A2)*($F$2:$F$18>=F2))
6、条件求均值公式
EXCEL函数公式模板:=SUMIF(条件区域, 条件值, 求和区域)/COUNTIF(条件区域, 条件值)
示例:计算各班数学、三门总分平均分。在下表,以计算数学各班均分为例,在W3单元格输入公式:
=SUMPRODUCT(($A$2:$A$18=A2)*($F$2:$F$18>=F2))
7、计算不重复值个数的公式
EXCEL函数公式模板:=SUMPRODUCT(1/COUNTIF(区域 , 区域))
示例:计算学生籍贯地数量。在下表,在Z2单元格输入公式:
=SUMPRODUCT(($A$2:$A$18=A2)*($F$2:$F$18>=F2))
每天学习一点,每天进步一点,积硅步,必能致千里、得大成。