一. 需求分析
- 通報企業(yè)每日運(yùn)營的一些關(guān)鍵指標(biāo)（包括銷售量，銷售額，購買人數(shù)，訂單數(shù)，毛利額，件單價，連帶率，環(huán)比等）的完成情況，并將日報模板化，自動化。
二. 做日報的流程
- 從公司數(shù)據(jù)庫中提取數(shù)據(jù)到Excel表中；
- 在Excel中對數(shù)據(jù)進(jìn)行相應(yīng)的處理并繪制圖表；
- 把繪制好的圖表復(fù)制到Word文檔中，編輯通報正文，最后以郵件正文的形式發(fā)出。
- 一般通報的指標(biāo)、內(nèi)容都基本固定，所以我們可以把日報模板化，自動化。
三. 日報自動化原理
- 通過VBA語句，從數(shù)據(jù)庫自動提取前一日相應(yīng)的關(guān)鍵指標(biāo)數(shù)據(jù)，并自動追加放置在一張名為 “數(shù)據(jù)源” 表中的相應(yīng)位置，實現(xiàn)一鍵自動提取數(shù)據(jù)（注意：本日記中，因為這一步驟篇幅較長，我用預(yù)設(shè)全年日期，手動添加數(shù)據(jù)的方式模擬了這一過程）。
- 在數(shù)據(jù)轉(zhuǎn)化區(qū)中，根據(jù)制定的日期條件，動態(tài)引用 “數(shù)據(jù)源” 表中相應(yīng)的數(shù)據(jù)，并自動繪制圖表、組合通報文字。
- 在日報正文區(qū)中，引用相應(yīng)的組合好的通報文字與繪制的圖表。
- 通過控件選擇需要通報的日期，并自動生成相對應(yīng)日期的日報正文。
四.日報自動化的實現(xiàn)過程
首先要規(guī)劃好日報所需的數(shù)據(jù)，建立數(shù)據(jù)模板。在”日報.xlsx”文件中，準(zhǔn)備三張表。
- ”數(shù)據(jù)源“表：用于存放每日通報所需的關(guān)鍵指標(biāo)數(shù)據(jù)
- ”數(shù)據(jù)轉(zhuǎn)化“表：用于動態(tài)引用 ”數(shù)據(jù)源” 表中的數(shù)據(jù)，并進(jìn)行相應(yīng)的數(shù)據(jù)轉(zhuǎn)化，最后自動繪制圖表，組合通報文字
- “日報正文”表：引用 “數(shù)據(jù)轉(zhuǎn)化” 表中相應(yīng)組合好的通報文字與繪制好的圖表，并呈現(xiàn)日報。
以下是具體實現(xiàn)過程
步驟1.（建立 “數(shù)據(jù)源”表）
- 通過"開始"菜單欄- "編輯窗口"- "填充"- "序列"功能預(yù)設(shè)全年日期；
- 根據(jù)你自己定的數(shù)據(jù)模板提取相應(yīng)的數(shù)據(jù)，表中毛利額以及它之前的所有列均為提取字段，月累計及年累計為計算字段，其余的為輔助字段；
- 計算字段的計算過程如下： 年累計用函數(shù) =SUM($C$2:C2) 計算。月累計需借助輔助字段K，用函數(shù) =SUM(INDIRECT(K2):C2) 計算每月1號到當(dāng)前日期的總和，其中 =INDIRECT(K2) 是對每月1號單元格的引用；
-
輔助字段的計算過程如下： 月份用函數(shù) =MONTH(A2) 計算。 每月1號用函數(shù) =IF(I2=I1,J1,ROW(I2)) 計算。 每月1號銷售額對應(yīng)的單元格用函數(shù) ="C"&J2 計算
步驟2.（建立 “數(shù)據(jù)轉(zhuǎn)化”表）
- 創(chuàng)建日期控件:
- 選擇 ‘開發(fā)工具’ 選項卡 - ‘控件’區(qū)域中 - 選擇 ‘插入’ - 選擇 ’組合框(窗體控件)‘；
- 拖動鼠標(biāo)，創(chuàng)建控件，接著鼠標(biāo)右鍵選擇 ’設(shè)置控件格式‘；
- 在 ’數(shù)據(jù)源區(qū)域‘ 中選擇 ’數(shù)據(jù)源‘ 表中的日期列，在’單元格鏈接‘選擇 C2單元格，‘下拉顯示項數(shù)’默認(rèn)就可以，點擊確定，完成創(chuàng)建。
- 創(chuàng)建數(shù)據(jù)動態(tài)引用：
- 設(shè)置標(biāo)題；
- 采用OFFSET函數(shù)動態(tài)引用數(shù)據(jù)。選中A6:H12單元格，填入函數(shù) =OFFSET(數(shù)據(jù)源表!A1, C2-6, 0, 7, 8)，按住shift+ctrl+enter鍵完成數(shù)據(jù)引用；
- 用函數(shù) =F6/C6 計算毛利率（毛利額/銷售額）。
- 創(chuàng)建報告數(shù)據(jù)表：
- 設(shè)計報告表格式；
- 用INDEX和MATCH函數(shù)組合的方式引用當(dāng)日和昨日各指標(biāo)的值。當(dāng)日指標(biāo)值采用函數(shù) =INDEX($B$12:$I$12, 1, MATCH($A17, $B$5:$I$5, 0))函數(shù)計算，昨日指標(biāo)值采用函數(shù) =INDEX($B$11:$I$11, 1, MATCH($A17, $B$5:$I$5, 0))函數(shù)計算；
- 創(chuàng)建表，存儲月份和銷售額目標(biāo)； 4.月累計銷售額用函數(shù) =INDEX($B$12:$I$12, 1, MATCH($A25, $B$5:$I$5, 0)) 計算，月完成目標(biāo)采用函數(shù) =TEXT(B25/VLOOKUP(MONTH(A12)&"月", $I$17:$J$28, 2, 0), "0.00%") 計算； 5.計算環(huán)比（=當(dāng)日/昨日-1）； 6.計算連帶率（銷售量/購買人數(shù)），計算件單價（銷售額/銷售量）
- 創(chuàng)建報告標(biāo)題及正文：
- 擬定報告標(biāo)題及正文內(nèi)容格式；
- 直接引用報告數(shù)據(jù)表中數(shù)據(jù)；
- 用 =CONCATENATE函數(shù)拼接數(shù)據(jù)。
- 生成圖表：
- 從 ”數(shù)據(jù)轉(zhuǎn)化“ 表中引用制作圖表所需的數(shù)據(jù)，制作圖表。（注：這里的圖表比較簡單，我就不多介紹了）。
步驟3.（建立 “通報正文”表）
建立好日報模板，日期控件從 "數(shù)據(jù)轉(zhuǎn)化" 表中直接復(fù)制過來（這里注意檢查控件的數(shù)據(jù)源引用區(qū)域是否正確），數(shù)據(jù)也是直接引用，圖表直接復(fù)制粘貼即可。日報正文如下，報告很簡陋，有時間再美化了。
五. 參考資料
- 《誰說菜鳥不會數(shù)據(jù)分析》
- 小蚊子數(shù)據(jù)分析-Excel報告自動化
六. 總結(jié)
一直期待能將數(shù)據(jù)分析帶到傳統(tǒng)中小制造業(yè)，幫助他們加快轉(zhuǎn)型升級的步伐。我認(rèn)為傳統(tǒng)制造業(yè)轉(zhuǎn)型升級，得數(shù)據(jù)先行。目前最要的任務(wù)還是腳踏實地先把技能get到先吧，然后再談夢想?。?！
