设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

查找和引用的Excel函数

2014年04月24日 09:00 发布来源:Excel网

    在使用Excel时很多情况下,我们需要把个数不定的工作表数据汇总在一张工作表上,以便进行动态的跟踪分析:或者是把几个相关联的Excel工作表数据汇总在一起,此时,我们就需要使用有关查找和引用的Excel函数

    一般情况下,每个月的数据保存在每张工作表中,而且随着时间的推移,工作表也逐步增加。是否可以制作一个动态的汇总表格,随着工作表数目的增加或减少,把这些工作表数据显示在一个汇总工作表上呢?

    图1所示是截止到某月的各个月份的利润表,现在要求把这些月份工作表数据汇总到一张工作表上,以便于进一步分析利润表各个项目的变化趋势。

查找和引用的Excel函数

图1

    各个月份利润表会随着时间的推移而增加。例如,目前是7个月份的数据,那么“汇总表”工作表中就显示7个月的数据汇总;如果又增加了8月和9月份的数据,那么“汇总表”工作表上就显示9个月的数据汇总。

    对于这样的多工作表汇总(实质上就是跨工作表数据查询)问题。使用INDIRECT函数是最方便的。考虑到“汇总表”工作表的A列结构与每个分表的结构完全一样。并且每个工作表的名字分别是“01月”、“02月”、“03月”等,而“汇总表”工作表第一行的标题文字也是“01月”、。02月”、“03月”等。这样就可以充分利用标题文字和工作表名称来创建高效查询公式了。

    激活工作表“汇总表”。在单元格B2中输入公式:

    =INDIRECT(B$1&"!B"&ROW())

    将其向右复制到单元格M2.然后选择单元格区域B2:M2.将其向下复制到第17行。就得到了各个工作表的汇总数据,如图2所示。

查找和引用的Excel函数

图2

    在这个公式中,字符串“B$1&"!B'&ROW()”构建了对某个工作表单元格的引用。例如。对于单元格B2.该字符串是“01月1B2".使用INDIRECT函数将这个字符串转换为真正的单元格地址引用。即可得到工作表“01月”的B2单元格中的数据。

    但是,当工作表不存在时,公式就会得到错误的结果。例如目前只有7个月的数据。在“汇总表”工作表中|列以后就是错误值“#REF!”。为了不显示这个错误值。使表格整洁美观,可以使用条件格式来隐藏这些错误值。

    选择单元格区域B2:M17.单击“开始”选项卡,在“样式”功能组中选择“条件格式”|“新建规则”命令。打开“新建格式规则”对话框。在“选择规则类型”列表中选择“使用公式确定要设置格式的单元格”选项。然后在“编辑规则说明“选项组中输入计算公式“=ISERROR(B2)”,单击“格式”按钮。打开“设置单元格格式”对话框,将字体颜色设置为白色。条件格式设置情况如图3所示。

Excel中新建格式规则

图3

    这样,如果又增加了8月和9月份的数据。那么“汇总表”工作表中就会显示9个月的数据汇总,如图4所示。

Excel汇总表

图4

    查找和引用的Excel函数除此以外,这种使用INDIRECT函数汇总多个工作表数据的方法还有一个优点,就是不受各个Excel工作表先后顺序的影响,也就是说,各个Excel工作表的先后顾序是可以任意调整的。 

原创文章如转载请注明:转载自Excel学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/shujufenxi/509.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号