Excel讲解及练习员工工资表

1、模块三 Excel 电子表格制作项目一不变项目二 制作员工工资表（调整了任务和素材）项目说明工资是企业在一定时间内直接支付给本单位员工的劳动报酬，也是企业进行各种费用计提的基础。工资管理是企业管理的重要组成部分，是每个单位财会部门最基本的业务之一。手工进行工资核算，需要占用财务人员大量的精力和时间，并且容易出错，采用计算机进行工资核算可以有效提高工资核算的准确性和及时性。项目分析本项目要求建立员工工资表，计算其中的相关数据，对工资数据进行查询与汇总分析，并打印工资发放条。2010 年 4 月份某公司员工基本工资和出勤情况如表3-2-1 所示。表 3-2-1 2010 年 4 月份某公司员工基本

2、工资和出勤情况表职工代码姓名性别年龄所属部门职工类别基本工资事假 天数病假 天数A001许振男57管理部管理人员3000A002徐仁华女55管理部管理人员28002A003张焱女36管理部管理人员26005B001郑昂男27销售部销售员2000B002李帆男32销售部销售员2000B003吴星男36销售部销售员150015B004唐嘉男45销售部销售员1500B005孙丽女29销售部销售员150020C001许涛男21生产部工人12002C002陈苏苏女26生产部工人1200C003王飞飞女22生产部工人120016任务 1 建立员工工资表并输入基本数据分析或说明：建立基本工资表主要是搭建工资

3、表的框架，在工资表中列出一些有关员工工资的项目，并输入基本数据。在输入数据时为了防止出错可以进行有效性控制。步骤:1、启动 Excel 应用程序，单击“文件”7“另存为”菜单项，在弹出的“另存为”对话框中，输入文件名“工资”，文件类型为“ .xls ”。2、 双击工作表标签，将工作表“ sheetl ”重命名为“ 2010 年 4 月”。3、 在工作表第一行输入如下标题：职工代码、姓名、性别、年龄、所属部门、职工类别、基本工资、岗位工资、奖金、应发合计、事假天数、事假扣款、病假天数、病假扣款、扣款合计、养老金、医疗金、公积金、应发工资、所得税、实发工 资。4、参照表 3-2-1 中的数据输入第

4、一列“职工代码”和第二列“姓名”。5、光标放在 C2 单元格，单击“数据”7“有效性”菜单项，打开“数据有 效性”对话框，如图 3-2-1 所示。6、在“允许”列表中选择“序列”，在“来源”中输入“男，女”（用英文逗 号隔开），单击“确定”按钮完成设置。7、将光标移动到 C2 单元格右下角，当光标变为实心的十字形时，按住鼠标左键向下拖拉，将 C2 单元格的有效性控制复制到C 列其他单元格。图 3-2-1 数据有效性对话框8、根据表格 3-2-1 的数据输入“性别”、“年龄” “所属部门”和“职工类别”等数据，其中“所属部门”和“职工类别”两列数据采用有效性控制输入，最后结 果如图3-2-2 所

5、示。E3Microsoft ExceL -工侯，sis 017巴文件迢）鋼辑迢）视囹（町 播入 格武）工且 数据 窗口（町 帮助01）-X图 3-2-2 2010 年 4 月工资表任务 2 计算“岗位工资”、“奖金”和“应发合计”分析或说明：IF 函数可以根据设定的条件在相应单元格中自动填入数据。在2010 年 4月工资表中，岗位工资和奖金根据每位员工的职工类别不同而有所不同，具体情况如下表3-2-2 所示。本任务是先用 IF 函数输入岗位工资和奖金，然后用公式“应发合计=基本工资+岗位工资+奖金”计算应发合计。表 3-2-2 岗位工资和奖金表职工类别岗位工资奖金管理人员500200工人200

6、300销售员300奖金=销售额X提成比例（销售额 3 万元，提成比例为 2%,3 万元w销售额V10 万元，比例为 4%,销售额10 万元， 比例为8%）步骤：1、光标放在 H2 单元格，单击“插入”T“函数”菜单项，在弹出的“粘贴专jA晶心*3出空I也J蝴E豪屯氧亠八八A包1234567职工代码A001A002A003B001B002另准男女女男男华名振仁&昂帆姓许徐张郑李D EF车龄倂属部门职工类别57 管理部管理人员55管理部36管理部27 销售部32 销售部管理人员管理人员硝售员销售员8瓦101112B003B004B005C001C002C003陈苏苏王飞飞364529销售部

7、销售部销售酩21生产部26 生产部22 生产部人人人.ITTG基本工资30002800260020002000150015001500120012001200H _产 岗位工茁 n 20104H/Sheet2/Sheet3/2、在弹出的“函数参数”对话框中，输入各项参数，如图3-2-3 所示。函数”对话框中选择 IF 函数，单击“确定”按钮。图 3-2-3 if函数参数对话框之一3、用鼠标按住 H2 单元格右下角的填充柄向下拖拉复制函数，输入其他人员 的岗位工资。4、打开素材文件夹中的“销售额.doc ”文档，单击“编辑”7“复制”菜单 项，复制全部数据。5、光标放在工作表 sheet2 的

8、A1 单元格，单击“编辑”宀“粘贴”菜单项，将 Word 文档表格中的数据转换成Excel 工作表，双击工作表标签“ sheet2 ”，将其重命名为“销售表”。6、光标放在“ 2010 年 4 月”工作表的 I2 单元格，单击“插入”7“函数” 菜单项，在弹出的“粘贴函数”对话框中选择 IF 函数，单击“确定”按钮。7、在弹出的“函数参数”对话框中，输入各项参数，所图3-2-4 所示。8、用鼠标左键按住 I2 单元格右下角的填充柄向下拖拉复制函数，输入其他人员的奖金，结果如图3-2-5 所示。9、光标放在 I5 单元格，单击“插入”7“函数”菜单项，在弹出的“粘贴 函数”对话框中选择 IF 函

9、数，单击“确定”按钮。10、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-6 所示。在Excel 编辑栏内可得到如下 IF 函数的表达式：“ =IF(F5=销售员,IF(销售表！E2=10,销售表！E2*8%,销售表！E2*4%)*10000) ”。11、 用鼠标左键按住 I5 单元格的填充柄， 向下拖拉到 I9 为止， 输入其他销售 员的奖金。12、单击 J2 单元格，输入公式：“ =G2+H2+I2 ”，单击“确定”按钮，计算应发合计。13、用鼠标左键按住 J2 单元格的填充柄，向下拖拉输入其他人员的应发合计。图 3-2-4 if函数参数对话框之二口口Microsoft Exc

10、el -工資工資. .sis目回目回I: :色文件迥色文件迥編編辑氓）辑氓）视图视图M撅入撅入（D格式格式（工具工具（D数数摇迥摇迥窗口窗口（D= =帮助帮助徂）徂）-fl x：- -討討A弩丄启弩丄启 7 7 匕匕 - -”出出I 3 “肓肓二二12JBf屯土屯土_BK14EFGHIJ1所属部门职T类别基本 T 资岗位工资 奖金应发合计 事假：2管理部管理人员30005002003管理部管理人员23005002004管理部管理人员26005002005销售部销售员2000300销售员6销售部销售员2000300销售员1销售部销售员1500300销售员8销售部销售员1500300销售员9销售部

11、销售员1500300 销售员10 生产部工人120020030011 生产部工人12002003Q012生产部工人120020030014 4制2010 年4月/销售/Sheet3/就绪就绪数字数字图 3-2-5 计算奖金瓯数参數冈=9600Logical.test任何一个可判断为 TRUE 或 FALSE 的数值或表达式计算姑果=9600有关T哀函数的帮助00图 3-2-6 if函数参数对话框之三任务 3 计算“事假扣款”、“病假扣款”和“扣款合计”分析或说明：该公司规定，病事假应扣款额和病事假天数有关，具体扣款金额如下表3-2-3 所示。本任务利用 IF 函数计算病事假扣款，然后用公式“扣

12、款合计=事假扣款+病假扣款”计算扣款合计。表 3-2-3 病事假扣款表病事假天数应扣款金额（元）事假病假工人其他人员 15应发合计X80%400500步骤：1、 光标放在 L2 单元格中，单击“插入”7“函数”菜单项，在弹出的“粘贴函数”对话框中选择 IF 函数，单击“确定”按钮。2、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-7 所示。在编辑栏内得到 IF 函数的表达式为：“ =IF（K215,J2/22*K2,J2*80%）取涓TRUE9600判断一卜乗件星否满足.如果満足返回一个值.如果不满星则返回另一个值IFT.ogLcal_test 任何一个可判断为 TEUE 或 FA

13、LSE 的数值或表这式计算結果=有关该函数的帮勛伯）图 3-2-7 if 函数参数对话框之四3、用鼠标左键按住 L2 单元格右下角的填充柄向下拖拉，计算其他人员的事 假扣款。4、光标放在 N2 单元格中，单击“插入”T“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。5、在弹出的“函数参数”对话框中，输入各项参数，所图3-2-8 所示。在编辑栏内得到 IF 函数的表达式为：=IF（M2=0,0,IF（M2=15,IF（F2= 工人 ,200,300）,IF（F2= 工人,400,500）。6、 用鼠标左键按住 N2 单元格右下角的填充柄向下拖拉，计算其他人员的病 假

14、扣款。7、 在 02 单元格中输入公式：“ =L2+N2，单击“确定”按钮，计算扣款合计。8、 用鼠标左键按住 O2 单元格右下角的填充柄向下拖拉，计算其他人员的扣款合计。确定取梢=0判断一个祭件是辞足*如果扁足返回一亍值，如果苹禱足则返回另一个图 3-2-8 if函数参数对话框之五9、 选择 L2 : 012 数据区域，单击“格式”“单元格”菜单项，在弹出的对话框中单击“数字”选项卡。10、在“数字”选项卡的“数值”分类中，将事假扣款、病假扣款、扣款合计 的小数位数设置为 2 位，如图 3-2-9 所示。 Microsoft Excel一 工養.xl呂任务 4 计算“应发工资”分析或说明：应

15、发工资=应发合计-扣款合计-养老金-医疗金-公积金。其中，养老金按应发合计的 8%扣除，医疗金按应发合计的 2%扣除，公积金按应发合计的10%扣除。本任务先分别计算“养老金”、“医疗金”和“公积金”，然后再用上述公式计算应发工资。 步骤：1、 光标放在 P2 单元格中，输入公式：“ =J2*8% ”，按回车键算出养老金。2、 用鼠标左键按住 P2 单元格右下角的填充柄向下拖拉，计算其他人员的养老金。3、 光标放在 Q2 单元格中，输入公式：“ =J2*2% ”，按回车键算出医疗金。4、 用鼠标左键按住 Q2 单元格右下角的填充柄向下拖拉，计算其他人员的医疗金。5、 光标放在 R2 单元格中，输

16、入公式：“ =J2*10% ”，按回车键算出公积金。6、 用鼠标左键按住 R2 单元格右下角的填充柄向下拖拉，计算其他人员的公积金。:宋体12% TaP18*KLN0PQ1事假天数事假扣款病假天数病假扣款 扣款合计养老金医疗签20. 0000.0032318. 180318. 1840. 005300300. 0050. 0000. 0060. 0000. 00715 4224. 0004224. 0080 0000.0090. 0020500500. 00100. 002200200. 00110. 0000. 00120. 0016400400. 00V14 4 n2010年4月/铛書表/

17、弘歸诣/|就细数宇：可 文件 编辑 视图 描入 格式迫）ZM（I）数据 窗口:帮助-iS X图 3-2-9各项扣款7、 光标放在 S2 单元格中，输入公式：“ =J2-O2-P2-Q2-R2 ”，按回车键算出应发工资。8、 用鼠标左键按住 S2 单元格右下角的填充柄向下拖拉，计算其他人员的应发工资，如图 3-2-10 所示。图 3-2-10 计算应发工资任务 5 计算“所得税”和“实发工资”分析或说明：我国相关税法规定，工资、薪金所得应缴纳个人所得税。即每月取得工资收入后，先减去个人承担的基本养老保险金、医疗保险金和失业保险金，以及按省级政府规定标准缴纳的住房公积金，再减去 2000 元/月（

18、个人所得税起征额为 2000 元），为应纳税 所得额，按5%至 45%的九级超额累进税率计算应缴纳个人所得税。计算公式为：“个人所得税=应纳税所得额X税率-速算扣除数”。表 3-2-4 所得税税率表级数全月应纳税所得额税率%速算扣除数（元）1不超过 500 元的502超过 500 元至 2000 元的部分10253超过 2000 兀至 5000 兀的部分151254超过 5000 兀至 20000 兀的部分203755超过 20000 元至 40000 元的部分2513756超过 40000 元至 60000 元的部分3033757超过 60000 元至 80000 元的部分3563758超过

19、 80000 元至 100000 元的部分40103759超过 100000 元的部分4515375步骤：1、 光标放在 T2 单元格，单击“插入”T“函数”菜单项，在弹出的“粘贴函数”对 话框中选择 IF 函数，单击“确定”按钮。2、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-11 所示。在编辑栏内得 到IF函 数 的 表 达 式 为：“ =IF(S2-2000500,0,IF(S2-20002000,(S2-2000)*10%-25,IF(S2-20005000,(S2-2000)*15%-125,IF(S2-200020000,(S2-2000)*20%-375, 复核)”

20、。3、 考虑到该企业员工的应发工资在扣除2000 元之后，没有超过 20000 元的，所以 IF函数只判断到 S2-2000368 260 0035+4 2563. 60200. 0013634：1701160.000 1160.000. 00136341701360.000 1360.00100. 0013634170960,000960, .00M0PTU0002160. 00270 2160.000 2160.00545002000400“中”，IF （成绩V90, “良”，“优”）”。表 3-2-5 学生成绩表成绩等级成绩V60不及格60成绩V70及格70W成绩V80中80W成绩V90良90w成绩优2、有效性控制Excel 强大的制表功能给我们的工作带来了方便，但是在数据录入过程中难免会出错， 比如重复的身份证号码， 超出范围的无效数据等。 其实可以通过有效性控制来限定输入的内 容，以避免错误数据的输入。 把既定的规律化的输入内容作为选项，在有效性设置对话