實現聚光燈聯動式數據查詢

馬震安

1.插入微調框

在工作表中選擇“開發工具”選項卡，點擊“插入”按鈕，選擇“表單控件”下的“數值調節鈕”，在工作表的合適位置雙擊插入，個微調框，這個微調框用于變換項目。右擊插入的微調框，選擇“設置控件格式”，在彈出對話框的“控制”選項卡中，“當前值”處設置為“2”，“最小值”處設置為“1”，“最大值”處設置為“6”，“步長”處設置為“1”，“單元格鏈接”處選擇工作表中的一個空白單元格（如B10）。這樣，當點擊微調框的上、下箭頭時，B10單元格的值就會發生相應的變化。同樣道理，再插入，個微調框用于變換產品，其最大值設置為10，“單元格鏈接”處設置為“B11”。再分別利用文本框為兩個微調框插入標簽，將微調框與各自的標簽進行組合（圖2）。這里需要說明的是，微調框的最大值是根據數據表中的項目數、產品數決定的。

2.插入公式

在C10單元格輸入公式“=INDEX（A2：A7，7-$B$10）”，這樣就能根據B10單元格的值從A2：A7單元格區域中獲取相應的項目名稱，即可根據變換項目的微調框進行相關項目名稱的獲取;在C11單元格中輸入公式“=INDEX（A1：L1，1，13-$B$11）&""”，這樣就能根據B11單元格的值從A1：L1單元格區域中獲取相應的產品名稱，即可根據變換產品的微調框進行相關產品名稱的獲取;在C12單元格中輸入公式“=VLOOKUP（$C$10，$A$2：$L$7月3-$B$11，0）”，這樣可以根據C10和B11單元格的值獲取到數據區域中相應的值;在C14、C15、C16、C17單元格分別輸入預算毛利率、實際毛利率、上年售價、實際售價，在D13單元格輸入公式“=C11”，在D14單元格輸入公式“=VLOOKU P（C14，$A$2：$L$2，13-$B$11，FALSE）”，在D15單元格輸入公式“=VLOOKUP（C15，$A$3：$L$3，13-$B$11，FALSE）”，在D16單元格輸入公式“=VLOOKUP（C16，$A$5：$L$5，13-$B$11，FALSE）”，在D17單元格輸入公式“=VLOOKUP（C17，$A$6：$L$6，13-$B$11，FALSE）”，這樣就獲取了相關數據作為圖表的數據源（圖4）。

3.設置條件格式

切換到“開始”選項卡，點擊“條件格式→管理規則→新建規則“。在彈出窗口的“選擇規則類型”處選擇最下面的“使用公式確定要設置格式的單元格”，在“為符合此公式的值設置格式”處輸入“=A2=$C$10”，點擊“格式”按鈕，將填充色設置為深綠色，文字設置為白色，確定后點擊“應用于”處的箭頭，選擇“A2：A7”。

同理，再依次新建規則并設置：新建規則，公式為“C1=$C$11”，“應用于”處選擇“=$C$1：$L$1”;新建規則，公式為“=B2=$C$12”，“應用于”處選擇“=$B$2：$L$7”;新建規則，公式為“=$C$11=B$1”，“應用于”處選擇“=$B$2：$L$7”，將填充色設置為灰色;新建規則，公式為“=$C$10=$A2”，“應用于”處選擇“=$B$2：$L$7”，將填充色設置為灰色。

利用管理規則窗口中的上、下箭頭調整規則順序，使填充色為灰色的放置于填充色為深綠色的下面（圖5）。

通過上述設置，就可以達到點擊微調框進行顯示聚焦的效果了。

4.插入圖表并進行美化

選中C13：D15單元格區域，插入簇狀柱形圖，選中插入的圖表，點擊“設計→切換行/列”，再為圖表添加圖例;選中C16：D17單元格區域，同樣插入簇狀柱形圖，設置好水平（分類）軸標簽;調整好這兩個圖表的大小，刪除垂直軸標簽、標題及網格線（圖6）。

分別選中圖表，設置好它們的背景色及柱體填充色，添加數據標簽;利用形狀中的矩形畫一個背景，將其置于底層，拖動圖表及微調框的位置到矩形背景，對齊兩個微調框，將背景、圖表及微調框進行組合;調整組合大小，并拖動它到合適的位置，將不需要的數據進行遮擋即可。