简单实用的Excel函数公式用法

一、多区间判断

如下图所示，要根据 D 列销售金额给出对应的等级。

规则为：50000 以上级别为 A，30000 以上级别为 B，其他为 C。

E2 单元格公式为：

=IFS(D2>50000,"A",D2>30000,"B",TRUE,"C")

IFS 函数的常用写法为：=IFS(条件 1,结果 1,条件 2,结果 2……TRUE,所有条件都不符合时返回什么)

二、 条件计数

如下图，要统计薪资标准在 8000 以上的人数占比。

=COUNTIF(E:E,">8000")/COUNT(E:E)

先使用 COUNTIF(E:E,”>8000″)统计出 8000 以上的个数，然后用 COUNT(E:E)计算出 E 列中的所有数值个数，二者相除得到占比。

三、筛选状态下求和

SUBTOTAL 函数仅对可见单元格汇总计算，能够计算在筛选状态下的求和。

如下图，对 B 列的部门进行了筛选，使用以下公式可以计算出筛选后的数量之和。

=SUBTOTAL(9,F2:F14)

SUBTOTAL 第一参数用于指定汇总方式，可以是 1~11 的数值，通过指定不同的第一参数，可以实现平均值、求和、最大、最小、计数等多种计算方式。

如果第一参数使用 101~111，还可以忽略手工隐藏行的数据，小伙伴们有空可以试试。

四、数据查询

如下图所示，要根据 G1 的部门，在 A 列查询该部门，并返回 B 列对应的负责人姓名。

如果你使用的是 Excel 2021 或者是 Office 365，可以使用这个公式：

=XLOOKUP(G1,A2:A11,B2:B11)

第一参数是查询的内容，第二参数是查询的区域，查询区域只要选择一列即可。第三参数是要返回哪一列的内容，同样也是只要选择一列就可以。

公式的意思就是在 A2:A11 单元格区域中查找 G1 单元格指定的部门，并返回 B2:B11 单元格区域中与之对应的姓名。

五、限制录入重复内容

1、选中要输入数据的 A2:A10 单元格区域，【数据】→【数据验证】

2、允许类型选择“自定义”，公式为：

=COUNTIF(A:A,A2)=1

六、突出显示重复数据

1、选中已录入数据的 A2:A10 单元格区域，新建格式规则

2、使用公式：=COUNTIF(A$2:A2,A2)>1

3、设置突出显示的格式