Excel中匹配诊断列表返回对应身体系统状态的公式需求

前提假设 #

假设你的查找表结构为：

• A列：诊断文本（如FIBROSIS, MYOCARDIAL、SHOCK, PULMONARY）
• B列：对应身体系统（如CARDIO、RESP）
• C列：状态标记（固定为Y）

公式实现 #

1. CARDIO系统状态（F列）

在F2单元格输入以下公式，下拉填充：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(IF($B$2:$B$100="CARDIO",$A$2:$A$100,""),E2)))>0,"Y","")

逻辑说明：

• IF($B$2:$B$100="CARDIO",$A$2:$A$100,"")：筛选出所有属于CARDIO系统的诊断文本
• ISNUMBER(SEARCH(...),E2)：检查患者的诊断文本（E2）中是否包含上述任一诊断，返回布尔值
• --将布尔值转为1/0，SUMPRODUCT求和后，只要有一个匹配结果就大于0，返回Y，否则留空

2. RESP系统状态（G列）

在G2单元格输入以下公式，下拉填充：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(IF($B$2:$B$100="RESP",$A$2:$A$100,""),E2)))>0,"Y","")

逻辑与F列完全一致，仅将系统筛选条件改为RESP。

适配旧版Excel（无动态数组函数） #

如果你的Excel版本不支持动态数组，可改用FILTERXML拆分诊断文本，结合COUNTIF实现：

F列公式：

=IF(COUNTIF($A$2:$A$100,"*"&TEXTJOIN("*",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(E2,", ","</s><s>")&"</s></t>","//s"))&"*")>0,"Y","")

逻辑说明：

• SUBSTITUTE(E2,", ","</s><s>")将多诊断文本转为XML格式
• FILTERXML(...)拆分出单个诊断条目
• TEXTJOIN拼接成通配符匹配格式，COUNTIF统计查找表中匹配的诊断数量，大于0则返回Y

注意事项 #

• 调整公式中$B$2:$B$100、$A$2:$A$100的单元格范围，匹配你实际的查找表行数
• 若需忽略诊断文本的大小写差异，保留SEARCH函数即可；若需严格区分大小写，替换为FIND函数

内容的提问来源于stack exchange，提问作者Lauren P