疑难279 如何设计工资表首页.docx
- 文档编号:25410832
- 上传时间:2023-06-08
- 格式:DOCX
- 页数:10
- 大小:1.45MB
疑难279 如何设计工资表首页.docx
《疑难279 如何设计工资表首页.docx》由会员分享,可在线阅读,更多相关《疑难279 如何设计工资表首页.docx(10页珍藏版)》请在冰豆网上搜索。
疑难279如何设计工资表首页
疑难279如何设计工资表首页
工资表首页是现实工资表系统中各个表格标题的页面,通过点击表格标题实现超链接跳转到相应的页面进行操作。
那么,如何使用Excel设计工资表的首页呢?
解决方案
使用函数与公式、超链接、单元格格式、图片、Flash、控件等功能设计工资表的首页
操作方法
设计工作表首页
步骤1单击“页面布局”选项卡“背景”,选择合适的图片作为工资表的首页的背景。
步骤2在C2:
G2单元格设置工资表的标题,其中在C2单元格输入公司名称,在D2单元格输入年份,在F2单元格输入月份。
步骤3在C6单元格输入如下公式,显示当前日期。
=TEXT(NOW(),’’今天是yyyy年mm月dd日aaaa’’)
步骤4在C8:
C9单元格区域插入ShockwaveFlashObject控件,并设置Flash文件clock47.swf作为时钟。
步骤5在G8、G10、G12单元格建立超链接,分别链接到“工资计算表”、“工资条”、“工资查询表”工作表。
步骤6设置C2:
G2、G8、G10、G12单元格边框及背景颜色。
步骤7在“工资计算表”、“工资条”、“工资查询表”工作表的A1单元格分别插入一个图片按钮,超链接到“首页”工作表的A1单元格。
步骤8隐藏工资表标签,取消锁定C2、D2、F2单元格,保护工作表。
至此,工资表首页的设计就已完成,如图10-41所示。
原理分析
1.使用TEXT+NOW函数组合显示当前系统日期及星期。
2.使用超链接功能实现首页于各个工作表之间的相互链接。
3使用单元格格式、图片美化首页页面。
4.使用控件在Excel中插入Flash,动态显示系统时间。
5.保护工作表以避免首页布局及公式被意外修改。
疑难280如何设计工资计算表
在企业、事业单位中,财务部门每月都将花费大量的精力来计算当月员工的工资。
如果全部采用手工操作,不仅工作效率低,而且容易出错。
那么,如何使用Excel的函数与公式等功能来设计工资计算表,以提高工作效率呢?
解决方案
使用条件格式、自定义单元格格式、数据有效性、函数与公式等功能设计工资计算表。
操作方法
设计工资计算表
步骤1在“工资计算表”的D1单元格输入下列公式,作为表格标题。
步骤2在“工资计算表”中设置工资表的列字段,比如:
工号、姓名、部门、入职日期、身份证号码、银行工作卡号、基本工资、岗位津贴、交通补贴等。
然后将列字段套用表格式,并设置“表包含标题”,如图10-42所示。
步骤3全选“工资计算表”工作表,在条件格式中使用以下公式设置单元格外边框。
=(A1<>’’’’)*(ROW()>2)
步骤4设置“工号”字段格式(假如工号格式为A0000):
选择“工资计算表”工作表的A列,设置单元格格式为自定义格式“A0000”。
步骤5选择“工资计算表”工作表的E列,设置单元格格式为“文本”。
在数据有效性中使用如下公式限制E列输入文本的长度:
=OR(LEN(E1)=15,LEN(E1)=18)
步骤6选择“工资计算表”工作表的F列,设置单元格格式为“文本”。
步骤7在“工资计算表”工作表的M4单元格输入下列公式,计算“司龄补贴”(假设满1年司龄补贴为50):
=50*DATEDIF([@入职日期],DATE(首页!
$D$2,首页!
$F$2,1),”y”)
步骤8在“工资计算表”工作表的P4单元格输入下列公式,计算“加班工资”(每月按21.75个工作日计算):
=ROUND([@加班天数]*([@基本工资]+[@岗位津贴])/21.75,2)
步骤9在“工资计算表”工作表的R4单元格输入下列公式,计算“缺勤应扣”工资:
=ROUND([@缺勤天数]*([@基本工资]+[@岗位津贴])/21.75,2)
步骤10在“工资计算表”工作表的S4单元格输入下列公式,计算“应发工资”:
=SUM(表1[@[基本工资]:
[奖金]],[@加班工资])-[@缺勤应扣]
步骤11在“工资计算表”工作表的V4单元格输入下列公式,计算“代扣社保”。
=ROUND([@应发工资]*[@社保],2)
步骤12在“工资计算表”工作表的W4单元格输入下列公式,计算“代扣公积金”:
=ROUND([@应发工资]*[@住房公积金],2)
步骤13在“工资计算表”工作表的AB4单元格输入下列公式,计算“税前工资”:
(@应发工资)-SUM(表1[@[代扣社保]:
[代扣其他]])
步骤14在“工资计算表”工作表的AC4单元格输入下列公式,计算“代扣个人所得税”(按2011年9月新税法计算,其中AB1单元格为个税起征点金额):
ROUND(MAX(([@税前工资]-$AB$1)*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701},0),2)
步骤15在“工资计算表”工作表的AD4单元格输入下列公式,计算“实发工资”:
=[@税前工资]-[@代扣个人所得税]
至此,工资计算表设计就已完成,如图10-43所示。
原理分析
1.套用表格格式,实现录入数据时自动扩展数据区域的格式与公式,以及在公式中使用结构化引用动态提取表中数据。
2.使用条件单元格格式自动设置外边框。
3.使用单元格自定义自动设置“工号”格式。
4.E、F列设置为文本格式,以防止输入超过15位数字之后的数字转换为0值。
5.E列设置数据有效性,防止输入非15或18位的错误身份证号码。
6.使用DATEDIF函数计算员工的司龄,再乘以满一年的司龄补贴金额,计算出员工的实际司龄补贴。
7.代扣个人所得税计算:
其中{3;10;20;25;30;35;45}%表示个人所得税1至7级的税率,即
{0.03;0.1;0.2;0.25;0.3;0.35;0.45}
其中5*{0;21;111;201;551;1101;2701}表示个人所得税1至7级速算扣除数,即:
{0;105;555;1005;2775;5505;13505}
然后使用个税计算公式:
个税=(应发工资-个税起征点金额)*税率-速算扣除数
计算出对应所有级别的个税,返回数组:
{69.5412;126.804;-91.392;-425.49;-2059.588;-4693.686;-12461.882}
然后使用MAX函数求取最大值,并设置下限值为0,利用ROUND函数两位小数,既得出对员工的个人所得税。
疑难281如何设置工资条。
工资条是员工所在单位定期给员工一张反映工资的纸条,其中包含工资各构成部分的项目名称和具体数值。
每月发工资前,公司财务部门都需要将员工工资制成工资条发给各个员工。
若按照传统的复制、粘贴、排列方法来操作,则需要进行很长版面的重复操作,如果其中某个数据要改动,牵涉的版面范围就会很广,工作量很大。
那么,如何使用Excel来设计工资条,以提高工作效率呢?
解决方案
使用条件格式、自定义单元格格式、数据有效性、函数与公式等功能设计工资条。
操作方式
※设计工资条※
步骤1在“工资条”工作表的C1单元格输入下列公式,作为表格标题。
步骤2全选“工资条”工作表。
在条件格式中使用以下公式设置单元格外边框。
=(A1<>””)*(ROW()>2)
步骤3全选“工资条”工作表,在条件格式中使用以下公式设置单元格背景色及字体颜色,如图10-44所示:
=(A1<>””)*(ROW()>2)*(MOD(ROW(),3)=0)
步骤4选择“工资条”工作表的A列,设置单元格格式为自定义格式“A0000”。
步骤5定义名称“工资条项目”,引用位置为“=表1[口口口标题]”,然后在“工资条”工作表的第3行单元格设置数据有效性序列为“=工资条项目”。
步骤6在“工资条”工作表的第3行单元格的数据有效性下拉列表中选择工资条需要显示的项目名称,如工号、姓名、部门、基本工资、岗位津贴等。
步骤7在“工资条”工作表的A4单元格输入以下公式,并向下复制填充。
=IF(OR(MOD(ROW(A2),3)=0,ROUNTA(表1[工工号]),A$3=””),””,IF(MOD(ROW(A2),3)=1,A$3,SMALL(表1[工号],INT((ROW(A2)+2)/3))))
步骤8在“工资条”工作表的B4单元格输入下列公式,并向右、向下复制填充。
=IF(OR($A4=””,B$3=””),””,VLOOKUP($A4,表1[#全部],MATCH(B$3,表1[#标题],0),0))
步骤9保护工作表
至此,工资条的设计就已完成,如图10-45所示.
原理分析
1.使用条件格式设计工资条的边框,以及工资条项目名称的背景和字体颜色。
2.使用数据有效性设置工资条项目名称。
3.A列公式设置每位员工的工资条占用3行单元格,即第1行为标题行,第2行为各项工资数据,第3行为空白行。
也就是说,ROW函数引起的行号被3除余1的行为标题行,被3除余2的行为各项工资数据的行,能被3整除的行为空白行。
其中SMALL函数从小到大提取”工资计算表”中的工号,并每隔3行进行排列。
4.B:
V列公式使用VLOOKUP+MATCH函数组合按A列工号及第3行标题查找对应的各项工资数据。
疑难282如何设计工资查询表
有时候需要单独查询某个员工的工资,如果在工资计算表中查看,由于数据较多,不利于查看,而且可能会泄漏工资秘密。
那么,该如何设计工资查询表,以便于查询单个员工的工资明细数据呢?
→解决方案
使用函数与公式,数据有效性,单元格自定义格式等功能设计工资查询表。
操作方法
设计工资查询表
步骤1在”工资查询表”工作表的C1单元格输入下列格式,作为表格的标题。
步骤2定义名称”名字”,引用位置”=表1{姓名},然后在”工资查询表”工作表的F3单元格设置数据有效性序列为”=姓名”。
步骤3在”工资查询表”工作表的D5单元格输入下列公式。
=IF(F3=””,””,F3)
步骤4选择”工资查询表”工作表的D6:
D7单元格区域,输入下列公式,按{Ctrl+Enter}组合键结束。
=IF($F$3=””,””,INDEX(表1,MATCH($F$3,表1{姓名},0)MATCH(C6,表1{#标题},0)))
步骤5设置”工资查询表”工作表的D6单元格格式为自定义格式”A0000”。
步骤6在”工资查询表”工作表的F5单元格输入下列公式
步骤7选择”工资查询表”工作表的F6:
F7单元格区域,输入下列公式,按{Ctrl+Enter}组合键结束。
=IF($F$3=””,””,VLOOKUP($D$6,表1,MATCH(E6,表1{#标题},0),0))
步骤8选择”工资查询表”工作表的D11:
D20单元格区域,输入下列公式,按{Ctrl+Enter}组合键结束。
=IF($F$3=””,””,VLOOKUP($D$6,表1,MATCH(C11,表1{#标题},0),0))
步骤9选择”工资查询表”工作表的F11:
F17单元格区域,输入下列公式,按{Ctrl+Enter}组合键结束。
=IF($F$3=””,””,VLOOKUP($D$6,表1,MATCH(E11,表1{#标题},0),0))
步骤10在”工资查询表”工作表的D21单元格中输入下列公式
=SUM(D11:
D19)-D20
步骤11在”工资查询表”工作表的D21单元格中输入下列公式
=SUM(F11:
F17)
步骤12在”工资查询表”工作表的D21单元格中输入下列公式
=D21-F21
步骤13取消锁定”工资查询表”工作表的F3单元格,保护工作表。
至此,工资查询表的设计就已完成,如图10-45所示
1.设置数据有效性序列,按姓名字段进行工资查询。
2.设置INDEX、MATCH、VLOOKUP等查询函数,在“工资计算表“中查询对应项目的信息及金额数据。
3.保护工作表,防止公式被意外修改。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 疑难279 如何设计工资表首页 疑难 279 如何 设计 工资表 首页