办公小技巧：实现聚光灯联动式数据查询

如果Excel表格中的数据繁多，查询起来就比较麻烦。若利用点击上下箭头按钮的方式，在表格源数据上形成聚光灯效果，能够上、下、左、右移动，且对应的图表也随之联动变换，数据显示就会一目了然。那么，这样的效果是如何实现的呢？其实，利用条件格式、OFFSET函数及表单控件相结合，就能轻松实现（图1）。

1. 插入微调框

在工作表中选择“开发工具”选项卡，点击“插入”按钮，选择“表单控件”下的“数值调节钮”，在工作表的合适位置双击插入一个微调框，这个微调框用于变换项目。右击插入的微调框，选择“设置控件格式”，在弹出对话框的“控制”选项卡中，“当前值”处设置为“2”，“最小值”处设置为“1”，“最大值”处设置为“6”，“步长”处设置为“1”，“单元格链接”处选择工作表中的一个空白单元格（如B10）。这样，当点击微调框的上、下箭头时，B10单元格的值就会发生相应的变化。同样道理，再插入一个微调框用于变换产品，其最大值设置为10，“单元格链接”处设置为“B11”。再分别利用文本框为两个微调框插入标签，将微调框与各自的标签进行组合（图2）。这里需要说明的是，微调框的最大值是根据数据表中的项目数、产品数决定的。

小提示：如果在Excel菜单栏中未能发现“开发工具”选项卡，请先单击“文件”菜单并选择“选项”，然后在弹出的“Excel选项”设置窗口中，点击左侧栏内的“自定义功能区”，最后选中右侧列表中“主选项卡”下的“开发工具”选项，确定即可（图3）。

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,13-$B$11,0)”，这样可以根据C10和B11单元格的值获取到数据区域中相应的值；在C14、C15、C16、C17单元格分别输入预算毛利率、实际毛利率、上年售价、实际售价，在D13输入公式“=C11”，在D14单元格输入公式“=VLOOKUP(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）。

分别选中图表，设置好它们的背景色及柱体填充色，添加数据标签；利用形状中的矩形画一个背景，将其置于底层，拖动图表及微调框的位置到矩形背景，对齐两个微调框，将背景、图表及微调框进行组合；调整组合大小，并拖动它到合适的位置，将不需要的数据进行遮挡即可。