2013年2月22日 星期五

Excel-取出合於部分名稱的總和(SUMPRODUCT,SUBSTITUTE)

有網友問到,在一個 Excel 的資料清單中(如下圖左),如果想要取出合於部分名稱的總和(如下圖右)該如何處理?

本例為資料清單中的名稱和要搜尋的名稱略有差異,前後多了一些字元。

 

【準備工作】

選取資料範圍,按一下 Ctrl+Shift+F3 鍵,定義名稱:ID、Name、Num。

 

【輸入公式】

儲存格F2:=SUMPRODUCT((ID=F$1)*(SUBSTITUTE(Name,$E2,"")<>Name)*Num)

複製儲存格F2,貼至儲存格F2:J18。

SUBSTITUTE(Name,$E2,"")<>Name:利用 SUBSTITUTE 函數,將B欄中的名稱中,含有E欄中的字串以空白取代。如果取代後的結果和取代前的名稱不相同,則表示B欄中的名稱含有E欄中的字串。

最後,再利用 SUMPRODUCT 函數算出小計。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

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

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

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

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

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

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

 

SUBSTITUTEhttp://office.microsoft.com/zh-tw/excel-help/HP010342927.aspx

SUBSTITUTE:將文字字串中的 old_text 部分以新字串 new_text 取代。

語法:SUBSTITUTE(text, old_text, new_text, [instance_num])

text:要取代字元的文字,或含有該文字之儲存格的參照。

old_text:這是要取代的文字。

new_text:是要用來取代 old_text 的文字。

instance_num:指定要將第幾個 old_text 取代為 new_text

沒有留言:

張貼留言

檢視其他文章

好康東東