您現在的位置是:首頁 > 手機遊戲首頁手機遊戲
一個比 vlookup 好用10倍的查詢函式,你知道嗎?
- 2023-01-26
查詢差異用什麼函式
Vlookup是我們最常用的查詢函式,但在實際查詢過程中,它有時卻顯得力不從心,如
指定位置查詢
、
多條件查詢、反向查詢
等。有一個函式卻可以輕易實現,它就是今天的主角:
LOOKUP函式
一、查詢最後一條符合條件的記錄
【例1】查詢A產品的最後一次入庫單價
=Lookup(1,0/(c5:10=B13),D5:D10)
二、多條件查詢
【例2】根據時間和產品名稱查詢
=Lookup(1,0/((b25:b30=C33)*(C25:C30=C34)),D25:D30)
三、指定區域最後一個非空值查詢
【例3】在第16行查詢每個人最後一次還款日期
=Lookup(1,0/(B2:B13<>“”),$A2:$A13)
四、反向查詢
【例4】根據真名查詢A列網名
=Lookup(1,0(b2:b5=A9),A2:A5)
五、模糊查詢之1
【例5】如下圖所示,要求根據提供的城市從上表中查詢該市名的第2列的值。
=LOOKUP(9^9,FIND(A7,A2:A4),B2:B4)
六、模糊查詢之2
【例6】如下圖所示,要求根據地址從上表中查詢所在城市的提成。
=lookup(9^9。find(A$3:A$6,A10),B$3:B$6)
可能很多新手對上面的公式不太理解,為什麼公式要這樣寫。其實只需要記住固定的用法就可以了(
如果你非要了解就看本文最後部分
)。即:
lookup(
1
,
0
/(
(條件1)*(條件2)*。。(條件n)
),
區域或陣列
)
當然lookup函式的用法遠不止以上這些,它是excel函式高手的最愛,如果有人對你說“我函式還可以”,你就問他:你會用lookup嗎?
如果你很想很想了解lookup的用法,小編就把原理告訴你,能不能懂就看你的悟性了。
附
lookup查詢原理:
二分法
想了解lookup的查詢原理和更深入的使用它,你必須瞭解今天要學習的
二分法
原理。
從一個例子說起:
【例】下圖中左表和右表只有第5行的會員名子不同,但在第11行查詢
B
對應的消費金額時結果卻不同。甚至左表中查詢到的是會員A的消費金額。
公式:
B11 =
LOOKUP(A11,A2:B8)
E11 =
LOOKUP(D11,D2:E8)
其實,lookup函式很清醒,一點都不傻,只是我們對它瞭解的太少了!
lookup函式查詢是遵循
二分法
查詢原理,所以要看懂上例中的查詢結果,必須要了解什麼是二分法查詢。(
二分法是excel中最難理解的函式知識點,建議同學們
洗把臉清醒一下
再向下看
)
一、什麼是二分法。
從前向後一個一個的查詢,是
遍歷法
。二分法不是這樣,它是從二分位處查詢,如果查詢不到再從下一個二分位處查詢,直到查詢到和他大小相同或比它小的數。
二、基本原理。
想了解二分法,必須瞭解下面2個原理。
1、二分位的判定
說白了,二分位就是中間的位置,如果有
7
個數(lookup函式的第2個引數的總行數),那麼第
4
個數就是中間的位置。
=LOOKUP(A11,A2:B8)
如果有10個數呢,則第5個位置是二分位。這裡有一個公式可以計算出來。
=INT((總行數+1)/2)
2、查詢方向確定
當在二分位查詢不到時,接下來該怎麼查詢呢?
當上一次二分位值
大於
查詢的值時,
向上
繼續查詢,在
二分位上面區域
找出新的二分位,直到找出符合條件的值。如下圖中,先從第5行查,因為
C>B
,所以就向上繼續查,上面區域
D2:D4
區域的二分位值是
D3
,而D3的值是B,則對應的E列值800是是查詢結果。
當數值
小於
查詢的值時,向
下
繼續按二分法查。如下圖中,先查詢第
5
行,發現A
D>B
,所以A7的A最終符合條件(查詢到和目標值相等,或比目標值
小
的值)
當二分值
等於
查詢的值時,向下
逐個查
,最後
相鄰且相等
的值即符合條件。
如下圖所示,在A5(第1個二分位)查詢到了A,本來查詢結果應該是B5的860,但由於A6的值也是A,所以查詢結果變成了第6行的值。而A8雖然也是A,但和A5不是
連續相等區域
。所以結果不是B8。
三、lookup示例解析
1、區間查詢。
【例1】如下圖所示,是典型的區間查詢公式。查詢350對應的提成比率是7%。
查詢過徎:
從第1個二分位處(A4)處開始查詢,200<350,向下查詢。
在第2個二分位處(A6)的值400>350,所以要向上查,上面只有A5一個值300,且該值小於350,所以B5的值即為最終查詢結果。
2、查詢最後一個。
【例2】如下圖所示,查詢顧客A最後一次的消費金額。
分析
:
這裡肯定不能直接用Lookup查詢B,否則按二分位查詢,結果不一定是最後一個。所以就用0除的方法把符合條件的變成0,不符合條變成錯誤值:
=0/(A2:A8=A11)
結果是
{
0
;
0
;#DIV/0!;#DIV/0!;
0
;#DIV/0!;#DIV/0!}
由於lookup會過濾掉錯誤值,所以在lookup眼中,上面的陣列是這樣的:
{0;0;0}
然後用1查詢最後一個0的位置
=Lookup(1,0/(A2:A8=A11),B2:B8)
這裡1是
任一個大於0的值
,因為大於0,所以用二分法查詢時,會一直向下查詢,直到最後一個0值。
lookup的二分法查詢,是跳躍式的查詢。它總認為被查詢的1組值是從小到大排列,如果遇到小的就向下找更大的,如果遇到大的就向上找更小的。
在Excel表格中查詢,除了一般的精確查詢,其他的都可以承包給萬能的lookup函數了。