矿用通信电缆MHYV厂家
免费服务热线

Free service

hotline

010-00000000
矿用通信电缆MHYV厂家
热门搜索:
技术资讯
当前位置:首页 > 技术资讯

制作强大的资金来往管理明细表

发布时间:2021-01-20 08:44:57 阅读: 来源:矿用通信电缆MHYV厂家

对于很多从事财务统计工作的朋友来说,资金管理是日常工作中非常重要的一项任务,特别是每到月尾岁末的时候,更是需要进行繁忙统计汇总工作,尽管现在很多单位都已经使用了财务软件,但是对于一些相对比较繁杂的资金管理业务,财务软件也并不一定适合本单位使用,而使用大家熟悉的Excel,就可以设计出一份非常专业的资金来往管理系统,更为重要的是,充分挖掘Excel的潜能,能够根据本单位的需要,进行个性设计,下面就以某单位2009年度资金管理为例,来介绍具体的操作方法。

本文实例所用到的表格可以从下载到。

1.整理资金管理相关基础资料

通常资金管理要涉及到经办人、资金来往项目以及明细项目等资料,为了以后使用上的方便,将这些资料预先整理,并根据具体情况,予以定义名称,对于以后的资金管理工作是非常有益的。

启动Excel2007,新建一个工作簿,将其命名为“2009年度资金管理明细表”,在其中新建一个工作表,将其命名为“基础资料表”,在此表中B2、C2单元格中分别输入经办人、资金来往项目列标题,并在下面输入具体的资料,同时在E2、F2单元格中,输入资金来往项目的下级明细项目列标题,如收货款、付提成,并在下面输入具体的明细项目(见图1)。

随之单击“公式”菜单,从弹出的“定义名称”功能区中选择“名称管理器”选项,在弹出的“名称管理器”对话框中,单击“新建”按钮,在随之弹出的“新建名称”对话框中依次创建以下几个定义名称:经办人,其引用位置为“=基本资料表!$B$3:$B$13”;资金用途项目,其引用位置为“=基本资料表!$C$3:$C$13”;数据区原点,其定义名称为“=基本资料表!$B$2”;列标题,其引用位置为“=基本资料表!$B$2:$F$2”;收货款,其引用位置为“=基本资料表!$E$3:$E$8”;付提成,其引用位置为“=基本资料表!$F$3:$F$13”,创建完毕后,关闭此对话框。

2.编辑日常使用的资金使用明细表

作为单位的日常资金管理来说,主要是资金的收支数据以及相关信息的记载,因此新建一个工作表,将其命名为“资金使用明细表”,在此表的B3:L3区域中依次输入相关列标题,如日期、凭证号数、摘要、项目、明细项目、经办人、借方金额、贷方金额 、余额等内容,然后在“记载资金”来往数据的第一行的D6中输入“期初余额”,并在K6中输入“期初金额”,如120000元;选择记载日期的B7:B200区域,右击鼠标,从弹出的右键菜单中选择“设置单元格格式”命令,将此区域的格式设置为一种日期类型,如“3月14日”,由于凭证号数一般为固定的多位序号格式,如8位的20090001,因此将凭证号数所在的C7:C200单元格区域设置为自定义格式,其类型为“20090000”,这样当输入凭证号数为1时,就会显示为20090001。

接着选择资金来往项目的F7:F200区域,单击“数据”菜单,从弹出的“数据”功能区中,选择“数据有效性”选项,从随之弹出的命令列表中,选择“数据有效性”选项,在弹出的设置窗口中,将“设置”选项卡下面的“允许”设置为“序列”,在“来源”框中输入公式“=资金用途项目”,关闭此窗口(见图2)。然后选择资金来往明细项目所在的G7:G200区域,将其数据有效性“来源”设置为“=OFFSET(基本资料表!$E$3:$J$13,MATCH(资金使用明细表!$G$7,基本资料表!$E$3:$M$3,0),)”;选择经办人所在的H7:H200区域,将其数据有效性“来源”设置为“=经办人”。最后在计算资金余额的K7单元格中输入公式“=IF(F7="","",SUM(K6,I7,-J7))”,并将其拖动到下面的对应单元格区域中,在底部的借方金额合计、贷方金额合计以及年终余额中分别输入公式“=SUM(I7:I200)”、“=SUM(J7:J200)”、“=K6+I201-J201”。

小提示

此处明细项目所在的单元格数据有效性来源,使用了“=OFFSET……”公式,此公式的作用是,利用OFFSET、MATCH函数,结合前面设置的数据区原点、列标题等定义名称,使G7:G200单元格区域中的可供选用数据,与F7:F200中的数据联动起来,而计算余额的“=IF(F7=""……”公式,则能够根据F7中是否存在摘要内容,而决定是否计算余额,从而避免了在K7:K200区域中没有发生金额,也计算余额的错误。

这样在日常资金管理工作中,当输入每条资金变动信息时,只需要输入日期、凭证编号以及摘要,单击项目下面的F列单元格,即可弹出下拉列表框,从中选择对应资金来往项目,而单击G列单元格,则也可以弹出下拉列表框,而且这种列表框与F列是级联的,也就是说只会显示F列中项目的相关下级明细项目,而当输入借方、贷方金额后,会在K列中知道计算出正确的余额(见图3)。

3.利用函数,进行资金期间汇总以及明细查询

在资金管理过程中,经常需要进行查询、汇总,而要进行这些工作时,可以通过新建一个汇总表以及查询表来完成。

新建一个工作表,将其命名为“汇总表”,输入标题后,在D2中输入“请输入查询期间:”,然后在F2中输入“至”,而用E2、G2单元格用来存储查询的起止日期。在C6:H6中输入汇总表格列标题,如汇总项目、收 入、支 出等,而在D8:D20区域中输入要汇总的资金来往项目,如收货款、其他转入、其他收入、收入合计、付工资等,然后在汇总本期输入的E8单元格中输入公式“=SUMPRODUCT((资金使用明细表!$B$6:$B$367>=VALUE($E$2))*(资金使用明细表!$B$6:$B$367<=VALUE($G$2)*(资金使用明细表!$F$6:$F$367=$D8)),(资金使用明细表!$I$6:$I$367))”,在汇总本期支出的G8中输入公式“=SUMPRODUCT((资金使用明细表!$B$6:$B$367>=VALUE($E$2))*(资金使用明细表!$B$6:$B$367<=VALUE($G$2)*(资金使用明细表!$F$6:$F$367=$D8)),(资金使用明细表!$J$6:$J$367))”,并将这两个公式复制到对应的其他需要汇总的项目单元格中,同时在收入、支出合计的单元格中输入求和公式。这样当在E2、G2中输入汇总查询的起止日期后,如2009年7月1日至2009年11月30日就会在下面的表格中,自动计算汇总各个资金来往项目在此期间的汇总金额(见图4)。

小提示

此处使用了“=SUMPRODUCT((资金使用明细表!$B$6:$B$367>=VALUE($E$2))”公式,其作用为是利用SUMPRODUCT函数,根据E2、G2中的起始日期以及D8中的要汇总项目,如“收货款”,将资金使用表中!$I$6:$I$367区域中符合条件的数值予以求和。

由于资金管理过程中,还需要进行明细查询,如按照资金来往项目、经办人、明细项目等,而这些同样可以通过新建一个工作表来完成,新建一个工作表,将其命名为“按照资金用途项目查询”,在此表的C3中输入“请输入查询资金来往项目:”,将D3单元格的数据有效性来源设置为“=资金用途项目”,在C4:J4单元格区域中,将资金使用明细表中的列标题复制过来,然后在显示查询数据日期的C7单元格中输入以下数组公式“{=INDEX(资金使用明细表!B:B,SMALL(IF(资金使用明细表!$F$6:$F$367=$D$3,ROW(资金使用明细表!$F$6:$F$367),500),ROW(B1))) =INDEX(资金使用明细表!B:B,SMALL(IF(资金使用明细表!$F$6:$F$367=$D$3,ROW(资金使用明细表!$F$6:$F$367),500),ROW(B1)))}”,而在后面的显示凭证号数、查询摘要、来往项目等单元格中,仍然按照以上的格式输入相关公式,输入时,只需要修改第一个公式中的“资金使用明细表!B:B”中的列号即可,如在显示凭证号数的D7中,只需要将公式中的列号修改为“资金使用明细表!C:C”即可,其他类推,然后将这些公式复制到对应的下面单元格中(见图5)。

小提示

此处使用了数组公式“{=INDEX(资金使用明细表!B:B……”,其作用为利用INDEX、SMALL、IF等公式,根据D3单元格中要查询的资金来往项目,将资金使用明细表!B:B列中符合条件的数据引用过来。还需要注意的是,为了避免在此表的C:C列中显示不应有的错误值“1月0日”,应将此列中设置为不显示零值,单击左上角的Office按钮,从弹出菜单中选择“Excel选项”,在弹出的选项设置窗口中,将“高级”类别中的“在具有零值的单元格中显示零”复选项取消即可。

这样单击D3单元格中,从弹出的下拉列表框中选择所要查询的资金来往项目,如“付货款”,就可以在下面的明细表中显示所有的查询数据了。

通过以上方法,不仅仅可以应有在资金管理工作中,还可以应用在生产、考勤、人事管理等等各个需要进行统计汇总的工作中,而且可以按照不同的项目进行汇总查询,只需要修改相应的查询选项以及公式即可。

蚂蚊彩呗官网app下载

魔力宝贝觉醒九游版

天使童话

封神来了BT版