動態圖表條件排序巧實現

馬震安

1. 設置交互控件

在圖表的數據源所在工作表的F2、F3、F4單元格中分別輸入銷售、任務、完成率，從而構建交互式下拉列表所需要的菜單內容。在工作表中選擇“開發工具”選項卡，點擊“插入”按鈕“表單控件”下的“組合框”，在工作表的合適位置按鼠標左鍵畫出一個組合框控件。右擊插入的組合框，選擇“設置控件格式”，在彈出窗口的“控件”選項卡，數據源區域選擇“F2：F4”，單元格鏈接選擇“J1”。這樣，當點擊列表中的菜單時，J1中就會出現該菜單的索引值。再插入3個單選選項按鈕，右擊插入的單選按鈕，選擇“編輯文字”，分別輸入“正常”、“升序”、“降序”。再次右擊第1個單選按鈕，選擇“設置控件格式”，在彈出對話框的“控制”選項卡中值處選擇“選擇”，單元格鏈接處選擇“J2”，其他兩個單選按鈕的值處選擇“未選擇”，單元格鏈接處都選擇“J2”。這樣，當在這3個單選按鈕間切換時，J2單元的值就會發生變化，在“1”、“2”、“3”這3個數之間進行切換（圖2）。

2. 構建圖表數據源

圖表的數據源是根據控件所鏈接單元格的值決定的，是由定義名稱所建立的。選擇“公式”選項卡，點擊“名稱管理器”，在彈出的窗口中點擊“新建”，名稱處輸入“xztj”，引用位置處輸入“=CHOOSE（$J$1， $B$2：$B$7，$C$2：$C$7，$D$2：$D$7）”；再新建，名稱為“pxlx”，引用位置處為“=CHO OSE（$J$2，ROW（$2：$7），SMALL（xzt j /1%%%+ROW（$2：$7），ROW（$1：$6）），LARGE（xztj /1%%%+ROW（$2：$7）， R O W （ $ 1 ： $ 6 ）））”；再新建，名稱為“h a n g h a o”，引用位置處為“=--RIGHT（pxlx，2）”；再新建，名稱為“d i a n”，引用位置處為“=T（O F FS E T（$A$1，hanghao-1，））”；再新建，名稱為“xiaoshou”，引用位置處為“=N（OFFSET（$B$1，hanghao-1，））”；再新建，名稱為“r e n w u”，引用位置處為“= N （O FFS E T（$ C $1，han g ha o -1，））”；再新建，名稱為“w a n c h e n g l v”，引用位置處為“=N（OFFSET（$D$1，hanghao-1，））”。這樣，圖表所需要的數據源就構建完成了（圖3）。

3. 設置圖表

將數據區域選定，插入柱形圖，右擊圖表，選擇“更改圖表類型”，在彈出的窗口中選擇“組合”，“完成率”處選擇“帶數據標記的折線圖”，勾選其“次坐標”。選定圖表中的折線圖，右擊選擇“設置數據系列格式”，在“填充與線條”選項卡，線條中選擇“無線條”，標記的“數據標記選項”中選擇“內置”，類型處選擇“菱形”，大小為30，純色填充，50%透明度（圖4）。

接下來，右擊圖表，選擇“選擇數據”，在彈出窗口的“圖例項”處，選定“銷售”，點擊“編輯”，在系列值處輸入“=Sheet1！xiaoshou”，選定“任務”，系列值更改為“=Sheet1！renwu”，選定“完成率”，系列值更改為“=Sheet1！wanchenglv”；在“水平（分類）軸標簽”處，選定“A店”，點擊“編輯”，在彈出的窗口中輸入“=Sheet1！dian”，其他標簽的軸標簽區域也都改成“=Sheet1！dian”。這樣，圖表與控件之間就有交互效果了（圖5）。

4. 美化圖表

選定圖表中的完成率系列，添加數據標簽，標簽居中，右擊標簽，選擇“設置數據標簽格式”，在標簽選項中，數字類別處選擇“百分比”，小數位數1位；根據需要更改圖例的位置，當然也可根據需要添加其他的數據標簽。在J3單元格中輸入“="各店 " & CHOOSE（$J$1，$B$1，$C$1，$D$1）&IF;（$J$2=1，"正常"，IF（$J$2=2，"升序"，"降序"）） & "統計數據"”，然后選擇圖表標題，在公式編輯欄中輸入“=Sheet1！$J$3”，這樣圖表標題就會跟隨控件的選擇而進行變化了（圖6）。

最后，右擊每個控件，選擇“置于頂層”，然后將這些控件拖動到圖表相應的位置就可以了。