我主要是嘗試在給定單元格中搜索范圍子字串。
我最終反對的是這個例子中的紅色數字：
我目前有類似的東西：
=IF(ISNUMBER(SEARCH("COD",A2)),B2, 0) IF(ISNUMBER(SEARCH("HADDOCK",A2)),B2, 0) IF(ISNUMBER(SEARCH("SALMON",A2)),B2, 0)
但是當然，隨著我搜索的字串數量的增加，維護起來變得很尷尬！
因此，與其將字串“硬編碼”到等式中，不如參考一系列其他具有潛在值的單元格。
我一直在嘗試各種組合，但沒有運氣在線搜索。
即我嘗試過以下操作但沒有運氣：
{=IF(ISNUMBER(SEARCH(Fish,A2)),B2,0)} (where fish is the range of cells)
(but this just takes the first cell in the range call 'Fish')
=COUNTIF(rng,"*"&Fish&"*"))
uj5u.com熱心網友回復：
首先，我會將搜索字串放在列中而不是行中，并且可能將它們完全洗掉到單獨的作業表中。
然后，有幾種方法可以做到。基本思想是從列中取出字串陣列并將其包裝在“*”中，以便部分匹配起作用。所以對于魚，你會得到類似的東西：
="*" & H:H & "*"
這里唯一的問題是您還將包含列標題（魚），并且您范圍內的任何空單元格都將在陣列中用“**”表示，這將匹配任何內容。所以你需要過濾陣列。如果您有 FILTER，那很容易，或者您可以：
="*" & FILTER(H:H, H:H <> "") & "*"
但是您仍然會有列標題。您可以通過不同地指定范圍來避免這種情況：
="*" & FILTER(H2:H10, H2:H10 <> "") & "*"
如果您沒有過濾器，那么您仍然可以這樣做，但我將列出其余的想法，然后再回到替代方法。獲得過濾后的搜索詞串列后，您可以將其與 SUM COUNTIFS 一起使用以獲取匹配項。由于您正在向 COUNTIFS 傳遞一個要匹配的值陣列，它將生成一個匹配陣列，因此您將其傳遞給 SUM 以減少到單個值，如下所示：
=SUM(COUNTIFS($A2, "*" & FILTER(H$2:H$10, H$2:H$10 <> "") & "*"))
我將向范圍添加部分靜態限定符，以便我可以復制它而不必每次都重寫公式。如果 A 列中的值與 H 列中的任何內容匹配，則此公式將為您提供 1 或 0，您可以在 IF 中使用它來獲取 B 列中的值：
=IF(SUM(COUNTIFS($A2, "*" & FILTER(H$2:H$10, H$2:H$10 <> "") & "*")), $B2, 0)
您可以將其復制到 C2:E5 中，然后您將獲得指定的值。如果您只想獲得總和，那么您可以使用 SUMIFS 而不是 COUNTIFS：
=SUM(SUMIFS($B:$B, $A:$A, "*" & FILTER(H$2:H$10, H$2:H$10 <> "") & "*"))
這里需要注意的是，您可以使用陣列文字，而不是將范圍傳遞給 xxxIFS，除非您有大量不斷變化的值，否則這可能更容易閱讀。陣列字面量的另一個優點是所有版本的 excel 都應該支持它，而 FILTER 則不會。陣列文字將使原始公式如下所示：
=IF(SUM(COUNTIFS($A2, "*" & {"cod", "haddock"} & "*")), $B2, 0)
好的，所以如果你沒有 FILTER，你將不得不做一些陣列乘法。你可以用 SUMPRODUCT 做到這一點，但我認為沒有必要。
=IF(SUM(COUNTIFS($A2, "*" & H$2:H$10 & "*") * (H$2:H$10<>"") ), $B2, 0)
在這里，您只是將 countifs 生成的陣列乘以 <>"" 生成的陣列。
Also, there is a way to do it with SEARCH, and it's a preference which to use. You don't have to wrap the terms in "*", but you still have to filter the arrays somehow, so one of these:
=IF(SUM(--ISNUMBER(SEARCH(FILTER(H$2:H$10, H$2:H$10<>""), $A2))), $B2, 0)
=IF(SUM(--ISNUMBER(SEARCH(H$2:H$10, $A2))*(H$2:H$10<>"")), $B2, 0)
or with the array literal:
=IF(SUM(--ISNUMBER(SEARCH({"cod", "haddock"}, $A2))), $B2, 0)
So what we're doing here is using search with the array of values, then taking that array and passing it to ISNUMBER to generate an array of booleans, then coercing that to an array of 0s or 1s with --. Passing that array of 0/1s to SUM gives us a number, and if that number is >0 then it indicates a match.
Hopefully that wasn't too long winded. Let me know if anything was unclear or if you have any trouble.
Also, it would be possible to use IF to filter the match range, like this:
="*" & IF(H2:H10<>"", H2:H10) & "*"
只要FALSE不可能匹配您的任何完整字串，因為它會回傳如下內容：
此外，FILTER 將減少所涉及陣列的大小，因為它只回傳非空行，并且可能會提高性能，但除非您有大量資料，否則我懷疑它是否會引人注目。
uj5u.com熱心網友回復：
有點不同的方法。使用以下公式：
=IF(MAX(IFERROR(SEARCH( INDEX($B$8:$H$10,MATCH(C$1,$A$8:$A$10,0), ),$A2),0))>=1,$B2,"")
...作為矩陣（使用 ctrl shft Enter 而不是 Enter）并用中性的東西填充魚/水果/蔬菜資料庫中的所有空單元格（例如：-）。例如 cod,haddock, ... 變成 ... cod,haddock,-。然后在 C1 中輸入公式，然后向下拖動，然后向右拖動所有公式...
我分幾部分解釋：
{1} INDEX($B$8:$H$10,MATCH(C$1,$A$8:$A$10,0), )
... 它找到專案，以便 C1 匹配 a8:a10 （因此 fish 得到 {cod,haddock,-,-,-} ）。請注意，您的演示一直持續到 D 列，但公式到 H 列為止。回傳一組專案
{2}IFERROR(SEARCH( {1} ,$A2),0))逐個查找 $A 中來自 {2} 的所有專案。回傳一個陣列 0 表示未找到或 >=1 表示找到（例如 {0,11,0,0,0}）
{3} =IF(MAX( {2} >=1 ,$B2,"")...如果找到的最大值 >=1，我們有一個匹配項并回傳 $b2，否則回傳 "" 表示沒有匹配項。
只需記住在一個單元格中輸入公式作為矩陣，然后拖動或復制粘貼...干杯
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/321778.html