酱腌菜厂家
免费服务热线

Free service

hotline

010-00000000
酱腌菜厂家
热门搜索:
技术资讯
当前位置:首页 > 技术资讯

玩的就是专业Excel打造客户往来款管理系统

发布时间:2020-02-11 05:08:49 阅读: 来源:酱腌菜厂家

在统计财务管理过程中,客户来往款处理是非常重要的工作之一,特别是涉及到客户欠款管理工作更是重中之重,这项工作涵盖了多个方面,包括客户资料、来往款明细以及欠款帐龄分析等繁杂工作,而通过传统的记账方式操作起来效率非常低,其实通过Excel来设计一份智能的来往款管理系统,就完全可以胜任这方面的工作,让你的工作效率大大提高,下面就以某单位的客户来往款管理为例,来介绍具体的操作方法。

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

1.整理客户资料以及客户来往款明细表

作为客户来往款管理工作,其中很重要的一点就是客户基本资料的整理工作以及日常的来往款处理工作。启动Excel 2003,新建一个名为的“客户来往款管理”的工作簿,在其中新建一个工作表,将其命名为“客户基本资料”,在其中输入客户名称、联系人、电话、负责人、地址等信息,同时为了以后的数据处理过程中,便于调用客户信息,需要将客户名称予以定义名称。定义名称时,执行“插入→名称→定义”命令,在弹出的定义名称对话框中,将名称文本框输入定义名称“客户名称”,引用位置设置为“=客户基本资料!$B$4:$B$29”,单击“确定”按钮(见图1)。

为了处理日常客户来往款数据,新建一个工作表,将其命名为“来往款明细表”,在其中的B2:G2行中,输入客户名称、摘要、日期、借方、贷方、余额等列标题,然后选择客户名称所在的B3:B100区域,执行“数据→有效性”命令,在弹出的设置窗口中,将“设置”选项卡中有效性条件的“允许”设置为“序列”,“来源”框中输入“=客户名称”,单击“确定”按钮。同时在计算客户来往款余额的G3单元格中输入数组公式“{=SUM((B$3:B3=B3)*(E$3:E3))-SUM((B$3:B3=B3)*(F$3:F3))}”,并将此公式复制到下面的G3:G100区域中(见图2)。

小提示

此处的{=SUM((B$3:B3=B3)……}为数组公式,将{ }中的公式输入后,不要直接按回车键,而是要同时按Ctrl+Shift+Enter组合键,这时系统自动会添加“{ }”符号,此公式作用是自动将截至到当前日期的某客户来往款借方合计减去贷方合计。

这样当需要输入客户名称时,只要单击B3:B100列中的任一单元格,就会弹出下拉列表框,从其中选择需要的客户名称即可,然后在借方、贷方区域中输入来往款数据,其中借方为客户欠款数据,而贷方为客户还款数据,在后面的G3:G100区域中,会自动计算出当前客户欠款余额。

2.巧妙设计,自动分析欠款帐龄

由于很多单位来往款比较多,而且由于种种原因,有的客户长期欠款不还,这时候就需要进行应收款帐龄分析,以便于及时进行财务分析以及催款,需要进行帐龄分析,通过以下操作就可以非常方便地完成这项工作。

新建一个工作表,将其命名为“欠款分析”,在其中的C2单元格中输入公式“=NOW()”,以便于确定帐龄分析的基准日期,如2009年8月23日,根据单位欠款帐龄的分析需要,如将欠款分为1~30日、30~60日、60日以上几个时间段,然后在A3:J3区域中,输入单位名称、1-30天、30-60天、客户地址等列标题,同时为了便于帐龄分析,在D3、F3单元格中输入“未还款”。然后将客户名称复制到B3:B100区域中(见图3)。

在C4单元格中输入公式“{=IF(D4>0,SUM((来往款明细表!B$3:B$100=欠款分析!B4)*($C$2-来往款明细表!D$3:D$100<=30)*(来往款明细表!E$3:E$100)),SUM((来往款明细表!B$3:B$100=欠款分析!B4)*($C$2-来往款明细表!D$3:D$100<=30)*(来往款明细表!E$3:E$100))+D4)}”,D4中输入“{=IF(F4>0,SUM((来往款明细表!B$3:B$100=欠款分析!B4)*($C$2-来往款明细表!D$3:D$100>30)*($C$2-来往款明细表!D$3:D$100<=60)*(来往款明细表!E$3:E$100)),SUM((来往款明细表!B$3:B$100=欠款分析!B4)*($C$2-来往款明细表!D$3:D$100>30)*($C$2-来往款明细表!D$3:D$100<=60)*(来往款明细表!E$3:E$100))+F4)}”,E4中输入公式“=IF(D4<0,0,D4)”,F4中输入公式“{=SUM((来往款明细表!B$3:B$100=欠款分析!$B4)*($C$2-来往款明细表!D$3:D$100>60)*(来往款明细表!E$3:E$100))-SUM((来往款明细表!B$3:B$100=欠款分析!$B4)*(来往款明细表!F$3:F$100))}”,G4中输入公式“=IF(F4<0,0,F4)”,H4中输入“=SUM(C4,E4,G4)”,I4中输入公式“=IF(ISERROR(VLOOKUP(B4,客户基本资料!$B$3:$H$13,2,FALSE)),"",VLOOKUP(B4,客户基本资料!$B$3:$H$13,2,FALSE))”,J4中输入公式“=IF(ISERROR(VLOOKUP(B4,客户基本资料!$B$3:$I$13,3,FALSE)),"",VLOOKUP(B4,客户基本资料!$B$3:$I$13,3,FALSE))”,并将这些公式复制到下面的单元格区域中。

小提示

此处大量应用了不少公式,虽然看似复杂,其实也很简单,其中C4中的{=IF(D4>0……用来计算1~30日未还欠款数据,D4中的{=IF(F4>0……用来计算30~60日未还欠款,F4中的{=SUM((来往款明细表!B$3:B$100……则用来计算超过60日的未还欠款,I4 、J4中的=IF(ISERROR(VLOOKUP(B4……是通过B4中的客户名称来引用“客户基本资料”表中的联系人以及联系电话信息。

当输入这些公式后,在相应的单元格中,就会自动计算出某个客户的欠款帐龄分析情况,比如东明科技有限公司超过60日的欠款为2000.00元,而且在后面自动显示欠款单位联系人以及电话信息,以便于及时催款。

3.让工作更高效,快速打印催款单

每到月末,当客户欠款数据统计完毕,就需要进行催款工作,而打印催款的也是不小的工作量,设计打印催款单表时,新建一个工作表,命名为“催款单”,在其中编辑一个催款单模板,如“致:XXXX,截止2009年8月23日贵公司尚欠货款额为:XXXX”,为了自动引用前面几个工作表的相关数据,采用前面的方法将存储欠款单位信息的B3单元格,设置其数据有效性的来源为“=客户名称”,存储客户欠款金额的E4单元格中输入公式“=IF($B$3="","",INDEX(欠款分析!H4:H100,MATCH($B$3,欠款分析!B4:B100,0)))”,而在存储大写金额的G4单元格中输入公式“="大写人民币:"&SUBSTITUTE(SUBSTITUTE(IF(E4<0,"负","")&TEXT(TRUNC(ABS(ROUND(E4,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(E4,2))),"",TEXT(RIGHT(TRUNC(ROUND(E4,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(E4,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(E4,2),3))=".",TEXT(RIGHT(ROUND(E43,2)),"[DBNum2]")&"分",IF(ROUND(E4,2)=0,"","整")),"零元零",""),"零元","零")”

这样当从B3下拉列表框中选择欠款单位,如东明科技有限公司,在E4中就会显示欠款金额2000.00元,并且在G4显示大写金额贰仟元整,然后进行打印即可(见图4)。

4.一统全局,添加精致首页

虽然此工作簿能够高效管理客户来往款欠款,但是由于存在多个工作,使用起来需要逐个切换,非常麻烦,针对这种情况,可以给整个工作表设置个首页,这样从首页上就可以自动切换到各个工作表了。编辑首页可以采用设置超链接或者VBA的方法,这里采用了更为快捷的VBA方法,特别适合于大量工作表的数据处理。新建一个工作表,将其放置在其他工作表的最前面,命名其为“首页”,在这个工作表添加一个工作表标题,然后在其中的C6:G6区域中输入各个工作表的表名,然后按下ALT+F11组合键,启动VBA编辑器,在其中输入如下代码(见图5):

Private Sub Worksheet_SelectionChange(ByVal

Target As Range)

If unt > 1 Then Exit Sub

If Intersect(Target, Range("c6:g6")) Is Nothing Then Exit Sub

If Target = "" Then Exit Sub

Sheets(lue).Activate

End Sub

小提示

此代码中的If Intersect(Target, Range("c6:g6")) ……作用是如果选择的单元格不在工作表名所在的c6:g6区域内,则退出程序。

最后单击“保存”按钮,关闭VBA编辑器,这样当单击“首页”工作表中的某个工作表名时,就会自动切换到相应的工作表上(见图6)。

以上操作都是针对客户来往款管理进行的,其实利用以上操作思路以及相关公式,也可以应用在日常工作的其他方面,如存货管理、资金管理等等各个方面。(Wj)

注册公司问题

广州筹划税务方案

广州注册公司需要什么手续

代理记账

相关阅读