在 Excel 中，制作動態(tài)圖表需要用組合框或選項按鈕控件，如果只動態(tài)顯示一項數(shù)據(jù)，用一個組合框即可；如果要顯示兩項以上數(shù)據(jù)，需要用選項按鈕與組合框組合。例如要動態(tài)顯示每個月生產(chǎn)產(chǎn)品的產(chǎn)量和合格數(shù)量，用一個組合框就可以完成；而要動態(tài)顯示每個月或每個分店的營業(yè)額，需要用選項按鈕與組合框。

Excel制作動態(tài)圖表需要用 OffSet 函數(shù)，用它實現(xiàn)根據(jù)當(dāng)前選擇值返回對相應(yīng)單元格的引用；如果僅用一個組合框，不需要定義名稱；如果用選項按鈕與組合框組合，則需要定義名稱。

一、Excel顯示“開發(fā)工具”選項卡

在默認(rèn)情況下，Excel 不顯示“開發(fā)工具”選項卡，需要先把它顯示出來，方法為：右鍵功能區(qū)任意空白處，在彈出的菜單中選擇“自定義功能區(qū)”，打開“Excel 選項”窗口，并自動選擇“自定義功能區(qū)”，勾選“主選項卡”下的“開發(fā)工具”，單擊“確定”，則“開發(fā)工具”顯示到功能區(qū)；操作過程步驟，如圖1所示：

圖1

二、Excel制作單組合框控件動態(tài)圖表

1、插入組合框控件。

A、選擇“開發(fā)工具”選項卡，單擊“插入”，選擇“表單控件”下的“組合框”，鼠標(biāo)變?yōu)?ldquo;加號”，把它移到要畫組合框的位置，按住左鍵并拖動，畫出的組合框大小合適后放開左鍵，則成功插入一個組合框；

B、右鍵組合框，在彈出的菜單中選擇“設(shè)置控件格式”， 打開“設(shè)置控件格式”窗口，把光標(biāo)定位到“數(shù)據(jù)源區(qū)域”右邊，框選 A2:A7 作為組合框的數(shù)據(jù)源（如果數(shù)據(jù)源行數(shù)比較多，可以直接輸入，注意加上 $，以表示絕對引用）；再把光標(biāo)定位到“單元格連接”的左邊，選擇一個單元格（如 J3）作為顯示組合框當(dāng)前選擇值的索引（序號），單擊“確定”，則把“月份”添加為組合框的數(shù)據(jù)源；

C、單擊任意空白處釋放組合框的選中狀態(tài)，再單擊它，則展開剛才添加的數(shù)據(jù)源“月份”，選擇“2月”，J3 中顯示 2，再選擇“4月”，J3 中顯示 4；操作過程步驟，如圖2所示：

圖2

2、復(fù)制表頭和根據(jù)組合框選擇值顯示相應(yīng)記錄。

A、單擊 A1 單元格選中它，按住 Shift，再單擊 D1，選中表頭，按 Ctrl + C 復(fù)制，選中 F1，按 Ctrl + V，把表頭粘貼一份；單擊“組合框”選擇“1月”；

B、選中 F2，選擇“公式”選項卡，單擊“查找與引用”，在彈出的選項中選擇 OFFSET，打開“函數(shù)參數(shù)”窗口，單擊 Reference 輸入框右邊的“把窗口收縮起來的圖標(biāo)”，單擊 A1，則 A1 自動輸入到 Reference 的輸入框中，同樣方法把 J3 輸入到 Rows 右邊的輸入框中，再把兩個輸入框的引用改為 $A$1 和 $J$3；把光標(biāo)定位到 Cols 右邊的輸入框中，把 COLUMN(A1)-1 復(fù)制過去，單擊“確定”，則 F2 中的值變?yōu)?ldquo;1月”，正是組合框的值；

C、選中 F2，把鼠標(biāo)移到 F2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)楹谏�加號后，按住左鍵，往右拖一直拖到 I2，則提取了“1月”的數(shù)據(jù)；操作過程步驟，如圖3所示：

圖3

D、公式 =OFFSET($A$1,$J$3,COLUMN(A1)-1) 說明：

（1）在“函數(shù)參數(shù)”窗口設(shè)置的其實就是公式 =OFFSET($A$1,$J$3,COLUMN(A1)-1)，公式的意思是：以 A1 為基準(zhǔn)，返回 A1 下 1 行（J3 中的值為 1）0 列（COLUMN(A1)-1) 值為 0）的引用，即返回對 A2 的引用，也就是“1月”。

（2）$A$1 表示對行和列都絕對引用，當(dāng)往下拖時， A1 不會變 A2、A3 等；當(dāng)往右拖時，A1 不會變 B1、C1 等；$J$3 與 $A$1 是一個意思。

（3）COLUMN(A1) 返回 A1 的列號 1，COLUMN(A1)-1 等于 0；當(dāng)公式拖到 G2 時，COLUMN(A1)-1 變?yōu)?COLUMN(B1)-1，結(jié)果為 1；則 OFFSET($A$1,$J$3,COLUMN(A1)-1) 變?yōu)?OFFSET($A$1,1,1)，即返回 A1 下 1 行 1 列的引用，即返回對 B2 的引用。

提示：如果對 OffSet 函數(shù)還不熟悉，請查看《Excel OffSet函數(shù)的使用方法，含與Sum、Match、CountIf、If、Or、Row組合實現(xiàn)動態(tài)求和、一個重復(fù)多個、分段》一文。

3、生成動態(tài)圖表。

A、選擇“插入”選項卡，單擊“插入柱形圖或條形圖”圖標(biāo)，在彈出的圖表樣式中選擇第一個“簇狀柱形圖”，則插入一個圖表；右鍵圖表，在彈出的菜單中依次選擇“置于底層”→ 置于底層，把圖表下移到最底層，不讓它遮擋組合框；如果文檔中插入的東西不多，也可以一層層的下移；

B、把鼠標(biāo)移到圖表上，鼠標(biāo)變?yōu)閹�四個箭頭的十字架后，按住左鍵，把圖表移好位置；右鍵組合框選中它，按住鼠標(biāo)左鍵，把組合框移到圖表的右上角；

C、右鍵圖表，在彈出的菜單中選擇“選擇數(shù)據(jù)”，打開“選擇數(shù)據(jù)源”窗口，框選 F1:I2，把“圖表數(shù)據(jù)區(qū)域”改為所選區(qū)域，單擊“確定”，則圖表 X 軸變?yōu)?ldquo;月份”，單擊“組合框”選擇“3月”，則圖表顯示“3月”的數(shù)據(jù)，再選擇“5月”，則圖表顯示“5月”的數(shù)據(jù)；操作過程步驟，如圖4所示：

圖4

三、Excel制作雙組合框控件動態(tài)圖表

1、復(fù)制表頭。選中 B1 單元格，按住 Shift，單擊 G1，選中 B1:G1，當(dāng)前選項卡為“開始”，選中 A9，單擊窗口右上角的“粘貼”，在彈出中的選項中，選擇“粘貼”下的“轉(zhuǎn)置”，則表格列標(biāo)題被轉(zhuǎn)為行；操作過程步驟，如圖5所示：

圖5

2、插入“選項按鈕”控件。

A、選擇“開發(fā)工具”選項卡，單擊“插入”，在彈出的控件中，選擇“選項按鈕”，鼠標(biāo)變?yōu)榧犹枺�把它移到要插�?ldquo;選項按鈕”的位置，按住左鍵并拖動，則畫出一個選項按鈕，單擊里面的文字把光標(biāo)插到文字前，選中所有文字，輸入“分店”；

B、右鍵“選項按鈕”，在彈出的選項中選擇“設(shè)置控件格式”，打開“設(shè)置控件格式”窗口，單擊“單元格鏈接”輸入框右邊的圖標(biāo)把窗口收縮起來，單擊 B9，把它作為連接“選項按鈕”的單元格，單擊“確定”，“選項按鈕”設(shè)置好；

C、按 Ctrl + C 復(fù)制選項按鈕，再按 Ctrl + V 把它粘貼一份，把粘貼的副本與“分店”對齊，單擊副本中的文字把光標(biāo)定位到里面，選中所有文字，輸入“月份”；按住 Shift，右鍵“分店”選項按鈕，把兩個選項按鈕選中，然后把它們移到合適位置，單擊一下任意空白處釋放選項按鈕的選中狀態(tài)，單擊“分店”，B9 顯示 1，單擊“月份”，B9 顯示 2；操作過程步驟，如圖6所示：

圖6

3、用“定義名稱”為選項控件添加引用。

1、右鍵“分店”選項按鈕選中它，選擇“公式”選項卡，單擊“定義名稱”打開“新建名稱”窗口，在“名稱”右邊輸入“分類選項”，選中“引用位置”右邊輸入框中 = 右邊的文字，輸入 IF(，單擊 B9，輸入“=1,”，框選 A2:A7，輸入“,”，框選 A9:A14，輸入“)”，按回車確定，則操作完成；演示如圖7所示：

圖7

提示：也可直接把公式 =IF(Sheet1!$B$9=1,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 復(fù)制到“引用位置”右邊的輸入框中。

2、公式 =IF(Sheet1!$B$9=1,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 說明：

Sheet1 為工作表名稱；Sheet1!$B$9=1 為 IF 的條件，如果條件成立，則返回 Sheet1!$A$2:$A$7，即返回所有“分店”；否則返回Sheet1!$A$9:$A$14，即返回所有“月份”。

4、插入組合框控件。

A、選擇“開發(fā)工具”選項卡，單擊“插入”，選擇“表單控件”下的“組合框”，鼠標(biāo)變?yōu)榧犹枺�把它移到要插入組合框的位置，按下左鍵并拖動，則插入一個組合框；

B、右鍵“組合框”，在彈出的選項中選擇“設(shè)置控件格式”，打開“設(shè)置控件格式”窗口，在“數(shù)據(jù)源區(qū)域”右邊輸入上一步定義的“選項控件”名稱“分類選項”；單擊“單元格鏈接”輸入框右邊的小圖標(biāo)，把窗口收縮起來，單擊 C9 把它作為組合的鏈接單元格，再單擊輸入框右邊的圖標(biāo)把窗口展開，單擊“確定”；

C、把“組合框”移到“月份”右邊，單擊任意空白處釋放組合框的選中狀態(tài)，單擊“組合框”選擇“3號店”，則“3號店”作為它的當(dāng)前選項，C9 中同時變?yōu)橄鄳?yīng)的值； 選擇“月份”，組合框中自動變?yōu)?ldquo;3月”，再次單擊“組合框”，選擇“4月”；操作過程步驟，如圖8所示：

圖8

5、定義“X軸類別”和“動態(tài)顯示數(shù)據(jù)”名稱。

A、選擇“公式”選項卡，單擊“定義名稱”，打開“新建名稱”窗口，把“X軸類別”復(fù)制到“名稱”后，把公式 =IF(Sheet1!$B$9=2,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 復(fù)制到“引用位置”后并覆蓋原有文字，單擊“確定”；

B、再次單擊“定義名稱”，在打開的窗口中，把“動態(tài)顯示數(shù)據(jù)”復(fù)制到“名稱”后，把公式 =IF(Sheet1!$B$9=1,OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6),OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)) 復(fù)制到“引用位置”輸入框中且覆蓋原有文字，單擊“確定”；兩個名稱定義好；操作過程步驟，如圖9所示：

圖9

C、公式說明：

（1）=IF(Sheet1!$B$9=2,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 意思是：如果 B9 中的數(shù)值為 2，則返回 A2:A7 中的“分店”，否則返回 A9:A14 中的“月份”。

（2）=IF(Sheet1!$B$9=1,OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6), OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1))

a、Sheet1!$B$9=1 是 IF 的條件，意思是：如果 B9 中的值等于 1（即當(dāng)選擇“分店”時），則執(zhí)行 OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6)，也就是顯示當(dāng)前選擇的“分店”每個月的營業(yè)額；否則執(zhí)行 OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)，即顯示當(dāng)前選擇的“月份”每個店的營業(yè)額。

b、OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6) 中，$C$9 返回的是“組合框”的選擇值，假如“組合框”的當(dāng)前選擇值為“4號店”，則$C$9 返回 4，則 OFFSET 返回以 A1 為基準(zhǔn)、A1 下 4 行 1 列且高度為 1、寬度為 6 的單元格引用，即返回 $B$5:$G$5，也就是返回“4號店”每個月的營業(yè)額（可以參考最后一步的演示）。

c、OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)

IF(Sheet1!$C$9<=6,Sheet1!$C$9,6) 用于返回列號，意思是：如果 C9 中的值小于等于 6，則反回 C9 中的值，否則返回 6，這主要是為了防止組合框選擇值超過表格要顯示到圖表中的列數(shù)（即“1月”到“6月”的 6 列）而發(fā)生異常；

假如“組合框”當(dāng)前選擇值為“4月”，則 IF(Sheet1!$C$9<=6,Sheet1!$C$9,6) 返回 4，則 OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)  變?yōu)?OFFSET(Sheet1!$A$1,1,4,6,1)，意思是：以 A1 基準(zhǔn)，返回 A1 下 1 行 4 列且高度為 6 寬度為 1 的單元格引用，即返回 $E$2:$E$7，也就是返回“4月”每個分主店的營業(yè)額。

6、生成雙控件動態(tài)圖表。

A、選擇“插入”選項卡，單擊“插入柱形圖”圖標(biāo)，然后選擇第一個圖表，則插入一個空白圖表，右鍵該圖表，在彈出的菜單中選擇“置于底層”→ 置于底層，把圖表置于底層以顯示“選項按鈕和組合框”；

B、把圖表拖到合適的位置，右鍵它，在彈出的菜單中選擇“選擇數(shù)據(jù)”，打開“選擇數(shù)據(jù)源”窗口，單擊“添加”，打開“編輯數(shù)據(jù)系列”窗口，選中“系列值”輸入框中所有文字，按 Delete 鍵把它們刪除，單擊工作表名稱 Sheet1，再把前面定義的名稱“動態(tài)顯示數(shù)據(jù)”復(fù)制過去，單擊“確定”返回“選擇數(shù)據(jù)源”窗口；

C、單擊“編輯”，打開“軸標(biāo)簽”窗口，單擊工作表名稱 Sheet1，把前面定義的名稱“X軸類別”復(fù)制過去，單擊“確定”，再次單擊“確定”；把“圖表標(biāo)題”移到左邊，單擊“組合框”，選擇“4號店”，則顯示“4號店”每個月的營業(yè)額；選擇“月份”，則顯示“4月”每個分店的營業(yè)額，再選擇“5月”，則顯示“5月”每個店的營業(yè)額；操作過程步驟，如圖10所示：

圖10