经验知识

Excel进阶:实现聚光灯联动式数据查询

微信扫一扫,分享到朋友圈

Excel进阶:实现聚光灯联动式数据查询
收藏 0 0

如果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)。

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

Zen3架构!AMD下代桌面锐龙APU样品现身:8核心+高频率

上一篇

十一火车票今日开抢!用上这个小窍门“捡漏”成功几率大增

下一篇

你也可能喜欢

发表评论

您的电子邮件地址不会被公开。 必填项已用 * 标注

提示:点击验证后方可评论!

 

插入图片

微信扫一扫

微信扫一扫