Excel 使用 SUMPRODUCT 與 SEARCH 函數一次搜尋多個關鍵字。原本網路文章只能處理布林邏輯 OR 條件，透過 ChatGPT 瞭解公式，再修改成可以用在 AND 與 NOT 條件。
 |
| Made with DALL-E 3 |
網路上有一份文件「如何利用 SUMPRODUCT 一次搜尋多個關鍵字」：
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$5,A2)))>0
網頁上沒有任何說明。經詢問 ChatGPT 以及實際測試，上面公式的用途是搜尋「A2」欄位值，如果包含「$D$2:$D$5」儲存格內的任一關鍵字，則回傳 TRUE、如果沒有則回傳 FALSE。
本站「Excel 布林搜尋多個關鍵字」、「Google 試算表 (google sheet) 搜尋一個或多個關鍵字」文章，也是使用 SEARCH 函數，但是沒有使用 SUMPRODUCT 函數。一旦關鍵字多達數十個時，會導致函數變成很長，就會遇到 Excel 儲存格的函數長度限制：8,192 個字元。使用 SUMPRODUCT 函數，就可以避開這個問題。
公式整理與修改
狀況一：文章內容是否包含任一關鍵字 (布林邏輯：OR)
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$5, A2)))>0
搜尋「A2」文章欄位值，如果包含「$D$2:$D$5」儲存格內的任一關鍵字，則回傳 TRUE、如果沒有則回傳 FALSE。
狀況二：文章內容是否包含所有關鍵字 (布林邏輯：AND)
=SUMPRODUCT(--COUNT(SEARCH($D$2:$D$5, A2)))=COUNTA($D$2:$D$5)
搜尋「A2」文章欄位值，如果包含「$D$2:$D$5」儲存格內的所有關鍵字，則回傳 TRUE、如果沒有則回傳 FALSE。這裡用到 COUNT 函數，可參考 Excel 布林搜尋多個關鍵字 文章說明。
狀況三：文章內容是否不包含所有關鍵字 (布林邏輯：NOT)
=SUMPRODUCT(INT(COUNT(SEARCH($D$2:$D$5, A2))))=0)
搜尋「A2」文章欄位值，如果不包含「$D$2:$D$5」儲存格內的所有關鍵字，則回傳 TRUE、如果沒有則回傳 FALSE。
公式說明
以下是 ChatGPT 詢問結果，經過編輯與文字修正：
1. SEARCH 函數：
- `SEARCH($D$2:$D$5,A2)` 會在儲存格 A2 (文章) 中尋找範圍 `$D$2:$D$5` (多組關鍵字) 中每一個值的出現位置。
- 如果找到相符的文字，`SEARCH` 會回傳該文字在 A2 中的起始位置（正整數）；如果沒有找到，則回傳 #VALUE! 錯誤值。
2. ISNUMBER 函數：
- `ISNUMBER(SEARCH($D$2:$D$5,A2))` 會檢查 `SEARCH` 函數的回傳值是否為數字。
- 對於每一個在 A2 中找到的相符文字，它會回傳 `TRUE`（因為 `SEARCH` 回傳數字）；對於未找到的情況，它會回傳 `FALSE`（因為 `SEARCH` 回傳 #VALUE! 錯誤值）。
3. 雙負號 (`--`)：
- `--ISNUMBER(SEARCH($D$2:$D$5,A2))` 會將 `TRUE` 和 `FALSE` 值轉換為 1 和 0。
- 這是因為在 Excel 中，`TRUE` 被當作 1，`FALSE` 被當作 0。
- 備註：也可以使用 INT 函數，將 `TRUE` 和 `FALSE` 值轉換為 1 和 0。
4. SUMPRODUCT 函數：
- `SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$5,A2)))` 則將轉換後的 1 和 0 加總。
- 這意味著它會計算 A2 中有多少次與 `$D$2:$D$5` 範圍中任何一個值相符的情況。
5. 比較運算符 (`>0`)：
- 最後，`>0` 用來檢查 `SUMPRODUCT` 的結果是否大於 0。
- 如果是，整個公式的結果為 `TRUE`（表示 A2 至少包含範圍 `$D$2:$D$5` 中的一個值）；如果不是，則結果為 `FALSE`。
留言
張貼留言