求可忽略顺序判断单元格多文本全包含的Excel公式

需求说明 #

判断Cell1中所有逗号分隔的文本项，是否全部存在于Cell2中（不考虑文本项的顺序）。

适用公式 #

方法1：Excel 365/2021 动态数组公式 #

若你的Excel支持动态数组，可使用以下简洁公式（假设Cell1为A2，Cell2为B2）：

=AND(ISNUMBER(SEARCH(", "&TEXTSPLIT(A2, ", ")&", ", ", "&B2&", ")))

公式逻辑

• TEXTSPLIT(A2, ", ")：将Cell1内容按, 拆分，得到单个文本项的数组
• ", "&TEXTSPLIT(...)&", "：给每个文本项前后添加, ，避免部分匹配（比如防止"Red"和"Redish"被误判为匹配）
• SEARCH(..., ", "&B2&", ")：在处理后的Cell2内容中查找每个文本项，找到返回位置值，找不到返回错误值
• ISNUMBER(...)：将查找结果转换为TRUE（找到）或FALSE（未找到）
• AND(...)：只要有一个项未找到就返回FALSE，全部找到则返回TRUE

方法2：兼容旧版Excel的公式 #

若使用不支持动态数组的旧版Excel，可使用以下数组公式（输入完成后需按Ctrl+Shift+Enter确认）：

=SUMPRODUCT(--ISNUMBER(SEARCH(", "&TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99))&", ", ", "&B2&", ")))=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

公式逻辑

• SUBSTITUTE(A2,",",REPT(" ",99))：将逗号替换为99个空格，方便后续拆分提取文本项
• MID(..., ...,99)：逐个提取每个文本项，再用TRIM去除多余空格
• 后续SEARCH与ISNUMBER的逻辑和方法1一致
• SUMPRODUCT(--...)：统计找到的文本项数量，与Cell1中的总项数对比，数量相等则返回TRUE，否则返回FALSE

验证示例 #

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