2015年5月6日 星期三

Excel-由字串篩選資料計算個數和加總(SUMPRODUCT、SUBSTITUTE)

網友問到:在 Excel 的資料表中有一個資料數列,其中的資料含有 5"、6"、7" 的 A管、B管、C管 的組合,每一種組合都設有一個長度,如何能根據這些沒有排列規則的數列,來計算各種排列的個數和總度?(參考下圖,以紅色字為例求 5" 的 B 管之個數和總長度。)

雖然儲存格內容是任意排列的資料,不過透過 SUBSTITUTESUMPRODUCT 函數,可以使用很短的公式即可求得答案。

 

【準備工作】

選取儲存格A1:B24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料、長度。

 

【設計公式】

(1) 計算個數

儲存格E2:=SUMPRODUCT((SUBSTITUTE(資料,E$1&$D2,"")<>資料)*1)

SUBSTITUTE(資料,E$1&$D2,""):將資料範圍內的所有儲存格內容之 5"A 以空白取代。

(SUBSTITUTE(資料,E$1&$D2,"")<>資料):用上式的結果判斷是否與原儲存格內容不相符,若是則傳回 TRUE,若否則傳回 FALSE。其中 TRUE 表示該儲存格中「有」包含 5"A,若為 FALSE 代表該儲存格中「沒有」包含 5"A。如此傳回 TRUEFALSE 陣列。

公式中的「*1」,可以藉由數值運算將 TRUEFASLE 陣列轉換為 1/0 陣列。

透過 SUMPRODUCT 函數可以計算為 TRUE 的個數,其結果即為含有 5"A 的個數。

複製儲存格E2,貼至儲存格E2:G4。

 

(2) 計算總長度

儲存格E7:=SUMPRODUCT((SUBSTITUTE(資料,E$1&$D2,"")<>資料)*長度)

公式原理同(1)的說明,不同之處為「*1」修改為「*長度」,經由 SUMPRODUCT 函數可以求得 TRUEFALSE 陣列及長度陣列的「乘積和」,其結果即為總長度。

複製儲存格E7,貼至儲存格E7:G9。

沒有留言:

張貼留言

檢視其他文章

好康東東