工具癖Excel

这些常用的表格公式,90%的人竟然都不知道

2019-06-04  本文已影响21人  章小余

在日常的工作中,我们难免会碰到一些需要处理分析数据的工作,财务、人力、运营等就更不用说,几乎天天都要跟各种数据打交道。

数据处理作为一项专业性较强的工作,需要掌握一定的专业技能,如果单纯依靠手工,那么工作效率低、经常加班,说的就是你无疑了。有些常用的表格公式,竟然有90%的人都不知道。

今天,给大家分享一组石墨表格中的公式使用技巧,帮助大家摆脱数据处理的低效,一步步成长为别人眼中的“数据大神”。

01

 自动求和、求平均值 

日常数据处理中,我们经常需要用到求和或求平均值等基本操作。在石墨表格里,我们只需要插入相关公式、下拉填充、双击修改引用区,自动求和、求平均值等操作就可以一气呵成。

如果你觉得公式操作太复杂,那还有一个更快捷的方式!在石墨表格里选中数据时,点击右下角的两个小三角符号,就可以直接看到数据的总和、平均值等信息。

02

 跨 sheet 引用公式 

当统计表中需要分别引用计算其他工作表中的数据时,我们就需要用到跨 sheet 引用公式,该如何操作呢?

方法1:输入公式后,直接点击其他sheet ,选择好数据后回车确定。

方法2:输入公式,然后再输入要引用的工作表名称与单元格,回车确定。如 =MIN('1班'!K4:K15)。

03

 STDEV 求标准差 

假如我们要从以下 A、B 两个小组中评出一个最佳团队,计算后发现两个小组的成单总量和平均量都相同,那两个小组谁更加优秀呢?

这时候就要用到 STDEV 标准差函数,标准差反映了数据的离散程度,计算结果越小,表明团队的凝聚力更强。所以,最终我们将 A 组将被评为最佳团队。

04

SUBTOTAL 搞定所有数据汇总 

SUBTOTAL 是非常强大的汇总函数,包含了平均值、计数、最大最小、相乘、标准差、求和、方差等,并可以根据不同筛选条件来展示不同的计算结果。当表格有筛选时,极力推荐大家使用该函数。

05

COUNTIF 和 SUMIF 按条件求和

在财务人员的日常工作中,经常需要对数据进行有条件的统计,COUNTIF 和 SUMIF 就是两个常用的公式。

比如要统计工龄超过 3 年的人数,可以使用 COUNTIF 函数,用于统计满足某个条件的单元格数量;如果要统计要薪资超过 5 千的员工薪水总和, 可以用 SUMIF 函数,用于对符合指定条件的值求和。

06

 IF 判断是否重复 

IF 判断公式,它的作用是设置条件判断是否满足该条件,如果满足则展示 A,不满足则展示 B。该公式还可以与多种函数嵌套使用来达到不同的效果。

下图中,我们需要判断该表中的 “影片” 是否重复,这里 COUNTIF 是作为 IF 的判断条件,如果 COUNTIF 的计数结果大于1,则显示重复,如果不满足条件则显示不重复。

下图中,我们需要判断候选人是否达到录取资格,如果 “面试成绩” 和 “笔试成绩” 均满足大于85分的条件,则生成结果 “录取”,否则 “不予录取” 。

07

VLOOKUP 匹配功能强大

VLOOKUP 是功能强大的匹配函数,它可以用一个参考值与整个表进行匹配。需要注意的是查找的参考值应该始终位于所在区域的第一列,否则无法正确计算。

下图中,我们需要根据航空公司代码匹配出航空公司全称,航空公司代码参考值位于A2,航司列表在该表第二个 sheet "航司" 中,那么跨 sheet 查找区域应为 "航司!A:B" ,需匹配的结果位于第二列,故起始行应为 "2" ,精确查找的指令为0。写好公式:=VLOOKUP(A2,航司!A:B,2,0),双击填充柄,其他航空公司的全称也自动填充到表格中。

08

黄金搭档 INDEX 和 MATCH 

VLOOKUP 函数也不是万能的,如果遇到反向查找、双向查找等比较复杂的操作时使用 VLOOKUP 函数就会不太方便。

下图中,如需要根据销售额来匹配员工姓名,此时就不适合用 VLOOKUP,因为这里 VLOOKUP 的查找范围应以查找值(销售额)所在单元格开头,即应以 D 列或 F 列开头,但员工姓名位于 A 列,显然不适合。

这种情况下更适合使用的是 INDEX + MATCH 这对组合函数。MATCH 确定查询内容和查询的区域 D 列,返回相应销售额所在的行,INDEX 指定匹配查询的区域 A 列,匹配出行值和列值交叉确定的数据。

上一篇下一篇

猜你喜欢

热点阅读