vba If 语句 Excel 通配符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28004044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If Statement Excel Wildcard
提问by user3474932
I have two columns, Ref and Text. I need formula to know if text column has disputeword in it. All the same reference should show "Yes" in dispute column. e.g. in below case first 4 cells in dispute column should show "Yes"
我有两列，Ref 和 Text。我需要公式来知道文本列中是否有争议词。所有相同的参考文献都应在争议栏中显示“是”。例如，在下面的情况下，争议列中的前 4 个单元格应显示“是”
Reference Text Dispute
5558012265 Dispute 5004710 06/01
5558012265
5558012265 Dispute 5004710 06/01
5558012265
5551007340
5551007340
Please help I will be very thankful to you
请帮助我将非常感谢你
回答by Rick Hitchcock
You can do this without a helper column. Assuming your data is in cells A2:A10, enter the following as an array formula1in C2, and copy down:
您可以在没有帮助列的情况下执行此操作。假设您的数据位于单元格 A2:A10 中，请在 C2 中输入以下数组公式1，然后向下复制：
=IFERROR(IF(MATCH(A2&"*dispute*",A:A&B:B,0),"Dispute",""),"")
This searches for the value in cell A2 concatenated with the word "dispute" (case-insensitive). The wildcard (*) causes MATCHto ignore anything after the word "dispute" (such as " 5004710 06/01.")
这将搜索单元格 A2 中与单词“dispute”（不区分大小写）连接的值。通配符 (*) 导致MATCH忽略单词“dispute”之后的任何内容（例如“5004710 06/01”。）
The IFfunction outputs "Dispute" if found. If not found, MATCHreturns an error, and IFERRORis used to ignore that error.
IF如果找到，该函数会输出“Dispute”。如果未找到，则MATCH返回错误，并IFERROR用于忽略该错误。
11个数组公式使用输入CtrlCtrl+ ShiftShift+ EnterEnter。
回答by twin
Create an auxiliary column ("C") like this (assuming ref is "A" and text is "B"):
像这样创建一个辅助列（“C”）（假设 ref 是“A”，文本是“B”）：
=1*IFERROR(FIND("dispute",lower(B2))>0,FALSE)
and put this in the "dispute" colum:
并将其放在“争议”栏中：
=IF(SUMIF(A:A,A2,C:C)>0,"Dispute","")
