电脑小百科

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

[复制链接]
阅读次数 51      发表日期 2020-9-2 13:41
如果Excel表格中的数据繁多,查询起来就比较麻烦。若利用点击上下箭头按钮的方式,在表格源数据上形成聚光灯效果,能够上、下、左、右移动,且对应的图表也随之联动变换,数据显示就会一目了然。那么,这样的效果是如何实现的呢?其实,利用条件格式、OFFSET函数及表单控件相结合,就能轻松实现(图1)。
1 - 1598920865768.jpg
1. 插入微调框
在工作表中选择“开发工具”选项卡,点击“插入”按钮,选择“表单控件”下的“数值调节钮”,在工作表的合适位置双击插入一个微调框,这个微调框用于变换项目。右击插入的微调框,选择“设置控件格式”,在弹出对话框的“控制”选项卡中,“当前值”处设置为“2”,“最小值”处设置为“1”,“最大值”处设置为“6”,“步长”处设置为“1”,“单元格链接”处选择工作表中的一个空白单元格(如B10)。这样,当点击微调框的上、下箭头时,B10单元格的值就会发生相应的变化。同样道理,再插入一个微调框用于变换产品,其最大值设置为10,“单元格链接”处设置为“B11”。再分别利用文本框为两个微调框插入标签,将微调框与各自的标签进行组合(图2)。这里需要说明的是,微调框的最大值是根据数据表中的项目数、产品数决定的。
2 - 1598920874324.jpg
小提示:如果在Excel菜单栏中未能发现“开发工具”选项卡,请先单击“文件”菜单并选择“选项”,然后在弹出的“Excel选项”设置窗口中,点击左侧栏内的“自定义功能区”,最后选中右侧列表中“主选项卡”下的“开发工具”选项,确定即可(图3)。
3 - 1598920880340.jpg
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)。
4 - 1598920890299.jpg
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)。
5 - 1598920897254.jpg
通过上述设置,就可以达到点击微调框进行显示聚焦的效果了。
4. 插入图表并进行美化
选中C13:D15单元格区域,插入簇状柱形图,选中插入的图表,点击“设计→切换行/列”,再为图表添加图例;选中C16:D17单元格区域,同样插入簇状柱形图,设置好水平(分类)轴标签;调整好这两个图表的大小,删除垂直轴标签、标题及网格线(图6)。
6 - 1598920905235.jpg
分别选中图表,设置好它们的背景色及柱体填充色,添加数据标签;利用形状中的矩形画一个背景,将其置于底层,拖动图表及微调框的位置到矩形背景,对齐两个微调框,将背景、图表及微调框进行组合;调整组合大小,并拖动它到合适的位置,将不需要的数据进行遮挡即可。
来源:CFAN


2020上云采购季

返回顶部 返回列表