扫码下载APP
及时接收最新考试资讯及
备考信息
今天我们学习考勤表一个超牛功能:动态查询。先看查询效果:根据选择的月份不同,生成对应月份的考勤表:
其实有很多Excel用户都想实现这样的查询功能,只要变换查询的关键信息,就可以生成对应的表格。
做这样的表是不是很复杂?需要用到很高深的Excel功能,难道是传说中的VBA功能?
你想多了,做这样的查询表其实只需要一个公式。比如今天的考勤表,它的查询公式为:
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""
虽然只是一个公式,但看起来有些复杂,大部分新手估计看不太懂。所以我们有必要剖析一下这个它。
我们要想根据G3单元格的日期从对应月份的工作表中返回考勤信息,就需要把日期和工作表名关联起来。所以公式用Text函数从G3中提取年月(G3中看似是年月格式,其实是包含日的),以和工作表名称保持一致。
=TEXT($F$3,"yyyy年m月")
工作表名有了,接下来生成单元格地址。由于所有考勤表格式完成一致,所以总表的单元格(如A7)要提取的也是各个表A7的内容。也就是说接下来要自动生成公式所在单元格的地址(如A7中生成地址A7),所以用了:
=ADDRESS(ROW(),COLUMN())
row()和Column()分别返回公式所在单元格的行、列数,然后用Address(行数,列数)生成单元格地址。
它和已生成的工作表名连在一起,正好生成了完成的引用“字符串”
=TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN())
公式生成的字符串只是“字符串”,并不能从对应表中提取数据,所以用Indirect函数把它转换为可以提取值的引用。
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))
好象公式设置好了,但当向下复制公式时,你就会发现当被提取的值为空时显示0,这显示不是我们想要的。
其实我们用Vlookup函数提取时也遇到这样的问题。怎么把0值转换为空白,高手们是这样做的,在公式后面添加 &"",即:
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""
到此,公式设置完成。Indirect函数在Excel中是无可替代的动态引用函数,有了它,你就可以做到以“一表查百表”,彻底改变你的表格结构。
更多Excel技巧的内容欢迎大家关注正保会计网校胡雪飞老师的《财会人必须掌握的100个Excel实操技巧 》课堂!立即购买>>
想学习更多财税资讯、财经法规、专家问答、能力测评、免费直播,可以查看正保会计网校会计实务频道,点击进入>
Copyright © 2000 - www.fawtography.com All Rights Reserved. 北京正保会计科技有限公司 版权所有
京B2-20200959 京ICP备20012371号-7 出版物经营许可证 京公网安备 11010802044457号