6.2 數據透視圖表：製作項目獎金情況動態看板

6.2

數據透視圖表：製作項目獎金情況動態看板

6.2.1案例情況介紹

在某互聯網公司中有多名開發人員，他們會針對產品出現的各類不同問題進行開發並完成修復。當BUG修復后，會根據BUG類型的不同對相關人員發放獎金，項目獎金金額=BUG類型的單價×核定的標準工時。在過去的一年中，這樣的BUG修復記錄多達幾千行，在年終時對於每個人的項目獎金情況要進行可視化的呈現：每位員工在各類型BUG修復中的完成情況和個人在團隊中的佔比情況（圖6-1中顯示了部分數據情況）。

圖6-1

看到下面這一張效果圖（見圖6-2），有沒有感覺跟日常做的報表不一樣？此報表的底色沒有用Excel默認的白色背景，而是改成了深藍色，這讓整個看板充滿了設計感與科技感。是的，這就是顏色的魅力！如果你的BI看板是放置在電子屏、會議室的大屏幕上進行展示的，就可以選擇類似於這種「深色底紋、淺色字體」的配色方案。當然這個配色方案可以根據公司的Logo顏色或自己的喜好進行調整，但在配色方案的選擇上，還是建議大家按照第1章的內容多參考一些成功的設計方案為宜。

圖6-2

首先，我們來分析一下這張圖表的整體結構，包括選項控制項組合框條形圖、圓環圖、表格、小火箭圖形等元素，並且通過單擊控制項組合框可以查看到不同人員的圖表數據，實現動態圖表的效果。

如何實現這種動態聯動更新的效果呢？思路決定出路，每一個精美的圖表背後都有3個最基礎的數據來支撐：數據源、參數、報表。數據源是匯總業務流水的台賬，方便我們日後查看業務數據的增減變化；參數是一些業務之間共性存在的基本規則，比如A、B、C、D、E、F各類不同BUG問題的開發單價等；報表則是用於呈現數據統計結果的各類表格、圖表、動態圖表等，可讓我們的數據進行有效的可視化呈現。

針對Excel台賬的數據源，我們還需要計算出具體的項目獎金情況，這需要用到與函數相關的內容。接下來我們就一同開始製作本節的案例吧。

6.2.2整理數據源

①將數據源設置為超級表（見圖6-3）后，核定各記錄的獎金金額。

②計算核定獎金（單價）。在G列中輸入核定獎金（單價），其中G2單元格的公式=IF（（@是否完工）=＂完成＂，VLOOKUP（（@BUG類型），$K$2：$L$7，2，0），0）（見圖6-4）。

圖6-3

圖6-4

③計算獎金。在H列中的數據為獎金（即最終發放給個人的項目獎金），其中H2單元格的公式=IF（（@是否完工）=＂完成＂，VLOOKUP（（@BUG類型），$K$2：$L$7，2，0）*（@核定工時），0）（見圖6-5）。

圖6-5

說明：在使用超級表編寫的函數公式中，公式中的引用參數不直接顯示單元格的地址，而是顯示為@欄位名。超級表公式的編寫具有以下3個特點：

·當輸入一個公式時，下面的單元格會自動填充公式，不需要再去手動填充公式。

·當需要增加多行新數據時，格式也能保持與以前的一樣，並且有公式的列也會自動填充，直接呈現計算結果。

·在新增內容后，已做好的透視表不用再去更改數據源區域，只要單擊刷新就可以了。

6.2.3製作數據透視表

1.創建透視表

選中數據源中的任意一個有字單元格，單擊【插入】選項卡中的【數據透視表】，在彈出的對話框中選中【新工作表】，單擊【確定】按鈕（見圖6-6）。此時Excel會以選中的單元格為起點，向四周擴散選中到一個連續的表格區域，即以前面新建的超級表作為數據源區域，並將選擇的超級表表名（即表1）顯示在數據來源窗口中。

2.設置透視表的佈局

在數據透視表裏將【BUG類型】放到【列】區域中，將【開發人員】放到【行】區域中，將【核定工時】放到【值】中。設置完成後，也便快速地統計出了各個開發人員已完工的項目獎金統計表（見圖6-7）。

圖6-6

圖6-7

6.2.4製作圖表看板

1.製作背景板

新建一張工作表，選中行列標籤交叉位置處的小三角，即快速選中整張表格。利用ColorPix工具取色，將表格底色【形狀填充】顏色的參數值調整為RGB色值：5，12，54，即深藍色（見圖6-8）。

圖6-8

2.製作繪圖數據源

①在第1行和第1列內輸入表頭和姓名，並設置文字的字體、字型大小、顏色等，其中字體顏色為亮藍色，利用ColorPix工具取色，即RGB色值：0，176，240（見圖6-9）。

圖6-9

將相應的數據從透視表中運用VLOOKUP函數匹配進去，在B2單元格內輸入公式=VLOOKUP（$A2，＇2透視表＇！$A：$H，COLUMN（B1），0）。

說明：COLUMN是一個簡單的輔助函數，即COLUMN（reference），其中reference為需要得到其列標的單元格或單元格區域。這裏我們使用到的COLUMN（B1）是指引用的第2列，即計算的結果等於2來取代VLOOKUP函數的第3個參數匹配結果的位置。這樣方便我們在向右拖曳快速複製公式時，VLOOKUP函數引用的列號能夠隨着列的變化而變化（見圖6-10）。

圖6-10

②將公式批量填充進其他單元格，選中要填充的B2：G10單元格區域，在B2編輯欄的公式處，按住Ctrl+回車（Enter）組合鍵，完成批量填充（見圖6-11）。

圖6-11

③一鍵快速計算合計金額的方法如下：選中A1：H11區域，按住Alt+=組合鍵，實現快速批量求和。此時，已經完成了最右側獎金總額列的結果計算和最下面的合計行的計算（圖6-12）。

④美化表格。選中數據源中的第1行，單擊滑鼠右鍵，選擇【設置單元格式】，在打開的【設置單元格格式】對話框中選擇【邊框】，設置直線樣式為虛線，【顏色】為灰色，【邊框】位置為下邊框。設置好第1行后，單擊【格式刷】按鈕，單擊倒數第2行，使其應用相同的格式效果（見圖6-13和圖6-14）。

圖6-12

圖6-13

圖6-14

3.製作微圖表：迷你圖與條件格式

①插入迷你圖。選中B12單元格，在【插入】選項卡的【迷你圖】功能組中單擊【柱形】按鈕（見圖6-15）。

圖6-15

②設置迷你圖的範圍。在彈出的【編輯迷你圖】對話框中，【數據範圍】選擇的是B2：B10單元格區域，單擊【確定】按鈕。此時在B12單元格內已經生成了一個迷你柱形圖，並且它是能夠隨着行列的大小變化而變化的（見圖6-16）。

圖6-16

③快速填充迷你圖。生成第一個迷你柱形圖后，選中B12單元格右下角的十字句柄+，向後拖動到H12單元格內，讓每一列的數據都快速生成迷你柱形圖（見圖6-17）。

④美化迷你圖。選中所有迷你柱形圖所在的單元格區域，單擊【設計】選項卡，可以通過單擊迷你柱形圖按鈕右側的小三角，快速調整迷你圖的顏色，或者直接通過【樣式】功能區調整迷你圖的樣式。通過單擊【標記顏色】右側的小三角，選擇【高點】，將顏色設置為亮藍色（見圖6-18）。

圖6-17

圖6-18

⑤設置條件格式（另一種嵌入單元格內的微圖表）。在【獎金總額】處設置數據條效果。選中H2：H10單元格區域，單擊【開始】選項卡，之後單擊【條件格式】→【數據條】→【淺藍色數據條】。此時單元格中已經生成了類似於條形圖效果般的數據條了。如果你手動更改數據源表中的內容就會發現，數據條的條件格式和迷你圖都會隨着單元格數值的變化而變化（見圖6-19）。

⑥條件格式的美化。若在數據呈現時無須顯示數據條上的數字，而只顯示數據條的話，只需單擊【開始】選項卡，之後單擊【條件格式】→【管理規則】，找到對應的條件格式規則后，單擊【編輯規則】，在彈出的【編輯格式規則】對話框中，勾選【僅顯示數據條】複選框，單擊【確定】按鈕（見圖6-20）。

圖6-19

圖6-20

4.製作控制項並關聯數據源

①插入【姓名】的組合框控制項：單擊【開發工具】選項卡，之後單擊【插入】→【組合框（窗體控制項）】，在表格的空白區域，拖曳繪製一個組合框控制項（見圖6-21）。

②繪製完成後，選中控制項，單擊滑鼠右鍵，選擇【設置控制項格式】（見圖6-22），在彈出的【設置對象格式】對話框中設置【數據源區域】，選擇A2：A10單元格區域；設置【單元格鏈接】，選擇J10單元格。設置完成後，單擊【確定】按鈕（見圖6-23）。

圖6-21

圖6-22

圖6-23

③將J10單元格的字體顏色設置為白色，方便我們後面進行關聯計算。設置完畢后，我們單擊組合框控制項，選擇不同的姓名，則會在J10單元格中顯示該姓名處在A2：A10單元格區域中的第幾位。比如選擇「表姐」，則J10單元格顯示2（見圖6-24）。在J12單元格中輸入公式=INDEX（A2：A10，J10），即在A2：A10的範圍內返回J10單元格的值所在位數的姓名。比如J10單元格顯示2，則INDEX計算的結果返回的是「表姐」（見圖6-24）。

圖6-24

④在K12單元格中輸入公式=VLOOKUP（$J$12，$A$2：$H$10，COLUMN（B2），0），然後拖動至Q12單元格填充公式（見圖6-25）。

圖6-25

5.製作動態柱形圖

①選中K12：Q12，單擊【插入】選項卡，之後單擊【二維柱形圖】→【簇狀柱形圖】。此時Excel根據選定的數據源，自動插入一張默認的Excel柱形圖（見圖6-26）。

②選中圖表區域中的垂直坐標軸，單擊滑鼠右鍵，選擇【設置坐標軸格式】，在右側的窗格中，將【坐標軸選項】的最大值調整為800。這樣做是為了避免選擇不同人員姓名時出現非統一標準的柱形圖，對讀圖者造成視覺上的誤解（見圖6-27）。

③刪除圖表標題、網格線、垂直坐標軸，設置圖表的顏色為無填充，字體為微軟雅黑，字體顏色為白灰色（見圖6-28）。

圖6-26

圖6-27

圖6-28

④選中藍色的柱形，在右側【設置數據系列格式】窗格的【系列選項】中，將【分類間距】調小一些，比如調整為127%，使得柱形圖變得寬一些（見圖6-29）。

圖6-29

⑤再設置柱形圖的漸變填充效果：在右側【設置數據系列格式】窗格的【填充與線條】選項卡中，將填充模式更改為漸變填充，線性方向設置為自上而下，顏色設置為亮藍色到深藍色。選中圖表區域周圍的邊界點，將圖表調整到合適的大小和位置區域，即完成柱形圖的繪製（見圖6-30）。

圖6-30

說明：下面計算部門獎金總額（見圖6-31）。

更改透視表求和的值為獎金后，我們發現柱形圖的數據顯示異常。這就是使用透視表來作為圖表數據源的好處，當有任何數據變化時，所有的圖表調整也隨之更新（見圖6-32和圖6-33）。

圖6-31

圖6-32

此時我們只需重新設置柱形圖坐標軸的範圍區間，即在【設置坐標軸格式】窗格中將最大值調整為「100000」，則柱形圖又呈現出之前設置好的樣子了（見圖6-33）。

圖6-33

6.製作動態圓環圖

①在J13單元格內輸入文字「部門獎金總額」。在K13單元格內輸入公式=SUM（表1（獎金）），就是對數據源表格的獎金列的所有數據進行匯總求和（見圖6-34）。

圖6-34

②在L13單元格內輸入文字「個人比率」。在M13單元格內輸入公式=Q12/K13，這是指每一位員工的獎金總額占部門獎金的比率（見圖6-35）。

圖6-35

③在N13單元格內輸入公式=1-M13，構建出圓環圖的數據源（見圖6-36）。

圖6-36

④選擇M13：N13單元格區域，單擊【插入】選項卡，之後在【圖表】中單擊【餅圖】→【圓環圖】（見圖6-37）。

圖6-37

⑤美化圓環圖。刪除圖表標題、圖例，選中整個圖表，將【形狀填充】設置為【無顏色】，【形狀輪廓】邊框設置為【無邊框】。再選中繪圖中的環形，將【形狀輪廓】邊框設置為【無邊框】（效果參見圖6-38）。

圖6-38

⑥設置圓環圖的配色方案。僅選中藍色部分的圓環（即單擊兩次選中該部分），在【設置數據點格式】窗格中將顏色設置為漸變填充。然後，僅選中橙色部分的圓環，調整其填充顏色為色板中已有的亮藍色並將透明度更改為84%。從而使圖表呈現出一種有數據為亮藍色、無數據為半透明的效果（見圖6-39）。

圖6-39

⑦將圓環圖的幅寬設置得大一些。選中圓環圖后，在【設置數據系列格式】窗格中將【圓環圖內徑大小】更改為62%。完成後，調整圓環圖的大小和位置，使其放置在看板中合適的位置（見圖6-40）。

圖6-40

6.2.5完善看板

具體步驟如下。

①製作標題引用動態數據來源。

在O13單元格中輸入公式=J12&＂的獎金比率：＂&ROUND（M13，2）*100&＂%＂。這個公式的作用是將下一步文本框中需要顯示的內容先固化在一個單元格中，使其成為文本框顯示的來源（見圖6-41）。

圖6-41

②繪製一個文本框，並在編輯欄里輸入=$O$13。這時文本框裏就出現了相應的數據，更改文本框的背景顏色為無顏色，邊框顏色設置為無顏色，並修改其字體、字型大小及顏色（見圖6-42）。

圖6-42

將N13和O13中輔助數據的字體設置為與看板底色一樣的深藍色，即起到類似於隱藏的效果，使得整個看板的版面更加整潔（見圖6-43）。

圖6-43

③插入Logo。在本例中，以小火箭圖片進行演示。從示例文件中選擇小火箭的圖片，也可以根據自己的需求放置其他圖片，將其插入Excel中（見圖6-44）。

圖6-44

默認插入的圖片會有白色的底色，這與本例中商務圖表的整體風格相異。因此，我們要將圖片中的圖案摳出來。選中圖片，單擊【格式】→【刪除背景】，此時圖片會將需要刪除的區域塗抹成「玫紅」色塊（見圖6-45）。

圖6-45

單擊【標記要保留的區域】，將不刪除的地方標記出來。設置完畢后，單擊【保留更改】，即可通過Excel完成快速摳圖（見圖6-46和圖6-47）。

圖6-46

圖6-47

調整圖片大小后將其放置到合適的位置，即可完成本例所有圖表看板的繪製工作了（見圖6-48）。

小結：大家初見這張看板時，會感覺其中的元素很多。但只要做過一次，就會知道製作過程其實並沒有大家想像中那麼難。同理，本例中商務圖表的製作思路，還可用在你工作中其他數據的有效呈現上。

圖6-48