如何用Excel实现应收账款自动清账如何用Excel实现应收账款自动清

前言应收账款的核销是件费时费力的事，即使是使用财务软件也必须十分小心，一不留神就会张冠李戴。本文将针对物业行业周期性收费的特征，介绍如何利用Excel的Power Query功能来实现应收账款的自动清账。
思路根据收费性质的不同，设置不同的清账策略

水电物业费（先开票后收费）具有周期性，先出账单后收费，出具账单后根据账单金额开票，并将开票金额及所属会计期间录入Excel表格，收到客户的水电物业费时，判断费用的所属会计期间，将负数金额计入该期间，若该客户在该期间的水电物业费汇总金额为零则标记自动清账。
其他款项（先收费后开票）收到款项时即开具发票，一般情况下当月清账，但遇到月底关账，需要跨越开票，这种情况下，采用的方法是将该客户同一收费项目下收到的所有款项和所有开票金额汇总，若合计数为零则全部标记自动清账，若不为零则需要配合手动清账。

数据录入每一条记录需要包含的字段有商铺名称、收费项目、会计期间、账单期间、金额、入账日期、凭证类型。

会计期间是指应收账款所属期间（一般为开票月份），会计期间有可能晚于账单期间（先出账单下月开票）。
金额正数代表开票，负数代表收到款项（可以是银收、现收，也可以是押金抵扣等）
凭证类型根据需要可以设置期初、银收、现收、转账等

文章图片
image.png
添加一列以年份月份表示的会计期间

=IF(ISBLANK([@会计期间]),"",TEXT([@会计期间],"yyyymm"))

创建辅助表

收费项目分类表
按照收费性质将收费项目分为两类，编号为1的收费项目属于先收费后开票，编号为2的收费项目属于先开票后收费。

文章图片
image.png
辅助表1（已清账项目_不含水电物业）

点击“自表格/区域”

文章图片
image.png
筛选出先收费后开票的项目

文章图片
image.png
按收费项目和商铺名称分类汇总

文章图片
image.png
汇总金额四舍五入去掉尾差

文章图片
image.png
筛选出汇总金额为0记录

文章图片
image.png
删除总金额列后点击关闭并上载

文章图片
image.png
上载后生成辅助表1

文章图片
image.png

辅助表2（已清账项目_水电物业费）

筛选出先开票后收费项目

文章图片
image.png
按照收费项目、商铺名称和会计期间（年月）分类汇总

文章图片
image.png
汇总金额四舍五入去掉尾差

文章图片
image.png
筛选出汇总金额为0记录

文章图片
image.png
【如何用Excel实现应收账款自动清账】删除总金额列后点击关闭并上载

文章图片
image.png
上载后生成辅助表2

文章图片
image.png

添加清账标志

自动清账

=CHOOSE(VLOOKUP([@收费项目],收费项目分类,2,0), //用choose函数选择不同处理方法 COUNTIFS(已清账项目_不含水电物业[收费项目],[@收费项目],已清账项目_不含水电物业[商铺名称],[@商铺名称]), //商铺名称和收费项目出现在已清账项目清单中则标记1 COUNTIFS(已清账项目_水电物业费[收费项目],[@收费项目],已清账项目_水电物业费[商铺名称],[@商铺名称], 已清账项目_水电物业费[会计期间（年月）],[@会计期间（年月）]))//商铺名称、收费项目和会计期间均出现在已清账项目清单中则标记1