Excel 公式：檢查儲存格是否包含多個值其中之一，但排除其他值

假設您有兩份值清單，想檢查 B3 儲存格是否包含 E3:E5 範圍中的任一值，同時又不包含 F3:F4 範圍中的任何值（如下圖所示）。本教學將提供一個實用公式，協助您在 Excel 中快速完成這項任務，並詳細說明該公式的各個引數。

通用公式：

引數

返回值：

此公式會傳回 1 或 0：當儲存格包含任一需包含的值，且不包含任何需排除的值時，傳回 1；否則傳回 0. 其中，1 與 0 會被視為邏輯值 TRUE 與 FALSE 來處理。

公式運作方式

假設您想檢查 B3 儲存格是否包含 E3:E5 範圍中的任一值，同時排除 F3:F4 範圍中的值，請使用下列公式：

按下 Enter 鍵，立即取得檢查結果！

說明

第 1 部分：(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) 檢查儲存格是否包含 E3:E5 中的值

SEARCH 函數：SEARCH 函數會傳回某文字字串在另一字串中首次出現的位置。若找到相符文字，即傳回其相對位置；若未找到，則傳回 #VALUE！ 錯誤。例如，公式 SEARCH($E$3:$E$5,B3) 會在 B3 儲存格中逐一搜尋 E3:E5 範圍內的每個值，並傳回各文字字串於 B3 中的位置，最終輸出陣列結果如下：{1;7;12}。

ISNUMBER 函數：當儲存格為數字時，ISNUMBER 函數會傳回 TRUE。因此 ISNUMBER(SEARCH($E$3:$E$5,B3)) 會傳回陣列結果 {TRUE，TRUE，TRUE}，因為 SEARCH 函數找到了 3 個相符項目。

--ISNUMBER(SEARCH($E$3:$E$5,B3)) 會將 TRUE 值轉換為 1，FALSE 值轉換為 0，因此此公式會將陣列結果轉換為 {1;1;1}。

SUMPRODUCT 函數：用於將多個範圍或陣列相乘後加總，並傳回乘積總和。SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) 傳回 1+1+1=3.

最後，將左側公式 SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) 與 0 比較：只要公式結果大於 0，即傳回 TRUE，否則傳回 FALSE。此處結果為 TRUE。

第 2 部分：(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) 檢查儲存格是否不包含 F3:F4 中的值

公式 SEARCH($F$3:$F$4,B3) 會在 B3 儲存格中逐一搜尋 $F$3：$F$4 範圍內的每個值，並傳回各文字字串在 B3 儲存格中的位置。它會傳回如下陣列結果：{#VALUE!;#VALUE!}。

ISNUMBER(SEARCH($F$3:$F$4,B3)) 會傳回陣列結果 {false;false}，因為 SEARCH 函數未找到任何相符項目。

--ISNUMBER(SEARCH($F$3:$F$4,B3)) 會將 TRUE 值轉換為 1，FALSE 值轉換為 0，因此此公式會將陣列結果轉換為 {0;0}。

SUMPRODUCT 函數：用於將多個範圍或陣列對應相乘後加總，並傳回乘積的總和。 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) 傳回 0＋0＝0.

最後，將左側公式 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) 與 0 比較：只要該公式結果等於 0，即傳回 TRUE，否則傳回 FALSE。此處結果為 TRUE。

第 3 部分：將兩個公式相乘

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

在此公式中，1 與 0 會被視為邏輯值 TRUE 與 FALSE 來處理。

範例檔案

點擊下載範例檔案

相關公式

• 檢查儲存格是否包含特定文字
  若要檢查儲存格是否包含範圍 A 中的文字，卻不包含範圍 B 中的文字，可使用 Excel 中結合 COUNT、SEARCH 與 AND 函數的陣列公式，輕鬆達成條件篩選！
• 檢查儲存格是否包含多個值其中之一
  本教學提供一個實用公式，可快速檢查儲存格是否包含 Excel 中指定的多個值之一，並詳細說明公式參數與運作原理！
• 檢查儲存格是否包含某些值其中之一
  假設在 Excel 中，E 欄有一份值清單，您想檢查 B 欄的儲存格是否包含 E 欄中的任一值，並傳回 TRUE 或 FALSE。
• 檢查儲存格是否包含數字
  有時您需要確認儲存格是否含有數字字元。本教學提供一個實用公式：當儲存格包含數字時傳回 TRUE，否則傳回 FALSE。