前提:本文涉及的数据表单都是删减过后的,以交流思路为主。
本榜单应用于无系统并需要手动记录订单的销售团队,应用于销售进度激励等。
进入正文:
这个表单是在一家电商公司试岗的时候遇到的,业务团队需要定期看到这个表单,而数据表单为纯手工登记,每次还需要人为的去调整这个榜单并发布,当订单超过100单,这个表单维护起来就很考验制作者的熟练程度和及时性了。
可惜的是我是个新人去了之后完全做不到老员工的快速反应,所以便只做了这样的自动表,只需要登记好销售数据,就可以自己计算得到结果。
下面直接看实现过程吧。
这个过程需要的函数有些要求,需要是office365或者WPS。本文使用的是office365,只提供思路,实现过程会因为工具的不同而产生出入。
一、开局一张表,还是emmm......手工表
真心建议如果你的公司也在用这种登记方式的话,赶紧换一换吧,效率低且易出错,后期还不容易核对。
二、梳理数据,构建数据结构
根据需求,我们知道,这个榜单需要展现的数据包含:地域、销售姓名、产品类别、成交单数
那么我们可以得到一张这样的销售表单
在实际应用中,我们只需要填写销售及其成单产品类型即可。其余的数据可以通过员工表及产品表匹配过来。
至此我们的数据结构所需要的表格就齐了。其中员工表和产品表是基本固定的,跟随公司政策情况变化而变化。我们只需要实时登记销售人员的姓名和销售产品即可,后期有销售对数据有疑问,也可以根据这个表及群内报单数据进行核对。
今天的主角是如何制作实时更新的榜单,所以三个表格如何联动就跳过了。
三、制作销售榜单
数据需求:
1、根据登记的订单,按照销售成交订单数量降序排列,订单数相同按成交时间升序排列,即谁先成交谁在前
2、榜单按照地区和成交产品类型分别排序,成交单品大于1单可上榜单,成交组合商品大于3单可上榜单
由需求及数据表我们知道,我们需要提取的数据如下:
1、运河区+单品(A/B)
2、运河区+组合(A+B)
3、新华区+单品(A/B)
4、新华区+组合(A+B)
以上四类的销售成交订单排序,我们以运河区单品为例,其他的三个只需要修改条件就可以得到公式了。
第一步:利用filter函数提取运河区单品的销售名单
=FILTER(订单表[销售],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))
该函数office365和WPS都可以使用,表示根据条件提取表中的数据,如何使用大家自行百度,这里说一下多条件的写法。(条件A)*(条件B)表示同时满足AB两个条件,(条件A)+(条件B)表示满足A或B。
这里获取的是运河区单品的数据,所以条件用*连接。这样我们得到该条件下所有销售的结果。
第二步:UNIQUE对第一步的结果去重
=UNIQUE(FILTER(订单表[销售],(订单表[地域]="运河区")*(订单表[产品]<>"AB")))
第三步:FREQUENCY获得工号的频率分布,以此确定销售对应单数
=FREQUENCY(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")),UNIQUE(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))))
因为这个思路中的函数基本都是数组溢出一类的,count等计数函数无法参与其中,所以在计算销售的成交订单数量的时候使用到这个函数,而我们提取的 FILTER(订单表[销售] 也变为了 FILTER(订单表[工号] 。
这个公式在使用的时候不知为何会多出一个数,处理的过程中对他进行了FILTER嵌套,函数及结果如下:
=FILTER(FREQUENCY(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")),UNIQUE(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")))),FREQUENCY(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")),UNIQUE(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))))>0)
至此符合条件的销售及其订单数目已经得到了,接下来需要实现的是,相同订单数的销售按照时间排序的问题。
第四步:XLOOKUP或者 VLOOKUP匹配成交时间
这里的时间顺序,因为是手工单,所以用登记序号表示。
=VLOOKUP(UNIQUE(FILTER(订单表[销售],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))),SORT(FILTER(IF({1,0},订单表[销售],订单表[序号]),(订单表[地域]="运河区")*(订单表[产品]<>"AB")),2,-1),2,FALSE)
用vlookup比xlookup公式少一些,但原理是一样的,都是匹配销售的最新成单排名(即序号)
首先获取的是销售及其订单序号,并对结果序号列进行降序排列
SORT(FILTER(IF({1,0},订单表[销售],订单表[序号]),(订单表[地域]="运河区")*(订单表[产品]<>"AB")),2,-1)
然后用我们一开始得到的销售名称UNIQUE(FILTER(订单表[销售],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))),套入vlookup公式,获取精确结果
至此我们得到了3列数据,如下图:
第五步:HSTACK组合上述3列数据,然后定义名称为【运河区单品】
=HSTACK(UNIQUE(FILTER(订单表[销售],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))),FILTER(FREQUENCY(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")),UNIQUE(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")))),FREQUENCY(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB")),UNIQUE(FILTER(订单表[工号],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))))>0),VLOOKUP(UNIQUE(FILTER(订单表[销售],(订单表[地域]="运河区")*(订单表[产品]<>"AB"))),SORT(FILTER(IF({1,0},订单表[销售],订单表[序号]),(订单表[地域]="运河区")*(订单表[产品]<>"AB")),2,-1),2,FALSE))
如果觉得公式太多了,可以把每一步的公式都自定义名称,这样后期看起来也方便一些,我是在这一步把这么庞大的一个公式自定义名称的。
最后一步:把公式填到榜单模板中
我们最后一步之前的步骤只是得到了销售名称,订单数量以及最后一单的序号。榜单需求还需要满足订单数>=1和如果订单数相同则谁先成单谁排前面两个条件。
所以先提取出来订单>=1的数据
=FILTER(运河区单品,CHOOSECOLS(运河区单品,2)>=1)
然后对第三列序号列进行升序排列,然后对第二列订单数目进行降序排列,这样就可以得到订单优先时间优先的数据结果,最终公式如下:
=IFERROR(TAKE(SORT(SORT(FILTER(运河区单品,CHOOSECOLS(运河区单品,2)>=1),3,1),2,-1),3,2),"")
这样我们的榜单就完成了,撒花✿✿ヽ(°▽°)ノ✿
具体操作视频:待更新
本文利用数组函数进行嵌套,最终实现这个更新效果。重要的还是先有思路,然后再实现每一步的时候,找对应的函数。
这个案例是上个月完成的,写这篇文章的时候,我看着公式都懵了,所以这个方法还是复杂了些,不是思路有多复杂,是内容太多显得复杂,但这是目前我掌握的最简单的方法了,如果以后有新的进步了,再继续精简。