一,、效果圖

二,、數(shù)據(jù)源

數(shù)據(jù)源與日報、周報基本一樣,，為公司2018年1月1日至12月31日的所有銷售訂單數(shù)據(jù),，為了方便后續(xù)計算月度數(shù)據(jù)，增加輔助列計算當前日期的月份,，公式為I2=MONTH(B2),，函數(shù)MONTH可以返回日期中的月份。

三、月報看板的制作思路

1,、 明確數(shù)據(jù)分析和展示要素,；

2、 求出作圖所需數(shù)據(jù),，制作圖表,；

3、 設(shè)計數(shù)據(jù)分析看板布局；

4,、 插入日期調(diào)節(jié)按鈕,，實現(xiàn)動態(tài)查詢,；

5,、將相關(guān)的數(shù)據(jù)和圖表放在看板合適的位置,；

6,、 調(diào)整配色,，美化看板,。

四,、操作步驟

第一步：明確數(shù)據(jù)分析和展示要素。

案例展示的要素和展示方式為：

① 用大字報的方式展示本月訂單數(shù)和銷售金額，本月單日最高和最低銷售金額、訂單情況,。

② 與上月相比訂單和銷售額的變化情況，增加顯示紅色向上的三角形,，數(shù)據(jù)為紅色,，減少顯示綠色向下的三角形,，數(shù)據(jù)為綠色，相等顯示白色等號,，數(shù)據(jù)為白色。由于要在一個單元格顯示三角形和數(shù)據(jù)兩項內(nèi)容,，所以用粘貼鏈接的圖片的形式展示,。

③ 用儀表盤展示本月計劃完成率和年度計劃完成率,。

④ 用條形圖展示截止到當天為止的商品排行版,、區(qū)域排行版、城市排行版和業(yè)務(wù)員銷售排行版,。

第二步：根據(jù)展示要素要求設(shè)計公式求出作圖數(shù)據(jù),，制作圖表

1,、用C2單元格與日期調(diào)節(jié)按鈕連接,，通過按鈕的調(diào)整使C2單元格數(shù)據(jù)變化，實現(xiàn)動態(tài)效果,。

2,、用DATE函數(shù)計算本月和上月的起止日期

本月起始日期C3=DATE(2018,C2,1)，即2018年選定月份的第1天,。

本月截止日期C4=DATE(2018,C2+1,1)-1,，即選定月份下一個月第1天再減1天。

上月起始日期I3=DATE(2018,C2-1,1)

上月截止日期I4=DATE(2018,C2,1)-1

3,、用SUMIF函數(shù)統(tǒng)計本月銷售額和上月銷售額,。

本月銷售額C5=SUMIFS(數(shù)據(jù)源!$E:$E,數(shù)據(jù)源!$B:$B,'>='&$C$3,數(shù)據(jù)源!$B:$B,'<='&$C$4)

上月銷售額I5=SUMIFS(數(shù)據(jù)源!$E:$E,數(shù)據(jù)源!$B:$B,'>='&$I$3,數(shù)據(jù)源!$B:$B,'<='&$I$4)

4、用COUNTIF函數(shù)統(tǒng)計本月訂單和上月訂單,。

本月訂單C6=COUNTIFS(數(shù)據(jù)源!$B:$B,'>='&$C$3,數(shù)據(jù)源!$B:$B,'<='&$C$4)

上月訂單I6=COUNTIFS(數(shù)據(jù)源!$B:$B,'>='&$I$3,數(shù)據(jù)源!$B:$B,'<='&$I$4)

5,、用IF函數(shù)實現(xiàn)本月銷售額和訂單與上月相比，不同情況顯示不同,。

即增加時顯示紅色向上三角形,，相同時顯示等號，減少時顯示綠色向下三角形,，并顯示增加和減少比例數(shù)據(jù),。公式為：

E5=IF(C5>I5,'▲',IF(C5=I5,'=','▼'))

E6=IF(C6>I6,'▲',IF(C6=I6,'=','▼'))

F5=IF(C5>I5,(C5-I5)/C5,IF(C5=I5,0,(C5-I5)/C5))

F6=IF(C6>I6,(C6-I6)/C6,IF(C6=I6,0,(C6-I6)/C6))

想了解SUMIF、COUNTIF和IF等函數(shù)詳細用法的可以回看我往期分享的詳細教程,。

6,、用條件格式實現(xiàn)增加比例和向上三角形為紅色，減少比例和向下三角形顯示為綠色,，相同時等號和0顯示白色

① 設(shè)置增加顯示紅色數(shù)據(jù)：選擇E5和F5單元格→【開始】→【條件格式】→【新建規(guī)則】→【使用公式確定格式的單元格】→在【為符合此公式的值設(shè)置單元格】中輸入公式=$C$5>$I$5→點【格式】→【字體】→選擇紅色,，確定即可。

②設(shè)置減少顯示綠色數(shù)據(jù)：同樣的方法設(shè)置E5和F5單元格當$C$6=$I$6時顯示白色，當$C$6<$C$6時顯示為綠色,。

③同樣的方法設(shè)置E6,、F6單元格的條件格式。

7,、計算本月計劃銷售金額完成率和本年計劃銷售金額完成率，制作儀表盤

① 每月的計劃銷售金額都是2600000,，即表中L13單元格輸入計劃金額

② 本月完成金額L14=C5

③ 本月計劃完成率L16=L14/L13

④ 本年的計劃銷售金額L20=2600000*12

⑤ 本年累計完成金額L21=SUMIFS(數(shù)據(jù)源!$E:$E,數(shù)據(jù)源!$B:$B,'>=2018-1-1',數(shù)據(jù)源!$B:$B,'<='&$C$4)

⑥ 本年累計完成率L22 =L21/L20

⑦ L17和L24是儀表盤的指針,，L18和L25是制作儀表盤的占位輔助數(shù)據(jù)，儀表盤制作方法請看我前面分享的教程《4步完成EXCEL高仿真數(shù)據(jù)儀表盤，你的數(shù)據(jù)看板讓老板眼前一亮》

8、設(shè)置本月每天銷售金額和訂單數(shù)公式,，制作銷售金額折線圖和標識最高最低的訂單柱形圖

① 由于全年各月天數(shù)有28,、30和31天之分，而在制作圖表時,，NA錯誤是只占位不顯示的,，所以我們要運用這個特點設(shè)計公式，不論天數(shù)多少圖表顯示都沒問題,。

② 本月第1天日期C13 =IF(($C$3+ROW(1:1)-1)>$C$4,NA(),($C$3+ROW(1:1)-1))

③ 本月第1天銷售金額D13 =IF(($C$3+ROW(1:1)-1)>$C$4,NA(),SUMIFS(數(shù)據(jù)源!$E:$E,數(shù)據(jù)源!$B:$B, C13))

④ 選中第1至31天的銷售金額插入折線圖,，制作如下圖所示的折線面積圖，為了金額顯示清晰,，這里坐標軸的單位用的是萬元,。

⑤ 本月第1天訂單E13=IF(($C$3+ROW(1:1)-1)>$C$4,NA(),COUNTIF(數(shù)據(jù)源!$B:$B,C13))

⑥ 由于我們要將最多和最少的訂單標識出來，所以要增加輔助列

最多訂單F13=IFERROR(IF(E13=$C$9,E13,0),0)

最少訂單G13=IFERROR(IF(E13=$C$10,E13,0),0)

⑦ 用訂單數(shù),、最多訂單和最少訂單三列數(shù)據(jù)制作如下柱形圖,，此圖的具體制作方法見我前面分享的教程《EXCEL動態(tài)標注最高和最低值的圖表，讓你一眼看出誰的業(yè)績最好,！》

9,、設(shè)置排行版公式,，制作排行版條形圖

① C55=SUMIFS(數(shù)據(jù)源!$E:$E,數(shù)據(jù)源!$C:$C,$B55,數(shù)據(jù)源!$J:$J,月報計算!$C$2)

② F55=LARGE($C$55:$C$59,ROW(A1)),，其中，LARGE(array,k),，返回數(shù)據(jù)集中第K個最大值,。ROW(A1)返回是1，當公式往下填充時依次得出第1,、第2個……最大值,。

③ 用LOOKUP函數(shù)根據(jù)F列的數(shù)據(jù)查找出第1、第2個……最大值對應(yīng)的商品情況,。E55=LOOKUP(1,0/($C$55:$C$59=F55),$B$55:$B$59)

④ 用E55:F59數(shù)據(jù)插入條形圖,，并設(shè)置好圖形的格式和標簽等。

⑤ 同樣的方法可以制作區(qū)域排行版,、城市排行版和業(yè)務(wù)員銷售排行版條形圖,。

⑥ LOOKUP函數(shù)的詳細教程可關(guān)注后回看我前面分享的詳細教程,，條形圖的美化設(shè)置可回看我前面分享的圖表教程。

第三步：設(shè)計日報看板布局,。根據(jù)展示要求和美觀考慮,，設(shè)計如下所示的布局。

第四步：根據(jù)布局情況，插入日期調(diào)節(jié)按鈕,，相關(guān)數(shù)據(jù)和圖表,。

1、用文本框?qū)崿F(xiàn)月報大字報數(shù)據(jù)顯示,。

其中顯示1月的文本引用的是【月報計算】工作表中C2單元格的數(shù)據(jù),。同樣的方法將本月銷售額、訂單數(shù),，與上單日最高和最低的金額,、訂單都用文本框?qū)崿F(xiàn)。與上月對比情況用粘貼為鏈接的圖片實現(xiàn),。

2,、 插入日期調(diào)節(jié)按鈕,。

點【開發(fā)工具】→【插入】→在表單控件中點擊【數(shù)值調(diào)節(jié)鈕（窗體控件）】→在放置位置劃出調(diào)節(jié)按鈕→右鍵→【設(shè)置控件格式】→在彈出的【設(shè)置對象格式】對話框中點【控制】→【當前值】任意設(shè)為1-12之間的值→【最小值】設(shè)為1→【最大值】設(shè)為12，因為1年最多12個月→【單元格鏈接】點右邊的小紅箭頭后再點選【月報計算】工作表的C2單元格,。

第五步： 將【月報計算】工作表中已經(jīng)做好的排行版圖復(fù)制粘貼到相應(yīng)位置,。

第六步：調(diào)整配色,，美化看板。