如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(1)

编按:本篇讲解用数据透视表制作账龄区间不固定的账龄分析表。

接我们上次的话题,要实现这种不规则的账龄区间,透视表的自动分组是做不到的:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(2)

怎么办?自动分组不行那就自定义分组呗。下面请继续跟着老菜鸟折腾透视表吧……

还是这个数据源:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(3)

第1步:首先做出一个透视表的雏形

操作过程动态演示如下:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(4)

步骤分解:

(1)插入数据透视表。

(2)将账龄、客户简称两个字段放在行标签。这里注意账龄要放在前面。

(3)再把应收账款余额放在数据区域。

(4)在“设计”功能卡的“报表布局”中选择“以表格形式显示”,将透视表按表格显示。

(5)在汇总单元格上右击鼠标,单击“分类汇总“账龄””命令取消汇总。注意确保账龄按照升序排列。

第2步:自定义账龄分区

接下来就是自定义分组的过程了,我们以50天以内这个区间为例进行演示:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(5)

步骤分解:

(1)选择50天以内的账龄数据,右击鼠标,选择“组合”命令,会自动多出来一个“账龄2”字段,并且出现一个“数据组1”的行标签:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(6)

(2)把“数据组1”改成“50天以内”,就完成了第一个区间的分组。

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(7)

(3)接下来要对50-90天的数据进行分组。首先选中“数据组1汇总”单元格,右击鼠标选择“分类汇总“账龄2””取消汇总。然后按照50天以内的操作方法一样进行组合。

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(8)

(4)完成前两个分组后,最后两个分组的方法都是一样的,就不在赘述了。完成后效果是这样的:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(9)

这与我们实际需要的还是有点不同,别急,调整一下布局就好了。

第3步:调整布局

方法也简单;

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(10)

步骤分解:

(1)单击“分析”功能卡“显示”选项组中的“字段列表”,显示数据透视表字段窗口。(也可以在透视表单元格上右击鼠标选择“显示字段列表”命令显示数据透视表字段窗口。)

(2)将账龄字段从行标签去掉。

(3)再把账龄2字段拖到列标签就OK了。

数据行数较多时的完善方法

以上是在透视表中自定义分组的一个方法。可能有些朋友会发现一个问题,本例中的数据比较少,如果区间内涉及到的行数比较多,账龄2数据选择会比较麻烦。

这的确是个问题,不过并不是没办法解决,我们以90-120这个区间来进行说明。

(1)和之前一样,先把透视表处理成这个样子:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(11)

(2)接下来在账龄那里点击下拉选项,依次选择“标签筛选”-“介于”:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(12)

会看到这样的设置界面:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(13)

按我们的需要填入具体的数字:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(14)

(3)确定后可以一次筛选出指定区间的数据,然后选择对应的区域,右键组合:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(15)

这样就完成了一个区间的分组操作:

如何在excel中用财务函数做账龄(如何用透视表做好账龄分析表)(16)

(4)其他区间的选择方法都是一样的。

注意:每次筛选都是在“账龄”字段处进行的,而不是“账龄2”。当全部设置完成后,清除账龄中的筛选设置,然后重命名数据组,对字段布局进行调整,完成整个操作。

小结

相比透视表的自动分组来说,自定义分组略显复杂,但是相比辅助列写公式来说,还是比较容易上手的,并且自定义分组是完全可以按照自己的实际需求来自由组合,用处非常广泛。

对于大多数伙伴来说,要想精通公式函数,活学活用短时间内是比较困难的,因此要做数据分析、统计汇总工作,透视表不能不会。今后有机会我们将继续分享透视表的其他妙用,关于账龄统计的讨论就告一段落了。

****部落窝教育-excel透视表账龄分析****

原创:老菜鸟/部落窝教育(未经同意,请勿转载)

更多教程:部落窝教育(www.itblw.com)

exceljiaocheng

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页