车辆实际价值计算公式销售16项经典指标汇总统计表EXCEL公式解读

2020年10月19日发(作者：强琮)
销售16项经典指标汇总统计表EXCEL公式解读

一、本表用到的函数式及功能概述：
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、今年各部门各月销售预算量

四、表的结构如下：


五、操作办法及效果展示：
只需要每天在Sheet2（今年每日）贴入销售原始数据，Sh eet1的更新当天日期（E1），2分钟内即可输出所有
需要的数据。
举例：如下图，在E 1输入日期格式的2020617或2020-6-17，D1自动生成当月的日期D1=TEXT(E1,月< br>月，这里需要一个文本格式的6月，主要是匹配“预算”中月份的格式，所以这里出现月，意思是提取的月
份数字“6”与“月”拼在一起。

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

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


六、主要功能及使用函数
1、表一的B列是读取“预算”（格式如下）各销售部门的年销售预 算，匹配条件只有部门一个条件，用到函数式
是VLOOKUP()。
举例：B3=VLOOKUP(A3,预算!A:N,14,0)


2、 C和D为各月的销售预算及累计销售预算，因为配备的条件有部门和月份，而且这两个条件在“预算1”中分别< br>在左列和顶行，需要用到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,-$$T$$1+1,今年每
日!C:C,今年每日!B: B,$$D$$1)，这里面开始时间是：今年每日!C:C,-$$T$$1+1，其中$$E$$1-$$T$$1+1是202061，如何自动生成呢？请看下图：

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

5、G列是单个产品的月累计销量，这里的品名可以通过下拉选择（如下）。
举例 ：=SUMIFS(今年每日!G:G,今年每日!A:A,销售今年每日!D:D,A3,今年每日!C:C, -$$T$$1+1,今年每
日!C:C,今年每日!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列是去年 月销售的数据，函数式与今年的销售项的相同，数据源是“去年每日”。2020617对应去年同期的
日期如何生成呢？使用日期间隔函数，见表右侧的辅助数据栏R~T列：R1=EDATE(E1,-12)，- 12表示是E1前12个月
的日期，12真是后12个月的日期。
举例：J3=SUMIFS (去年每日!G:G,去年每日!A:A,销售去年每日!D:D,A3,去年每日!C:C,-$$T$$1+1, 去年每
日!C:C,。如下图：


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

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