在单个单元格中使用多个条件和多个实例进行 Excel 计数-解网

问：

enter image description here

我需要一个函数的帮助，该函数将在 A 列（例如 A1）中找到 1 的所有值。对于每个值 1，我需要函数来计算以 178 开头的 IP 地址数。或 14.在相应的 B 列中（例如 B1）。IP地址用逗号和空格分隔，例如178.5.4.3,178.5.4.4

我能够数出 178 个实例的总数。在B列中使用以下公式：

=SUM(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"178.",""))/LEN("178."))

或者通过在替代函数中添加四个空格来做到这一点：

=SUM(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"178."," ")))

但是，我似乎找不到一种方法来搜索 A 列中的 1（不计算它），然后计算 178 的数字。和 14.在相应的 B 列中。

目的是找出区域 1、2、3 等中正在使用的 IP 地址数量。


1   14.38.227.88
1   14.38.227.103
1   14.38.227.104
2   178.32.38.106, 178.32.34.241, 178.32.33.65, 178.32.32.20, 178.32.34.135
2   178.32.38.187, 178.32.38.52, 178.32.38.50, 178.32.38.4, 178.32.36.223, 178.32.35.197, 178.32.34.254, 178.32.32.160, 178.32.32.159, 178.32.32.76, 178.32.32.39, 178.32.32.38, 178.32.32.26
3   178.32.33.61, 178.32.33.57
3   178.32.38.180, 178.32.38.119, 178.32.38.64, 178.32.38.62, 178.32.37.215, 178.32.35.108, 178.32.35.42, 178.32.34.242, 178.32.34.237, 178.32.34.1, 178.32.33.60, 178.32.33.59, 178.32.32.78, 178.32.33.56
4   178.32.39.6, 178.32.38.186, 178.32.38.115, 178.32.38.58, 178.32.37.216, 178.32.34.236, 178.32.33.206, 178.32.33.66, 178.32.33.55, 178.32.33.54, 178.32.32.77, 178.32.32.40, 178.32.32.33, 178.32.38.166
4   178.32.33.53, 178.32.38.167
4   178.32.33.68

Excel excel 公式计数单元格多实例

α --> 首先将地址数据拆分为列，IP
φ --> 为那些地址以 & 开头的地址返回一个堆叠成一列的数组，并排除其余不以 & 开头的数组。ZonesIP178.14.
最后，使用两个数组进行矩阵乘积以获取计数，并通过在使用后包装以组合两个数组，排除重复项。MMULT( )UNIQUE()HSTACK( )

此外，上面的公式假设每行将有最大 IP 地址，如果不是，则将其替换为 this 以使其成为动态-- ,，即该公式将是，9999MAX(LEN(B1:B10)-LEN(SUBSTITUTE(B1:B10,",",))+1)

=LET(
     Ψ, MAX(LEN(B1:B10)-LEN(SUBSTITUTE(B1:B10,",",))+1),
     α, TEXTBEFORE(TEXTAFTER(", "&B1:B10,", ",SEQUENCE(,Ψ)),"."),
     φ, TOCOL(IFS(ISNUMBER(XMATCH(α,{"178","14"})),A1:A10),3),
     UNIQUE(HSTACK(φ,MMULT(N(φ=TOROW(φ)),φ^0))))

更新的公式：

该公式根据 OP 的评论进行更新：

OP 说道：

谢谢。我看到它在同一电子表格中处理数据。如果我尝试引用另一个电子表格中的单元格，它似乎不起作用。其中“All_Devices_Requiring_IP_Space”是同一工作簿中的另一个传播，IP 地址位于 E 列中，区域编号位于 D 列中。

我看到它在您的链接中起作用。但是，每次我将公式剪切并粘贴到新单元格中时，我都会在电子表格中收到 #CALC！错误。

我发现了问题。该函数不占用区域列中的空格。数据必须存在于两列的每一行中。这行不通： ' 1 178.32.33.68 5 14.38.227.88 5 14.38.227.103 14.38.227.104 178.32.33.68 14.38.227.88 5 14.38.227.103 6 14.38.227.104

• 细胞中使用的配方C1

=LET(
     δ, FILTER(All_Devices_Requiring_IP_Space!D1:E27,All_Devices_Requiring_IP_Space!D1:D27<>""),
     Ψ, MAX(LEN(INDEX(δ,,2))-LEN(SUBSTITUTE(INDEX(δ,,2),",",))+1),
     α, TEXTBEFORE(TEXTAFTER(", "&INDEX(δ,,2),", ",SEQUENCE(,Ψ)),"."),
     Φ, TOCOL(IFS(ISNUMBER(XMATCH(α,{"178","14"})),INDEX(δ,,1)),3),
     UNIQUE(HSTACK(Φ,MMULT(N(Φ=TOROW(Φ)),Φ^0))))

计数子字符串

=LET(data,A2:B21,ucol,1,vcol,2,crit,{"A.";"B."},dlm,", ",
    ud,CHOOSECOLS(data,ucol),
    vd,CHOOSECOLS(data,vcol),
    u,UNIQUE(ud),
    v,BYROW(u,LAMBDA(r,LET(
        f,FILTER(vd,ud=r),
        sc,TEXTSPLIT(TEXTJOIN(dlm,,f),dlm),
        vc,COUNT(SEARCH(TOCOL(crit&"*"),sc)),
    vc))),
HSTACK(u,v))

=LET(data,A2:B21,ucol,1,vcol,2,crit,{"A.";"B."},dlm,", ",
    ud,CHOOSECOLS(data,ucol),
    vd,CHOOSECOLS(data,vcol),
    u,UNIQUE(ud),
HSTACK(u,BYROW(u,LAMBDA(r,
    COUNT(SEARCH(TOCOL(crit&"*"),
        TEXTSPLIT(TEXTJOIN(dlm,,
            FILTER(vd,ud=r)),dlm)))))))

上一个：在Excel VBA中查找上次使用的单元格

下一个：我目前有一些VBA代码，可以根据单元格的内容运行和突出显示单元格。如果单元格为空，如何添加VBA以检查R列？

在单个单元格中使用多个条件和多个实例进行 Excel 计数

Excel counting with multiple criteria and multiple instances in a single cell

评论

评论

计数子字符串