我有一個文字字串集合，每個文字字串都有一個關聯的數值。我需要對那些「合格」的條目的相關數值進行求和。文字條目透過包含任何一個或多個指定的目標字串而合格。一個條目可能包含多個目標字串，或多個目標字串。但是，如果條目通過包含與任何目標或目標組合的任何匹配而合格，我只想對條目的關聯值求和一次。
例如，假設儲存格 A1:A3 分別包含apple、banana、pear，而 B1:B3 各包含數字1。我的搜尋目標是a和p。所有三個文字條目均符合資格，因為它們每個都包含至少一個目標的至少一個實例。對 B 列中的關聯值求和應傳回結果3。
我嘗試過使用 SUMIF 和通配符目標。我的這個例子的公式是：
=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))
然而，這會重複計算與多個目標相符的條目。在這種情況下，所有三個都包含a，兩個也包含p，因此它產生的總和為5。
如何在不重複計算條目的情況下完成此操作？
答案1
將標準放在工作表內的實際單元格中比硬編碼在公式中更靈活。
如果您使用垂直的，連續的單元格範圍（例如H1:H2）為此目的，並假設範圍是一個垂直的範圍，你可以使用這個數組公式**:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))
如果您堅持在公式中包含標準，那麼：
=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))
問候
答案2
這是一個相對簡單的解決方案。使用關聯值 all 1，它會產生所需的結果3，但我分配了不同的值以證明選擇了正確的值，並包含一個不匹配的條目以進行良好的測量。
條目清單位於 C 欄中，其關聯值位於 D 欄中。
只有某些函數可以使用通配符，因此使用 SEARCH 來尋找目標字串。
處理多個 OR 標準的典型方法是將每個測試的結果相加。但是，當項目可以滿足多個條件時，就會重複計算。為了解決這個問題，需要檢查聚合標準測試以查看總和是否大於零，這就是與關聯值一起使用的值。
將目標搜尋字串作為陣列處理會變得很複雜，因為用於此類公式的常用函數會先計算整個陣列的結果，然後再將其應用於下一項。所以我分別對待每個標準。對於更多標準，只需在測試ISNUMBER(SEARCH("target",range))之前在括號內為每個條件添加另一個即可>0。
SUMPRODUCT 使用普通的非陣列公式進行陣列式計算。
E1中的公式為：
=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)
答案3
我真的以為這會是一個SUMPRODUCT(--公式，但我無法讓它發揮作用。這應該可行 -
=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))
其中將{"d","g"}是您的搜尋字串陣列。
這是一個陣列公式，因此一旦放入，就必須按ctrl+ shft+ entr，並且大括號應顯示在整個函數周圍的公式欄中。
請注意，它僅在您搜尋單一列時才有效。
考慮到通配符，這可能不會起作用。也許正規表示式就是您所需要的？