第7章圖表設(shè)計(jì)和制作7.1任務(wù)書7.2任務(wù)示范7.3知識(shí)拓展

7.1任務(wù)書

1.基本圖表的繪制

繪制4張基本圖表，分別展示4個(gè)產(chǎn)品在不同地區(qū)的銷售情況。通過本任務(wù)熟練掌握?qǐng)D表的繪制方法。

2.餅圖的繪制

餅圖是經(jīng)常使用的一種圖表形式，通過繪制不同的餅圖，掌握三維餅圖、條餅圖的繪制方法。本任務(wù)是繪制反映各項(xiàng)目成本的三維餅圖。

3.動(dòng)態(tài)圖表繪制

動(dòng)態(tài)圖表是一種交互性的圖表形式，通過采用兩種不同方法繪制動(dòng)態(tài)圖表，從而掌握動(dòng)態(tài)圖表的繪制方法。

7.2任務(wù)示范

1.基本圖表的繪制

【任務(wù)實(shí)施】

原始數(shù)據(jù)如表7-1所示。

(1)繪制產(chǎn)品A的簇狀柱形圖，圖表放置于H1:N8區(qū)域，如圖7-1所示。

圖7-1產(chǎn)品A簇狀柱形圖

(2)繪制產(chǎn)品B簇狀柱形圖，為其添加趨勢(shì)線(指數(shù))，圖例位置置于底部。圖表放置在O1:U8區(qū)域，如圖7-2所示。

圖7-2產(chǎn)品B添加趨勢(shì)線

操作步驟：選擇數(shù)據(jù)源，先選擇A1:A8，按住“Ctrl”鍵再選擇C1:C8，選完數(shù)據(jù)區(qū)域，在“插入”選項(xiàng)卡中選擇柱形圖，然后選擇簇狀柱形圖。選擇圖表工具中的“布局”選項(xiàng)卡，單擊“趨勢(shì)線”按鈕，為其添加指數(shù)型趨勢(shì)線；在“布局”選項(xiàng)卡中設(shè)置圖例位置“在底部顯示圖例”，并調(diào)整圖表大小，放置在O1:U8區(qū)域。

(3)繪制產(chǎn)品C條形圖，去掉圖例，設(shè)置坐標(biāo)軸，最小值為300，最大值為1200，主要刻度為200。圖表放置在H11:N18區(qū)域，如圖7-3所示。

圖7-3產(chǎn)品C條形圖

操作步驟：選擇數(shù)據(jù)源，直接選擇D1:D8，在“插入”選項(xiàng)卡中選擇條形圖，然后選擇簇狀條形圖。右鍵選擇數(shù)據(jù)，設(shè)置水平(分類)坐標(biāo)軸，再選擇A2:A8區(qū)域。在“布局”選項(xiàng)卡中設(shè)置圖例為無。選擇底部數(shù)值坐標(biāo)軸，雙擊進(jìn)入坐標(biāo)軸格式設(shè)置，設(shè)置坐標(biāo)軸最小值為固定300，最大值為固定1200，主要刻度設(shè)置為固定200，如圖7-4所示。

圖7-4設(shè)置坐標(biāo)軸選項(xiàng)

(4)繪制產(chǎn)品D的柱形圖，要求去掉圖例和網(wǎng)格線，為華南地區(qū)添加數(shù)據(jù)標(biāo)簽，將該地區(qū)及銷售額顯示其上。圖表放置在O11:U18區(qū)域，如圖7-5所示。

圖7-5產(chǎn)品D添加數(shù)據(jù)標(biāo)簽

操作步驟：選擇數(shù)據(jù)源，先選擇A1:A8，按住“Ctrl”鍵再選擇E1:E8，插入簇狀柱形圖。在“布局”選項(xiàng)卡中設(shè)置圖例為“無”，網(wǎng)格線設(shè)置為“無”。選中華南這個(gè)柱子，右鍵設(shè)置添加數(shù)據(jù)標(biāo)簽，在添加的標(biāo)簽上再單擊右鍵，打開“設(shè)置數(shù)據(jù)標(biāo)簽格式”對(duì)話框，如圖7-6所示。

圖7-6“設(shè)置數(shù)據(jù)標(biāo)簽格式”對(duì)話框1

(5)繪制迷你圖，如圖7-7所示。

圖7-7迷你圖

操作步驟1：選中F2單元格，在“插入”選項(xiàng)卡中選擇“迷你圖”命令組中的折線圖，然后選擇數(shù)據(jù)區(qū)域B2:E2單元格，向下填充，即可繪制迷你折線圖(反映這4種產(chǎn)品在不同地區(qū)的銷售情況)。

操作步驟2：選中B9單元格，在“插入”選項(xiàng)卡中選擇“迷你圖”命令組中的柱形圖，然后選擇數(shù)據(jù)區(qū)域B2:B8單元格，向右填充，即可繪制迷你柱形圖(反映不同地區(qū)這4種產(chǎn)品的銷售情況)。

2.餅圖的繪制

繪制反映各項(xiàng)目成本的三維餅圖，最終效果如圖7-8所示。

圖7-8各項(xiàng)目成本圖

【任務(wù)實(shí)施】

(1)繪制各項(xiàng)目成本費(fèi)用構(gòu)成餅圖。

各項(xiàng)目成本的數(shù)據(jù)信息如表7-2所示。

操作步驟1：選擇數(shù)據(jù)，再選擇項(xiàng)目列，按住“Ctrl”鍵選擇合計(jì)列。

操作步驟2：插入三維餅圖，將項(xiàng)目B這個(gè)餅向外拉。

操作步驟3：為餅圖添加數(shù)據(jù)標(biāo)簽，設(shè)置圖例位置為“左邊”，如圖7-9所示。

圖7-9設(shè)置數(shù)據(jù)標(biāo)簽格式

(2)繪制企業(yè)支出費(fèi)用復(fù)合條餅圖，如圖7-10所示。

要求根據(jù)公司支出費(fèi)用表，繪制條餅圖，設(shè)置第二區(qū)域有5個(gè)數(shù)據(jù)。

圖7-10條餅圖

操作步驟1：通過鼠標(biāo)選擇數(shù)據(jù)，包含標(biāo)題行，要求選中除了“其他”這行的所有行，并按住“Ctrl”鍵進(jìn)行加選。

操作步驟2：在“插入”選項(xiàng)卡中選擇餅圖再選擇條餅圖，單擊右鍵設(shè)置數(shù)據(jù)系列格式，設(shè)置第二繪圖區(qū)包含5個(gè)值，如圖7-11所示。

操作步驟3：修改標(biāo)題文字為“人工費(fèi)占支出份額最大”。

操作步驟4：單擊右鍵設(shè)置數(shù)據(jù)標(biāo)簽格式，在對(duì)話框中設(shè)置標(biāo)簽，包括類別名稱、百分比、顯示引導(dǎo)線，標(biāo)簽位置為“最佳匹配”，如圖7-12所示。并手工將“其他”改成“人工費(fèi)”，效果如圖7-12所示。圖7-11第二個(gè)條形圖設(shè)置圖7-12“設(shè)置數(shù)據(jù)標(biāo)簽格式”對(duì)話框2

(3)繪制動(dòng)態(tài)客戶應(yīng)收賬分布餅圖。

原始數(shù)據(jù)如表7-3所示。

繪制的客戶應(yīng)收賬分布圖如圖7-13所示。

圖7-13客戶應(yīng)收賬分布圖

操作步驟1：設(shè)計(jì)輔助表格。復(fù)制標(biāo)題行，選擇性粘貼，再選擇“轉(zhuǎn)置”，將一行標(biāo)題粘貼為一列，如圖7-14所示。

圖7-14轉(zhuǎn)置結(jié)果示意圖

操作步驟2：在“開發(fā)工具”選項(xiàng)卡的控件命令組中選擇插入組合框，并單擊右鍵設(shè)置控件格式，設(shè)置數(shù)據(jù)源區(qū)域?yàn)椴僮鞑襟E1轉(zhuǎn)置得到的輔助列，并將組合框的圖層次序設(shè)為“置于頂層”，如圖7-15所示。

圖7-15設(shè)置組合框的控件格式

操作步驟3：復(fù)制客戶A到客戶D到D10:D13，在其右側(cè)選擇E10:E13，輸入“=INDEX($B$2:$E$5，，$C$10)”，然后使用“Ctrl?+?Shift?+?Enter”組合鍵得到結(jié)果。INDEX函數(shù)能返回行列交叉的數(shù)據(jù)。

操作步驟4：選擇數(shù)據(jù)源D10:E13，在“插入”選項(xiàng)卡中選擇餅圖，設(shè)置數(shù)據(jù)標(biāo)簽，如圖7-16所示。

操作步驟5：修改圖表標(biāo)題為“客戶應(yīng)收賬分布圖”，設(shè)置圖表背景為“純色填充”，如圖7-17所示。

圖7-16設(shè)置數(shù)據(jù)標(biāo)簽格式圖7-17設(shè)置圖表區(qū)格式

操作步驟6：將圖表蓋住輔助表格，將組合框移到圖表中某個(gè)區(qū)域，用于選擇不同信用期限，動(dòng)態(tài)展示客戶的應(yīng)收賬分布圖。

3.動(dòng)態(tài)圖表繪制

【任務(wù)實(shí)施】

(1)通過輔助表格繪制動(dòng)態(tài)圖表，在“動(dòng)態(tài)圖表”工作表中繪制圖表。

原始數(shù)據(jù)如表7-4所示。

輔助表格如表7-5所示。

操作步驟1：設(shè)計(jì)輔助表格，G2放置組合框的返回值；I列數(shù)據(jù)為組合框的數(shù)據(jù)源，復(fù)制并選擇性粘貼，轉(zhuǎn)置4個(gè)產(chǎn)品名稱；H列數(shù)據(jù)為引用原始數(shù)據(jù)的某一個(gè)產(chǎn)品的數(shù)據(jù)。此處使用INDEX函數(shù)，在H1單元格輸入公式“=INDEX($B1:$E1，$G$2)”，向下填充，即可引用原始數(shù)據(jù)的某一個(gè)產(chǎn)品的銷售數(shù)據(jù)。

操作步驟2：在開發(fā)工具中插入組合框，設(shè)置組合框的控件格式，如圖7-18所示。圖7-18設(shè)置控件格式

操作步驟3：利用這個(gè)輔助列數(shù)據(jù)，繪制動(dòng)態(tài)圖表。選擇A1:B8，在“插入”選項(xiàng)卡中的“圖表”命令組單擊柱形圖，選擇簇狀柱形圖，生成一張默認(rèn)圖表；選擇數(shù)據(jù)源，點(diǎn)擊“產(chǎn)品”，然后“編輯”，在“系列名稱”里輸入“=Sheet1!$H$1”，在“系列值”里輸入“=Sheet1!$H$2:$H$8”，點(diǎn)擊“確定”按鈕。

將圖表放置于輔助表格之上，蓋住輔助信息，將組合框疊放次序設(shè)置為“置于頂層”，放到相應(yīng)的位置，選擇不同產(chǎn)品進(jìn)行動(dòng)態(tài)觀察。

最終效果如圖7-19所示。

圖7-19最終效果

(2)定義動(dòng)態(tài)名稱，實(shí)現(xiàn)動(dòng)態(tài)圖表的繪制。

操作步驟1：在“公式”選項(xiàng)卡通過名稱管理，用公式

定義3個(gè)名稱。分別是：

產(chǎn)品名稱=OFFSET(sheet1!$A$1,sheet1!$G$2,1,1)

產(chǎn)品=OFFSET(sheet1!$A$2,sheet1!$G$2,7,1)

地區(qū)=sheet1!$A$2:$A$8

G2單元格為組合框的鏈接單元格，選擇組合框不同內(nèi)容時(shí)，G2單元格的數(shù)值會(huì)發(fā)生變化。在I2:I5單元格轉(zhuǎn)置粘貼產(chǎn)品名稱，用于組合框的數(shù)據(jù)區(qū)域。

OFFSET函數(shù)說明如圖7-20所示。

圖7-20

OFFSET函數(shù)圖示

操作步驟2：選擇“插入”選項(xiàng)卡中的柱形圖中的簇狀柱形圖，然后單擊右鍵選擇數(shù)據(jù)源，單擊“添加”按鈕，設(shè)置系列名稱為“=sheet1!產(chǎn)品名稱”，系列值為“=sheet1!產(chǎn)品”，編輯水平軸標(biāo)簽為“=sheet1!!地區(qū)”。

操作步驟3：在I1輸入“產(chǎn)品名稱”，在I2:I5單元格轉(zhuǎn)置粘貼產(chǎn)品A至產(chǎn)品D中，用于組合框的數(shù)據(jù)區(qū)域。在“開發(fā)工具”選項(xiàng)卡中插入組合框控件，設(shè)置控件格式，數(shù)據(jù)源區(qū)域?yàn)椤?I$2:$I$5”，單元格鏈接為“$G$2”，如圖7-21所示。

圖7-21設(shè)置組合框控件格式

(3)利用復(fù)選框，控制多個(gè)對(duì)象圖表。

繪制2015年銷售統(tǒng)計(jì)圖表，要求根據(jù)不同的復(fù)選框選擇顯示不同的圖表內(nèi)容，最終效果如圖7-22所示。

圖7-22銷售統(tǒng)計(jì)圖

原始數(shù)據(jù)如表7-6所示。

操作步驟1：在“開發(fā)工具”選項(xiàng)卡中插入3個(gè)復(fù)選框，設(shè)置控件格式，都設(shè)置為“已選擇”，分別設(shè)置鏈接單元格為$B$15、$C$15、$D$15。疊放次序?yàn)椤爸糜陧攲印薄?/p>

操作步驟2：設(shè)計(jì)輔助表格，復(fù)制標(biāo)題行至G2:J2，復(fù)制1～12月至G3:G14，在當(dāng)前銷售額下，H3單元格輸入公式“?=?IF($B$15,B2,NA())”，向下填充；累計(jì)銷售額下，I3單元格輸入公式“?=?IF($C$15,C2,NA())”，向下填充；環(huán)比增長率下，J3單元格輸入公式“?=?IF($D$15,D2,NA())”，向下填充。

操作步驟3：插入圖表，繪制當(dāng)月銷售額的簇狀柱形圖。選擇數(shù)據(jù)源，直接選擇$G$2:$J$14區(qū)域作為數(shù)據(jù)源；然后修改累計(jì)銷售額的圖表類型為面積圖，在“布局選項(xiàng)卡”中選擇系列“環(huán)比增長率”，設(shè)置所選內(nèi)容格式，設(shè)置為“次坐標(biāo)軸”，如圖7-23所示。同時(shí)更改圖表類型為折線圖。

圖7-23設(shè)置數(shù)據(jù)系列格式

(4)使用VBA進(jìn)行動(dòng)態(tài)圖表制作。

操作步驟1：新建工作簿，保存為xlsm格式，為包含VBA代碼的文件。

操作步驟2：輸入原始數(shù)據(jù)，如表7-7所示。

操作步驟3：插入餅圖，選擇前兩列數(shù)據(jù)，即1月的信息，繪制餅圖。為其添加數(shù)據(jù)標(biāo)簽，設(shè)置顯示標(biāo)簽為“百分比”，標(biāo)簽位置為“最佳匹配”。

操作步驟4：在開發(fā)工具VisualBasic編輯器中，選擇worksheet中的selectionchange進(jìn)行代碼輸入。

操作步驟5：回到工作表，當(dāng)該圖表為唯一圖表的前提下，鼠標(biāo)點(diǎn)選其他月份單元格(B1、C1、D1、E1)，該圖表就會(huì)發(fā)生變化，實(shí)現(xiàn)動(dòng)態(tài)圖表繪制。

7.3知識(shí)拓展

常見錯(cuò)誤及解決方法：出現(xiàn)錯(cuò)誤時(shí)通常有一些錯(cuò)誤值，各個(gè)錯(cuò)誤值代表不同的含義，每個(gè)錯(cuò)誤值都有不同的原因和解決方法。

1)?####錯(cuò)誤

該錯(cuò)誤表示列不夠?qū)挘蛘呤褂昧素?fù)日期或時(shí)間。當(dāng)列寬不足以顯示內(nèi)容時(shí)，可以通過以下幾種辦法予以糾正。

(1)調(diào)整列寬或直接雙擊列標(biāo)題右側(cè)的邊界。

(2)縮小內(nèi)容以適應(yīng)列寬。

(3)更改單元格的數(shù)字格式，使數(shù)字適合現(xiàn)有單元格寬度。例如，可以減少小數(shù)點(diǎn)后的小數(shù)位數(shù)。

當(dāng)日期和時(shí)間為負(fù)數(shù)時(shí)，可以通過以下幾種方法予以糾正：

(1)如果使用的是1900日期系統(tǒng)，那么日期和時(shí)間必須為正值。

(2)如果對(duì)日期和時(shí)間進(jìn)行減法運(yùn)算，那么應(yīng)確保建立的公式是正確的。

(3)如果公式是正確的，但結(jié)果仍然是負(fù)值，那么可以通過將相應(yīng)單元格的格式設(shè)置為非日期或時(shí)間格式來顯示該值。

2)?#VALUE!錯(cuò)誤

該錯(cuò)誤表示使用的參數(shù)或操作數(shù)的類型不正確，可能包含以下一種或幾種錯(cuò)誤：

(1)當(dāng)公式需要數(shù)字或邏輯值(例如TRUE或FALSE)時(shí)，卻輸入了文本。

(2)輸入或編輯數(shù)組公式，沒有按“Ctrl?+?Shift?+?Enter”組合鍵，而是按了“Enter”鍵。

(3)將單元格引用、公式或函數(shù)作為數(shù)組常量輸入。

(4)為需要單個(gè)值(而不是區(qū)域)的運(yùn)算符或函數(shù)提供區(qū)域。

(5)在某個(gè)矩陣工作表函數(shù)中使用了無效的矩陣。

(6)運(yùn)行的宏程序中所輸入的函數(shù)返回“#VALUE!”。

3)?#DIV/0!錯(cuò)誤

該錯(cuò)誤表示使用數(shù)字除以零(0)。具體表現(xiàn)在：

(1)輸入的公式中包含明顯的除以