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