二维码 购物车
部落窝在线教育欢迎您!

解放双手! 每日业绩通报你需要这张全自动表

 

作者:苗旭来源:部落窝教育发布时间:2020-05-09 17:22:57点击:764

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!如何快速做好每日业绩通报?如果每次都要重新输入日期、手动整理计算数据,那不但太费时间了而且还容易出错。今天苗老师要和大家分享一张全自动的Excel业绩通报表,解放你的双手、双眼,赶紧来看看吧!

 

小胡:“苗老师,您能教我怎么做微信数据通报吗,我今天又被领导批评了,说我做通报动作慢,还老是出错。但是这么多数据,我要看、要找、还要算,哪有那么快呀。而且每天都要做,烦都烦死了。”

 

苗老师:“小胡,你说慢点,一进来噼里啪啦说这么一大堆,我都听混了,你先把你们业务部门的通报给我看看。”

 

小胡:“就是这样的,202053日,当日时间进度为33.9%,当日的收入为33.7万元,全年累计收入为370.7万元,完成全年500万指标的74.1%。超过时间进度40.3%。”以下为各店收入和排名,如图1所示。

 

1

 

苗老师:“其实这个通报很简单,有几点我想应该是比较重要的问题,一个是指标完成率,一个是时间进度的对比,还有一个是店内排名。”

 

小胡:还有日期呢,好几次都把日期写错了。

 

苗老师:“我再看看你的取值表。”

 

小胡:“我发您看看。”如图2所示。

 

2

 

苗老师:“现在我挨个来帮你解决问题,先说表格部分。关于表格中的日期问题,用TODAY()这个函数,就能直接得到当日的日期。如果通报的是昨天的数据,可以用TODAY()-1,数据会随着日期每天变化。”我们把日期放在一个固定的位置,比如放在A2单元格中,如图3所示。

 

社交网站的手机截图
描述已自动生成

3

 

苗老师:接着我们可以为这张通报表制作一个带有日期的标题,使用连接符&,可以把文字和日期连接起来,我们把表和标题放在B1的位置,然后输入:

 

=A2&"各门店销售通报"

 

因为“各门店销售通报”是文本,所以需要在两边加上英文双引号。

 

小胡:“苗老师,不对不对啊,怎么输入完后,出来的是一串数字?”如图4所示。

 

4

 

苗老师:“不急不急,那是因为日期和文本连接之后,变成了文本格式,所以日期格式就不见了,我们这里使用TEXT函数对它的格式进行调整即可。”

 

公式调整为:=TEXT(A2,"yyyymd")&"各门店销售通报"

 

TEXT的第二个参数,表示要显示的格式,ymd分别代表了年、月、日。设置完成后,标题就按照我们的需求生成了,如图5所示。

 

手机截图图社交软件的信息
描述已自动生成

5

 

不熟悉TEXT函数的同学,可以看下之前的文章《5分钟,学会文本函数之王——TEXT的常用套路》。

 

标题部分做好了,下面我们来看看收入部分的数据。收入部分的取值,虽然比较简单,但你需要先理解一个概念:我们在制表的时候,最好有一个前台表,一个后台表。前台表主要用来放我们的通报,后台表就是用来放数据的,然后将它们用函数关联起来。如图6所示。

 

手机屏幕截图
描述已自动生成

6

 

把你的数据源贴在后台表里,如图7所示。

 

7

 

粘贴的时候需注意,格式要保持一致。接着在前台表中输入“=”引用对应的后台表数据即可。注意,当后台表比较复杂时,可能要用到VLOOKUPCOUNTIFSSUMIFS这类函数。

 

我们这表挺简单的,直接用“=”号连接即可,然后再完善表的整体结构。如图8所示。

 

8

 

注意,C2单元格的公式,是直接引用的后台表B2单元格,后续如果后台表数据有变动,那这里的数据也会跟着改变。由于全年指标是确定的,所以可以通过公式:“=E3/F3”直接得到完成率。

 

当日排名和全年完成率排名,可以直接用排名函数RANK,得到结果。

 

D3单元格的为:=RANK(C3,C$3:C$9)

 

注意,公式中的区域要加上“$”符号,如果不加,下拉公式时,它会发生变化,排名结果就会出错。如图9所示。

 

9

 

设置完成后,我们的表格部分就做完了,如图10所示。

 

10

 

下面开始说通报的文字内容,先来看看需要通报的信息。

 

202053日,当日时间进度为33.9%,当日的收入为33.7万元,全年累计收入为370.7万元,完成全年500万指标的74.1%。超过时间进度40.3%。以上为各店收入和排名。

 

通过上述文字,我们可以发现,目前我们还缺少时间进度和超过时间进度的数据,下面我们计算一下这部分的数据。

 

时间进度就是当年已经过去的天数占全年天数的百分比,2020年是366天,所以这里的分母是366。分子我们可以使用DATEDIF函数进行计算。DATEDIF函数用于计算从A日期到B日期之间相距多少天、多少月或多少年。

 

结构是:DATEDIF(起始日期,结束日期,返回类型)

  

公式为:=DATEDIF("2019-12-31",A2,"d")/366

 

这里的返回类型是"d",表示计算两日期相差的天数,如果是计算相差的年数和月数,把"d"换成"y""m"就好了。注意,起始日期一定要小于结束日期。

 

算出相差的天数后,再用它除以当年的天数,并将结果用百分比的格式呈现就好了。如图11所示。

 

11

 

有了时间进度,我们还缺一个超过时间进度的数据,这个就简单了,直接用完成率减时间进度,就可以得到结果,如图12所示。

 

12

 

现在我们已经把需要的数据都准备好了,下面就开始制作要汇报的内容吧!仍然是用到“&”和“TEXT”函数,我们先把所有的数据用“&”拼接起来。

 

=A2&",当日时间进度为"&A4&",当日的收入为"&C10&"万元,全年累计收入为"&E10&"万元,完成全年"&F10&"万指标的"&G10&"。超过时间进度"&A6&"。以上为各店收入和排名。"

 

但这还没有经过加工,所以得到的结果并不是我们想要的。如图13所示。

 

13

 

小胡:“我知道了,用TEXT函数,日期前面介绍过了,可是这个百分比要怎么办呢?”

 

苗老师:“我直接告诉你怎么写吧。”如图14所示。

 

=TEXT(A2,"yyyymd")&",当日时间进度为"&TEXT(A4,"0.0%")&",当日的收入为"&C10&"万元,全年累计收入为"&E10&"万元,完成全年"&F10&"万指标的"&TEXT(G10,"0.00%")&"。超过时间进度"&TEXT(A6,"0.0%")&"。以上为各店收入和排名。"


14

 

但这里还有一个问题,我们在将数据截图发送出去时,由于下方区域的内容过多,超过了表格区域,就会导致整体表格不美观。这里我们要在公式中加上CHAR(10)函数,再点击“自动换行”按钮,文字内容就能在单元格里分行了。如图15所示。

 

=TEXT(A2,"yyyymd")&",当日时间进度为"&TEXT(A4,"0.0%")&",当日的收入为"&C10&"万元,全年累计收入为"&E10&"万元,完成全年"&F10&"万指标的"&TEXT(G10,"0.00%")&""&CHAR(10)&"超过时间进度"&TEXT(A6,"0.0%")&"。以上为各店收入和排名。"

 

15

 

苗老师:“你看,这样就拥有了一个简单的自动通报系统,整个前台表就做完了。以后每天只需把表格和文字截图,转发到群里,就OK了。”如图16所示。

 

图片包含 游戏机
描述已自动生成

16

 

小胡:“太棒了,这样我再也不用担心被领导批评了。”

 

本文配套的练习课件请加入QQ群:1003077796下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel制作曲线图《年终报告,你需要这张Excel图表来“撑场面”》

Excel连接字符串的方法连接字符串的神奇妙用,让你轻松实现高效率办公!

DATEDIF函数的应用用上DATEDIF,您永不再缺席那些重要的日子!

关于时间计算方面的技巧《996和955到底差了多少小时,你会算吗?