用分隔符连接名称(Microsoft Excel)
克里斯有一个工作表,在A到F列中有客户名称。在G列中,他想包含一个公式,该公式将从六个名称列中获取所有名称,并将它们连接成一个长字符串,每个名称之间都包含字符// 。有可能在所有六列中都没有名称,并且不应有多余的前导//后缀定界符。
在Excel中串联文本很容易。例如,如果您在单元格A2中有东西,并且想要将其与单元格B2中的东西串联起来,则可以使用以下公式:
=A2 & B2
您可以在两个值之间添加//分隔符,只需将它们添加到适当的位置即可:
=A2 & "//" & B2
这很容易。使用这种方法,可以使用以下公式将所有六个名称连接起来:
=A2 & "//" & B2 & "//" & C2 & "//" & D2 & "//" & E2 & "//" & F2
当您意识到其中一些单元格中可能没有任何东西时,事情就变得棘手了。因此,该公式将导致尾部定界符或//结束符或双定界符(////)
结果中间的某处。
显而易见的解决方案是使用IF语句在连接名称单元之前检查它们的内容。但是,这可能会导致公式出奇的长。例如,以下公式将正确执行检查和连接:
=IF(RIGHT(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)),2)="//",LEFT(CONCATENATE(IF(A3="","", CONCATENATE(A3,"//")),IF(B3="","",CONCATENATE(B3,"//")), IF(C3="","",CONCATENATE(C3,"//")),IF(D3="","",CONCATENATE(D3,"//")), IF(E3="","",CONCATENATE(E3,"//")),IF(F3="","",F3)), LEN(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))-2),CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))
是的,这是一个单行公式。 (哇!)此公式使用嵌套IF语句的方法来获得所需的结果。这可能在此特定实例中有效,但是该公式的运行非常接近Excel的限制,即只允许将IF语句嵌套到七个级别。
解决潜在嵌套级别问题的方法是不嵌套IF语句。取而代之的是,您可以分别评估每个单元格并连接返回的所有内容。
=MID(IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)
请注意,此公式要短得多。如果将公式“分解”成多行,您会更好地看到它在做什么:
=MID( IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)
公式中的每个IF语句都计算一个名称单元,如果该单元不包含任何文本,则不返回任何内容(“”),或者返回后跟名称的定界符(//)。然后将整个公式包含在MID语句中,该语句有效地切断了字符串中的第一个//分隔符。
如果不使用ISTEXT函数来评估单元格,而只是简单地进行布尔比较以找出单元格中是否有任何文本,则可以更进一步缩短此公式,如下所示:
=MID(IF(A3>"","//"&A3,"") & IF(B3>"","//"&B3,"") & IF(C3>"","//"& C3,"") & IF(D3>"","//"&D3,"") & IF(E3>"","//"&E3,"") & IF(F3>"","//"&F3,""),3,2000)
这是完全相同的技术,只是短了一点。 (而且比原始公式要短很多。)
只要名称单元格中的值为文本,此公式将非常有用。如果您的姓名列出于某些原因在其中包含数值,则可以轻松地修改公式以使用ISBLANK而不是ISTEXT,如下所示:
=MID(IF(ISBLANK(A3),"","//"&A3) & IF(ISBLANK(B3),"","//"&B3) & IF(ISBLANK(C3),"","//"& C3) & IF(ISBLANK(D3),"","//"&D3) & IF(ISBLANK(E3),"","//"&E3) & IF(ISBLANK(F3),"","//"&F3),3,2000)
_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。
本技巧(2173)适用于Microsoft Excel 97、2000、2002和2003。