Excel实现工资管理表

【任务目标】

某单位工资管理表从四个方面对职工工资进行日常计算、统计和管理：基本信息统计，各年龄段、职称人数统计，汇总统计，统计图表制作。该单位某月人事部门拿到的职工工资初始表、统计初始表如图2.17和图2.18所示。这些表格需要补齐该月缺失的基本数据，进行格式化设置，再进行一系列计算，最后得到统计结果，供高层决策。即运用Excel数据计算、函数操作等技术，对图2.17所示的职工工资初始表执行一系列数据格式化检查、计算和统计后，得到“职工工资表”，效果如图2.19所示；完成图2.18所示的统计初始表中各个数据的统计以及图表的插入，得到最终统计结果表，效果如图2.20所示。注意，最终完成的职工工资表应在图2.19的基础上隐藏职工的身份证号码。

具体要求为：

（1）完成当月工资文件的建立，包括一个“工资管理”工作簿文件，“工资管理”工作簿中包含“职工工资表”、“统计结果表”工作表。

（2）对“职工工资表”中的相关数据设置有效性条件并输入缺失数据。

①设置“身份证号码”的有效性条件为18位文本。

②设置“参加工作时间”的有效性条件为介于1980年1月1日和2018年12月之间。

③设置“出勤天数”介于0和26之间。

④图2.17所示职工工资初始表的缺失数据，如职工的“身份证号码”、“参加工作时间”和“出勤天数”等列数据需参照图2.19所列数据补齐。

图2.17　职工工资初始表

图2.18　统计初始表

图2.19　未隐藏身份证号码的职工工资表

图2.20　统计结果表

（3）“职工工资表”需按照下列格式化要求来设置。

对“职工工资初始表”的标题行设置跨行居中，字体设置为宋体、14磅、加粗；列标题行字体设置为宋体、12磅、加粗；表格中其他内容的字体设置为宋体、11磅。

（4）需按照如下工资相关的计算逻辑完成“职工工资表”中“年龄”等列数据以及相关合计单元格数据的计算，具体如下。

①根据职工的“身份证号码”计算每位职工的年龄：年龄=现在的年份-出生的年份。

②根据职工的“参加工作时间”计算每位职工的工龄：工龄=现在的年份-参加工作的年份。

③根据“基本工资=工龄×100”的规则计算每位职工的基本工资。

④根据“高级工程师2500、工程师2000、助理工程师1500”的岗位工资标准计算出每位职工的岗位工资。

⑤根据“出勤天数超过20天为加班，加班费标准按每天出勤工资计算”的规则计算出每位职工的加班费。

⑥根据“按正常天数，出勤天数不足20天为缺勤，缺勤按每天出勤工资扣除”的规则计算出每位职工的缺勤扣除。

⑦根据“实发工资=基本工资+岗位工资+加班费-缺勤扣除”的规则计算出每位职工的实发工资。

⑧在“备注”列按实发工资进行排名计算。

⑨在“合计”行（即每列工资的最后一行），计算出各种工资的合计总数。

⑩对“职工工资初始表”按“部门”进行排序操作。

（5）在“统计初始表”中完成一系列统计操作。

①完成“统计初始表”相关的计算操作。

a.分别计算所有职工的平均年龄、平均工龄、平均工资、平均出勤天数。

b.分别计算各职称级别的人数。

c.分别计算各年龄段人数。

d.用“分类汇总”功能计算各部门的工资总金额、加班费总金额。

②根据“统计初始表”各类数据制作相应的统计图表。

a.制作年龄结构饼形图表。

b.制作职称结构饼形图表。

c.制作部门人数柱形图表。

d.制作部门实发工资总数柱形图表。

【任务解析】

（1）通过Excel的创建工作簿、创建工作表功能完成表格文件的建立。

（2）通过Excel的数据验证功能完成工作表中数据列的有效性验证。

（3）通过Excel的字体设置、对齐方式设置等功能完成工作表的格式化设置。

（4）通过Excel提供的各类基本函数，对职工工资表中需要计算的列数据编写计算公式。可使用IF函数计算不同情况下的单元格的取值，例如岗位工资、加班费、缺勤扣除等。

（5）通过Excel提供的各类基本函数，对统计表中需要计算的单元格数据编写计算公式。

①用AVERAGE函数分别计算所有职工的平均年龄、平均工龄、平均工资、平均出勤天数。

②用COUNTIF函数分别计算各职称级别的人数。

③用FREQUENCY（）、COUNTIFS（）函数分别计算各年龄段人数。

④用“分类汇总”功能计算各部门的工资总金额、加班费总金额。

（6）通过Excel的插入图表功能，根据工作表中数据生成需要的图表。

【任务实施】

任务1　创建“工资管理”工作簿

创建“工资管理”工作簿，在“工资管理”工作簿中分别创建工作表“职工工资初始表”、“统计初始表”，工作表内容如图2.17和图2.18所示。将工作簿保存为：工资管理.xlsx。

任务2　对“职工工资初始表”中的相关数据设置有效性条件并输入缺失数据

（1）设置下列数据列的有效性验证条件：“身份证号码”为18位文本；“参加工作时间”介于1980年1月1日和2018年12月之间；“出勤天数”介于0和26之间。

在【数据】选项卡的【数据工具】组中，单击【数据有效性】按钮右侧的三角形按钮，选择【数据有效性】命令，利用“数据有效性”的功能和相应操作分别设置：

①“身份证号码”的有效性条件为：18位文本。

②“参加工作时间”的有效性条件为：介于1980年1月1日和2018年12月之间。

③“出勤天数”的有效性条件为：介于0和26之间。

（2）补录缺失的“身份证号码”“参加工作时间”和“出勤天数”列数据。

按照图2.19所示工作表内容，分别在“职工工资初始表”中的C7、C12、C17单元格录入相关职工的“身份证号码”；在D4、D9、D12、D17单元格中录入相关职工的“参加工作时间”；在M5、M8、M11、M14、M17、M19、M22单元格中补录相关职工的“出勤天数”。

注意：按职工的“姓名”在图2.19中查询相关职工缺失的“身份证号码”、“参加工作时间”和“出勤天数”等数据并录入。

任务3　对“职工工资初始表”进行格式化设置

对“职工工资初始表”的标题行设置跨行居中，字体设置为宋体、14磅、加粗；列标题行字体设置为宋体、12磅、加粗；表格中其他内容的字体设置为宋体、11磅。

利用【开始】选项卡中的【字体】组、【对齐方式】组中的相关命令分别对“职工工资初始表”的标题行、列标题行、表格中其他内容按要求进行格式化设置。

任务4　完成“职工工资初始表”的计算操作

（1）根据职工的身份证号码，计算每位职工的年龄，年龄=现在的年份-出生的年份。(www.chuimin.cn)

查阅NOW（）函数、YEAR（）函数、MID（）函数功能，综合使用这三个函数从身份证号码中取出出生年份。

设置G3=YEAR（NOW（））-MID（C3，7，4），计算出职工“包宏伟”的年龄，然后利用数据填充功能完成其他职工的年龄的计算。

（2）计算每位职工的工龄。

设置H3=YEAR（NOW（））-YEAR（D3），计算出职工“包宏伟”的工龄，然后利用数据填充功能完成其他职工的工龄的计算。

注意：H3=YEAR（NOW（））-YEAR（D3），如果计算的结果（H3单元格）仍是日期类型的格式，应将单元格格式设置为【常规】或【数字】，结果才能正常显示为工龄值。

（3）计算每位职工的基本工资：基本工资=工龄×100。

设置I3=H3*100，计算出职工“包宏伟”的基本工资，然后利用数据填充功能完成其他职工基本工资的计算。

（4）使用IF函数的嵌套计算出每位职工的岗位工资。

岗位工资为：

高级工程师：2500。工程师：2000。助理工程师：1500。

设置J3=IF（E3="高级工程师"，2500，IF（E3="工程师"，2000，1500）），计算出职工“包宏伟”的岗位工资，然后利用数据填充功能完成其他职工岗位工资的计算。

（5）使用IF函数计算每位职工的加班费，出勤天数超过20天为加班。加班费标准按每天出勤工资计算（每天出勤工资：C24单元格数据）。

设置K3=IF（M3＞20，（M3-20）*$C$24，0），计算出职工“包宏伟”的加班费，然后利用数据填充功能完成其他职工加班费的计算。

（6）使用IF函数计算每位职工的缺勤扣除，按正常天数，出勤天数不足20天为缺勤，缺勤按每天出勤工资扣除（每天出勤工资：C24单元格数据）。

设置L3=IF（M3＜20，（20-M3）*$C$24，0），计算出职工“包宏伟”的缺勤扣除，然后利用数据填充功能完成其他职工缺勤扣除的计算。

（7）计算每位职工的实发工资：实发工资=基本工资+岗位工资+加班费-缺勤扣除。

设置N3=I3+J3+K3-L3，计算出职工“包宏伟”的实发工资，然后利用数据填充功能完成其他职工实发工资的计算。

（8）在“备注”列按实发工资进行排名计算。

设置O3=RANK（N3，$N$3：$N$22，0），计算出职工“包宏伟”的工资排名，然后利用数据填充功能完成其他职工工资排名的计算。

（9）在“合计”行（即每列工资的最后一行），统计出各种工资的合计总数。

（10）对“职工工资初始表”按“部门”进行排序操作。

在【数据】选项卡的【排序和筛选】组中，单击【排序】命令，进入【排序】对话框，按“部门”进行“升序”排序。

所有计算和操作完成后，效果如图2.19所示。

任务5　在“统计结果表”中完成对“职工工资初始表”的一系列统计操作

每个统计操作分两步完成：

①完成表中相关的计算操作。

②根据统计表各类数据制作相应的统计图表。

（1）在指定单元格计算所有职工的平均年龄、平均工龄、平均出勤天数。

设置B3=AVERAGE（工资表！G3：G22）；

B4=AVERAGE（工资表！H3：H22）；

B5=AVERAGE（工资表！N3：N22）；

B6=AVERAGE（工资表！M3：M22）

D3=COUNTIF（工资表！G3：G22，"＞43.25"）；

D4=COUNTIF（工资表！H3：H22，"＞20.2"）；

D5=COUNTIF（工资表！N3：N22，"＞4235"）；

D6=COUNTIF（工资表！M3：M22，"＞21.15"）。

（2）统计各年龄段的人数，分别用FREQUENCY（），COUNTIFS（）函数统计各年龄段人数。将结果分别放于人数1、人数2中。年龄段划分为：

青年（年龄＜=30）中年（30＜年龄＜=50）老年（年龄＞50）

设置C9=FREQUENCY（工资表！G3：G22，A9：A11），请根据FREQUENCY函数的操作方法进行后续的操作，计算出C10和C11的值。

设置D9=COUNTIF（工资表！G3：G22，"＜=30"）；

设置D10=COUNTIFS（工资表！G3：G22，"＞30"，工资表！G3：G22，"＜=50"）；

设置D11=COUNTIF（工资表！G3：G22，"＞50"）

（3）统计各职称级别的人数，用COUNTIF函数进行统计。

设置F9=COUNTIF（工资表！E3：E22，"高级工程师"）；

设置F10=COUNTIF（工资表！E3：E22，"工程师"）；

设置F11=COUNTIF（工资表！E3：E22，"助理工程师"）。

（4）制作各年龄段的人数饼形图，制作各职称级别的人数饼形图，如图2.20所示。

①选中相应数据区域，选择【插入】选项卡下【图表】组的【饼图】功能，在【二维饼图】中选择【饼图】图表制作年龄结构图表。

②选中相应数据区域，用类似的方法制作职称结构图表。

（5）进行分类汇总操作。

①插入一张工作表，命名为“分类汇总表”。

②将数据（B2：O22）复制到“分类汇总表”工作表中，前面已按“部门”进行了排序操作，现在可以直接按“部门”分类汇总来计算出各部门的人数（对“部门”计数）。

在【数据】选项卡的【分级显示】组中，单击【分类汇总】命令，进入【分类汇总】对话框，在【分类字段】下选择【部门】，在【汇总方式】下选择【计数】，【选定汇总项】下选择【部门】，勾选【汇总结果显示在数据下方】，最后单击【确定】按钮完成操作。

③使用类似的操作方法按“部门”分类汇总计算出各部门的实发工资总金额、加班费总金额。（“实发工资”“加班费”的汇总方式为“求和”）。需要注意的是，为了保证所有的分类汇总的计算数据都显示出来，分类汇总计算各部门的工资总金额、加班费总金额时，不要勾选【替换当前分类汇总】选项。

④分类汇总结果如图2.21所示。

图2.21　分类汇总表

⑤在“统计初始表”的“分类汇总统计结果”区（B15：F17单元格区域），分别使用公式引用“分类汇总表”中的分类汇总结果数据。

例如：在“统计初始表”中，分别设置B15=分类汇总表！E10，B16=分类汇总表！E21，B17=分类汇总表！E28，B15：F17单元格区域其他数据的引用请依照此方法进行。

⑥使用COUNTIF、SUMIF函数统计各部门的人数和工资总数，对比“分类汇总表”中相应数据，看是否一致。

⑦制作各部门的人数和实发工资总数柱形图表，如图2.20所示。

a.选中相应数据区域，选择【插入】选项卡下【图表】组的【柱形图】功能，在【二维柱形图】中选择【簇状柱形图】图表制作部门人数图表。

b.选中相应数据区域，用类似的方法制作各部门实发工资总数图表。

⑧将“身份证号码”列数据隐藏，对原始数据进行保护。

操作过程：选中“身份证号码”这一列，然后单击【开始】选项卡，再在【单元格】组中单击【格式】按钮，在其下拉式菜单中选择【隐藏和取消隐藏】功能下的【隐藏列】命令。

任务6　保存工作簿

所有操作完成后，把“职工工资初始表”重命名为“职工工资表”，把“统计初始表”重命名为“统计结果表”，并保存工作簿“工资管理”。