2013年10月25日 星期五

Excel-計算分組小計(SUMPRODUCT,VALUE,LEFT,RIGHT,LEN)

在 Excel 中,有人取得下圖中的資料,是由一個分組編碼和一個數字組合而成,如果想要在一個儲存格中計算各個分組的小計,而不需要使用輔助欄位來分離資料中的編碼和數字,再予以加總,該如何處理?

【準備工作】

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

 

【輸入公式】

儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*VALUE(RIGHT(資料,LEN(資料)-1)))

LEFT(資料,1)=C2:取出資料陣列中每個儲存格的第一個字元,並判斷是否與儲存格C2相同,得到一個 True/False 陣列。

RIGHT(資料,LEN(資料)-1)):取出資料陣列中每個儲存格除了第一個字的其他字元。

VALUE(RIGHT(資料,LEN(資料)-1)):利用 VALUE 函數將上述字元(字串)轉換成數字。

在 SUMPRODUCT 函數中使用「*」運算,將上述的 True/False 陣列轉換為 1/0 陣列。

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

沒有留言:

張貼留言

檢視其他文章

好康東東