Excel / 利用萬用字元搜尋欄位內容包含某字串並加總數值 (Sum if cells contain specific text)

在 Excel 當中我們可以使用 SUMIF 或 SUMIFS 函數來根據某一欄位的內容，決定另一欄位的數值是否要被加總。但如果我們要判斷的是欄位內容是否包含某個特定字串呢？這時候可以利用 * 這個萬用字元 (Wildcard) 作為判斷條件。下面範例計算當 Item 欄位內容含有 Hoodie 的字串，就加總該列的 Quantity 數值：

=SUMIFS(C6:C17, B6:B17, "*Hoodie*")

同樣的，也可以搜尋含有特定字串開頭或結尾的值，只需標上單邊的萬用字元即可。當萬元字元擺在前面表示字串前方還有其他文字存在，也就是字串是出現在結尾的地方。反過來把萬用字元擺在後面，就是字串出現在開頭的地方：

=SUMIFS(C6:C17, B6:B17, "Hoodie*")

必須注意的是，SUMIF / SUMIFS 這個函數會忽略大小寫的差異，若需判斷大小寫的不同，則可使用下面另一個區分大小寫的方法。

其他萬用字元 (wildcare)

除了 * 這個萬用字元代表任何字元或字串外，還有其他幾個常見的萬用字元可以交互使用，使用方式如下：

Target	Criteria
包含 3 個字元	“???”
在 x 和 z 中間夾一個字元，如 ‘xyz’, ‘xxz’, xaz’…	“x?z”
以 xyz 開頭的字串	“xyz*”
以 xyz 結尾的字串	“*xyz”
任何含有 xyz 的字串，包含 ‘xyz’	“xyz”
任何包含 A1 欄位內的值的字串	“”&A1&””

使用 SUMPRODUCT 和 FIND 函數實現大小寫區別的判斷

若我們想要搜尋特定含有大小寫區別的字串，並加總另一個欄位的數值，可以搭配 SUMPRODUCT 和 FIND 函數來實現。首先 FIND 函數是搜尋某一子字串存在目標字串的哪一個位置，例如 FIND("plane", "airplane") = 4。而在上面的範例當中，若我們想要搜尋含有大寫開頭 Hoodie 的欄位內容並加總，可以使用下面的方式：

=SUMPRODUCT(--ISNUMBER(FIND("Hoodie", B6:B18)) * C6:C18)

以下我們來解析一下這段公式，首先我們知道 FIND 函數可以搜尋子字串的位置，而這次我們使用陣列函數來回傳一整個陣列的搜尋結果：

FIND("Hoodie", B6:B18)

這個函數會回傳 {6, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 7, #VALUE!, 6, 6, 1}。

同時我們再利用 ISMUNBER 這個函數來判斷回傳的資料內容是否為數值內容，其中數值的陣列內容會被轉換為 True，而因為找不到字串而回傳 Value Error 的內容會被轉換為 False，因此內容為 {True, False, False, False, False, False, False, False, True, False, True, True, True}。

接著使用 -- 這個運算子將 True & False 的值轉換為 1 和 0：

--ISNUMBER(FIND("Hoodie", B6:B18) = {1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 1}

並且在乘上每一列的數值後做加總，最終就會得到結果：

  SUMPRODUCT(--ISNUMBER(FIND("Hoodie", B6:B18)) * C6:C18) 
= SUMPRODUCT({1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 1} * {9, 8, 7, 7, 6, 6, 3, 6, 6, 4, 4, 3, 15})
= 37

註：-- 運算子可用可不用，因為在運算當中會自動將 True & False 邏輯值轉換為 1 和 0 計算。

Reference

Sum if cells contain specific text | EXCELJET