将此公式放在C2中，并复制/粘贴到三列

=IF(NOT(ISERROR(SEARCH(C$1,$B2))),1,0)

您可以创建一个自动化的文本到列，这是一个逗号分隔的值，但你可以得到真或假的逗号，空格等需要

Sub Text2Columns()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
    Case "Sheet2", "Sheet3"
        'do nothing
    Case Else
        ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), 
DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, OtherChar:="|", _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 
1)), _
            TrailingMinusNumbers:=True
End Select
Next ws
Application.ScreenUpdating = True
End Sub

这里是一个动态数组解决方案（替代方法），它动态地创建头部并溢出1 s和0 s，它还检查确保它不返回任何**FALSE POSITIVES**：

·单元格C1中使用的公式

=LET(
     a, DROP(B1:B4,1),
     b, SEQUENCE(,MAX(LEN(a)-LEN(SUBSTITUTE(a,",",))+1)),
     c, UNIQUE(TOROW(TEXTSPLIT(TEXTAFTER(","&a,",",b),","),2),1),
     w, LAMBDA(x,y,N(ISNUMBER(FIND(","&INDEX(c,,y)&",",","&INDEX(a,x)&",")))),
     VSTACK(c,MAKEARRAY(ROWS(a),COLUMNS(c),w)))

测试用例一：

如果您将头文件分开拆分，请尝试以下公式：

·单元格C2中使用的公式

=MAKEARRAY(ROWS(B2:B4),COLUMNS(C1:E1),LAMBDA(x,y,
 N(ISNUMBER(FIND(","&INDEX(C1:E1,y)&",",","&INDEX(B2:B4,x)&",")))))