新用戶掃碼下載
掃碼下載APP
及時(shí)接收最新考試資訊及
備考信息
這個(gè)考勤表的查詢功能，真的超實(shí)用！
今天我們學(xué)習(xí)考勤表一個(gè)超牛功能：動(dòng)態(tài)查詢。先看查詢效果：根據(jù)選擇的月份不同，生成對(duì)應(yīng)月份的考勤表：
其實(shí)有很多Excel用戶都想實(shí)現(xiàn)這樣的查詢功能，只要變換查詢的關(guān)鍵信息，就可以生成對(duì)應(yīng)的表格。
做這樣的表是不是很復(fù)雜？需要用到很高深的Excel功能，難道是傳說中的VBA功能？
你想多了，做這樣的查詢表其實(shí)只需要一個(gè)公式。比如今天的考勤表，它的查詢公式為：
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""
雖然只是一個(gè)公式，但看起來有些復(fù)雜，大部分新手估計(jì)看不太懂。所以我們有必要剖析一下這個(gè)它。
我們要想根據(jù)G3單元格的日期從對(duì)應(yīng)月份的工作表中返回考勤信息，就需要把日期和工作表名關(guān)聯(lián)起來。所以公式用Text函數(shù)從G3中提取年月（G3中看似是年月格式，其實(shí)是包含日的），以和工作表名稱保持一致。
=TEXT($F$3,"yyyy年m月")
工作表名有了，接下來生成單元格地址。由于所有考勤表格式完成一致，所以總表的單元格（如A7）要提取的也是各個(gè)表A7的內(nèi)容。也就是說接下來要自動(dòng)生成公式所在單元格的地址（如A7中生成地址A7），所以用了：
=ADDRESS(ROW(),COLUMN())
row（）和Column（）分別返回公式所在單元格的行、列數(shù)，然后用Address（行數(shù),列數(shù)）生成單元格地址。
它和已生成的工作表名連在一起，正好生成了完成的引用“字符串”
=TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN())
公式生成的字符串只是“字符串”，并不能從對(duì)應(yīng)表中提取數(shù)據(jù)，所以用Indirect函數(shù)把它轉(zhuǎn)換為可以提取值的引用。
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))
好象公式設(shè)置好了，但當(dāng)向下復(fù)制公式時(shí)，你就會(huì)發(fā)現(xiàn)當(dāng)被提取的值為空時(shí)顯示0，這顯示不是我們想要的。
其實(shí)我們用Vlookup函數(shù)提取時(shí)也遇到這樣的問題。怎么把0值轉(zhuǎn)換為空白，高手們是這樣做的，在公式后面添加 &""，即：
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""
到此，公式設(shè)置完成。Indirect函數(shù)在Excel中是無可替代的動(dòng)態(tài)引用函數(shù)，有了它，你就可以做到以“一表查百表”，徹底改變你的表格結(jié)構(gòu)。
更多Excel技巧的內(nèi)容歡迎大家關(guān)注正保會(huì)計(jì)網(wǎng)校胡雪飛老師的《財(cái)會(huì)人必須掌握的100個(gè)Excel實(shí)操技巧 》課堂！立即購(gòu)買>>
想學(xué)習(xí)更多財(cái)稅資訊、財(cái)經(jīng)法規(guī)、專家問答、能力測(cè)評(píng)、免費(fèi)直播，可以查看正保會(huì)計(jì)網(wǎng)校會(huì)計(jì)實(shí)務(wù)頻道，點(diǎn)擊進(jìn)入>
實(shí)務(wù)學(xué)習(xí)指南
距6月報(bào)稅結(jié)束還有天
新用戶掃碼下載
京公網(wǎng)安備 11010802044457號(hào)