Excel也可以很好玩_在线翻页电子书免费阅读,发布_云展网
阅读云展网其他3D杂志
喜欢这样的3D电子杂志？您也可以在几分钟内把文档免费上传到云展网变成翻页书！[点击上传我的文档]
Excel也可以很好玩
描述：全面解析200多个Excel疑难问题，倾情奉献10套Excel行政管理解决方案，独家提供12多种Excel财务预测方法，精选15个常用Excel VBA代码
关键字: Excel
第 8 章 Excel 为考勤统计提速中，将数组间对应的元素相乘，并返回乘积之和。”
其使用方法是：
SUMPRODUCT(array1,array2,array3, ...)
其中的 Array1, array2, array3 分别表示一个数组，SUMPRODUCT 将这些数组对应元素进行相乘，并将这些乘积相加，返回相加的结果。
例如，如图 8-10 中计算商品销售总金额时，我们使用的是数组公式来计算，其实使用SUMPRODUCT 函数，就不用定义数组公式了，因为 SUMPRODUCT 函数的参数就是数组，只需要定义以下公式就可计算出总金额。
= SUMPRODUCT (D3:D4, E3:E4)
公式定义和计算结果如图 8-11 所示。
图 8- 11 用 SUMPRODUCT 函数计算总金额
李璐：“用这个函数就比数组公式好理解些。”
马波：“这是 SUMPRODUCT 函数的基本用法，接下来我再介绍其扩展用法，我们统计考勤表就需要使用这些扩展用法。”8.5.2 使用 SUMPRODUCT 进行多条件计数
马波接着说：“使用 SUMPRODUCT 函数可在大量的数据中进行多条件计数。”
李璐：“多条件计数？”
马波：“就是判断满足多个条件的数据有多少。例如，在我们的考勤表中，员工每打一次指纹就会生成一条数据，如果在某一天一条数据都没有，就说明该员工在这一天没打卡。要判断未打卡数据就需要两个条件：员工姓名（或编号 ），日期。这时就可以使用下面的公式来计数。”
=SUMPRODUCT((C2:C400=&李璐&)*(E2:E400=DATE()))
以上公式中，C2:C400 就是一个数组，该数组中的每一个元素都与“李璐”进行比较，若相等，则返回 TRUE，否则返回 FALSE，这些返回值组成一个包含 TRUE 或 FALSE 的数组；同样，E2:E400 也是一个数组，这个数组中的值与日期“”进行比较，若相等，返回 TRUE，否则返回 FALSE，这些返回值组成另一个包含 TRUE 或 FALSE 的数组；然后 SUMPRODUCT 函数这两个数组相乘的结果累加。当某一行的 C 列为“李璐”，E 列为“”，将返回数值 1 作为运算结果。
183菜鸟的 Excel 智慧职场路
根据以上分析可知道，SUMPRODUCT 函数返回值为几，就表示指定日期该员工（李璐）有几条打卡数据，当函数返回值为 0 时，表示指定日期没有打卡数据。
李璐：“这个公式绕了几个弯，比较难懂，不过结果倒很简洁。我再看看这个公式。”
马波：“多条件计数中的条件数量可以是很多个，因此公式可写如下形式。”
=SUMPRODUCT((条件 1)*(条件 2)*(条件 3)* …(条件 n))8.5.3 使用 SUMPRODUCT 进行多条件求和
马波：“从多条件计数可以扩展 SUMPRODUCT 函数的另一个应用，就是多条件求和。多条件求和的公式类似下面的形式。”
=SUMPRODUCT((条件 1)*(条件 2)* (条件 3) *…(条件 n)*某区域)
就对于完全满条件 1、条件 2 到条件 n 的行，将指定区域的数据汇总求和。
例如，假设在工资表中 B 列保存员工的性别，C 列保存部门名称，F 列保存应发工资，现在要统计“市场部女员工的应发工资总额”，则可使用以下公式进行计算：
=SUMPRODUCT((B2:B20=&女&)*(C2:C20=&市场部&)*F2:F20)
李璐：“哈哈，SUMPRODUCT 函数真的功能很强哟，以后我进行多条件统计时就好办了，直接派它上场就解决了。”
8.6 用 SUMPRODUCT 函数处理考勤表
马波：“现在就该 SUMPRODUCT 函数上场解决考勤表的问题了。”
李璐：“怎么弄？我可是一点头绪都还没有。”
马波：“按我的步骤操作吧，在用 SUMPRODUCT 函数之前，我们还需要对考勤数据进行一下加工。”
李璐：“怎么加工？”8.6.1 有备无患
马波：“为了保存原来的操作，先将名称‘2 月’的工作表复制一份作为备份。这叫做有备无患，如果对工作表操作出问题了，可通过备份表恢复数据。复制工作表的操作如下。”
（1）在“2 月”工作表标签上单击鼠标右键，弹出如图 8-12 所示的快捷菜单。
（2）在快捷菜单中单击选择“移动或复制”命令，打开如图 8-13 所示的“移动或复制工作表”对话框。
（3）在图 8-13 所示对话框中，单击选择“2 月”工作表，并选中下方的“建立副本”复选框，然后单击“确定”按钮将“2 月”工作表复制一个副本，Excel 为副本定义名称为“2 月 (2)”。184第 8 章 Excel 为考勤统计提速
（4）单击切换到“2 月”工作表，删除其中的 I、J、K、L 这 4 列（这是前面操作时添加的）。
图 8- 12 工作表快捷菜单
图 8- 13 移动或复制工作表8.6.2 加工考勤数据
马波：“在原始的考勤数据中，日期和时间在同一列中，由于要判断某一天是否打卡，需要根据日期进行判断，因此，我们需要先把日期和时间分到两列中。”
李璐：“数据分列，这个我知道。我看日期和时间数据之间有一个空格分隔，可以根据这个条件来分列。”
（1）在 D 列和 E 列之间插入一个空列。
（2）单击列表签“D”，选中整个 D 列。
（3）在功能区中单击切换到“数据”选项卡，如图 8-14 所示。
图 8- 14 数据工具
（4）在功能区“数据”选项卡的“数据工具”组中单击“分列”命令按钮，打开如图 8-15 所示的对话框，选中“分隔符号”单选按钮，
（5）单击“下一步”按钮，向导将显示如图 8-16 所示对话框，选中“空格”复选框，并选中“连续分隔符号视为单个处理”复选框。
185菜鸟的 Excel 智慧职场路图 8- 15 文本分列向导第 1 步
图 8- 16 文本分列向导第 2 步
（6）单击“下一步”按钮，向导将显示如图 8-17 所示对话框，选择“常规”单选按钮。
（7）在 E1 单元格输入“时间”，将 D1 单元改为“日期”，得到如图 8-18 所示的表格。
图 8- 17 文本分列向导第 3 步
图 8- 18 分列后的数据8.6.3 制作考勤统计表
马波：“不错，数据分列操作挺熟练的。现在数据准备好了，还要做一张考勤统计表，用来记录每一位员工每一天的出勤情况。”
李璐：“从打卡机中导出的数据不就是这个吗？”
马波：“打卡机导出的是明细数据，其实最终咱们关心的是迟到、早退、旷工或正常这几种状态。通过对原始数据进行汇总计算，最后生成员工每一天的出勤状态就可以了。因此，这张‘考勤统计表’包括这一个月的所有工作日，需要考勤的员工姓名。可以将工作日放在表的列中，每天为一列，然后每行显示一个员工。”
李璐：“这种表格简单，我马上做出来。”186第 8 章 Excel 为考勤统计提速
（1）在“2 月”工作表标签上单击右键，从弹出的快捷菜单中选择“插入”命令，打开如图 8-19 所示的“插入”对话框。
图 8- 19 插入
（2）在图 8-19 所示对话框中选择“工作表”，单击“确定”按钮，向工作簿中新插入一张工作表，将其标签改名为“考勤统计表”。
（3）在“考勤统计表”中单击选择 B1 单元格，输入“”，并设置其格式为显示“2/1”。
（4）右键向右拖动 B1 单元格的填充柄到 V1 单元格，从弹出的快捷菜单中选择“以工作日填充”。
（5）从 A2 单元格开始向下输入考勤员工的姓名，最后得到如图 8-20 所示的“考勤统计表”。
图 8- 20 考勤统计表8.6.4 统计考勤状态马波：“万事俱备，只欠东风了，这东风就是 SUMPRODUCT 函数。”
187菜鸟的 Excel 智慧职场路
李璐：“该怎么用这个函数来统计考勤状态？”
马波：“先确定一下，在‘2 月’工作表中 C 列保存的是员工姓名，D 列是打卡日期，E 列是打卡时间。还有，看一下‘2 月’工作表中最后一行数据在第几行？”
李璐：“按&Ctrl&+&↓&组合键可快速到表格的最后一行。最后一行行号是 390。”
马波：“好，记住这几个数据，在定义公式时需要使用。下面是具体操作步骤。”
（1）在“考勤统计表”中单击选择 B2 单元格。
（2）在 B2 单元格中输入以下公式：
=IF(SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*('2 月'!$D$2:$D$390=B$1)),
IF(SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*('2 月'!$D$2:$D$390=B$1)*
('2 月'!$E$2:$E$390&9/24)),IF(SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*
('2 月'!$D$2:$D$390=B$1)*('2 月'!$E$2:$E$390&17.5/24)),&正常&,&早退&),&迟到&),&旷工&)
（3）这个公式看起来很长，下面分步给你讲解一下。首先看下面这一部分：
SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*('2 月'!$D$2:$D$390=B$1))
这是使用 SUMPRODUCT 函数的多条件计数功能，$A2 单元格保存的是员工的姓名，B$1 单元格保存的是考勤的某一天。上面这部分函数的功能是根据员工姓名、考勤日期进行计数，若
日这天“张新”没有打卡数据，则计数返回为 0，否则返回大于 0 的值。这里的返回值供 IF 函数进行判断，若返回值为 0，则输出“旷工”，若返回值不为 0，则进行迟到、早退、正常这 3 种状态的判断。由于判断这 3 种状态的代码比较长，先把这3 种状态统称为“上班”，则 B2 单元格的公式可简化为以下情况：
=IF(SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*('2 月'!$D$2:$D$390=B$1)), &上班&,&旷工&)
（4）类似地，在“上班”状态中，又通过 IF 函数进行判断，这部分代码如下：
SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*('2 月'!$D$2:$D$390=B$1)*
('2 月'!$E$2:$E$390&9/24))
这部分代码有 3 个条件，姓名等于$A2 中的内容，日期等于 B$1 中的内容，时间小于9/24（这里的 9/24 使用了时间的一个技巧，全天 24 小时，为一个整数 1，因此 9 点就是9/24），表示在 9 点之前有打卡数据。
如果不满足这 3 个条件（其实前两个条件前面已经判断过，这里主要是满足 9 点之前这个条件），则可认为是迟到。而满足这 3 个条件则可以认为未迟到，然后进行早退的判断。
（5）类似地，在“未迟到”时，又要判断是否早退，使用以下代码：
SUMPRODUCT(('2 月'!$C$2:$C$390=$A2)*('2 月'!$D$2:$D$390=B$1)*
('2 月'!$E$2:$E$390&17.5/24))
这里的 17.5/24 表示 17:30 分。
（6）这个公式经过这几层嵌套，最终完成了复杂的判断。这个公式的整个流程如 图8-21 所示。
（7）在 B2 单元格输入公式后得到如图 8-22 所示的计算结果，根据计算结果可看出，“张新”在
日这天上班是“正常”状态。188第 8 章 Excel 为考勤统计提速当 天 有 考 勤 数 据?
旷工9:0 0 前 有 考 勤 数 据?
是1 7 :3 0 后 有 考 勤 数 据?
早退正常图 8- 21 考勤状态判断流程
图 8- 22 公式及计算结果
（8）向右拖动 B2 单元格的填充柄到 V2 单元格，可看到“张新”当月的考勤状态就生成完成了，如图 8-23 所示。图 8- 23 拖动复制一行公式
189菜鸟的 Excel 智慧职场路
（9）确保 B2:V2 这部分单元格区域处于选中状态，向下拖动选中区域的填充柄到第13 行，得到如图 8-24 所示的结果。
图 8- 24 向下拖动复制公式
到此，就完成了每一天的考勤状态的统计。
对于这种用拖动的方式快速复制公式，是由于在 B2 单元格定义公式时使用了单元格的绝对引用、混合引用。如对于姓名（ A 列），使用的是$A2 的方式，表示不管公式复制到哪里都使用 A 列的数据，只是行会随公式的位置而变化。这种混合引用需要多用心去体会一下。
李璐：“这几种引用方式是容易把人搞昏，有这个公式，我正好可以参照学习一下单元格的引用方式。”8.6.5 汇总考勤数据
马波：“有了上班的这 4 种状态，要汇总每位员工的出勤数据就很简单了。”
李璐：“恩，这个我会了，我先把这些数据汇总出来，再去研究上面这个公式。”
（1）在 X1、Y1、Z1 单元格分别输入“迟到”、“早退”、“旷工”。
（2）在 X2 单元格定义如下公式：
=COUNTIF(B2:V2,&迟到&)
（3）在 Y2 单元格定义如下公式：
=COUNTIF(B2:V2,&早退&)
（4）在 Z2 单元格定义如下公式：
=COUNTIF(B2:V2,&旷工&)
（5）选择 X2:Z2 单元格区域，向下拖动填充柄复制公式，最后得到如图 8-25 所示的汇总数据。190第 8 章 Excel 为考勤统计提速
图 8- 25 考勤汇总数据
从最终汇总的数据看，这个月忘记打卡的人很多，可能很多同事还不习惯这种指纹打卡方式。不过，这只是打卡机中的数据统计的结果，还要和考勤异常登记表中的登记情况进行对比，可能有些同事由于出差、公休等原因没有打卡。
191菜鸟的 Excel 智慧职场路
第 9 章 每月的期盼：发薪日
对于喜欢追逐新潮的年轻人，每月的工资都用在吃大餐、买靓衫、朋友聚会上，于是形成了典型的“月光族”。月光族的口号是 ：“吃光用光，身体健康，挣多少花多少 。”只要吃得开心，穿得漂亮，想花就花，根本不在乎钱财。可是花光了后该怎么办？于是，公司每月的发薪日就成了大家翘首企盼的日子。
知道大家对发工资的急切期盼心情，因此，人资部主管张新总是在每月初就会把考勤、业绩考核、工资表的制作这几件事排在最重要的位置。
早上上班李璐就被张新叫到办公室，张新说 ：“李璐，马上又要到发薪日了，咱们部门的工资专员朱芳有事请假了，你来做一下工资表。”
李璐：“好，但我原来没做过，不知道做得好不。”
张新：“我这里有上个月的工资表文件，一会儿发给你，基本上就在这张表上进行修改就可以，主要是考勤扣款这部分每月都有变化，然后是将社保和个税的代扣款计算准确就可以了。你 Excel 用得很熟练，应该很快就可以做好的。”
李璐：“那行，我做好后再给你审核。”
张新：“要尽量快点哟，工资表做好后，还要交给总经理审核签字，如果老总出差就签不到字。所以我们尽量提前几天把工资表做好。”
9.1 重新建立工资表
李璐打开张新发过来的工资表文件，看了一下里面的数据，基本上都是手工输入的，只有几个汇总数据是通过 SUM 函数计算的。这种工资表做起来确实费时费力，每个人的项目都要逐个去计算。李璐想，Excel 就拥有强大的查表和计算功能，应该可以很快就完成工资的计算。只是要做这样一套工资表格，开始需要花些时间。做吧，一来当作使用 Excel练练手，另外，做好后也可方便同事以后的工资计算。
李璐决定先用部分后勤管理人员的工资数据来制作这一套工资表格。9.1.1 搭建工资表框架
李璐首先将张新发来的工资表结构复制了一份，粘贴到一个新的 Excel 工作簿中，作为工资表的框架，并将该工作簿保存为“2 月工资表.xlsx”，如图 9-1 所示。192第 9 章 每月的期盼：发薪日
图 9- 1 工资框架
从图 9-1 所示的工资表框架可以看到，公司后勤管理人员的工资主要由基本工资、职务工资、工龄工资、绩效这几部分组成，其中基本工资一般都不会变化（除了调资之外），而职务工资会随员工任职的变化而变化，而工龄工资随着员工在公司上班的时间变化，绩效是根据每月的绩效考核来核发。这几部分内容都需要参照其他表格中的数据来生成，不用手工输入。
再看扣款部分，考勤扣款根据考勤统计的数据计算得出，代扣社保按国家法规以工资的一定比例扣除，代扣个税按税率扣除。这些扣款都需要通过计算得到。9.1.2 员工基本情况表
根据公司的规定，员工基本工资各不相同，在入职时会定一个基数，然后会不定时的进行调整（包括上调和下调），基本工资记入员工的档案，另外员工的职务、工龄这些信息也保存在员工档案内。为了方便计算，李璐将部分员工的基本情况复制出来，删除不需要的列，得到如图 9-2 所示的表格。图 9- 2 员工资料
193菜鸟的 Excel 智慧职场路
9“员工资料”这份表格，就可以将这里的数据引入到工资表中，这样就不会在工资表中漏掉某位同事了。李璐按以下步骤开始创建公式引入这些数据。
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，删除 A3:A15 单元格区域中原来的数据。
（2）单击选择 A3 单元格输入以下公式：
=[人资数据.xlsx]员工资料!A2
以上公式引用“人资数据.xlsx”工作簿中的“员工资料”工作表中的数据，注意单元格的引用为相对引用，方便后面公式的复制。
（3）向右拖动 A3 单元格的填充柄到 C3 单元格，得到如图 9-3 所示的数据。
（4）确保如图 9-3 所示，选中了 A3:C3 单元格区域，向下拖动填充柄，得到如图 9-4所示的填充数据。从图中可看到后面有一行数据全为 0，说明在“员工资料”表中已没有人员信息了，删除最后一行为 0 的数据即可。
图 9- 3 向右复制公式
图 9- 4 向下复制公式9.1.3 计算工龄工资
根据公司的薪酬制度规定，员工每年工龄有 100 元工龄工资。如图 9-2 所示的“员工资料”表中保存着员工的工龄，从该表中获取工资后再乘以 100 即可得到工龄工资。
对于这种从一个表中查询获取数据的操作称为查表，李璐正好学习了 Excel 的查表函数。Excel 的查表函数主要有以下几个：? LOOKUP 函数? VLOOKUP 函数? HLOOKUP 函数
在可以使用 VLOOKUP 函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。正好，查询员工的工龄就是以员工的姓名为基准，找到姓名后，再在同一行的第 4 列就可找到工龄。VLOOKUP 函数的使用方法如下：
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])194第 9 章 每月的期盼：发薪日? lookup_value：要在表格或区域的第一列中搜索的值。? table_array：包含数据的单元格区域。? col_index_num：从 table_array 参数中返回的匹配值的列号。col_index_num 参数为 1
时，返回 table_array 第一列中的值；col_index_num 为 2 时，返回 table_array 第二列
中的值，依此类推。? range_lookup：设为 TRUE 或为 FALSE，用来控制返回精确匹配值或近似匹配值。
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 E3 单元格输入以下公式：
=VLOOKUP(A3,[人资数据.xlsx]员工资料!$A$2:$D$13,4)*100
表示在“人资数据.xlsx”的“员工资料”工作表中查找当前工作表（“2 月工资表.xlsx”中的“Sheet1”工作表）的 A3 单元格中的值，找到后返回“员工资料！$A$2:$D$13”的第 4 列（即工龄），再将返回的工龄乘以 100，即可得到工龄工资。
（2）向下拖动 E3 单元格的填充柄到 E14 单元格，即可计算出全部的工龄工资，如图9-5 所示。
图 9- 5 计算工龄工资9.1.4 计算职务工资
根据员工的职务不同，每月工资中将发放不等的职务工资。而员工的职务会随着工作业绩进行调整，因此职务工资应该也是变化的。在“人资数据.xlsx”工作簿中创建如图 9-6所示的“职务工资”工作表保存不同职务的职务工资，这样，也可通过 VLOOKUP 函数查表计算出每位员工的职务工资。
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 D3 单元格输入以下公式：
=VLOOKUP(B3,[人资数据.xlsx]职务工资!$A$2:$B$6,2,FALSE)
（2）向下拖动 D3 单元格的填充柄到 D14 单元格，即可计算出全部的职务工资，如
195菜鸟的 Excel 智慧职场路图 9-7 所示。
图 9- 6 职务工资
图 9- 7 计算职务工资9.1.5 计算绩效
对于每月绩效的计算，也需要通过查表的方式来完成。首先查看一下绩效考核的情况，如图 9-8 所示。在该表中，每位员工有一个绩效奖金的定额，还有一个考核得分，评分按5 分制进行，当得分为 4 分时，计发 100%的绩效奖金，评分为 5 分时，计发 5/4 倍的绩效奖金，类似地，如果得 3 分，计发 3/4 倍的绩效奖金。
图 9- 8 绩效考核数据
根据以上的规则，通过查询“绩效考核.xlsx”工作簿中的“Sheet1”工作表即可计算出相应的绩效工资。
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 F3 单元格输入以下公式：
=VLOOKUP(A3,[绩效考核.xlsx]Sheet1!$A$3:$C$14,2,FALSE)*
VLOOKUP(A3,[绩效考核.xlsx]Sheet1!$A$3:$C$14,3,FALSE)/4196第 9 章 每月的期盼：发薪日
以上公式中，通过两次查表来获取数据：第一次查表获取对应员工的绩效奖金定额，第二次查询获取得分。将这两个数相乘后除以 4 即可得到实际应发的绩效工资。
（2）向下拖动 F3 单元格的填充柄到 F14 单元格，即可计算出全部的职务工资，如图9-9 所示。
图 9- 9 计算绩效工资9.1.6 计算应发小计通过以上步骤将各项应发工资都已填入到工资表中，接着定义应发小计。（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 H3 单元格。
（2）在功能区“开始”选项卡的“编辑”组中，单击自动求和按钮
，Excel自动使用 SUM 函数对 C3:G3 单元格区域进行求和，生成的公式如下：
=SUM(C3:G3)（3）向下拖动 H3 单元格的填充柄到 H14 单元格，即可计算出全部的应发小计，如图 9-10 所示。
图 9- 10 计算应发小计
197菜鸟的 Excel 智慧职场路
9.2 代扣款的计算
公司在发放工资时，有一些法定的代扣款，如社保、个税，另外公司内部管理中也会有一些扣款项目，如缺勤扣款等。对于这些扣款项目，也是有规则的，李璐也决定用公式进行自动计算。9.2.1 计算考勤扣款
根据公司的考勤管理制度规定，对于迟到、早退，每次扣款 30 元，而对于旷工则是旷一罚三，即旷工一天要扣除三天的工资。
对于考勤扣款的计算，也需要查询考勤统计表中每个员工的缺勤情况，然后再根据规则计算扣款金额。打开“2 月考勤数据.xlsx”工作簿，查看“考勤统计表”工作表如图 9-11所示。这里显示的考勤统计表是经过修正的，根据考勤异常登记表中登录的情况对一些旷工、迟到、早退情况进行了调整。
图 9- 11 考勤统计表
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 I3 单元格，输入以下公式。
=(VLOOKUP(A3,[2 月考勤数据.xlsx]考勤统计表!$A$2:$Z$13,24,FALSE)+
VLOOKUP(A3,[2 月考勤数据.xlsx]考勤统计表!$A$2:$Z$13,25,FALSE))*30+
VLOOKUP(A3,[2 月考勤数据.xlsx]考勤统计表!$A$2:$Z$13,26,FALSE)*H3/22*3=SUM(C3:G3)
在以上公式中，第一个 VLOOKUP 函数查询迟到次数，第二个 VLOOKUP 函数查询早退次数，将这两个数据相加乘以 30，即得到迟到、早退的扣款。第三个 VLOOKUP 函数查询旷工天数，然后乘以 H3/22*3，表示按月应发小计除以 22 天，得到每天的工资数，乘以 3 是旷一罚三的作用。
（2）向下拖动 I3 单元格的填充柄到 I14 单元格，即可计算出全部的考勤扣款情况，如图 9-12 所示。198第 9 章 每月的期盼：发薪日
图 9- 12 计算考勤扣款
从图 9-12 可看出，在考勤扣款中有些小数部分的位数保留了 4 位，这应该是显示格式的设置问题，在后面进行统一设置。9.2.2 代扣三险一金
根据相关法律法规的规定，企业为员工购买社会保险的同时，员工个人也支付相应比例。这些款项统一由企业缴存，因此，应在工资表中代扣个人应缴部分。
个人需缴纳的社会保险费由三部分：养老保险、失业保险和医疗保险。其中养老保险个人缴纳工资基数的 8%，失业保险为 1%，医疗保险为 2%。
三险一金中的“一金”是指住房公积金，住房公积金不是强制性的。不过，作为福利的一部分，HG 公司为员工缴存了住房公积金，按基本工资额的 6%缴存。
因此，三险一金个人扣款总额为基本工资的 17%。有了这个比例，就可以方便地定义公式计算出代扣金额了。
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 J3 单元格，输入以下公式。
（2）向下拖动 J33 单元格的填充柄到 J14 单元格，即可计算出全部的考勤扣款情况，如图 9-13 所示。
199菜鸟的 Excel 智慧职场路
图 9- 13 计算代扣社保
9.3 代扣税款的计算
前面也提到过，在员工工资中代扣个人所得税是企业的法定义务。因此，在制作工资表时需要计算这项代扣款。因为个人所得税的计算比较复杂，所以李璐将其放在最后来做。为此，李璐还专门上网查了一下个人所得税的相关内容。9.3.1 个人所得税怎么算
对于个人所得税的计算，李璐还没搞明白。反正自己现在的收入还达不到缴纳个人所得税的标准，因此也不怎么关注。
但是，现在要做工资表，就需要把计算方法弄明白，才不会给同事算错。1．个人所得税税率表
李璐知道月工资收入超过 3500 元就需要缴纳个人所得税，但具体怎么计算还不知道。于是，李璐百度了一下个人所得税税率表，发现从 2011 年 9 月 1 开始实行了新的 7 级超额累进个人所得税税率表。个税起征点为 3500 元。
李璐在“人资数据.xlsx”工作簿中新建名为“个税税率表”的工作表，将网上找到的这个表复制到 Excel 表格中，方便计算时查表。为了 Excel 计算方便，还将原来的级数 1~7改为每档的最高金额，改造后的个人所得税税率表如图 9-14 所示。200第 9 章 每月的期盼：发薪日
图 9- 14 个税税率表2．个人所得税的计算方法
李璐又从网上查看个人所得税的计算方法，计算应缴纳个税的公式为：
应缴纳的个税＝[(应发工资 － 三险一金)－3500]×税率－速算扣除数
以上公式中的三险一金是指个人承担的养老、医疗、失业、公积金，不包括公司承担部分。
有了以上公式，再根据图 9-14 所示的税率表，就可以计算员工的个人所得税了。例如，某员工本月应发工资为 8200，其三险一金个人承担部分为 910，则计算其个税的步骤如如下：
（1）计算出其“应纳税所得额”，也就是应发工资减三险一金，再减 3500，即：
8200 – 910 – 3500 = 3790
（2）根据 3790 这个应纳税所得额，该数小于 4500，在图 9-14 所示的税率表中查找，得知其税率为 10%，速算扣除数为 105。根据上面的公式，可按下式计算出个税金额。
（8200 – 910 – 3500）* 10% - 105 = 2749.3.2 Excel 计算个人所得税
手工计算个人所得税的方法李璐已经学会了，但是，这种手工计算方法怎么在 Excel中定义公式呢？1．用 VLOOKUP 函数查表
李璐首先想到的是用 VLOOKUP 函数进行查找，于是在税率表中创建公式进行测试。
（1）在 F5 单元格输入一个金额 5000，用来测试查找的税率是否准确。
（2）在 G5 单元格中输入以下公式：
=VLOOKUP(F5,A4:D10,3)
（3）这时在 G5 单元格可看到公式计算结果为 0.1，即查找到的税率为 10%。可是，这和左边表中的数据是不相符的（ 5000 元的纳税额，税率应该为 20%，位于 10%的下一行），如图 9-15 所示。再输入其他的数，如 3000 进行测试，得到的为 0.03，正确的税率应该是下一行的 10%。
201菜鸟的 Excel 智慧职场路
图 9- 15 用 VLOOKUP 函数查税率表（错误结果）
为什么会出现这种情况呢？李璐经过查找资料发现，VLOOKUP 函数的第 4 个参数若为 TRUE 或省略时，该函数在查找时，若没有完全一致的数据与之匹配，就会找到小于它的最大数。如图 9-15 中的 5000，在 A4:A10 区域中没有，则会找到小于它的最大值 4500，然后返回该行第 3 列的数。
怎么办呢？李璐在思考：VLOOKUP 会找到小于它的最大数，每次从表中找到的税率都位于正确值的上面的一档，那就将“级数”中的金额都向下移一行不就 OK 了？
于是李璐将 A3:A9 单元格区域的数据向下移了一行，这时在 F5 单元格输入 18000，G5 显示税率为 0.25（即 25%），如图 9-16 所示，OK。
图 9- 16 修改后 VLOOKUP 查税率表（正确结果）
从税率表中查找到税率，再用相同的方法查找到速算扣除数，个人所得税的计算就简单了。
可是，用 VLOOKUP 函数查表的方法要改变原来的税率表，感觉不是太好。如果某天将正常的税率表复制过来，则计算的结果就会出错。李璐觉得还是该另找方法。2．用数组公式计算
在李璐遇到有难题时，总是该马波上场，这次也不例外，李璐又把马波呼叫出来了。
马波：“计算个税的公式啊？首先，你看个税税率表的数据是一个二维表，其实对于这种二维表的情况，可以考虑用数组公式。数组公式一般人都不会，不过好在你已经懂一些了。”
李璐：“怎么定义数组公式？”202第 9 章 每月的期盼：发薪日
马波：“这就要用到一个新函数：FREQUENCY。”
FREQUENCY 函数可以计算数值在某个区域内的出现频率，然后返回一个垂直数组。例如，有以下公式：
=FREQUENCY(;000;})
这个公式的输出结果是多少呢？
李璐马上在一个新工作表的 A1 单元格中输入这个公式，结果为 0，如图 9-17 所示。
图 9- 17 FREQUENCY 函数运算结果
马波：“不对，FREQUENCY 函数的返回值是一个数组，因此，你必须选择 A1:A7 这7 个单元格，然后定义成数组公式，才能得到正确的结果。如果不定义为数组公式，就只能得到结果数组中第 1 个元素的值。”
于是李璐将 A1:A7 单元格区域选中，然后在编辑栏显示的公式中单击鼠标，按组合键&Ctrl&+&Shift&+&Enter&，得到如图 9-18 所示的结果。
图 9- 18 数组公式运算结果
马波：“从图 9-18 可看出，FREQUENCY 函数将 3000 与后面的数组进行频率归并，找到 3000 属于
这个区间，于是在结果数组的第 2 个元素中加 1，而其他各区间并没有数据，所以都为 0。”
李璐：“计算得到这个数组有什么用啊？”
马波：“还没看出来啊，结果数组中第 2 元素为 1，正好对应个税表中第二档，这样就可以找到这一档的税率，可以用以下公式计算。”
=FREQUENCY(;000;})*{0.03;0.1;0.2;0.25;0.3;0.35;0.45}
同样定义为数组公式，得到的结果如图 9-19 所示。
从图 9-19 的计算结果可看到，只有 A2 单元格中有一个值为 0.1，表示找出了 3000 元的税率为 10%。
203菜鸟的 Excel 智慧职场路
图 9- 19 计算出税率
李璐：“哦，这样的啊，通过数组计算，最后得到税率，接着就是用 3000 去乘以这个数组，求得未扣除速算数的税款？”
马波：“对，用以下公式就可以了。”
=3000 * FREQUENCY(;000;})*
{0.03;0.1;0.2;0.25;0.3;0.35;0.45}
以上公还是要定义为数组公式，得到的结果如图 9-20 所示。
图 9- 20 计算出税款（含速算扣除数）
李璐：“现在算出了 300，怎么减速算扣除数呢？”
马波：“仍然用数组公式，具体公式如下。”
=3000 * FREQUENCY(;000;})*
{0.03;0.1;0.2;0.25;0.3;0.35;0.45} – {0;105;555;05;13505}
同样需要将公式定义为数组公式，计算的结果如图 9-21 所示。
图 9- 21 应缴纳税款
李璐：“这一步计算结果有很多数，怎么区分哪个是需要的呢？”204第 9 章 每月的期盼：发薪日
马波：“分析一下计算的结果，发现什么特征没有？我们要的数肯定是 195。”
李璐：“其他数要么为 0，要么为负，我们要的数是最大数。”
马波：“对，这时我们再使用一个求最大数的函数 MAX，就齐活了。完整的计算公式如下。”
=MAX(3000 * FREQUENCY(;000;})*
{0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;105;555;05;13505})
将这个公式定义在 B2 单元格，还是要定义为数组公式，运算结果如图 9-22 所示。
图 9- 22 最终的公式
李璐：“哦，妈呀，这个公式整这么复杂。”
马波：“其实公式并不复杂，我逐步演示给你看，方便你理解。如果一开始就把这个公式给你，估计你是不容易理解。”
李璐：“也是，这样逐步推进，理解起来是要方便些。那我在实际应用中只需要将那些数据替换成单元格区域就可以了吧。”
马波：“对，这个就你自己去整吧，如果弄出来了，说明你把这个公式理解了，否则，就还要再把前面的操作看一下。”
李璐把“人资数据.xlsx”和“2 月工资表.xlsx”两个工作簿打开，开始定义个人所得税的计算公式。
（1）在“2 月工资表”工作簿的“Sheet1”工作表中，单击选择 K3 单元格，输入以下公式。
=IF((H3-[ 人 资 数 据 .xlsx] 个 税 税 率 表 !$C$1-I3-J3)&=0,&&,MAX((H3-[ 人 资 数 据 .xlsx] 个 税 税 率
表!$C$1-I3-J3)*FREQUENCY((H3-[人资数据.xlsx]个税税率表!$C$1-I3-J3),[人资数据.xlsx]个税税
率 表 !$A$4:$A$9)*[ 人 资 数 据 .xlsx] 个 税 税 率 表 !$C$4:$C$10-[ 人 资 数 据 .xlsx] 个 税 税 率
表!$D$4:$D$10))
输入以上公式后，按&Ctrl&+&Shift&+&Enter&组合键完成数组公式的定义。
（2）向下拖动 K3 单元格的填充柄到 K14 单元格，即可计算出全部的代扣个税情况，如图 9-23 所示。
205菜鸟的 Excel 智慧职场路
图 9- 23 计算代扣个税9.3.3 多发 1 毛少领 5000 元
通过计算个人所得税，李璐感觉自己又学到许多知识，不仅是对 Excel 数组公式的应用有了更进一步的了解，对个人所得税的知识也掌握了不少。
这时李璐想起了年前被媒体炒得火热的年终奖个人所得税问题，于是决定再深入了解一下。通过网络查询，李璐知道了年终奖个人所得税的计算方法如下：
根据国家税务局的规定，明确年终奖是全年的业绩考核，因此会根据年终奖金额除以12 后的金额来查找税率。年终奖的计税与当月工资仍然有关系，如果当月工资低，未达到3500 的标准，可以在年终奖里继续抵扣。
计算年终奖个人所得税的方法，就是先用年终奖金额除以 12 后的值查找税率，然后用年终奖金额乘以税率，再减去税率所在档次的速算扣除数即可。
例如：12 月工资 2500，年终奖 30000，应缴纳多少个人所得税？
计算步骤如下：
（1）因为 12 月工资不足 3500（差 1000），因此，应从年终奖中减少 1000，即年终奖计税额为 29000。
（2）用 6.67，查税率表，未超 4500，属于第一档，税率为 3%，速算扣除数为 0。
（3）计算应缴税金：0。
可是怎么会出现多发年终奖，反而少领钱的问题呢？
原来是出在税率表中档次之间的分界点处。为了彻底弄清这个问题，李璐在 Excel 中做了一个表，如图 9-24 所示。
在此表中，李璐专门找了一个临界点 4500 来计算，当年终奖为 54000 元时，分档金额为 0 元，从税率表查看可知道，4500 元这一档的税率为 10%，速算扣除数为 105，因此应缴税款为 5=5295 元。
而当年终奖为 54000.10 元时，分档金额为 =4500.01 元，超过了 4500 元，则税率应为 20%，速算扣除数为 555，因此应缴税款为 %-555=10245.02 元。206第 9 章 每月的期盼：发薪日
图 9- 24 年终奖个税比较
原来如此，知道这个原因以后，做年终奖分配时就要注意避开这些分界点的金额，让同事能领到更多的年终奖。
9.4 切断公式引用
通过边学边做的过程，李璐终于把工资表中最复杂的部分弄完了，剩下的都是些简单工作了。还需要定义一些简单的汇总计算公式，设置一下格式，工资表就算完成了。9.4.1 完善工资表的公式
李璐收回思绪，开始完善工资表中的公式。扣款项目已经计算完成，还需要定义一个扣款小计，将所有扣款列累加起来就行。然后还要需要定义实公式计算发工资，在最后一行中定义一个合计公式，计算工资的合计金额。
（1）单击选择 M3 单元格，输入以下公式：
=SUM(I3:L3)
（2）向下拖动 M3 单元格的填充柄到 M14 单元格。
（3）单击选择 N3 单元格，输入以下公式：
（4）向下拖动 N3 单元格的填充柄到 N14 单元格。
（5）单击选择 A15 单元格，输入“合计”。
（6）单击选择 C15 单元格，输入以下公式：
=SUM(C3:C14)
（7）向右拖动 C15 单元格的填充柄到 N15 单元格。
定义完所有公式后，得到的工资表如图 9-25 所示。
207菜鸟的 Excel 智慧职场路
图 9- 25 工资表9.4.2 自动更新的工资表
在李璐制作这份工资表时，其中大部分单元格的数据都是通过引用其他工作簿、工作表中的数据，或定义公式计算得出。对于这种引用其他工作簿、工作表数据的表格，如果其他工作簿、工作表中的数据发生变化时，当前工资表中的数据也会随之变化。
因此，这些工作簿就构成了一套工资系统，下个月只需要把业绩考核、考勤数据更新到对应的工作簿中，工资表就自动生成了。如果某位同事的基本工资、职务、工龄等信息有变化，只需要更新“人资数据.xlsx”工作簿中的“员工资料”工作表中的数据，工资表中的对应数据也会自动变化。
OK，这次做这份工资表格虽然花了几个小时的时间，不过，以后每个月就可节省时间了，只需要把参照的这些工作簿修改好，再打开工资表工作簿更新数据就搞定，几分钟的事！
默认情况下，当打开工资表工作簿时，会自动更新这些修改后的数据。也可在功能区“数据”选项卡的“链接”组中，单击“编辑链接”命令按钮，打开如图 9-26 所示的“编辑链接”对话框，在该对话框中列出了当前工作簿引用的其他工作簿，选中某个工作簿之后，单击右侧的“更新值”即可更新当前工作簿中的内容。
图 9- 26 编辑链接208第 9 章 每月的期盼：发薪日
单击图 9-26 所示对话框左下角的“启动提示”按钮，打开如图 9-27 所示的“启动提示”对话框，在该对话框中可设置打开工作簿时是否显示更新链接的提示。
图 9- 27 启动提示9.4.3 不再需要链接
通过 Excel 的更新链接可以很方便、快速地制作出工资表，但是，当一份工资表制作完成后，是不希望其中的数据再发生变化。这时，就需要断开链接，可是，下个月做工资表时又希望能快速生成工资表。更新、不更新是一对矛盾。
一个解决方案就是将生成的工资表复制一份，并以值的方法粘贴到另一个工作表中。这样，就可将当月工资数据固化。具体操作步骤如下：
（1）在“Sheet1”工作表中选中所有数据，将这些数据复制到剪贴板。
（2）切换到“Sheet2”工作表，修改其标签为“2 月工资”，单击选择 A1 单元格。
（3）在功能区“开始”选项卡的“剪贴板”组中，单击“粘贴”按钮下方的下拉箭头，从下拉菜命令列表中选择“选择性粘贴”命令，打开如图 9-28 所示的“选择性粘贴”对话框。
图 9- 28 选择性粘贴
（4）单击选择“数值”单选按钮，单击“确定”按钮完成粘贴，如图 9-29 所示。这时的工资表中无任何公式，都是最终的数据。当修改其他工作簿或工作表中的数据时，不会对该表造成任何数据影响。
209菜鸟的 Excel 智慧职场路
图 9- 29 无公式的工资表9.4.4 工资表也需要美化
工资表是要交给总经理签字的，因此，也应将其做得美观大方。
首先，对于人民币来说，最多只能显示到分，这是最小单位，而现在工资表中的数据有的显示了小数点后 4 位，因此需要进行调整。
（1）李璐拖动鼠标选择 C3:N15 这片显示数字的区单，单击右键，从弹出的快捷菜单中选择“设置单元格格式”命令，打开如图 9-30 所示对话框。
图 9- 30 设置单元格格式
（2）在“数字”选项卡的“分类”列表中单击“数值”，然后在右侧设置显示 2 位小数，并勾选“使用千位分隔符”复选框，单击“确定”按钮，所有数字都显示 2 位小数。
对于表格的格式化，李璐已经非常熟练了，不一会就把基本格式做出来，通过打印预览得到如图 9-31 所示的工资表效果。210第 9 章 每月的期盼：发薪日
图 9- 31 打印出的工资表
9.5 自动生成工资条
李璐将工资表打印出来交给张新审核签字。
张新拿着工资表说：“不错，这么快就做出来了。这份表就先放我这里，我拿去给财务部和总经理签字。”
李璐：“好，那我就交差了哟，老大。”
张新：“还没完哟，工资表你是打印出来了，发给每个员工的工资条呢？”
李璐：“哦，还要做工资条。行，我马上去做。”
张新：“别急，还要给你说个事。这个工资表的电子文件你要设置一个密码，不要让其他人员查看到里面的数据。”
李璐：“好，打工资条，设密码。我记住了。”9.5.1 生成工资条
要生成工资条，要求每次输出一行表头，接着输出一个员工的工资数据，然后空一行再重复前面的过程。
要做一个生成工资条的公式需要使用到以下几个函数：? ROW：返回单元格的行号。? COLUMN：返回单元格的列号。? MOD：返回两数相除的余数。? OFFSET：以指定的引用为参照系，通过给定偏移量得到新的引用。
以上函数中，通过 OFFSET 函数在工资表中指定一个参照单元格，然后偏移一定的距离，以得到需要的工资数据。
定义的工资条公式如下：
=IF(MOD(ROW(),3),OFFSET('2 月工资'!$A$2,(MOD(ROW()-1,3)&0)*
ROUND(ROW()/3,),COLUMN(A1)-1),&&)
211菜鸟的 Excel 智慧职场路
在定义公式时需要考虑三种情况：
（1）第一行显示工资表的表头。
（2）第二行显示一位员工的工资数据。
（3）显示一个空行。
在公式中可通过函数 MOD(ROW(),3)来得到三种状态，分别为 1、2、0，当计算的结果为 1 或 2 时输出数据，当结果为 0 时输出空行。
公式中的(MOD(ROW()-1,3)&0)* ROUND(ROW()/3,)这一部分计算出 OFFSET 函数的行偏移量，(MOD(ROW()-1,3)&0)的计算结果有三种状态，分别是：FALSE、TRUE、TRUE，当其值为 FALSE 时，不管 ROUND(ROW()/3,)的计算结果为多少，整个算式的结果都为 0，这样就可输出工资表的表头（行偏移为 0）。而当(MOD(ROW()-1,3)&0)的结果为 TRUE 时，将根据 ROUND(ROW()/3,)的计算结果输出一行数据，一个空行。
生成工资表的具体操作如下：
（1）在“工资条”工作表中选中 A1 单元格，输入上面定义的公式。
（2）向右拖动 A1 单元格的填充柄到 N1 单元格。
（3）确保 A1:N1 单元格区域处于被选择的状态，向下拖动填充柄到第 35 行，即可得到如图 9-32 所示的工资条
图 9- 32 工资条9.5.2 工资表的保密
张新专门交待了要给工资表进行保密设置，应该是不允许别人随便打开工资表，以免工资数据外泄。这就需要设置工作簿的打开权限。
（1）在功能区单击“文件”打开 Excel 的后台视图，如图 9-33 所示。212第 9 章 每月的期盼：发薪日
图 9- 33 Excel 后台视图
（2）在后台视图中单击“保护工作簿”按钮，将弹出一个命令列表。
（3）选择“用密码进行加密码”命令，弹出如图 9-34 所示的“加密文档”对话框，在“密码”框中输入密码。
（4）单击“确定”按钮，将弹出如图 9-35 所示的“确认密码”对话框，再次输入密码。图 9- 34 加密文档
图 9- 35 确认密码
（5）单击“确定”按钮，完成加密设置。这时后台视图可看到如图 9-36 所示的提示，表示该文档需要密码才能打开。
（6）保存工作簿后退出 Excel。重新打开该文档时，将出现如图 9-37 所示的对话框，要求用户输入密码，若密码输入错误，将弹出如图 9-38 所示的错误提示。
213菜鸟的 Excel 智慧职场路
图 9- 36 Excel 后台视图
图 9- 37 打开文档要求输入密码
图 9- 38 密码错误提示214第 10 章 人力资源部是服务部门
第 10 章 人力资源部是服务部门
转眼间，李璐到 HG 公司已经一年多了。在这一年多的时间里，通过自己的不懈努力，李璐学习了很多的人力资源管理知识，并将这些知识应用到日常工作中。特别是通过 Excel这个给力工具的辅助，李璐总是能保质保量地完成领导交办的各项任务，得到领导和同事的肯定。
在公司的一次人事调整中，经部门主管张新的推荐，李璐成为了人力资源部的副主管，协助张新管理人力资源部的工作。李璐深知人力资源部是一个服务部门，既要为公司的人力资源需求服务，也要为全公司所有工作的人员做好服务。如对员工的入职、转正、合同签订、福利等做好服务，让员工能感觉到公司对其的关怀。
10.1 混乱的员工信息表
虽然是个副职，大小也算是个领导。领导与普通职员在工作中考虑的问题就是不同，原来做普通职员时，李璐只要把自己负责的工作做好，再完成领导临时安排的一些任务之后，就不用再想什么。可是现在不同了，不但要指导部门同事完成具体的工作，还需要站得更高，去发现工作中的问题，并想办法进行改进。
李璐在查看已有的员工信息表时就发现了很多问题，如图 10-1 所示。
图 10- 1 员工信息表在图 10-1 所示的员工信息表中，李璐发现几个方面的问题：
215菜鸟的 Excel 智慧职场路? 部门名称混乱：如本来是一个部门的，却用多种表示方式输入，如“人资部”和“人
力资源部”，“第一店”和“第 1 店”。这些名称虽然我们可以辨识并知道其含义，但
是 Excel 会将这些看成是不同的内容，在今后汇总查询时将得不到正确的结果。? 职务名称混乱：和部门名称类似，“员工”和“职员”混用。? 身份证号码错误：身份证号码只能是 15 位或 18 位，可表格中有的身份证号码明显位
数不足。? 年龄错误：员工的年龄没有设置为自动更新，有的还显示为去年的年龄。这种滞后的
数据将为统计造成差错。
发现问题，就应马上解决问题。李璐找来负责员工信息管理的何声志，将发现的问题给他讲了，让他尽快纠正这些问题。
10.2 限制输入数据
第二天，何声志将修改后的员工信息表发给李璐，并走过来说：“我改了近一天时间，在修改的过程中发现有时由于输入失误，还是会造成错误的数据，有没有办法限制输入，让错误的内容输不进去。”
李璐：“当然有，要使控制表格中输入的数据，有事前控制和事后检查两种方式。事前控制可以使用 Excel 的数据有效性功能，当输入的数据不符合要求时，给出提示。”10.2.1 设置数据有效性条件
何声志：“数据有效性功能我知道一些。”说着，他开始对部门名称进行操作。
（1）单击选择 C2 单元格，在功能区“数据”选项卡的“数据工具”组中单击“数据有效性”按钮，打开“数据有效性”对话框，如图 10-2 所示。
图 10- 2 数据有效性
（2）在“允许”中选择“序列”，在“来源”中输入公司目前的部门名称“总经办,216第 10 章 人力资源部是服务部门人资部,财务部,开发部,市场部,第一店,第二店”，并选择“忽略空值”和“提供下拉箭头”两个复选框。
（3）单击“确定”按钮后，选中 C3 单元格后，其右侧就会出现下拉箭头，单击下拉箭头就出现了如图 10-3 所示的列表。
图 10- 3 下拉列表框
李璐：“对，这样就可以限制不合要求的数据输入了。”
何声志：“可是，这种方式在图 10-2 中输入部门名称，这部分内容太长了。”
李璐：“可以引用单元格区域，即先在一片单元格区域中输入部门名称，然后在图 10-2的来源部分引用这片单元格区域就可以了。”10.2.2 名称让 Excel 更智能
李璐话锋一转，接着说：“不过，将单格格区域定义为一个名称，然后在图 10-2 中直接引用名称，更简单，更灵活。”
何声志：“名称？每个单元格都有一个名称啊，如 A1、B2 这些就是单元格的名称。”
李璐：“‘名称’是 Excel 的一种特殊称呼，可对一片单元格区域定义一个名称，在需要使用这片单元格区域的地方，直接使用这个名称就可以了。这和 A1、B2 有相似之处，不过，Excel 中的名称具有更强大的功能，除了对单元格区域定义名称之外，也可以对公式、一个数值、一串字符定义名称。”1．定义名称
何声志：“怎么定义名称？”
李璐：“可以使用很多方法定义名称，一般采用的操作步骤如下。”
（1）在工作表中选择需要定义名称的一片单元格区域，如图 10-4 所示。
217菜鸟的 Excel 智慧职场路
图 10- 4 选择单元格区域
（2）在功能区“公式”选项卡的“定义的名称”组中，单击“定义名称”按钮，打开“新建名称”对话框，在“名称”中输入“测试数据”，在“引用位置”将自动显示上一步选中的数据区域，如图 10-5 所示。
（3）单击“确定”按钮完成名称的创建。
（4）创建好名称之后，就可以在工作表中随时调用了，例如，在 D2 单元格中输入以下公式：
=SUM(测试数据)
即可对 A2:B6 单元格中的数据进行求和。
图 10- 5 新建名称
图 10- 6 使用名称2．用名称设置数据有效性
何声志：“哦，我知道了，用名称代替原来单元格区域的引用。在数据有效性的 ‘来源’处也可使用定义的名称。我来改一下员工信息表中 C2 单元格的数据有效性设置。”
（1）在工作簿中新建一个名为“参数”的工作表，在 A 列输入部门名称，如图 10-7所示。
（2）选中 A2:A8 单元格区域，在功能区“公式”选项卡的“定义的名称”组中，单击“定义名称”按钮，打开“新建名称”对话框，在“名称”中输入“部门名称”，在“引用位置”将自动显示上一步选中的数据区域，如图 10-8 所示。218第 10 章 人力资源部是服务部门图 10- 7 部门名称
图 10- 8 新建名称
（3）单击“确定”按钮完成新建名称。
（4）切换到“员工信息”工作表，选中 C2 单元格。
（5）在功能区“数据”选项卡的“数据工具”组中，单击“数据有效性”按钮，打开“数据有效性”对话框，将“来源”中原来输入的内容删除，重新输入“=部门名称”，如图 10-9 所示。这样，“来源”框中的内容就简洁多了。
图 10- 9 数据有效性
（6）单击“确定”按钮，返回工作表进行测试，可以看到，与原来未使用名称时的效果相同。
李璐：“在‘员工信息’表中如职务、学历这些都可以在 ‘参数’表中输入，然后定义为名称，然后在‘员工信息’表中通过数据有效性来引用这些数据，这样，就可大大减少输入带来的错误了。”10.2.3 智能的名称定义
何声志：“在数据有效性功能中使用名称是比较方便。可是，我还有一个疑问，例如，在部门名称这里，如果公司增加一个部门，我又得重新把‘部门名称’这个名称重新定义一下？”
李璐：“可以这样，只需要修改一下名称引用的单元格区域，表格中的其他地方都不
219菜鸟的 Excel 智慧职场路需要改，在下拉列表中就可以看到新增的部门名称了。这种方法对于变动不频繁的名称定义可行，但是，在有些情况下，名称引用列的数据变动比较大，这时可采用另外一种方法，定义动态变化的名称。”
何声志：“名称的定义也可以动态变化？”
李璐：“对，前面提到过，不仅可以将单元格区域定义为名称，也可以将公式定义为名称。因为公式的计算结果是变化的，所以名称的定义也就是可变的了。就以‘部门名称’为例来看看动态名称的优势。”
（1）在功能区“公式”选项卡的“定义的名称”组中，单击“名称管理器”命令按钮打开如图 10-10 所示的“名称管理器”对话框。可看到在名称列表中显示了前面定义的“部门名称”这个名称的信息。
（2）单击选择“部门名称”，再单击“编辑”按钮，打开如图 10-11 所示的“编辑名称”对话框，可对名称进行修改。
图 10- 10 名称管理器
图 10- 11 编辑名称
（3）在“引用位置”中原来是引用一个单元格区域，将其修改为以下公式，则名称就具有了动态数据功能：
=OFFSET(参数!$A$2,,,COUNTA(参数!$A:$A)-1)
何声志：“我测试一下。”
（1）何声志在“参数”工作表中添加了一个部门名称“第三店”，如图 10-12 所示。
（2）切换到“员工信息”工作表，单击 C2 单元格右侧下拉箭头，在下拉列表中果然可以看到新增的部门名称“第三店”，如图 10-13 所示。220第 10 章 人力资源部是服务部门图 10- 12 增加部门名称
图 10- 13 下拉列表中的新增内容
（3）将“参数”工作表中 A9 单元格的“第三店”删除，“员工信息”工作表中的下拉列表框中也没有了“第三店”这个部门了。
何声志：“嘿，真神了。但是，这个公式是什么含义？”
李璐：“在公式中用到了两个函数。下面分别介绍这两个函数的作用。”
COUNTA 函数用来计算单元格区域中不为空的单元格的个数，在公式中将计算“参数”工作表中 A 列不为空的单元格数量。
OFFSET 函数在公式中起主要作用。这个函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。这个函数的参数比较多，使用方法如下：
OFFSET(reference, rows, cols, [height], [width])? reference 作为偏移量参照系的引用区域。在以上公式中指定为“参数!$A$2”，表示以
A2 单元格为参照系。? rows 和 cols 表示相对于参照系的行、列偏移量。在以上公式中未指明数据，表示偏移
量为 0，即行、列都没有偏移。? height 和 width 用来设置所要返回的引用区域的行数和列数。在以上公式中 height 设
置为 COUNTA(参数!$A:$A)-1，即设置返回的引用区域的行数为 A 列非空单元减 1（减
1 是因为从 A2 单元格开始返回数据）。
了解 OFFSET 函数和 COUNTA 函数的作用后，就知道了在上面公式定义的返回的单元格区域引用是从 A2 单元格开始，行数为 A 列非空单元格数量减 1。
何声志：“知道了，当在 A 列中输入新的部门名称后，COUNTA 返回的数就会增加，所以 OFFSET 函数返回的引用也会增多。”
李璐：“对，知道这个公式的含义之后，也就知道了这个公式也有缺陷，就是要求在 A列输入部门名称时，各行之间不能留有空格，否则在下拉列表中显示的数据就会不全。”
何声志已经搞懂了动态名称的定义方法，根据员工信息表中需要用到的数据有效性设置内容，在“参数”工作表中输入了“职务”、“学历”、“民族”、“婚姻状态”等数据，如图 10-14 所示。
接着打开“名称管理器”，将“参数”工作表中新增的几列都定义为动态名称，得到如图 10-15 所示的名称列表。
221菜鸟的 Excel 智慧职场路
图 10- 14 参数
图 10- 15 名称管理器
接着，何声志在“员工信息”工作表中为对应列设置了数据有效性功能。10.2.4 限制身份证号码的位数
何声志：“对于身份证的位数该怎么限制呢？”
李璐：“限制身份证的位数也是通过数据有效性条件来设置，在数据有效性设置中可通过自定义方式使用公式来验证。”
（1）单击选择 E2 单元格。
（2）在功能区“数据”选项卡的“数据工具”组中，单击“数据有效性”按钮，打开“数据有效性”对话框，在“允许”下拉列表框中选择“自定义”，下方将显示“公式”输入框，输入以下公式：
=OR(LEN($E2)=15,LEN($E2)=18)
如图 10-16 所示。
在上面的公式中用到了两个函数，这两个函数的作用是：? LEN 返回文本字符串中的字符数。? OR 函数中可以有多个参数，任何一个参数逻辑值为 TRUE，即返回 TRUE；所有参数
的逻辑值为 FALSE，即返回 FALSE。
以上公式有两个条件，一个是判断 E2 单元格内容的长度为 15，一个判断长度为 18。通过 OR 函数进行组合，只要两个条件中有一个为 TRUE，则表示输入的数据符合要求。
（3）对于给手工输入的单元格设置有效性条件时，最好设置出错提示。这样，当输入的数据出错时，通过错误提示可知道出错的原因。在图 10-16 所示对话框中单击“出错警告”选项卡，显示如图 10-17 所示对话框，在其中输入出错提示。222第 10 章 人力资源部是服务部门图 10- 16 自定义数据有效性
图 10- 17 出错警告
（4）设置好身份证长度验证以后，如果在 E2 单元格输入的长度不符合要求，将显示如图 10-18 所示的错误提示对话框。
何声志：“Excel 中的公式功能真强大，我也得学学公式的使用了。”
图 10- 18 错误提示10.2.5 工号不能相同
何声志：“李主管，我平常在输入工号时，有时由于输入错误会使两个员工的工号相同，我想，用数据有效性加公式也应该能解决这个问题。可是，我对公式还不熟，这个公式该怎么定义呢？”
李璐：“通过数据有效性是可以防止录入重复数据。要使输入的数据和已有数据不重复，也就是输入的值在已有数据中是唯一的。可以用 COUNTIF 函数来计算，具体公式如下：”
=COUNTIF($A:$A,A2)=1
这个公式在 A 列中统计 A2 单元格的值出现的次数，如果是唯一的值，肯定只会出现一次，所以判断其值是否等于 1。若是等于 1，表示 A2 中的值是唯一的，返回逻辑 TRUE，否则，返回逻辑值 FALSE，就会出现错误提示。
在 A2 单元格定义数据有效性条件，如图 10-19 所示。
223菜鸟的 Excel 智慧职场路
图 10- 19 数据有效性10.2.6 用表格自动复制格式
通过一系列数据有效性的设置，何声志开始向新设计的表格中添加员工的信息。添加第一个员工的信息没出问题，可是在输入第二个员的信息时，单击 C3 单元格并没有出现选择部门名称的下拉列表框，如图 10-20 所示。
图 10- 20 无下拉箭头
何声志分析，这是因为前面只是为 C2 单元格设置了数据有效性条件，C3 单元格并没有进行设置，所以就没有下拉列表框出来。但是，如果每个单元格都要进行重复的设置，效率也太慢了吧。
何声志又找到李璐，把这个问题描述了一下。
李璐：“不需要每个单元格单独设置，可以只对每一列进行一次设置。单击列标签中的 C 选中 C 列，再从功能区选择相应的命令按钮来设置数据有效性条件。这样，C 列的每个单元格都可以看到部门名称下拉列表了。如图 10-21 所示。”
何声志：“可是，在表头 C1 单元格也有下拉列表了，这样不好看。还有，有可能误操作把表头的显示内容也修改成了一个部门名称，如图 10-22 所示。224第 10 章 人力资源部是服务部门图 10- 21 下拉列表
图 10- 22 表头也有下拉列表
李璐：“在选择单元格区域时把 C1 单元格排除在外就可以了。不过，不说这种方式了，还有一种更简单的方法，就是把已有数据的区域创建为一个表格，以后你紧接着表格输入的数据会自动应用表格中已设置的各种样式、设置等内容。咱们就用这种方法来做员工信息表。”
（1）先把前面的操作撤消，只保留工作表中第 1、2 行的数据。
（2）单击选择 A1 单元格。
（3）在功能区“插入”选项卡的“表格”组中，单击“表格”按钮，弹出如图 10-23所示的“创建表”对话框。
图 10- 23 创建表
（4）在图 10-23 所示的“创建表”对话框中自动填入了当前数据区域，如果单元格区域不正确，可在工作表中拖动鼠标重新设置。选中“表包含标题”复选框，单击“确定”按钮，即可将当前数据创建一个表格，如图 10-24 所示。新建的表格每列标题右侧都出现一个下拉箭头，是方便进行数据筛选的。
图 10- 24 新建表格
李璐：“好，创建好表格之后，在表格下一行输入数据时，该行将自动添加到表格中去，同时具体有表格中已设置的各种格式效果。”
何声志在 A3 列中输入 10002，然后输入员工的姓名，单击选择 C3 单元格时，久违的下拉箭头终于显示出来了，单击后显示出下拉列表，如图 10-25 所示。
225菜鸟的 Excel 智慧职场路
图 10- 25 下拉列表
何声志马上又检查了一下其他几列设置的数据有效性条件，发现都自动加上了。 在A3 单元格输入与上一行相同的工号“10001”时也会弹出重复工号的提示。
OK，这样输入表格中的数据就要方便多了，一次设置，终身受益！
10.3 身份证号码信息丰富
李璐：“这张表格还需要进行一下处理。原来的表格中，员工的身份证号码内包含的出生日期和我们登记的出生日不一致，需要进行调整。还有，性别其实也可以从身份证号码中计算出来，不用手工输入。”
原来，身份证号码的每位数都有含义，身份证号码有 15 位、18 位之分。早期签发的身份证号码是 15 位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了 18 位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下：
（1）15 位的身份证号码：其中第 1、2 位为省份代码，第 3、4 位为地市代码，第 5、6 位为区县代码，第 7、8 位为出生年份（2 位），第 9、10 位为出生月份，第 11、12 位为出生日期，第 13~15 位为顺序号，并能够判断性别，末位奇数为男性，偶数为女性。
（2）18 位的身份证号码：1~6 位与 15 位的相同，第 7~10 位为出生年份（4 位），第11、12 位为出生月份，第 13、14 位为出生日期，第 15~17 位为顺序号，并能够根据第 17位判断性别，奇数为男，偶数为女。18 位为校验位。
作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0~9，就不会出现 X，但如果尾号是 10，那么就得用 X 来代替，因为如果用 10 做尾号，那么此人的身份证就变成了 19 位。X 是罗马数字的 10，用 X 来代替 10，可以保证公民的身份证符合国家标准。
何声志想，身份证号码的信息含量真丰富，既有所属区域的信息，又有出生日期、还可分辨性别。10.3.1 从身份证号得到生日
知道身份证号码的编码规则，只要输入身份证号，就可将其生日、性别提取出来了。226第 10 章 人力资源部是服务部门省去再次手工输入的麻烦，也可避免输入的错误。
要从身份证号码中取出一部分字符，需要用到 Excel 的字符处理函数。何声志打 开Excel 帮助文件，从中查找出处理文本的函数，查阅了多个文本处理函数后，发觉可能会用到以下几个函数：? MID 函数：返回文本字符串中从指定位置开始的特定数目的字符。? LEFT 函数：返回文本字符串中第一个字符或前几个字符。? RIGHT 函数：根据所指定的字符数返回文本字符串中最后一个或多个字符。
通过 Excel 的帮助，何声志仔细了解这 3 个函数使用方法。通过 MID 函数就可从身份证号码中分别提取年、月、日这 3 个部分，要将这 3 部分组合成 Excel 能识别的日期格式，还需要使用一个名为 DATE 的函数，在 DATE 函数中，分别给出年、月、日三个参数，就可得到一个日期数据了。
从身份证号码中提取生日可分两步，第一步从身份证号中分别提取年、月、日这 3 个数据，第二步将这 3 个数据作为 DATE 函数的参数进行组合，就可得到生日。
理论知识准备充足了，接下来就该开始实践了。
首先单击选择 G2 单元格，在其中输入以下公式：
=DATE(MID(E2,7,4),MID(E2,11,2),MID(E2,13,2))
得到如图 10-26 所示的结果。
图 10- 26 从身份证号中得到生日从图 10-26 所示结果可看出，G2 单元格得到了正确的出生日期，但 G3 单元格中由于E3 单元还未输入身份证号，所以显示的内容为 “#VALUE!”的错误信息。在工作表中应尽量避免出现错误信息，因此需要对公式进行修改。要避免出现错误，就需要对身份证号所在列（E 列）进行判断，如果 E2 为空，则 G2单元格也显示空，若 E2 不为空，再进行生日的提取。公式修改为以下形式：
=IF(E2=&&,&&,DATE(MID(E2,7,4),MID(E2,11,2),MID(E2,13,2)))在以上公式中，用 IF 函数进行判断，若 E2 为空，IF 函数返回空，否则就返回 DATE函数的处理结果。经过这样的修改，公式就具有容错性了。可是，还有个问题。想到容错性，何声志又想起上面的这个公式是按 18 位身份证号码进行处理的，如果是 15 位的身份证号，显然会出错的。还需继续对公式进行修改，既要能处理 18 位，也要能处理 15 位的情况，只有继续使用 IF 函数了。修改后公式如下：=IF(E2=&&,&&,
IF(LEN(E2)=18,DATE(MID(E2,7,4),MID(E2,11,2), MID(E2,13,2)),
227菜鸟的 Excel 智慧职场路
DATE(MID(E2,7,2),MID(E2,9,2),MID(E2,11,2))))
这个公式有点复杂了，用了两层 IF 嵌套。最外层的 IF 判断 E2 单元格是否为空，若为空，直接返回空就结束了。如果 E2 不为空，进入第二层 IF 函数，判断 E2 的长度是否等于 18，若是，则用 DATE(MID(E2,7,4),MID(E2,11,2), MID(E2,13,2))提取生日，若不等于18，则应等于 15（因在 E2 单元格限制了只能为 15 或 18 位），则用 DATE(MID(E2,7,2),MID(E2,9,2),MID(E2,11,2))提取生日。
对这种复杂的、多层嵌套的公式，何声志感觉还是比较难，总是把括号输错。通过多次错误，何声志也总结了一下这种公式的输入技巧：就是分层分步输入。例如，对于上面的公式，可按以下步骤输入：
（1）输入最外层的 IF 函数的完整形式，将不能确定具体内容的部分用一些简单的描述文字来表示，如最外层 IF 函数输入为如下形式：
=IF(E2=&&,&&,&不为空的情况&)
（2）接下来就是在“不为空的情况”部分输入内层的公式。
=IF(E2=&&,&&, IF(LEN(E2)=18, &18 位的情况&,&15 位的情况&))
（3）最后，在“18 位的情况”和“15 位的情况”这两部分中分别输入 DATE 函数组合的日期即可。也可先在其他单元格中将这两种情况的公式输入验证，得到正确的结果后再复制粘贴到上面的公式中。10.3.2 从身份证号得到性别
编写好从身份证号码中提取生日的公式，再定义从身份证号码中得到性别的公就比较简单了。
从身份证号中得到性别分三步：第一步，判断身份证号是 15 位还是 18 位，第二步提取性别识别位（15 位身份证号是取最后一位，18 位身份证号是取第 17 位），第三位判断性别识别位的奇偶，奇数表示男性，偶数表示女性。
要判断一个数的奇偶性，可将该数除以 2，其余数为 0 表示该数为偶数，余数为 1 表示该数为奇数。在 Excel 中，可通过函数 MOD 进行求余数运算。
=IF(E2=&&,&&,
IF(LEN(E2)=18,IF(MOD(MID(E2,17,1),2),&男&,&女&),
IF(MOD(RIGHT(E2,1),2),&男&,&女&))))
何声志选中 F2 单元格，并输入以上公式，公式计算出了性别，如图 10-27 所示。
图 10- 27 取得性别228第 10 章 人力资源部是服务部门10.3.3 计算员工年龄
员工的生日已提取出来了，要知道其年龄就很简单了。也要通过公式进行计算，求出其年龄，并能实时更新。
何声志分析，该怎么计算年龄呢，应该用其生日与当前日期进行运算。
通过查询 Excel 的帮助，知道可使用 TODAY()函数获取当前日期。日期的本质是一个整数，是表示距离
日的天数，如果用当前日期减去生日得到的是该员工出生以来的天数，并不是年龄。
以下公式用当前日期的年减去生日（G2 单元格）中的年，可得到生日：
=YEAR(TODAY()) – YEAR(G2)
可是，这个年龄只是以日期中的年份作为运算依据，一个特殊的情况：某人生日 为，现在的日期为 ，用上面的公式计算出其年龄为 。可他昨天刚满 26，而通常年龄是按周岁计算，因此，这种计算方法还是有问题。
何声志向李璐请教：“计算员工年龄的问题，我找遍了 Excel 的帮助，找不到一个这种功能的函数，直接提取年份计算又有误差，该怎么办？”
李璐：“这个问题可以用一个函数解决，这个函数确实在帮助里找不到。这个函数就是 DATEDIF，这是一个隐藏函数。”
接着李璐就简单说了一下这个函数的使用。
DATEDIF 函数用来计算返回两个日期之间的年、月、日间的隔数。有 3 个参数，按以下格式使用：
DATEDIF(start_date,end_date,unit)
Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。
End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。
Unit 为所需信息的返回类型，可分别设置为以下值，以获得不同的间隔数。? &Y&，返回时间段中的整年数。? &M&：返回时间段中的整月数。? &D&：返回时间段中的天数。? &MD&：返回 start_date 与 end_date 日期中天数的差，忽略日期中的月和年。? &YM& ：返回 start_date 与 end_date 日期中月数的差，忽略日期中的日和年。? &YD&：返回 start_date 与 end_date 日期中天数的差，忽略日期中的年。
李璐：“现在要计算年龄，只需要将第 3 个参数设置为 Y 即可。”
何声志测试了一下 DATEDIF 函数的使用，然后编写出计算年龄的公式如下：
=IF(ISERROR(DATEDIF(G2,TODAY(),&Y&)),& &,DATEDIF(G2,TODAY(),&Y&))
在以上公式中，使用函数 ISERROR 判断计算的结果若为一个错误值，则返回空，否则返回计算结果。
使用 ISERROR 也是一种容错的方法，不过这种方法需要先对公式计算一遍，若计算结果不会产生错误，还需要再次计算一遍公式。
229菜鸟的 Excel 智慧职场路
何声志将以上公式输入到 H2 单元格，公式计算的年龄如图 10-28 所示。
图 10- 28 计算年龄
通过以上的设计，现在的“员工信息”工作表已经非常智能了，大部分内容都只需要单击鼠标在下拉列表中选择即可，这样可保证输入的数据都是符合要求的。而对于性别、出生日期、年龄等可通过身份证号获取的信息也不需要重复输入了，这样也可最大限度保证数据的一致性。
对表格进行这些设置以后，输入的数据的正确性也可得到保证了。
10.4 找出不符合要求的信息
新的“员工信息”工作表已设计出来了，可是面对这么多的员工信息，全部重新输入一遍的工作量太大。有没有什么办法可以将原来有错误信息的工作表中的错误找出来，然后将其修改正确，再粘贴到新设计的“员工信息”工作表中呢？
李璐看到何声志凝思的表情，问道：“还有什么问题没解决？”
何声志将自己的想法说了一遍。
李璐：“有办法。要使控制表格中输入的数据，有事前控制和事后检查两种方式。事前控制可以使用 Excel 的数据有效性功能，当输入的数据不符合要求时，给出提示。咱们前面做的工作就是属于事前控制。你现在提出的想法就属于事后检查了，事后检查可通过Excel 的条件格式来完成。”10.4.1 找出错误的身份证号
何声志：“条件格式？”
李璐：“就是设置一个条件，当单元格的数据满足这个条件时，单元格显示为一种格式，当不满足条件时，单元格显示另一种格式。通过条件格式功能，就可以将不满足条件的数据用一种醒目的格式标出来。”
何声志：“例如，我要将不符合要求的身份证号码找出来，该怎么通过条件格式进行设置？”
李璐：“对于身份证号码，主要是检查其长度，因此需要通过公式的方式来设置。”
（1）选择身份证号码列的所有单元格。
（2）在功能区“开始”选项卡的“样式”组中，单击“条件格式”命令按钮，打开230第 10 章 人力资源部是服务部门如图 10-29 所示的下拉命令列表。
图 10- 29 条件格式
（3）从中选择“新建规则”命令，打开如图 10-30 所示的“新建格式规则”对话框，在该对话框上方列出了规则类型，选择不同的规则类型，下方会出现不同的规则定义界面。从规则类型列表可看到，可根据单元格等于某值、包含指定的值、排名靠前/或靠后、唯一值/或重复值等不同情况来设置格式，对于更复杂的情况，还可以通过公式来设置。
（4）在图 10-30 所示对话框的规则类型列表中选择“使用公式确定要设置格式的单元格”，然后在下方输入以下公式：
=AND(LEN($E2)&&15,LEN($E2)&&18)
以上公判断 E2 单元格内容的长度不等于 15，也不等于 18。
输入公式后的对话框如图 10-31 所示。
在设置条件格式时输入的公式返回值应该是一个逻辑值，当公式返回值为 TRUE 时，就会应用设置的格式，当公式返回值为 FALSE 时，格式将不会发生变化。图 10- 30 新建格式规则
图 10- 31 输入公式（5）在图 10-31 所示对话框中单击“格式”按钮打开“设置单元格格式”对话框，设
231菜鸟的 Excel 智慧职场路置背景色为红色，如图 10-32 所示。
图 10- 32 设置单元格格式
（6）依次单击两个对话框的“确定”按钮返回到 Excel 工作表状态，可看到不符合要求的身份证号码单元格的背景色显示为红色了，如图 10-33 所示。
图 10- 33 突出显示不符要求的身份证号
通过李璐的演示，何声志掌握了条件格式的设置。在原来的“员工信息”工作表中找出身份证号码错误的地方后，通过查询员工档案，将其修改正确。10.4.2 找出不一致的部门名称
何声志发觉 Excel 的条件格式真好用，可能很方便地将特殊的单元格标注出来。决定使用这个功能再从“部门”列中找出不符合要求的部门名称。
（1）拖动鼠标选中“部门”列的全部单元格。
（2）在功能区“开始”选项卡的“样式”组中，单击“条件格式”命令按钮，从下拉命令列表中选择“新建规则”，打开“新建格式规则”对话框，在规则类型中有一项为“只为包含以下内容的单元格设置格式 ”，这个规则应该可以指定具体的值，将不符合要232
办公时间：周一至周五 （9：00-18：00）
销售电话：020-
售后：020-
除特别注明外，作品版权归上传方所有和负责。如果无意之中侵犯了您的权益，请来信告知，本站将在三个工作日内做出处理。