☰

生成以逗号分隔的单元格内容列表，不包括空白

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
生成以逗号分隔的单元格内容列表，不包括空白

为了连接某四个单元格，我会使用：

=CONCATENATE(A2,",",C2",",D2,",",F2)

这样就使得...

  • A2 = “马太福音”
  • C2 =“标记”
  • D2 =“卢克”
  • F2 = “约翰”

将导致Matthew,Mark,Luke,John。

但我们遇到了类似的问题......

  • A2 =“耶稣”
  • C2 =“”
  • D2 =“玛丽”
  • F2 = “约瑟夫”

这将导致Jesus,,Mary,Joseph。

这里，多余的逗号是不需要的。有没有办法优雅地处理这个问题，以便所有非空白单元格都包含在逗号分隔的列表中，同时避免在某些单元格为空白时添加不必要的逗号？

当然，这可以通过大量嵌套 IF 来实现，但如果可能的话，我真的想避免这种情况。可以使用原生 Excel 函数或数组公式来实现吗？或者是否必须求助于 VB Script 来实现这样的功能？

答案1

在 Excel 2016 中，您可以使用TEXTJOIN：

=TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined)

因此，对于您的情况，您可以使用以下公式：

=TEXTJOIN(",",TRUE,A2,C2,D2,F2)

答案2

正如您所说，嵌套的 IF 并不符合您的口味，但如果没有@nixda 指出的 VBA 解决方案，我看不出还有其他方法可以做到这一点。

对于我提出的解决方案，我假设您的数据如下：

 |    A    |
-+---------+
1| Matthew |
2| Mark    |
3| Luke    |
4| John    |

我有一个解决方案，可以满足你的要求，但是“埋葬”。我在阅读了你最近的回答中留下的评论后明白了这一点（特别是关于你总是第一个单元格已填满）。这样就不存在嵌套，因为第一个条件（在下面的推理中）始终为假。

=CONCATENATE(A1;IF(ISBLANK(A2);"";"," & A2);IF(ISBLANK(A3);"";"," & A3);IF(ISBLANK(A4);"";"," & A4))

您可以随时删除CONCATENATE并用 & 符号替换公式，如@Scott 的回答中指出的那样。目前，这只是一个外观问题。

=A1&如果（ISBLANK（A2）；“”；“，”＆A2）&如果（ISBLANK（A3）；“”；“，”&A3）&如果（ISBLANK（A4）；“”；“，”&A4）

第一个值总是被写入，因此唯一需要做的就是检查积极的单元格为空白，如果不是，则在其后面添加逗号。

这样，您就不需要任何辅助细胞，所有细胞都具有单一功能。

我还写了一个更复杂的版本，因为我假设了一般术语（即可能存在您没有填充第一个单元格的情况）。它只需要 2 级嵌套，因为最多需要检查 3 个条件：

  • 细胞在积极的单元格为空？
  • 是个积极的单元格为空？
  • 以上都不为真吗？（默认条件）

假设您从头开始推理，则所需的格式如下：

  • 如果是，那么不在单元格数据前放置一个逗号。如果没有，则继续。
  • 如果是，那么不放置任何东西（另一种解决方案是放置单元格）。
  • 如果没有一个是真的，则在单元格数据后面放置一个逗号。

因此，这是我使用的公式。我假设所有数据都在一列中，要在一行中使用，请更改一些公式和范围。

=CONCATENATE(A1;IF(ROWS(A1:A1)=COUNTBLANK(A1:A1);A2; IF(ISBLANK(A2);"";"," & A2));IF(ROWS(A1:A2)=COUNTBLANK(A1:A2);A3; IF(ISBLANK(A3);"";"," & A3));IF(ROWS(A1:A3)=COUNTBLANK(A1:A3);A4; IF(ISBLANK(A4);"";"," & A4)))

ROWS计算任意给定间隔内的行数。如果它等于积极的单元格，则表示积极的单元格是空白的，因此前面不应加逗号。

ROWS和中的间隔COUNTBLANK是细胞在后面这积极的细胞。

答案3

如果您愿意使用一些“辅助”单元格来获取中间值，您可能会喜欢这个：

将单元格AA2（或Sheet2!A2，或您想要放置的任何位置）设置为

=IF(A2="", "", A2&",")

（其中只是 的一种简洁说法），并设置单元格、，类似地。设置为x & yCONCATENATE(x, y)AC2AD2AF2AZ2

=AA2 & AC2 & AD2 & AF2

那么你的最终结果是

=IF(AZ2="", "", LEFT(AZ2, LEN(AZ2)-1))

解释：

AA-单元AF格将逗号附加到非空值。因此，使用您的示例，

Matthew         Mark            Luke            John

会导致

Matthew,        Mark,           Luke,           John,

尽管

Jesus                           Mary            Joseph

会导致

Jesus,                          Mary,           Joseph,

（请注意，列中没有逗号C）。 

AZ2是上述内容的简单串联：Jesus,Mary,Joseph,，因此我们删除了后面的多余逗号Jesus，但在末尾添加了一个逗号。  除最后一个字符外LEFT(AZ2, LEN(AZ2)-1)，其余全部AZ2为。我们需要测试是否AZ2为空，以避免在所有四个输入单元格都为空时出现错误。

答案4

如果您想要一个通用的解决方案，最好的答案就是涉及 VBA。

如果您的示例数据与您要解决的完整问题非常接近，那么您只需执行以下操作即可删除双逗号：

=SUBSTITUTE(CONCATENATE(A2,",",C2",",D2,",",F2),",,",",")

上述方法仅在您只有一个值宽的间隙时才有效。您需要嵌套的 substitute() 公式来处理更大的间隙。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,",",C2,",",D2,",",F2),",,,,,,,,,,,,,,,,",","),",,,,,,,,",","),",,,,",","),",,,",","),",,",",")

上述操作会将最多 398 个连续逗号减少为一个。它还会减少 399 以上的许多值，但 399 本身和其他几个值将导致多个逗号。

相关内容