员工档案及工资管理

任务目标:

1. 通过数据有效性、条件格式限定输入范围、文本长度，避免数据重复出现。

2. 通过公式或函数从身份证号获取员工的基本信息

3.在不连续的单元格中输入相同的数据

4.通过公式和函数对员工档案中的信息进行统计，分析，汇总。

5.通过VLOOKUP函数调用数据

6.设计辅助表快速计算个人所得税

7.通过Round函数使得个税计算结果精确显示

8.保护工作表中的基础数据，隐藏计算公式

9.通过公式和函数批量生成工资条。

步骤：

1. 定义名称

1）打开案例“员工档案及工资表.xlsx”, 在工作表“员工档案”中，按下ctrl +A组合键选择整个数据里诶包A1：N70，在名称输入框中"all" ,按下enter，定义整个数据列表。

2）在"公式"选项卡上的“定义的名称”组中单击“定义名称”按钮，打开“新建名称”对话框，在“名称”文本框中输入用于引用的名称“单位工龄工资”，在“范围”下拉列表框中选择“工作簿”，在“备注”中输入对该名称的说明。在“引用位置”框中输入“=80”，点击“确定”。

3）A1：N70选中，在“公式”选项卡上的“定义名称”组中，单击“根据所选内容创建”按钮，打开“以选定区域创建名称”对话框，选中“首行”复选框，取消其他复选项，单击"确定". 点击“名称管理器"就可以看刚才把表中你的每一列就添加了名称。

2. 完善档案表数据

1）在“名称框”下拉列表中选择“性别”以选中相应的列区域。在"数据"选项卡上的“数据工具”组中，单击“数据有效性”按钮，打开“数据有效性”对话框。在“设置”选项卡中，从“允许”下拉框中选择“序列”，在“来源”文本框中依次输入数值“男,女”两个值，用西文逗号隔开。确保勾选“提供下拉箭头”，然后单击“确定”。

2）从“名称框”下拉列表中选择“身份证号”以选中相应列区域。在"开始"选项卡上的“样式”组中，单击“条件格式”按钮，打开规则下拉列表。从“突出显示单元格规则”下选择“重复值”命令，打开“重复值”对话框。在“设置为”下拉列表中指定格式“绿填充色深绿色文本”。单击“确定”。查看“身份证号”列，发现有重复项，我们采用排序方式，用身份证号，单元格颜色排序，会发现“任飞燕”和“于小谦”的身份证号相同，经比对"于小谦“的身份证号输入错误，我们修正为“410322198903066121”.

3) 从“名称框”中选择“身份证号”，在“数据”选项卡，“数据工具”组中，单击“数据有效性”，在"允许”下拉条中，选择“文本长度”，从“数据”下拉条中选择“等于”，在“长度”框中输入“18”，单击“出错警告”选项卡，输入错误信息“身份证号不是18位！”，单击“确定”。

4）在D2单元格中单击，输入公式“= if(ISODD(MID(C2,17,1)),"男","女")”按enter提取性别，依次拖动填充柄，自动填充D列数据。

5）在单元格E2单击，输入公式"=date(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))",依次拖动填充柄，自动填充E列数据。

6) 在单元格F2单击，输入公式“=INT(YEARFRAC(E2,TODAY(),1))”,依次拖动填充柄，自动填充F列数据。

7)单击K2单元格，输入公式"==ROUND(DAYS360(J2,TODAY())/365,0)", 依次拖动填充柄，自动填充K列数据。这里工龄采用了四舍五入的计算，还可以直接取整数，"=INT(DAYS360(J2,TODAY())/365)", 还可以采用计算F列年龄的公式，计算工龄。

8）单击M2单元格，输入公式"== K2 * 单位工龄工资", 前面我们曾定义过一个常量" 单位工龄工资 = 80". 按enter。然后自动填充M列。在N2单元格点击，输入公式"=L2+M2",按enter，然后自动填充N列。

3. 对档案表信息进行统计分析

1）单击工作表“统计”，进入统计工作表，点击C3单元格，输入公式“=COUNTIF(部门,B3)”，然后按下enter。然后，拖动填充柄，填充到C9， 点击C10单元格，输入公式"=SUM(C3:C9)" 然后按下enter。或者输入“=COUNTA(员工编号)”.

2) 单击单元格D3，输入公式“=COUNTIFS(部门,B3,性别,"女")”，然后按下enter。然后，拖动填充柄，填充到D9， 点击D10单元格，输入公式"=SUM(D3:D9)"然后按下enter。

3)单击E3单元格，输入公式"=AVERAGEIF(部门,b3,基本工资)",，然后按下enter。然后，拖动填充柄，填充到e9， 点击e10单元格，输入公式"=AVERAGE(基本工资)"然后按下enter。

4)单击F3单元格，输入公式"=AVERAGEIFS(基本工资,部门,B3,性别,"女")",然后按下enter。然后，拖动填充柄，填充到F9， 点击F10单元格，输入公式"=AVERAGEIF(性别,"女",基本工资)"然后按下enter。

5)点击G单元格，输入公式"=MAX(IF(部门=B3,基本工资))", 然后按下 ctrl + shift + enter组合键。然后，拖动填充柄，填充到G9， 点击G10单元格，输入公式"=MAX( 基本工资)"然后按下enter。

6) 点击H单元格，输入公式"=MIN(IF(部门=B3,基本工资))",然后按下 ctrl + shift + enter组合键。然后，拖动填充柄，填充到H9， 点击H10单元格，输入公式"=MIN( 基本工资)"然后按下enter。

4. 计算研发人员工资。

1) 点击B5单元格，输入" '001 ",注意是西文的单引号,然后拖动填充柄，自动填充B6：B34区域。

2）点击D5单元格，输入公式"=vlookup(c5,all,2,false)"然后按下enter。然后拖动填充柄，自动填充D6：D34区域。

3)点击E5单元格，输入公式"=VLOOKUP(C5,all,7,FALSE)",然后按下enter。然后拖动填充柄，自动填充E6：E34区域。

4)点击F5单元格，输入公式"=VLOOKUP(C5,all,14,FALSE) ", 然后按下enter。然后拖动填充柄，自动填充F6：F34区域。

5) 点击H5单元格，首先选择H5：H27，按下ctrl键不放，再选择H29:H34,输入数字260，同时按下ctrl+enter，批量输入交通补贴。

6）应付工资总额 = 基本工资+奖金+补贴-扣除病事假。 点击J5单元格，输入公式"=F5+G5+H5-I5",然后按下enter.然后拖动填充柄，自动填充J6：J34区域。

7)应纳税所得额 = 应付工资总额 - 扣除社保 - 费用减除标准3500，当计算结果小于0是，应纳所得税为0，然后得到其他值，查看税率表，计算对应的税率。点击L5，输入公式“=IF((J5-K5-3500)>0,J5-K5-3500,0)”,然后按下enter。然后拖动填充柄，自动填充L6：L34区域。

8)个人所得税 = 应纳税所得额*对应税率 - 对应速算扣除数。  其中税率和速算扣除数通过查阅工作表“税率表”获得。 

税率 = IF(L5>80000,45%,IF(L5>55000,35%,IF(L5>35000，30%,IF(L5>9000,25%,IF(L5>4500,20%,IF(L5>1500,10%,3%))))))

速算扣除数 =IF(L5>80000,13505,IF(L5>55000,5505,IF(L5>35000，2755,IF(L5>9000,1005,IF(L5>4500,555,IF(L5>1500,105,0))))))

那么个人所得税 = L5 * IF(L5>80000,45%,IF(L5>55000,35%,IF(L5>35000，30%,IF(L5>9000,25%,IF(L5>4500,20%,IF(L5>1500,10%,3%)))))) - IF(L5>80000,13505,IF(L5>55000,5505,IF(L5>35000，2755,IF(L5>9000,1005,IF(L5>4500,555,IF(L5>1500,105,0)))))) ,然后按下enter。然后拖动填充柄，自动填充M6：M34区域。

9)实发工资 = 应付工资总额  -  扣除社保 - 个人所得税。  点击N5, 输入公式 " = J5-K5-M5",然后按下enter。然后拖动填充柄，自动填充N6：N34区域。

5. 对档案及工资表进行保护。 通常情况下，一个公司员工的档案及工资水平都是保密额，为了防止他人修改工作表的内容，可以设置工作表保护，一来限制他人改动基础数据，二来防备他人查看并修改公式。具体做法如下：

1）在工作表“员工档案”中单击单元格C2，在“视图”选项卡上的“窗口”组中，单击“冻结窗格”按钮，打开下拉列表。从下拉列表中选择“冻结拆分窗格”。C2单元格左侧的两列就固定，左右固定，C2上的标题行业固定。选中L,M,N三列，在M列上单击右键，选择“隐藏”命令。在“审阅”选项卡上的“更改”组中，单击“保护工作表”按钮。在弹出的对话框中输入密码“123”。 可以保护员工档案。

2) 隐藏计算公式，点击工作表标签“研发人员工资表”，依次选择奖金、补贴、扣除病事假、扣除社保4列数据 ，点击“开始”选项卡，在“单元格”组中点击“格式”按钮，在下拉菜单中选“锁定单元格”。 在“开始”选项卡上的"编辑"组中，单击“查找和选择”按钮，在下拉列表中选择“公式”，所有包含公式的列被选中，在“开始”选项卡上的“单元格”组中，单击“格式”按钮，从打开的下拉列表中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，在“保护”选项卡中勾选“隐藏”，勾选“锁定”。然后单击确定。在"审阅"选项卡上的“更改”组中，单击“保护工作表”，打卡“保护工作表”对话框中，输入密码“123”。