excel函数在工资表中的应用.docxVIP

excel函数在工资表中的应用 随着经济的不断发展，会计行业的数量不断增加，工作量也在增加。为减轻会计从业人员的负担,提高工作效率,通过两例Excel函数实例,可以轻松解决日常问题。 1 工资条的设置 使用Excel按人头制作工资条通常有两个解决方案,一个是利用Word的“邮件合并”功能,另一个是利用VBA(可视化基础语言应用程序)来实现。 用“邮件合并”功能操作虽说不难,但数据过多时也很麻烦;用VBA处理起来倒是很方便,但几十行的程序也够初学者忙活的。经过一番试验,笔者找出了一个比较简单的方法,只需一个公式就可以按人头打印出工资条来。 新建一Excel文件,在sheet1中存放工资表的原始数据,假设有M列。第一行是工资项目,从第二行开始是每个人的工资在sheet2中我们来设置工资条。根据实际情况,工资条由三行构成,一行对应工资项目,一行对应一个人的工资数据,然后是一个空行用来方便切割。这样三行构成一个工资条。工资项目处在行号除以3余数为1的行上;空行处在行号能整除3的行上。以上两行不难设置,关键是工资数据行,牵扯到sheet1与sheet2中数据的对应,经分析不难看出“sheetl中的数据行=INT((sheet2中的数据行+4)/3)”(图1)。 这样我们在sheet2的A1单元格中输入公式“=IF (MOD (ROW (),3)=0,,IF (MOD (ROW (),3)=1,Sheet1!A$1,INDEX (Sheet 1!$A:$N,INT((ROW()+4)/3),COLUMN())))”。确认后选择A1单元格,把鼠标放在A1单元格的右下角,鼠标变成“+”时,向右拖动鼠标自动填充至M列,这样工资条中的第一行就出来了。选定A1:M1,把鼠标放在M1单元格的右下角,鼠标再次变成“+”时,向下拖动鼠标自动填充到数据的最后一行,工资条就全部制作完成了。 该公式运用IF函数,对MOD函数所取的引用行号与3的余数进行判断。如果余数为0,则产生一个空行;如果余数为1,则固定取sheet1中第一行的内容;否则运用INDEX函数和INT函数来取Sheet1对应行上的数。 最后来设置一下格式,选定A1:N2设上表格线,空行不设。然后选定A1:N3,拖动N3的填充柄向下自动填充,这样有数据的有表格线,没有数据的没有表格线。最后调整一下页边距,千万别把一个工资条打在两页上(图2)。 2 利用int函数解决零头不够、残余时的问题 目前一些中小企业是用现金作为工资发放的主要形式,这给财务人员带来了负担,特别是出现零头不够或多余时更是麻烦。如果此时能算出每个人各种面额人民币的张数,财务人员到银行取钱时按实际支取,就不会出现零头不够或多余的问题了,巧用微软的Excel软件的INT函数可以轻松解决这个问题。如要完成下列表格的统计(见表)。 2.1 计算补贴的张数 在C2中输入=INT(B2/100)即可。INT函数是取整函数,功能为取不大于参数中的最大整数,对工资总额除以100(面额),再取整就可得出面额是100元的张数,再拖动填充柄(复制公式)即可算出其他人的张数。在C6中输入=SUM(C2:C5)即可得出所有人面额是100元的总张数。 2.2 计算已发的额为100元的总钱额 在D2中输入=INT((B2-C2*100)/50)即可。其中C2*100为已发的面额是100元的总钱额,那么(B2-C2*100)是剩余未发的钱额,再除以50取整后就可得出面额是50元的张数,再拖动填充柄即可算出其他人的张数。在D6中输入=SUM(D2:D5)即可得出所有人需面额是50元的张数。 2.3 填充柄计算张数的方法 在E2中输入=INT ((B2-C2*100-D2*50)/20)即可。其中(B2-C2*100-D2*50)是剩余未发的钱额,再除以20取整后就可得出面额是20元的张数,再拖动填充柄即可算出其他人的张数。在E6中输入=SUM(E2:E5)即可得出所有人需面额是20元的张数。 2.4 填充柄计算张数的方法 在F2中输入=INT((B2-C2*100-D2*50-E2*20)/10)即可。其中(B2-C2*100-D2*50-E2*20)是剩余未发的钱额,再除以10取整后就可得出面额是10元的张数,再拖动填充柄即可算出其他人的张数。在F6中输入=SUM(F2:F5)即可得出所有人需面额是10元的张数。 2.5 计算每相线张数的计算 在G2中输入=INT((B2-C2*100-D2*50-E2*20-F2*10)/5)即可。其中(B2-C2*100-D2*50-E2*20-F2*10)是剩余未发的钱额,再除以5取整后就可得出面额是5元的张数,再拖动填充柄即可算出其他人的张数。在G6中输入=SUM(G2:G5)即可得出所有人需面额是5元的张数。 2.6