excel数据处理技巧：快速引用数据，制作自动更新的通报表格

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

---

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

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

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

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

小胡：“还有日期呢，好几次都把日期写错了。”

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

小胡：“我发您看看。”如下图所示：

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

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

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

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

小胡：“苗老师，不对不对啊，怎么输入完后，出来的是一串数字？”如下图所示：

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

公式调整为：=TEXT(A2,"yyyy年m月d日")&"各门店销售通报"

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

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

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

把你的数据源贴在后台表里，如下图所示：

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

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

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

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

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

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

设置完成后，我们的表格部分就做完了，如下图所示：

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

2020年5月3日，当日时间进度为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"就好了。注意，起始日期一定要小于结束日期。

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

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

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

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

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

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

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

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

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

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

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

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

好了，故事分享结束。

＊＊＊＊部落窝教育-excel数据自动引用＊＊＊＊

原创：苗旭/部落窝教育（未经同意，请勿转载）