來自遠方的網友問到：在 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 函數的應用
留言列表