TEXTJOIN 的替代方案

TEXTJOIN 的替代方案

目前正在 Microsoft Teams 中构建 Excel 表单,但最终需要将其下载到用户计算机中才能填写。TEXTJOIN 在 Microsoft Teams 中在线工作,但这将下载到没有 TEXTJOIN 功能的 Excel 2010...所以我希望使用 CONCATENATE 函数。不确定它如何适合我当前的公式以获得相同的结果。这是当前公式:

=TEXTJOIN(", ", TRUE, IF(D12:D19=Sheet2!C8, IF(C12:C19="高重要性", A12:A19, ""), ""))

谢谢

答案1

您需要连接 8IF个:

= MID(IF(AND(D12=Sheet2!C8,C12="High Importance"),", "&A12,"")&
      IF(AND(D13=Sheet2!C8,C13="High Importance"),", "&A13,"")&
      IF(AND(D14=Sheet2!C8,C14="High Importance"),", "&A14,"")&
      IF(AND(D15=Sheet2!C8,C15="High Importance"),", "&A15,"")&
      IF(AND(D16=Sheet2!C8,C16="High Importance"),", "&A16,"")&
      IF(AND(D17=Sheet2!C8,C17="High Importance"),", "&A17,"")&
      IF(AND(D18=Sheet2!C8,C18="High Importance"),", "&A18,"")&
      IF(AND(D19=Sheet2!C8,C19="High Importance"),", "&A19,""),3,999)

至于 vba,请参阅此处的 TEXTJOINIFS() UDF:

https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a

您可以使用以下公式:

=TEXTJOINIFS(A12:A19,", ",D12:D19,Sheet2!C8,C12:C19,"High Importance")

相关内容