大家好，我是虎课网的讲师，我叫郭辉 ，主讲财务会计，玩转Excel类的课程 ，目前呢是现任的某公司财务总监 ，拥有二十多年的财务工作经验和十几年的Excel学习经历 ，下面我们要讲解的这个案例呢是一个销售及应收款管理的案例 ，我们先来看一下它的背景介绍 ，很多企业对软件的数据统计结果呢不怎么满意，经常呢要把数据从软件里导出来 ，然后自己呢制作一些具有个性化需求的统计啊、汇总啊、查询啊等等 ，会做一些这样的表 ，以应对公司的各种统计需求 ，本案例呢就是利用了出库数据和收款数据 ，制作了一个分客户查询和月度汇总查询的这样一个模版 ，既可以实现查询的目的 ，也可以用于生成对账单，其实呢还有一些汇总的功能 ，也可以用于生成对账单，其实呢还有一些汇总的功能 ，那么本课程用到的重点函数呢是 ，VLOOKUP和SUMPRODUCT ，适用人群呢财务统计，包括仓库，有的单位其实仓库管理员的这个功能很大很大 ，其实就是叫这么个名 ，但是呢每个单位像这种一般职员它的这种功能呢 ，会根据不同的单位会赋予不同的职能吧 ，好，那下面呢我们就来进入这个案例 ，这是个基础资料表，这个呢不是很重点，重点的还是在后边，我们一会会说到这个 ，先看这个台账，这个台账呢 ，在介绍之前呢我呢跟大家呢先说一说 ，在实际工作当中，我们在用的时候呢会出的一些问题啊 ，

我们平时在登记这些数据的时候呢，习惯呢把一类数据呢登记在一个表 ，比方说，这个发货记录，是发给哪个客户了，什么时间呢 ，是发的什么产品呢，什么规格，多少数量等等，价格，金额然后样登记 ，回头呢再找一个表来登记这个 ，什么时候什么时间，收的哪个客户的钱，收了多少钱 ，甚至可能还要再增加一些列 ，就是收的银行承兑呀，还是这个汇款呀，还是支票啊还是，等等 ，总之吧，这个表咱们只是仅限于一些主要字段 ，那么分开进行填写它就会出一些问题，出什么问题呢 ，就是你要是想进行整合的时候呢就不方便，就不方便 ，而且呢客户名称呢也不是特别容易统一 ，毕竟呢是在两个表，甚至呢可能是两个人在做这个表 ，那么如果我们要是把它整合在一个表里，把发货的和收款的整合在一个表 ，你就会发现它会带来很方便的汇总还有查询 ，那下面呢我们就来看一看它这个查询的效果 ，我们在这个列表框当中选择不同的客户名称 ，我们呢就可以获得这个客户在指定时间内的所有的发货记录和收款记录 ，那看一下这张表你想到了什么 ，是不是经常在跟客户进行对账的时候呢 ，我们是需要一个这样的东西 ，我们把这个月份调整好，一月份的对账，二月份的对账 ，然后呢把这个呢做个截图啊或者说是发个电子邮件啊等等，非常的方便 ，这是明细数据，再往上看，这里面还有合计 ，

既有这个客户，这叫上海千百度这公司的期初数 ，期初就指的这个期，就是指的你查询期的这个期 ，然后本期的发货金额 ，本期的收款金额，还有期末的欠款额 ，那想一想，我们平时对账的时候除了需要这些明细之外 ，是不是呢还希望能够有一个汇总 ，而且还希望有一个截止的应收数的这么一个数据 ，用于呢与对方呢进行确认，看一看是不是还差这么多钱 ，非常得方便，而且呢如果呢要进行日期的调整也行 ，2月1号到2月29号同样也可以，非常方便 ，而且呢还不止这些，还不止这些，还有什么呢 ，就是当我们选择了某一个公司 ，看到了我们在指定月份期间内的明细之后 ，你打开后面的这个客户汇总表 ，立即就能看到这个公司，因为我们选择的这个公司 ，跟在前面这个表选择是关联着的 ，你在这儿选一个，后面这个就自动跟过来了 ，那么在这个表当中呢，我们就能够看到 ，这个公司从本年度的一月、二月、三月一直到年末的全年12个月的 ，发货金额、收款金额以及这个年度的期初数和期末数 ，其实这个期末数呢就是截止到当前了，因为现在我们是三月份 ，我们现在是三月份，所以这就是截止到三月份的数据 ，那如果我们现在到了八月份了，这个数它自动地就会跳到是八月份的这个期末数 ，那你看我们既能查询明细，又能查询汇总吧 ，

是不是很方便 ，那这些呢其实都仰仗我们这个台账 ，由于我们这个台账的数据整合在一起 ，所以呢就能够出来这么多的一些便捷的查询结果了 ，好，那下面呢咱们就来说一说这个东西呢它的背后的是靠哪些公式 ，哪些原理在支撑的呢 ，第一步呢我们先来看一下这个 ，登记台账的辅助列 ，那这个辅助列呢是为了后面的查询做一个铺垫 ，在这辅助列当中呢我们写了一个公式 ，写了一个公式，那么这个公式它是什么作用呢 ，来看一下啊，在这个公式当中呢 ，我们用到了一个IF ，然后这个IF呢它的里面是三个参数 ，第一个是判断表达式 ，第二个呢是当它成立的时候返回的这个值，也就是从这个MAX后面这个 ，如果它要不成立就返回这个空，这是这个主函数的一个功能 ，那下面呢咱们就来说一说它的其它的，其它的这个作用 ，为什么这个里面出现了一个AND ，我们可能平时用的简单的时候经常会遇到这种，就是 ，C2什么等于M1，或者什么B2等于什么C2等等啊 ，一般就是一个判断，但是现在呢我们需要有三个判断 ，而且这三个判断之间的关系呢是一个并且的关系 ，所谓并且，就是说C2要等于查询表的M1 ，并且B2要大于查询表的B1 ，并且B2要小于查询表的B2，那么这三个判断是什么意思呢，咱们来看一下 ，本公式呢取自于A2单元格 ，然后呢我们来看它对应的C2就是客户名 ，

它说客户名等于查询表M1，看一下M1在哪儿呢 ，看一下M1在哪儿呢，在这儿呢 ，这个M1呢是根据我们在这儿选择的这个不同的选项 ，然后由它呢来控制L1单元格，这个我们可以在这儿看一下，这是个小技巧 ，我们在这个地方来选择一下数据源，就是我们从基础数据表的A列 ，基础数据表的A列就是在这儿，A列就这个 ，这是它的这个数据源，然后呢链接的单元格呢就是本表的L1单元格 ，那么它的这个作用呢就是，当你选第一个选项的时候它的结果是1 ，当你选第四个选项的时候它的结果是4 ，然后我们再用这个1 2 3 4，用这个数字来控制一个OFFSET就可以了 ，这个OFFSET的意思就是基础表中的A1单元格向下移动多少行 ，向下移动几行呢，4行，你这是4，它就是向下移动4行 ，那我们来看一下基础资料以A1单元格为起始定位点向下移动1 2 3 4就是千百度 ，那这个公式其实呢比较简单，虽然这个OFFSET这个函数属于比较高大尚的一个啊 ，但是呢在这里面应用很简单 ，以基础资料表的A1单元格为起始定位点向下移动多少行 ，多少行呢取决于它的变化啊 ，它是1，那么你就向下移动1行，它是2，你就向下移动2行 ，它要如果说是4，你就向下移动4行 ，好，这是这个刚才说到我们这个公式当中的这个C2 ，当前的客户等于我选择的那个客户，你看我们在这是不是选择的这个客户 ，对吧，我们选择了千百度，通过公式，利用这个中间过度的这个值 ，把这个对应的那个公司名称就取出来了啊 ，

好，接着往下说C2等于指定的客户名称的 ，然后后面的B2大于等于什么，B2小于等于什么 ，咱们先看两个B2 ，B2是谁呢，是日期，就是发布日期要大于查询表的B1 ，小于查询表的B2，来看一下查询表的B1和B2是谁呢，是它俩 ，是它俩，一个是起始日一个是终止日，上面是开始，下面是结束 ，1是开始，2是结束 ，然后我们再来解读这个公式 ，说，当前这一行的日期大于这个开始，小于这个终止 ，说明一个什么问题呢，是不是你当前发货的这个日期 ，在你查询这个期间呢 ，你指定的开始和指定的终止 ，而我当前这个日期大于你的开始值而小于你的终止值 ，是不是说明我这个日期在你这个查询的起始和终止之间 ，也就是相当于是符合你的查询时间的一个要求 ，那这是这两个B所起的作用 ，那么把这三个条件并在一起说，你再体会一下它的作用是什么呢 ，就是我现在想判断这一行记录当中，客户这个名称与我设定的要查询的这个客户名称 ，是不是相等，这是个相等，这是第一个 ，第二个，那么我当前行的这个记录的发布日期或者说这个时间 ，是否在查询的这个时间段内 ，是否在这个查询的时间段内 ，那也就是通过大于等于开始小于等于终止，通过这两个实现 ，如果这三个都成立，客户名也相符，这个日期又在这两个之间 ，那么就怎么着呢，那就说明呢这就是我想筛选出来的那条记录了 ，

这就是我想筛选出来的那条记录了 ，那么怎么给满足这个条件的做个标记呢 ，就让这经在这儿呢给它做一个叫做MAX（A$1：A1）然后+1 ，那这个东西是什么意思呢 ，这个东西的意思就是说从当前行的这个单元格上一行 ，现在我们这个公式不是在A2吗，在A2，就是从它当前行的上一行 ，以A1单元格为起始点，然后向下的 ，某一个区域内的那个最大值加1 ，那也就是说如果你这个条件被满足 ，那么就让它等于上边之前的这些数加1 ，那这样看呢，不是特别得明显，咱们呢把这个查询日期改成一月 ，然后把我们的这个查询的客户名，我们给它改一下 ，改成一个比方说武汉紫水晶 ，武汉紫水晶，然后呢我们再来品，你看这出现1了 ，那这个1是怎么来的呢，这个1呢就是首先C2和两个时间已经满足了 ，在这两个时间段内了，那2020年1月1号，然后客户是紫水晶 ，在这个区间，然后客户也是紫水晶 ，那也就是说当前行呢是我们希望把它筛选出来一条记录 ，那么满足这个条件怎么办呢，就是A1至A1的最大值 ，A1这里面没有值，这就是一个汉字，它没有数据 ，那最大值是多少呢，就是0，0+1就等于1 ，这是我们的第一个，然后再来看下面这个公式 ，你就体会出来，这里面出现了一个什么A1：A4 ，因为我们这个公式在设计的时候呢涉及到一个把头，A1：A1的这个头部 ，用一个$锁定了，所以当公式向下复制的时候前面这个1不动 ，

而后面这个1会变成2 3 4 ，那也就是说在这个单元格，我们这个MAX（A$1：A4） ，A1：A4这个区间内的那个最大值，最大值是几啊，是1 ，怎么办呢，加1，它就变成了2了 ，那么我们通过这个公式要实现什么呢 ，就是对当前行记录符合客户名称的 ，和在查询日期之间的这个记录 ，添加一个序列号 ，给它添加一个序列号，你看凡是满足这个条件的，它前面自动加了一个序列号 ，