3C科技 娛樂遊戲 美食旅遊 時尚美妝 親子育兒 生活休閒 金融理財 健康運動 寰宇綜合

Zi 字媒體

2017-07-25T20:27:27+00:00
加入好友
上次跟大家講了,如何用VLOOKUP函數建立《員工檔案管理表》。VLOOKUP函數雖然很強大,但其基本用法中只能作正向查找,而不能反向查找,即根據前列查找后列的內容,而不能根據后列查找前列內容。如我用VLOOKUP函數,可以根據工號來查姓名,但是反過來不行。但如果我用INDEX+MATCH函數組合,可實現雙向查找,可順可逆。接下來,我們還是以《員工檔案管理表》舉例,如何根據姓名來查員工信息。1、設置數據有效性關鍵步驟:數據—數據有效性—允許(序列)—來源(框選數據源)2、INDEX函數INDEX翻譯成中文的意思是「索引」,INDEX函數是指返回指定區域、指定位置的數值。INDEX的語法:INDEX(array,row_num,column_num),指返回數組中指定的單元格或單元格數組的數值。INDEX(reference,row_num,column_num,area_num),指返回引用中指定單元格或單元格區域的引用。簡單點說就是 INDEX(區域,第幾行,第幾列),返回區域第幾行第幾列的數值。那麼,如何用INDEX函數來找出李四的身份證號呢?關鍵步驟:輸入INDEX—框選查找區域—李四的身份證號在第3行第3列做到這一步,很多人會直接往右邊複製公式,結果看似和源數據一樣,但是換個姓名後面的信息並沒有隨之變化,所以這樣是不可取的。3、COLUMN函數身份證號是在源數據的第3列,出生日期是在源數據的第4列,以此類推……這個時候,我們可以用COLUMN函數來表達列標,即COLUMN是指查看所選擇的某一個單元格所在第幾列,即它是第幾列。4、MATCH函數COLUMN函數解決了列標的問題,行號我們要用到MATCH函數來表示。MATCH函數的意思是返回目標值在查找區域中的位置,語法為:MATCH(lookup_value,lookuparray,match-type)lookup_value:表示查詢的指定內容;lookuparray:表示查詢的指定區域;match-type:表示查詢的指定方式,用數字-1、0或者1。match_type=0(精確查找)查找精確等於lookup_value的第一個數值,lookup_array按任意順序排列。一般只使用精確查找。match_type=1查找小於或等於lookup_value(目標值)的最大數值在lookup_array(查找區域)中的位置,lookup_array必須按升序排列。match_type=-1查找大於或等於lookup_value(目標值)的最小數值在lookup_array(查找區域)中的位置,lookup_array必須按降序排列。如果我們要查找李四在源數據第幾行,可以怎樣表示?關鍵步驟:輸入=INDEX—框選李四(需查詢內容)—框選源數據姓名列(查詢的區域)—0-(精確匹配)5絕對引用(錯誤做法)很多人以為這樣就完了,直接向右複製發現公式不適用,那是因為我們沒有採取絕對引用。關於絕對引用、相對引用和混合引用這三者的區別,我們舉例來說明:1、相對引用,複製公式時地址跟著發生變化,如C1單元格有公式:=A1+B1當將公式複製到C2單元格時變為:=A2+B2當將公式複製到D1單元格時變為:=B1+C12、絕對引用,複製公式時地址不會跟著發生變化,如C1單元格有公式:=$A$1+$B$1當將公式複製到C2單元格時仍為:=$A$1+$B$1當將公式複製到D1單元格時仍為:=$A$1+$B$13、混合引用,複製公式時地址的部分內容跟著發生變化,如C1單元格有公式:=$A1+B$1當將公式複製到C2單元格時變為:=$A2+B$1當將公式複製到D1單元格時變為:=$A1+C$1規律:加上了絕對地址符「$」的列標和行號為絕對地址,在公式向旁邊複製時不會發生變化;沒有加上絕對地址符號的列標和行號為相對地址,在公式向旁邊複製時會跟著發生變化;混合引用時部分地址發生變化。注意:工作薄和工作表都是絕對引用,沒有相對引用。具體操作很簡單,選中后按一下F4即可切換到絕對引用。(其他幾種引用木模式切換自己可以多按幾下F4試試)6、最後效果圖中可以看到,我們直接搜姓名,相關的員工信息就能顯示出來。無論是VLOOKUP,還是INDEX+MATACH函數組合,合適自己需求的才是最好的。技多不壓身,多學也無妨!

本文由yidianzixun提供 原文連結

寫了 5860316篇文章,獲得 23313次喜歡
精彩推薦