2014年4月30日 星期三

Excel-排除文字留下數字加總(SUMPRODUCT)

有網友問到:如果一個工作表中的資料項目含有文字和數字(參考下圖),如何排除其中的文字,並且把同類的項目予以計算加總?

【準備工作】

選取儲存格A1:A20,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目。

儲存格D2:=SUMPRODUCT((RIGHT(項目,3)=C2)*(VALUE(LEFT(項目,LEN(項目)-3))))

RIGHT(項目,3)=C2):取出項目陣列中的最右側三個字元,判斷是否和儲存格C2相同,結果得到一個 TRUE/FALSE 的陣列。

LEFT(項目,LEN(項目)-3)):取出項目陣列中排除最右側三個字元所剩的字元,結果為一個數字字元形成的字串陣列,例如:"86","92","20","147","166", … 。

VALUE(LEFT(項目,LEN(項目)-3))):將上式所得數字字元形成的字串陣列,透過 VALUE 函數轉換成數字陣列。

最後透過 SUMPRODUCT 函數,將 TRUE/FALSE 陣列和數字陣列,執行乘積和,即為所求。在運算過程中, TRUE 會被視為 1,FALSE 會被視為 0。本例為:

TRUE*86+TRUE*90+FALSE*20+FALSE*147+TRUE*166+…

=86+90+166+…

複製儲存格D2,貼至儲存格D2:D4。

沒有留言:

張貼留言

檢視其他文章

好康東東