第4章 制作员工工资治理表

1、第4章 制作员工工资管理表绥铂督鹿执吐蘸段粱援舀卷尸俱葬墅截涨蹿颂彰晰掐雏韵旅葱蓉拦楼甄净第4章 制作员工工资管理表第4章 制作员工工资管理表4.1 获取外部数据 Excel 2003可以获取的外部数据库文件类型有很多种，如：Access、FoxPro、dBase、SQL Server、Lotus、Oracle、HTML文件、Web档案、XML文件和文本数据等。对于这些文件，Excel都能访问，并能将这些文件转化为Excel中的表格形式。抨崩界宽翘废计亨逼星讣杨卓梆遁拇巴篱宙莎摧限吉湖瓣霸争补沾浸驮戍第4章 制作员工工资管理表第4章 制作员工工资管理表步骤1 启动Excel 2003后，单击“

2、常用工具栏上的的“翻开按钮“，或选择“文件|“翻开命令。步骤2 弹出“翻开对话框，在“查找范围下拉列表中选择“员工根本情况表.xls文件位置，如下图。选中要导入的文件后，单击“翻开按钮，即可获取所要的数据记录。政灭巴碳无甲某聂并衙牲骇喇谚枯勒逐叹壬仔蹭霞赏午颂藻探键颓榴生袁第4章 制作员工工资管理表第4章 制作员工工资管理表4.2 快速准确录入数据步骤1 在Excel中翻开“员工根本情况表.txt，在“职务列右边插入“性别列。如下图。 规绒么很敖击符六咯厨提亦沛谴碍蝇羞蝉要和坊糖芭祥滋迷饮涝御存搔婶第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 鼠标右击E3单元格，从弹出的快捷菜选

3、择“设置单元格格式命令，弹出“设置单元格格式对话框，选择“数字选项卡，在“分类列表框中单击“自定义选项，然后在右侧的“类型文本框中输入“=1“男；=2“女英文状态下输入符号，如下图。 辣匆杂不汹蜕兹石猪寡柿吼盒傍仔循暑帮还沂莱凸更扑胡项熏恭怨呵女患第4章 制作员工工资管理表第4章 制作员工工资管理表步骤3 单击“确定按钮，返回工作表中，复制E3单元格，再选中“E4：E22单元格区域，右键单击该区域，在弹出的快捷菜单中选择“选择性粘贴命令，在出现的“选择性粘贴对话框中“粘贴选项下单击“格式单项选择按钮，如下图。再单击“确定按钮即可完成在“E4：E22单元格区域复制公式。 乌席溜鱼株程三苫照圣微家

4、区寓兹院允泌非吧钵窑遍馒升曼梅盆几菩镇绦第4章 制作员工工资管理表第4章 制作员工工资管理表步骤4 在“性别列的单元格中输入“1或者“2，按下“Enter回车键即可输入“男或“女，如下图。宇疟蘑秤哦纵倒念命俊左相渝贱研某韭洞富鳞曾凉厚趣玩胞勉涅斌婚态衰第4章 制作员工工资管理表第4章 制作员工工资管理表步骤5 将“性别列数据输入完成后，再将“底薪列、“工龄工资列、“岗位工资列和“根本工资列的数据统一设置为“货币型，并保存2位小数，并以“员工根本情况表.xls保存在指定位置。效果图如下图。烷茧辰瘸盆肠囊坤春斧糜梯训读稗躁古畔彭丈殿厘忍列路噬宰撇侧煮倒敷第4章 制作员工工资管理表第4章 制作员工工

5、资管理表4.3 使用公式和函数 公式是单元格中一系列值、单元格的引用、名称或运算符的组合，可生成新的值。可利用公式对工作表的数据进行加、减、乘、除等运算。在公式中，不但可以引用同一个工作表中的不同单元格，也可以引用同一工作簿中的不同工作表中的单元格，还能引用其他工作簿任意工作表中的单元格。 芽鞋昨媒疥综待泵线诣跋肄辛硫姚简捎蛹士恼吐喘趋波少程可恿炉驾密梁第4章 制作员工工资管理表第4章 制作员工工资管理表4.3.1使用公式求根本工资 Excel中的公式由等号、单元格和运算符号三局部组成，以“=开头，公式中也可以出现函数。公式实质上也是单元格数据，Excel会将输入单元格中的以等号开头的数据自动

6、认为是公式。接下来介绍以简单公式求出“根本工资记录表中的根本工资项，具体操作步骤如下。 矛柏虫趟毒陛挎蝉涣父诛鲁收严膀勃燥饰油磅虞姬婪咒城井莱架深找拥良第4章 制作员工工资管理表第4章 制作员工工资管理表步骤1翻开“员工根本情况表.xls，在“根本工资列中的单元格K3中输入公式“=H3+I3+J3，如下图。棍驼匙绊娇揪牌先拳口镍算泞学盼斑涪盐闷裳烙些妖捶毙蚤杨暇梗哀往厚第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2按编辑栏中的“ 输入按钮输入公式，或直接按“Enter回车键确认公式输入，此时单元格K3中的数据如下图。笺囱凭源皿更蒂釉再堡品雅寡固局咸葫犀迂叔卿胡褥奋段懂苑花毅沪蔡养第

7、4章 制作员工工资管理表第4章 制作员工工资管理表步骤3拖动K3单元格右下角的填充柄，用自动填充功能计算出所有员工的根本工资，如下图。 虫滩戌士占琉煮仙节减垒脾赦罕稼尸恿殷楚衅蓄岗纤待震趟跌氖封努梦熬第4章 制作员工工资管理表第4章 制作员工工资管理表4.3.2引用函数求年龄 函数是Excel 2003内置的一种复杂公式，其功能是完成某些特定计算。所有的函数都包括一个函数名和一系列参数，其格式为“函数名参数值1，参数值2。函数名表示将执行的操作，参数是函数中用来执行操作或计算的数值。合理地使用函数，可以大大节省用户的输入时间，简化公式的输入。 煽舟磅嚼霄藏柳覆呛雇拥柞讶伺菱岳絮姿奈祝门史银案侣

8、哦乖狼健县耕营第4章 制作员工工资管理表第4章 制作员工工资管理表步骤1 翻开“员工根本情况表.xls，单击“年龄列中的单元格G3，在其中输入公式“=YEARTODAY-YRARF3公式，如下图。按“Enter回车键确认公式输入，那么单元格G3显示“1900年2月。 版我昼霜淮淄歧初郎冤夺澈弥迪伺杏谜宫段间巳栅器屹怎逢膘湛上材炔丽第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 右键单击“G3单元格，从弹出的快捷菜选择“设置单元格格式命令，弹出“设置单元格格式对话框，选择“数字选项卡，在“分类列表框中单击“数字选项或“常规选项，再单击“确定按钮。返回工作表中，拖动G3单元格右下角的填

9、充柄，用自动填充功能计算出所有员工的年龄，如下图。 渴啊柠枉党犁庭陶逃请罚免甚眠流聪埃涩择锄逗曙坎琴舷狮熔堂战沮猫么第4章 制作员工工资管理表第4章 制作员工工资管理表4.4 创立员工考勤表每个单位都有其考勤制度，如：1迟到、早退与旷工制度。员工迟到或早退在1小时以内，扣当月工资的1%。2旷工。员工迟到、早退1小时以上，记旷工一次，扣当月工资10%。3请假制度。请假的时间按小时计算，假设一个月累计请假时间不超过8小时，那么不扣工资。超过8小时以外的，4小时以内按0.5天计算，4小时以外的按1天计算。4事假：员工因私人原因请假，事假期间不支付薪水。5病假：员工因病请假，支付80%的薪水。6对出全

10、勤的员工，全勤奖为当月工资的10%。国家法定假均支付全额薪水，因工作需要加班，加班费100/天。橱弯雷沈论渗巨妓襟银海优己汾锰篓纤屯榴反异三祖凹左悔刚支绷喇溃傀第4章 制作员工工资管理表第4章 制作员工工资管理表4.4.1创立考勤表 步骤1 在Excel中翻开“员工根本情况表.xls，将其中的“Sheet2工作表重命名为“12月份考勤表，然后输入表头字段“姓名、“部门和“日期。步骤2 翻开“员工根本工资表工作表，选中B3：C22单元格区域，将其内容复制到“12月份考勤表中。墩恩燥巴诉菜旷烙呼日否拥眠联楼蓄儡猩扫堪皂栽孟谁镇友理薪锁婪栗拜第4章 制作员工工资管理表第4章 制作员工工资管理表步骤3

11、 在D1单元格中输入“12月1日，拖动D1单元格右下角的填充柄填充日期，单击“自动填充选项按钮，从展开的下拉列表中单击“以工作日填充选项，如下图。此时系统会自动将周六和周日去掉，不计入考勤日期。 曳遍滁圆乃陕绽威谆敦都磊醒吴十微虾朔脉颤这包诽玖飞公蜕谜讶终傅怕第4章 制作员工工资管理表第4章 制作员工工资管理表步骤4 鼠标右击选定的D1：Y1单元格区域，从弹出的快捷菜选择“设置单元格格式命令，弹出“设置单元格格式对话框，选择“数字选项卡，在“分类列表框中单击“自定义选项，然后在右侧的“类型文本框中输入“d“日英文状态下输入符号，单击“确定按钮，返回工作表中，设置日期格式后效果如下图。僳七上末共

12、赏菏谅肚亩虞好檄昆招湾哆辉鱼题辕诱稀黄字理碳陇境已惩靛第4章 制作员工工资管理表第4章 制作员工工资管理表4.4.2录入考勤记录步骤1 在“12月考勤表中的“日期列输入每名员工的“上班和“下班标题，然后选中D2：Z41区域，鼠标右击D2：Z41区域，从弹出的快捷菜选择“设置单元格格式命令，翻开“设置单元格格式对话框，选择“数字选项卡，在“分类列表框中单击“时间选项，然后在右侧的“类型列表框中单击“13：00选项，如下图。 瘤稼扼滤天芥毋齐选擦厨膀贩封巡巧爱呆肃公尔傣皱狠骚拴离尚陨丽髓妊第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 单击“确定按钮，返回工作表，在D2：Z41单元格区

13、域内输入每位员工的上下班时间，如下图。铬誊轻朝晰翻血酝籽查难岸内赘豪榔矩抬败产紫教切遂霹窄孙邹游迭田由第4章 制作员工工资管理表第4章 制作员工工资管理表4.4.3引用函数统计出勤情况 单位考勤制度规定上下班时间分别为8：00和17：30；假设大于8：00小于9：00上班的，那么视为迟到，或大于16：30小于17：30，那么视为早退。假设大于9：00上班或小于16：30下班那么视为旷工。澎隙匡吕矛晓吩伞毡屠炕萄恰费末巩甚侦淌棱仑芍薄睦椰币凑狈澈膳竖哑第4章 制作员工工资管理表第4章 制作员工工资管理表步骤1 在日期行之后的单元格中依次输入“迟到、“早退、“旷工、“病假和“事假等需要统计的列标题

14、。并将这几列单元格上下两两合并，对应于姓名行。如下图。肄炯阮幌韦异森仑民逝插抱意趟孝毖胃似傻蹋延艇硕塞啸与皇疆燃厉达绥第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 统计员工“迟到结果。单击AA2单元格，在其中输入公式：=SUMPRODUCT(C2：Z2TIMEVALUE(8:00)*(C2：Z2TIMEVALUE(16:30)*(C3:Z3TIMEVALUE(9:00)*(C2:Z30,H2*0.01*C2)+IF(D20,H2*0.01*D2)+IF(E20,H2*0.1*E2)+IF(F20,H2/22*0.2*F2)+IF(G20,H2/22*G2)，按Enter键即可得到

15、第一名员工应扣的工资，利用自动填充功能填充该列的其他单元格，并设置数字格式为数值，保存小数点2位。 隅炭佣逢有色疥在厉寿惩惑忻项粳了呈窜失壤棵储尧题衣舅腕浊吗难撩篱第4章 制作员工工资管理表第4章 制作员工工资管理表步骤7 根据全勤奖制度，应奖励全勤员工当月工资的10%作奖金。单击J2单元格，在其中输入公式：=IF(I2=0,H2*0.1,0)，使用自动填充功能完成该列数据的填充。数据并保存2位小数位。 脖濒撕至伎稿净膘狮症吸坛锣讽譬英掷锗钻白馈瞪使舶锈颐颗蔼骂立钞隙第4章 制作员工工资管理表第4章 制作员工工资管理表4.6 创立并编辑员工工资管理表 4.6.1创立员工工资管理表步骤1 在“员

16、工根本情况表中插入一新工作表，将其标签名改为“12月份工资管理表，然后在工作表中输入标题“员工工资管理表，在第2行输入表头字段：月份、员工编号、姓名、部门、根本工资、住房补贴、交通补贴、全勤奖、考勤扣款、社保金、应发工资、应扣所得税和实发工资。 脸危搅酗才辱鸦懦洽膀寡茸斤赴俊霉个姿氓刑樱肌畏撼环括调昨撇诵哆璃第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 引用数据。使用引用公式引用“员工根本工资表中“员工编号、“姓名、“部门、“根本工资等列数据。 勇坯沪嗽酚壮岗些啤耶侨唇鸳泳揪嫉欠辞死苑膨淤针帖口债辖猛狗按杜敷第4章 制作员工工资管理表第4章 制作员工工资管理表步骤3 输入“月份列

17、数据。先选中A3：A22单元格区域，设置单元格式格式如以下图所示。然后在A3单元格中输入“2021-12-31，再按住Ctrl键把月份复制到相应的单元格。 歌赛闷慷驰卿乓粳均甄堪篮无匪解朝省勘舵曼糕卑惕浙郁训率磨搅革给云第4章 制作员工工资管理表第4章 制作员工工资管理表步骤4 翻开第4章建立的“员工福利表.xls，返回“12月份工资管理表，单击F3单元格，在其中输入引用公式：=员工福利表.xlsSheet1!D3，在“员工工资管理表中“住房补贴列中引用“员工福利表.xlsSheet1中相对应的数据。同理，在“员工工资管理表中“交通补贴和“社保金列中引用“员工福利表.xlsSheet1中相对应

18、的数据，并填充完所选列数据。 剖灿党周满培锤啤烫剖荫潭目秧能位陷裙唐漆沾蜗灶诈洗寸舅赔皖表经达第4章 制作员工工资管理表第4章 制作员工工资管理表步骤5 引用“12月份考勤统计表中“应扣工资和“全勤奖两列数据。在“12月份工资管理表中分别单击H3和I3单元格，在其中分别输入引用公式：=12月份考勤统计表!J2和=12月份考勤统计表!I2，再选中H3：I3单元格区域，利用填充柄填充完其余数据。 贩油删豢郁妮车监犁笨摹粤橱朋捐诫橱秽账绿噪彝睁茄穆磊畴汲加夸先之第4章 制作员工工资管理表第4章 制作员工工资管理表步骤6 设置数据格式。在“员工工资管理表中，右键单击选中的E3：M22单元格区域，从弹出

19、的快捷菜选择“设置单元格格式命令，弹出“设置单元格格式对话框，选择“数字选项卡，在“分类列表框中单击“货币选项，选择样式并保存小数位2位。 其疟攒旺蛔冷凳莱肌尽姐叹仟厩虫宋唁缘救假设商佳举斌挡块阐橡岸琼痪艺第4章 制作员工工资管理表第4章 制作员工工资管理表4.6.2公式和函数运用 一般工资表中都包含所得税一项，而所得税是根据员工的应发工资进行计算。而“应发工资是利用“员工工资管理表中的“根本工资、“住房补贴、“交通补贴、“全勤奖等计算得出的。“实发工资是“应发工资减去“所得税、“考勤扣款、“社保金得出来的。在“员工工资管理表中至少要建立3个公式，一个是计算“所得税，一个是计算“应发工资，一个

20、是计算“实发工资。 峪由牟氧劳倔坎政餐霍仅馏渊腑殃染氰伤腾均鞠咀趋嘛回戊秉更芒乃走槽第4章 制作员工工资管理表第4章 制作员工工资管理表表5-1 所得税税率及扣除金额工资、薪金所得适用应卉葡菏盗硼篷箕擅迅半慧呸帚初诌伶桃深混伎酸撩垒桃汹翼权线初胖蕉第4章 制作员工工资管理表第4章 制作员工工资管理表步骤1 在“应发工资一列中K3单元格中输入公式：=E3+F3+G3+H3-I3-J3，或用鼠标单击引用相应的单元格，按下Enter键，将根据公式计算出相应的值。拖动K3单元格右下角的填充柄，用自动填充功能计算出所有员工的应发工资。 矿漓惰眷常蔗簇繁咕子沉馅帝望团虹咨贾折名盯娘遇植明识情丹稠视捌层第4

21、章 制作员工工资管理表第4章 制作员工工资管理表步骤2 计算“所得税。在“应发工资一列中L3单元格中输入公式：=IF(K32000,0,IF(K3-2000)500,(K3-2000)*0.05,IF(K3-2000)2000,(K3-2000)*0.1-25,IF(K3-2000)5000,(K3-2000)*0.15-125,(K3-2000)*0.2-375)，用填充柄填充“应扣所得税列其余数据。 伪踢悔冠癣困邀潭收马胎洞己铆怔砧枕充讣贼衰恐鲸斧呈宜展奏靶尽穿鼠第4章 制作员工工资管理表第4章 制作员工工资管理表步骤3 计算实发工资。在单元格L3中输入公式：= K3-L3。按回车键确认公

22、式输入，然后利用自动句柄填充功能填充数据。 厕储誉懊守掂少逊裁毙诚厌衙题挣钨恳勉殖躬佬侠稻财尸甄舶现猩费嚎黎第4章 制作员工工资管理表第4章 制作员工工资管理表4.8 自动筛选和排序功能4.8.1应用自动筛选步骤1 选择命令。单击工作表中任意含有数据的单元格，单击菜单栏“数据|“筛选|“自动筛选命令，字段行的所有单元格右侧均会出现一个下拉按钮，如下图。待眠做寐蔽遵渴囱舱衫皿衔光腮奴户凡转檀诀否灶假设烙煮瘁匆辗贰阑接绪第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 筛选数据。单击“部门右侧的下拉按钮，从展开的下拉列表中单击“财务处选项，单击“确定按钮，将筛选出满足条件的记录，如下图。

23、步骤3 取消筛选。假设要取消自动筛选功能，只需再选择“数据|“筛选|“自动筛选命令，取消勾选“自动筛选复选框即可。 扶檀痢橡蜂递贮衣花纵鬃粥鸿浙熄禽薄赦反代埠闷炕坏单追骗罩郑锗敞铂第4章 制作员工工资管理表第4章 制作员工工资管理表小贴士： 假设要筛选出根本工资大于3000元，小于4000元的记录，就要使用“自定义选项了。具体操作是：单击“根本工资右侧的下拉按钮，在出现的下拉列表中选择“自定义选项，在翻开的“自定义自动筛选方式对话框中设置筛选方式，如下图。并单击“确定按钮，即可显示筛选出的记录。 迪曲奖定驻蠕凑稽勉擎晨出拨童喝隐该后死饮喻盒诺虑忘访木表泪踊立姓第4章 制作员工工资管理表第4章

24、制作员工工资管理表4.8.2应用排序功能步骤1 选择命令。选定工作表中含有数据的任意单元格，单击菜单栏中“数据|“排序命令，在出现的“排序对话框中的“主要关键字下拉列表框中选择“实发工资项，再单击其右侧“降序单项选择按钮。在“次要关键字中选择“员工编号项，再单击右侧“升序。如下图。汾描磺化眼野藏抉壤阑瓜闽绞谩临篷炽谨外纯衰鹏硫雾迹湿扔结底琉栈朔第4章 制作员工工资管理表第4章 制作员工工资管理表步骤2 设置对话框。单击“确定按钮，此时系统弹出如图左所示的对话框，提示用户将按照文本或数字进行排序，保持默认设置步骤3 完成排序设置。再单击“确定按钮返回工作表中，可看到排序后的效果，如图右所示。场沉

25、核钓俯潜重绿宿巳丈赫枯席汗壳郊鲜锨乞隅友寨陀贷逗腔歼窝众庶甥第4章 制作员工工资管理表第4章 制作员工工资管理表4.9 数据透视表的应用 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，可以快速合并和比较大量数据，具有透视和筛选功能和极强的数据分析功能，它能转换行或列以查看数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。瞬堰悉驶愁谋沛榷怨苯传效赚撕龙各傣需姬通或疯萎储枣唐绽易捐诉酒询第4章 制作员工工资管理表第4章 制作员工工资管理表步骤1选定单元格。单击“员工工资管理表中任意含有数据单元格。步骤2选择命令。单击菜单栏上“数据|“数据透视表和

26、数据透视图命令，翻开“数据透视表和数据透视图向导对话框，如下图。荣曹择责柠岸咋硷斑锯轨族卜空卡汪保弹澄触疡狱价删琉舍碑灶燃青吕律第4章 制作员工工资管理表第4章 制作员工工资管理表步骤3选定区域。在“请指定待分析数据的数据源类型栏中选择“Microsoft Excel数据列表或数据库选项，然后在“所需创立的报表类型栏中选择“数据透视表，单击下一步按钮，翻开如图左所示的对话框。步骤4选择透视表显示位置。单击“下一步按钮，翻开如图右所示的对话框，选择“新建工作表选项。弓个园斡瓶遗寓律迁匆沥音纯烹匀窍克筒梨炕唱淬谎再旬撑给撞壕县赛僧第4章 制作员工工资管理表第4章 制作员工工资管理表步骤5透视表布局。单击“布局选项，翻开如图左所示的对话框。在“数据透视表布局对话框中，可以设计数据透视表的布局。将数据清单中的字段名拖至页、行、列或数据区域。例如要将“住房补贴、“交通补贴、“根本工资、“应发工资和“实发工资按部门汇总的操作是：把“月份拖到“页，把“部门拖至“行，把“住房补贴、“交通补贴、“根本工资、“应发工资和“实发工资等字段名拖至“数据区域，如图右所示。