2015年9月15日 星期二

Excel-計算資料清單前幾碼相同者的數值總和(LEFT,SUMPRODUCT)

有網友問到:在 Excel 的工作表中有一個資料清單,如下圖的A欄和B欄。如何摘要結果如C欄和D欄?
小計欄位要計算資料清單前幾碼相同者的數值總和,其中,類別欄位(C欄)為標示A欄的前三碼在第一次出現時顯示,其餘以空白顯示,而小計欄位(D欄)則根據C欄的結果,將相同前三碼者之B欄數值予以加總。該如何處理?
【公式設計與解析】
儲存格C2:=IF(LEFT(A2,3)=LEFT(A3,3),"",LEFT(A2,3))
如果本列的前三碼和下一列的前三碼相同者,顯示空白,否則顯示前三碼。
儲存格D2:
=IF(C2<>"",SUMPRODUCT((LEFT($A$2:$A$22,3)=LEFT(A2,3))*$B$2:$B$22),"")
LEFT($A$2:$A$22,3)=LEFT(A2,3):在 SUMPRODUCT 函數中,判斷A欄每一個儲存格前三碼和儲存格A2相同者,傳回一個 TRUE/FALSE 陣列。
將上式置入 SUMPRODUCT 函數,並和B欄的數值執行乘積和。
如果C欄為空白儲存格者,D欄也給予顯示空白。
複製儲存格C2:D2,往下各列貼上。

【延伸練習】
如果改成以下的呈現方式,該如何處理?

以下為參考答案:
儲存格C2:=IF(LEFT(A2,3)=LEFT(A3,3),"",LEFT(A2,3))
儲存格D2:
=IF(C2<>"",SUMPRODUCT((LEFT($A$2:$A$22,3)=LEFT(A2,3))*$B$2:$B$22),"")

沒有留言:

張貼留言

檢視其他文章

好康東東