《Excel财务日常数据与实例讲解》_从理论到实践的一大步_5.2.2 保存为合同信息表和收款信息表

【摘要】 本书摘自《Excel财务日常数据与实例讲解》一书中第5章，第2.2节，作者是韩小良、贾春雷。

5.2.2 数据分别保存为合同信息表和收款信息表：数据量不大

案例5-3

上面的例子还是比较简单的。如果手头是这样的表单，如图5-25所示， 一个表格是合 同信息表， 一个表格是收款信息表，那么如何编制每个客户的应收账款账龄分析表呢?注意 付款是分批次的，每次付款的金额和时间也是不一样的。应收账款账龄分析表结构如图5-26所示。

这个问题的关键是要对每个合同进行核算，计算每个合同的已收金额和未收金额，以及 未收金额的逾期天数。在原始表单中设计3个辅助列，分别计算每个合同的已收金额、未收金额和逾期天数， 如图5-27所示。各个单元格的公式如下。

单元格 G2:

=SUMIF (收款信息!A:A,C2,收款信息!E:E)

单元格 H2:

=D2-G2

单元格 I2:

=IF(H2=0,"",TODAY(-F2)

这样，就可以直接根据合同明细表的这些列计算每个客户的应收账款账龄分析表，结果 如图5-28所示。各个单元格的公式如下。

单元格 C4:

=SUMIF (合同信息!A:A,B4,合同信息!D:D)

单元格 D4:

=SUMIF (合同信息!A:A,B4,合同信息!G:G)

单元格 E4:

=SUMIF (合同信息!A:A,B4,合同信息!H:H)

单元格 F4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,"<=0")

单元格 G4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,">=1",合同信息!I:I,"<=30") 单元格 H4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,">=31",合同信息!I:I,"<=60") 单元格I4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,">=61",合同信息!I:I,"<=90") 单元格 J4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,">=91",合同信息!I:I,"<=180") 单元格 K4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,">=181",合同信息!I:I,"<=365")

单元格L4:

=SUMIFS (合同信息!H:H,合同信息!A:A,B4,合同信息!I:I,">=366")

5.2.3 数据分别保存为合同信息表和收款信息表：数据量很大

案例5-4

上面的解决方案是使用函数公式。假若合同信息表有上千行甚至数万行数据，收款信息 表也会有数千行甚至数万行数据，此时若使用函数做，就比较烦琐了，建议使用更高效的Power Query 工具来解决。下面是具体的步骤。

① 选择“数据” → “获取数据” → “自文件” → “从工作簿”命令，从文件夹里选 择工作簿，打开“导航器”对话框，勾选“选择多项”和两个表，如图5-29所示。

② 单击“编辑”按钮，打开 “Power Query编辑器”窗口，如图5-30所示。

③ 在左侧选择两个表，将默认的步骤“更改的类型”删除，并再将日期列的数据格 式设置为“日期”,将金额列的数据类型设置为“小数”,如图5-31所示。

④ 在左侧选中“合同信息”表，选择“开始”→ “合并查询”→ “将查询合并为新查询” 命令，打开“合并”对话框，设置合并选项。

(1)上下两个表分别选择“合同信息”和“收款信息”。

(2)分别选择两个表的“合同号”列。

(3)在底部的“联接种类”中选择“左外部(第一个中的所有行，第二个中的匹配行)”。 设置合并选项，如图5-32所示。

⑤ 单击“确定”按钮，得到一个新查询 “Mergel”, 如图5-33所示。

⑥ 单击最右边的“收款信息"列的展开按钮，打开筛选窗格，勾选“收款日期”和“收 款金额",取消其他项目的选择，如图5-34所示。

⑦ 单击“确定”按钮，得到如图5-35所示的查询表。

⑧ 选中“收款金额”列，选择“转换”→ “替换值”命令，打开“替换值”对话框，

在“要查找的值”输入框中输入null, 在“替换为”输入框中输入0,如图5-36所示。

⑨ 单击“添加列” → “自定义列”按钮，打开“自定义列”对话框，如图5-38所示。

输入新列名“未收金额”,自定义列公式如下。

=[合同金额]- [收款金额]

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。