2016年4月25日 星期一

Excel-根據兩個條件來查詢(SUMPRODUCT,LOOKUP)

在下圖中,網友想要根據一個基本表(由員工、地點、時薪組成),要在日期清單中依地點和員工查詢時薪,再根據輸入的時數求得薪資,該如何處理?
依題意,地點和員工是輸入的資料,時薪是要以公式求得,薪資=時薪X時數。
Excel-根據兩個條件來查詢(SUMPRODUCT,LOOKUP)

【公式設計與解析】
(1) 使用 SUMPRODUCT 函數
儲存格D2:=SUMPRODUCT((B2=$I$2:$I$7)*(C2=$H$2:$H$7)*$J$2:$J$7)
(B2=$I$2:$I$7)*(C2=$H$2:$H$7):兩個條件之間的『*』,相當於執行邏輯 AND 運算,傳回 TRUE/FALSE 陣列。第二個『*』表示乘法運算,可將 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後在 SUMPRODUCT 函數中,將上述結果和儲存格J2:J7陣列執行乘積和,結果即為所求。
複製儲存格D2,貼至儲存格D2:D16。

(2) 使用 LOOKUP 函數
儲存格D2:=LOOKUP(1,1/((B2=$I$2:$I$7)*(C2=$H$2:$H$7)),$J$2:$J$7)
複製儲存格D2,貼至儲存格D2:D16。

沒有留言:

張貼留言

檢視其他文章

好康東東