純Excel圖表制作高仿真儀表盤.doc

純Excel圖表制作高仿真儀表盤作者：ExcelPro文章來源：ExcelPro的圖表博客點擊數(shù)：603更新時間：2011-6-29 這是前一篇日志雙餅圖制作儀表盤的前身版本，格式化效果更加豐富、豪華。由于是前一做法的前身版本，文字略有雷同，請理解。-一、什么是儀表盤 儀表盤（guage，speedometer，dial chart，dashboard），是模仿汽車速度表的一種圖表，常用來反映預(yù)算完成率、收入增長率等比率性指標。它簡單、直觀，人人會看，是商業(yè)面板（Dashboard）最主要特征的圖表類型。一看到儀表盤，就會使人體驗到?jīng)Q策分析的商務(wù)感覺。 在Excel圖表之道中曾介紹了一種模仿制作儀表盤的方法，其思路是使用餅圖+圖片填充的方式，需要從水晶易表等軟件中摳出空表盤圖片，填充到圖表的繪圖區(qū)作為背景。 這種方法雖然簡單，但也有缺點，一是空表盤圖片不易獲得，二是表盤圖片無法編輯，也就不方便自定義表盤樣式。本文介紹一種完全使用Excel圖表的實現(xiàn)方式，具有高度仿真、可靈活自定義表盤樣式的特點。二、作圖思路 想象儀表盤的外圈、預(yù)警色帶、刻度標簽，都是一圈一圈的，因此我們可以使用環(huán)形圖及其標簽來制作，指針則使用一個透明餅圖來模仿。三、作圖步驟 首先組織如下圖的作圖數(shù)據(jù)。 B列為刻度標簽，是我們想顯示在表盤刻度線邊的數(shù)字，CE列數(shù)據(jù)為用來模仿外觀和定位標簽的輔助數(shù)據(jù)，分別負責內(nèi)圈標簽、預(yù)警色帶、外圈刻度線的位置。 一般儀表盤的指針范圍從左下到右下，幅度是270度，10等分的話，就是10個27的數(shù)據(jù)，外加一個90度的數(shù)據(jù)，合計360度。為了讓刻度標簽正好顯示在刻度線上，我們將0和27交叉組織，到時好利用0數(shù)據(jù)點的標簽來顯示刻度標簽。而色帶的數(shù)據(jù)，270度范圍可以用一個數(shù)據(jù)覆蓋，便于一次填充漸變顏色。也可以使用10個27和90，或?qū)嶋H需要的數(shù)值區(qū)間，這樣可以分段設(shè)置預(yù)警色帶。 1、使用CE列的數(shù)據(jù)生成一個環(huán)形圖，設(shè)置第一扇區(qū)起始角度位225度，也就是指標為0時指針的位置。設(shè)置內(nèi)徑大小為70%左右。 2、對圖表進行一些格式化。 外圈序列用來模擬表盤的外框，使用白色框線，淡色填充?；蚝谏畛?，隨自己定義。 中圈序列用來模仿表盤的預(yù)警色帶，270度那個大數(shù)據(jù)點使用紅-綠漸變填充，其他數(shù)據(jù)點無框線、無填充色，隱藏。 內(nèi)圈序列用來輔助顯示刻度標簽，先使用xy chartlabeler工具設(shè)置其數(shù)據(jù)標簽顯示為B列的數(shù)據(jù)，注意這時數(shù)據(jù)標簽剛好與外圈刻度線對齊。然后設(shè)置內(nèi)圈無框線、無填充色，隱藏。 這時一個空的儀表盤已經(jīng)出現(xiàn)。 3、將J列的數(shù)據(jù)加入圖表做餅圖。 G7和H7分別是指標的名稱和值，如營業(yè)收入，H9為目標值，則收入完成率=H7/H9，J7將其折算成270度為100%的角度數(shù)，J8用來模擬指針，J9用來占位。 我們將用J列的數(shù)據(jù)加入圖表來制作一個餅圖，以模擬儀表盤的指針。操作方法：復(fù)制J列-選中圖表-選擇性粘貼-新建序列，這時該序列是一個環(huán)形圖，將其圖表類型修改為餅圖，設(shè)置餅圖的第一扇區(qū)起始角度也為225度。 4、調(diào)整餅圖的大小。 這時餅圖蓋住了之前的圓環(huán)圖，不要緊，我們將要調(diào)整它的大小并設(shè)置其為透明。 餅圖的大小將決定指針的長短。這里需要一點小操作技巧。 1）先選中整個餅圖序列，按住鼠標往外拉，使餅圖呈分離狀，至合適大小。 2）再逐一選擇單個扇區(qū)，按住鼠標往里拉，使餅圖回復(fù)到聚攏狀態(tài)。對于為0的那個扇區(qū)，為方便選中操作，可臨時性將J8的數(shù)據(jù)修改為20，待操作完成后再修改回去。 完成后若半徑長度不符合要求，可以反復(fù)重來直至餅圖半徑長度合適。若是Excel2007/2010，應(yīng)可在其格式對話框中直接設(shè)置分離的百分比為20%。 5、格式化餅圖模擬指針。 將餅圖的J8扇區(qū)設(shè)置為紅色邊框，無填充色，其他扇區(qū)設(shè)置為無邊框無填充色，隱藏。當把J8改回0時，該扇區(qū)正好像一個指針。 6、鏈接儀表盤顯示值。 將內(nèi)圈序列90度數(shù)據(jù)點的標簽往上移，粗體，作為儀表盤的數(shù)字顯示。注意，我們在數(shù)據(jù)組織時已將其數(shù)據(jù)連接為H7的輸入指標。也可以在這時讓其直接鏈接H7的數(shù)據(jù)。 設(shè)置外圈序列90度的數(shù)據(jù)點顯示標簽，并鏈接至G7的指標名稱。若指標名稱過長，數(shù)據(jù)標簽易折行，可另使用文本框并鏈接至G7。 7、繪制一個圓圈，放在中心點，模仿指針的固定鈕。 OK！至此我們已經(jīng)完成了一個純圖表制作、高度仿真、且可以充分自定義樣式的儀表盤。試著調(diào)整F7 的數(shù)值，可以看到儀表盤的指針將跳到相應(yīng)位置。四、儀表盤的個性化樣式設(shè)置 由于本方法完全采用純圖表來制作儀表盤，具有非常強大的自定義樣式能力，幾乎所有元件都可以自定義格式。譬如： 1）可以對外框使用仿金屬質(zhì)感的填充效果，圖。如果不告訴你，你相信這些圖表是純Excel制作的嗎？ 2）可以調(diào)整刻度線的密度，這由C列數(shù)據(jù)決定，如改為每塊13.5來組織。 3）可以對預(yù)警色帶使用綠-紅漸變效果，以適用于高值不好的情況。 4）可以對預(yù)警色帶使用分段填色效果，這只需要將中圈序列的數(shù)據(jù)源改到C列或E列即可。事實上，所有輔助數(shù)據(jù)均可只使用C列，重復(fù)粘貼即可。 5）可以對預(yù)警色帶按指定數(shù)字區(qū)間填色，這只需要將中圈序列的數(shù)據(jù)源修改為相應(yīng)的折算數(shù)值即可。 6）可以修改設(shè)定刻度標簽為相應(yīng)的數(shù)字或文本，這只需要修改B列的數(shù)據(jù)。 7）可以調(diào)整指針的長短和粗細，這在前面第三點的第4/5步調(diào)整餅圖大小時控制。 8）可以調(diào)整外圈、預(yù)警色帶、刻度標簽的位置，這只需要調(diào)整圓環(huán)圖的系列次序。 幾乎你可以想到的樣式效果，差不多都可以格式化出來。尤其是使用2007/2010版本時，格式化效果更加豐富，需要的只是你的創(chuàng)意和美感。（以下分別為嘗試的簡單風格、金屬風格、鋼板風格） 是不是很酷？據(jù)我所知，此方法為本博客首創(chuàng)，也是做法最簡單、效果最逼真的儀表盤做法，值得得瑟一下，呵呵。五、儀表盤的應(yīng)用 怎樣讓這個儀表盤圖表可以方便地應(yīng)用到其他報告中呢？ 為方便后續(xù)應(yīng)用儀表盤圖表，我們將CE列輔助數(shù)據(jù)轉(zhuǎn)換為靜態(tài)數(shù)組。方法：選擇某個序列的圖形，鼠標定位到公式欄，按F9鍵，此時該序列公式即變化為一組靜態(tài)數(shù)據(jù)。以預(yù)警色帶的數(shù)據(jù)源為例： =SERIES(Sheet1!$D$6,Sheet1!$C$7:$C$29,2) -轉(zhuǎn)換為了： =SERIES(預(yù)警色帶,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,270,18,54,18,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2) 依次對中圈、內(nèi)圈序列作同樣處理。這樣圖表將不再依賴于CD列的數(shù)據(jù)了。 在需要創(chuàng)建新的儀表盤時，前面所有的步驟都不需要了。我們只需要復(fù)制這個圖表到自己的工作表中，并將圖表中餅圖序列的數(shù)據(jù)源調(diào)整為相應(yīng)的數(shù)據(jù)源即可