用Excel制作工资报表的注意点.doc

Excel中IF函数实例:制作工资表 计算个人所得锐一、 问题的提出 1. 所得税计算问题。按照个人所得税有关规定，课税工资小于等于1000元时，不纳税；工资大于1000元且小于等于1500元时，税率为5%；工资大于1500元时，税率为10%。该会计应用逻辑函数IF( )对所得税额作两段处理，在“所得税”P3中输入公式“=IF(O31500, (O3-1500)*0.1+500*0.05, (O3-1000)*0.05)”，以致课税工资小于等于1000元时无法得出税额为零。其处理情况如图1（原始报表很大，不便观察，该图系对原始报表的缩简）。图中“应税工资”O8、O12分别为908.66、838.26，故“所得税”P8、P12中的值应为0，而现在却分别为4.57和8.09。为了使课税工资小于等于1000元时税额为零，只得重新核查报表并在相关单元格输入零。图1 工资报表2. 工资表平衡问题。该会计每次制作工资表总是难以平衡，误差少则几分钱，多则几角。图1中Q10中的公式为“=O10-P10”，应为1316.82，可表格计算却为1316.83，原因何在？百思不得其解。为了平衡工资表，又得核查修改。上述两个问题的出现，耗费了会计大量的时间，以至于制作一张工资报表需好几天，有人建议她改用其它软件，可她又不想因改学其它软件而支付学习成本。基于这种考虑，她迫切需要解决以上难题，二、 问题的解决1. 所得税的计算。用逻辑函数IF( )计算所得税的思路是正确的，但利用单层IF( ) 函数，无法处理两种以上的状态，单层函数只能按给定表达式的值或真或假，返回两种状态中的一种，以致课税工资小于等于1000元时无法得出税额为零。而利用IF( ) 函数的嵌套，则可实现对多种状态的处理。所谓函数嵌套，指函数的参数包含子级函数，Excel函数嵌套最多可含7层。鉴于此，可用嵌套函数改写 “所得税”P3中的公式，公式为：“=IF(O31500,（O3-1500）*0.1+500*0.05 , IF(O31000, (O3-1000)*0.05,0 )，并将该公式复制到“P4：P12”。这样处理，课税工资小于等于1000时的税额即可为零，如图2中P8、P12的值为0。2. 工资表的平衡。工资表不平衡源于对所得税小数位数的取舍。按实际意义，所得税应为两位小数，而按税率公式计算的所得税却为三位小数。图1所得税为两位小数只是一种形式，是通过格式化单元格而得到的，实际上它是三位小数。其中，P10形式为16.68，实为16.675，Q10中的值应为：“1333.50-16.675=1316.825”，由于取两位小数，于是出现了：“1333.50-16.68=1316.83”的误差。这种误差只出现于所得税小数第三位为5 的情况，至于其它情况则不会出现。一个单位职工人数多达数百人，所得税小数第三位为5的对象肯定不止一个，这样的对象越多，则误差越大。如何解决这一问题？利用舍入函数ROUND（），将所得税由形式上的两位小数变为实际的两位小数，即可解决这一问题，即将“所得税”P3中的公式改为：“=IF(O31500,ROUND(O3-1500)*0.1+500*0.05,2),IF(O31000,ROUND(O3-1000)*0.05,2),0)，并将该公式复制到“P4：P12”。由此，工资表的平衡问题得到圆满解决。图2 P8、P12的值为0EXCEL设计高效的报表相信制作过报表或者浏览过诸多报表的读者一定不会觉得有比Excel更好用的界面环境了，EXCEL界面环境确实是设计报表的不二选择，其设计效率与便捷的操作设置在定义静态报表方面的优势无可比拟。 同样的争议也曾经在专业级报表工具业内出现过，到底是控件式拖拽设计还是类似于EXCEL的设计模式？毕竟几个元老级别报表工具使用的都是控件拖拽的设计界面，但是，自从润乾报表推出类EXCEL报表设计工具以来，这一争议得到了更响亮的回应：即便是在专业的报表设计工具内，类似于EXCEL的设计报表的工作效率还是远超出控件拖拽式的设计，如同以前的结论：在习惯了EXCEL的设计环境以后，没有人愿意拿PowerPoint的矩形框来拼表！ EXCEL即便是对非专业人士而言，也是一个容易上手的制表工具，入门根本不需要多少时间，很快就可以从无到有制作出界面精美的表格。下面我们来看看快逸类EXCEL的设计模型在多大程度上继承了EXCEL的特点。 快逸报表类EXCEL的设计界面在左侧窗口的主设计界面，是最为常见的行列式二维表格，在这里设定格式时候会发现，在设定静态风格上，快逸与EXCEL的功能完全一致： 设定单元格宽度和高度、背景色、前景色、显示格式、换行、格线等等； 字体类型、字体大小、字体粗细、斜体及下划线设置等； 数据水平对齐、垂直对齐； 格式刷； 在定义格式方面，这些都与EXCEL毫无差别，而快逸的设计环境在静态格式的基础上还对单元格的表达式进行了继承；还记得如何在EXCEL中定义表达式么？想要在E3单元格中汇总A3-D3的数据，那你要在E3单元格中写入：=SUM(A3:D3)。同样的例子不妨拿到快逸里面试一下，你会发现，在快逸里面表达式处理与EXCEL如出一辙。 快逸可以像EXCEL一样对位置变动的单元格中的表达式自动调整，如果在上面的表格里面，我在B3和C3之间插入一个新列，这时候EXCEL的F3单元格（原E3）的表达式会自动调整为:=SUM(A3:E3)，同样，在快逸的设计表格里，当设计者插入一行或者一列时，相关的表达式也会进行自动调整；不仅如此，诸如：A2+B3-A4、（A1+E2*D4）/C3，同EXCEL一样，这类灵活的公式定义也能得到正确运算。 快逸报表是在继承EXCEL设计习惯的基础上实现了动态扩展数据的功能，这也是EXCEL无法实现的地方，例如下面报表的设计： 它的实际设计界面如下： 就工作效率而言，处理诸如上例之类的报表，快逸报表毫无疑问是报表设计人员的利器，正是由于快逸的这种先进的类EXCEL的设计模式，与以往的工具相比，同一报表的制作效率提高竟达90。这也是为什么自快逸报表问世以来，受到广大报表设计者、编程人员、项目管理者的热捧，因为同传统的报表工具相比，快逸报表确实是提高WEB报表设计效率，缩短开发周期，有效降低开发成本不可多得的新一代报表工具。用EXCEL做进销存的仓库报表(一)新建工作簿 1单击开始菜单，在弹出的开始菜单项中单击新建office文档，出现新建office文档对话框窗口。 2新建office文档对话框窗口中的常用活页夹中，双击空工作簿，出现名为Book1的空工作簿。 3将Book1保存为进销存自动统计系统xls。 (二)定义工作表名称及数据 1双击Sheet1工作表标签，输入进货后按【Enter】键。 2双击Sheet2工作表标签，输入销售后按【Enter】键。 3双击Sheet3工作表标签，输入进销存自动统计后按【Enter】键。 4选择进货工作表，输入标题(进货日期、商品名称、进货数量)和相应各项数据。 限于篇幅，以及仅为说明问题起见，这里只列举甲、乙、丙三种商品(表1图)。 5选择销售工作表，输入标题(销售日期、销售去向、商品名称、销售数量)和相应各项数据(表2图)。 6选择进销存自动统计工作表，在第一行中分别输入标题内容：商品名称、当前总进货量、当前总销售量、当前库存量(表3图)。(三)定义公式 1在进销存自动统计工作表中选择B2单元格，输入=SUMIF(进货！B：B，甲，进货！C：C)，按【Enter】键。 2向下拖动B2单元格右下方的黑点至B4单元格，进行公式复制的操作。 3选择B3单元格，按F2键，修改公式中的甲为乙，同样，修改B4单元格公式中的甲为丙。如果有更多的商品，依此类推，直至修改完毕为止。注意，从公式定义可以看出，此例中的单元格相加求和的条件依据是商品名称：甲、乙、丙。 4选定B2至B4单元格，向右拖动B4单元格右下方的黑点至C列，进行公式的复制操作。 5选择C2单元格，按F2键，将公式中的进货修改为销售，同样，再分别修改C3、C4单元格公式中的进货为销售。如果有更多的单元格需要定义公式，依此类推，直至修改完毕为止。 6选定D2单元格，输入=B2-C2，按【Enter】键。 7向下拖动D2单元格右下方的黑点至D4单元格(如果有更多的，一直向下拖动到最后一个单元格即可)，完成公式的复制工作。(四)库存报警(字符突出显示)设置 1单击D列的列标，然后选择格式菜单中的条件格式命令。 2在打开的条件格式对话框中，在条件1区域中进行最高库存量报警的突出显示设置： 首先，从左到右，分别选定单元格数值(Excel97中是单元格数值为)、大于或等于，并输入一个合适的最高库存量报警线数字。 然后，单击格式按钮，在打开的对话框中设置颜色为红色，字形为加粗。 最后按确定按钮，完成库存一旦超高即报警的突出显示设置。 3在条件格式对话框中，单击添加按钮，随即便会增加一个条件2区域。 在条件2区域中进行最低库存量报警的突出显示设置： 首先，从左到右，分别选定单元格数值、小于或等于，并输入一个合适的最低库存量报警线数字(比如，输入1，表示当库存只剩一件或没有时，突出警示)。 然后单击格式按钮，再在打开的对话框中设置颜色为蓝色，字形为加粗。 最后按确定按钮，即完成库存超低的报警突出显示设置。(五)日常应用 1平时，每次只要在进货工作表和销售工作表中输入实际发生的进货或销售数据，进销存自动统计表中便会自动得到当前的总进货量、当前的总销售量以及当前库存量。同时，当库存量超过或低于报警线数字时，就会以红色或蓝色并加粗字符来突出显示。 2购入进货工作表中没有的新货时，需要按照上面所述方法在进货工作表和进销存自动统计工作表中增设相应的商品名称及其取数公式，公式设置还是按照前面所描述的方法，采取复制加修改的方法最快捷。结束语 本文提供和介绍了利用Excel实现有关进销存业务自动统计的一种基本思路和基本做法，其中重点是公式和条件格式的运用。至于商品进销存业务中的商品编号、业务摘要 、单价、金额以及备注等，可根据各自需要在工作表中进行相应设置；也可以对举例中的数据项标题名称进行更改；还可以对公式中单元格相加求和的条件依据进行更改，比如，商品名称变为商品编号。用EXCEL做报表用EXCEL做报表时建立了两个报表，报表1是学生登记表，表2是学生学号，表1中，当A列录入学号时，B列姓名就自动引用工作簿中学生名录中的相对应学号的学生姓名。如当A5录入学号7时。B5就自动引用工作簿中学生名录中的B11的学生姓名“主办者”要在表1的B列写公式，用VLOOKUP举例说明：（此处表1和2都在一个工作簿内，在不同工作薄的话大同小异）表1：A列要输入学号，B列自动出学生姓名表2：A列为学号，B列为学生姓名也就是说表2为数据源表，后面的输入都是在表1完成第一行为标题行，A1输入“学号”，B1输