人力资源报表中Excel单元格的处理方法

大家好，感觉又有段时间没有分享技能技巧了，前段时间一直忙着给客户录制图表课程以及启动新课程《数据透视表》的录制准备，所以耽搁了些。

今天要给大家分享的是在最近一对一的培训过程中，学员遇见的问题，我就捡中间的三个小问题来讲解一下。

绝对引用和相对引用

这其实是个老生常谈的问题，而且是新手最容易搞不清的地方，培训的时候，就有小伙伴经常会问我，什么时候该用绝对引用，什么时候该用相对引用。

先看案例：

她要的目的是根据左侧岗位编号，对简历编号进行自动编号以避免人工编号的错误概率，当左侧是TD-023的时候，右侧简历编号就是TD-023-1这种自动计数的方式。

根据她的描述，很明显这个公式中肯定要用到连接符&，唯一稍微需要点技巧的就是最后的这个编号。一起看看公式：

公式为：=A2&"-"&COUNTIF($A$2:A2,A2)

Countif容易理解，条件计数，这个公式写好之后，直接进行公式的拖拽填充就可以实现她要的结果了。

之前我也跟大家强调过，$这种符号就是绝对引用，你就把它想象成一个钉子，把那个单元格给固定住，也就是你随便怎么拉动鼠标填充，这个都不会改变，所以当你往下填充的时候，第二个单元格就变成了：=A3&"-"&COUNTIF($A$2:A3,A3)

也就是说计数的范围是逐步增加的，如果你一开始就全部用了绝对引用，那么计数的结果永远都是一个数值，不会有任何改变。大家可以自行再理解一下。

求和

在说到求和，之前在我的基础视频课程Excel从起步到起飞（收费）中有一篇《你会求和么？》介绍过一些高级的关于求和的玩法，来看看今天的案例：

先不说求和，其实她对这个表格在事先设计的时候就应该避免出现这种情况，因为在美观度上我是完全不认同这种的方式的，而且对后面的求和公式也会略显复杂。那么如何避免出现这种情况呢？必备函数IFERROR完全可以避开这种，如：

公式为：

=IFERROR(VLOOKUP(A2,'C:UsersdemonDesktop[sample.xlsx]招聘岗位及进度汇总'!$B$5:$Y$207,24,0),0)

这个公式理解也非常简单，=IFERROR(条件判断结果，如果前面不成立就是我了)

第一个参数：条件判断结果，成立就直接取值；

第二个参数：条件不成立时要返回的结果；

最后再来进行求和汇总的时候，可以直接用SUM函数就可以很轻松的搞定了。

但是，我们一般会多想一点，如果客户要求说我就要这样，必须要保留这种样式，那么这种直接用SUM进行求和就得不到我们要的结果了，如：

这种情况该怎么办呢？之前给大家介绍过有个符号是可以将文本进行强制转换成数值的，就是“--”，在文本前加上两个负号，我们小学都学过负负得正。另外，在基础课程中也给大家介绍过一个函数是：ISNUMBER()，判断单元格中的内容是不是数值，是的话返回1，不是的话返回0。

所以我们看看利用上述两个该怎么写公式：

公式为：

=SUM(IF(ISNUMBER(--B2:B204),B2:B204))

这个公式是用了数组公式的方式，不是office365版本的小伙伴，写完之后需要用到Ctrl+Shift+Enter来结束公式的输入。

理解一下：首先将B2到B204中的内容强制转换成数值，然后再挨个判断B2到B204单元格中的内容是否是数值，因为一旦是文本，你再怎么转换它还是个文本，但是如果是数字的文本形式，通过--是可以将其转换成数值的。外面的IF函数就很容易理解了，得到的结果就是0，以及真正返回的数值，最后套个SUM进行求和，就得到了最终的结果。

单元格强制输入短日期格式

看案例：

给到别人填写你的表格的时候最忌讳的就是大家填的格式千奇百怪，尤其是日期，有人喜欢2020/9/16这种。有人喜欢2020.9.16这种，还有的喜欢2020-9-16这种，但是Excel有可能将2020.9.16这种识别为文本格式，因此在后期计算的时候会给你造成公式不畅的后果。如何让填表的人按照你的方式填写呢？

这种首先想到的是数据验证，也就是通过数据有效性进行设置，输错了就弹出警告框。

第一步，给要输入短日期的单元格定义单元格格式，如：