學不完．教不停．用不盡

來自遠方的網友問到：在 Excel 中，如果要根據項目表中的內容，來判斷是否含有特的關鍵字，該如何處理？

參考下圖，在輸入的文字中，如何在項目表中找尋對應的類別。

 

【公式設計與解析】

儲存格B2：=IFERROR(INDEX($D$2:$D$5,SUMPRODUCT(ROW($E$2:$M$5)*
(SUBSTITUTE($A2,$E$2:$M$5,"")<>$A2))-1,0),"X")

複製儲存格B2，貼至儲存格B2:B8。

公式中使用 SUMPRODUCT 函數，所以公式運算可以使用「陣列」來運算。

(1) SUBSTITUTE($A2,$E$2:$M$5,"")

利用 SUBSTITUTE 函數，將儲存格A2的內容逐一用儲存格E2:M5中陣列中的每一個元素置換成空字串「""」。

(2) (SUBSTITUTE($A2,$E$2:$M$5,"")<>$A2)

將第(1)式的傳回值和儲存格A2內容比較是否為「不相等」，傳回TRUE／FALSE陣列。

若傳回 TRUE 值：表示儲存格A2的內容「有」包含儲存格E2:M5中的內容；

若傳回 FALSE 值：表示儲存格A2的內容「沒有」包含儲存格E2:M5中的內容。

(3) SUMPRODUCT(ROW($E$2:$M$5)*(第(2)式))

ROW 函數用以傳回列號。ROW($E$2:$M$5)表示陣列「{2;3;4;5}」。

將第(2)式的傳回值乘上ROW($E$2:$M$5)，可以取得第(2)式中傳回 TRUE 者的列號。

(4) INDEX($D$2:$D$5,第(3)式-1,0)

利用 INDEX 函數，透過第(3)式的傳回值，查詢在陣列儲存格D2:D5中對應的內容。

其中「-1」乃因第(3)式的傳回值是 2～５，而 INDEX 函數中的陣列是 1～4項。

(5) IFERROR(第(4)式,"X")

因為公式可能因為第(3)式查詢不到任何內容而傳回錯誤訊息，所以利用 IFERROR 函數將錯誤訊息以「X」顯示。

若將儲存格D2:D3定義名稱「類別」，將儲存格E2:M5定義名稱「項目」，則公式改為：

儲存格B2：=IFERROR(INDEX(類別,SUMPRODUCT(ROW(項目)*(SUBSTITUTE
($A2,項目,"")<>$A2))-1,0),"X")

【延伸閱讀】

參考：本部落格中其他關於 Excel SUMPRODUCT 函數的應用

參考：本部落格中其他關於 Excel SUBSTITUTE 函數的應用