如何创建一个列有包含“是”的前一个字段的标题的字段？
我有一张excel表格，格式如下：
╔════════╦════════╦════════╦════════╦═════════╗
║ Field1 ║ Field2 ║ Field3 ║ ... ║ Field10 ║
╠════════╬════════╬════════╬════════╬═════════╣
║ no ║ no ║ no ║ ... ║ no ║
║ no ║ yes ║ no ║ ... ║ no ║
║ yes ║ yes ║ yes ║ ... ║ yes ║
║ yes ║ yes ║ no ║ ... ║ yes ║
║ . ║ . ║ . ║ ... ║ . ║
║ . ║ . ║ . ║ ... ║ . ║
║ . ║ . ║ . ║ ... ║ . ║
╚════════╩════════╩════════╩════════╩═════════╝如你所见，每个字段都可以有“是”和“否”的任意组合
我正在尝试创建一个基于先前显示的数据格式的单一字段。此字段将包含包含“是”的字段的名称。如果“是”存在于多个字段中，则应列出以逗号分隔的字段。
下面是一个示例，说明所需字段可能是什么样子：
╔════════╦════════╦════════╦════════╦═════════╦══════════════════════════╗
║ Field1 ║ Field2 ║ Field3 ║ ... ║ Field10 ║ NewField ║
╠════════╬════════╬════════╬════════╬═════════╬══════════════════════════╣
║ no ║ no ║ no ║ ... ║ no ║ ║
║ no ║ yes ║ no ║ ... ║ no ║ Field2 ║
║ yes ║ yes ║ yes ║ ... ║ yes ║ Field1, ..., Field10 ║
║ yes ║ yes ║ no ║ ... ║ yes ║ Field1, Field2, Field10 ║
║ . ║ . ║ . ║ ... ║ . ║ ... ║
║ . ║ . ║ . ║ ... ║ . ║ ... ║
║ . ║ . ║ . ║ ... ║ . ║ ... ║
╚════════╩════════╩════════╩════════╩═════════╩══════════════════════════╝我正试图用excel公式来实现这一点，但到目前为止，我认为唯一的解决方案是在excel公式中包含所有可能的排列。当然，这是低效和耗时的创造和作出改变。是否有办法有效地达到这一结果？
回答 3
Stack Overflow用户
发布于 2016-07-26 18:55:38
如果您没有Office 365 Excel，那么这里有一个自定义UDF来执行您想做的事情：
Function JoinField(ttl As Range, srchrng As Range, crit As Variant, Optional sep As String = ",") As String
Dim ttlArr() As Variant
Dim srchrngArr() As Variant
Dim i&,j&
ttlArr = ttl.Value
srchrngArr = srchrng.Value
If UBound(ttlArr, 1) <> UBound(srchrngArr, 1) Or UBound(ttlArr, 2) <> UBound(srchrngArr, 2) Then Exit Function
For i = LBound(ttlArr, 1) To UBound(ttlArr, 1)
For j = LBound(ttlArr, 2) To UBound(ttlArr, 2)
If srchrngArr(i, j) = crit Then
JoinField = JoinField & ttlArr(i, j) & sep
End If
Next j
Next i
JoinField = Left(JoinField, Len(JoinField) - Len(sep))
End Function把它放在一个附加到工作簿的模块中。不将其放入工作表代码或ThisWorkbook代码中。
然后，它被称为正常函数：
=JoinField($A$1:$J$1,$A2:$J2,"Yes",",")其中第一个标准是要连接的名称。第二种是有标准的范围。第三是要找的标准。第四是可选的分离特征。缺省值是,。
如果您拥有最新的Office 365 Excel，则可以将此公式用作数组。
=TEXTJOIN(",",TRUE,IF($A2:$J2 = "Yes", $A$1:$J$1,""))作为一个数组，当退出编辑模式而不是Enter时，它需要用Ctrl Enter来确认。如果操作正确，Excel将把{}放在公式周围。
Stack Overflow用户
发布于 2016-07-26 21:09:43
为了演示如何使用多个IF公式，公式将变得多么繁琐和冗长，您可能最终得到如下公式：
=IF(LEFT(IF(A2="yes",A$1,"")&IF(B2="yes",", "&B$1,"")&IF(C2="yes",", "&C$1,"")&IF(D2="yes",", "&D$1,"")&IF(E2="yes",", "&E$1,"")&IF(F2="yes",", "&F$1,"")&IF(G2="yes",", "&G$1,"")&IF(H2="yes",", "&H$1,"")&IF(I2="yes",", "&I$1,"")&IF(J2="yes",", "&J$1,""),1)=",",SUBSTITUTE(IF(A2="yes",A$1,"")&IF(B2="yes",", "&B$1,"")&IF(C2="yes",", "&C$1,"")&IF(D2="yes",", "&D$1,"")&IF(E2="yes",", "&E$1,"")&IF(F2="yes",", "&F$1,"")&IF(G2="yes",", "&G$1,"")&IF(H2="yes",", "&H$1,"")&IF(I2="yes",", "&I$1,"")&IF(J2="yes",", "&J$1,""),", ","",1),IF(A2="yes",A$1,"")&IF(B2="yes",", "&B$1,"")&IF(C2="yes",", "&C$1,"")&IF(D2="yes",", "&D$1,"")&IF(E2="yes",", "&E$1,"")&IF(F2="yes",", "&F$1,"")&IF(G2="yes",", "&G$1,"")&IF(H2="yes",", "&H$1,"")&IF(I2="yes",", "&I$1,"")&IF(J2="yes",", "&J$1,""))上面的公式假定第1行为字段标题，第一个字段开始列A。上面的公式将放在K2中并复制下来。
现在，如果出于某些原因，您不允许使用VBA或以.XLSM格式保存工作表，那么您将需要类似上述可怕公式的内容。
去掉滚动条后，公式看起来更像：
=IF(LEFT(IF(A2="yes",A$1,"")&IF(B2="yes",", "&B$1,"")&IF(C2="yes",", "&C$1,"")&
IF(D2="yes",", "&D$1,"")&IF(E2="yes",", "&E$1,"")&IF(F2="yes",", "&F$1,"")&
IF(G2="yes",", "&G$1,"")&IF(H2="yes",", "&H$1,"")&IF(I2="yes",", "&I$1,"")&
IF(J2="yes",", "&J$1,""),1)=",",
SUBSTITUTE(IF(A2="yes",A$1,"")&IF(B2="yes",", "&B$1,"")&IF(C2="yes",", "&C$1,"")&
IF(D2="yes",", "&D$1,"")&IF(E2="yes",", "&E$1,"")&IF(F2="yes",", "&F$1,"")&
IF(G2="yes",", "&G$1,"")&IF(H2="yes",", "&H$1,"")&IF(I2="yes",", "&I$1,"")&
IF(J2="yes",", "&J$1,""),", ","",1),
IF(A2="yes",A$1,"")&IF(B2="yes",", "&B$1,"")&IF(C2="yes",", "&C$1,"")&
IF(D2="yes",", "&D$1,"")&IF(E2="yes",", "&E$1,"")&IF(F2="yes",", "&F$1,"")&
IF(G2="yes",", "&G$1,"")&IF(H2="yes",", "&H$1,"")&IF(I2="yes",", "&I$1,"")&
IF(J2="yes",", "&J$1,""))工作中丑陋的证据
一个主要的缺点是，如果你需要添加另一个领域，编辑公式将是皇家痛苦的屁股！
Stack Overflow用户
发布于 2016-07-26 18:54:17
如果只有10列，你就可以做某事。像这样
=IF(A2=1;INDIRECT("A1";TRUE);"") &“"，”&IF“(B2=1；INDIRECT(”B1“；TRUE)；”“)&”“&.
在本例中，just添加0，1作为值，而不是yes/no。
https://stackoverflow.com/questions/38597536
复制相似问题
腾讯云开发者
