網友問到：在 Excel 的資料表中有一個資料數列，其中的資料含有 5"、6"、7" 的 A管、B管、C管 的組合，每一種組合都設有一個長度，如何能根據這些沒有排列規則的數列，來計算各種排列的個數和總度？(參考下圖，以紅色字為例求 5" 的 B 管之個數和總長度。)
雖然儲存格內容是任意排列的資料，不過透過 SUBSTITUTE 和 SUMPRODUCT 函數，可以使用很短的公式即可求得答案。
【準備工作】
選取儲存格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。如此傳回 TRUE／FALSE 陣列。
公式中的「*1」，可以藉由數值運算將 TRUE／FASLE 陣列轉換為 1／0 陣列。
透過 SUMPRODUCT 函數可以計算為 TRUE 的個數，其結果即為含有 5"A 的個數。
複製儲存格E2，貼至儲存格E2:G4。
(2) 計算總長度
儲存格E7：=SUMPRODUCT((SUBSTITUTE(資料,E$1&$D2,"")<>資料)*長度)
公式原理同(1)的說明，不同之處為「*1」修改為「*長度」，經由 SUMPRODUCT 函數可以求得 TRUE／FALSE 陣列及長度陣列的「乘積和」，其結果即為總長度。
複製儲存格E7，貼至儲存格E7:G9。
留言列表