Excel制作單雙控件動態圖表，含用組合框、選項按鈕動態顯示每月產量與合格數量、月份或分店營業額

在 excel 中，制作動態圖表需要用組合框或選項按鈕控件，如果只動態顯示一項數據，用一個組合框即可；如果要顯示兩項以上數據，需要用選項按鈕與組合框組合。例如要動態顯示每個月生產產品的產量和合格數量，用一個組合框就可以完成；而要動態顯示每個月或每個分店的營業額，需要用選項按鈕與組合框。

Excel制作動態圖表需要用 OffSet 函數，用它實現根據當前選擇值返回對相應單元格的引用；如果僅用一個組合框，不需要定義名稱；如果用選項按鈕與組合框組合，則需要定義名稱。

一、Excel顯示“開發工具”選項卡

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

圖1

二、Excel制作單組合框控件動態圖表

1、插入組合框控件。

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

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

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

圖2

2、復制表頭和根據組合框選擇值顯示相應記錄。

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

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

C、選中 F2，把鼠標移到 F2 右下角的單元格填充柄上，鼠標變為黑色加號后，按住左鍵，往右拖一直拖到 I2，則提取了“1月”的數據；操作過程步驟，如圖3所示：

圖3

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

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

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

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

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

3、生成動態圖表。

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

B、把鼠標移到圖表上，鼠標變為帶四個箭頭的十字架后，按住左鍵，把圖表移好位置；右鍵組合框選中它，按住鼠標左鍵，把組合框移到圖表的右上角；

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

圖4

三、Excel制作雙組合框控件動態圖表

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

圖5

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

A、選擇“開發工具”選項卡，單擊“插入”，在彈出的控件中，選擇“選項按鈕”，鼠標變為加號，把它移到要插入“選項按鈕”的位置，按住左鍵并拖動，則畫出一個選項按鈕，單擊里面的文字把光標插到文字前，選中所有文字，輸入“分店”；

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

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

圖6

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

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

圖7

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

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

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

4、插入組合框控件。

A、選擇“開發工具”選項卡，單擊“插入”，選擇“表單控件”下的“組合框”，鼠標變為加號，把它移到要插入組合框的位置，按下左鍵并拖動，則插入一個組合框；

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

C、把“組合框”移到“月份”右邊，單擊任意空白處釋放組合框的選中狀態，單擊“組合框”選擇“3號店”，則“3號店”作為它的當前選項，C9 中同時變為相應的值； 選擇“月份”，組合框中自動變為“3月”，再次單擊“組合框”，選擇“4月”；操作過程步驟，如圖8所示：

圖8

5、定義“X軸類別”和“動態顯示數據”名稱。

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

B、再次單擊“定義名稱”，在打開的窗口中，把“動態顯示數據”復制到“名稱”后，把公式 =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)) 復制到“引用位置”輸入框中且覆蓋原有文字，單擊“確定”；兩個名稱定義好；操作過程步驟，如圖9所示：

圖9

C、公式說明：

（1）=IF(Sheet1!$B$9=2,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 意思是：如果 B9 中的數值為 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（即當選擇“分店”時），則執行 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)，即顯示當前選擇的“月份”每個店的營業額。

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

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，這主要是為了防止組合框選擇值超過表格要顯示到圖表中的列數（即“1月”到“6月”的 6 列）而發生異常；

假如“組合框”當前選擇值為“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)  變為 OFFSET(Sheet1!$A$1,1,4,6,1)，意思是：以 A1 基準，返回 A1 下 1 行 4 列且高度為 6 寬度為 1 的單元格引用，即返回 $E$2:$E$7，也就是返回“4月”每個分主店的營業額。

6、生成雙控件動態圖表。

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

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

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

圖10

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。