5.7 【生產】動態圖表：車間產能看板

5.7

【生產】動態圖表：車間產能看板

5.7.1產能數據的特點

在製造業，尤其是離散型製造業中，實時了解產能的情況是科學排產、發揮產能最大化的重要信息手段之一。因此，有效地將產能情況進行可視化的呈現也是產能類的商務圖表的製作目標所在。

在本節中，我們將模擬某生產車間中各班組、各產品完工情況，以梳理其實際產能與目標完成率情況；並通過控制項工具製作人機互動式的動態圖表——根據操作者選擇的內容，呈現出與之對應的圖表效果。此外，還將剖析儀錶盤類商務圖表的具體製作方法。

5.7.2動態圖表的應用介紹

在企業中，各個管理系統平台、資料庫之間存在三大應用困難：

·業務系統數據過於分散，而通過軟體公司二次開發的方式構建統一報表平台的成本較高，且客戶化開發的軟體功能固化，需求擴展性差。

·市場環境和業務需求多變，無法一次性窮舉出所有的圖表信息展示需求，無法快速地滿足企業不斷變化的報表需求。

·使用專業的編程軟體，對操作者個人的應用水平要求較高。而且這類人員大多為IT業出身，在滿足業務需求實踐時需要付出大量的溝通成本。

而使用Excel來製作動態圖表具備了簡捷、專業、靈活3個重要特性，使其被越來越多的企業廣泛應用，作為企業數據管理可視化呈現的有效手段之一。

·簡捷：通過Excel商務圖表製作的動態圖表是完全基於Excel環境製作的，日常辦公人員非常熟悉，沒有二次學習成本。而且，能夠通過控制項、數據有效性等與函數的結合，實現「所見即所得」的人機互動式報表的呈現。

·專業：各類型的Excel商務圖表，都可以基於Excel的環境進行設計和實踐，能夠製作出各類中國式的複雜報表，並且能夠實現與數據源的填寫、錄入實時聯動。其數據準確、圖表高效。

·靈活：數據源的更新與商務圖表的生成，支持根據管理者的不同維度進行關聯和聯動。可以隨著管理動作的漸進明細，即隨著各工序的推進、細化、計劃調整與完工情況，逐步添加更多的商務圖表。這樣一來，就避免了固化管理系統擴容難的情況出現。

5.7.3產能看板動態圖表的製作

1.用控制項製作連續的5組計劃產能與實際產能對比柱形圖

具體步驟如下。

①調用【開發工具】選項卡。在默認情況下，Excel頂部的工具欄只有文件、開始、插入、頁面布局、公式、數據、審閱、視圖這8個選項卡，而不包含【開發工具】選項卡（見圖5-66）。因此，需要將其調取出來，方便後面插入控制項等互動式報表用到的工具。

圖5-66

單擊【文件】選項卡中的【選項】，在彈出的【Excel選項】對話框中選擇【自定義功能區】，勾選【開發工具】（見圖5-67）。

圖5-67

單擊【確定】按鈕后，可見工具欄中已經出現了【開發工具】選項卡（見圖5-68）。

②插入控制項。單擊【開發工具】選項卡，之後單擊【插入】→【數值調節按鈕】，按滑鼠左鍵繪製控制項。

圖5-68

在【控制項】中的相關按鈕均可以用插入的方式，像繪製圖形一樣在Excel單元格區域內直接繪製（見圖5-69）。

圖5-69

③修改控制項格式。選中已經插入的【數值調節按鈕】控制項后，單擊滑鼠右鍵，選擇【設置控制項格式】，在彈出的【設置對象格式】對話框中單擊【控制】選項卡，進一步設置控制項的屬性：選擇【單元格鏈接】框后，單擊Excel中的K1單元格，之後單擊【確定】按鈕。這樣實現了在調整控制項上下小箭頭時，其控制的值與單元格的值綁定（鏈接）的效果（見圖5-70）。

④構建繪圖數據源。在工作表頂部第1～3行的位置，我們通過函數的方法構建製作商務圖表的數據源，使其實現在單擊選擇控制項上下小箭頭時，繪圖數據源能夠隨著選擇內容的變化，而關聯到數據源第4～24行中對應內容的效果。

圖5-70

這裡構建的繪圖數據源要實現以當前控制項控制的【班組】為中心（見圖5-71），並包含前後兩個班組的【計劃產能】、【加工總量】一共5個班組的數據情況。

此時，以D1單元格為中心，使其值=K1單元格的值，即綁定了【數值調節按鈕】控制項控制的單元格的值。

然後對其左右兩側單元格分別設置如下。

在B1：F1單元格區域（不包含D1）中分別輸入（D1-2，D1-1，D1+1，D1+2）。

之後採用VLOOKUP函數，查找各組別對應的產值情況：

在B2單元格中輸入複合函數（=IFERROR（（VLOOKUP（B1，$A$4：$C$24，2，0），」」）），並將其應用於B2：F2單元格區域。

注意，如果被查找的【班組】超出數據源範圍已經給定的1～20組之間時，則使用VLOOKUP函數查找出來的結果會出現錯誤；如根據班組序號21，使用VLOOKUP函數查找相應的結果，則會顯示＃N/A的錯誤。此時只需在VLOOKUP函數外部嵌套一個IFERROR函數即可。即，當VLOOKUP函數查詢的結果為錯誤值時，單元格返回的計算結果為＂＂（空）。當然，如果VLOOKUP函數運行的結果不是錯誤值，就返回其查找的對應結果（見圖5-71）。

圖5-71

⑤創建相鄰組別產能情況柱形圖。選中A1：F3單元格區域，單擊【插入】選項卡，之後單擊【圖表】→【堆積柱形圖】進行具體設置（見圖5-72）。

圖5-72

選中圖表區域后，單擊【設計】選項卡，之後選擇【數據】功能組中的【選擇數據】，選擇對數據源的引用，以進行進一步的設置。

在彈出的【選擇數據源】對話框中，選中【班組】系列后，單擊【刪除】按鈕。然後選中【水平（分類）軸標籤】下的【編輯】按鈕，設置其【軸標籤區域】為B1：F1單元格區域。設置完畢后，單擊【確定】按鈕（見圖5-73）。

圖5-73

⑥修改圖表的類型。選中圖表區域后，單擊【設計】選項卡中的【更改圖表類型】，在彈出的【更改圖表類型】對話框中找到【組合】選項。將【計劃產能】、【加工總量】數據系列的圖表類型均修改為【簇狀柱形圖】，單擊【確定】按鈕。

回到圖表操作區域后，選中任意一個數據系列（即柱形圖），然後單擊滑鼠右鍵，選擇【設置數據系列格式】，在右側的【設置數據系列格式】窗格中，將柱形圖的【系列重疊】修改為100%。

⑦柱形圖的美化。選中圖表區域中的【圖表標題】，對其標題進行修改，例如命名為【班組計劃與實際完成情況對比】。

固定坐標軸範圍：因為各組的產能大小情況不盡相同，為了讓各個生產班組的圖表在同一標準之下進行呈現，我們需要將該圖表的坐標軸範圍進行固定。選中坐標軸以後，在右側的【設置坐標軸格式】窗格中，將【坐標軸】範圍設置為0～280。然後清潔圖表版面：刪除坐標軸以及網格線，之後調整柱形圖的填充顏色（見圖5-74）。

⑧添加數據標籤。選中柱形圖中需要添加數據標籤的數據系列后，單擊滑鼠右鍵，選擇【添加數據標籤】→【添加數據標籤】，在此添加數據標籤。選擇已添加的數據標籤，在右側的【設置數據標籤格式】窗格的【標籤選項】中，將【標籤位置】設置為【數據標籤內】。

圖5-74

製作完畢后，可以單擊【數值調節按鈕】，查看到連續5組計劃產能與實際產能對比的動態柱形圖表。

2.製作相鄰兩組產品明細情況對比動態圖表

具體步驟如下。

①製作製圖數據源。在L2單元格中輸入函數=VLOOKUP（$K$1，$A：$I，COLUMN（A4），0），將其向右拖曳至T2單元格。

說明：根據K1單元格的值，即由【數值調節按鈕】控制的組數，在數據源列表區域中查找對應的班組、計劃產能、加工總量以及A/B/C/D/E/F各產品的產值數據。其中，VLOOKUP函數的第三參數返回的列數，使用COLUMN函數嵌套得出（見圖5-75）。

圖5-75

COLUMN函數會返回引用單元格的列號，如A1單元格的列號=1。如果參數中不填寫內容，則返回當前單元格的列號=COLUMN（）。

②計算上一組產能情況。在L3單元格中輸入函數公式=IF（L2-1=0，1，L2-1），確保第3行數據為上一生產班組的實際情況；並使用VLOOKUP函數，將上一組的產值數據查找出來。M3單元格的函數公式=VLOOKUP（$L$3，$A：$I，COLUMN（B5），0）。

③插入柱形圖。選擇O1：S3單元格區域，單擊【插入】選項卡，之後單擊【圖表】→【柱形圖】。插入柱形圖后，選中圖表區域，單擊【設計】選項卡，之後選擇【數據】功能組中的【選擇數據】（見圖5-76）。

圖5-76

在彈出的【選擇數據源】對話框中，將數據系列的名稱分別修改成單元格L2、L3的值，單擊【確定】按鈕（見圖5-77）。

④固定坐標軸範圍。修改坐標軸範圍為0～30，用ColorPix取色器工具，將填充顏色修改為黃、藍色系，並刪除坐標軸、網格線，修改圖表背景和邊框為「無色」，即完成本圖的製作。當單擊【數值調節按鈕】控制項時，圖表發生聯動效果（見圖5-78）。

圖5-77

圖5-78

3.製作儀錶盤類圖表

具體步驟如下。

①建立儀錶盤標識。在K2單元格中計算該組別的完成率，即輸入函數（N2/M2）。然後將該完成率綁定到文本框中予以顯示。

單擊【插入】選項卡中的【文本框】，在Excel空白區域繪製一個文本框。繪製完畢后選中文本框，並在編輯欄中輸入：K1&＂組計劃完成率：＂&ROUND（K2*100，0）&＂%＂（說明：K2單元格計算的是一個小數。將其乘以100即放大100倍以後，通過ROUND函數四捨五入保留到整數位，然後再通過拼接字元串的方法關聯鏈接一個「%」，是將小數在公式顯示結果中以百分比形式進行呈現的有效手段）。

插入完畢后，文本框的填充顏色、邊框均設置為無色，字體、字型大小更改為微軟雅黑10號。

②剪切儀錶盤。在工具表中，將製作好的儀錶盤模板剪切並粘貼到動態圖表的相應位置，修改圖表的相關配色方案，即可完成儀錶盤與文本框的組合。

說明：因為儀錶盤的繪製過程較為複雜，所以按照下文的方式在已經製作完儀錶盤工具后，在工作中遇到相應的場景時，只需重複將其剪切到你需要布局的圖表位置即可。無須從零開始一步步進行設置，這樣可提高商務圖表的製作效率（見圖5-79）。

圖5-79

【儀錶盤的繪製】（見圖5-80）

具體步驟如下。

①插入圓環圖。按照A1：C24單元格區域輸入對應的數值，然後選中A1：C24單元格區域，單擊【插入】選項卡，之後在【圖表】中單擊【餅圖】→【圓環圖】，Excel默認插入一個3層的圓環圖（見圖5-81）。

圖5-80

圖5-81

②修改圓環設置。選擇內部圓環，單擊滑鼠右鍵，之後選擇【設置數據系列格式】，在右側【設置數據系列格式】窗格的【系列選項】中，將【第一扇區起始角度】修改為「225°」，將【圓環圖內徑大小】修改為50%（見圖5-82）。

③簡化圓環圖。刪除圖表標題和圖例，並將最外層和最內層圓環的填充顏色和邊框設置成無色。

④添加百分比刻度。選中最內層圓環，單擊滑鼠右鍵，選擇【添加數據標籤】→【添加數據標籤】，在右側的窗格中設置數據標籤的格式為【無引導線】，並通過拖曳的方式將數據標籤移至相適宜的位置（見圖5-83）。

圖5-82

圖5-83

⑤圓環圖的配色美化。將第二層即中間的那層圓環圖，按照從0%到100%、由淺入深的配色方案，進行顏色填充。注意，將多餘的非儀錶盤的部分設置為無色填充（見圖5-84）。

⑥指針的繪製。製作指針數據源如下。

圖5-84

E1：指針；E2：=E6*200；E3：2（指針的寬度）；E4：=270-SUM（E2：E3）。這裡，在E6單元格中會顯示儀錶盤需要顯示的比率值。而E7單元格中為將其轉化為整數的計算結果，E7的函數公式=ROUND（E6*100，0）。

選中E1：E4單元格區域，單擊【插入】選項卡，之後單擊【圖表】→【餅圖】進行設置（見圖5-85）。

圖5-85

選擇已經插入的餅圖后，單擊內部圓環，之後單擊滑鼠右鍵，選擇【設置數據系列格式】，在右側的【設置數據系列格式】窗格中單擊【系列選項】，修改【第一扇區起始角度】為225°，即與儀錶盤的底部圓環圖的起始角度一致（見圖5-86）。

圖5-86

⑦修改指針的顏色。將除指針之外的扇形區域的填充顏色和邊框修改為無色，將指針修改為你喜歡的顏色，比如藍色。

設置指針餅圖的背景填充顏色與邊框顏色均為無色，然後依次選中【儀錶盤】與【指針】兩張圖表，單擊【格式】選項卡，之後單擊【對齊】→【水平對齊】→【垂直居中】，使這兩張圖表對齊。初步調整后，可進一步拖曳指針圖表的大小，使其居中放置在儀錶盤的中心區域。

⑧製作標籤。單擊【插入】選項卡，依次繪製3個【文本框】，之後分別在編輯欄中輸入「您的業績完成率為：」、「=E7」和「%」。調整文本框的字體、大小和位置，使其放置在儀錶盤的合適位置，並作為標籤顯示當前儀錶盤的百分比情況（見圖5-87）。

圖5-87

⑨插入控制項，控制完成率數值。單擊【開發工具】選項卡，之後單擊【插入】→【數值調節按鈕】。按住滑鼠左鍵拖曳，於合適區域繪製數值調節按鈕。繪製完畢后，選中【數值調節按鈕】控制項並單擊滑鼠右鍵，選擇【設置控制項格式】，在打開的【設置對象格式】對話框的【控制】選項卡中，設置【單元格鏈接】到E7單元格，【最小值】設置為0，【最大值】設置為100。設置完畢后，單擊【確定】按鈕（見圖5-88）。

圖5-88

設置完畢后，你可以單擊【數值調節按鈕】，看到儀錶盤的指針隨著數值的大小變化而變化了。