☰

Excel 前缀或后缀

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 前缀或后缀

Excel 中有一列包含逗号分隔的列表：

Header
1, 61
61
1, 61, 161
5, 55

我想提取这些数据，以便我可以计算每个项目的出现次数，以便得到以下结果：

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

我尝试使用“*”进行 countif 但结果很乱，因为在这种情况下我有前缀或后缀（1,61,161）

请帮忙！

答案1

选项1：

我想建议使用 UDF（用户定义函数），它不仅可以计算逗号分隔数字的出现次数，还可以计算文本。

在此处输入图片描述

怎么运行的：

  • 按Alt+F11然后获取 VB 编辑器复制&粘贴此代码为模块。

    Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

  • 在范围中输入条件H18:H26，然后在单元格中输入此公式I18并填写。

=CountOccurrence($G$18:$G$24,H18)

选项 2：

在单元格中输入此公式I18并填写。

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

根据需要调整单元格引用。

答案2

  1. 首先，您需要将数字拆分到单独的单元格中：
    • 选择您的数据
    • 在数据选项卡上选择“拆分为列”
    • 选择“分隔”，下一步
    • 选择数据以“逗号”分隔，完成
  2. 现在您可以使用 COUNTIF，例如
    =COUNTIF($A$2:$C$5,E2)

在此处输入图片描述

答案3

看起来您已经有了解决方案，但我会提出一个处理动态数据的非 VBA 解决方案。它使用一些辅助列，您可以将其预填充到任意大的范围。当没有关联数据时，单元格将为空白。可以消除一些辅助列；包含它们是为了最大限度地减少重复，但可以隐藏所有辅助列。

在此处输入图片描述

您的数据在 A 列中。B 列根据逗号的数量确定每个条目中的值的数量。B3 中的公式为：

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

C 列只是 B 列的累计组件计数。C2 输入为0。C3 中的公式为：

=IF(ISBLANK(A3),"",SUM(B3:B$3))

将 B 列和 C 列填充到尽可能多的行中，只要有数据即可。如果需要，您可以随时扩展这些列。

E 列只是为了方便。它为解析的值提供了索引。您可以对 进行硬编码，1然后为每个连续行添加 1。出于任何原因，我根据行号（-2下面公式中的 是调整值以从 开始1）来设置它。超出总值数量的单元格将变为空白。我在 E3 中的公式：

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

F 列只是为了避免公式重复。它会提取相关的 A 列条目，从中解析当前组件。F3 中的公式是：

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

它通过比较 E 列中的组件编号与 C 列中的累积组件计数来找到适当的条目。

G 列是解析后的组件值，全部位于单个连续的列中，便于操作。G3 中的公式为：

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

通过从当前元素编号中减去最后一个“完成”的输入记录的累积元素数来确定从 F 列条目中解析哪个元素。

列 E 到 G 应扩展到足够多的行，以覆盖预期的组件值数量（至少是数据行数的几倍）。请注意，上述所有引用范围 $C$2:$C$10 的公式都应进行调整，以包含您数据的全部范围。

现在，您已将所有解析的元素放在一个漂亮的列中，有多种方法可以聚合它们并获取计数。我使用了数据透视表，它同时还为您提供了唯一值的列表。

选择数据透视表 G 列的完整预填充范围。将该字段用于行窗口和值窗口（选择计数作为聚合）。该范围将包括未使用的行中的空白，因此请使用内置过滤器取消选择空白。

当数据发生变化时，只需刷新数据透视表并验证过滤器中是否选择了任何新元素值。

相关内容