各種格式下求餘額令人頭疼。餘額就是當天最後一個金額,如果當天沒有就獲取前一個日期最後一個金額。
其實,這種不管怎麼變就那麼幾個套路,一起來破解套路。
1.日期格式相同,獲取每天的餘額
這種非常簡單,藉助LOOKUP函數查找最後一個滿足條件的值這個特點即可解決。
=LOOKUP(G2,B:E)
2.日期格式不同,獲取每天的餘額,每天只有一個對應值
這種可以藉助函數將兩邊的日期格式轉換成一樣的,然後再處理。
左邊的用DAY函數提取日。
右邊的用SUBSTITUTE函數將日替換掉,再用--將文本數字轉換成數值。
有了這2個輔助列,就變得很簡單,繼續用LOOKUP函數查找。
這是一種思路,最後也可以直接將3條公式合併起來。
=LOOKUP(--SUBSTITUTE(H2,"日",""),DAY($B$2:$B$8),$F$2:$F$8)
還是剛剛的2個表格,現在只要查找當天的餘額,如果當天沒有餘額的顯示空白,又該如何處理?
剛剛的2個案例是用LOOKUP函數查找區間的2種語法,也叫模糊查找。其實LOOKUP函數的語法非常多,現在用精確查找來解決剩下的2個問題。
=IFERROR(LOOKUP(1,0/(G2=$B$2:$B$39),$E$2:$E$39),"")
=IFERROR(LOOKUP(1,0/(--SUBSTITUTE(H2,"日","")=DAY($B$2:$B$8)),$F$2:$F$8),"")
最後總結一下LOOKUP函數的語法:
=LOOKUP(查找值,區域)
=LOOKUP(查找值,查找區域,返回區域)
=LOOKUP(1,0/(查找值=查找區域),返回區域)
LOOKUP是一個很難精通的函數,用法靈活多變,平常第3種語法用得最多,稱為經典查找模式,如果其他記不住,就記住這種就好,80%的查找都可以通過這個模式搞定。
閱讀更多 牛賬網 的文章