克里斯有一个工作表,在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。