财务会计网—财会人的网上家园！

标题: 这个考勤表超牛的查询功能，真的超实用！ [打印本页]

作者: admin 时间: 2020-3-23 10:56
标题: 这个考勤表超牛的查询功能，真的超实用！

今天我们学习考勤表一个超牛功能：动态查询。先看查询效果：根据选择的月份不同，生成对应月份的考勤表：

(, 下载次数: 0)

其实有很多Excel用户都想实现这样的查询功能，只要变换查询的关键信息，就可以生成对应的表格。

做这样的表是不是很复杂？需要用到很高深的Excel功能，难道是传说中的VBA功能？

你想多了，做这样的查询表其实只需要一个公式。比如今天的考勤表，它的查询公式为：

=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""

(, 下载次数: 0)

虽然只是一个公式，但看起来有些复杂，大部分新手估计看不太懂。所以我们有必要剖析一下这个它。

我们要想根据G3单元格的日期从对应月份的工作表中返回考勤信息，就需要把日期和工作表名关联起来。所以公式用Text函数从G3中提取年月（G3中看似是年月格式，其实是包含日的），以和工作表名称保持一致。

=TEXT($F$3,"yyyy年m月")

(, 下载次数: 0)

工作表名有了，接下来生成单元格地址。由于所有考勤表格式完成一致，所以总表的单元格（如A7）要提取的也是各个表A7的内容。也就是说接下来要自动生成公式所在单元格的地址（如A7中生成地址A7），所以用了：

=ADDRESS(ROW(),COLUMN()

row（）和Column（）分别返回公式所在单元格的行、列数，然后用Address（行数,列数）生成单元格地址。

它和已生成的工作表名连在一起，正好生成了完成的引用“字符串”

=TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN())

(, 下载次数: 0)

公式生成的字符串只是“字符串”，并不能从对应表中提取数据，所以用Indirect函数把它转换为可以提取值的引用。

=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))

(, 下载次数: 0)

好象公式设置好了，但当向下复制公式时，你就会发现当被提取的值为空时显示0，这显示不是我们想要的。

(, 下载次数: 0)

其实我们用Vlookup函数提取时也遇到这样的问题。怎么把0值转换为空白，高手们是这样做的，在公式后面添加 &""，即：

=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""

到此，公式设置完成。Indirect函数在Excel中是无可替代的动态引用函数，有了它，你就可以做到以“一表查百表”，彻底改变你的表格结构。

欢迎光临财务会计网—财会人的网上家园！ (https://www.cwkjw.com/)