01 数据源要准确又精细

肖敏刚接触Excel的时候，表格做得特别随意，以致于后续做数据统计时常常出现统计数据不准确、表格筛选不精确、数据透视无法呈现想要的结果等一系列问题。经过长期的摸索和向前辈请教，她终于慢慢养成了良好的做表习惯，为后面高效处理数据打下了坚实的基础。

肖敏在办公桌上小心翼翼地贴上了刚打印出来的小提示——做表好习惯：

1. 切记及时保存（Ctrl+S），以防万一；

2. 充分利用好各个工作表（sheet），数据源、数据加工、统计报表分开；

3. 各个工作表（sheet）之间有逻辑关系的数据要随时进行校验。

接下来，肖敏需要开始准备工资发放的一系列工作了。

肖敏打开邮箱，开始处理本月的奖金数据：集团总部50名员工中，有20人因为不同的原因须发放奖金。但是由于部门、奖金类别、奖金金额都不一样，奖金的发起人和审批人也不同，她需要从各封邮件中将奖金整理和统计出来。

几年前，肖敏习惯于这样整理数据，但这样做会带来如下问题：

（1）姓名居中，两字人名中间有空格，难以搜索

很多人都会遇到这种情况，有时是因为从邮件中把数据复制粘贴过来之后发生了格式变动；有时是因为数据提供方手动增加了空格；有时是制表人为了让所有人名对齐，特意将两字人名中加入空格，这样带来的最大困扰是当我们需要搜索某个人的时候，无法直接输入名字进行搜索，在进行条件查找等操作时，查找条件中也需要输入同样数量的空格，这在无形中增加了工作难度，也降低了工作效率，如下图所示。

遇到这样的情况，我们可以用“Ctrl+H”，在“查找和替换”对话框中的“查找内容”中输入空格，点击“全部替换”，就可以把所有空格去掉了。

如果仍然希望名字对齐显示的话，也可以将空格去掉之后，选择姓名列，点击鼠标右键选择“设置单元格格式”，在“水平对齐”下拉菜单中选择“分散对齐（缩进）”即可（在本书第六章会介绍相应的公式，来帮助大家处理类似问题）。

（2）奖项和金额全部罗列，无法分类统计

例如，工作突出奖500元、重大贡献奖2,000元、专利奖2,000元，虽然描述清晰准确，但在Excel表格中这样描述实在浪费，导致“奖项名称”字段无法作为统计口径进行分类，“奖项金额”的数字也无法直接进行统计计算（具体统计方法将在下文中体现）。

（3）需要手动计算数据

从上面表格中不难看出，虽然各个小计和奖金总计数据是准确的，但制表时需要单独手动计算，然后将数据填写到对应单元格中，而Excel强大的计算功能并没有得到很好的发挥。

（4）只记录姓名，不记录工号，易造成信息混淆

我们在对员工信息和数据进行统计分析时，往往需要为每个人设置一个工号作为“唯一识别字段”。这就像我们的身份证号码一样，它是唯一的、不与他人重复的、能与他人完全区分的信息。

而在员工数据处理中，只记录姓名不记录工号这种习惯本身并不算是错误，只是有些不够严谨。中国人同名同姓者人数众多，随着公司的发展和人员流动，很容易出现公司内部甚至部门内部存在同名同姓人员的情况。在后续的数据统计中会由于姓名无法起到识别作用，从而造成一定的困扰，影响我们的工作效率。

肖敏一般也不会采用员工的身份证号来作为“唯一识别字段”。因为一方面，如果所有内部表格都要列明身份证号码，那么无法对员工的隐私起到很好的保护作用；另一方面，身份证号码有18位数字，在Excel中，数字过长在显示上和在公式引用上都会有一些不便。

现在的肖敏，更习惯这样记录信息，如下所示。

在这样精细清晰的数据源的基础上，肖敏可以很直接地用筛选功能查看各个部门和各个奖金类别的信息，甚至能很快地用数据透视的方法或直接用公式做出数据统计，以便发送给更高级别的负责人进行整体奖金的审批。

涨知识

神奇的数据透视

Excel的数据透视功能非常贴心地为我们准备了便捷的信息交互汇总和清晰即时的数据看板展示，成为数据统计功能中的“神器”。自从学会了数据透视之后，肖敏再也不用面对一堆表格数据发愁了。

肖敏用鼠标选中自己仔细整理出来的奖金明细表格中的内容，单击“插入-数据透视表”，如下图所示。

在弹出的“创建数据透视表”对话框中进行简单的设置操作：

【选择一个表或区域】：这里需要选择的是我们的数据源范围，肖敏在插入数据透视表前已经选中了奖金明细表格中的内容。因此，Excel会自动识别和设置好这个范围。如果在插入前没有选中数据源，也可在此位置进行设置。

【选择放置数据透视表的位置】：如果选择“新工作表”，Excel则会自动新建一个工作簿（sheet）用来显示数据透视内容及操作选项。肖敏考虑到这次的数据源较少，需要统计的内容也不多，她希望透视结果跟数据源并列显示，即显示在H1的位置。因此，她选择了“现有工作表”，并在“位置”中直接设置了H1。

设置完成点击“确定”后，右侧将会出现字段操作界面，在这里进行字段设置后，H1的位置将会看到数据透视表的结果展示。

肖敏想要从以下三个维度分别统计一下这次奖金的发放情况。

（1）按奖金类别统计

直接在右侧的操作界面中，将“奖金类别”拖拽至“行”区域，将“工号”和“奖金金额”拖拽至“值”区域，完成设置后，表格H1的位置会直接显示统计结果，即每类奖项的人数及金额总计。

（2）按部门统计

在右侧的操作界面中，将“部门”拖拽至“行”区域，将“工号”和“奖金金额”拖拽至“值”区域，完成设置后，表格H1的位置会直接显示统计结果，即各个部门的人数及金额总计。

（3）按部门和奖金类别综合统计

既按部门又按奖金类别统计，只需要在“按部门统计”的设置基础上，在右侧的操作界面中，将“奖金类别”拖拽至“行”区域即可，完成设置后，表格H1的位置会直接显示各个部门和各个奖金类别的人数及金额统计结果。

统计完奖金数据，肖敏需要开始统计考勤信息了。在前几天，她已经提前向各个部门助理发送了各部门的员工打卡信息及休假情况。今天大家都已经将各自部门的考勤数据进行了确认和审批。

为了让考勤数据更方便统计并与薪酬管理对接，肖敏习惯让考勤表尽可能地详细、清晰，根据内容可以划分为：基础信息、出勤统计及考勤详情。

【基础信息】：主要包含员工个人基本信息，如工号、姓名、在职状态、部门、岗位、入离职时间等，还包括考勤周期，用于区分不同周期的数据，并参与出勤统计的计算。

【出勤统计】：主要是关于出勤信息的相关统计，如该考勤周期的总计薪天数、法定节假日天数、员工在职计薪天数和实际出勤天数等。

【考勤详情】：这里记录了每个员工的详细考勤信息，如出差情况、各个假别的请假情况和加班数据等。

涨知识

出勤统计的方法

出勤统计中包含的主要是汇总数据，从全月的维度来统计员工整体的出勤情况，这些数据在工资计算中主要用来计算当月基本工资和补助等信息。

以2021年6月为例，6月14日为法定节假日（端午节），以下分别计算各个口径数据。

【当月计薪天数】：在不考虑员工个人入离职、休假情况的前提下，计算全月计薪天数。一般情况下，全月计薪天数等于全月工作日的天数。

需要注意的是，法定节假日属于计薪日，不属于工作日。因此，在计算计薪天数时需要把国家规定的法定节假日及期间倒休天数进行特殊处理。在计算两个日期中间有多少实际工作日的时候，肖敏习惯用Excel中的工作日计算公式：NETWORKDAYS。用自然语言来解释这个公式就是：NETWORKDAYS（开始日期，结束日期，［某个具体假期日期］）。

“开始日期”和“结束日期”是必须要输入的；“某个具体假期日期”是选填参数，在此处将假期日期填入后，公式会自动将该日期从工作日中刨除。

在这里，由于我们需要计算的是当月计薪天数，而当月计薪天数应当包含法定节假日，所以“某个具体假期日期”可以直接忽略不填，即2021年6月计薪天数计算公式为：NETWORKDAYS（DATE（2021,6,1），DATE（2021,6,30））。可得出结果为22天。

【在职法定节假日天数】：对于全月在职的人员来说，6月大家都有1天法定节假日（6月14日端午节），当天带薪且不需要出勤，而对于当月入/离职的人员就未必如此了。如果入职时间在端午假期之后，或离职时间在端午假期之前，则6月14日不带薪；反之，如果入职时间在端午假期之前，或离职时间在端午假期之后，则6月14日带薪。我们可以通过手动记录或用条件判断的公式对在职法定节假日天数进行计算。

【在职计薪天数】：这是每名员工当月实际需要发薪的天数，这个字段的内容与员工的入/离职时间和在职法定节假日天数相关，可通过NETWORKDAYS进行计算。

【实际出勤天数】：这是员工当月实际出勤的天数，一般实际出勤天数会作为各项补贴（如餐补、交通补贴等）计算的依据。由于法定节假日（不加班的前提下）不需要员工出勤，则实际出勤天数不含在职法定节假日天数，同时须除去员工各项休假的天数。

先填写完各项假期数据，再计算出以上信息，这便是一个完整的考勤统计表了。不论是考勤信息的记录还是薪酬核算，我们都应做充分的准备。 BIFb+f8tRq/lpOYk30YrzC1Njwt3fxUzhvejkUR5SO2xNqo3sAqjjKNDIoa9/GrF