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數據自動引用＊＊＊＊
原創：苗旭/部落窩教育（未經同意，請勿轉載）
下一篇
女命看法
最新文章
-
“豫西中醫界之泰鬥喬保均”治療疑難病60年經驗賞析
5小時前 -
回憶、健忘和灑脫
6小時前 -
弘一法師：“真的不忍心告訴你，這個世界隻是一個夢。你一輩子執
6小時前 -
心理學，準的讓你驚叫
6小時前 -
人生，成大器者有四識，知識、見識、膽識、遠識
6小時前 -
俞和：被遺忘的書法家，以古為師，創新在手！
6小時前 -
2024屆新高考II卷語文真題答案及解析
7小時前 -
生前隻是小人物，死後震驚史學界
7小時前
熱門文章
-
每日一誦傷寒論第241條
1周前 -
老張老李侃門球之140篇
1天前 -
這個穴位可以治療多種胃痛腹痛，還可以減肥
5天前 -
為什麼五點鐘要起床答案讓人吃驚！（現在知道還不晚）
1周前 -
治療坐骨神經痛藥酒５方
1周前 -
美麗中國-2870：中國最大的内陸河，塔裡木河
2天前 -
二十四山開門放水作竈真訣開門放水作竈直訣——子山
1天前 -
門球技巧 隻需五個字讓你打好門球
1天前
有話要說...