EXCEL技巧：制作简易“应收账款账龄分析表”
应收款项及账龄分析Excel图表模板
应收帐款明细及帐龄分析表
公司名称：
年
对方名称 期末余额
1年以内
金额 比例
一、外部单位及个人：
月日
帐龄
1-2年
2-3年
金额 比例 金ቤተ መጻሕፍቲ ባይዱ 比例
3年以上 金额
二、内部单位：
合计
说明：往来款项的帐龄分析仅针对集团外单位分析，对于集团内单位的往来款项不必进行帐龄分析，只填写明细余额即可。
单位：元 3年以上
比例
析，只填写明细余额即可。
应收账款账龄分析表制作
应收账款账龄分析做过财务的人都清楚我们的软件里也设有对应收账款的账龄的分析，但是好多企业都没有对账龄分析的好的财务软件，就像是我们的财务软件里面没有固定资产模块，可以自动计提折旧一样，我们只好用函数和一些别的方法来实现对于应收账款账龄的分析了，下面要用到的是数据透视表来做账龄分析表。
求和项:金额列标签行标签1-3061-90>121总计李四1500015000刘明60006000马二40004000王五30003000张海7000070000张三500000500000总计5100007300015000598000看到如此规范的一个账龄分析表，你可能只在财务软件里看到过，现实工作当中我利用了三个技巧，将此表做好。
①数据自动获取（OFFSET函数）②数据自动刷新（VBA）③数据组合（将账龄分好）第一步：单击任何一个单元格，点击“公式”-“定义名称”，如下图在引用位置输入offset函数，定义名称为“动态数据”，修改引用位置=offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),counta(Sheet1!$1:$1))这个引用位置公式是利用了offset的一个用法，“Sheet1!$A$1”表示起点第一个单元格；第一个0表示向下0行；第二个0表示向右0列；counta(Sheet1!$A:$A)表示有几行，选中第一列就可以，但是需要注意的是第一列不能有空单元格，否则会出现错误；counta(Sheet1!$1:$1)表示有几列，选中第一行就知道有几列了，同样不能有空单元格。
（注：OFFSET函数，有如下两种用法①OFFSET(起点，向下几行，向右几列)②OFFSET(起点，向下几行，向右几列，多少行，多少列)第一个用法是获取某个动态单元格，第二个用法是获取某个动态区域）第二步：要想实现数据的自动刷新就要借助VBA了,先要根据获取的动态数据插入一个数据透视表，然后再实现VBA的使用。
用EXCEL自动编制往来账款账龄分析表_汤芳芳
王凡德
三、 挂账付款表的设计与制作 （一） 挂账付款表的格式。应付账款账龄分析的依据是采 购挂账的时间和金额、 付款的时间和金额等几个因素， 因此此 表的设计应体现这些因素。挂账付款&’’&表的格式如表)。 表! "##"年!月挂账付款明细表 供应商名称 欠款余额 挂账金额 单位： 元 付款金额
) & %
!""# 年第 $ 期 )$
&’’(’)。前者为计算账龄分析的基础数据表格，每年一个文
等。后者 件， 每月一个工作表， 工作表名称分别为 ’&’)、 ’&’&、 为数据汇总和分析结果表。因为数据量大、 设置的公式多， 文 件容量大， 为操作方便， 最好每月一个文件。
财务管理信息化
!"#$%%&’()*$% +),$,*)$% -**.&,#),(
ห้องสมุดไป่ตู้
)?
（45<D!’， （45<E!+=7 （44<： ， ） !"* ’， "* 44<） 44<， 45<） <’天 （45<D!’， （45<E!+=7 （5<： ， <)? !"* ’， "* 44<） 5<，
（45<B+=7 （44<： （44<： ） ） ） A’天 "* 44<） D’0’’)， ’， 45<B+=7 44<） （45<D!’， （45<E!+=7 （C<： ， ’， "* 44<） C<， ［ ［/’’( 年 应 付 账 款 付 款 明 细 0123］ A)? !"* 8,=%， >> ， "#$%& （. （ ［ ’(’).！ ! + 4+ !)： ! + 5+ !(6’， 7489: ! + ;<， . /’’(年应付账款 ） ， ） 明细0123］ ’(’).！! + ;+ !)： ! + ;! +(6’， (） 该公式用了三个函数钳套， 分别是 "*、 和 "#+%,,-,、 其含义是， 如果 "#$%& 函 数 出 现 错 误 的 结 果 （即 "#$%&， 供应商在 “付款明细表” ， 让本单元格显示 ’(’) 中不存在） 为空， 否则运行 "#$%&函数。 在 “付款明 "#$%& 函数即本公式第三部分的含义是， 查找并引用对应单元格 细表 /’’(” ’(’) 工作表第二列中， 内容的值， 单元格内容在’(’)工作表中的第二列的值。用 这个公式的好处是，无论“应付账款账龄分析表”中的 在 “付款明细表 /’’(” “&& 公司” ’(’) 工作表列的第几行， 都可以取得其相对应的值。 将该公式纵向复制下去，可以得到本列其他行的公 式； 将该公式横向复制， 并将公式中的相应年月替换为单 元格所对应的年月， 可以得到其他各年月的值。 这种数据导入的技巧能帮我们避免大量数据重复录入 工作， 在实际工作中应用范围极广。 账龄分析区域账龄分析的计算公式如表<。 说明， 由于数据有限其中/?<年包括了大于<年的账龄段。 （)） 将以上公式复制到第三行以下的各行。至此， 一月份 即告完成。 的账龄分析表文件 “应付账款账龄分析’(’)” （/） 将 “应付账款账龄分析 ’(’)” 文件在同一文件簿中复 制 )) 份， 并分别命名为 “应付账款账龄分析 ’(’/” 、 …… ’()/。 、 （/） 步骤， 但需将公式中表示年月的数字替换 重复上述 （)） 为文件所属年月。以此类推， 就能得到其他年度的账龄分析 表。 总之， 提升财务管理信息化水平， 需要我们大家的共同 基础工作 努力。运用 %&9%@ 自动编制往来账款账龄分析表， 一经建立， 则能事半功倍。 （45<B+=7 （5<： （5<： ） ） ） F’天 "* 44<） D’0’’)， ’， 45<B+=7 44<） （45<D!’， （45<E!+=7 （&<： ， F)? !"* ’， "* 44<） &<， （45<B+=7 （C<： （C<： ） ） ） )/’天 "* 44<） D’0’’)， ’， 45<B+=7 44<） （ ， ， （ （ ： ） ， ， )/)? !"* 45<D!’ ’ "* 45<E!+=7 G< 44< G< （45<B+=7 （&<： （&<： ） ） ） )6’天 "* 44<） D’0’’)， ’， 45<B+=7 44<） （45<D!’， （45<E!+=7 （I<： ， )6)? !"* ’， "* 44<） I<， （45<B+=7 （G<： （G<： ） ） ） 44<） D’0’’)， ’， 45<B+=7 44<） )H’天 "* （45<D!’， （45<E!+=7 （J<： ， )H)? !"* ’， "* 44<） J<KL<K,<K+<K8<K=<， （45<B+=7 （I<： （I<： ） ） ） <A6天 "* 44<） D’0’’)， ’， 45<B+=7 44<） （ ， ， （ （ ： ） ， !"* 45< D!’ ’ "* 45< E!+=7 $< 44< $< K%< K*< KM< K
excel 应收账款账龄分析与坏账提取
6.2.2 创建坏账统计表格并统计坏账金额所占比例
输入公式计算第一个 客户的坏账金额总值， 然后将公式向下复制， 计算其他客户的坏账 金额总值
利用“求和”按钮 计算坏账金额合计 值
制作的统 计表格
输入公式计算第一个客户 坏账金额所占比例值，然 后复制公式计算所有客户 坏账金额所占比例值
6.2.3 创建图表分析应提取坏账
“应收账款明细”工作表标签， 在表格的右侧添加“坏账提 取比例”和“坏账金额”列， 并设置其数字格式分别为 “百分比”和“货币”，然 后输入公式计算第一位客户 在应收账款账龄的坏账提取 比例
复制公式计算出其他 客户在应收账款账龄 的坏账提取比例
输入公式计算第 一位客户的应提 取坏账金额 复制公式计算 其他客户的应 提取坏账金额
制作的应 提取坏账 分布饼图
本章小结
通过本章学习，读者应掌握如下知识。 掌握应收账款所包含的项目及如何制作应收账 款明细和坏账提取表格的方法。 掌握应收账款账龄的概念及使用Excel函数统计 不同账龄段的应收账款金额及应提取坏账金额， 以便对账龄和坏账等进行分析操作的方法。 掌握利用图表对应收账款和应提取坏账金额进 行分析、比较操作的方法。 重点掌握OFFSET函数和数组公式的应用。
6.2.1 设置公式计算坏账提取比例 和应提取坏账金额
在本例中，假设企业根据应收账款的账龄 进行坏账的提取，即在信用期内的不提取坏账， 超信用期1~30天的按2%提取，超过信用期 31~90天的按6%提取，超过信用期91~180 天的按9%提取，超过信用期181~360天的按 14%提取，超过信用期360天以后的按25%提 取坏账。
6.1.1 创建应收账款明细表并计算
新建“客户代码”工 作表，并在其中输入 客户代码和客户名称 数据并格式化表格
应收账款账龄动态分析图表制作
应收账款账龄动态分析图表制作下面对每项操作进行细节演示。
效果图基础表一、账龄动态变化设置到期日栏录入公式“开始日+账期”，在账龄栏录入公式“today （）-开始日”，在超期天数栏录入公式“today（）-到期日”，录入完毕后下拉公式填充下面单元格。
填充完毕后，F—H列每天都会自动更新。
二、超期预警设置1、使用条件格式，超期一栏中大于0的以大红色填充显示，操作路径“条件格式——突出显示单元格规则——大于”，弹出“大于”窗口。
2、窗口上第一空格处金额改为0，后面设置为为默认显示颜色，此例中用大红色填充进行预警，点击设置为中的下拉窗口，点击“自定义格式”，弹出“设置单元格格式”窗口。
3、在“设置单元格格式”窗口填充处，选择大红色后点击确定。
回到“大于”窗口，再点击确定即可。
三、账龄汇总表制作1、按“插入——数据透视表——数据透视表”路径插入数据透视表，弹出“创建数据透视表”窗口后点击确定。
2、上步操作中选择放置数据透视表的位置默认为新工作表，因此透视表在一张新的sheet表中显示。
3、在右边窗口中，将账龄拖至行标签处，将金额拖至数值处，拖动两次（拖两次的目的是数据区域一栏显示金额，一栏显示金额占比）。
4、将鼠标放至行标签处，右键选择“创建组”，弹出组合窗口。
5、将窗口中数值分别改为1、120、30后点击确定。
6、将鼠标放至单元格“求和项：金额2”，右键选择“值字段设置”，弹出“值字段设置”窗口。
7、将自定义名称处“金额2”改为“占比”，将值显示方式改为“列汇总的百分比”，点击确定，完成设置，以后右键点击刷新即可更新。
四、分析图表制作1、插入数据透视表，操作路径“选项——数据透视图”，弹出“插入图表”窗口。
2、选择柱形图后，点击确定。
3、点击选择“占比”，然后右键选择“设置数据系列格式”，弹出“设置数据系列格式”窗口后，点击选择“次坐标轴”后点击关闭。
4、点击选择图表区域中占比图，右键选择“更改系列图表类型”，弹出“更改图表类型”窗口，选择“折线图”后点击确定，完成基本图表制作。
财务人员Excel 应用教程：应收应付款表格制作
财务人员Excel 应用教程：应收应付款表格制作第1节、到期示意表的提醒功能尽管非常用心，非常在意，你也不可能清楚记得每一件事。
但电脑还可以自动对重要的事情进行提示。
比如，到期应付款项，可以自动填充颜色来提醒财务人员，更方便查看。
就上面例子，按照给定日期，自动对将要到期的汇票项填充颜色以提醒使用者，更醒目更直接。
步骤01 调用上例工作表打开上例工作表，将文件另存为新文件，删除不用的F列，同时删除G5、G6单元格内容，并去掉G6的填充色。
步骤02 使用条件格式选中A2:E2单元格区域，点击“开始”→“样式”→“条件格式”→新建规则，填出“新建格式规则”对话框。
在“选择规则类型”中选择“使用公式确定要设置格式的单元格”，然后在“编辑规则说明”的编辑框中输入公式：“=($D2-$G$2<=30)*($D2-$G$2>0)”。
步骤03 设置单元格格式公式输入完成后，点击下方的“格式”按钮，弹出“设置单元格格式”对话框，在“填充”选项卡中，选择一个填充颜色，然后按“确定”按钮返回“条件格式”对话框。
再次按确定按钮，完成条件格式的设置。
步骤04 复制条件格式选中A2:E2单元格区域，点击工具栏中的“格式刷”按钮，当光标改变形状，变成空心十字加格式刷的形状时，按住Shift 键不放单击E20单元格，现在，格式全部复制到A1:E20单元格区域了。
步骤05 高亮区分的到期提示项现在，条件格式设置完成了，表中30天内到期的记录项就会自动填充橘黄色显示出来，非常醒目。
第2节、承兑汇票到期示意图在公司财务工作中，长期会使用承兑汇票，承兑汇票到期后需要用现金偿还。
这张到期示意图，可以清楚显示到期偿还的金额，还有到规定日期还剩余多少天数。
相关人员从这张表可以随时掌握承兑汇票的现状。
步骤01 新建表格并录入数据启动Excel2007新建一个工作簿，将Sheet1改名为“到期示意表”。
在第一行单元格输入标题，并完成A2:E20单元格区域的数据录入。
使用Excel计算应收账款账龄
使用Excel计算应收账款账龄
走过路过不要错过
问题描述
如下图所示，A1:D9为公司对各客户的应收账款。
要求根据G1:G7的应收账款账龄分类，计算每项应收账款的账龄。
解决方法
在D2单元格输入公式：
=VLOOKUP(TODAY()-B2,$F$2:$G$7,2,TRUE)
拖动填充柄，将公式向下复制到D9单元格。
公式解析：
（1）TODAY()用于返回当前日期，即“2022/4/22”。
TODAY()返回的日期是动态的，随着时间的流逝，TODAY()返回的日期也会自
动更新。
当明天再打开工作簿时，TODAY返回的日期就是“2022/4/23”。
（2）TODAY()-B2用于计算开出发票后，过了多少天。
如下图所示，在D2单元输入公式“=TODAY()-B2”，得到的就是应收账款账龄的具体天数。
（3）使用VLOOKUP函数将应收账款账龄分类。
VLOOKUP(TODAY()-B2,$F$2:$G$7,2,TRUE)，用于在F2:G7单元格区域查找“TODAY()-B2”。
VLOOKUP函数的最后一个参数“TRUE”，代表近似查询。
当VLOOKUP在F2:G7查找不到“TODAY()-B2”时，会找到小于查找值的最大值。
以D2单元格的账龄天数“1080”为例，VLOOKUP函数无法在F2:F7查找到该值，于是会找到小于“1080”的最大值，也就是”361“，并返回对应的账龄分类”1年以上“。
在看。
《Excel在财务管理中的应用》第四章——应收账款管理
(5)机会成本。机会成本,是指利用一定资源获得某种收入时所放弃的另一种收入。在实行本方案 时,失去所放弃方案的潜在收益,是实行本方案的一种代价,称为本方案的机会成本。机会成本,实际上 不是一种支出和费用,而是失去的收益,与通常意义上的成本概念不同,是辩证的概念,这种收益不是实 际的而是潜在的。例如,现有A和B两种方案,若选择A方案可获利10万元,则这10万元的潜在收益就是 选择B方案的机会成本。
第四章 应收账款管理
4.1 应收账款统计表及账龄分析表
4.1.1 创建应收账款统计表
1.编制应收账款基本信息表 企业进行应收账款管理的基础是客户及赊销额的基本信息,因此,首先需要根据客户及赊销信息,将 应收账款基本信息录到应收账款基本信息表中。 企业的应收账款基本信息主要包括客户代码、客户名称、业务员、应收金额、预收金额、实收 金额,灵活进行内容的设计和编制,具体操作 步骤如下。
图4-8
4.2 应收账款对账单
4.2.1 创建应收账款对账单
发送应收账款对账单,是企业与客户之间重要的活动之一。对于财务人员而言,准确、快速地完 成应收账款对账单的制作,并发送至相应的客户邮箱中,是一项既要求准确率、又要求效率的工作。 Excel不仅能够快速完成对账单的制作,还能够准确发送邮件,是财务人员必须掌握的工具之一。
图4-5
4.1.1 创建应收账款统计表
5.计算逾期账龄及逾期金额 计算逾期账龄的基本思路是:判断该项应收账款是否已经逾期,如果已经逾期,则显示计算出的逾期 时间,否则不显示。
判断是否逾期可以使用IF函数,逾期时间的计算可以使用DAYS360函数。DAYS360函数是按照一 年360天的算法返回两个给定日期的相差天数,其语法为 DAYS360(start_date,end_date,[method])。 计算逾期金额可以使用IF与DAYS360嵌套函数来实现,逾期金额可用期末余额表示。操作步骤如下。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
制作简易“应收账款账龄分析表”
1、先填制“应收账款明细表”（以流水账的形式填），如图1
（图1）
2、再作一个“账龄区间表”，如图2
（图2）3、在“应收账款明细表”中设置以下公式：如图3
（图3）
4、公式设置完后，得到以下结果，如图4
（图4）
5、到此，账龄分析表的基础数据基本做好了，但是，表中只做了24行，如果以后还要接着往下登记，希望能在分析表
中能自动添加进去，要怎么办呢？有一个简单的办法是，将明细“插入表格”功能，这样往下增加的记录就会自动添加到表格区域中，如图5
（图5）
选定明细表，点“插入”－“表”，
如图6
（图6）
6、勾选“表包含标题”确定。
如图7，形成一张右下角带有“小三角”的表格区。
这样，只要在明细下方添加数据，表格就会自动扩充区域。
（图7）7、选定图7的表区域，插入“数据透视表”，如图8
（图8）
得到图9效果：
（图9）图9就是简易的账龄分析表了，这时可以测试一下，增加明细后，刷新一下分析表，新增的数据也就自动汇总到分析表中去了，如图10
(图10)新增K公司应收账款60万，到期2015－6－10，账龄为0~30天，再将账龄分析表刷新一下，就自动添加了这条记录了。
如图11。