如下圖1所示，單元格區(qū)域A1:E12是一組數(shù)據(jù)，單元格區(qū)域B16:C20是一個標(biāo)準(zhǔn)查找表。其中，列D中的數(shù)據(jù)為對應(yīng)的列C中的數(shù)據(jù)減去列A中的數(shù)據(jù)，例如D2=C2-A2；列E中的數(shù)據(jù)為：列B中的值在查找表B16:C20中查找相應(yīng)的解決用時，然后與列D中的值比較，如果列D中的值小于查找到的解決用時，則輸入“Pass”，否則輸入“Fail”。如何使用公式在列E中輸入相應(yīng)的值？
圖1
先不看答案，自已動手試一試。
公式
在單元格E2中，輸入數(shù)組公式：
=IF((D2)<=(TIMEVALUE(INDEX($C$17:$C$20,MATCH(B2,$B$17:$B$20,0),0))),”Pass”,”Fail”)
下拉至單元格E12。
公式解析
首先看公式中的：
MATCH(B2,$B$17:$B$20,0)
在單元格區(qū)域B17:B20中查找單元格B2中的值“SL3”出現(xiàn)的位置，顯示結(jié)果為：3。
傳遞給INDEX函數(shù)：
INDEX($C$17:$C$20,MATCH(B2,$B$17:$B$20,0),0))
即：
INDEX($C$17:$C$20,3,0))
獲取單元格區(qū)域C17:C20中第3行的值：08:00。
然后將該值轉(zhuǎn)換為時間序數(shù)：
TIMEVALUE(08:00)
得到：
0.333333333333333
將其與單元格D2中的值比較。在單元格D2中顯示的是“時:分”格式，但Excel實(shí)際存儲的是時間序數(shù)，因此可以正確地對兩者進(jìn)行比較。結(jié)果為：True，因此最終公式的結(jié)果為：
Pass
這個公式比較簡單，關(guān)鍵是在比較前應(yīng)使用TIMEVALUE函數(shù)將時間轉(zhuǎn)換為序數(shù)。我們知道，Excel是以序數(shù)的形式存儲時間的，一天的時間范圍從0至0.99999999，代表0:00:00至23:59:59。序數(shù)可以這樣換算：
小時數(shù)*60/一天的總分鐘數(shù)
例如：8:00可以換算為：
8*60/1440=0.333333333333333
上面使用了MATCH/INDEX函數(shù)組合進(jìn)行查找，本例中也可使用VLOOKUP函數(shù)來查找，公式為：
=IF(D2<TIMEVALUE(VLOOKUP(B2,$B$17:$C$20,2,FALSE)),”Pass”,”Fail”)
結(jié)果相同。
