Excel讲解及练习--员工工资表

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

2、XX性别年龄所属部门职工类别基本工资事假天数病假天数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应用程序，单击“文件”一“另存为”菜单项，在弹出的“另存为”对话框中，输入文件名“工资”，文件类型为“.xls2、 双击工作表标签，将工作表“sheetl”重命名为“2010年4月”。3、 在工作表第一行输入如下标题：职工代码、XX性别、年龄、所属部门、职工类别、基本工资、岗位工资、奖金、应发合计、事假天数、事假扣款、病假天数、病假扣款、扣款合计、养老金、医疗金、公积金、应发工资、所得税、实发工资。4、 参照表3-2-1中的数据输入第一列“职工代码”和第二列“XX'。5、 光标放在C2

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

5、且舷塔窗口处）筝劲坦）闫X：心&色4号乳I语T二TJ»J一£X，H幽/DT7色1职工代码2 A0013 Q0Z4 A002.S源七成姓许徐张1 2 o Q o O B B昂帆郑李3 4o O o OE Bo o O Ono B c C匚孙许陈B C | D E F 6 H V 性别年龄所属部门职工类别甚本工资岗位工罐 另57管理部管理人月3000女t55管理部管理人员2800女38管理部管理人负2600.男27销售部铺售员2000另32瑜哲部场售：2pop-J另36惜管部销售员1500_男）45销售部 铺售员1500 女29南省部的官员_1500美 21生产部 1.

6、 A1200女26生产部工人120012H *C003王飞飞女的生产部LA12007卜nKSO1O4M/Sh&et2/Shet3/|<>|较事城咕图3-2-22010年4月工资表任务2计算“岗位工资”、“奖金”和“应发合计”分析或说明：IF函数可以根据设定的条件在相应单元格中自动填入数据。在2010年4月工资表中，岗位工资和奖金根据每位员工的职工类别不同而有所不同，具体情况如下表3-2-2所示。本任务是先用IF函数输入岗位工资和奖金，然后用公式“应发合计=基本工资+岗位工资+奖金”计算应发合计。表3-2-2岗位工资和奖金表职工类别岗位工资奖金管理人员500200工人2003

7、00销售员300奖金=销售额X提成比例（销售额<3万元，提成比例为2%,3万元直销售额<10万元，比例为4%,销售额）10万元，比例为8%）步骤：1、光标放在H2单元格，单击“插入”一“函数”菜单项，在弹出的“粘贴3-2-3所示。2、 在弹出的“函数参数”对话框中，输入各项参数，如图函戴薛敦因&= TRUE-am %= 200IFLflgleal_te£i£2='管理人员"T_true500Talue_lf_fil£eif生2=+聚售直I。，20。)=500判断一个条件是否濡足,如果湍足返回一个值，如果不请足则返回另T也Val

8、uc_if_fal=c为Logical-tMt为FALLS时打返回值一如果忽略，则返回FALSE胡定 | | 附泊 |计算结果500有美泛医皴前帮助G)图3-2-3if函数参数对话框之3、 用鼠标按住H2单元格右下角的填充柄向下拖拉复制函数，输入其他人员的岗位工资。4、 打开素材文件夹中的“销售额.doc”文档，单击“编辑”一“复制”菜单项，复制全部数据。5、 光标放在工作表sheet2的A1单元格，单击“编辑”一“粘贴”菜单项，将Word文档表格中的数据转换成Excel工作表，双击工作表标签“sheet2”，将其重命名为“销售表”。6、 光标放在“2010年4月”工作表的I2单元格，单击“插

9、入”一“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF函数，单击“确定”按钮。7、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-4所示。8、 用鼠标左键按住I2单元格右下角的填充柄向下拖拉复制函数，输入其他人员的奖金，结果如图3-2-5所示。9、 光标放在I5单元格，单击“插入”一“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF函数，单击“确定”按钮。10、在弹出的“函数参数”对话框中，输入各项参数，所图3-2-6所示。在Excel编辑栏内可得到如下IF函数的表达式：“=IF(F5="销售员”,IF(销售表！E2<3,销售表！E2*2%,IF(销售表！E2&

10、gt;=10,销售表！E2*8%,销售表！E2*4%)*10000)”。11、用鼠标左键按住I5单元格的填充柄，向下拖拉到I9为止，输入其他销售员的奖金。12、单击J2单元格，输入公式："=G2+H2+I2”，单击“确定”按钮，计算应发合计。13、用鼠标左键按住J2单元格的填充柄，向下拖拉输入其他人员的应发合计。£救法敷IFLoifcal_testV31ua_lf_fiLce二 200判断满足，如果丽足返回一个值，如果不满足叫返回另一个值Loslcal_tcst任何一力可判断为THUE或FALSE的其值或超太苴计算姑平=工口百千芭回我为黏助KE)图3-2-4 if函数参数对

11、话框之二E N - CPU -ri '1. Fl 'r -工程 xl %上回三:其文件旧编辑旧初图M：锣助®=城am u：宋体, 12K14, A_ (5 x一具数据1出口 .M)事假:2管理部管理人员3000EOO2003管理部管理人员2800EOO2004管理部管理人员260C5002005箱信部销售员2000S0C铛售员6希售怖销售员20003口 口铛售员7精信部销售员150c匆1铛售员8俏信部销售员1S0D30D带售员9销售部销售员1S0D20 口错售员10生产部工人120C20C33011生产部工人120020050012生产都工人120C2OC330所属部

12、门嬴工类别基本T胫岗鱼工登奖金应支合计1秋字* *卜卜1就麻EFGH I I图3-2-6if函数参数对话框之三任务3计算“事假扣款”、“病假扣款”和“扣款合计”分析或说明：该公司规定，病事假应扣款额和病事假天数有关，具体扣款金额如下表3-2-3所示。本任务利用IF函数计算病事假扣款，然后用公式“扣款合计=事假扣款+病假扣款”计算扣款合计。表3-2-3病事假扣款表病事假天数应扣款金额（元）事假病假工人其他人员<15（应发合计/22）X事假天数200300>15应发合计X80%400500步骤：1、 光标放在L2单元格中，单击“插入”一“函数”菜单项，在弹出的“粘贴函数”对话框中选择I

13、F函数，单击“确定”按钮。2、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-7所示。在编辑栏内得到IF函数的表达式为：“=IF（K2<15,J2/22*K2,J2*80%）”。=0判断1个茨件是否清足，如果满足退回一个值，如所满足明返回另一不懂LcElcaJ_tCEt任闹一个可判断WTRUE或mE的数值或假式计算站平-0有全过函效的竟助KE）南定J-图3-2-7if函数参数对话框之四3、 用鼠标左键按住L2单元格右下角的填充柄向下拖拉，计算其他人员的事假扣款。4、 光标放在N2单元格中，单击“插入”一“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF函数，单击“确定”按钮。

14、5、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-8所示。在编辑栏内得到IF函数的表达式为：=IF（M2=0,0,IF（M2<=15,IF（F2="工人”,200,300）,IF（F2="工人",400,500）。图3-2-8if函数参数对话框之五6、 用鼠标左键按住N2单元格右下角的填充柄向下拖拉，计算其他人员的病假扣款。7、 在O2单元格中输入公式：“=L2+N2”，单击“确定”按钮，计算扣款合计。8、 用鼠标左键按住O2单元格右下角的填充柄向下拖拉，计算其他人员的扣款合计。“单元格”菜单项，在弹出的9、 选才iL2:O12数据区域，单击“格

15、式”对话框中单击“数字”选项卡。10、在“数字”选项卡的“数值”分类中，将事假扣款、病假扣款、扣款合计的小数位数设置为 2位，如图3-2-9所示。图3-2-9 各项扣款任务4计算“应发工资”分析或说明：应发工资=应发合计-扣款合计-养老金-医疗金-公积金。其中，养老金按应发合计的8%扣除，医疗金按应发合计的2%扣除，公积金按应发合计的10%扣除。本任务先分别计算“养老金”、“医疗金”和“公积金”，然后再用上述公式计算应发工资。步骤：1、光标放在P2单元格中，输入公式："=J2*8%”，按回车键算出养老金。2、用鼠标左键按住P2单元格右下角的填充柄向下拖拉，计算其他人员的养老金。3、光

16、标放在Q2单元格中，输入公式："=J2*2%”，按回车键算出医疗金。4、用鼠标左键按住Q2单元格右下角的填充柄向下拖拉，计算其他人员的医疗金。5、光标放在R2单元格中，输入公式："=J2*10%”，按回车键算出公积金。6、用鼠标左键按住R2单元格右下角的填充柄向下拖拉，计算其他人员的公积金。7、光标放在S2单元格中，输入公式："=J2-O2-P2-Q2-R2”，按回车键算出应发工资。8、用鼠标左键按住S2单元格右下角的填充柄向下拖拉，计算其他人员的应发工资，如图3-2-10所示。Erct-工贪一工isj5T文件解瑞能田）翔图谡插入CD格式9）工具数幅GD窗口00褶

17、不回-IX=JJ?J二J1:3，J9E71创*王:宋建事12VBI11奉室专用-1_RI5N0PQRST1-1病假扣款扣款合计赤毫金医疔金公积有应发工资所得税实企200,00296743702960.00A0318.182A070站口24S1.82430030d002646633：2340.005o0.0095223811909520.,00600.002L6542702160.00704224.00422-4105.65280.,008。0.。匚3528844035260095D0500.00310.477.63862604,00Cj2D0200.001军3417011G0.001100.0

18、01363417013E0.0012400400.0013634170960,00VM<料、讪田且/箱售表/Sh=*/|<J->11妙字图3-2-10计算应发工资任务5计算“所得税”和“实发工资”分析或说明：我国相关税法规定，工资、薪金所得应缴纳个人所得税。即每月取得工资收入后，先减去个人承担的基本养老保险金、医疗保险金和失业保险金，以与按省级政府规定标准缴纳的住房公积金，再减去2000元/月（个人所得税起征额为2000元），为应纳税所得额，按5%至45%的九级超额累进税率计算应缴纳个人所得税。计算公式为：“个人所得税=应纳税所得额X税率-速算扣除数”。表3-2-4所得税税率

19、表级数全月应纳税所得额税率%速算扣除数（元）1不超过500元的502超过500元至2000元的部分10253超过2000兀至5000兀的部分151254超过5000兀至20000兀的部分203755超过20000元至40000元的部分2513756超过40000元至60000元的部分3033757超过60000元至80000元的部分3563758超过80000元至100000元的部分40103759超过100000元的部分4515375步骤：1、光标放在T2单元格，单击“插入”一“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF函数，单击“确定”按钮。2、在弹出的“函数参数”对话框中，输入各

20、项参数，所图3-2-11所示。在编辑栏内得到IF函数的表达式为：“=IF(S2-2000<500,0,IF(S2-2000<2000,(S2-2000)*10%-25,IF(S2-2000<5000,(S2-2000)*15%-125,IF(S2-2000<20000,(S2-2000)*20%-375,"复核")”。3、考虑到该企业员工的应发工资在扣除2000元之后，没有超过20000元的，所以IF函数只判断到S2-2000<20000,当S2-2000>20000时，作复核处理。成效泰救LogicaL_lestTaJjie If ti

21、reValue_lf_falseIF0国-0邛史加。0第口期电以及glPCS区-7=71判断一个暴件是苫满足知果洞足返回一个值，如果不期是明返回另一个值Lagicaltrst怪同一小可判断为TELE或FALSE的魏值或法卷式计算站里-71有美函用为书灯(口|曲定匚取消图3-2-11if函数参数对话框之六4、用鼠标左键按住T2单元格右下角的填充柄向下拖拉，计算其他人员的所得税。5、光标放在U2单元格中，输入公式：“=S2-T2”，按回车键计算实发工资。6、用鼠标左键按住U2单元格右下角的填充柄向下拖拉，计算其他人员的实发工资,如图3-2-12所示。r2Mi.crosoftExcel-工麦范文.罢

22、1行日回冈r为文件CE）编辑（X）视图任）插入CI）格式g）TX（D看蜡居山）窗口但）帮助（R）_5>JXD3。母西电第6|C丁|曝EA:4*TU13T=服jj0FQRSTU人1病停天数病假扣款扣款合计养老金医疗金公积金应发工费*得税实虹资200.007t3702960.007128B9.0030318.IS230703502481.2024E1.8245WM300.002546S3302340.0002340.005C001的2223119A笺级加112gR鹑LM600.oo_216於2160.00o£160.0Q704224.00422.4105.65280400100.0

23、03Uu.U0空嵬也44U3SZU.UU127招UU9500500.00310.4T7.636S到4.Q。35.42563.50个10gzoo200.0013617011帅0。0neo.M1100.0013633G0.00：1:12IS4t>0400.0013&341709SO.OO0960.00131iH14卜>HK20103E£S./Sheetl/Chart1/HI潜:二tdl就络|图3-2-12所得税和实发工资项目总结本项目是制作一张员工工资表，包括工资表数据输入与统计分析。在数据输入时可以利用有效性控制和IF函数，快速正确地输入指

24、定数据。工资表数据输入完成后，可以运用数据透视表与数据透视图进行汇总与统计分析。拓展延伸1、IF函数的使用函数格式：if（logical_test,valuejf_true,valuejf_false）。其中："logical_test”表示设定的条件；"value_if_true”表示当目标单元格与设定条件相符时返回的函数值；“value_if_false”表示当目标单元格与设定条件不符时返回的函数值。IF函数可以嵌套使用，最多可以嵌套7层。以表3-2-5为例，不同分数段要求输入不同的等级，则IF函数为：“IF（成绩V60,“不与格”，IF（成绩V70,“与格”，IF（成绩80,“中”，IF（成绩V90,“良”，“优”）”。表3-2-5学生成绩表成绩等级成绩60不与格600成绩70与格700成绩80