來自遠方的網友問到:在 Excel 中,如果要根據項目表中的內容,來判斷是否含有特的關鍵字,該如何處理?
參考下圖,在輸入的文字中,如何在項目表中找尋對應的類別。
【公式設計與解析】
儲存格B2:=IFERROR(INDEX(D2:D5,SUMPRODUCT(ROW(E2:M5)*
(SUBSTITUTE(A2,E2:M5,"")<>A2))-1,0),"X")
複製儲存格B2,貼至儲存格B2:B8。
公式中使用 SUMPRODUCT 函數,所以公式運算可以使用「陣列」來運算。
(1) SUBSTITUTE(A2,E2:M5,"")
利用 SUBSTITUTE 函數,將儲存格A2的內容逐一用儲存格E2:M5中陣列中的每一個元素置換成空字串「""」。
(2) (SUBSTITUTE(A2,E2:M5,"")<>A2)
將第(1)式的傳回值和儲存格A2內容比較是否為「不相等」,傳回TRUE/FALSE陣列。
若傳回 TRUE 值:表示儲存格A2的內容「有」包含儲存格E2:M5中的內容;
若傳回 FALSE 值:表示儲存格A2的內容「沒有」包含儲存格E2:M5中的內容。
(3) SUMPRODUCT(ROW(E2:M5)*(第(2)式))
ROW 函數用以傳回列號。ROW(E2:M5)表示陣列「{2;3;4;5}」。
將第(2)式的傳回值乘上ROW(E2:M5),可以取得第(2)式中傳回 TRUE 者的列號。
(4) INDEX(D2:D5,第(3)式-1,0)
利用 INDEX 函數,透過第(3)式的傳回值,查詢在陣列儲存格D2:D5中對應的內容。
其中「-1」乃因第(3)式的傳回值是 25,而 INDEX 函數中的陣列是 14項。
(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 函數的應用
