403 | Lookup函數0/結構的詳細剖析 | ||||
示範檔 | 無 | 範例檔 | 403.XLSX | 結果檔 | 無 |
Lookup函數功能很強大,它可以範圍查找、最後一個符合條件查找、多條件查找等…。
如下圖所示,在G4儲存格設置公式,根據G2的入庫時間和G3的產品名稱,從B欄中查找對應的入庫單價。
F3儲存格公式:「=LOOKUP(1,0/((A2:A7=F1)*(B2:B7=F2)),C2:C7)」
有很多同學提問,為什麼要用0/的結構?其實該結構廣泛應用於lookup查找公式中,為了幫助同學們理解以便能靈活應用。今天就此進行詳細的剖析:
首先,先看看(A2:A7=F1)*(B2:B7=F2)運算後是什麼個結果?
在Excel公式中如果:
A和B的值相等,=A=B 會傳回結果True,True在四則運算中相於數字1
A和B的值不相等,=A=B 會傳回結果False,False在四則運算中相於數字0
所以(A2:A7=F1)的結果是由False和True構成的一組值,如果放在儲存格中,結果如E6:E11範圍值所示:
同樣(B2:B7=F2)的結果也是由True和Fasle組成的一組數值,而2個相同大小的一組值相乘,True*True=1,True*False=0,False*False=0,相乘的最終結果是由1和0組成的一組數。如下圖 H7:H12 所示。
由上圖可以看出,相乘結果中值為1的列(G8所示),正是符合兩個條件的行。那麼怎麼把這個1的位置提取出來呢?
Lookup函數的查找原理是二分法。按二分法原理,Lookup函數會在在二分位處查找,要想準確查找到,這組值需要按昇冪排列,而只是公式(A2:A7=F1)*(B2:B7=F2)的結果是不符合要求的。
於是高手們想出了用0除的方法,把結果由1和0變成了由1和錯誤值構成的一組值。唯一符合條件的值為0,其他的均為錯誤值#DIV/0!
Lookup函數還有一個關鍵的特徵,查找時可以忽略錯誤值,這樣一組數值忽略後只剩下一個值,這時只需要使用任一個大於等於0的值查找即可。即:
=LOOKUP(1,0/((A2:A7=F1)*(B2:B7=F2)),C2:C7)
補充:0/的目的就是把符合條件的變成0,其他的變成錯誤值,利用Lookup查找忽略錯誤值的特徵查找到符合條件的值。
留言列表