我的书签
添加书签
移除书签实例七 员工社保情况统计
7.1案例简介
7.1.1 案例需求与展示
李强是某企业行政部的一名助理，主要负责本公司员工档案的日常管理和员工每年各项基本社会保险费用的统计。针对企业员工的基本情况，现需要李强对本年度12月的员工社保情况进行统计。要求如下：
- 对员工的身份证号进行检验后将正确的身份证号写入“员工档案”表中。
- 统计员工的年龄及工龄工资。
- 统计员工社保费用。
根据以上要求，李强利用Excel中的公式和常用函数很快做好了统计工作。效果如图7-1所示。
图7-1员工社保情况统计效果图
7.1.2知识技能目标
本案例涉及的知识点主要有：公式的使用、单元格的相对引用和绝对引用、常见函数的使用、函数的嵌套等。
知识技能目标：
- 掌握Excel中公式的输入与编辑。
- 掌握单元格的相对引用与绝对引用。
- 掌握IF、VLOOKUP、MID、TEXT、INT、CEILINT、MOD、SUMPRODUCT等常见函数。
7.2案例实现
7.2.1校对员工身份证号
员工的身份证号由18位数字组成，由于数字较多，录入时难免会出现遗漏或错误，为保证身份证号码的正确性，需要对员工录入的身份证号进行校对。思路如下：
- 将员工的身份证号自左向右拆分到对应的列中。
- 将身份证号的前17位数字分别与对应系数相乘，将乘积之和除以11，所得余数即为计算出的检验码。
- 将原身份证号的第18位与计算出的检验码进行对比，比对相符时说明输入的身份证号是正确的，不符时说明输入的身份证号有误。突出显示校对后的错误结果。
需要用到COLUMN、MID、TEXT、MOD、SUMPRODUCT函数实现。
COLUMN函数功能：返回所选择的某一个单元格的列数。
语法格式：COLUMN（reference）
参数说明：reference为可选参数，如果省略，则默认返回函数COLUMN所在单元格的列数。
MID函数功能：从一个文本字符串的指定位置开始，截取指定数目的字符。
语法格式：MID（text,start_num,num_chars）
参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。
TEXT函数功能：将指定的值转换为特定的格式表示。
语法格式：TEXT(value, format_text)
参数说明：value是需要转换的值；format_text表示需要转换的文本格式。
MOD函数功能：返回两个数相除的余数。
语法格式：MOD (number,divisor)
参数说明：number是被除数；divisor为除数。
SUMPRODUCT函数功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
语法格式：SUMPRODUCT(array1, [array2], [array3], …)
参数说明：array1为必选参数，其元素是需要进行相乘并求和的第一个数组；[array2], [array3], …为可选参数，为2 到 255 个数组参数，其相应元素需要进行相乘并求和。此处需要注意，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。
了解了所需要的函数，具体操作如下：
1）打开素材中的工作簿文件“员工社保统计表.xlsx”，切换到“身份证校对”表。
2）选择单元格D3，在其中输入公式“=MID($C3,COLUMN(D2)-3,1)”，输入完成后，按
图7-2身份证号分离后效果（部分）
3）选择单元格V3，在其中输入公式“=TEXT(VLOOKUP(MOD(SUMPRODUCT(D3:T3*校对参数!$E$5:$U$5),11),校对参数!$B$5:$C$15,2,0),”@”)”，按
4）选择单元格W3，并在其中输入公式“=IF(U3=V3,”正确”,”错误”)”，按
5）为了突出显示“错误”的校验结果，可以利用条件格式设置其字体颜色与填充颜色。使单元格区域W3：W102处于选中的状态，切换到“开始”选项卡，单击“样式”功能组中的“条件格式”按钮，在下拉列表中选择“新建规则”命令，打开“新建格式规则”对话框，在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”下方的编辑框中输入公式“=IF($W3=”错误”,TRUE,FALSE”，单击“格式”按钮，打开“设置单元格格式”对话框，在“字体”选项卡中设置“字体颜色”为“标准色”中的“红色”，在“填充”选项卡中设置“背景色”为“浅绿色”，如图7-3所示。单击“确定”按钮，完成条件格式设置。效果如图7-4所示。
图7-3“新建格式规则”对话框
图7-4身份证号校对后效果图（部分）
【校对员工身份证号】视频教程：
7.2.2完善员工档案信息
员工的身份证号中包含了员工的性别、出生日期等信息，所以在员工档案表中录入正确的身份证号很重要。通过上一节的操作，我们已经验证了身份证号的正确性，对于正确的号码，直接将其录入到员工档案表即可；对于错误的身份证号，假设所有错误的号码都是由于最后一位检验码输错导致的，我们可以将错误号码的前17位与正确的验证码连接，即可输入正确的身份证号。此处需要使用IF、VLOOKUP、MID函数的嵌套。具体操作如下：
1）切换到“员工档案表”，选择单元格区域C3：C102，右击鼠标，从弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中选择“常规”选项，单击“确定”按钮，完成选中区域的格式设置。
2）选择单元格C3，在其中输入公式“=IF(VLOOKUP(A3,身份证校对!$B$3:$W$102,22,0)=”错误”,MID(VLOOKUP(A3,身份证校对!$B$3:$W$102,2,0),1,17) & VLOOKUP(A3,身份证校对!$B$3:$W$102,21,0),VLOOKUP(A3,身份证校对!$B$3:$W$102,2,0))”，输入完成后，按
3）利用填充句柄填充公式到C102单元格。
在18位的身份证号中，第17位是判断性别的数字，奇数代表男性，偶数代表女性，可以利用MID函数将第17位数字提取出来，然后利用MOD函数取第17位数字除以2的余数，如果余数为0，则第17位是偶数，也就是该身份证的性别是女性，反之，则说明身份证性别为男性。具体操作如下：
1）选择单元格D3，并在其中输入公式“=IF(MOD(MID(C3,17,1),2),”男”,”女”)”，输入完成后，按
2）再次选择单元格D3，利用填充句柄填充到C102单元格。
在18位的身份证号中，第7至14位是出生日期，可以利用MID函数提取年、月、日数字，然后利用DATE函数进行格式转换。
DATE函数功能：返回代表特定日期的序列号。
语法格式：DATE（year,month,day）
参数说明：year是一到四位数字，代表年份；month代表每年中月份的数字，day代表在该月份中第几天的数字。
了解了所需要的函数，具体操作如下：
1）选择单元格E3，并在其中输入公式“=DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2
))”，输入完成后，按
2）再次选择单元格E3，利用填充句柄填充到E102单元格。
统计了员工的出生日期以后，计算每位员工截止2020年12月31日的年龄，每满一年才计算一岁，一年按365天计算。可以利用DATE函数将2020年12月31日转换成日期型数据，使其与出生日期做减法，得到的结果除以365，再用INT函数取整。
INT函数功能：将数值向下取整为最接近的整数。
语法格式：INT（number）
参数说明：number需要进行向下取整的实数。
了解了所需要的函数，具体操作如下：
1）选择单元格F3，并在其中输入公式“=INT((DATE(2020,12,31)-E3)/365)”，输入完成后，按
2）向下拖动填充句柄填充至F102单元格。效果如图7-5所示。
图7-5统计性别、出生日期、年龄后效果图（部分）
7.2.3 计算员工工资总额
员工的工资总额由工龄工资、签约工资、上年月均资金三部分组成。员工的工龄工资由员工在本公司工龄乘以50得到，员工的工龄以员工入职时间计算，不足半年按半年计、超过半年按一年计，一看按365天计算，计算结果需要保留一位小数。可以利用DATE函数将2020年12月31日转换成日期型数据，使其与入职时间做减法，得到的结果除以365，再用CEILING函数四舍五入。
CEILING函数功能：将参数 Number 向上舍入（沿绝对值增大的方向）为最接近的 significance 的倍数。
语法格式：CEILING(number, significance)
参数说明：number必需参数，表示要舍入的值。significance必需参数，表示要舍入到的倍数。
了解了所需要的函数，具体操作如下：
1）选中K3单元格，并在其中输入公式“=CEILING((DATE(2020,12,31)-J3)/365,0.5)”，输入完成后，按
2）利用填充句柄计算出所有员工的工龄。
3）选择单元格区域K3：K102，打开“设置单元格格式”对话框，切换到“数字”选项卡，在“分类”下方的列表框中选择“数值”选项，设置右侧“小数位数”微调框的值为“1”，如图7-6所示。单击“确定”按钮，完成小数位数的设置。
图7-6“设置单元格格式”对话框
4）选择单元格M3，并在其中输入公式“=K3*50”，按
5）选择单元格O3，并在其中输入公式“=SUM(L3:N3)”，按
图7-7“工资总额”计算完成后效果图（部分）
【计算员工工资总额】视频教程：
7.2.4计算员工社保
本市上年职工平均月工资为7086元，社保基数最低为人均月工资7086元的60%，最高为人均月工资7086元的3倍。当工资总额小于最低基数时，社保基数为最低基数；当工资总额大于最高基数时，社保基数为最高基数；当工资总额在最低基数与最高基数之间时，社保基数为工资总额。利用IF函数即可实现，具体操作如下：
1）切换到“员工档案”工作表，使用
2）切换到“社保计算”工作表，选择单元格B4，右击鼠标，从弹出的快捷菜单中选择“粘贴选项”中的“值”命令，如图7-8所示。
图7-8“粘贴选项”命令
3）选择单元格E4，并在其中输入公式“=IF(D4<7086\*60%,7086\*60%,IF(D4>7086*3,
7086*3,D4))”。按
4）由于每位员工每个险种的应缴社保费等于个人的社保基数乘以相应的险种费率，所以选择单元格F4，并在其中输入公式“=E4*社保费率!$B$4”，按
5）选择单元格G4，并在其中输入公式“=E4*社保费率!$C$4”，按
6）选择单元格H4，并在其中输入公式“=E4*社保费率!$B$5”，按
7）选择单元格I4，并在其中输入公式“=E4*社保费率!$C$5”，按
8）选择单元格J4，并在其中输入公式“=E4*社保费率!$B$6”，按
9）选择单元格K4，并在其中输入公式“=E4*社保费率!$C$6”，按
10）选择单元格L4，并在其中输入公式“=E4*社保费率!$B$7”，按
11）选择单元格M4，并在其中输入公式“=E4*社保费率!$C$7”，按
12）选择单元格N4，并在其中输入公式“=E4*社保费率!$B$8”，按
13）由于医疗个人负担中还有个人额外费用一项，所以在单元格O4中输入公式“=E4*社保费率!$C$8+社保费率!$D$8”，按
14）选中表格中所有的金额数据，即单元格区域D4：O103，打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中选择“货币”选项，设置“小数位数”为“2”，设置“货币符号”为“¥”，如图7-9所示。单击“确定”按钮，完成所选区域的单元格格式设置。
图7-9设置“货币”格式
15）单击“保存”按钮，保存工作簿文件，完成案例的制作。
7.3案例小结
本案例通过对员工社保情况的统计分析讲解了Excel2016中公式和函数的使用、单元格的引用、函数嵌套使用等内容。在实际操作中大家还需要注意以下问题：
（1）当公式中引用了自身所在的单元格时，不论是直接引用还是间接引用，都称为循环引用。中，在单元格A2中输入公式“=1+A2”，由于公式出现在单元格A2中，相当于单元格A2引用了单元格A2，此时就产生了循环引用，公式输入完成，按
图7-10循环引用后的提示框
若必须使用循环引用，且需要得到正确的结果，需要启用迭代计算。如，在单元格A3中输入公式“=A1+A2”，在单元格A1中输入数据“1”，在单元格A2中输入公式“=A3*2”，这样单元A2的值又依赖于A3，而A3单元格的值又依赖于A2，形成了间接的循环引用。此时可进行如下操作：
1）新建一个空白工作簿，单击“文件”按钮，在下拉列表中选择“选项”命令，打开“Excel选项”对话框。
2）单击“公式”选项，在“计算选项”栏中勾选“启用迭代计算”复选框，如图7-11所示。
图7-11“Excel选项”对话框
3）单击“确定”按钮，完成循环引用的设置。
（2）由于Excel内置函数太多，我们无法一一掌握，此时可以利用Excel内置的帮助系统。利用该系统，用户可以解决在使用Excel过程中所遇到的各种问题，包括Excel的新技术、函数说明及应用等。启用Excel的帮助系统，操作如下：
1）打开工作簿窗口，按
图7-12“帮助”窗口
2）在搜索框中输入关键字“RANK函数”，单击“搜索”按钮。即可在窗口界面中显示与“RANK函数”相关的内容。
3）单击“RANK函数”超链接，如图7-13所示。即可在窗口中看到RANK函数的说明、参数含义等内容，如图7-14所示。
图7-13“RANK函数”超链接 图7-14“RANK函数”信息
（3）常见函数举例
日期与时间函数：
- TODAY：一般格式是TODAY（），功能是显示当前的日期。该函数没有参数。
- NOW：一般格式是NOW（），功能是返回当前的日期和时间。该函数没有参数。
- YEAR：一般格式是YEAR（serial-number），功能是返回某日期对应的年份。serial-number为一个日期值，其中包含需要查找年份的日期。
- MONTH：一般格式是MONTH（serial-number），功能是返回某日期对应的月份。
- DAY：一般格式是DAY（serial-number），功能是返回某日期对应当月的天数。
- WEEKDAY：一般格式是WEEKDAY（serial-number，return_type），功能是返回某日为星期几。serial-number为必需的参数，代表指定的日期或引用含有日期的单元格；return_type为可选参数，表示返回值类型。其值为1或省略时，返回数字1（星期日）到数字7（星期六），其值为2时，返回数字1（星期一）到数字7（星期日），其值为3时，返回数字0（星期一）到数字6（星期日）。
7.4经验技巧
7.4.1巧用剪贴板
Office剪贴板是内存中的一块区域，能够暂存Office文档或其他程序复制的多个文本和图形项目，并将其粘贴到另一个Office文档中。通过使用剪贴板，可以在文档中根据需要排列所复制的项目。
Office剪贴板使用标准的“复制”和“粘贴”命令。只需要将项目复制到剪贴板，将其添加到项目集合中，然后就可以随时将其从剪贴板粘贴到任何Office文档中。收集的项目将保留在剪贴板中，直到退出所有Office程序或从剪贴板任务窗格中将其删除。
在日常的工作中，复制和粘贴是最为频繁的操作之一，经常会出现需要多次复制多个文本或图片的情况，如果文本和图片放在不同的文件中，需要每次都要到不同的文件中去复制，会花费很多时间，降低工作效率，此时，利用剪贴板可以很好的解决此问题，操作如下：
1）在打开的工作簿文件中，切换到“开始”选项卡，单击“剪贴板”功能组右下角的对话框启动器按钮，打开“剪贴板”窗格，如图7-15所示。
图7-15“剪贴板”窗格
2）右击需要粘贴的项目右侧的下拉按钮，在下拉列表中选择“粘贴”命令，如图7-16所示。即可实现该条目的快速粘贴。
图7-16“粘贴”命令
需要注意以下事项：
- 剪贴板中可容纳的项目数最多为24个，若超出此限制，则按复制时间的先后次序依次为后来的项目所替换。
- 单击“选项”按钮，可以通过其中的“按Ctrl+C两次后显示Office剪贴板”命令，快速调出Office剪贴板窗口，如图7-17所示。
图7-17剪贴板“选项”命令
7.4.2使用公式求值分步检查
Excel公式运用无处不在，当对公式计算结果产生怀疑，想查看指定单元格中公式的计算过程与结果时，可利用Excel提供的公式求值功能，使用该功能可大大提高检查错误公式的效率。以“员工档案”表为例，可进行如下的操作：
1）切换到“员工社保统计表”工作簿中的“员工档案”表，选择单元格F4。
2）切换到“公式”选项卡，单击“公式审核”功能组中的“公式求值”按钮，如图7-18所示。打开“公式求值”对话框。
图7-18“公式求值”按钮
3）单击“求值”按钮，如图7-19所示。可看到“DATE(2020，12，31)”的值，如图7-20所示。
图7-19“公式求值”对话框
图7-20 DATE函数求值结果
4）继续单击“求值”按钮，最后可在界面 中看到公式计算的结果，最后单击“关闭”按钮，如图7-21所示。单击“重新启动”按钮，可重新进行分步计算。
图7-21 公式最后结果
7.5拓展练习
王明是某在线销售数码产品公司的管理人员，于2020年初随机抽取了100名网站注册会员，准备使用Excel分析他们上一年度的消费情况（效果如图7-22、7-23所示），请根据素材文件夹中的“Excel.xlsx”进行操作。具体要求如下：
1）将“客户资料”工作表中数据区域A1：F101转换为表，将表的名称修改为“客户资料”，并取消隔行的底纹效果。
2）将“客户资料”工作表B列中所有的“M”替换为“男”，所有的“F”替换为“女”。
3）修改“客户资料”工作表C列中的日期格式，要求格式如“80年5月9日”（年份只显示后两位）。
4）在“客户资料”工作表D列中，计算每位顾客到2020年1月1日止的年龄，规则为每到下一个生日，计1岁。
5）在“客户资料”工作表E列中，计算每位顾客到2010年1月1日止所处的年龄段，年龄段的划分标准位于“按年龄和性别”工作表的A列中。
6）在“客户资料”工作表F列中，计算每位顾客2019年全年消费金额，各季度的消费情况位于“2019年消费”工作表中，将F列的计算结果修改为货币格式，保留0位小数。
7）在“按年龄和性别”工作表中，根据“客户资料”工作表中已完成的数据，在B列、C列和D列中分别计算各年龄段男顾客人数、女顾客人数、顾客总人数，并在表格底部进行求和汇总。
图7-22完成后的“客户资料”表效果图
图7-23完成后的顾客人数统计表效果图
