Excel如何识别字符串内6种单词组合 自动匹配填写Open/Close列数值

适配全部规则的Excel公式方案 #

前置说明 #

公式完全匹配你给出的6项匹配规则，默认处理A1单元格的交易文本，可直接下拉适配整列。

提示：如果你发现实际匹配结果和你给出的示例1不符，只需调整对应列公式IF条件里的组合判断规则即可，比如将Bought+Call的判断从C列移到B列即可。

如果你使用Excel 365/2021及以上版本 #

B列（Open列）公式

=LET(
price,TRIM(MID(A1,SEARCH("@",A1)+1,255))*1,
first_word,TEXTBEFORE(A1," "),
has_put,ISNUMBER(SEARCH("Put",A1)),
has_call,ISNUMBER(SEARCH("Call",A1)),
sold_only,AND(first_word="Sold",NOT(OR(has_put,has_call))),
IF(OR(AND(first_word="Sold",has_call),AND(first_word="Bought",has_put),sold_only),price,"")
)

C列（Close列）公式

=LET(
price,TRIM(MID(A1,SEARCH("@",A1)+1,255))*1,
first_word,TEXTBEFORE(A1," "),
has_put,ISNUMBER(SEARCH("Put",A1)),
has_call,ISNUMBER(SEARCH("Call",A1)),
sold_only,AND(first_word="Sold",NOT(OR(has_put,has_call))),
IF(OR(AND(first_word="Sold",has_put),AND(first_word="Bought",has_call),sold_only),price,"")
)

如果你使用2019及以下旧版Excel（不支持LET函数） #

B列（Open列）公式

=IF(
OR(
AND(ISNUMBER(SEARCH("Sold",LEFT(A1,FIND(" ",A1)-1)),ISNUMBER(SEARCH("Call",A1))),
AND(ISNUMBER(SEARCH("Bought",LEFT(A1,FIND(" ",A1)-1)),ISNUMBER(SEARCH("Put",A1))),
AND(ISNUMBER(SEARCH("Sold",LEFT(A1,FIND(" ",A1)-1)),ISERROR(SEARCH({"Call","Put"},A1)))
),
TRIM(MID(A1,SEARCH("@",A1)+1,255))*1,
""
)

C列（Close列）公式

=IF(
OR(
AND(ISNUMBER(SEARCH("Sold",LEFT(A1,FIND(" ",A1)-1)),ISNUMBER(SEARCH("Put",A1))),
AND(ISNUMBER(SEARCH("Bought",LEFT(A1,FIND(" ",A1)-1)),ISNUMBER(SEARCH("Call",A1))),
AND(ISNUMBER(SEARCH("Sold",LEFT(A1,FIND(" ",A1)-1)),ISERROR(SEARCH({"Call","Put"},A1)))
),
TRIM(MID(A1,SEARCH("@",A1)+1,255))*1,
""
)

公式逻辑说明 #

1. 价格提取：通过SEARCH("@",A1)定位@符号位置，截取之后的内容并转为数值格式，避免文本格式无法后续计算
2. 首词识别：365版本用TEXTBEFORE直接提取第一个空格前的内容，旧版用LEFT(A1,FIND(" ",A1)-1)实现相同效果
3. 规则匹配：
   • 优先判断是否为无Call/Put的单独Sold场景，符合则两列都填价格
   • 再分别匹配首词+交易类型的4种组合，符合对应规则就把价格填入对应列，否则留空
4. 所有公式直接下拉即可批量处理A列所有交易文本，粘贴新的交易记录后会自动计算

内容的提问来源于stack exchange，提问作者J. Feeble