微精选

3 行公式秒算 500 人 2 年预收、欠款！-销售、回款比对原来可以这么爽

文/孔述

最近上级主管部门要求做一项数据统计分析：把过去两年所有业务员每个月的销售额与回款额进行比对分析，并计算当月预收款及欠款，以验证业务员在一定期内，回款额与销售额是动态平衡的关系，同时作为考核业绩的依据。

这种数据分析是制造业、物流或商贸行业的常规性工作。

具体来说：100多个业务员的姓名、长达两年（24个月）的数据，以及四张（2023及2024的回款表和销售表、顺序完全打乱）明细表，需要对其进行统计比对分析。

正常情况下，很多人、立即想到做成这样一张表：按照姓名查找对应月份的各类数据。

其中：2024报 是指收入回款报帐、2024销 是指实际销售收入）。

思路决定出路。 录单小姐姐接到任务后，立刻开始手工复制粘贴，然后仔细核对。

然而，没多久，她就发现工作难以继续推进。因为不仅人员多（两年间有人员辞职与新增），且顺序杂乱无序，同时查看两张表格时，不是找人费时间，就是看错行，繁杂的数据令人眼花缭乱，还让人手忙脚乱。最后不得已，工作无法再进行下去了。
其实，在函数时代，找数字，不如让数字自动对号入座！

而找数据的工作交给函数来完成，不仅解放双手 ，提高效率，更重要的是准确率即找作质量大大提升。

其实，上述工作仅需“3行公式”，就能让Excel自动完成2023 – 2024年所有预收、欠款的对号入座，整个过程不过十分钟，而且零差错，即使是新人也能直接套用——

一张表是所有业务员每月收款表，另一张表是所有业务员每个月销量表，如何根据这两张表生成一张根据业务员每月收款销量对照表。

一、思路

一句话：概括就是“回款－销售＝差额”，正数为预收，负数为欠款。先统一人员口径，再让公式自行找数、自动排雷。

1、这是业务员2024年报账收入表，该表记录了业务员当月实际收回的货款（费用另行报销，不得坐支，不在此表列示）

2、这是业务员2024年销售发货表。其中，有调（离）职人员、新增人员等变动情况。

3、制作汇总2023年、2024年每个业务员每月的预收、欠款情况。

依据上述两表（2023年销售、回款报账表略），提取每个业务员每月对应的回款额与销售额，通过回款额减去销售额计算差额。差额为正即为预收款，差额为负即为应收款（欠款），即：

回款额 – 销售额>0为预收款，

回款额 – 销售额<0为欠款。

同时需对两年数据进行比较分析。

前已述及，由于业务员人数众多，两表登记顺序不一致，且手工摘抄需逐一从两表中核对对应数值，不仅繁琐，还极易出错。若采用函数处理，不仅能大幅减轻工作量，还能显著提升效率与准确性。

下表即为预收、欠款明细。这项看似工作量浩大的任务，只需几个公式就能完成，且大多为自动生成。

下面看看是如何生成的？

二、3步落地

1. 基础数据：建“人员字典”
   在B1单元格输入：=UNIQUE(‘2024报账’!A:A)，一秒钟就能拿到不重复的名单，后续所

有查询都用它作为“身份证”。

因为两表业务顺序不一致，需要建立共用基础数据以统一查询口径。在B1单元格输入公式=UNIQUE(‘2024报账’!A:A)，提取报账表中的人员名单。

右拉、下拉，数据瞬间呈现，差额行留空。

2）表头设计：三行模板自动生成
每个业务员需用三行进行比对，姓名需重复三次，月份为1 – 12月。利用这一特点：

在A3单元格输入公式=ROUNDUP(ROW(A1)/3,0)，生成连续重复三次的序号；

在B3单元格输入公式=INDEX(基础数据!$B$2:$B$39,MATCH(A3,基础数据!$A$2:$A$39,))，依据序号匹配姓名；

在C3单元格输入公式=SWITCH(COUNTIF($B$3:B3,B3),1,”2024报”,2,”2024销”,3,”差额”)，设置不同的表名标识；

在D2单元格输入公式=SEQUENCE(1,12,1,1)，生成1 – 12月的月份序列；

在D3单元格输入公式=IF($C3=”差

额,””,INDEX(INDIRECT($C3&”!A1:M39″),MATCH($B3,INDIRECT($C3&”!A1:A39″),),MATCH(D$2,IN

DIRECT($C3&”!A1:M1″),)))，选中D3单元格向右、向下拖动，即可自动提取所有相关数据，仅“空出差额行”。

3)一键算差额 + 红绿灯
选中间隔空行，Ctrl + Enter

批量录入：=IFERROR(D3 – D4,0)
4)条件格式：差额大于0时显示绿色（代表预收），小于0时显示红色（代表欠款），零值无色。
2023年同理再操作一遍，两张结果透视即可做同比。

最后检查、验证表格数据的准确性，经验证无误。

针对表格中的错误值（人员变动无数据，故取值出错），采用IFERROR()函数进行容错处理，即大功告成。

综上，整个过程并未使用复杂公式，主要是对基本函数的灵活运用：

1、通过生成连续重复序号，统计姓名出现次数以自动生成“2024报”“2024销”等表名，为后续INDIRECT函数的引用创造条件；

2、通过条件函数空出差额行，最后选择差额行进行抵减计算，并通过条件格式高亮显示结果。

这种方法不仅高效准确，还彻底避免了手工重复枯燥的工作。

三、效果

原来3人2天的工作量，现在1人3分钟即可完成；

新增/离职人员自动匹配适应，无需修改公式；

差错率从“肉眼抽查”直接降至0。

把模板另存为“销售回款秒算器”，明年只需替换基础表，刷新即可。
复杂的是业务，简单的是公式——让Excel做苦力，你把时间拿去签更大的单！