excelperfect

本次的練習(xí)是：如下圖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é)果相同。