☰

Excel：按分隔符拆分字符串并确定结果数组的长度

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-365
Excel：按分隔符拆分字符串并确定结果数组的长度

我有一张 Excel 表，其中包含如下列：

| 143 | 423 |    |    | 423;234 | 134;234;143 | =SOME_FORMULA() |

数据具有以下特点：

  • 虽然是数字，但它们应该被视为字符串，因为它们是 ID。列的格式为文本。
  • 任何列都可以包含零个或多个 ID 号。如果一列中有多个 ID 号，则它们将以分号分隔。
  • 标记的单元格SOME_FORMULA()应包含该行中所有 ID 号的计数。在此示例中，单元格应显示7。

在搜索如何按分隔符拆分字符串时，我发现的所有答案都依赖于与我的数据不匹配的假设，即他们假设字符串中元素的数量是预先知道的，和/或他们尝试将文本分成固定数量的列（这再次假设元素的数量是预先知道的）。

我怎样才能计算出我所寻找的东西？

答案1

只是想添加一种更简单的方法。问题描述单元格是空白的还是包含一个或多个 ID。如果单元格中有多个 ID，则其他 ID 前面会有一个分号。因此，您可以从非空单元格数加上分号数来获取 ID 数。

在此处输入图片描述

G1中的公式为：

=COUNTA(A1:F1)+SUMPRODUCT(LEN(A1:F1)-LEN(SUBSTITUTE(A1:F1,";","")))

COUNTA 为您提供具有至少一个 ID 的单元格数。该公式的其余部分通过比较所有内容的长度与不带分号的所有内容的长度来找出分号的数量。

答案2

我不太确定您最终是否在寻找 SOME_FORMULA() 或在分隔符上拆分字符串的方法。

不过，我为 SOME_FORMULA() 提出了一个可能的解决方案

假设

  • 您的数据格式为文本

  • 列数是固定的。最右边的其余单元格可以留空

    在此示例中，示例数据位于单元格 C3:J12 中

现在在 K3 中输入以下公式，然后在公式栏中按 CTRL + SHIFT + ENTER 创建一个数组公式。该公式现在应括在花括号中，以表明它是一个数组公式。

将其向下拖动至目标行。

=SUM(IF(LEN(C3:J3)-LEN(SUBSTITUTE(C3:J3,";",""))>=1,IF(C3:J3=";",0,IF(RIGHT(C3:J3,1)=";",LEN(C3:J3)-LEN(SUBSTITUTE(C3:J3,";","")),LEN(C3:J3)-LEN(SUBSTITUTE(C3:J3,";",""))+1)),IF(ISBLANK(C3:J3),0,1)))

在此处输入图片描述

此解决方案中的一些验证和限制

  • 即使你在单元格的最后一个条目后添加了一个额外的 ; 它也会被忽略
  • 空白视为 0
  • 多个 ; 一个接一个将导致验证失败，并且 ID 的数量将在每次后续重复时增加一 ; 因此请避免使用以下数据234;567;;
  • 非空白单元格但仅包含空格仍将计为 1

相关内容