5分鐘做好模板后,查找員工信息只需3秒!

2017/08/09

上次跟大家講了,如何用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+C1

2、絕對引用,複製公式時地址不會跟著發生變化,如C1單元格有公式:=$A$1+$B$1

當將公式複製到C2單元格時仍為:=$A$1+$B$1

當將公式複製到D1單元格時仍為:=$A$1+$B$1

3、混合引用,複製公式時地址的部分內容跟著發生變化,如C1單元格有公式:=$A1+B$1

當將公式複製到C2單元格時變為:=$A2+B$1

當將公式複製到D1單元格時變為:=$A1+C$1

規律:加上了絕對地址符「$」的列標和行號為絕對地址,在公式向旁邊複製時不會發生變化;沒有加上絕對地址符號的列標和行號為相對地址,在公式向旁邊複製時會跟著發生變化;混合引用時部分地址發生變化。注意:工作薄和工作表都是絕對引用,沒有相對引用。

具體操作很簡單,選中后按一下F4即可切換到絕對引用。(其他幾種引用木模式切換自己可以多按幾下F4試試)

6、最後效果

圖中可以看到,我們直接搜姓名,相關的員工信息就能顯示出來。無論是VLOOKUP,還是INDEX+MATACH函數組合,合適自己需求的才是最好的。技多不壓身,多學也無妨!

本文由 一點資訊 提供 原文連結

立即按讚,感謝大大無私地分享
寫了5859835篇文章,獲得4290
Line

熱門推薦

精彩推薦

高雄| Ideology Iphone現場維修包膜 | KAMY手機維修包膜配件店 | 彩繪膜料 高雄| Ideology Iphone現場維修包膜 | KAMY手機維修包膜配件店 ,現場提供很多透明彩繪模料,及手機平板殼,提供手機電池維修更換,給你...
您會告訴別人您現在的情緒嗎?或是別人會從您的表情觀察到您的情緒?或是您的朋友並不知道您有情緒,因為您把它掩飾得很好?人本來就有七情六慾、喜怒哀樂,情緒是與生俱來的,只是我們在日常生活中如何讓情緒透...
對成長中的孩子來說,對於親子之間依附關係的理解,會以觸覺為主。有些孩子被家人以外的人擁抱或接近,會逃避躲開,他們可能不是真的天生害羞,而是缺少了觸覺刺激。 想改善孩子怕生,害羞的性格,爸媽可以透過...
小陽鐵板燒 | 高雄楠梓台糖美食 | 雙人分享餐 | 紐澳沙朗牛排 | 飲料免費暢飲 小陽鐵板燒 | 高雄楠梓台糖美食 除了主廚特餐和精緻全餐以外,還有單點,也提供白飯 飲料 濃湯冰淇淋 免費暢飲 享用,光一份雙人分享...
偌大的世界廣納了多元的民族以及悠久的文化歷史,許多新奇的事物都等著我們親身去探訪。藉由這次的主題《小腳走世界》,希望能幫助孩子拓展宏觀的世界視野,並培養孩子具有豐富的國際觀。 〈GoGo劇場〉 在〈GoGo...
正式啟動為期五天的連假模式,那麼多天安排好去哪兒遛小孩了嗎? 若有經過嘉義,不妨將『故宮南院』納入行程中喔!故宮南院有南、北側停車場,南側離嘉義故宮博物館較近,而北側停車場至博物館步行約需20分鐘,園...
孩子為什麼會有血便? 血便可區分為潛血便、鮮血便及黑色便。大便顏色正常,必須靠檢驗才知道出血的叫潛血便。鮮血便通常是肛門或直腸的出血。胃和十二指腸的出血,排出的多為暗紅色或黑色血便。某些食物,例如:...
  這次決定住在南投一晚,到了晚上竟然開始下起雨來了。 訂的是位在草屯的福美大飯店,完全是看上有軍公教方案。 所以選擇它,畢竟能省一點是一點。 到的時候,比較晚了,附有地下停車場,停好車後坐電梯上來繞了...
高雄髮型設計 | 彌敦道 Hair salon | 挑染頭髮髮色 | 給自己一個全新浪漫髮型 高雄髮型設計 | 彌敦道 Hair salon 有專業髮型設計師為你親自造型,從修剪到挑染頭髮髮色 一手包辦到完成你的頭髮造型,也給自己一個...
媒體型態多元,校園新聞逐漸成為探討焦點,其中校園霸凌和被霸凌事件更成為各國憂心的教育議題。為何孩子的價值觀走樣、行為偏差嚴重、情緒管理也失控?從教養的觀點探究起因,在於孩子從小無法從人與人之間的差...
則回覆