Excel 公式：檢查儲存格是否包含所有指定項目

假設在 Excel 中，E 欄包含一組值，您希望檢查 B 欄的儲存格是否包含 E 欄中的所有值，並如以下截圖所示傳回 TRUE 或 FALSE。本教學將提供一個公式，協助您輕鬆完成這項任務。

通用公式：

參數

返回值：

此公式會傳回邏輯值：FALSE 表示儲存格未包含所有項目，TRUE 則表示儲存格已包含所有項目。

公式運作方式

例如，若 B 欄包含一組文字字串，而您希望檢查這些字串是否包含 E3:E5 範圍內的所有值，請使用下列公式。

按下 Enter 鍵，並向下拖曳填滿控點至您要檢查的儲存格。FALSE 表示該儲存格未包含 E3:E5 範圍內的所有值，TRUE 則表示相對儲存格已包含全部值。

說明

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

ISNUMBER 函數：用於判斷某值是否為數字，並傳回 TRUE 或 FALSE。在此，ISNUMBER(SEARCH($E$3:$E$5,B4)) 將傳回陣列結果 {true;true;true}，因為 SEARCH 函數成功找到了 3 個對應項目。

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

SUMPRODUCT 函數：用於將多個範圍相乘或對陣列求和，並傳回乘積總和。SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) 傳回 1＋1＋1＝3.

COUNTA 函數：傳回非空白儲存格的數量。COUNTA($E$3:$E$5) 傳回 3，因此當 SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) 的結果等於 3 時，整個公式將傳回 TRUE；否則傳回 FALSE。

備註：

公式 =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) 並非完全精確的檢查方式。詳情請參見以下截圖：

範例檔案

按一下即可下載範例檔案

相關公式

• 計算等於指定值的儲存格數量
  運用 COUNTIF 函數，輕鬆計算等於或不包含特定值的儲存格數量！
• 計算等於 x 或 y 的儲存格數量
  有時您需要計算符合兩個條件其中之一的儲存格數量，此時可使用 COUNTIF 函數輕鬆達成！
• 計算同時等於 x 與 y 的儲存格數量
  本文介紹用於計算同時符合兩個條件的儲存格數量的實用公式。
• 計算不等於指定值的儲存格數量
  本文將說明如何運用 COUNTIF 函數，輕鬆計算不等於特定值的儲存格數量！