EXCEL技巧：制作简易“应收账款账龄分析表”

应收账款账龄分析做过财务的人都清楚我们的软件里也设有对应收账款的账龄的分析，但是好多企业都没有对账龄分析的好的财务软件，就像是我们的财务软件里面没有固定资产模块，可以自动计提折旧一样，我们只好用函数和一些别的方法来实现对于应收账款账龄的分析了，下面要用到的是数据透视表来做账龄分析表。

求和项:金额列标签行标签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的使用。

应收账款账龄动态分析图表制作下面对每项操作进行细节演示。

效果图基础表一、账龄动态变化设置到期日栏录入公式“开始日+账期”，在账龄栏录入公式“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 应用教程：应收应付款表格制作第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计算应收账款账龄
走过路过不要错过
问题描述
如下图所示，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作为分析工具的相对比较实用、比较有效、更加易于财务人员掌握的分析方法，其基本思路是日常在应收账款发生或回款时建立Excel数据清单进行详细记录，在编制账龄分析表前进行业务的核销，随后建立数据透视表编制账龄分析表，这样的表可以被灵活地进行重新设置，以满足不同的账龄分析目标。

【关键词】应收账款；账龄分析；Excel；数据透视表[文献标识码]A[文章编号]1673-0194(2006)10-0037-04应收账款是指企业因销售商品、产品或提供劳务等原因，应向客户或接受劳务的客户收取的款项或代垫的运杂费等。

应收账款发生后，企业应采取各种措施，尽量争取按期收回款项，否则会因拖欠时间过长而发生坏账，使企业蒙受损失。

其中一个重要的措施就是及时地实施应收账款账龄分析，编制账龄分析表。

Excel 是一种实施应收账款账龄分析的很好的工具，应用Excel编制账龄分析表相对手工编制账龄分析表更加快捷、精确，账龄分析的结果更便于企业对其应收账款实施监督，以及时地收回款项，降低企业经营风险。

应用Excel实施账龄分析，编制应收账款账龄分析表的方法有多种，本文从一个新的角度，探讨如何综合应用Excel的多种功能，建立数据透视表来编制应收账款账龄分析表的方法，这种方法相对其他应用Excel实施账龄分析的方法效率更高、更加实用。

一、应用Excel实施应收账款账龄分析的模式的确定定期对应收账款进行整理，分析企业应收账款在各个账龄时间段的金额、百分比等分布情况，编制应收账款账龄分析表，这就是应收账款的账龄分析。

表1是某企业2005年12月31日的应收账款账龄分析表。

通过账龄分析可以了解到企业应收账款的分布情况，可以运用最适当的收款政策收回款项，此外还可以预测有多少欠款会因拖欠时间太久而可能成为坏账。

Excel 制作应收账款分析表应收账款主要是指在生产经营活动中，债权人因提供商品或者服务获得的要求债务人付款的权利。

例如企业应当支付的水费、电费等，就属于水务公司和电力公司的应收账款。

下面就来介绍“应收账款分析表”和“应收账款分析图”的制作方法。

1．实验目的●插入行和列●使用公式和函数●创建图表●设置图表格式●添加趋势线2．操作步骤：（1）新建Excel工作簿，将Sheet1工作表命名为“应收账款”，并输入相关的数据，如图11-26所示。

输入图11-2 输入数据（2）选择【收款期】列，单击【插入】下拉按钮，执行【插入工作表列】命令，如图11-27所示。

图11-2Excel 插入列。

（3）在新插入的列中，输入“已收款金额”及相应数据。

然后，按照相同的方法，插入两外两列，并输入“未收款金额”以及“到期日”的相关数据，如图11-28所示。

图11-2Excel 插入数据（4）右击第二行行号，执行【插入】命令，如图11-29所示。

然后，在新插入的行中输入相应信息。

图11-29 插入行（5）选择I4单元格，在【编辑栏】中输入“=IF(H4>$I$2,"否","是")”公式，并向下填充至I11单元格区域，如图11-30所示。

插入列 执行选择执行 输入数据 输入公式效果显示图11-30 填充公式 提 示根据对指定的条件计算结果为 TRUE 或 FALSE ，返回不同的结果。

其语法为：IF(logical_test,value_if_true,value_if_false)。

其中，Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。

Value_if_true 是 logical_test 为 TRUE 时返回的值。

Value_if_false 是logical_test 为 FALSE 时返回的值。

（6）选择J4单元格，输入“=IF($I$2-$H4<0,$D4-$E4,0)”公式，并向下填充至J11单元格，如图11-31所示。

Excel教程-应收应付账款明细表模板，使用技巧小编教你怎么操作，应收应付账款明细表是会计行业规范的工作表格，应收系统和应付系统都已经启用的情况下，才可以将债权和债务进行核销。

应收应付账款明细表Excel模板。

应收应付账款明细表模板使用技巧应付应收账款的四大模块：1.发票管理，将发票输入之后，可以验证发票上所列物料的入库情况，核对采购订单物料，计算采购单和发票的差异，查看指定发票的所有采购订单的入库情况，列出指定发票的有关支票付出情况和指定供应商的所有发票和发票调整情况。

2.供应商管理，提供每个提供物料的供应商信息。

如使用币种、付款条件、付款方式、付款银行、信用状态、联系人、地址等。

此外，还有各类交易信息。

3.支票管理，可以处理多个付款银行与多种付款方式，能够进行支票验证和重新号，将开出支票与银行核对，查询指定银行开出的支票，作废支票和打印支票。

4.账龄分析，可以根据指定的过期天数和未来天数计算账龄，也可以按照账龄列出应付款的余额。

使用该函数时，需要注意以下几点：1、第4参数一般用0(或FASLE)以精确匹配方式进行查找。

2、第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。

3、如果查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A。

4、查找值必须位于查询区域中的第一列。

3：多条件查找要求查询部门为生产,并且岗位为部长的姓名。

公式为：=LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9)LOOKUP函数多条件查询写法为：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)。

制作简易“应收账款账龄分析表”
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。

Excel账龄分析，Vlookup+透视表（超级实⽤）
周末惊喜：
部落窝教育微课堂，就在今天，所有课程买⼀送⼀。

咨询微信：529828270，长按下⾯⼆维码直达微课堂：
账龄是指公司尚未收回的应收账款的时间长度。

账龄是在分析应收账款时最为重要的信息，所
有账龄在合理周转天数以上的应收账款都会给公司运营造成负⾯影响，账龄越⾼，资⾦效率越
低，发⽣坏账的风险越⼤，财务成本越⾼。

下⾯为应收账款表，现在领导要求我们统计各账龄级别的⾦额。

最终的效果：
解决思路：
1、⾸先解决截⽌⽬前所过的天数：C2单元格公式为：=TODAY()-A2，当前⽇期减去应收⽇
期。

2、账龄级别统计：根据A13：B17区域的账龄参数表，在D2输⼊：
=VLOOKUP(C2,$A$14:$B$17,2,1)，通过vlookup函数的模糊查找，来判断各账⽬的账龄。

3、选中数据源区域，插⼊透视表。

将账龄级别、应收账款⾦额拖动分别拖动到⾏字段、值字
段，就能快速分析出各账龄的总额。

用EXCEL自动编制往来账款账龄分析表
汤芳芳;王凡德
【期刊名称】《冶金财会》
【年(卷),期】2005(024)001
【摘要】当今知识经济时代，计算机技术、网络技术和现代信息技术融入财务管理及会计核算，有利于提高核算能力，使企业在瞬息万变的市场竞争环境中获得最大的经济效益。

经过实践，用EXCEL自动编制往来账款账龄分析表，是企业实现往来账款管理的主要手段。

便于企业了解账款性质，合理负债，减少资金占用，及时回笼货币，加快资金周转，提高资金使用效率。

同时也减轻了会计人员的工作压力，提高了核算质量。

这里将举实例作一介绍。

【总页数】2页(P31-32)
【作者】汤芳芳;王凡德
【作者单位】中国铝业股份有限公司河南分公司财务部;中国铝业股份有限公司河南分公司财务部
【正文语种】中文
【中图分类】F2
【相关文献】
1.用 Excel 97 编制等级资料的 Ridit 分析表格 [J], 潘定举
2.用EXCEL表编制应收账款的账龄分析表和坏账准备计提的会计与税务处理 [J], 郑丽娜
3.如何运用EXCEL自动编制应收账款账龄分析表 [J], 瞿四美
4.应收账款账龄分析表编制方法及应用 [J], 傅秉潇
5.应收账款账龄分析表编制方法及应用 [J], 傅秉潇
因版权原因，仅展示原文概要，查看原文内容请购买。