上篇文章分享了日期和時間函數,這篇介紹查找引用函數。在數據量非常少的時候 ,可能並不需要使用查找引用函數,但是數據量一大,你就能感受到查找引用函數有多重要。接下來跟著小魚一起來學習查找引用函數吧!
知識點:
- column columns row rows match vllookup hlookup lookup
- index indirect offset
一、查找函數
1.column函數、columns函數
語法:column(區域)、columns(數組或區域)
作用:column函數——返回一個引用的列號
columns函數——返回某一引用或數組的列數
示例:
2.row函數、rows函數
語法:row(區域)、rows(數組或區域)
作用:row函數——返回一個引用的行號
rows函數——返回某一引用或數組的行數
示例:
3.match函數
語法:match(查找值,查找區域或數組,[配備類型])
作用:返回查找值在查找區域或數組的相對位置
示例:
解讀:公式的第三個參數用中括號表示該參數為選填項,有兩種匹配類型可選。第一種是0(FALSE),代表精確匹配;第二種是1(TRUE),代表近似匹配。如果不填寫第三參數,默認為0(精確匹配)。
4.vlookup函數
語法:vlookup(查找值,查找區域,區域內第幾列,[匹配類型])
作用:根據首列滿足查找值的行序號,返回在區域內對應列數的值
示例:
5.hlookup函數
語法:vlookup(查找值,查找區域,區域內第幾行,[匹配類型])
作用:根據首列滿足查找值的列序號,返回在區域內對應行數的值
示例:
6.lookup函數
(1)向量形式
語法:lookup(查找值,查找區域,[結果區域])
作用:從單行/列中查找一個值
示例:
解讀:公式的第三個參數用中括號表示該參數為選填項。需要注意的是,查找區域和結果區域的範圍應該相等。
仔細的朋友應該有注意到,前四位小陳、小林、小羅、小張的班級查詢結果都是對的,第五位小湯顯示班級為三班,但依照數據來看,其實應該是五班才對。那麼是哪裡出現錯誤了?
出錯的原因在於lookup函數的折半查找算法。折半算法為:將數據按從小到大排好順序,取中間位置的數與查找值對比。如果中位數>查找值,在前半段進行第二次折半查找,否則在後半段進行第二次查找,直到找到匹配的值。如果匹配不到查找值,則返回小於或等於查找值的最大值。如果查找值比查找區域的最小值還小,那麼會返回#N/A錯誤。
所以示例出錯的原因在於查找區域沒有排序。其實不止數值,文本也有排序。文本的排序依據為文本的拼音a~z按位置代表1~26排序。對上述示例修改如下:
所以,要使用到lookup函數,一定要先對查找區域進行排序,不然就會出錯了!
(2)數組形式
語法:lookup(查找值,數組)
作用:從數組中查找一個值
示例:
解讀:lookup函數數組形式中,查找值應位於第二參數數組區域的第一行/列。如果數組區域行數>列數,返回符合查找值所在行對應最後一列的值;如果行數
總結使用lookup函數要注意的點:
- 查找區域(數組形式中,為查找區域的第一行/列)要排序!
- 如果查找值
- 如果沒有找到匹配值,則返回小於查找值的最大值。
7.lookup函數與vlookup函數、hlookup函數的總結
(1)hlookup函數和vlookup函數要求查找值在查找區域的第一行/列,lookup函數沒有這個要求;
(2)lookup函數要求查找區域按小到大排序,hlookup函數和vlookup函數沒有這個要求;
(3)lookup函數只能實現單行/列的查找,hlookup函數和vlookup函數可以實現多列查找。
二 、引用函數
1.index函數
(1)連續區域
語法:index(區域,行號,列號)
作用:返回給定區域中特定行列單位格的交叉值或引用
示例:
(2)不連續區域
語法:index((區域1,區域2,...),行號,列號,第幾個區域)
作用:返回給定區域中特定行列單位格的交叉值或引用
示例:
(3)match函數和index函數的組合使用
優勢:hlookup函數和vlookup函數要求查找值在查找區域的第一行/列,如果數據不滿足這個要求,可以使用match+index函數的組合。
解讀:match函數可以返回查找值對應的行/列號,放入index函數的行/列的位置,然後在列/行的位置輸入目標值所在目標區域的列/行號。
2.indirect函數
語法:indirect(引用區域,[引用格式])
作用:返回文本字符串所指定的引用
示例:
解讀:第二參數引用格式有兩種,第一種 1(TRUE)代表A1格式,可忽略不寫;第二種0(FALSE)代表R1C1格式,這裡解釋一下這個格式,R代表row,C代表column,R1C1意思是第一行第一列交叉位置的單位格,即A1,但這種格式不常用。indirect函數的引用區域加"",為直接引用文本;引用區域不加"",為應用那個該文本表示的位置。
3.offset函數
語法:offset(基準位置,幾行(正下負上),幾列(正右負左),[返回幾行],[返回幾列])
作用:將指定的位置作為參照系,偏移特定的行列返回引用
示例:
(1)不填寫第四、第五參數
(2)填寫第四、第五參數
解讀:如果offset函數的第四、第五參數填寫的話,因為引用的是一個數組區域,所以需要按組合鍵“ctrl+shift+enter”,不然就會報錯。
查找引用函數就分享到這裡了,接下來是Excel系列函數篇的最後一個函數類型——邏輯函數了。敬請期待!
本文首發於公眾號:數據分析魚,歡迎圍觀!