1、多区间判断
如图1所示,要根据D列销售金额给出对应的等级。
规则为:50000以上级别为A,30000以上级别为B,其他为C。
E2单元格公式为:
=IFS(D2>50000,"A",D2>30000,"B",TRUE,"C")
IFS函数的常用写法为:
=IFS(条件1,结果1,条件2,结果2……TRUE,所有条件都不符合时返回什么)
2. 条件计数
如图2,要统计薪资标准在8000以上的人数占比。
=COUNTIF(E:E,">8000")/COUNT(E:E)
先使用COUNTIF(E:E,">8000")统计出8000以上的个数,然后用COUNT(E:E)计算出E列中的所有数值个数,二者相除得到占比。
3、筛选状态下求和
SUBTOTAL函数仅对可见单元格汇总计算,能够计算在筛选状态下的求和。
如图3,对B列的部门进行了筛选,使用以下公式可以计算出筛选后的数量之和。
=SUBTOTAL(9,F2:F14)
SUBTOTAL第一参数用于指定汇总方式,可以是1~11的数值,通过指定不同的第一参数,可以实现平均值、求和、最大、最小、计数等多种计算方式。
如果第一参数使用101~111,还可以忽略手工隐藏行的数据,小伙伴们有空可以试试。
4、数据查询
如图4所示,要根据G1的部门,在A列查询该部门,并返回B列对应的负责人姓名。
如果你使用的是Excel 2021或者是Office 365,可以使用这个公式:
=XLOOKUP(G1,A2:A11,B2:B11)
第一参数是查询的内容,第二参数是查询的区域,查询区域只要选择一列即可。第三参数是要返回哪一列的内容,同样也是只要选择一列就可以。
公式的意思就是在A2:A11单元格区域中查找G1单元格指定的部门,并返回B2:B11单元格区域中与之对应的姓名。
5、限制录入重复内容
1、选中要输入数据的A2:A10单元格区域,【数据】→【数据验证】
2、允许类型选择“自定义”,公式为:
=COUNTIF(A:A,A2)=1
6. 突出显示重复数据
1、选中已录入数据的A2:A10单元格区域,新建格式规则
2、使用公式:
=COUNTIF(A$2:A2,A2)>1
3、设置突出显示的格式