Excel基础与应用 罗新密 30580-008

EXCEL基础及应用,第8章制作与打印工资表,案例说明,知识点,案例制作,8.1,8.2,8.3,8.4,知识拓展,8.1案例说明,工资表的处理，对每个单位都是一件既重要又繁琐的工作，而且近年来工资的频繁调整和每年员工的增减和调资，使得工资管理的工作量越来越大。在手工条件下，编制工资发放明细表和汇总表是会计工作中较复杂的事，单位职工越多，工作量越大，就越容易出错。如用传统的固定工资管理软件，由于其供用户修改的权限有限，修改处理较复杂且要专业的人员处理，很难适应实际要求。利用Excel软件，不写程序，快速制作工资明细表、工资条和工资汇总表，既减轻工作负担，提高工作效率，又能规范工资核算，为查询、汇总、调资等提供条件。,8.1.1案例分析,一般企业工资表,主要包含3张表,分别为:工资明细表、工资汇总表、工资条。工资明细表中的部分数据就是根据该表有关项目自动计算出来的,避免了手工计算。工资明细表是工资表中的主要表格,按工资项目列示每位职工的各项工资数,如图8-1所示。工资汇总表是对工资明细表的汇总。本例中按部门分类汇总，如图8-2所示。工资条比较通用,是需要一种带有独立表头的工资表单，如图8-3所示。为了防止别人在使用中误修改或删除表格内容和各打印用的引用工作表，需要对表格中除了工资总表中员工基本信息录入的单元格以外的部分和各打印用的引用工作表进行保护。,图8-1工资明细表,图8-2工资汇总表,图8-3工资条,8.1.2设计思路,8.2知识点,8.2.1工资表分部门统计、汇总根据工资表的特点，重点是计算、统计、汇总方面。主要使用的函数有If()、Countif()、Sumif()。IF()函数在本例中计算应缴所得税发挥了重要作用。分类统计汇总可以使用函数，如Countif()、Sumif()，也可以使用Excel中的分类汇总法或数据透视表法。,8.2.2打印预览与打印设置,打印预览：Excel2003中采用了所见即所得的技术，打印预览可以看到普通视图中看不到的内容，显示打印页真实的打印效果，从而避免失误、提高打印质量，而且可以依据所显示的情况快速更改许多打印设置。页面设置打印预览中的“设置”与“页边距”进行快速打印设置打印区域设置,8.3案例制作,8.3.1工资表数据录入在建立工资表之前，应先对工资表中包含的字段内容进行分析。以确定字段的排列。基本数据：部门、姓名、基本工资、住房补贴、加班工资、缺勤扣发、全勤奖金是需要人工输入的计算数据：应发工资、失业保险金、养老保险金、医疗保险金、应纳税所得额、应扣所得税、应扣、实发工资等是通过公式和函数计算获得。技巧：自定义、数据有效性,8.3.2工资表数据计算,应发工资=基本工资+住房补贴+加班工资+全勤奖金-缺勤扣发失业保险金=基本工资*1%养老保险金=基本工资*1.4%医疗保险金=基本工资*2%应纳税所得额=应发工资-住房补贴-失业保险金-养老保险金-医疗保险金-1600应扣=失业保险金+养老保险金+医疗保险金+应扣所得税实发工资=应发工资-应扣“应扣所得税”需要根据中华人民共和国个人所得税法对工资、薪金所得税进行计算。见表1。本例中使用if()函数实现。应扣所得税=(IF(应纳税所得额0,0,IF(应纳500,应纳税所得额*0.05,IF(应纳税所得额2000,应纳税所得额*0.1-25,IF(应纳税所得额5000,应纳税所得额*0.15-125,IF(应纳税所得额20000,应纳税所得额*0.2-375,IF(应纳税所得额40000,应纳税所得额*0.25-1375,IF(应纳税所得额60000,应纳税所得额*0.3-3375,应纳税所得额*0.35-6375),操作步骤：,（1）计算“应发工资”，选定I3单元格，输入公式“=D3+E3+F3+H3-G3”，然后通过拖动填充柄复制,把公式复制到其他行。（2）失业保险金：选定J3单元格，输入公式“=D3*1%”。（3）养老保险金：选定K3单元格，输入公式“=D3*1.4%”（4）医疗保险金：选定L3单元格，输入公式“=D3*2%”（5）应纳税所得额：选定M3单元格，输入公式“=I3-E3-J3-K3-L3-1600”（6）应扣所得税：选定N3单元格，输入公式,=IF(M30,0,IF(M3500,M3*0.05,IF(M32000,M3*0.1-25,IF(M35000,M3*0.15-125,IF(M320000,M3*0.2-375,IF(M340000,M3*0.25-1375,IF(M360000,M3*0.3-3375,M3*0.35-6375)（7）应扣：选定O3单元格，输入公式“=J3+K3+L3+N3”（8）实发工资：选定P3单元格，输入公式“=I3-O3”,8.3.3工资表分部门统计、汇总,1、分类汇总法操作步骤：（1）对“工资明细表”按部门进行排序。（2）执行菜单“数据”“分类汇总”,打开分类汇总窗口,“分类字段”中选择分类标准,如“部门”；“汇总方式”中选择汇总方式，如“求和”；“选定汇总项”中选择对那一项汇总，如“实发工资”；然后设置其他内容,点击“确定”。（3）汇总部门人数，再次执行菜单“数据”“分类汇总”,打开分类汇总窗口,“分类字段”中选择“部门”；“汇总方式”中选择“计数”；“选定汇总项”选择中“部门”；然后将“替换当前分类汇总”前方框中的勾取消，最后点击“确定”。（4）汇总表就出来了,在左侧选择汇总级别,可以得到不同级别的汇总表。如图8-6所示。,图8-6分类汇总之工资表汇总,2、数据透视表法,操作步骤：（1）执行菜单【数据】【数据透视表和数据透视图】,打开“数据透视表和数据透视图向导3步骤之1”选择【下一步】。（2）“数据透视表和数据透视图向导3步骤之2”选择制作透视表的数据源，如果系统选择的数据源正确，单击【下一步】，否则单击【选择区域】右侧的图标【】，选择制作透视表的数据源，确认后返回到“数据透视表和数据透视图向导3步骤之2”，选择【下一步】。（3）“数据透视表和数据透视图向导3步骤之3”，此时，可以选择【新建工作表】或【现有工作表】，这里我们选择“新建工作表”，单击【完成】。（4）在新建的工作表出现的布局中，如图8-7所示，设置透视表格式布局内容。,图8-7数据透视表布局,图8-8数据透视表之工资表汇总,8.3.4工资条的制作,工资条的制作方法较多，多数方法比较复杂，不利于操作。下面介绍两种较为简单的方法：函数法和插入表头法。1、函数法操作步骤：（1）新建一个工作，命名为“工资条”，选择“工作条”的A1单元格中输入=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,工资明细表!A$2,INDEX(工资明细表!$A:$P,INT(ROW()+4)/3)+1,COLUMN()（2）选择A1:P1区域，选择P1右下角的填充柄，向下拖至输出所有员工的工资条即可，如图8-9所示。,图8-9函数法制作工资条,2、插入表头法,操作步骤：（1）新建一个工作表，命名“工资条”，将“工资明细表”表头和数据复制到“工资条”中。（2）在工资表的右侧两列中，交叉输入任意字符或数字（主要是为了后面的“空位”空值，所以无限制），然后选中交叉的四个单元格，使用的“填充柄”，复制这种格式至工资表的结束行。如图8-10所示。,图8-10交叉输入任意字符或数字,（3）选择【编辑】菜单中的【定位】命令，在打开的“定位”对话框中单击【定位条件】按钮，在打开的“定位条件”对话框中，选择【空值】，然后单击【确定】按钮。如图8-11所示。（4）选择【插入】菜单中【行】命令，这时便会从第三行开始，每一行的前面插入了一个空行。,图8-11定位条件,（4）选择【插入】菜单中【行】命令，这时便会从第三行开始，每一行的前面插入了一个空行。（5）复制表头的工资表头数据，选择A1：P1，选择【编辑】菜单中的【复制】按钮。然后本例选中工资表A3：A80列的数据区域，选择【编辑】菜单【定位】命令，在打开的“定位”对话框中单击【定位条件】按钮，在打开的“定位条件”对话框中，选择【空值】，然后单击【确定】按钮，这时会选中刚插入的空行，在选择【编辑】菜单中【粘贴】命令，这时工资条就算制作完成了。如图8-12所示。,图8-12插入表头法,8.3.5保护工资表,8.3.5.1保护工资簿为了防止别人随意打开或修改工作簿，可以在保存时给工作簿加个密码。方法如下：操作步骤：（1）选择【文件】菜单中的【另存为】命令；（2）在“另存为”对话框中选取【工具】菜单中【常规选项】，如图8-12所示。（3）在打开的【保存选项】对话框中，根据用户的需要输入“打开权限密码”或“修改权限密码”；（4）单击【确定】退出。,图8-12常规选项,8.3.5.2保护工资表,在使用中误修改或删除表格内容和各打印用的引用工作表，需要对表格中除了工资总表中员工基本信息录入的单元格以外的部分和各打印用的引用工作表进行保护。操作步骤：（1）选定需保护的单元格区域，选择【格式】菜单中【单元格】命令，在弹出的对话框中选择【保护】标签，从对话框中选中【锁定】，单由【确定】按钮退出。（2）选择【工具】菜单菜单中【保护】命令，选取【保护工作表】。如图8-13所示。勾选“保护工作表及锁定的单元格内容”，在“取消工作表保护时使用的密码”处输入密码。（3）单击【确认】。这样，如果别人想修改被保护的工作表，计算机会拒绝操作，从而确保了数据的安全。,图8-13保护工作表,8.3.5工资表页面设置,8.3.5.1在工资表的每一页加标题行如果工资表表格很长时，需要在每页都要打印同样的标题。操作步骤：（1）选择【文件】菜单，执行【页面设置】命令。（2）在打开的对话框中选择【工作表】标签。（3）选择“打印标题”在“顶端标题行”中输入“$2:$2”。如图8-14所示。或单击其右端的【】图标，选择“工资明细表”的标题行。如图8-15所示。（4）单击【确定】，标题行设置完成。,图8-14页面设置之工作表,图8-15标题行设置,8.3.5.2页眉和页脚设置,工作表的页眉和页脚包含了许多有用信息，例如工资表作为财务报表还需要有制表人签字、制表日期、公司徽标等等。操作步骤：（1）选择【视图】菜单，单击【页眉和页脚】命令。如图8-16所示。（2）选择【自定义页脚】。如图8-17所示。（3）单击标有【左】的输入框，输入“制表人：”。（4）单击标有“中”输入框，输入文字“第”，单击其上方的第二图标【】此时在其框中出现“&页码”，然后再输入“页”。接下来单击空格（主要为分割总页码），继续输入“共”字，单击其上方的第三个图标【】此时在其框中出现“&总页码”，在其后输入“页”，如图8-16所示。（5）单击标有“右”输入框，单击其上方的第四图标【】，设置日期，此时在其框中出现“&日期”。（6）单击【确定】两次。页眉页脚设置完成。,图8-16页面设置之页眉/页脚,图8-17页眉/页脚之自定义页脚,8.3.6分页设置与设置打印区域,8.3.6.1分页设置（1）插入水平分页符单击需要分页处的行标号，然后选择【插入】菜单，单击【分页符】命令，即可插入水平分页符。（2）插入垂直分页符单击需要分页处的列标号，然后选择【插入】菜单，单击【分页符】命令，即可插入垂直分页符。（3）插入水平、垂直分页符单击需要分页处的单元格，然后选择【插入】菜单，单击【分页符】命令，即可插入水平垂直分页符。（4）移动分页符在分页预览视图里，可以用鼠标拖动分页符来改变其在工作表中的位置。（5）删除分页符选择【插入】菜单，单击【删除分页符】命令，可删除插入的分页符。,8.3.6.2设置打印区域,（1）选择需要打印的工作表区域。如图8-18所示。（2）选择菜单【文件】菜单，执行【打印区域】命令下的子命令“设置打印区域”。此时，在选定区域下方出现一条双虚线。如图8-18所示。双虚线上方即为打印区域。提示：这种设置方法Execl会记住这个区域，下次执行打印任务时还会打印这个区域。除非选择“文件”菜单，执行“打印区域”命令下的子命令“取消打印区域”命令，让Excel取消这个打印区域。,图8-18打印区域选择,8.3.7打印预览,在Excel2003中采用了所见即所得的技术，一个文档在打印输出之前，通过打印预览命令可在屏幕上观察文档的打印效果，在打印预览的状态下还可以依据所显示的情况进行相应参数的调整。,8.3.7.1调整页边距,（1）选择【文件】菜单，执行【打印预览】命令。（2）在打开的【打印预览】窗口。如图8-19所示。在该窗口的最上面有一些命令按钮，通过这些命令按钮，可以用不同的方式查看版面效果或调整版面的编排，在“状态栏”上显示了当前的页号和选定工作表的总页数。（3）单击【页边距】按钮后，出现一些浅色的线条，这些线条代表所设定的上、下、左、右边界和页眉、页脚的位置。如图8-20所示。可以看到代表边界的四条线及图片的方框。（4）将鼠标指针移到适当的小方块上。按下鼠标按钮不放，并拖动鼠标。当拖动鼠标时，小方块所属的边界线会跟着一起移动，当把边界线移到理想的位置后，松开鼠标按钮，新的边界即已设定好了。,图8-19打印预览,页边距,图8-20页边距,8.3.7.2调整页眉页脚位置,（1）选择【文件】菜单，执行【打印预览】命令。（2）在【页边距】中，页眉/页脚的四周有一个虚线线条围成的框。如图8-21所示。页眉位置页脚位置（3）将鼠标指针移到围绕页眉/页脚的方框中，按下鼠标按钮不放，并拖动鼠标。当拖动鼠标时，方框会随之移动，窗口右上方显示的与边缘距离也跟着变化，可以让我们了解新的位置所在。当把方框移到所要的位置后，松开鼠标按钮，新的位置即已设定好了。,8.3.7.3分页预览,（1）选择【文件】菜单，执行【打印预览】命令。（2）在打印预览状态下单击【分页预览】按钮或在编辑状态下单击【视图】菜单下【分页预览】命令，会切换到分页预览视图模式，蓝色外框包围的部分就是系统根据工作表中的内容自动产生的分页符。（3）如果要改变打印区域，可以在蓝色的外框上按住鼠标左键不放并拖曳，松开鼠标后即可看到新的打印区域。如果要取消分页预览设置时，可以单击【视图】菜单下【普通】命令。,8.4知识拓展,8.4.1不打印包含零值的单元格（1）选择【工具】菜单中【选项】命令。（2）在打开【选项】对话框，进入【视图】选项卡，把【窗口选项】区的【零值】复选框的勾去掉。如图8-22所示。（3）单击【确定】按钮后，返回Excel编辑窗口，可以看到工作表中的所有零值都变成了空白单元格，再执行【打印】命令，零值单元格就不会被打印出来。,图8-22不打印“0”值,8.4.2不打印包含错误值单元格,（1）选择菜单【文件】菜单的【页面设置】命令。（2）打开【页面设置】对话框，进入【工作表】选项卡，在【打印】区单击“错误单元格打印为”下拉框，选择“空白”项。如图8-23所示。（3）单击【确定】按钮，这样在打印的时候就不会将这些错误信息打印出来了。,图8-23不打印错误值,8.4.3打印工作表中的公式,（1）选择【工具】菜单中【选项】命令。（2）在打开【选项】对话框，进入【视图】选项卡，勾选“窗口选项”区的【公式】复选框。如图8-24所示。（3）单击【确定】按钮后返回Excel编辑窗口，可以看到所有的公式都显示出来了，此时再打印工作表，就会把工作表中所有的公式内容都打印出来。提示：使用快捷键“Ctrl”可以快速地在公式和计算结果之间进行切换。要打印公式，切换到公式显示时，执行打印命令即可。,图8-24打印公式,8.4.4打印特定的几个区域,8.4.4.1几个区域打印在不同页中方法1：操作步骤：（1）按住【Ctrl】键，同时选中要打印的几个区域。（2）选择【文件】菜单的【打印】命令，在弹出的【打印内容】对话框的【打印内容】区，勾选【选定区域】单选框。如图8-25所示。（3）单击【确定】即可。,图8-25选定打印区域设置,方法2：（1）在“普通”视图选定一个需要打印的区域。（2）选择【视图】菜单中，单击【分页预览视图】命令，在分页视图下，按住【Ctrl】键，同时选中要打印的几个区域，单击鼠标右键，在弹出的菜单中选择【添加到打印区域】命令即可。如图8-26所示。用同样的方法设置其它需要打印的区域。,图8-26添加打印区域设置,8.4.4.2几个区域打印在同页中,（1）选择【视图】菜单，执行【视面管理器】命令，打开【视面管理器】对话框。如图8-27所示。单击其中的【添加】按钮，打开【添加视图】对话框。如图8-28所示。输入一个名称（如“全部内容”），单击【确定】退出。,图8-27视图管理器,