實現(xiàn)聚光燈聯(lián)動式數(shù)據(jù)查詢

馬震安

1.插入微調(diào)框

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

2.插入公式

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

3.設(shè)置條件格式

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

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

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

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

4.插入圖表并進行美化

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

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