在 Excel 中有一個資料表(參考下圖左),如果第 1,2 碼為代號,第 4,5 碼為分組,根據這兩個資料來做小計,該如何處理較為簡單?藉用 SUMPRODUCT 函數,可以用最簡捷的公式計算出來。

首先,選取儲存格A1:A25,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:Data。

【輸入公式】

儲存格D2:=SUMPRODUCT((LEFT(Data,2)=$C2)*(MID(Data,4,2)=D$1)*RIGHT(Data,2))

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

LEFT(Data,2):取出 Data 陣列中每個儲存格左邊 2 個字元的陣列。

MID(Data,4,2):取出 Data 陣列中每個儲存格第 4,5 個字元的陣列。

RIGHT(Data,2):取出 Data 陣列中每個儲存格右邊 2 個字元的陣列。

其中的「*」運算,可以將判斷產生的 True/False 陣列,轉換為 1/0 陣列。

 

【補充資料】

關於函數說明,請參閱微軟網站的說明:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

 

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()