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函數組合,合適自己需求的才是最好的。技多不壓身,多學也無妨!

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

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

熱門推薦

精彩推薦

在這孩子的眼裡,胖瘦只是一個形容詞 它不代表詆毀,也不代表歧視   文/焦糖綠玫瑰 caramelgreen   前陣子,去接DAHLIA下課的時候,旁邊因為同個保姆而早就認識的小可愛,對我說了一句話:「DAHLIA媽媽,妳好胖...
舒暢之槌、七爪神功、錐形護體 3種放鬆加5種技法,變化出多種組合 釋放每日過勞的疲累與僵硬 絕無僅有、玫瑰爭取的獨家這裡買>>https://gbf.tw/cixns   文/焦糖綠玫瑰 caramelgreen   好多人知道我的腰椎與頸...
前陣子去翔翔學校發現在學習區某一角落的每日一書介紹了關於霸王龍的繪本,沒想到又是『宮西達也』的作品集,小編當場被這一排的繪本集吸引;霸王龍系列繪本的畫風線條和用色非常大膽,著實令人印象非常深刻。 ...
依布孕期來到23w囉~~ 超級開心 算是第二孕期階段 之前有跟大家分享過孕初期因為依布我變瘦了~ 所以還真有點擔心無法給小寶貝足夠的營養 從開始知道懷孕後 依布就都在家吃 不過要維持六大類食物的均衡 還是有點小...
前天晚上,我的女兒在公園跟新朋友爬上爬下 兩人玩瘋了,一個不小心 就從爬過數百次的溜滑梯上摔下來   文/焦糖綠玫瑰 caramelgreen   不管體格多強健,好動的孩子,難免都有在遊戲過程中受傷的經驗,前天晚上...
美之極潤膚乳 | Sabrina Cosmos 莎碧琳娜 | 讓妳散發淡淡的花香味 平日生活中都會擦點乳液,尤其平日待在冷氣房時間越久,皮膚更容易乾澀, Sabrina Cosmos 莎碧琳娜 美之極潤膚乳 的質地比較接近乳液與精華液的...
前幾天小編我帶寶貝到公園去玩,遠方好幾個街道忽然有熊熊大火燒起,似乎就要延燒到隔壁大樓,當場很多家長都很擔心(包含我),又加上現在正逢暑假期間,不曉得屋內是否有留在家中的孩子,如果有這些孩子是否都平...
各位拔拔媽媽,我們會在  7/5–7 /25  舉行 幼兒童書雜誌特賣 喔,歡迎來挑挑,現場有點讀筆教材、中文幼兒童書、英文兒童童書,各式繪本,還有眾多優惠好康,等你來拿哦!搶優惠~ 高雄嬰兒用品可參考 展示地點: ...
從DAHLIA還小的時候,我就教育她「物盡其用」 其中,最重要的是「捨」       文/焦糖綠玫瑰 caramelgreen   我們很幸運,一路上受到許多人的幫忙,收過不少恩典牌,所以,從DAHLIA還小的時候,我就教育她「物盡...
則回覆