1、本表用到的函数式及功能概述：

1、VLOOKUP()：纵向查找函数

2、INDEX+MATCH：纵向+横向双向查找函数

3、SUMIFS：把满足多个条件的数相加函数

4、">="&时间+"<="&时间：开始时间与结束时间函数

5、RANK()：排序函数

6、TEXT()：提取日期中数字函数

7、EDATE()：指定日期前或后数天的日期函数

二、本表实现销售数据统计功能：

1、读取各销售部门的月销售预算、年销售预算及截止当月累计销售预算

2、计算今年各销售部门当日的销售量、截止当日的月累计销售量及年累计销售量

3、计算去年各销售部门同日的销售量、截止当日的月累计销售量及年累计销售量

4、计算截止当日的月、年销售同比

5、计算截止当日的月销售预算完成率、累计到当月的销售预算完成率

6、计算截止当日的各单品月累计销售量和年累计销售量

7、计算截止当日的每月时间进度，以便与销售完成率形成快慢对比，如10号，自动显示本月时间进行到30%。

8、计算截止当日的跟部门的月、年销售量排名

9、可秒读本年内截止当日以前任何一天的上述项目数据。

三、本表需要的基础材料：

1、今年销售的流水记录

2、去年销售的流水记录

3、今年各部门各月销售预算量

4、表的结构如下：

五、操作办法及效果展示：

只需要每天在Sheet2（今年每日）贴入销售原始数据，Sheet1的更新当天日期（E1），2分钟内即可输出所有需要的数据。

举例：如下图，在E1输入日期格式的2020/6/17或2020-6-17，D1自动生成当月的日期D1=TEXT(E1,"m"&"月")=6月，这里需要一个文本格式的6月，主要是匹配“预算”中月份的格式，所以这里出现"m"&"月"，意思是提取的月份数字“6”与“月”拼在一起。

D列自动读取各部门该月的销售预算（数据来自“预算”）。

C列自动读入1~6月各部门的销售预算之和（数据来自“预算1”）。根据各月预算量快速算出累计月的预算量之和有点小技巧，如下图：2行是每月预算量，3行是每月的累计预算量，如2月的累计量=620+857=1477，先设置B3=B2，在C3输入C3=B3+C2，选中C3，按住右键往右拉倒12月。然后隐藏各部门单月的预算行，剩下累计行，选中B3:M3，下拉复制公式。

如果日期改为2020/5/1，以上对应数据就自动改为5月和1~5月。项目二中所介绍的其他数据也会跟着录入日期的改变而变化。所以，想看当日之前各天的综合数据，只需要更新销售流水数据，然后一键输入日期即可。

六、主要功能及使用函数

1、表一的B列是读取“预算”（格式如下）各销售部门的年销售预算，匹配条件只有部门一个条件，用到函数式是VLOOKUP()。

举例：B3=VLOOKUP(A3,预算!A:N,14,0)

2、C和D为各月的销售预算及累计销售预算，因为配备的条件有部门和月份，而且这两个条件在“预算1”中分别在左列和顶行，需要用到INDEX+MATCH。

举例：C3=INDEX(预算1!$B$2:$M$39,MATCH(A3,预算1!$A$2:$A$39,),MATCH($D$1,预算1!$B$1:$M$1,))，部门A3与预算1中的A列的部门名称匹配，$A$2:$A$39锁定区间，避免下拉复制公式时发生改变。月份D1与预算1中的12个月所在行$B$1:$M$1匹配。

3、E列是各部门每日的销售量，数据跟着E1的日期变化联动，数据源来自附表“今年每日”（格式如下），函数是SUMIFS()。

举例：E3=SUMIFS(今年每日!G:G,今年每日!A:A,"*销售今年每日!D:D,A3,今年每日!C:C,$E$1)，这里面匹配了3个条件，今年每日!A:A里包含销售与退货，只需要销售部分，所以匹配”*销售”；今年每日!D:D匹配销售部门；今年每日!C:C匹配日期E1，这里要锁定位置,$E$1，以配合下拉复制公式。

4、F列是月累计销售量，主要函数还是SUMIFS()，因为要用到起始时间，里面会增加起始时间函数">="&+"<="&。

举例：F3=SUMIFS(今年每日!G:G,今年每日!A:A,"*销售今年每日!D:D,A3,今年每日!C:C,">="&$E$1-$T$1+1,今年每日!C:C,"<="&$E$1,今年每日!B:B,$D$1)，这里面开始时间是：今年每日!C:C,">="&$E$1-$T$1+1，其中$E$1-$T$1+1是2020/6/1，如何自动生成呢？请看下图：

T1=TEXT(E1,"dd")=17，这是从日期E1中提取日的数值17，用2020/6/17（即E1）减去17天就是5月的最后一天，再加1就是6月1日，也就是E1-T1+1，锁定后就成了$E$1-$T$1+1。结束时间是：今年每日!C:C,"<="&$E$1。

5、G列是单个产品的月累计销量，这里的品名可以通过下拉选择（如下）。

举例：=SUMIFS(今年每日!G:G,今年每日!A:A,"*销售今年每日!D:D,A3,今年每日!C:C,">="&$E$1-$T$1+1,今年每日!C:C,"<="&$E$1,今年每日!F:F,$G$2)

如何设置品名的下拉选择？如下图，先选择G2，数据→有效性→数据有效性→序列→在来源中输入品名所在页面的位置：品名!A:A→确定，

6、H列是月销售与预算对比的完成率。

7、I列是H列数据的排名，用到的函数是RANK()。

举例：I3=RANK(H3,($H$3:$H$10,$H$12:$H$19))，其中($H$3:$H$10,$H$12:$H$19)是定义被排名数据的分段范围，这里是2段，中间用逗号分隔。

8、J列是去年月销售的数据，函数式与今年的销售项的相同，数据源是“去年每日”。2020/6/17对应去年同期的日期如何生成呢？使用日期间隔函数，见表右侧的辅助数据栏R~T列：R1=EDATE(E1,-12)，-12表示是E1前12个月的日期，12真是后12个月的日期。

举例：J3=SUMIFS(去年每日!G:G,去年每日!A:A,"*销售去年每日!D:D,A3,去年每日!C:C,">="&$R$1-$T$1+1,去年每日!C:C,"<="&$R$1)。如下图：

9、L列是年累计销售，函数式F列相同，只是起始时间为2019/1/1。

10、H22=57%，是指截止当日，本月的时间已经过去了57%，与H21的月销售完成率70.2%形成对比。这里需要计算每月的总天数，就要求出本月最后一天日期，再提取日期的天数。具体办法：见右侧辅助栏：先用EDATE(E1,1)算出下月本日的日期，即R3=EDATE(E1,1)=2020/7/17，把R3提取天数S3=TEXT(R3,"dd")=17，将R3减去17天就是6月的最后一天，即R4=EDATE(E1,1)-S3=2020/6/30，提取R4的天数：R5=TEXT(R4,"dd")=30。下载本文