編按：

哈嘍，大家好！如何快速做好每日業(yè)績通報(bào)？如果每次都要重新輸入日期、手動(dòng)整理計(jì)算數(shù)據(jù)，那不但太費(fèi)時(shí)間了而且還容易出錯(cuò)。今天苗老師要和大家分享一張全自動(dòng)的Excel業(yè)績通報(bào)表，解放你的雙手、雙眼，趕緊來看看吧！

小胡：“苗老師，您能教我怎么做微信數(shù)據(jù)通報(bào)嗎，我今天又被領(lǐng)導(dǎo)批評(píng)了，說我做通報(bào)動(dòng)作慢，還老是出錯(cuò)。但是這么多數(shù)據(jù)，我要看、要找、還要算，哪有那么快呀。而且每天都要做，煩都煩死了?！?/span>

苗老師：“小胡，你說慢點(diǎn)，一進(jìn)來噼里啪啦說這么一大堆，我都聽混了，你先把你們業(yè)務(wù)部門的通報(bào)給我看看?！?/span>

小胡：“就是這樣的，2020年5月3日，當(dāng)日時(shí)間進(jìn)度為33.9%，當(dāng)日的收入為33.7萬元，全年累計(jì)收入為370.7萬元，完成全年500萬指標(biāo)的74.1%。超過時(shí)間進(jìn)度40.3%?！币韵聻楦鞯晔杖牒团琶?，如圖1所示。

圖1

苗老師：“其實(shí)這個(gè)通報(bào)很簡單，有幾點(diǎn)我想應(yīng)該是比較重要的問題，一個(gè)是指標(biāo)完成率，一個(gè)是時(shí)間進(jìn)度的對(duì)比，還有一個(gè)是店內(nèi)排名?！?/span>

小胡：“還有日期呢，好幾次都把日期寫錯(cuò)了。”

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

小胡：“我發(fā)您看看?！比鐖D2所示。

圖2

苗老師：“現(xiàn)在我挨個(gè)來幫你解決問題，先說表格部分。關(guān)于表格中的日期問題，用TODAY()這個(gè)函數(shù)，就能直接得到當(dāng)日的日期。如果通報(bào)的是昨天的數(shù)據(jù)，可以用TODAY()-1，數(shù)據(jù)會(huì)隨著日期每天變化。”我們把日期放在一個(gè)固定的位置，比如放在A2單元格中，如圖3所示。

圖3

苗老師：接著我們可以為這張通報(bào)表制作一個(gè)帶有日期的標(biāo)題，使用連接符&，可以把文字和日期連接起來，我們把表和標(biāo)題放在B1的位置，然后輸入：

=A2&"各門店銷售通報(bào)"

因?yàn)椤案鏖T店銷售通報(bào)”是文本，所以需要在兩邊加上英文雙引號(hào)。

小胡：“苗老師，不對(duì)不對(duì)啊，怎么輸入完后，出來的是一串?dāng)?shù)字？”如圖4所示。

圖4

苗老師：“不急不急，那是因?yàn)槿掌诤臀谋具B接之后，變成了文本格式，所以日期格式就不見了，我們這里使用TEXT函數(shù)對(duì)它的格式進(jìn)行調(diào)整即可?！?/span>

公式調(diào)整為：=TEXT(A2,"yyyy年m月d日")&"各門店銷售通報(bào)"

TEXT的第二個(gè)參數(shù)，表示要顯示的格式，y、m、d分別代表了年、月、日。設(shè)置完成后，標(biāo)題就按照我們的需求生成了，如圖5所示。

圖5

不熟悉TEXT函數(shù)的同學(xué)，可以看下之前的文章《5分鐘，學(xué)會(huì)文本函數(shù)之王——TEXT的常用套路》。

標(biāo)題部分做好了，下面我們來看看收入部分的數(shù)據(jù)。收入部分的取值，雖然比較簡單，但你需要先理解一個(gè)概念：我們?cè)谥票淼臅r(shí)候，最好有一個(gè)前臺(tái)表，一個(gè)后臺(tái)表。前臺(tái)表主要用來放我們的通報(bào)，后臺(tái)表就是用來放數(shù)據(jù)的，然后將它們用函數(shù)關(guān)聯(lián)起來。如圖6所示。

圖6

把你的數(shù)據(jù)源貼在后臺(tái)表里，如圖7所示。

圖7

粘貼的時(shí)候需注意，格式要保持一致。接著在前臺(tái)表中輸入“=”引用對(duì)應(yīng)的后臺(tái)表數(shù)據(jù)即可。注意，當(dāng)后臺(tái)表比較復(fù)雜時(shí)，可能要用到VLOOKUP、COUNTIFS、SUMIFS這類函數(shù)。

我們這表挺簡單的，直接用“=”號(hào)連接即可，然后再完善表的整體結(jié)構(gòu)。如圖8所示。

圖8

注意，C2單元格的公式，是直接引用的后臺(tái)表B2單元格，后續(xù)如果后臺(tái)表數(shù)據(jù)有變動(dòng)，那這里的數(shù)據(jù)也會(huì)跟著改變。由于全年指標(biāo)是確定的，所以可以通過公式：“=E3/F3”直接得到完成率。

當(dāng)日排名和全年完成率排名，可以直接用排名函數(shù)RANK，得到結(jié)果。

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

注意，公式中的區(qū)域要加上“$”符號(hào)，如果不加，下拉公式時(shí)，它會(huì)發(fā)生變化，排名結(jié)果就會(huì)出錯(cuò)。如圖9所示。

圖9

設(shè)置完成后，我們的表格部分就做完了，如圖10所示。

圖10

下面開始說通報(bào)的文字內(nèi)容，先來看看需要通報(bào)的信息。

2020年5月3日，當(dāng)日時(shí)間進(jìn)度為33.9%，當(dāng)日的收入為33.7萬元，全年累計(jì)收入為370.7萬元，完成全年500萬指標(biāo)的74.1%。超過時(shí)間進(jìn)度40.3%。以上為各店收入和排名。

通過上述文字，我們可以發(fā)現(xiàn)，目前我們還缺少時(shí)間進(jìn)度和超過時(shí)間進(jìn)度的數(shù)據(jù)，下面我們計(jì)算一下這部分的數(shù)據(jù)。

時(shí)間進(jìn)度就是當(dāng)年已經(jīng)過去的天數(shù)占全年天數(shù)的百分比，2020年是366天，所以這里的分母是366。分子我們可以使用DATEDIF函數(shù)進(jìn)行計(jì)算。DATEDIF函數(shù)用于計(jì)算從A日期到B日期之間相距多少天、多少月或多少年。

結(jié)構(gòu)是：DATEDIF(起始日期,結(jié)束日期,返回類型)

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

這里的返回類型是"d"，表示計(jì)算兩日期相差的天數(shù)，如果是計(jì)算相差的年數(shù)和月數(shù)，把"d"換成"y"和"m"就好了。注意，起始日期一定要小于結(jié)束日期。

算出相差的天數(shù)后，再用它除以當(dāng)年的天數(shù)，并將結(jié)果用百分比的格式呈現(xiàn)就好了。如圖11所示。

圖11

有了時(shí)間進(jìn)度，我們還缺一個(gè)超過時(shí)間進(jìn)度的數(shù)據(jù)，這個(gè)就簡單了，直接用完成率減時(shí)間進(jìn)度，就可以得到結(jié)果，如圖12所示。

圖12

現(xiàn)在我們已經(jīng)把需要的數(shù)據(jù)都準(zhǔn)備好了，下面就開始制作要匯報(bào)的內(nèi)容吧！仍然是用到“&”和“TEXT”函數(shù)，我們先把所有的數(shù)據(jù)用“&”拼接起來。

=A2&"，當(dāng)日時(shí)間進(jìn)度為"&A4&"，當(dāng)日的收入為"&C10&"萬元，全年累計(jì)收入為"&E10&"萬元，完成全年"&F10&"萬指標(biāo)的"&G10&"。超過時(shí)間進(jìn)度"&A6&"。以上為各店收入和排名。"

但這還沒有經(jīng)過加工，所以得到的結(jié)果并不是我們想要的。如圖13所示。

圖13

小胡：“我知道了，用TEXT函數(shù)，日期前面介紹過了，可是這個(gè)百分比要怎么辦呢？”

苗老師：“我直接告訴你怎么寫吧?！比鐖D14所示。

=TEXT(A2,"yyyy年m月d日")&"，當(dāng)日時(shí)間進(jìn)度為"&TEXT(A4,"0.0%")&"，當(dāng)日的收入為"&C10&"萬元，全年累計(jì)收入為"&E10&"萬元，完成全年"&F10&"萬指標(biāo)的"&TEXT(G10,"0.00%")&"。超過時(shí)間進(jìn)度"&TEXT(A6,"0.0%")&"。以上為各店收入和排名。"

圖14

但這里還有一個(gè)問題，我們?cè)趯?shù)據(jù)截圖發(fā)送出去時(shí)，由于下方區(qū)域的內(nèi)容過多，超過了表格區(qū)域，就會(huì)導(dǎo)致整體表格不美觀。這里我們要在公式中加上CHAR(10)函數(shù)，再點(diǎn)擊“自動(dòng)換行”按鈕，文字內(nèi)容就能在單元格里分行了。如圖15所示。

=TEXT(A2,"yyyy年m月d日")&"，當(dāng)日時(shí)間進(jìn)度為"&TEXT(A4,"0.0%")&"，當(dāng)日的收入為"&C10&"萬元，全年累計(jì)收入為"&E10&"萬元，完成全年"&F10&"萬指標(biāo)的"&TEXT(G10,"0.00%")&"。"&CHAR(10)&"超過時(shí)間進(jìn)度"&TEXT(A6,"0.0%")&"。以上為各店收入和排名。"

圖15

苗老師：“你看，這樣就擁有了一個(gè)簡單的自動(dòng)通報(bào)系統(tǒng)，整個(gè)前臺(tái)表就做完了。以后每天只需把表格和文字截圖，轉(zhuǎn)發(fā)到群里，就OK了?！比鐖D16所示。

圖16

小胡：“太棒了，這樣我再也不用擔(dān)心被領(lǐng)導(dǎo)批評(píng)了?！?/span>

本文配套的練習(xí)課件請(qǐng)加入QQ群：1003077796下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

用Excel制作曲線圖《年終報(bào)告，你需要這張Excel圖表來“撐場面”》

Excel連接字符串的方法《連接字符串的神奇妙用，讓你輕松實(shí)現(xiàn)高效率辦公！》

DATEDIF函數(shù)的應(yīng)用《用上DATEDIF，您永不再缺席那些重要的日子！》

關(guān)于時(shí)間計(jì)算方面的技巧《996和955到底差了多少小時(shí)，你會(huì)算嗎？》